MySQL 8慢查询优化实战：执行计划分析与索引设计深度指南

EXPLAIN执行计划深度解读

MySQL慢查询优化的起点不是加索引，而是读懂执行计划。一条SQL执行慢的原因可能不是缺少索引，而是索引选择错误、扫描行数过多、或者临时表排序。通过EXPLAIN FORMAT=JSON获取的信息比普通EXPLAIN丰富得多：

EXPLAIN FORMAT=JSON
SELECT o.order_id, o.total_amount, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at BETWEEN '2026-07-01' AND '2026-07-31'
  AND o.status = 'PAID'
ORDER BY o.total_amount DESC
LIMIT 20\G

JSON格式输出中重点关注三个指标：rows_examined_per_scan（每次扫描行数）、rows_produced_per_join（实际产出行数）、filtered（过滤比例）。当rows_examined远大于rows_produced时，说明大量无效数据被扫描后丢弃，这是性能瓶颈的直接信号。

常见执行计划type字段的效率排序：system > const > eq_ref > ref > range > index > ALL。生产环境中ALL（全表扫描）和index（全索引扫描）是需要重点消除的，range以上的访问类型才算高效。

复合索引设计与最左前缀陷阱

多数慢查询的问题可以通过合理设计复合索引解决。复合索引遵循最左前缀匹配原则，但很多开发者对这个原则的理解不够精确。以下面这个索引为例：

ALTER TABLE orders ADD INDEX idx_status_created_amount (status, created_at, total_amount);

这个索引可以覆盖的查询条件组合：

-- 命中: status + created_at
WHERE status = 'PAID' AND created_at BETWEEN '2026-07-01' AND '2026-07-31'

-- 命中: status
WHERE status = 'PAID'

-- 未命中: 跳过status，直接用created_at
WHERE created_at BETWEEN '2026-07-01' AND '2026-07-31'

-- 命中: status + created_at + total_amount（可做索引下推）
WHERE status = 'PAID' AND created_at > '2026-07-01' ORDER BY total_amount DESC

最左前缀的真正含义是：索引从左到右依次匹配，遇到范围条件（BETWEEN、>、<、LIKE前缀）则停止匹配后续列。上面的第三个查询因为没有status条件，无法使用索引的任何部分。第四个查询中created_at是范围条件，total_amount无法用于过滤，但可以利用索引的有序性避免filesort（索引本身已按status、created_at、total_amount排序）。

索引下推与覆盖索引优化

MySQL 5.6引入的索引下推（ICP）在复合索引场景下效果显著。不启用ICP时，存储引擎根据最左前缀从索引中找到满足条件的行，然后回表取完整行数据，再由Server层根据剩余WHERE条件过滤。启用ICP后，存储引擎在索引遍历阶段就利用索引中包含的全部列进行过滤，减少回表次数。

-- 查看ICP是否启用
SHOW VARIABLES LIKE 'optimizer_switch';
-- 确保包含 index_condition_pushdown=on

-- 典型受益场景
SELECT * FROM orders
WHERE status = 'PAID' AND total_amount > 1000;

-- 有索引 idx_status_created_amount(status, created_at, total_amount)
-- ICP生效时：引擎层直接用total_amount>1000过滤，减少回表

覆盖索引是更进一步的优化——当查询的所有列都包含在索引中时，无需回表即可返回结果：

-- 覆盖索引查询
SELECT status, created_at, total_amount
FROM orders
WHERE status = 'PAID';

-- EXPLAIN中Extra列显示 Using index 即表示命中覆盖索引

覆盖索引的代价是索引体积增大，写入性能下降。对于频繁查询但很少更新的列，覆盖索引收益极高；对于频繁INSERT/UPDATE的表，需要权衡读写比例。

分页查询优化方案

深分页是慢查询的重灾区。传统LIMIT分页在偏移量大时性能急剧下降：

-- 慢：扫描100020行，丢弃前100000行
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- 优化方案1：游标分页
SELECT * FROM orders
WHERE id > 100000
ORDER BY id
LIMIT 20;

-- 优化方案2：延迟关联
SELECT o.* FROM orders o
INNER JOIN (
  SELECT id FROM orders ORDER BY id LIMIT 100000, 20
) t ON o.id = t.id;

游标分页是最优解，但要求客户端记录上一页的最后一条记录ID，不支持跳页。延迟关联方案兼容跳页，子查询只走索引找到20个ID，再回表取完整数据，扫描行数从100020降到20+子查询的索引扫描量。

慢查询监控与自动化巡检

优化不是一次性工作，需要持续监控。开启慢查询日志并设置合理阈值：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;  -- 500毫秒
SET GLOBAL log_queries_not_using_indexes = ON;

配合pt-query-digest工具分析慢日志，按总执行时间排序找出最耗资源的SQL：

pt-query-digest /var/lib/mysql/slow.log --since '24h' \
  --order-by Query_time:sum \
  --limit 10

对于核心业务表，建议建立SQL审计规则：所有新增查询必须通过EXPLAIN检查type和rows指标，全表扫描的SQL禁止上线。这个规则通过CI流程中的自动化检查来执行，比人工review更可靠。数据库性能问题的根因往往在设计阶段就埋下了，事后优化的成本远高于事前审查。