How to analyze MySQL slow SQL?

Time:2021-5-4

Content abstract:

  • Enable slow query log to capture slow SQL
  • Using explain to analyze slow SQL
  • Using show profile to query SQL execution details
  • Common SQL statement optimization

1、 Enable slow query log to capture slow SQL

① To query whether MySQL enables slow log capture:

SHOW VARIABLES LIKE '%slow_query_log%';

If not, open:

SET GLOBAL slow_query_log=1;

②   To view the time threshold of slow query:

SHOW GLOBAL VARIABLES LIKE '%long_query_time%';

You can adjust the time according to the actual situation

SET GLOBAL long_query_time=2;

③ How many SQL queries exceed the threshold of slow query time:

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

④ Use the MySQL provided log analysis tool mysqldumpslow to get the slow SQL

give an example:

–Get up to 10 SQL in slow log

. / mysqldumpslow - s R - t 10 / path / to / slow log file

–Get the top 10 queries with left join sorted by time

mysqldumpslow  - s t -t 10  - g "left join"   Slow log file

More usage can be found by using:. / mysqldumpslow — help

2、 Using explain to analyze slow SQL

give an example:

EXPLAIN SELECT a.username FROM tb_admin a LEFT JOIN tb_group p ON a.groupId = p.id WHERE a.username = 'xiaophai' LIMIT 1

1、id

The sequence number of a select query, which contains a set of numbers indicating the order in which the select statement or operation table is executed in the query
There are three cases

① The ID is the same, and the execution order is from top to bottom
② ID is different. If it is a subquery, the ID sequence number will increase. The higher the ID value is, the higher the priority is, and the more it is executed first
③ There are both the same and different. If the IDs are the same, they are considered as a group, and the execution order is from top to bottom; In all groups, the higher the ID value, the higher the priority, and the earlier the execution.

2、select_type

SIMPLE:     Simple select query, which does not contain subquery or union
     

Primary: the query contains any complex sub part, the outermost query
    

Subquery: the subquery part contained in select or where
    

Derived: the sub queries contained in from are marked as derived. MySQL will recursively execute these sub queries and put the results into the temporary table
    

Union: if union appears in the second select, it will be marked as union. If union is included in the subquery of the from clause, the outer subquery will be marked as developed
    

Union result: select to get result from Union table

3、table
    

Show which table this row of data is about

4、type
    

Type shows the access type, which is an important indicator. The result values from the best to the worst are as follows:
 

system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL

Generally speaking, it is necessary to ensure that the query reaches at least the range level, preferably Ref.

System: the table has only one row of records (equal to the system table). This is a special case of const type, which usually does not appear
    

Const: if it is found by index, const is used to compare the primary key index or unique index. Because only one row of data can be matched, it is very fast. If you put the primary key in the where list, MySQL can convert the query to a constant
    

eq_ Ref: unique index scan. For each index key, only one record in the table matches it. Common in primary key or unique index scanning
    

Ref: a non unique index scan that returns all rows that match a single value. In essence, it is also an index access, which returns all the rows that match a single value. However, it may find multiple rows that meet the conditions, so it should belong to the mixture of search and scan
    

Range: only the rows in the given range are retrieved, and one index is used to select the rows. The key column shows which index is used. Generally, queries such as between, <, >, in appear in your where statement. This kind of range scanning index is better than full table scanning, because it only needs to start at one point of the reduction and end at another point. It does not need to scan all the indexes
    

Index: full index scan. The difference between index and all is that the index type only traverses the index tree, which is usually faster than all because the index file is usually smaller than the data file( That is to say, although all and index read the whole table, index reads from the index, and all reads from the hard disk.)

All: full table scan, traverse the whole table to get the matching rows

5、possible_keys
    

Displays the indexes, one or more, that may be applied to this table. If there is an index on the field involved in the query, the index will be listed, but not necessarily used by the query

6、key
    

The actual index used. If NULL, no index is used.  
    

If the overlay index appears in the query, it will only appear in the key list.

7、key_len
    

Represents the number of bytes used in the index, which can be used to calculate the length of the index used in the query. The shorter the length, the better without loss of accuracy.
    

key_ The value displayed in len is the maximum possible length of the index field, not the actual length, that is, key_ Len is calculated according to the table definition, not retrieved from the table.

8、ref
    

Displays which columns of the index are used and which columns or constants are used to find values on the index columns.

9、rows
    

According to the table statistics and index selection, the number of rows that need to be read to find the required records is roughly estimated.

10、Extra
    

