MySQL optimization (3): Slow SQL analysis

For slow SQL optimizations, you can generally follow the following steps:

1, turn on slow query log, set more than a few seconds for slow SQL, crawl slow SQL

2, through explain to slow SQL analysis (focus)

3, Show profile query SQL in the MySQL server execution details and life cycle situation (focus)

4. Tuning the parameters of the database server

one, slow query log

1. Set Slow Query

(1) Set Open:SETGLOBAL Slow_query_log= 1; #默认未开启, turn on will affect performance, MySQL restart will fail (2) Check if open: Show VARIABLES like '%slow_query_log%'; (3) Set the threshold value:SETGLOBAL Long_query_time=3; (4) View threshold: Show "GLOBAL" VARIABLES like 'long_query_time%'; #重连或新开一个会话才能看到修改值 (5) by modifying the configuration file my.cnf to take effect permanently,[mysqld]under Configuration:[mysqld]Slow_query_log= 1; #开启 Slow_query_log_file=/var/Lib/Mysql/Atguigu-Slow.Log#慢日志地址, the default file name Host_name-Slow.LogLong_query_time=3; #运行时间超过该值的SQL会被记录, the default value>TenLog_output=FILE　　　　　　　　　　　

2. Get Slow SQL information

 like ' %slow_queries% ';

Analog statement: Select Sleep (4);

View logs: Cat Atguigu-slow.log

3, with log analysis tool Mysqldumpslow

Mysqldumpslow-S R-TTen /var/Lib/Mysql/Atguigu-Slow.Log#得到返回记录集最多的10个SQLmysqldumpslow-S C-TTen /var/Lib/Mysql/Atguigu-Slow.Log#得到访问次数最多的10个SQLmysqldumpslow-S T-TTen -G " Left JOIN"/var/Lib/Mysql/Atguigu-Slow.Log#得到按照时间排序的前10条里面含有左连接的查询语句mysqldumpslow-S R-TTen /var/Lib/Mysql/Atguigu-Slow.Log |More #结合|More use, to prevent the explosion screen condition S: means to sort by how C: Number of visits L: Lock time R: Return record T: Query time AL: Average lock time ar: Average return record at: Average query time t: Returns the data for the first number of bars G: Back with a regular matching pattern, Case insensitive

second, explain analysis slow SQL

It is important to analyze slow SQL through explain , a separate chapter, MySQL optimization (4): Explain analysis.

Third, Show profile analysis of Slow SQL

Show profile is also a means of analyzing slow SQL, but it can get more detailed information than explain, can analyze the resource consumption of statement execution in the current session, and it is also important to get the time-consuming, the equivalent of the list of execution times for this SQL throughout the life cycle.

1, the default is off. When turned on, the last 15 run results are saved in the background, and the results are viewed through the show Profile command.

Open:set=on like'profiling%';

2. The time-consuming of SQL can be viewed through show profile

3, through the query_id can get specific SQL from connection-service-engine-storage four layer structure complete life cycle time

　　

available Parameters Type:  All   #显示所有的开销信息BLOCK IO #显示块IO相关开销CONTEXT switches #上下文切换相关开销CPU     #显示CPU相关开销信息IPC     #显示发送和接收相关开销信息MEMORY # Displays memory-related overhead information page faults #显示页面错误相关开销信息SOURCE #显示和Source_function, source_file,source_line related overhead information swaps #显示交换次数相关开销的信息 

4. When the four status shows a problem, group by may create a temporary table

#危险状态:
 to MyISAM   Table  to table on disk #把内存中临时表复制到磁盘, Danger!!!            Locked

　　

iv. Global Query log

Use only in test environments, not in production environments , and log all used SQL

1, open:

 open: SQL will be logged to the MySQL library General_ Log table  set  global General_log=  1  ;  set  global Log_output=   " table  "   =  1   #开启 general_log_file   =/ Path/ logfile #记录日志文件的路径 log_output  =  file  #输出格式 

2. View

Select *  from Mysql.general_log;

MySQL optimization (3): Slow SQL analysis