到底是哪条SQL拖慢了你的数据库？-技术圈

到底是哪条SQL拖慢了你的数据库？

欢少的成长之路

共 4071字，需浏览 9分钟

· 2021-09-30

开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

核心参数

以下就是我们要设置的核心参数

ps: 管理语句包括：ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, REPAIR TABLE.

确认单点还是集群

第一步要做的就是确认单点还是集群
以下是集群查询结果，可以看到是3节点集群，单点的返回结果是空的

show global status like 'wsrep_cluster_size';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| wsrep_cluster_size | 3 |
+-----------------------+-------+
1 rows in set (0.00 sec)

根据不同的集群有三种情况：

如果是主从模式集群，只需要在主节点上操作，同时打开log_slow_slave_statements开关就可以了。
如果是多主，则每个主节点都要操作一次。
如果是单点，只需要操作一次。

修改配置文件开启慢查询（要重启）

$ vim /etc/my.cnf.d/server.cnf

在mysqld节加上以下配置后重启即可

[mysqld]
slow_query_log = ON
slow_query_log_file = /data/mariadb/data/slow.log
long_query_time = 1
log_slow_slave_statements = ON
log_slow_admin_statements = ON

免重启方式开启慢查询（重启失效）

这种方式只需要登陆Mariadb输入以下命令即可
注：使用命令 set global 修改后，，需要重新连接或新开一个会话才能看到修改值。

set global log_slow_slave_statements=1;
set global log_slow_admin_statements=1;
set global slow_query_log=1;
set global slow_query_log_file='/data/mariadb/data/slow.log';
set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

查询是否修改成功

重启mariadb，然后输入命令查询，如果和以下结果一样，就是成功打开了

show variables like '%slow%';
+---------------------------+--------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------+--------------------------------------------------------------------------------------------------------------+
| log_slow_admin_statements | ON |
| log_slow_slave_statements | ON |
| slow_query_log | ON |
| slow_query_log_file | /data/mariadb/data/slow.log |
+---------------------------+--------------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)
show global variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

测试是否开启成功

执行查询语句，休眠4秒，超过我们定的1秒阀值

select sleep(4);
+----------+
| sleep(4) |
+----------+
| 0 |
+----------+
1 row in set (4.00 sec)

查看慢查询日志，可以看到我们刚刚执行的select sleep(4);

$ tail slow.log 
/usr/local/mariadb/bin/mysqld, Version: 10.1.20-MariaDB (Source distribution). started with:
Tcp port: 3306 Unix socket: /tmp/mariadb.sock
Time Id Command Argument
# Time: 190528 13:25:49
# User@Host: root[root] @ localhost []
# Thread_id: 59011860 Schema: QC_hit: No
# Query_time: 4.000110 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
# Rows_affected: 0
SET timestamp=1559021149;
select sleep(4);

分析慢查询语句

自带的日志分析工具mysqldumpslow：
比如:
得到返回记录集最多的10个SQL。

mysqldumpslow -s r -t 10 /database/mysql/slow.log

得到访问次数最多的10个SQL

mysqldumpslow -s c -t 10 /database/mysql/slow.log

得到按照时间排序的前10条里面含有左连接的查询语句。

mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow.log

另外建议在使用这些命令时结合 | 和more 或者直接 > file.log 重定向到新文件中，否则有可能出现刷屏的情况。

mysqldumpslow -s r -t 20 /mysqldata/mysql/slow.log | more

着重关注哪些sql?

查询次数多且每次查询占用时间长的sql
IO大的SQL
未命中索引的SQL

缺点：慢查询日志是在查询结束后才记录，故正在执行的慢SQL并不能被定位到，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等等，可以实时地查看SQL的执行情况。