Contains additional information that is not suitable for display in other columns but is important:
    

① Using filesort: it indicates that MySQL will sort the data by an external index instead of reading it by the index order in the table. The sort operation that cannot be completed by index in MySQL is called “file sort”
    

② Using temporary: temporary tables are used to save intermediate results, and MySQL uses temporary tables when sorting query results. It is commonly used in sorting order by and grouping query group by
    

③ Using index: indicates that the covering index is used in the corresponding select operation to avoid accessing the data rows of the table, which is efficient. If using where appears at the same time, it indicates that the index is used to search the key value of the index; If using where does not appear at the same time, it indicates that the index is used to read data instead of performing a search action. Covering index: understanding mode 1: the data column of select can be read only from the index, without reading the data row. MySQL can use the index to return the fields in the select list, without having to read the data file again according to the index, In other words, the query column should be covered by the built index. Understanding mode 2: index is an efficient way to find rows, but general databases can also use index to find the data of a column, so it does not have to read the whole row. After all, index leaf nodes store the data they index; When you can get the data you want by reading the index, you don’t need to read the row. An index that contains (covers) the data that meets the query result is called an overlay index. Note: if you want to use an overlay index, you must pay attention to only the columns you need in the select list, not select *, Because if all fields are indexed together, the index file will be too large and the query performance will be degraded
   

④ The value of the impossible where: where clause is always false and cannot be used to get any tuples
    

⑤ Select tables optimized away: the min / max operation is optimized based on index without group by clause, or the count (*) operation is optimized for MyISAM storage engine. The calculation does not have to wait for the execution phase, and the optimization is completed at the generation phase of query execution plan
    

⑥ Distinct: optimize the distinct operation, and stop the operation of finding the same value after finding the first matching ancestor

3、 Using show profile to query SQL execution details

Show profile is provided by mysql, which can be used to analyze the resource consumption of statement execution in the current session, and can be used for SQL tuning measurement

Analysis steps

1. View status: Show variables like ‘profiling’;

2. On: set profiling = on;

3. View results: Show profiles;

4. Diagnostic SQL: Show profile CPU, block io for query the previous SQL number;

All: displays all overhead information

Block IO: displays IO related overhead

Context switches: display context switch related overhead

CPU: displays CPU related overhead

IPC: display the overhead related to sending and receiving

Memory: displays memory related overhead

Page faults: display page error related overhead

Source: display and source_ function,Source_ file,Source_ Line related costs

Swap: display the cost related to the number of exchanges
     

Pay attention to (optimize in case of these situations)
    

Converting heap to MyISAM: the query result is too large, and there is not enough memory to move it to disk

Creating TMP table: creates a temporary table

Copying to TMP table on disk: copy the temporary table in memory to disk

locked

4、 Common SQL statement optimization

1. Try to avoid using or to join conditions in where sentences, otherwise it will lead to abandoning index and scanning the whole table.

select id from user where username='15623695987' or mobile='15623695987';

You can query as follows:

select id from user where username='15623695987' union all select id from user where mobile='15623695987';

2. Use like query correctly.

%XX% query will result in index not being used:

select id from user where username like '%test%'

Using index correctly:

select id from user where username like 'test%'

3. Try to avoid the expression operation on the field in the where sentence

Wrong way:

select id from user where score/2 =100;

It should be changed to:

select id from user where score =100*2;

4. If you confirm the number of query results, you should add limit as much as possible

select id from user where username='test' limit 1;

5. You don’t need to use implicit conversion

Error example:

select id from user where mobile=15689764359 limit 1;
select username from user where id='15' limit 1;

Right way:

select id from user where mobile='15689764359' limit 1;
select username from user where id=15 limit 1;

6. Using compound index correctly

Example: user table, index   idx_ username(username,group_ id)

Can use index:

select id from user where username='test' and group_id=1;
select id from user where username='test'
select id from user where group_id=1 and username='test'

Cannot use index:

select id from user where group_id=1;

Summary: do you use index and IDX_ username(username,group_ The order of these two fields is related to the sequence

7. If you use join, try to use small table join large table

8. Using exists and in correctly

① in

select `user`.id,`user`.username from `user` where `user`.id in (select `order`.user_id from `order`)

② exists

select `user`.id,`user`.username from `user` where exists (select`order`.user_id from `order` where `user`.id = `order`.user_id)

Application scenario reference: in should be used when the table in the main query is large and has an index if the result set records obtained by the sub query are less; otherwise, exist should be used when the table in the sub query is large and has an index if the main query records in the outer layer are less