MUYI · 后端工程师的生产级知识体系

Appearance

数据库语句优化手段 ​

声明：默认指 MySQL 的 InnoDB 存储引擎，以 MySQL 8.0 为主视角，涉及 5.7/8.0 行为差异处会单独标注。

一句话概括 SQL 优化的方法论：定位 → 分析 → 改写/加索引 → 验证。面试时最有价值的不是背"多少条手段"，而是能讲清楚三件事：

  • 慢是怎么被发现的。
  • 为什么慢。
  • 改了之后怎么证明变快了。

1 优化方法论总览 ​

  慢在哪？            为什么慢？              怎么改？                 怎么证明？
+-----------+      +----------------+      +------------------+     +-----------+
| 慢查询日志 | -->  | EXPLAIN 执行计划 | -->  | 索引 / SQL 改写   | --> | 对比耗时   |
| 应用监控   |      | EXPLAIN ANALYZE |      | 表结构 / 参数     |     | rows/成本 |
| show proce|      | 锁等待/IO 视角   |      | 读写分离/分库分表 |     | 慢日志回归 |
+-----------+      +----------------+      +------------------+     +-----------+
  • 先定位"哪几条 SQL 吃掉了大部分资源"（二八原则：少数慢 SQL 占了大部分总耗时）；
  • 再分析是索引问题、写法问题、还是数据量到了架构的临界点；
  • 改完必须量化验证，避免"感觉变快了"。

2 定位慢 SQL 的手段 ​

  • 慢查询日志：slow_query_log = ON，long_query_time（生产常设 0.5 s 或 1 s；排查期可临时调小到 0.1 并短开）。配合 pt-query-digest 按指纹聚合，直接给出 TOP N 慢 SQL；
  • 实时排查：SHOW PROCESSLIST 看当前正在跑的语句和状态（Sending data、Waiting for table lock、Sorting result 都是有信息量的状态）。遇到锁等待用 performance_schema 的 data_locks/data_lock_waits（8.0）。5.7 及以前是 information_schema.innodb_locks/innodb_lock_waits，8.0 已移除；
  • sys schema / performance_schema：sys.statements_with_full_table_scans、sys.schema_index_statistics 这类现成视图，8.0 开箱即用；
  • 应用层：APM（SkyWalking/Arthas trace）定位到具体接口里的具体 SQL，比从数据库侧倒推更快。

3 EXPLAIN 精读（分析手段的核心） ​

sql
EXPLAIN SELECT ...;

重点看 4 列：

  • type（访问类型），从好到坏：
级别含义典型场景
system/const主键或唯一索引等值查询，最多一行WHERE id = 1
eq_refJOIN 时被驱动表走主键/唯一索引JOIN b ON b.id = a.bid
ref普通二级索引等值查询WHERE idx_col = 'x'
range索引范围扫描WHERE id > 100、IN (...)
index扫整棵索引树（比全表好一点，免回表时还行）覆盖索引下的 count
ALL全表扫描需要优化
  • key/key_len：实际使用的索引及使用到的字节数，可反推联合索引用到第几列；
  • rows/filtered：优化器估算的扫描行数与过滤后比例（估算值，不等于真实值；8.0.18+ 用 EXPLAIN ANALYZE 可看真实行数与耗时）；
  • Extra：
值含义好坏
Using index覆盖索引，免回表好
Using index conditionICP 索引条件下推好（省回表）
Using where服务层再过滤中性
Using filesort额外排序大结果集时要警惕
Using temporary中间临时表GROUP BY/DISTINCT 常见，要警惕
Using join buffer (Block Nested Loop)被驱动表无索引，5.7 会用 BNL坏
Using join buffer (hash join)8.0.18+ 无索引 JOIN 时的哈希连接比 BNL 好，但仍建议补索引

