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

慢查询定位：从全局监控到单条SQL剖析

MySQL性能调优的第一步不是优化SQL，而是找到需要优化的SQL。生产环境中的慢查询可能分散在不同时段、不同服务中，靠人工review代码效率极低。数据库运维的标准做法是启用慢查询日志，配合pt-query-digest做聚合分析。

慢查询日志配置：

-- my.cnf核心配置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5          # 超过500ms记录
log_queries_not_using_indexes = 1  # 未使用索引的SQL也记录
min_examined_row_limit = 100      # 扫描行数低于100的不记录

-- 运行时动态调整（无需重启）
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.5;

pt-query-digest对慢查询日志的聚合分析能快速定位Top N问题SQL：

# 按执行总时间排序的Top 20慢查询
pt-query-digest --limit 20 --order-by Query_time:sum /var/log/mysql/slow.log

# 按查询次数排序（高频小查询也会累积可观开销）
pt-query-digest --limit 20 --order-by Count /var/log/mysql/slow.log

输出中的Query ID是SQL指纹，相同指纹的不同参数值会归为一组。重点关注总执行时间占比高、平均扫描行数远大于返回行数、且执行频率高的SQL。

EXPLAIN执行计划逐字段解读

拿到目标SQL后，EXPLAIN是SQL查询优化的起点。MySQL 8.0的EXPLAIN输出包含12个字段，其中type、key、rows、Extra四个字段最重要。

EXPLAIN FORMAT=TREE
SELECT o.order_id, o.amount, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.create_time >= '2026-07-01'
  AND o.status = 'PAID'
  AND c.region = 'EAST'
ORDER BY o.amount DESC
LIMIT 50;

type字段表示访问类型，性能从优到劣排序：

– system/const：单行查找，主键或唯一索引等值查询
– eq_ref：关联查询中每次匹配一行
– ref：非唯一索引等值查询
– range：索引范围扫描（BETWEEN、IN、>）
– index：全索引扫描
– ALL：全表扫描

实际优化中，type达到ref级别基本可接受，range级别表示索引仍在工作，index和ALL需要重点关注。

Extra字段的关键值含义：
– Using index：覆盖索引，无需回表，最优情况
– Using where：存储层返回数据后在Server层过滤
– Using temporary：使用了临时表（排序或分组）
– Using filesort：额外排序操作
– Using index condition：索引下推（ICP），减少回表次数

复合索引设计原则与常见反模式

索引设计遵循最左前缀匹配原则——复合索引(a, b, c)可以支持a、(a,b)、(a,b,c)三种查询模式，但不能跳过a直接匹配(b,c)。

高频反模式——索引列顺序不合理：

-- 订单表查询模式分析
-- 查询1：WHERE status = 'PAID' AND create_time >= '2026-07-01'
-- 查询2：WHERE customer_id = 100 AND status = 'PAID'

-- 错误索引：把status放在最左
-- INDEX idx_status_create(status, create_time)
-- 查询1可以用索引，查询2无法利用索引（跳过了create_time列）

-- 正确索引：等值条件列放最左，范围条件列放右边
-- INDEX idx_status_create(status, create_time) -- 查询1 OK
-- INDEX idx_customer_status(customer_id, status) -- 查询2 OK

-- 更优方案：分析查询频率决定索引策略
-- 如果查询1远多于查询2，优先保证查询1的索引效率

索引下推（Index Condition Pushdown，ICP）是MySQL 5.6+的重要优化。在没有ICP时，存储引擎通过索引定位到行后，不管该行是否满足WHERE条件都要回表取完整数据，再由Server层判断。启用ICP后，能在索引中判断的条件直接在存储引擎层过滤，减少无谓回表。

ICP生效的前提是WHERE条件中有一部分可以用索引列判断，另一部分不能。例如索引(status, create_time)，查询条件为status=’PAID’ AND create_time >= ‘2026-07-01’ AND amount > 1000，前两个条件可以通过索引判断，amount > 1000需要回表后判断——ICP会减少回表次数。

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

当单表数据量超过5000万行，即便索引优化到位，B+Tree层数增加和磁盘IO开销也会导致查询性能劣化。分库分表方案是此时的标准选择，但分表后的索引策略需要重新设计。

分表键（Sharding Key）的选择决定了数据分布的均匀性和跨片查询的频率。以订单表为例，按customer_id分片可以聚合同一客户的订单到同一分片，客户维度的查询效率最高；但按create_time维度的统计查询需要扫描所有分片。分库分表方案中不存在万能分片键，需要根据核心查询模式做取舍。

跨分片查询的优化策略：建立异构索引表（将分片键与常用查询条件的映射关系单独建表），查询时先通过异构索引定位分片，再精确查询目标分片。这本质上是用空间换时间，是数据库高可用架构在分片场景下的必要妥协。SQL查询优化在分片架构下的难度显著提升，需要将查询路由感知下沉到应用层或中间件层。