MySQL慢查询优化实战：从Explain到索引调优全流程

慢查询日志配置与问题定位

MySQL慢查询优化是数据库运维中最高频的工作。第一步是确保慢查询日志已正确开启：SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1;（单位秒，根据业务基线调整）。同时开启log_queries_not_using_indexes记录未使用索引的查询，这类查询即使执行时间短也可能在全表扫描。

慢查询日志文件增长很快，需要定期轮转。使用pt-query-digest工具分析慢日志，它会按总执行时间排序输出Top N查询，直接告诉你哪些SQL最需要优化。关键输出列包括：Query ID（查询指纹）、Exec time（总执行时间）、Lock time（锁等待时间）、Rows sent/examined（返回行数/扫描行数）。Rows examined远大于Rows sent意味着大量无效扫描，是优化的首要目标。

Explain执行计划的关键字段解读

拿到需要优化的SQL后，第一步是用EXPLAIN查看执行计划。Explain输出中最重要的字段及其含义：

– type：访问类型，从好到差排序：system > const > eq_ref > ref > range > index > ALL。出现ALL（全表扫描）必须优化，index（全索引扫描）也需要关注。
– key：实际使用的索引，如果为NULL说明没走索引。
– rows：预估扫描行数，越少越好。
– Extra：额外信息，Using index（覆盖索引，好）、Using filesort（额外排序，差）、Using temporary（临时表，差）、Using where（服务层过滤，中性）。

常见问题模式：type=ALL且rows很大——缺少索引或索引失效；type=ref但Extra=Using where——索引前缀匹配不够精确；Extra=Using filesort——ORDER BY的列不在索引中，需要额外排序步骤。

索引设计原则与常见失效场景

索引设计的黄金原则：最左前缀匹配。联合索引(a, b, c)可以支持a、a,b、a,b,c三种查询条件组合，但无法支持b或b,c条件查询（无法跳过最左列）。索引列的顺序应该按区分度从高到低排列，区分度等于列的不同值数除以总行数，越接近1区分度越高。

索引失效的六种常见场景：
1. 对索引列使用函数：WHERE YEAR(create_time) = 2026失效，改为WHERE create_time >= 2026-01-01 AND create_time < 2027-01-01
2. 隐式类型转换：WHERE varchar_col = 123（varchar列与整数比较），MySQL会将varchar转为整数导致索引失效
3. LIKE前缀通配符：WHERE name LIKE %张 失效，WHERE name LIKE 张% 有效
4. OR条件中有非索引列：WHERE indexed_col = 1 OR non_indexed_col = 2，整个条件走全表扫描
5. 联合索引跳列：INDEX(a,b,c)查询WHERE a=1 AND c=3，只有a走索引，c需要回表过滤
6. NOT IN/NOT EXISTS/!=：这类否定条件通常无法利用索引，考虑改写为IN或范围查询

覆盖索引与延迟关联优化

覆盖索引（Covering Index）是指查询所需的所有列都包含在索引中，无需回表查询主键数据。覆盖索引的判断标志是Explain的Extra列显示Using index。对于高频查询，设计覆盖索引能将IO次数从O(N)降到O(logN)，性能提升可达10倍以上。

延迟关联（Deferred Join）是深度分页场景的经典优化手法。当SELECT * FROM table ORDER BY id LIMIT 100000, 10时，MySQL需要扫描100010行再丢弃前100000行。延迟关联的思路是先通过子查询在覆盖索引上定位到目标行的主键，再回表获取完整数据：

-- 优化前：扫描100010行
SELECT * FROM orders ORDER BY create_time LIMIT 100000, 10;

-- 优化后：子查询走覆盖索引定位主键，外层只回表10行
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY create_time LIMIT 100000, 10
) t ON o.id = t.id;

实测在100万行数据表中，优化前查询耗时1.2秒，优化后耗时0.05秒，性能提升24倍。

复杂查询的拆分与重写策略

某些SQL本身的写法就决定了它不可能高效执行，需要从逻辑层面重写。子查询转JOIN：SELECT * FROM A WHERE id IN (SELECT a_id FROM B WHERE status=1)改写为SELECT A.* FROM A INNER JOIN B ON A.id = B.a_id WHERE B.status = 1，MySQL优化器对JOIN的执行计划选择空间更大。

UNION优化：多个UNION ALL查询同一张表的不同条件范围，可以合并为单个查询+OR条件，减少表扫描次数。COUNT优化：SELECT COUNT(*)需要扫描所有行，如果业务只需判断是否存在，用SELECT 1 LIMIT 1替代。大事务拆分：一个UPDATE影响百万行会长时间持锁，拆分为每次1000行的小事务批量执行。

优化完成后需要建立回归测试机制：记录优化前后的Explain输出和执行时间，写入优化日志；在CI中集成慢查询检查，对新SQL自动执行Explain分析，type=ALL的SQL阻断合入。