Common performance analysis steps
1, slow query open and capture
Explain + slow SQL analysis
MySQL > query SQL execution details and life cycle in MySQL server
4. Tuning parameters of SQL database server.
Small tables drive large tables
for(int i = 0; i < 5; i++){ for(int j = 0; j < 1000; j++){ ... } } for(int i = 0; i < 1000; i++){ for(int j = 0; j < 5; j++){ ... }}Copy the codeUsing the code above, if the first type of for is used, then we only create 5 links and the second type of for creates 1000 links. There is no doubt that fewer links are better!
Optimization principle: Small tables drive large tables, that is, small data sets drive large data sets.
Principle:
Select * from A where ID in (select ID from B) is equivalent to for select ID from B for select * from A where A.id = B.idCopy the codeIn takes precedence over EXISTS when the data set of table B must be smaller than that of table A
Select * from A where exists (select 1 from B where B.ID = A.id) equivalent to for select ID from A for select * from B where B.id = A.idCopy the codeIf the data set of A table is smaller than that of B, use exists rather than in.
Exists select… from table where exists (subquery)
This syntax can be interpreted as: put the data of the primary query into the sub-query for conditional verification, and determine whether the data of the primary query can be preserved according to the verification result (TRUE or FALSE).
1, exists (subquery) only returns true or false, so select * in a subquery can also be select 1 or otherwise
2. The actual execution process of a subquery may be worrified rather than compared item by item in our understanding. If there is a concern about efficiency, a practical test can be carried out to determine whether there is an efficiency problem
3. Subqueries in exists can also be replaced by conditional expressions, other subqueries or joins. What kind of optimal query needs specific analysis
The order by optimization
ORDER BY clause, try to use Index rather than FileSort
Create table + insert data + create index example:
use day_07;
create table tblA(
#id int primary key not null auto_increment,
age int,
birth TIMESTAMP not null
);
insert into tblA(age, birth) VALUES (22, NOW());
insert into tblA(age, birth) VALUES (23, NOW());
insert into tblA(age, birth) VALUES (24, NOW());
create index idx_A_ageBirth ON tblA(age, birth);
select * from tblA;
Copy the code
MySQL supports two kinds of sorting methods, FileSort and Index, Index is efficient.
It means that MySQL scans the index itself for sorting. The FileSort method is inefficient.
Order by (Index)
1, The order by statement uses the left-front index, which is the best left prefix rule
2. Use the combination of where clause and order by clause to meet the left front of the index, which also conforms to the best left prefix rule
So, if possible, do the sorting on the index column, following the best left prefix for the index
FileSort two kinds of sorting
If not on indexed columns, Filesort has two algorithms: double-way sort and single-way sort
Double sort: MySQL4.1 used double sort, literally scanning the disk twice to get the data. Read the row pointer and order Derby columns, sort them, then scan the sorted list and re-read the corresponding data transfer from the list according to the values in the list. Disk IO is very time consuming, fetching sort fields from disk, sorting them in buffer, and fetching other fields from disk. So this is double disk scan, double sort.
Fetching a batch of data requires two scans of the disk, which takes a lot of time, so after mysql4.1, there is a second improved algorithm, which is single-way sorting.
Single-way sort: Read from disk query requires all columns, according to the orderby columns to sort them in buffer, then scanning the sorted list of output, its efficiency is quicker, avoids the second reading data, and make random IO order IO, but it will use more space, because it put each line were kept in memory. But this situation is afraid to encounter a take the data to take the situation, then need to take multiple data, that is, multiple IO, as well as the previous double-way algorithm!
Two sorts of pits
In sort_buffer, method B takes up A lot more space than method A, because method B takes out all fields, so the total size of the data taken out may exceed the capacity of sort_buffer, so only sort_buffer can be taken each time. Sort_buffer size sort_buffer size sort_buffer size sort_buffer size So many IO, originally wanted to save an IO operation, but led to a large number of O operations, but the gain is not worth the loss
Order by optimization policy
Optimization strategy: Increase sort_BUFFer_SIZE parameter and max_LENGTH_FOR_SORT_DATA parameter
Select * from ‘select *’; select * from ‘select *’; The impact here is:
- When the Query field combined size less than max_ length_for_ sort the data and sort field is not TEXT | BLOB, will use the improved algorithm, single channel sorting, or with the old algorithm multiplex sort one by one
- Both algorithms may exceed the size of sort_buffer. When this happens, TMP files will be created for merge sort, resulting in multiple IO, but the risk of using single-path sort is higher, so increase sort_buffer_size
2. Try increasing sort_buffer_size
Regardless of which algorithm is used, increasing this parameter will improve efficiency, of course, depending on the system’s capacity, since this parameter is per-process
3, try to increase max_ LENGTH_FOR_sort_data
Increasing this parameter increases the probability of using the improved algorithm. However, if set too high, the probability of total data capacity exceeding sort_buffer_size increases, with obvious symptoms of high disk I/O activity and low processor utilization.
Order by optimization summary
Mysql can use the same index for both sorts and queries
KEY a_b_c (a, b, c)
Order by can use the left-most prefix of the index
order by a
order by a, b
order by a, b, c
order by a DESC, b DESC, c DESC
Copy the codeIf WHERE is defined as a constant using the left-most prefix of the index, then order by can use the index
where a = const order by b, c
where a = const and b = const order by c
where a = const and b > const order by c
Copy the codeYou cannot sort by index
Order by a ASC, b DESC, c DESC # where g = const order by b, Where a = const order by d # d where a = const order by d # d Order by b, c # For sorting, multiple equality conditions are range queriesCopy the codeGroup by optimization
Group by is the same as order BY. Group by is the same as order BY. Group by is the same as order by. When index columns are unavailable, increase the max_LENGTH_FOR_sort_DATA parameter or sort_BUFFer_SIZE parameter. Where having is higher than where having can be qualified.