MySQL 慢查询日志脚本

1、查看状态

# 查看慢查询功能状态
show variables like 'slow_query_log'; 
# 加个%可以查看相关的功能状态
show variables like 'slow_query_log%';

2、临时开启慢查询功能，当 MySQL Server 重启时，以上修改全部失效并恢复原状。

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/kalacloud-slow.log';
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET SESSION long_query_time = 1;
SET SESSION min_examined_row_limit = 100;
# SET GLOBAL slow_query_log ：全局开启慢查询功能。
# SET GLOBAL slow_query_log_file ：指定慢查询日志存储文件的地址和文件名。
# SET GLOBAL log_queries_not_using_indexes：无论是否超时，未被索引的记录也会记录下来。
# SET SESSION long_query_time：慢查询阈值（秒），SQL 执行超过这个阈值将被记录在日志中。
# SET SESSION min_examined_row_limit：慢查询仅记录扫描行数大于此参数的 SQL。

3、将慢查询设置写入 MySQL 配置文件，永久生效。

[mysqld]

slow-query-log = 1
slow-query-log-file = /var/log/mysql/localhost-slow.log
long_query_time = 1
log-queries-not-using-indexes

打开配置文件，把上面的的代码写在 [mysqld] 的下面即可。

sudo systemctl restart mysql

重启 MySQL Server 服务，使刚刚修改的配置文件生效。

4、使用 mysqldumpslow 工具对慢查询日志进行分析
把相同的 SQL 归为一类，并统计出归类项的执行次数和每次执行的耗时等一系列对应的情况。针对这条记录列出了对应的总量和平均量的记录

sudo mysqldumpslow -s at /var/log/mysql/kalacloud-slow.log

# 平均执行时长最长的前 10 条 SQL 代码
mysqldumpslow -s at -t 10 kalacloud-slow.log
# 平均锁定时间最长的前 10 条 SQL 代码
mysqldumpslow -s al -t 10 kalacloud-slow.log
# 执行次数最多的前 10 条 SQL 代码
mysqldumpslow -s c -t 10 kalacloud-slow.log
# 显示所有 user 表相关的 SQL 代码的具体值
mysqldumpslow -a -g 'user' kalacloud-slow.log
# 直接显示 SQL 代码的情况
mysqldumpslow -a kalacloud-slow.log

常用的参数讲解：

-s

• al：平均锁定时间

• at：平均查询时间 [默认]

• ar：平均返回记录时间

• c：count 总执行次数

• l：锁定时间

• r：返回记录

• t：查询时间

-t：返回前 N 条的数据

-g：可写正则表达，类似于 grep 命令，过滤出需要的信息。如，只查询 X 表的慢查询记录。

-r：rows sent 总返回行数。

5、Profilling - MySQL 性能分析工具
通过 Profilling 工具获取一条 SQL 语句在执行过程中对各种资源消耗的细节。

# 进入 MySQL Server 后，执行以下代码，启动 Profilling
SET SESSION profiling = 1;
# 检查 profiling 的状态，0 表示未开启，1 表示已开启
SELECT @@profiling;
# 执行需要定位问题的 SQL 语句
SELECT * FROM users WHERE name = 'Jack Ma';
# 查看 SQL 语句状态，会显示一个将 Query_ID 链接到 SQL 语句的表
SHOW PROFILES;
# 执行以下 SQL 代码，将 [# Query_ID] 替换为我们要分析的 SQL 代码Query_ID的编号
SHOW PROFILE CPU, BLOCK IO FOR QUERY [# Query_ID];