MySQL 慢查询根治指南从 EXPLAIN 看懂到索引覆盖率优化的完整链路一、加了索引为什么还慢——索引失效的六种典型场景数据库慢查询排查中最让人挫败的场景表上索引明明存在EXPLAIN却显示type: ALL全表扫描。索引失效不是偶发现象而是查询写法、字段类型、数据分布三者共同作用下的系统性问题。根据 MySQL 优化器的决策逻辑索引失效的根因可以归纳为六类隐式类型转换、左模糊匹配、函数包裹索引列、OR 条件未命中索引、联合索引的最左前缀不匹配、以及索引选择性过低导致优化器放弃索引。每一个失效场景都有明确的修复路径——关键是要读懂EXPLAIN输出的每一列信号。二、EXPLAIN 输出解读从执行计划中捕获性能信号flowchart TD A[EXPLAIN SELECT ...] -- B{type 字段} B --|ALL| C[全表扫描——最差情况br/必须优化] B --|index| D[全索引扫描——略优于 ALLbr/仍需关注行数] B --|range| E[索引范围扫描——可接受br/检查扫描行数与返回行数的比例] B --|ref| F[非唯一索引查找——良好br/单个索引值匹配多行] B --|eq_ref| G[唯一索引查找——优秀br/连接查询中每行精确匹配一行] B --|const| H[主键常量查找——最优br/O(1) 定位] A -- I{Extra 字段} I --|Using filesort| J[需排序且无法用索引——瓶颈信号] I --|Using temporary| K[需临时表——内存或磁盘代价] I --|Using index| L[覆盖索引——最优br/回表次数为零] I --|Using where| M[索引过滤——正常br/需关注 filtered% 值]EXPLAIN分析的关键维度矩阵维度指标优良差访问类型typeconst/eq_refref/rangeindex/ALL扫描行数rows 实际返回行 × 2 × 10 × 100过滤比例filtered 50% 10% 5%额外操作ExtraUsing indexUsing whereUsing filesort/temporary索引长度key_len精确匹配索引列部分使用索引0(NULL)三、索引优化的六种实战技巧-- 技巧 1: 联合索引的最左前缀——where 条件必须从联合索引的首列开始 -- ❌ 索引 idx_user_status_time(user_id, status, create_time) 无法被以下查询使用 status 列 SELECT * FROM orders WHERE status paid AND create_time 2025-01-01; -- ✅ 调整索引顺序或补充单列索引 ALTER TABLE orders ADD INDEX idx_status_time(status, create_time); -- 技巧 2: 覆盖索引消除回表——SELECT 列全部包含在索引中 -- ❌ 需要回表读取 title, content 列 SELECT title, content FROM posts WHERE author_id 100 ORDER BY create_time DESC LIMIT 20; -- ✅ 建立覆盖索引 (author_id, create_time, title, content) ALTER TABLE posts ADD INDEX idx_cover(author_id, create_time, title, content); -- Extra 显示 Using index —— 零回表 -- 技巧 3: 避免函数包裹索引列——优化器无法使用索引 -- ❌ 对索引列应用函数导致失效 SELECT * FROM users WHERE DATE(register_time) 2025-01-15; -- ✅ 改写为范围查询 SELECT * FROM users WHERE register_time 2025-01-15 00:00:00 AND register_time 2025-01-16 00:00:00; -- 技巧 4: 前缀索引——大索引列的空间效率与选择性权衡 -- 对 VARCHAR(768) 的电商 SKU 码建立前缀索引 ALTER TABLE products ADD INDEX idx_sku_prefix(sku(12)); -- 用以下查询评估前缀选择性 95% 即认为可接受 SELECT COUNT(DISTINCT LEFT(sku, 12)) / COUNT(*) FROM products; -- 技巧 5: JOIN 的驱动表选择——小表驱动大表 -- ❌ 大表驱动小表orders 10M 行users 10K 行 SELECT u.name, o.amount FROM orders o JOIN users u ON o.user_id u.id; -- ✅ 小表驱动大表——优化器自动选择但可用 STRAIGHT_JOIN 强制 SELECT u.name, o.amount FROM users u STRAIGHT_JOIN orders o ON u.id o.user_id; -- 技巧 6: 分页深翻——OFFSET 的性能灾难 -- ❌ OFFSET 1000000 需要扫描并丢弃前 100 万行 SELECT * FROM articles ORDER BY id LIMIT 20 OFFSET 1000000; -- ✅ 基于游标的分页 SELECT * FROM articles WHERE id 1000000 ORDER BY id LIMIT 20;四、索引的成本写入放大与维护开销写入性能代价每个二级索引在 INSERT/UPDATE/DELETE 时都需要同步维护 B-Tree 的插入和页分裂。3 个索引意味着写放大率达到 4 倍1 主键 3 个二级索引。在高写入速率10K QPS下索引维护的 I/O 可能超过数据本身。页分裂与空间碎片B-Tree 的页分裂导致索引物理页利用率下降特别是在 UUID 作为主键时随机插入导致频繁页分裂。使用自增主键可将页利用率从 50%~70% 提升至 90%。索引选择性评估COUNT(DISTINCT col) / COUNT(*)低于 5%~10% 的列不建议单独建索引。例如gender2 个值的索引选择性为 0.01%——MySQL 优化器宁可全表扫描也不愿回表 50% 的行。索引覆盖的维护成本包含业务字段的宽覆盖索引如包含 TEXT 列在每次 UPDATE 覆盖列时触发索引页更新。覆盖索引的目标是消除 SELECT 的回表但代价是增大了 UPDATE 的写入负担——必须在读写比中做取舍。五、总结MySQL 索引优化遵循三个核心原则能用索引 ! 用好索引必须从 type、rows、extra 三个维度综合判断覆盖索引优于回表索引包含 SELECT 全部列时最优索引不是越多越好每个索引都增加写入成本。排查路径先用EXPLAIN FORMATJSON获取成本模型信息锁定 rows 与 filtered 不匹配的查询再用pt-query-digest从慢查询日志中找到重复出现的高频慢 SQL最后按索引失效模式逐项修复——注意不要一次添加多个索引后仅看效果应每次变更一个索引并对比前后的EXPLAIN输出与执行时间。索引优化的本质是空间换时间的工程权衡每一列是否建索引都应有数据支撑而非经验判断。
MySQL 慢查询根治指南：从 EXPLAIN 看懂到索引覆盖率优化的完整链路
MySQL 慢查询根治指南从 EXPLAIN 看懂到索引覆盖率优化的完整链路一、加了索引为什么还慢——索引失效的六种典型场景数据库慢查询排查中最让人挫败的场景表上索引明明存在EXPLAIN却显示type: ALL全表扫描。索引失效不是偶发现象而是查询写法、字段类型、数据分布三者共同作用下的系统性问题。根据 MySQL 优化器的决策逻辑索引失效的根因可以归纳为六类隐式类型转换、左模糊匹配、函数包裹索引列、OR 条件未命中索引、联合索引的最左前缀不匹配、以及索引选择性过低导致优化器放弃索引。每一个失效场景都有明确的修复路径——关键是要读懂EXPLAIN输出的每一列信号。二、EXPLAIN 输出解读从执行计划中捕获性能信号flowchart TD A[EXPLAIN SELECT ...] -- B{type 字段} B --|ALL| C[全表扫描——最差情况br/必须优化] B --|index| D[全索引扫描——略优于 ALLbr/仍需关注行数] B --|range| E[索引范围扫描——可接受br/检查扫描行数与返回行数的比例] B --|ref| F[非唯一索引查找——良好br/单个索引值匹配多行] B --|eq_ref| G[唯一索引查找——优秀br/连接查询中每行精确匹配一行] B --|const| H[主键常量查找——最优br/O(1) 定位] A -- I{Extra 字段} I --|Using filesort| J[需排序且无法用索引——瓶颈信号] I --|Using temporary| K[需临时表——内存或磁盘代价] I --|Using index| L[覆盖索引——最优br/回表次数为零] I --|Using where| M[索引过滤——正常br/需关注 filtered% 值]EXPLAIN分析的关键维度矩阵维度指标优良差访问类型typeconst/eq_refref/rangeindex/ALL扫描行数rows 实际返回行 × 2 × 10 × 100过滤比例filtered 50% 10% 5%额外操作ExtraUsing indexUsing whereUsing filesort/temporary索引长度key_len精确匹配索引列部分使用索引0(NULL)三、索引优化的六种实战技巧-- 技巧 1: 联合索引的最左前缀——where 条件必须从联合索引的首列开始 -- ❌ 索引 idx_user_status_time(user_id, status, create_time) 无法被以下查询使用 status 列 SELECT * FROM orders WHERE status paid AND create_time 2025-01-01; -- ✅ 调整索引顺序或补充单列索引 ALTER TABLE orders ADD INDEX idx_status_time(status, create_time); -- 技巧 2: 覆盖索引消除回表——SELECT 列全部包含在索引中 -- ❌ 需要回表读取 title, content 列 SELECT title, content FROM posts WHERE author_id 100 ORDER BY create_time DESC LIMIT 20; -- ✅ 建立覆盖索引 (author_id, create_time, title, content) ALTER TABLE posts ADD INDEX idx_cover(author_id, create_time, title, content); -- Extra 显示 Using index —— 零回表 -- 技巧 3: 避免函数包裹索引列——优化器无法使用索引 -- ❌ 对索引列应用函数导致失效 SELECT * FROM users WHERE DATE(register_time) 2025-01-15; -- ✅ 改写为范围查询 SELECT * FROM users WHERE register_time 2025-01-15 00:00:00 AND register_time 2025-01-16 00:00:00; -- 技巧 4: 前缀索引——大索引列的空间效率与选择性权衡 -- 对 VARCHAR(768) 的电商 SKU 码建立前缀索引 ALTER TABLE products ADD INDEX idx_sku_prefix(sku(12)); -- 用以下查询评估前缀选择性 95% 即认为可接受 SELECT COUNT(DISTINCT LEFT(sku, 12)) / COUNT(*) FROM products; -- 技巧 5: JOIN 的驱动表选择——小表驱动大表 -- ❌ 大表驱动小表orders 10M 行users 10K 行 SELECT u.name, o.amount FROM orders o JOIN users u ON o.user_id u.id; -- ✅ 小表驱动大表——优化器自动选择但可用 STRAIGHT_JOIN 强制 SELECT u.name, o.amount FROM users u STRAIGHT_JOIN orders o ON u.id o.user_id; -- 技巧 6: 分页深翻——OFFSET 的性能灾难 -- ❌ OFFSET 1000000 需要扫描并丢弃前 100 万行 SELECT * FROM articles ORDER BY id LIMIT 20 OFFSET 1000000; -- ✅ 基于游标的分页 SELECT * FROM articles WHERE id 1000000 ORDER BY id LIMIT 20;四、索引的成本写入放大与维护开销写入性能代价每个二级索引在 INSERT/UPDATE/DELETE 时都需要同步维护 B-Tree 的插入和页分裂。3 个索引意味着写放大率达到 4 倍1 主键 3 个二级索引。在高写入速率10K QPS下索引维护的 I/O 可能超过数据本身。页分裂与空间碎片B-Tree 的页分裂导致索引物理页利用率下降特别是在 UUID 作为主键时随机插入导致频繁页分裂。使用自增主键可将页利用率从 50%~70% 提升至 90%。索引选择性评估COUNT(DISTINCT col) / COUNT(*)低于 5%~10% 的列不建议单独建索引。例如gender2 个值的索引选择性为 0.01%——MySQL 优化器宁可全表扫描也不愿回表 50% 的行。索引覆盖的维护成本包含业务字段的宽覆盖索引如包含 TEXT 列在每次 UPDATE 覆盖列时触发索引页更新。覆盖索引的目标是消除 SELECT 的回表但代价是增大了 UPDATE 的写入负担——必须在读写比中做取舍。五、总结MySQL 索引优化遵循三个核心原则能用索引 ! 用好索引必须从 type、rows、extra 三个维度综合判断覆盖索引优于回表索引包含 SELECT 全部列时最优索引不是越多越好每个索引都增加写入成本。排查路径先用EXPLAIN FORMATJSON获取成本模型信息锁定 rows 与 filtered 不匹配的查询再用pt-query-digest从慢查询日志中找到重复出现的高频慢 SQL最后按索引失效模式逐项修复——注意不要一次添加多个索引后仅看效果应每次变更一个索引并对比前后的EXPLAIN输出与执行时间。索引优化的本质是空间换时间的工程权衡每一列是否建索引都应有数据支撑而非经验判断。
