MySQL慢查询定位与索引优化实战：EXPLAIN执行计划深度分析

MySQL性能调优中，慢查询是最常见也是影响最大的性能瓶颈。一条未走索引的全表扫描查询在高并发场景下可能拖垮整个数据库实例。本文从慢查询日志配置、EXPLAIN执行计划解析、索引优化策略三个层面，给出系统化的MySQL查询优化方法论。

慢查询日志配置与拦截：从采集到分类分析

开启慢查询日志是定位问题的第一步。MySQL 8.x的推荐配置：

SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;
SET GLOBAL log_throttle_queries_not_using_indexes = 10;
SET GLOBAL min_examined_row_limit = 100;

对于RDS云数据库，还需启用performance_schema并按SQL指纹聚合统计：

SELECT 
    DIGEST_TEXT AS query_pattern,
    COUNT_STAR AS exec_count,
    ROUND(AVG_TIMER_WAIT/1000000000, 2) AS avg_ms,
    ROUND(MAX_TIMER_WAIT/1000000000, 2) AS max_ms,
    SUM_ROWS_EXAMINED AS total_rows_examined,
    SUM_ROWS_SENT AS total_rows_sent
FROM performance_schema.events_statements_summary_by_digest
WHERE AVG_TIMER_WAIT/1000000000 > 1000
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

DIGEST_TEXT将具体参数值替换为占位符，方便识别同类SQL的不同参数变体。关注total_rows_examined与total_rows_sent的比值——如果扫描了10万行但只返回100行，说明索引选择严重不当。

EXPLAIN执行计划关键字段解读：type、key、rows与Extra

EXPLAIN SELECT o.id, o.order_no, u.username, u.phone
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 1 
  AND o.created_at >= '2026-07-01'
  AND o.created_at < '2026-08-01'
ORDER BY o.created_at DESC
LIMIT 20;

EXPLAIN输出的核心字段：type是访问类型，性能从优到差为system > const > eq_ref > ref > range > index > ALL，ALL即全表扫描必须优化；key是实际使用的索引名称，NULL表示未走索引；key_len是索引使用的字节数，用于判断联合索引用了几个字段；rows是预估扫描行数；Extra包含影响性能的关键提示。

Extra字段的关键取值含义：Using index表示覆盖索引无需回表，最优情况；Using where表示在存储引擎返回数据后由Server层过滤，通常需要优化；Using temporary表示使用了临时表，常见于GROUP BY和DISTINCT；Using filesort表示使用了文件排序，常见于ORDER BY；Using join buffer表示JOIN时使用了Block Nested Loop，说明关联字段无索引。

联合索引设计与最左前缀原则：B+树索引结构下的选型策略

MySQL InnoDB使用B+树索引，联合索引的字段顺序直接决定哪些查询能命中索引。设计原则是将区分度高的字段放在前面、范围查询字段放在最后：

-- 订单表常见查询模式：
-- 1. WHERE status=1 AND created_at BETWEEN ... AND ...
-- 2. WHERE user_id=? AND created_at BETWEEN ... AND ...
-- 3. WHERE status=1 AND user_id=? AND created_at BETWEEN ... AND ...

-- 最优联合索引设计
CREATE INDEX idx_status_user_created ON orders(status, user_id, created_at);
CREATE INDEX idx_user_created ON orders(user_id, created_at);

-- 验证索引使用情况
EXPLAIN SELECT * FROM orders 
WHERE status = 1 AND user_id = 10086 
  AND created_at >= '2026-07-01' AND created_at < '2026-08-01';
-- type应为ref或range，key应为idx_status_user_created

常见错误：给每个字段单独建索引。MySQL 5.0+支持Index Merge，但合并多个单列索引的效率远不如一个精心设计的联合索引。单列索引越多，写入时的索引维护开销越大，优化器的选择也可能不准确。

分库分表方案下的查询优化：跨片JOIN与分页问题

当单表数据量超过千万行，即使有完美索引，B+树的层级增加也会导致查询性能衰减。分库分表引入了新的查询优化挑战。跨分片分页查询的性能问题：

-- 单表分页（第10000页）：慢，需要扫描前200000行
SELECT * FROM orders ORDER BY created_at DESC LIMIT 199980, 20;

-- 改用游标分页（基于上一页最后一条记录的created_at）：
SELECT * FROM orders 
WHERE created_at < '2026-08-05 10:30:00'
ORDER BY created_at DESC 
LIMIT 20;

游标分页将LIMIT offset, size转化为WHERE条件 + LIMIT size，无论翻到第几页扫描行数恒定。代价是只能”上一页/下一页”跳转，不支持直接跳转到指定页码。

数据库高可用架构下的索引变更：Online DDL与pt-online-schema-change

-- MySQL 8.0 Online DDL
ALTER TABLE orders ADD INDEX idx_status_created(status, created_at), 
  ALGORITHM=INPLACE, LOCK=NONE;

-- 使用pt-online-schema-change更安全
pt-online-schema-change   --alter "ADD INDEX idx_status_created(status, created_at)"   --host=127.0.0.1 --port=3306 --user=admin --ask-pass   --chunk-size=2000 --chunk-time=0.5   --critical-load="Threads_running=200"   --max-load="Threads_running=100"   D=production,t=orders --execute

pt-online-schema-change通过创建影子表、触发器同步增量数据、分批chunk迁移的方式，实现零锁表的索引变更。--critical-load和--max-load控制迁移速率，当Threads_running超过阈值时自动暂停，避免在业务高峰期影响正常查询。索引添加完成后使用ANALYZE TABLE orders更新统计信息，让优化器收集到新索引的基数估算数据。