⚡ SQL 优化
EXPLAIN 执行计划 · 慢 SQL 排查 · 分页/连接/聚合优化 · 大表治理思路
1. EXPLAIN 怎么用？关键字段怎么读？
EXPLAIN SELECT ... 查看执行计划。关键列：
- type（访问类型，性能从好到差）：
system > const > eq_ref > ref > range > index > ALL- const：主键/唯一索引等值（最快）
- eq_ref：join 中按主键取一行
- ref：普通索引等值
- range：索引范围（>、BETWEEN、IN）
- index：扫整个索引树（比 ALL 略好）
- ALL：全表扫描——优化的目标
- key：实际用的索引；key_len：索引长度（可判断联合索引用了几列）
- rows：预估扫描行数（越小越好）
- Extra（重要提示）：
- Using index：覆盖索引，好
- Using index condition：索引下推，好
- Using where：WHERE 条件在 Server 层过滤（引擎返回行之后）——与是否回表无关，覆盖索引 + 无法下推的条件也会同时出现 Using index 与 Using where
- Using filesort：文件排序（未用索引排序），大数据量要优化
- Using temporary：临时表（GROUP BY/DISTINCT 常见），要优化
🎯 面试要点
- type 达到 range/ref 一般可接受，ALL 且 rows 大要处理
- EXPLAIN ANALYZE（8.0）能给出实际执行时间和行数，更准确
- 避免 filesort：ORDER BY 与索引列顺序一致；避免临时表：GROUP BY 用索引
2. 线上慢 SQL 如何排查和优化？（完整流程）
- 开启慢查询日志：long_query_time=1（超过 1 秒记录）；
SHOW VARIABLES LIKE 'slow_query_log'确认 - 分析工具：mysqldumpslow 汇总慢日志（按耗时/次数排序），或 pt-query-digest 更专业
- EXPLAIN 定位问题：type=ALL？没走索引？filesort？临时表？
- 对症优化：
- 缺索引 → 补索引（先看 where/order/join 列）
- 索引失效 → 改写 SQL（去掉函数/隐式转换）
- 大字段回表 → 覆盖索引 / 少 select *
- 数据量太大 → 归档、分页改造、分库分表
- 锁等待 → 看是否长事务、行锁升级表锁
- 验证：优化后 EXPLAIN rows 明显下降、执行时间达标
🎯 面试要点
- 慢 SQL 处理优先级：先看是否没走索引，再看是否需要重构 SQL，最后才考虑分表
- 监控：SHOW PROCESSLIST 看当前执行的 SQL（谁在跑、跑了多久）
- 避免在 where 中做大量计算——把计算放应用层或加冗余字段
3. 深分页为什么慢？怎么优化？
LIMIT 100000, 20 慢的原因：MySQL 要扫描并丢弃前 100000 行（只能一条条跳过，无法直接定位）。数据越大越慢。
优化方案：
- 游标分页（推荐）：
WHERE id > 上一页最后一条的 id ORDER BY id LIMIT 20——直接走索引定位 - 延迟关联：先用覆盖索引查出 id 再回表取行：
SELECT * FROM t JOIN (SELECT id FROM t ORDER BY id LIMIT 100000, 20) tmp ON t.id = tmp.id - 禁止"跳页"的场景（用户只能翻下一页）适合游标；必须跳页则延迟关联
🎯 面试要点
- 游标分页需要排序字段唯一稳定（id 天然满足）
- ORDER BY 非索引列 + LIMIT 大偏移 = 双倍慢（filesort + 跳过）
- 大数据量表的分页尽量让产品限制深度（如最多 100 页）
4. 日常 SQL 优化的好习惯？
- SELECT 只取需要的列，避免 select *（回表 + 网络开销）
- 避免隐式类型转换：phone 是 varchar 就别传数字（等值失效）
- 批量插入用多值 INSERT 或分批 commit，别一条条插
- JOIN 字段类型/字符集一致；小表驱动大表（优化器一般自动）
- 用 EXISTS 替代 IN（子查询结果集大时）；IN 量大时考虑 JOIN 或拆批
- 避免在索引列上做运算/函数（见索引失效）
- 合理使用冗余字段/汇总表（统计场景），用空间换时间
- 数据删除用分批 DELETE（每批 1000 条 + sleep），避免长事务锁表
🎯 面试要点
- 优化大方向：少访问（索引）→ 少回表（覆盖）→ 少传输（精简列）→ 少排序（索引排序）
- 不要过度优化：QPS 低、数据量小的 SQL 不值得加索引