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

MySQL索引优化是数据库性能调优中最常见也最见效的手段。慢查询日志定位语句、EXPLAIN 分析执行计划、索引设计消除回表，三个环节构成完整的调优流程。本文用真实场景的 SQL 示例，讲解从发现问题到验证效果的完整操作。

慢查询日志如何开启与查看

MySQL 默认不开启慢查询日志。修改参数后重启或动态开启，设置阈值时间，低于阈值即记录。

# 动态开启（无需重启）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

# 查看配置
SHOW VARIABLES LIKE 'slow_query%';

线上建议阈值设为 1 秒，定期分析 slow.log。配合 mysqldumpslow 汇总高频慢语句，优先处理出现频率高的。

EXPLAIN 执行计划怎么看

EXPLAIN 是分析 SQL 执行计划的核心工具。重点关注 type、key、rows 三列：type 从 system 到 ALL 依次变差，ALL 表示全表扫描；key 是否命中索引；rows 是预估扫描行数，数字越大开销越大。

EXPLAIN SELECT id, title, created_at
FROM orders
WHERE user_id = 12345
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

type=ref 且 key 命中联合索引，说明索引有效。若看到 type=ALL 且 rows 达百万，基本可以判定这条语句在生产环境会很慢。

联合索引设计与最左前缀

联合索引遵循最左前缀原则：索引顺序 (user_id, status, created_at) 能支持 user_id 开头条件查询。查询条件要尽量让索引覆盖更多列，减少回表。

-- 推荐：联合索引覆盖等值+排序
CREATE INDEX idx_user_status_time ON orders (user_id, status, created_at);

-- 覆盖索引避免回表
CREATE INDEX idx_user_cover ON orders (user_id, status, created_at)
  INCLUDE (title, order_no);

等值条件列放前面，范围与排序列放后面。查询包含排序时，让排序列作为联合索引的最后一列，避免 filesort。

常见的索引失效场景

索引并非命中条件就一定生效，以下写法会造成索引失效：

对索引列使用函数：WHERE DATE(created_at) = ‘2026-09-01’ 会全表扫描，改成范围条件
隐式类型转换：字符串列与数字比较时不加引号
前导模糊查询：LIKE ‘%keyword’ 无法使用索引，考虑反向存储或全文索引
OR 连接不同索引列：需要拆成 UNION ALL 或建联合索引

-- 正确写法：时间范围条件走索引
SELECT * FROM orders
WHERE created_at >= '2026-09-01'
  AND created_at < '2026-09-02';

分页查询性能优化

深分页 LIMIT 100000, 20 会扫描大量行，优化用游标法（传入上一页最后一条记录的主键）：

-- 游标分页：翻页快
SELECT * FROM orders
WHERE id > 100000
ORDER BY id ASC
LIMIT 20;

业务允许时优先用 id 游标分页代替 offset 分页，数据量大时性能差异非常明显。

调优效果如何验证

每次改完索引跑一次 EXPLAIN 对比 type 与 rows，再用相同数据集压测对比响应时间。监控端到端延迟、慢查询数量与 QPS，观察优化是否真实落地。索引不是越多越好，冗余索引会增加写放大，定期用 pt-duplicate-key-checker 清理重复索引。

原创文章，作者：小编，如若转载，请注明出处：https://www.yunthe.com/mysql-suo-yin-you-hua-shi-zhan-man-cha-xun-ding-wei-yu-zhi/

慢查询定位：从全局监控到单条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查询优化在分片架构下的难度显著提升，需要将查询路由感知下沉到应用层或中间件层。