MySQL慢查询诊断与索引优化：从EXPLAIN执行计划到性能调优实战

MySQL慢查询是数据库运维中最常见性能问题。一条低效SQL可能拖垮整个数据库实例。本文以问答形式讲解慢查询诊断流程、EXPLAIN执行计划解读和索引优化策略。

如何定位MySQL慢查询

开启慢查询日志是定位慢SQL的第一步。在my.cnf中配置：

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON
min_examined_row_limit = 100

long_query_time设置为1秒，执行时间超过1秒的SQL会被记录。log_queries_not_using_indexes开启后，未使用索引的查询也会被记录，即使执行时间未超过阈值。min_examined_row_limit设置最小扫描行数，避免记录扫描行数过少的查询。

使用mysqldumpslow工具分析慢查询日志：

# 按总耗时排序，显示前10条
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 按次数排序
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 按返回行数排序
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

-s t按总耗时排序，-s c按执行次数排序，-s r按返回行数排序。生产环境中通常优先优化总耗时最高和执行次数最多的SQL。

EXPLAIN执行计划各字段如何解读

在SQL前加上EXPLAIN关键字即可查看执行计划。以一条典型慢查询为例：

EXPLAIN SELECT o.order_no, o.amount, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending' AND o.create_time > '2026-01-01'
ORDER BY o.create_time DESC
LIMIT 20;

执行计划输出中需要重点关注以下字段：

type字段表示访问类型，性能从好到差依次为：system > const > eq_ref > ref > range > index > ALL。ALL表示全表扫描，通常是优化重点。range表示索引范围扫描，index表示扫描整个索引树。

key字段显示实际使用的索引。如果为NULL说明没有使用索引。possible_keys显示可能使用的索引，key显示实际选择的索引，两者不一致时需要分析原因。

rows字段是预估扫描行数。这个值越小说明索引过滤效果越好。如果rows值接近表总行数，说明索引未生效。

Extra字段提供额外信息。Using index表示覆盖索引，无需回表。Using filesort表示需要额外排序操作。Using temporary表示使用了临时表。出现filesort和temporary通常需要优化。

联合索引最左前缀原则与索引设计

联合索引遵循最左前缀原则。索引(a, b, c)可以支持a、a,b、a,b,c三种查询条件组合，但无法支持b,c或c单独查询。

索引设计原则：区分度高的列放前面，等值查询条件放前面，范围查询条件放后面。

-- 错误设计：status区分度低放前面
CREATE INDEX idx_wrong ON orders(status, customer_id, create_time);

-- 正确设计：customer_id区分度高，等值查询放前面
CREATE INDEX idx_correct ON orders(customer_id, status, create_time);

-- 验证索引选择
EXPLAIN SELECT * FROM orders
WHERE customer_id = 1001 AND status = 'pending' AND create_time > '2026-01-01';
-- type应为ref或range，key应为idx_correct

覆盖索引优化：减少回表操作

当查询字段全部包含在索引中时，InnoDB直接从索引树返回数据，无需回表查询聚簇索引，称为覆盖索引。

-- 原始查询：需要回表
SELECT id, order_no, amount FROM orders WHERE customer_id = 1001;

-- 创建包含查询字段的覆盖索引
CREATE INDEX idx_covering ON orders(customer_id, order_no, amount);

-- 优化后：Using index出现在Extra字段，无需回表
EXPLAIN SELECT id, order_no, amount FROM orders WHERE customer_id = 1001;

覆盖索引将查询从两次IO（索引树+聚簇索引）减少为一次IO，在大数据量场景下性能提升明显。

分库分表场景下的索引优化策略

单表数据量超过千万行后，B+树索引层数增加，查询性能下降。分库分表是常见解决方案，但索引策略需要调整。

分片键选择原则：查询频率最高的条件作为分片键，避免跨分片查询。以订单表为例，按user_id分片：

-- ShardingSphere分片规则配置（YAML）
rules:
  - !SHARDING
    tables:
      orders:
        actualDataNodes: ds_${0..3}.orders_${0..15}
        databaseStrategy:
          standard:
            shardingColumn: user_id
            shardingAlgorithmName: db_mod
        tableStrategy:
          standard:
            shardingColumn: user_id
            shardingAlgorithmName: table_mod
    shardingAlgorithms:
      db_mod:
        type: MOD
        props:
          sharding-count: 4
      table_mod:
        type: MOD
        props:
          sharding-count: 16

分片后每个分表仍然需要建索引，索引策略与单表一致。跨分片查询会广播到所有分片，性能较差，应尽量避免。无法避免的场景可以考虑建异构索引表，用消息中间件异步同步数据到按其他维度分片的表中。

索引失效的常见原因排查

原因一：对索引列使用函数或运算。WHERE YEAR(create_time) = 2026不会使用create_time索引，改为WHERE create_time >= ‘2026-01-01’ AND create_time < '2027-01-01'。

原因二：隐式类型转换。字段类型为VARCHAR，查询条件传入整数值，MySQL会进行隐式转换导致索引失效。确保查询参数类型与字段类型一致。

原因三：OR条件两侧不是所有列都有索引。WHERE a = 1 OR b = 2，如果b没有索引，整个查询会全表扫描。改为UNION ALL或为b添加索引。

原因四：LIKE以通配符开头。WHERE name LIKE ‘%abc’无法使用索引。如果必须使用前缀模糊查询，考虑全文索引或搜索引擎方案。

原因五：NOT IN和NOT EXISTS优化器可能选择全表扫描。数据量大时改用LEFT JOIN … WHERE … IS NULL。

数据备份恢复方面，优化前建议使用pt-online-schema-change在线修改索引，避免长时间锁表影响业务。