1. 使用 show processlist 定位，查询正在执行的慢查询
可以显示哪些线程正在运行，如果有 process 权限，则可以看到所有线程；否则，只能看到当前会话的线程。
注：show full processlist 可以看到语句的全部内容，否则只能显示每个语句的前 100 个字符
2. 根据慢查询日志定位
MySQL 的慢查询日志记录的内容是：在 MySQL 中响应时间超过参数 long_query_time（单位秒，默认值为10）设置的值，并且扫描记录数不小于 min_examined_row_limit（默认值 0）的语句
MySQL 慢查询的相关参数解释：
- slow_query_log：是否开启慢查询日志，1 表示开启，0 表示关闭。默认是 FALSE
- slow_query_log_file：5.6 以上版本，MySQL 数据库慢查询日志存储路径。
- long_query_time：慢查询阈值，当查询时间多于设定的阈值时，记录日志。默认 10.000000 秒，小数部分是微秒。
- log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）
- log_output：日志存储方式，默认值为 FILE；TABLE 表示将日志存入数据库，日志信息会存入 mysql.slow_log 表中。也可以配置为
FILE,TABLE。
慢查询日志配置：
1
2
3
4
5
| show variables like 'slow_query_log'; // 查看慢查询日志是否打开
set global slow_query_log = 1; // 打开慢查询日志，只对当前数据库有效，MySQL 重启会失效。可以配置 my.cnf 永久有效
如何修改 my.cnf 以永久打开慢查询日志？增加或修改参数
slow_query_log = 1
slow_query_log_file = /tmp/mysql_slow.log
|
当开启慢查询后，由 long_query_time 确认哪些 SQL 会记录到日志中。注意：想要 set global long_query_time = 4; 修改慢查询时间阈值，需要重新连接或者重新开一个会话才能看到修改值。
1
2
3
4
5
6
7
8
9
10
11
| mysql> select sleep(12); // 睡眠 12 秒
ash-5.0# cat /var/lib/mysql/691db01cc7d0-slow.log // 查看慢查询日志
mysqld, Version: 5.7.36 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
# Time: 2022-05-15T02:41:00.809608Z
# User@Host: root[root] @ [192.168.10.1] Id: 58
# Query_time: 12.000204 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1652582460;
select sleep(12);
|
可以指定日志的存储方式，将日志记录到数据库中
1
2
3
4
5
6
7
8
| mysql> show variables like 'log_output'; // 查看日志的存储方式
mysql> set global log_output = 'TABLE'; // 设置存储到数据库
mysql> select * from mysql.slow_log; // 从数据库表中查看慢查询日志
+----------------------------+------------------------------+-----------------+-----------------+-----------+---------------+----+----------------+-----------+-----------+------------------+-----------+
| start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text | thread_id |
+----------------------------+------------------------------+-----------------+-----------------+-----------+---------------+----+----------------+-----------+-----------+------------------+-----------+
| 2022-05-15 10:47:07.653984 | root[root] @ [192.168.10.1] | 00:00:13.000243 | 00:00:00.000000 | 1 | 0 | | 0 | 0 | 0 | select sleep(13) | 58 |
+----------------------------+------------------------------+-----------------+-----------------+-----------+---------------+----+----------------+-----------+-----------+------------------+-----------+
|
未使用索引的语句也可以被记录到慢查询日志中
1
2
3
4
5
6
7
8
| mysql> show variables like 'log_queries_not_using_indexes';
mysql> set global log_queries_not_using_indexes = 1;
mysql> select * from mysql.slow_log;
+----------------------------+------------------------------+-----------------+-----------------+-----------+---------------+------+----------------+-----------+-----------+------------------------------------+-----------+
| start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text | thread_id |
+----------------------------+------------------------------+-----------------+-----------------+-----------+---------------+------+----------------+-----------+-----------+------------------------------------+-----------+
| 2022-05-15 10:51:01.844616 | root[root] @ [192.168.10.1] | 00:00:00.000206 | 00:00:00.000076 | 1 | 1 | test | 0 | 0 | 0 | select * from mysql.slow_log | 58 |
|
慢管理语句例如 analyze table 和 alter table 等也可以设置记录到慢查询
1
| show variables like 'log_slow_admin_statements';
|
3. 日志分析工具 mysqldumpslow
如果要手工分析日志，查找、分析 SQL，MySQL 提供了日志分析工具 mysqldumpslow
- -s：表示按照何种方式排序
- c 访问计数
- l 锁定时间
- r 返回时间
- t 查询时间
- al 平均锁定时间
- ar 平均返回记录数
- at 平均查询时间
- -t：是 top n 的意思，即为返回前面多少条的数据
1
2
3
4
5
6
7
8
9
10
11
| 得到返回记录集最多的10个SQL。
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log
得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log
得到按照时间排序的前10条里面含有左连接的查询语句。
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log
另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。
mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more
|