MySQL慢查询优化实战：EXPLAIN执行计划分析与索引重建

小编 • • 数据库

MySQL性能调优的第一步是抓住慢查询，一条SQL扫描全表与走索引的耗时差距能达到百倍以上。本文从慢查询日志定位、EXPLAIN执行计划解读到索引重建策略，给出完整的MySQL慢查询优化流程，附带可直接套用的排查命令与案例。

开启慢查询日志定位问题SQL

MySQL默认关闭慢查询日志，按以下参数开启并配置阈值：

-- 动态开启
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;

-- 查看当前设置
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

long_query_time=1表示超过1秒的SQL被记录，生产环境先按这个阈值跑一段时间，再根据日志量收敛。8.0里用performance_schema的events_statements_summary_by_digest表也能按SQL指纹聚合统计，配合sys.schema_unused_indexes可以找到从未使用的冗余索引。

EXPLAIN执行计划关键列解读

拿到慢SQL后用EXPLAIN看执行计划，重点读五列：

EXPLAIN SELECT o.order_no, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 1 AND o.created_at > NOW() - INTERVAL 7 DAY;

type列是访问类型，从好到差依次是system、const、eq_ref、ref、range、index、ALL，看到ALL就要优先建索引；key列显示实际使用的索引，为NULL说明没走索引；rows列是预估扫描行数，优化后应明显下降；Extra列出现Using filesort或Using temporary说明排序或分组没走索引，出现Using where配合type=ALL基本可以定位为全表扫描。

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

索引设计遵循最左前缀原则：复合索引(created_at, status, user_id)可以服务created_at范围、created_at+status精确、created_at+status+user_id三组查询，但跳过第一列直接查status不会命中。区分度低的列（status这类枚举）单独建索引收益很小，要放在复合索引靠后的位置。

索引失效的高频原因：函数包裹索引列WHERE DATE(created_at)='2026-09-01'会放弃索引，应改为created_at >= '2026-09-01' AND created_at < '2026-09-02'；隐式类型转换如字符串字段与数字比较；LIKE '%keyword'前置通配符导致无法走索引；OR条件中有一个字段无索引会退化为全表扫描。

MySQL索引重建与统计信息更新

表数据大量变更后索引碎片率上升，扫描性能下降。通过SHOW TABLE STATUS LIKE 'orders'查看Data_free字段评估碎片，重建索引使用：

ALTER TABLE orders ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;

ALTER TABLE重建表会整理数据页与索引页，Online DDL的ALGORITHM=INPLACE配合LOCK=NONE允许DML并发执行，大表建议在业务低峰执行并评估磁盘空间。统计信息过期导致优化器选错索引时，执行ANALYZE TABLE orders更新cardinality统计，必要时用FORCE INDEX或优化器提示校正。

分页查询与深分页优化

ORDER BY + LIMIT是慢查询重灾区：LIMIT 100000,20需要扫描前10万行再丢弃。优化方案有延迟关联：

-- 先只取主键再回表
SELECT t.*
FROM orders t
JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 20) tmp
  ON t.id = tmp.id
ORDER BY t.id;

时间线型数据推荐游标分页：WHERE id > last_seen_id ORDER BY id LIMIT 20，利用主键索引直接定位，恒定扫描20行。数据量更大时考虑按时间分区表，把查询范围缩到单个分区，从根上减少扫描量。

优化效果验证方法

每轮优化后用EXPLAIN对比rows预估与key列，再实际执行对比耗时，阈值建议从秒级优化到百毫秒内。线上验证用EXPLAIN ANALYZE（MySQL 8.0.18+）拿到真实执行时间与循环耗时，比rows预估更准。优化完成后保持慢查询日志开启，观察同类SQL是否回落，确认整体数据库负载（Threads_running、QPS）是否下降。

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

赞 (0)
小编小编
0
上一篇 2026年9月8日
下一篇 2026年9月8日

相关推荐

MySQL慢查询优化实战：EXPLAIN执行计划分析与联合索引设计

小编 • • 数据库

数据库性能问题八成出在慢查询：一条SQL没走索引，扫描行数从几千涨到几百万，接口响应从几十毫秒拖到数秒，连接池被占满后整个服务跟着雪崩。慢查询优化有固定流程：定位慢SQL、读执行计划、改索引或改写语句，按这个顺序排查，大部分问题能在半小时内解决。

第一步：开启慢日志并定位问题SQL

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;          -- 超过1秒记录
SET GLOBAL log_queries_not_using_indexes = ON;

配合sys视图快速聚合，找出累计耗时最高的语句：

SELECT query, exec_count,
       ROUND(avg_latency/1e9, 2) AS avg_sec,
       ROUND(sum_latency/1e9, 1)  AS total_sec
