Use MySQL to analyze SQL time-consuming issues

Slow log query

MySQL's slow log query is a log record provided by MySQL. It records statements whose response time exceeds the threshold in MySql. Specifically, SQL that takes longer than the long_query_time value will be recorded in the slow log. The default time of long_query_time is 10, which means running more than 10 statements.

For example, if a SQL statement takes more than 5 seconds to execute, even if it is a slow SQL statement, MySQL will record the SQL statement that exceeds 5 seconds. We can conduct a comprehensive analysis in conjunction with explain.

By default, the MySQL database does not enable slow query logs, and we need to set this parameter manually. Of course, if it is not required for tuning, it is generally not recommended to enable this parameter, because slow logging will more or less have a certain performance impact.

Whether it is enabled and set

#查看是否开启show variables like '%slow_query_log%';#开启set global slow_query_log = 1;

Using set global slow_query_log=1 to enable the slow query log will only take effect on the current database and will become invalid if MySQL is restarted. If you want it to take effect permanently, you must modify the configuration file my.cnf.

Note that after setting the slow query threshold time, you may not see the value change, that is, it does not take effect. In this case, you need to reconnect or open a new session to see the modified value.

show variables like '%long_query_time%'

Or you can use the following command without reopening the connection:

show variables like '%long_query_time%'

Which sql will be recorded by slow log, which is controlled by long_query_time. By default, the value of long_query_time is 10 seconds. , command:

show variables like '%long_query_time%';

If the running time is exactly equal to long_query_time, it will not be recorded. In other words, in mysql, it is judged to be greater than long_query_time, not greater than or equal to.

You can use the following statement to do a test

SELECT sleep(4)

If you set long_query_time to 3 seconds, then this statement will be recorded.

View the number of slow logs

show global status like '%slow_queries%'

Log analysis tool mysqldumpslow

In the production environment, if you want to manually analyze the log, find, Analyzing SQL is obviously a laborious task. MySql provides the log analysis tool mysqldumpslow.

For example:

#得到返回记录集最多的10个SQL
Mysqldumpslow –s r –t 10 D:\Program Files\mysql\data\DESKTOP-VN2D5OU-slow.log#得到访问次数最多的10个SQL
Mysqldumpslow –s c –t 10 D:\Program Files\mysql\data\DESKTOP-VN2D5OU-slow.log#得到按照时间排序的前10条里面含有左连接的查询
Mysqldumpslow –s t –t 10 –g “left join” D:\Program Files\mysql\data\DESKTOP-VN2D5OU-slow.log#另外建议在使用这些命令时结合|和more使用，否则可能出现爆破情况
Mysqldumpslow –s r –t 10 D:\Program Files\mysql\data\DESKTOP-VN2D5OU-slow.log|more

参数含义
s: 表示按照何种方式排序
c：访问次数
l：锁定时间
r：返回记录
t：查询时间
al：平均锁定时间
t：返回前面多少条的数据
g：后面搭配一个正则表达式

Use show profile for sql analysis

The show profile command can analyze the resource consumption of statement execution in the current session. Used to find SQL time-consuming bottlenecks. It is turned off by default and saves the results of the last 15 runs.

Check whether it is turned on (show variables like ‘profiling’;)
Turn on the function (set profiling = on;)

After turning it on, you can record the subsequent running of sql. Then use show profiles to view the results:

Further analyze the execution of a certain SQL statement through the command (show profile cpu, block io for query 3;), for example, the following analysis 3 No. SQL situation.

Some parameters behind Show profile:

• All: Display all overhead information

• Block io: Display block IO related overhead

• Context switches: Context switch related overhead

• Cpu: Display cpu related overhead

• Memory: Display memory-related overhead

• Source: Display overhead information related to source_function, source_file, source_line

Global query log

(Never turn it on in a production environment to view all executed SQL statements)

Set command:

set global general_log = 1;#以表的形式输出set global log_output = ‘TABLE’

After that, the SQL executed by mysql The statement will be recorded to the mysql.genearl_log table, which can be viewed with the following command:

select * from mysql.general_log;

It can also be configured in the configuration file and set as follows:

#开启General_log = 1#记录日志文件的路径General_log_file = D://path/logfile#输出格式Log_output=file

The above is the detailed content of Use MySQL to analyze SQL time-consuming issues. For more information, please follow other related articles on the PHP Chinese website!