4 索引层优化手段 ​

  • 覆盖索引：把 SELECT、WHERE、ORDER BY 涉及的列都放进联合索引，Extra: Using index 免回表，是 SQL 优化里性价比最高的一招；
  • 最左前缀对齐：联合索引列顺序 = 等值条件列在前、范围条件列在后、排序列衔接 WHERE 列。已有 (a) 再想要 (a,b) 时直接扩展原索引而不是新建；
  • 前缀索引：长字符串列取前 N 个字符建索引节省空间。COUNT(DISTINCT LEFT(col, N)) / COUNT(*) 趋于饱和的 N 即可选。代价是无法用于覆盖索引和 ORDER BY；
  • 函数索引（8.0.13+）：CREATE INDEX idx_y ON t ((YEAR(create_time)));，让"列上带函数"的查询也能走索引；5.7 做不到，只能改写 SQL；
  • 索引列保持干净：WHERE YEAR(t)=2024 改成区间。字符串列与数字比较时给常量加引号，避免隐式转换导致索引失效。细节见同目录《数据库索引失效总结》；
  • 删索引走灰度：8.0 可先把索引设为 INVISIBLE 观察，无退化再 DROP；5.7 建议用 gh-ost/pt-osc 在线操作。

5 SQL 写法层优化手段 ​

5.1 只查需要的列 ​

SELECT * 的三宗罪：无法覆盖索引（必然回表）、拉宽网络与内存占用、破坏通过视图/代理层的列变更兼容性。列出明确列名是底线。

5.2 深分页优化（高频考点） ​

sql
-- 反面：OFFSET 越大越慢，MySQL 要取出并丢弃前 1000000 行
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

-- 方案一：游标/书签（连续翻页、上页最大 id 已知时，最优）
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;

-- 方案二：延迟关联（先在覆盖索引上拿 id，再回表取 10 行）
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 10) t ON o.id = t.id;

原理：慢在"回表 100 万次"；先用覆盖索引把目标 10 行的主键找出来，回表次数从 100 万+10 降到 10。

5.3 JOIN 优化 ​

  • 小结果集驱动大表（优化器一般会自动选，被驱动表的关联列必须有索引，争取 eq_ref/ref）。确认选错顺序时用 STRAIGHT_JOIN 强制；
  • 关联两侧类型、字符集、collation 必须一致，否则隐式转换导致索引失效；
  • 无索引 JOIN：5.7 走 Block Nested Loop（被驱动表被反复全扫，极慢）。8.0.18+ 自动改用 hash join（8.0.20 起彻底移除 BNL），能救急但不是不加索引的理由；
  • 大表 JOIN 小表且数据可冗余时，考虑应用层组装代替 JOIN（分库分表后更是标准做法）。

5.4 子查询、IN 与 EXISTS ​

  • 老经验"小表 IN 大表、大表 EXISTS 小表"源自 5.5 时代相关子查询的低效实现。5.6+ 优化器会把符合条件的 IN 子查询改写为半连接（semijoin），两者差距已大幅缩小；
  • NOT IN (子查询) 仍容易踩相关子查询的坑，推荐 NOT EXISTS 或 LEFT JOIN ... IS NULL；
  • IN 列表过长（几千个值）时改为临时表 JOIN 或改范围条件。

5.5 写操作优化 ​

  • 批量插入：INSERT ... VALUES (...),(...),(...) 合并成批（一批几百到几千行），比逐条插快一个量级；更大量级用 LOAD DATA（配合 local_infile）；
  • 大批量删除/更新分批做：DELETE ... LIMIT 1000 循环 + 间歇提交，避免长事务、大段 undo、主从延迟；跨库归档用 pt-archiver；
  • 避免在事务里做 RPC/耗时计算：长事务撑大 undo、占锁、拖垮 MVCC，是生产事故常客；
  • UPDATE/DELETE 前先 EXPLAIN 或同条件 SELECT 确认影响行数。

5.6 其他高频细节 ​

  • UNION ALL 代替 UNION（后者要临时表去重，业务允许时省掉）；
  • COUNT(*) 就用这个写法（InnoDB 下 COUNT(*)≈COUNT(1)，都会挑最小的索引来扫；COUNT(列) 忽略 NULL 语义不同别混用）；
  • GROUP BY 想免临时表：分组列与索引前缀一致且 WHERE 是等值前缀。8.0 已移除 GROUP BY 隐式排序，依赖排序的老 SQL 需显式写 ORDER BY——升级必查项；
  • 大事务里避免一次性 SELECT 百万行到应用内存，改分页流式处理。

