MySQL-SQL query optimization

MySQL-SQL query optimization

Slow query log slow_query_log starts and stops recording the slow query log. Slow_query_log_file is not started by default. Slow_query_log_file specifies the storage path and file of the slow query log. It is saved in the MySQL data directory by default. long_query_time specifies the threshold for recording the SQL execution time of the slow query log. The default value is 10 seconds, usually changed to 0.001 seconds, which is 1 millisecond, may be more appropriate log_queries_not_using_indexes Whether to record the SQL setting of unused indexes and enable slow query

set global slow_query_log=on
set global long_query_time=0.001
set global slow_query_log_file='/var/lib/mysql/slow.log'
Slow query analysis tool

Official built-in mysqldumpslow

mysqldumpslow -sr -t 10 slow.log

Parameter meaning: -s order (c, t, l, r, at, al, ar) Specify the sorting method to output the results c: total number of times t: total time l: lock time r: total data rows at, al , ar: the average number of t,l,r, for example: at = total time/total number of times -t top Specify the top few as the result

Recommend to use pt-query-digest

Official installation address https://www.percona.com/doc/percona-toolkit/LATEST/installation.html

brew install percona-toolkit

pt-query-digest --explain h=127.0.0.1, u=root, p=p@ssWord slow-mysql.log

Real-time access to SQL with performance problems

Reference: https://cloud.tencent.com/developer/article/1437146 MySQL-SQL query optimization-Cloud + Community-Tencent Cloud