【MYSQL高级】Mysql找出执行慢的SQL【慢查询日志使用与分析】
分析慢SQL的步骤
1.慢查询的开启并捕获：开启慢查询日志，设置阈值，比如超过5秒钟的就是慢SQL，至少跑1天，看看生产的慢SQL情况，并将它抓取出来
2.explain + 慢SQL分析
3.show Profile。（比explain还要详细，可以查询SQL在MySQL数据库中的执行细节和生命周期情况）
4.运维经理 OR DBA，进行MySQL数据库服务器的参数调优。（后端程序员没有这个权限）
慢查询日志（定位慢sql）
基本介绍
慢查询日志是什么？
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
long_query_time的默认值为10，意思是运行10秒以上的语句- 由慢查询日志来查看哪些SQL超出了我们的最大忍耐时间值，比如一条SQL执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒钟的SQL，结合之前
explain进行全面分析
特别说明
**默认情况下，MySQL数据库没有开启慢查询日志，**需要我们手动来设置这个参数。
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。
查看慢查询日志是否开以及如何开启
- 查看慢查询日志是否开启：
SHOW VARIABLES LIKE '%slow_query_log%';。 - 开启慢查询日志：
SET GLOBAL slow_query_log = 1;。使用该方法开启MySQL的慢查询日志只对当前数据库生效，如果MySQL重启后会失效。
1 -- 指定数据库 2 mysql> use mysql; 3 Database changed 4 -- 查看慢查询日志是否开启 5 mysql> show variables like '%slow_query_log%'; 6 +---------------------+-------------------------------+ 7 | Variable_name | Value | 8 +---------------------+-------------------------------+ 9 | slow_query_log | ON | 10 | slow_query_log_file | /var/lib/mysql/mysql-slow.log | 11 +---------------------+-------------------------------+ 12 2 rows in set (0.00 sec) 13 -- 开启慢查询日志 14 mysql> set global slow_query_log = 1; 15 Query OK, 0 rows affected (0.00 sec)
如果要使慢查询日志永久开启（不推荐，浪费性能），需要修改my.cnf文件，在[mysqld]下增加修改参数。
1 # my.cnf 2 [mysqld] 3 # 开启慢查询 4 slow_query_log=ON 5 # 指定存储慢查询日志的文件。如果这个文件不存在，会自动创建 6 slow_query_log_file=/var/lib/mysql/slow.log
设置慢SQL的时间阈值
查看阈值
时间阈值是由参数long_query_time控制的，默认情况下long_query_time的值为10秒。
MySQL中查看long_query_time的时间：SHOW VARIABLES LIKE 'long_query_time%';。
1 mysql> SHOW VARIABLES LIKE 'long_query_time%'; 2 +-----------------+----------+ 3 | Variable_name | Value | 4 +-----------------+----------+ 5 | long_query_time | 1.000000 | 6 +-----------------+----------+ 7 1 row in set (0.00 sec)
注意：是超过阈值才会被记录，等于不会被记录
设置阈值
1 -- 设置阈值 2 mysql> set global long_query_time=3; 3 Query OK, 0 rows affected (0.00 sec) 4 -- 可以发现设置没有成功 5 mysql> SHOW VARIABLES LIKE 'long_query_time%'; 6 +-----------------+----------+ 7 | Variable_name | Value | 8 +-----------------+----------+ 9 | long_query_time | 1.000000 | 10 +-----------------+----------+ 11 1 row in set (0.00 sec)
也可以不重启连接，使用如下命令直接查看
1 show global variables like 'long_query_time';
也直接在my.cnf配置文件中修改
[mysqld] long_query_time=1
查询慢查询日志文件中的总记录条数
日志分析
模拟慢查询
1 mysql> select sleep(3); 2 +----------+ 3 | sleep(3) | 4 +----------+ 5 | 0 | 6 +----------+ 7 1 row in set (3.00 sec)
- use advanced_mysql_learning：使用的数据库
- Query_time：实际查询时间，单位是秒
- Lock_time：锁时间
- select sleep(4)：超时的语句
常见问题
在对慢查询进行查看的时候发现时间不对，正好与系统时间相差8个小时。可以查看mysql如下环境变量：
1 mysql> show variables like 'log_timestamps'; 2 +----------------+-------+ 3 | Variable_name | Value | 4 +----------------+-------+ 5 | log_timestamps | UTC | 6 +----------------+-------+ 7 1 row in set (0.00 sec)
UTC大家都知道是世界统一时间，而我现在的系统时间是东八区，比UTC早了8个小时，这就对上了。
修改参数就可以解决问题。
1 mysql> set global log_timestamps = system; 2 Query OK, 0 rows affected (0.00 sec) 3 4 mysql> show global variables like 'log_timestamps'; 5 +----------------+--------+ 6 | Variable_name | Value | 7 +----------------+--------+ 8 | log_timestamps | SYSTEM | 9 +----------------+--------+ 10 1 row in set (0.00 sec)
日志分析工具mysqldumpslow
在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。比如有100条慢sql，如何快速找出出现频次最高的前5条。
查看mysqldumpslow的帮助文档
在Linux命令行窗口执行mysqldumpslow --help