6 架构层优化手段（SQL 改不动时） ​

  • 冷热分离/归档：历史大表拆到归档库，主表保持体量；
  • 读写分离：报表类查询走从库；注意复制延迟下的读己之写；
  • 分库分表：单表行数经验阈值千万级（更准确看 B+ 树高度和 buffer pool 命中率）。提前定好分片键，跨分片查询靠冗余/宽表；
  • 缓存：读多写少的热点数据上 Redis，注意一致性策略（延迟双删/binlog 订阅）；
  • 异步化：削峰场景把同步写库改为 MQ 异步落库。

7 参数层优化（简要） ​

  • innodb_buffer_pool_size：物理内存的 50%~75%，最重要的一个参数；
  • redo log 容量：innodb_redo_log_capacity（8.0.30+ 统一参数，替代旧的 innodb_log_file_size×innodb_log_files_in_group）太小会频繁 checkpoint 抖动；
  • 双一配置的取舍：innodb_flush_log_at_trx_commit=1 + sync_binlog=1 最安全最慢；非核心业务可放宽（如 2/100）换吞吐；
  • max_connections 结合连接池（应用侧 HikariCP 限流）一起治理，避免连接风暴。

8 MySQL 8 特有能力清单（答题时的版本加分项） ​

能力版本对优化的意义
降序索引真正生效8.0混合序 ORDER BY a ASC, b DESC 免 filesort（5.7 忽略 DESC）
跳跃扫描 Skip Scan8.0.13前导列取值少时，跳过前导列也能用联合索引
函数/表达式索引8.0.13列上带函数的查询可以建索引
hash join8.0.18（8.0.20 移除 BNL）无索引 JOIN 的兜底，性能远好于 BNL
EXPLAIN ANALYZE8.0.18真实执行并输出每步实际耗时/行数
Instant DDL8.0.12（ADD COLUMN），8.0.29 扩展到 DROP COLUMN秒级加列不锁表（5.7 只能 gh-ost/pt-osc）
直方图统计8.0无索引列的分布信息辅助成本估算
CTE / 窗口函数8.0复杂分析逻辑（排名、分组 TopN）不用再写多层自连接

9 追问链（面试官会怎么往下问） ​

  • Q：线上怎么发现慢 SQL？ A：慢查询日志 + pt-query-digest 聚合出 TOP N，配合 APM 定位接口；实时问题用 show processlist 和 sys schema。
  • Q：EXPLAIN 主要看什么？ A：type 是否 ALL、key/key_len 用没用上/用到第几列、rows 估算量级、Extra 有没有 filesort/temporary/join buffer。
  • Q：rows 是准的吗？ A：是估算值，基于索引统计（基数）可能偏差很大；ANALYZE TABLE 重新采样，8.0.18+ 用 EXPLAIN ANALYZE 验证真实行数。
  • Q：LIMIT 1000000,10 为什么慢？怎么改？ A：要扫描并丢弃前 100 万行、且每行都可能回表。用游标（WHERE id > 上页最大 id）或延迟关联（覆盖索引先取 id）。
  • Q：为什么加了索引反而更慢/不走？ A：区分度低回表成本高于全表扫描。或触发隐式转换/函数/最左前缀这类失效场景。统计信息过期也会误判。
  • Q：8.0 无索引 JOIN 还会像 5.7 那么惨吗？ A：8.0.18+ 自动 hash join，比 BNL 好很多，但正确做法仍是给关联列补索引。
  • Q：单表多少数据要分库分表？ A：经验值千万行/单表 10 GB 量级开始规划。但真正的信号是 B+ 树长高、buffer pool 命中率下降、DDL/维护窗口不可接受。

10 小结 ​

  • 优化有固定套路：定位（慢日志）→ 分析（EXPLAIN）→ 手段（索引/写法/架构/参数）→ 验证（对比耗时与执行计划）；
  • 索引层三板斧：覆盖索引、最左前缀对齐、索引列保持干净；
  • 写法层高频考点：深分页（游标+延迟关联）、批量写、JOIN 列对齐类型、UNION ALL；
  • 数据量到架构临界点时，SQL 层优化收益有限，靠冷热分离/分库分表/缓存解决；
  • MySQL 8 的新能力（函数索引、Skip Scan、hash join、EXPLAIN ANALYZE、Instant DDL）既是优化工具也是面试版本差异考点。

2026-10 依据主题重写（原文件为空壳）。