FROM sys.statement_analysis
ORDER BY sum_latency DESC LIMIT 10;

优先处理”总耗时”高的语句，单次快但一天调用千万次的语句，往往比单次慢的语句更值得优化。

第二步：EXPLAIN执行计划关键字段解读

EXPLAIN SELECT * FROM orders
WHERE user_id = 1001 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20;

重点看四列：

  • type：访问类型，range以上可接受，ALL全表扫描且扫描行数大时必须处理。
  • key / rows：实际使用的索引与预估扫描行数，rows的量级直接决定耗时。
  • Extra：出现Using filesort说明排序没走索引；Using index说明命中覆盖索引，是理想状态。

第三步：联合索引设计与最左前缀原则

上面的查询，直觉建法是给user_id、status、created_at各建一个单列索引，实际执行时MySQL大概率只选其中一个。正确做法是建联合索引，按”等值条件在前、排序条件在后”的顺序：

ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, created_at);

这个索引同时解决三件事：user_id和status等值过滤、created_at排序（消除filesort）、LIMIT 20提前终止扫描。注意最左前缀原则：查询条件必须从索引最左列开始连续命中，跳过user_id直接查status用不上这个索引。

排序方向上有个坑：created_at用DESC排序时，8.0之前版本的索引都是升序存储，ORDER BY混用ASC/DESC可能退化为filesort，8.0支持降序索引：

ALTER TABLE orders ADD INDEX idx_user_time_desc (user_id, created_at DESC);

常见索引失效场景与SQL改写

  • 索引列上使用函数：WHERE DATE(created_at) = ‘2026-09-01’不走索引，改写为范围查询：WHERE created_at >= ‘2026-09-01’ AND created_at < '2026-09-02'。
  • 隐式类型转换：phone字段是VARCHAR，查询写WHERE phone = 13800001111（数字字面量）会导致索引失效，必须写字符串：WHERE phone = ‘13800001111’。
  • 前导通配符LIKE：LIKE ‘%手机’无法使用B+树索引，能改后缀匹配的改LIKE ‘手机%’，不能改的考虑全文索引或搜索引擎。
  • OR连接非索引列：OR两侧只要有列没有索引就全表扫描，拆成两个UNION查询分别命中各自索引。

深分页优化：延迟关联

LIMIT 100000, 20这类深分页会扫描100020行再丢弃前10万行。先用覆盖索引定位主键，再回表取数据：

SELECT o.* FROM orders o
JOIN (SELECT id FROM orders
      WHERE user_id = 1001
      ORDER BY created_at DESC
      LIMIT 100000, 20) t ON o.id = t.id;

子查询只走覆盖索引，扫描代价大幅下降，这是不改业务接口前提下最通用的深分页改法。分页深度更大的场景（管理后台任意跳页），把交互改成基于游标的”下一页”查询：WHERE created_at < 上一页最后一条的时间值。

优化闭环：改完必须验证

每次索引调整后重新EXPLAIN对比扫描行数，再在预发环境用接近线量的数据压测。线上加索引使用在线DDL（8.0默认ALGORITHM=INPLACE, LOCK=NONE），大表操作选业务低峰期执行，并提前确认磁盘剩余空间满足临时排序需求。

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

赞 (0)
小编小编
0
上一篇 2026年9月2日
下一篇 2026年9月2日

相关推荐

MySQL慢查询优化实战：EXPLAIN执行计划解读与索引调优案例

小编 • • 数据库

MySQL慢查询是数据库性能问题的核心来源。EXPLAIN命令输出查询执行计划，揭示优化器选择的访问路径、索引使用情况和扫描行数估算。读懂EXPLAIN输出并正确调整索引，是数据库性能调优的基本功。本文通过三个真实慢查询案例演示完整的优化流程。

EXPLAIN输出字段解读：type、key、rows与Extra列含义

执行 EXPLAIN SELECT 后输出的核心列：

EXPLAIN SELECT * FROM orders WHERE user_id = 10086 AND status = 1;

type列（访问类型，性能从好到差）：const > eq_ref > ref > range > index > ALL。ALL表示全表扫描，必须优化。range表示索引范围扫描。ref表示通过非唯一索引等值查询。

key列：实际使用的索引名。NULL表示未使用索引。

rows列：优化器估算的需要扫描的行数。rows越小说明索引过滤效果越好。

Extra列：额外信息。Using index（覆盖索引，无需回表）是理想状态；Using filesort（文件排序）和 Using temporary（临时表）是需要消除的信号。

案例一：隐式类型转换导致索引失效

线上发现订单查询接口偶发超时，慢查询日志记录：

