MySQL中一条慢查询SQL的优化思路是怎样的？

知识点图片

MySQL慢查询优化遵循发现、分析、解决闭环，核心用Explain分析，通过索引及SQL重写提升性能。

优化 MySQL 慢查询是一个系统性的工程，通常遵循 “发现问题 -> 分析问题 -> 解决问题 -> 验证效果” 的闭环流程。

以下是详细的优化思路和步骤：

在优化之前，首先要抓到“肇事”的 SQL 语句。

开启慢查询日志 (Slow Query Log)
- 查看配置：SHOW VARIABLES LIKE '%slow_query_log%';
- 设置阈值：设置 long_query_time（例如设置为 1 秒或 0.5 秒），超过该时间的 SQL 会被记录。
使用工具分析日志
- mysqldumpslow：MySQL 自带工具，可以按照频次、时间排序，帮你找到最耗时的那几类 SQL。
- pt-query-digest：Percona Toolkit 中的工具，分析报告更详细，能给出优化建议。
实时监控
- 使用 SHOW PROCESSLIST 查看当前正在执行的线程，排查是否有锁等待或长时间运行的 SQL。

找到慢 SQL 后，最核心的步骤是使用 EXPLAIN 命令查看执行计划。

关注 EXPLAIN 结果中的关键字段：

type (访问类型) - 性能由好到差排序：
- system > const > eq_ref > ref > range > index > ALL
- 优化目标：至少达到 range 级别，最好是 ref。如果是 ALL（全表扫描），必须优化。
key (实际使用的索引)
- 如果是 NULL，说明没用到索引，这是慢查询的常见原因。
rows (扫描行数)
- 预估需要扫描的行数。数值越小越好。
key_len (索引长度)
- 用于判断联合索引中被使用到的列是哪些（是否符合最左前缀原则）。
Extra (额外信息) - 重点关注坏味道：
- Using filesort：说明 MySQL 无法利用索引进行排序，需要额外的排序操作（CPU 消耗大）。
- Using temporary：说明使用了临时表（常见于 Group By 或 Distinct），性能损耗严重。
- Using index：这是好事，说明使用了覆盖索引，不需要回表查询。

根据 EXPLAIN 的分析结果，采取具体的优化措施。

全表扫描 (type=ALL)：
- 给 WHERE、ORDER BY、GROUP BY 后面的字段添加索引。
违背最左前缀原则：
- 如果是联合索引 (a, b, c)，查询 WHERE b=1 AND c=1 无法使用索引。必须带上 a。
索引失效陷阱：
- 函数计算：WHERE YEAR(create_time) = 2023 会导致索引失效，改为 WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'。
- 类型转换：字符串字段不加单引号（如 phone = 138000），发生隐式转换会导致索引失效。
- 模糊查询：LIKE '%abc'（前导百分号）无法使用索引，LIKE 'abc%' 可以。
- OR 条件：OR 两边的字段必须都有索引，否则索引失效。
利用覆盖索引：
- 尽量避免 SELECT *，只查询需要的列，让查询字段都在索引树中，避免回表（Back-table lookup）。

深分页优化 (Deep Pagination)：
- LIMIT 1000000, 10 会很慢，因为要扫描前 100 万行并丢弃。
- 优化：利用 ID 或者是上次查询的锚点。
  - SELECT * FROM table WHERE id > 1000000 LIMIT 10;
  - 或者延迟关联：SELECT a.* FROM table a, (SELECT id FROM table LIMIT 1000000, 10) b WHERE a.id = b.id;
优化 JOIN：
- 小表驱动大表：确保 Left Join 左边是小表（数据量小或过滤后数据量小）。
- 被驱动表的关联字段必须有索引。
优化 UNION：
- 尽量使用 UNION ALL 代替 UNION，除非必须去重。UNION 会多一步临时表去重操作。
优化 IN 和 EXISTS：
- 主表大、子表小，用 IN。
- 主表小、子表大，用 EXISTS。

字段类型：
- 能用 TINYINT 别用 INT。
- 尽量把字段设置为 NOT NULL，NULL 值会增加索引维护成本且让统计变复杂。
反范式设计：
- 如果关联查询太多，可以考虑在表中增加冗余字段，以空间换时间，减少 JOIN。

优化完成后，必须验证：

播面