[五]Mysql慢查询优化

原创已于 2025-05-07 13:26:22 修改 · 277 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

于 2025-05-07 13:25:47 首次发布

数据库专栏收录该内容

8 篇文章

订阅专栏

慢查询定位

定位慢查询问题，可以通过如下几个步骤进行：

开启慢日志；
使用慢日志查询分析管理工具；
基于已有的慢日志分析，对系统本身做优化。

1、开启慢日志

（1）查看慢查询日志是否开启
因为开启慢查询日志是有代价的（跟bin log、optimizer-trace一样），所以它默认是关闭的。
开启慢日志，执行命令：

show variables like 'slow%';

(2)开启慢查询日志

set global slow_query_log = on
show variables like 'slow%'；

2、慢查询的临界值设定

开启了之后，只记录查询比较慢的语句，这里会有一个标准值，而且这个标准值是可以由我们自己设定的，系统默认是long_query_time = 10秒。

show variables like '%long%';
+----------------------------------------------------------+-----------+
| Variable_name                                            | Value     |
+----------------------------------------------------------+-----------+
| long_query_time                                          | 10.000000 |
| performance_schema_events_stages_history_long_size       | 10000     |
| performance_schema_events_statements_history_long_size   | 10000     |
| performance_schema_events_transactions_history_long_size | 10000     |
| performance_schema_events_waits_history_long_size        | 10000     |
+----------------------------------------------------------+-----------+
5 rows in set (0.01 sec)

先设定查询时间阈值为1秒，然后进行耗时查询，再从日志中查看记录。

set long_query_time=1.00;
+----------------------------------------------------------+----------+
| Variable_name                                            | Value    |
+----------------------------------------------------------+----------+
| long_query_time                                          | 1.000000 |
| performance_schema_events_stages_history_long_size       | 10000    |
| performance_schema_events_statements_history_long_size   | 10000    |
| performance_schema_events_transactions_history_long_size | 10000    |
| performance_schema_events_waits_history_long_size        | 10000    |
+----------------------------------------------------------+----------+
5 rows in set (0.01 sec)

3、模拟慢查询

现在来模拟手动触发一个大于 1.00s 的慢查询(测试表中记录大概有750W条)，看是否都记录在了慢查询日志中。

select * from t_user where id > 40000 limit 4000000,10

查看慢查询日志当中有多少条记录:

show global status like '%Slow_queries%';

通过之前慢查询的日志路径中，查看。

# Time: 2025-05-07T03:59:46.066527Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 2.099325  Lock_time: 0.001390 Rows_sent: 10  Rows_examined: 4000010
use ais_1_2;
SET timestamp=1746590386;
select * from t_user where id > 40000 limit 4000000,10;
# Time: 2025-05-07T04:00:14.169876Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 2.024061  Lock_time: 0.000096 Rows_sent: 10  Rows_examined: 4000010
SET timestamp=1746590414;
select * from t_user where id > 40000 limit 4000000,10;
# Time: 2025-05-07T04:01:36.528860Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 5.273554  Lock_time: 0.000083 Rows_sent: 1  Rows_examined: 7547442
SET timestamp=1746590496;
select count(1) from t_user;
# Time: 2025-05-07T04:02:11.469447Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 2.059881  Lock_time: 0.000084 Rows_sent: 10  Rows_examined: 4000010
SET timestamp=1746590531;
select * from t_user where id > 40000 limit 4000000,10;

5、配置持久化

需要注意的是，上面的操作是在命令行中进行的，如果数据库进行重启，这些设置都会失效。

一般不建议长期开启慢查询日志，如果要永久生效，需要修改配置文件：

vim /etc/mysql/my.cnf
[mysqld]
slow_query_log = 1
long_query_time = 1.0
slow_query_log_file =/var/lib/mysql/mysql_slow.log

6、慢查询插件

wget https://downloads.percona.com/downloads/percona-toolkit/3.3.1/binary/redhat/7/x86_64/percona-toolkit-3.3.1-1.el7.x86_64.rpm

# 使用yum进行安装
yum install percona-toolkit-3.3.1-1.el7.x86_64.rpm

安装完成后，使用如下命令进行查看

 pt-query-digest laptop-svm6cms2-slow.log

# Query 2: 0 QPS, 0x concurrency, ID 0x58D071E5F863692CA2F7CEE09DED2C1B at byte 667
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2025-05-07T04:01:36
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         25       1
# Exec time     46      5s      5s      5s      5s      5s       0      5s
# Lock time      5    83us    83us    83us    83us    83us       0    83us
# Rows sent      3       1       1       1       1       1       0       1
# Rows examine  38   7.20M   7.20M   7.20M   7.20M   7.20M       0   7.20M
# Query size    14      27      27      27      27      27       0      27
# String:
# Databases    ais_1_2
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `ais_1_2` LIKE 't_user'\G
#    SHOW CREATE TABLE `ais_1_2`.`t_user`\G
# EXPLAIN /*!50100 PARTITIONS*/
select count(1) from t_user\G