# 慢查询日志
# Query_time: 3.2s  Lock_time: 0.0s  Rows_sent: 1  Rows_examined: 2800000
SELECT * FROM orders WHERE order_no = 2026080500001234;

order_no字段建有唯一索引，但查询耗时3.2秒，扫描280万行。执行EXPLAIN：

EXPLAIN SELECT * FROM orders WHERE order_no = 2026080500001234;

-- 结果
-- type: ALL  | key: NULL  | rows: 2800000  | Extra: Using where

type为ALL，索引完全未生效。查看表结构：order_no字段类型为VARCHAR(32)，但查询传入的是整数2026080500001234而非字符串。MySQL对整型和字符串比较时，将字符串列转换为数值——这导致全表扫描。

-- 查看字段类型
DESC orders;
-- order_no | varchar(32) | YES | MUL

-- 修正：传入字符串值
EXPLAIN SELECT * FROM orders WHERE order_no = '2026080500001234';
-- type: const  | key: uk_order_no  | rows: 1  | Extra: NULL

修正后查询从3.2秒降至0.2毫秒。根因在于ORM框架的参数绑定未指定字符串类型。检查MyBatis MapperXML中的参数类型，确保 #{orderNo} 对应Java String类型。

案例二：多列查询索引顺序与最左前缀原则

用户订单列表查询，WHERE条件包含user_id（等值）和create_time（范围排序）：

-- 慢查询：1.8秒，扫描15万行
SELECT * FROM orders
WHERE user_id = 10086
  AND create_time >= '2026-07-01'
  AND create_time < '2026-08-01'
ORDER BY create_time DESC
LIMIT 20;
EXPLAIN SELECT ... (同上);

-- type: ref  | key: idx_user_id  | rows: 150000  | Extra: Using where; Using filesort

只有idx_user_id单列索引起作用。Extra列出现 Using filesort，表示ORDER BY无法利用索引排序，MySQL需要内存排序15万行后取前20条。

创建联合索引（user_id在前，create_time在后）：

ALTER TABLE orders ADD INDEX idx_user_create (user_id, create_time);

EXPLAIN SELECT ... (同上);
-- type: range  | key: idx_user_create  | rows: 3200
-- Extra: Using index condition

修正后rows从15万降至3200，filesort消失。联合索引满足最左前缀原则：user_id等值匹配走索引定位，create_time范围扫描利用索引有序性直接ORDER BY，无需额外排序。

联合索引字段顺序原则：等值查询字段在前，范围查询字段在后，范围查询后的字段无法利用索引。如果还有status等值过滤条件，应为 (user_id, status, create_time)。

案例三：覆盖索引消除回表与分页深度优化

后台分页查询，深分页时性能急剧下降：

-- 第一页：0.3秒
SELECT * FROM orders ORDER BY id DESC LIMIT 0, 20;

-- 第10000页：4.5秒
SELECT * FROM orders ORDER BY id DESC LIMIT 200000, 20;

深分页问题在于MySQL需要扫描前200020行，丢弃前200000行只返回20行。优化方法：延迟关联，先通过覆盖索引查出主键，再关联查询完整数据：

-- 优化后深分页查询：0.4秒
SELECT t.* FROM orders t
INNER JOIN (
    SELECT id FROM orders ORDER BY id DESC LIMIT 200000, 20
) tmp ON t.id = tmp.id;

子查询 SELECT id FROM orders 只读取主键列，走主键索引的覆盖扫描（Using index），IO量极小。外层JOIN通过20个主键值精确回表，避免扫描200020行完整数据行。

如果只需返回部分字段而非全部，直接创建覆盖索引：

-- 需求：查询订单列表只需返回order_no、user_id、amount、status
-- 创建覆盖索引
ALTER TABLE orders ADD INDEX idx_cover_list (id, order_no, user_id, amount, status);

-- 查询直接走覆盖索引，无需回表
SELECT id, order_no, user_id, amount, status
FROM orders ORDER BY id DESC LIMIT 200000, 20;
-- Extra: Using index

索引维护与监控：定期审查冗余索引和未使用索引

索引不是越多越好——写入操作需同步更新所有索引，过多索引拖慢INSERT/UPDATE。定期通过sys.schema_unused_indexes视图排查从未使用的索引：

-- 查询从未使用的索引
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'your_db';

-- 查询冗余索引
SELECT * FROM sys.schema_redundant_indexes
WHERE table_schema = 'your_db';

删除冗余索引前，确认该索引未被任何查询使用：检查慢查询日志、应用代码中的ORM映射，确认后逐步下线。

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

赞 (0)
小编小编
0
上一篇 2026年8月5日
下一篇 2026年8月5日

相关推荐