MySQL调优

embedded/2024/10/9 13:30:45/

MySQL调优的一般流程为：

对MySQL进行监控
排查慢SQL
通过执行计划找出慢的原因
制定优化策略

对MySQL进行监控

监控工具（例如Prometheus+Grafana）监控MySQL，发现查询性能变慢，报警提醒运维人员。

排查慢查询日志

前置准备

// 开启慢查询日志，修改慢查询阈值
SET slow_query_log='ON';     #开启慢查询日志
SET long_query_time = 1;     #设置慢查询阈值(单位 秒)

找出最慢的几条SQL

运行时间超过 long_query_time 的SQL语句，会被写入到慢查询日志文件中或者mysql.slow_log表中。此时，可以使用慢日志分析工具 mysqldumpslow 来进行分析慢查询。

mysqldumpslow 命令的具体参数如下：-a: 不将数字抽象成N，字符串抽象成S
-s: 是表示按照何种方式排序：c: 访问次数l: 锁定时间r: 返回记录t: 查询时间al:平均锁定时间ar:平均返回记录数at:平均查询时间 （默认方式）ac:平均查询次数
-t: 即为返回前面多少条的数据；
-g: 后边搭配一个正则匹配模式，大小写不敏感的；

示例：

参考：慢查询分析调优工具～mysqldumpslow | MySQL 技术论坛 (learnku.com)

# 命令行，按照查询时间排序，查看前五条 慢查询SQL 语句
mysqldumpslow -s t -t 5 /var/lib/mysql/xxx-slow.log   # 得到访问次数最多的10条SQL：
mysqldumpslow -s r -t  10 /var/lib/mysql/xxx-slow.log# 得到按照时间排序的前10条里面含有左连接的SQL：
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/695f5026f0f6-slow.log
# 也支持管道符命令
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/695f5026f0f6-slow.log | more //分页显示

分析执行计划

上一步骤【排查慢查询日志】中找到了慢SQL语句之后，现在通过分析该慢SQL语句的执行计划，分析出具体慢的原因。

具体的执行计划中的各个参数表示的含义，参考：一文读懂 MySQL Explain 执行计划-阿里云开发者社区 (aliyun.com)

可以根据：EXPLAIN 你要分析的慢SQL语句如（EXPLAIN SELECT * FROM user u WHERE u.id=1）输出的内容进行分析：

mysql> EXPLAIN SELECT * FROM user u WHERE r.name = '扫地机';
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | u     | NULL       | const | PRIMARY       | PRIMARY      | 4       | const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+

比如：