知识点深化 · 数据库 · SQL 优化
SQL 优化：EXPLAIN、慢查询、索引失效场景
SQL 慢了先别瞎改，用 EXPLAIN 看执行计划：type 是否全表扫描、key 用没用上索引、rows 扫了多少行。这一页把 EXPLAIN 关键列、慢查询排查套路、索引失效场景讲透。
① 小白第一课怎么学（4 步走，约 60 分钟）
别急着背代码，先按这四步建立直觉：
1看图建立直觉（10 分钟）
读②③：执行计划长什么样。
2记 EXPLAIN 列（15 分钟）
读④：type/key/rows/Extra。
3手分析 SQL（20 分钟）
精读⑤。
4刷题纠错（15 分钟）
做⑦⑩，错题回⑥。
本课小目标学完你要能：① 读懂 EXPLAIN 的 type/key/rows；② 识别常见索引失效；③ 按套路优化慢 SQL。
② 一图看懂：SQL 优化全地图
读法：中心是 SQL 优化，左 EXPLAIN 右慢查询，下是失效场景与套路。
③ 本质直觉：EXPLAIN 是给 SQL 拍 X 光
慢 SQL 怎么办：先在 SQL 前加 EXPLAIN，看执行计划。重点看四列：
type：访问类型。const>eq_ref>ref>range>index>ALL。出现 ALL（全表扫描）就要优化。
key：实际用了哪个索引；NULL 表示没用索引。rows：预估扫描行数，越大越慢。Extra：出现 Using filesort、Using temporary 就要警惕。
慢查询日志设置 long_query_time=1（秒），超过的 SQL 会记录到 slow log，定期用 mysqldumpslow 汇总找出最频繁/最慢的 SQL。
④ 完整体系与对比表
EXPLAIN 关键列
| 列 | 含义 | 关注点 |
|---|
| type | 访问类型 | 避免 ALL |
| key | 实际用的索引 | NULL 说明没走索引 |
| rows | 预估扫描行数 | 越小越好 |
| Extra | 额外信息 | Using index 好；filesort/temporary 坏 |
索引失效场景清单
① 对索引列做函数/运算：where DATE(create_time)='2024-01-01'；② 隐式类型转换；③ like 前导 %；④ 联合索引非最左前缀；⑤ or 两边不全有索引；⑥ 优化器判断全表更快（数据量小）；⑦ not in / != 可能不走索引。
优化口诀EXPLAIN 先看 type 是不是 ALL；建联合索引贴合 where；避免 select * 走覆盖索引；大分页用游标/延迟关联
⑤ 用法场景与典型例题
例1（诊断）EXPLAIN 显示 type=ALL、key=NULL，说明什么？
全表扫描没走索引。
全表扫描，没用到索引。要给 where 条件列建索引。
例2（改写）where DATE(create_time)='2024-01-01' 索引失效，怎么改？
不要对列用函数。
改成范围：where create_time >= '2024-01-01' and create_time < '2024-01-02'，就能走索引。
例3（大分页）limit 1000000,10 很慢为什么？
offset 大要扫很多行丢弃。
深分页 offset 大时 MySQL 要扫描前 1000010 行再丢掉前 100 万。改成游标：where id > 上次最大id limit 10。
做题心法先 EXPLAIN 再动手；type=ALL 和 key=NULL 是两大红灯；对索引列别包函数。
⑥ 高频错误诊断（4 条）
错误1：以为建了索引就一定走优化器认为全表更快就不走（小表），或列选择性太差（性别）也不走。
错误2：select * 到处用多取列可能触发回表，且用不上覆盖索引。只查需要的列。
错误3：对索引列用函数where YEAR(create_time)=2024 让索引失效，改范围。
错误4：大 offset 深分页limit 1000000,10 扫百万行，用游标分页优化。
⑦ 考点真题演练（4 题）
考点分布
| 考法 | 出题形式 | 应对 |
|---|
| EXPLAIN | type=ALL 含义 | 全表扫描 |
| 索引失效 | 对列用函数 | 改范围查询 |
| 慢查询 | 怎么定位 | 慢查询日志 + EXPLAIN |
| 深分页 | limit 大 offset 怎么办 | 游标分页 |
真题基础1. EXPLAIN 中 type 列值为 ALL 表示？
真题中档2. 下列哪个写法会导致索引失效？
真题中档3. EXPLAIN 的 Extra 列出现 Using filesort 表示？
真题拔高4. 深分页 limit 1000000,10 性能差，优化方法是？
⑧ 必背知识点卡
EXPLAIN：type/key/rows/Extra
type：const>ref>range>index>ALL 避免 ALL
key：NULL=没走索引
失效：函数/转换/前导%/非最左
覆盖：Extra 显示 Using index 免回表
慢日志：long_query_time 记录 mysqldumpslow
深分页：游标代替大 offset
⑨ 应用输出：从慢日志到优化一条 SQL
场景：慢日志报告某条订单查询耗时 3 秒，每天调用上万次。
① 拿 SQL：从 slow log 找到最慢 SQL。
② EXPLAIN：type=ALL，rows=500 万，没走索引。
③ 建索引：给 where 条件 user_id+status 建联合索引。
④ 改写：把对 create_time 的函数改成范围；去掉 select *。
⑤ 复测：type=ref，rows 降到几百，耗时从 3 秒变 10ms。
口述思路合上书说："慢 SQL 先 EXPLAIN 看 type 和 key，建索引贴合 where，函数改范围。"
⑩ 分层练习（基础 + 中档 + 拔高）
▍基础 6 题
基础1EXPLAIN 看什么列？
type、key、rows、Extra。
基础3key=NULL 说明什么？
没用到索引。
基础4慢查询日志叫什么？
slow query log。
基础6Extra 里 Using index 好吗？
好，覆盖索引免回表。
▍中档 6 题
中档7like '%张' 走索引吗？
不走，前导通配符。
中档8为什么避免 select *？
多取列可能回表，用不上覆盖索引。
中档9慢查询阈值参数？
long_query_time。
中档10Extra 出现 Using temporary 说明？
用了临时表，常见 group by 缺索引。
中档11联合索引 where a=? and c=? 走索引吗？
只用到 a，c 因跳过 b 走不全。
中档12数据量很小时为什么不走索引？
优化器算全表扫描代价更低，正常。
▍拔高 6 题
拔高13什么是索引下推 ICP？
存储引擎层先用索引条件过滤，减少回表。
拔高14深分页为什么用游标？
避免扫描并丢弃大量行。
拔高15explain 的 rows 是精确值吗？
是预估值，基于统计信息。
拔高16什么是延迟关联优化分页？
先通过索引查出主键，再 join 回原表取列。
拔高17统计信息过期导致选错索引怎么办？
analyze table 刷新统计信息。
拔高18force index 什么时候用？
优化器选错索引时强制指定，但慎用。
⑪ 记忆口诀 + 7 天复习计划
三句口诀① 慢 SQL 先 EXPLAIN，type=ALL key=NULL 是红灯。② 函数转换前导模糊，索引全失效。③ 覆盖索引免回表，深分页用游标。
| 天 | 任务 | 自检 |
|---|
| 第 1 天 | 读②③④，画 EXPLAIN 列 | 四列含义对 |
| 第 2 天 | 背失效场景 + 基础 1-6 | 红灯认对 |
| 第 3 天 | 做中档 7-12，诊断 ALL | 能改写 SQL |
| 第 4 天 | 做拔高 13-18，讲延迟关联 | 能讲清游标 |
| 第 5 天 | 做⑦真题 4 题 | 限时每题 2 分钟 |
| 第 6-7 天 | 合上书默写优化口诀 | 不看资料全默对 |
← 返回软件技术总览