第 14 章 SQL优化面试精讲

📖 本篇简介：SQL 优化是 MySQL 面试的"压轴大题"，从一条慢 SQL 到整套数据库架构，都离不开对索引、JOIN、子查询、分页、批量写等细节的把控。本篇基于 MySQL 8.0，系统讲解慢查询定位、索引设计原则、小表驱动大表、JOIN 优化、深分页优化、批量插入优化、避免 SELECT *、count 优化、子查询改 JOIN、LIMIT 优化等高频优化点，并给出可落地的优化清单。学完后你能拿到一条慢 SQL 按"索引→写法→执行计划→架构"四层逐步拆解。

目录

• 题目 1：SQL 优化的整体思路是什么，从哪入手
• 题目 2：慢查询日志怎么开启，怎么定位慢 SQL
• 题目 3：如何避免索引失效，列出常见失效场景
• 题目 4：小表驱动大表是什么意思，怎么落实到 SQL
• 题目 5：深分页 LIMIT 1000000,10 为什么慢，怎么优化
• 题目 6：SELECT * 有什么问题，怎么改
• 题目 7：大表 COUNT(*) 很慢怎么办
• 题目 8：JOIN 优化的原则，被驱动表为什么要加索引
• 题目 9：子查询一定比 JOIN 慢吗，怎么改写
• 题目 10：批量 INSERT 怎么写最快
• 题目 11：UPDATE/DELETE 大表为什么会卡，怎么优化
• 题目 12：OR 条件为什么会让索引失效，怎么改
• 题目 13：LIKE '%xx' 前缀模糊怎么办
• 题目 14：索引列上做运算/函数为什么失效
• 题目 15：隐式类型转换为什么会让索引失效
• 题目 16：LIMIT 100000,20 改成"书签式"怎么写
• 题目 17：事务太长为什么会影响性能
• 题目 18：分批次处理大表 UPDATE/DELETE 怎么写
• 题目 19：SQL 优化到极限还是慢，下一步做什么
• 题目 20：常见坑与注意事项
• 速查表：SQL 优化速记
• 复习清单
• 练习题

题目 1：SQL 优化的整体思路是什么，从哪入手

考点

• 优化四层：索引、SQL 写法、表结构、架构
• 先定位再优化
• EXPLAIN 验证

参考答案

1. 第一步：定位。通过慢查询日志、Performance Schema、业务埋点找到真正慢的 SQL，不要凭感觉。

2. 第二步：EXPLAIN。看 type 是否 ALL、是否 Using filesort/temporary、扫描行数 rows 多大。

3. 第三步：索引优化。加/改索引，让 type 升到 ref/range。

4. 第四步：SQL 写法。避免 SELECT *、深分页、大事务、不必要 JOIN。

5. 第五步：表结构。字段类型优化、适当冗余、冷热分离。

6. 第六步：架构。读写分离、分库分表、缓存、ES 检索。

7. 原则：先低成本（加索引/改写法）后高成本（分库分表），不要一上来就拆库。

追问

• 追问：优化后怎么验证？应对：EXPLAIN 看执行计划 + 实测响应时间 + 观察慢查询日志是否消失。
• 追问：SQL 优化 DBA 还是开发做？应对：开发主导写法与索引设计，DBA review 与架构层优化。

题目 2：慢查询日志怎么开启，怎么定位慢 SQL

考点

• slow_query_log 参数
• long_query_time 阈值
• 分析工具 mysqldumpslow/pt-query-digest

参考答案

-- 本示例基于 MySQL 8.0
-- 查看慢查询日志状态
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志（全局动态生效）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;   -- 超过 1 秒算慢查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

1. slow_query_log=ON：开启慢查询记录。

2. long_query_time：阈值，默认 10 秒，生产一般设 1 秒。

3. 未走索引的查询也记录：log_queries_not_using_indexes=ON。

4. 分析工具：

• mysqldumpslow -s t -t 10 slow.log：按时间排序取前 10；
• pt-query-digest：更专业的慢查询分析，输出 SQL 指纹统计。

追问

• 追问：慢查询日志影响性能吗？应对：写日志有少量开销，但比 SQL 本身慢的代价小得多；生产建议开。
• 追问：怎么避免慢查询日志爆磁盘？应对：定期切割 + 设置 max_long_query_size，配合 logrotate。

题目 3：如何避免索引失效，列出常见失效场景

考点

• 索引列上函数/运算
• 前导模糊
• 隐式类型转换
• OR/UNION/不满足最左前缀

参考答案

| 失效场景 | 错误写法 | 正确写法 |

|----------|----------|----------|

| 函数包列 | WHERE DATE(create_time)='2026-10-01' | WHERE create_time>='2026-10-01' AND create_time<'2026-10-02' |

| 列上运算 | WHERE age+1=20 | WHERE age=19 |

| 前导模糊 | WHERE name LIKE '%张' | 改全文索引/ES |

| 隐式转换 | phone 是 VARCHAR，WHERE phone=13800000000 | WHERE phone='13800000000' |

| 联合索引非最左 | idx(a,b,c) WHERE b=1 | 调整索引或改写 |

| OR 含无索引列 | WHERE a=1 OR b=2（b 无索引） | 改 UNION ALL |

| NOT IN / != | WHERE dept!='研发' | 评估业务能否改 IN |

| IS NULL | 部分情况可走索引 | MySQL 8.0 优化后可走 |

追问

• 追问：NOT LIKE 走索引吗？应对：一般不走，相当于范围否定，优化器倾向全表扫。
• 追问：OR 两边都有索引呢？应对：8.0 可能 index_merge，比全表扫好，但不如联合索引。

题目 4：小表驱动大表是什么意思，怎么落实到 SQL

考点

• 嵌套循环 JOIN 原理
• 小结果集做驱动表
• WHERE 先过滤

参考答案

1. 嵌套循环 JOIN：驱动表每一行，都去被驱动表查一次。驱动表行数越少，循环次数越少。

2. 小表驱动大表：把经过 WHERE 过滤后结果集小的表做驱动表。

3. 落实手段：

• WHERE 条件先过滤驱动表；
• 被驱动表的 JOIN 列加索引，每次查询 O(log N)；
• 优化器一般自动选择，但可用 STRAIGHT_JOIN 强制。

-- dept 表小（100 行），emp 表大（百万行）
-- 让 dept 做驱动表，emp.dept_id 有索引
SELECT e.name, d.name
FROM dept d JOIN emp e ON e.dept_id = d.id
WHERE d.name = '研发';   -- 先过滤 dept 到 1 行，再循环 emp

追问

• 追问：优化器会选错驱动表吗？应对：统计信息不准时会，用 STRAIGHT_JOIN 或 FORCE INDEX 干预。
• 追问：被驱动表没索引会怎样？应对：Block Nested Loop，用 join_buffer 批量扫，性能差。

题目 5：深分页 LIMIT 1000000,10 为什么慢，怎么优化

考点

• OFFSET 扫描大量行丢弃
• 书签式/延迟关联
• 业务限制

参考答案

1. 慢的原因：LIMIT 1000000,10 要先扫描 1000010 行，再丢弃前 1000000 行，大量 I/O 浪费。

2. 优化 1：书签式（游标）分页：记住上一页最后一条的 id，下次从它往后查。

-- 错误：OFFSET 大
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

-- 正确：书签式
SELECT * FROM orders WHERE id > 上一页最大id ORDER BY id LIMIT 10;

3. 优化 2：延迟关联：先用覆盖索引查到 id，再回表取行。

SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 10) t
ON o.id = t.id;

4. 业务限制：超过 100 页的分页本身不合理，引导用户用搜索/筛选。

追问

• 追问：书签式分页有什么缺点？应对：不能跳页，只能上一页/下一页。
• 追问：要跳页怎么办？应对：限制最大页数，或用 ES 等外部搜索引擎。

题目 6：SELECT * 有什么问题，怎么改

考点

• 不需要的列浪费 I/O 与网络
• 无法走覆盖索引
• 字段顺序变化风险

参考答案

1. 浪费 I/O：多取不需要的列，尤其 TEXT/BLOB 字段，读数据页代价大。

2. 无法覆盖索引：SELECT * 必然回表，失去覆盖索引优势。

3. 网络开销：多行多列，传输体积大。

4. 耦合风险：表结构加字段后 SELECT * 会把新字段也带出来，可能影响缓存/序列化。

5. 改法：只写需要的列名。

-- 不推荐
SELECT * FROM emp WHERE dept='研发';

-- 推荐
SELECT name, salary, hire_date FROM emp WHERE dept='研发';

追问

• 追问：SELECT * 配合 LIMIT 1 呢？应对：仍然建议写列名，代码可读性与稳定性更好。
• 追问：用 SELECT * 查 count 怎么办？应对：直接 COUNT(*)，不要 SELECT * 再算。

题目 7：大表 COUNT(*) 很慢怎么办

考点

• InnoDB 无元数据缓存
• 近似值/汇总表
• 业务替代

参考答案

1. InnoDB 的 COUNT(*)：要遍历索引/数据页逐行计数，没有元数据 O(1)。

2. 优化方案：

• 近似值：SHOW TABLE STATUS LIKE 'emp' 的 rows 列是估算值，误差 10%~50%，适合页面展示；
• 汇总表：建一张计数表，业务 INSERT/DELETE 时同步更新；
• Redis：用 Redis 存计数，异步更新；
• WHERE 条件简单时：让优化器走最小索引扫描。

-- 近似值
SHOW TABLE STATUS LIKE 'emp';   -- 看 rows 列

-- 汇总表示例
CREATE TABLE stats_cnt(k VARCHAR(32) PRIMARY KEY, cnt BIGINT);
INSERT INTO stats_cnt(k,cnt) VALUES ('emp_total', 0)
ON DUPLICATE KEY UPDATE cnt=cnt+1;  -- 每次 INSERT 时 +1

追问

• 追问：MyISAM 为什么 COUNT(*) 快？应对：MyISAM 把行数存在元数据里，无 WHERE 时 O(1)。
• 追问：带 WHERE 的 COUNT(*) 能优化吗？应对：能走索引就走索引；大表复杂条件还是要预聚合。

题目 8：JOIN 优化的原则，被驱动表为什么要加索引

考点

• 嵌套循环
• 被驱动表 JOIN 列索引
• 减少回表

参考答案

1. 原则 1：被驱动表的 JOIN 列必须加索引，否则每次驱动表一行都全表扫。

2. 原则 2：驱动表尽量小（WHERE 过滤后）。

3. 原则 3：SELECT 的列尽量都在索引中，形成覆盖索引。

4. 原则 4：单表 JOIN 数量别太多（一般不超过 3~5 张），过多优化器选优困难。

5. EXPLAIN 看：被驱动表 type 至少要 ref，不能 ALL；Extra 不能有 Using join buffer。

-- emp 大表，dept 小表
-- 给 emp.dept_id 加索引，JOIN 时被驱动表 emp type=ref
CREATE INDEX idx_dept_id ON emp(dept_id);

SELECT e.name, d.name
FROM dept d JOIN emp e ON e.dept_id = d.id;

追问

• 追问：LEFT JOIN 哪个是驱动表？应对：LEFT JOIN 左表永远是驱动表，右表是被驱动表。
• 追问：JOIN 太多表怎么办？应对：拆成多次查询在应用层组装，或冗余字段减少 JOIN。

题目 9：子查询一定比 JOIN 慢吗，怎么改写

考点

• 相关子查询 O(N²)
• 改 JOIN 让优化器一次扫描
• 半连接优化

参考答案

1. 不一定：8.0 优化器对 IN/EXISTS 子查询做了大量半连接优化，很多场景自动改写成 JOIN。

2. 相关子查询：WHERE EXISTS (SELECT 1 FROM b WHERE b.a_id=a.id) 对外层每行执行一次，慢。

3. 改写：改成 JOIN 或 semi-join。

-- 慢：相关子查询
SELECT * FROM emp e
WHERE EXISTS (SELECT 1 FROM dept d WHERE d.id=e.dept_id AND d.name='研发');

-- 改写：JOIN
SELECT DISTINCT e.* FROM emp e JOIN dept d ON e.dept_id=d.id
WHERE d.name='研发';

4. 注意：改写时注意 DISTINCT 去重，避免 JOIN 产生重复行。

追问

• 追问：IN 和 EXISTS 哪个快？应对：小表用 IN，大表用 EXISTS（8.0 优化器会自动选）。
• 追问：FROM 里的派生表会慢吗？应对：8.0 有 derived_condition_pushdown，把外层条件下推，比 5.7 快很多。

题目 10：批量 INSERT 怎么写最快

考点

• 多值 INSERT
• 批量提交事务
• 关闭自动提交
• bulk_insert_buffer_size

参考答案

-- 慢：逐条 INSERT（每次一个事务）
INSERT INTO t(name) VALUES ('a');
INSERT INTO t(name) VALUES ('b');

-- 快：多值 INSERT，一个事务
INSERT INTO t(name) VALUES ('a'),('b'),('c'),('d');

-- 更快：LOAD DATA INFILE
LOAD DATA INFILE '/tmp/data.csv' INTO TABLE t FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

1. 多值 INSERT：把 N 条合并成一条 SQL，减少网络往返与事务提交次数。

2. 事务批量提交：每 1000~5000 条 COMMIT 一次，别一条一提交。

3. LOAD DATA INFILE：比 INSERT 快 20 倍以上，适合初始化/大批量导入。

4. 关唯一检查/外键检查（导入期间）：SET unique_checks=0; SET foreign_key_checks=0;，导完恢复。

追问

• 追问：一次 INSERT 多少条合适？应对：500~1000 条一批，别一次几万条（binlog/redo 压力大）。
• 追问：LOAD DATA 安全吗？应对：是 MySQL 官方推荐的批量导入方式，注意文件权限。

题目 11：UPDATE/DELETE 大表为什么会卡，怎么优化

考点

• 大事务锁范围大
• undo/redo 压力
• 主从延迟
• 分批处理

参考答案

1. 卡的原因：

• 一次性更新百万行，事务巨大，undo 日志膨胀；
• 锁范围大，阻塞其他业务；
• binlog 刷盘压力大，主从延迟；
• 主库更新大，从库单线程回放延迟高。

2. 优化：

• 分批 UPDATE/DELETE，每批 1000~5000 行；
• 每批 COMMIT 后 sleep 一小段；
• 业务低峰执行；
• 避免一次性 DELETE 全表，用 TRUNCATE（DDL，不可回滚）。

-- 分批删除：每次删 2000 条
DELETE FROM orders WHERE status='CLOSED' AND create_time<'2025-01-01' LIMIT 2000;
-- 循环执行直到 affect_rows=0，每批之间 sleep 1 秒

追问

• 追问：TRUNCATE 与 DELETE 区别？应对：TRUNCATE 是 DDL，重建表，不可回滚，不记逐行 binlog，快但危险。
• 追问：大表 DDL 怎么做？应对：用 pt-online-schema-change 或 gh-ost，避免锁表。

题目 12：OR 条件为什么会让索引失效，怎么改

考点

• OR 两边都有索引才能 index_merge
• 一边无索引就全表扫
• 改 UNION ALL

参考答案

1. OR 失效场景：OR 连接的多个条件中，只要有一列没索引，MySQL 倾向全表扫描。

2. 两边都有索引：8.0 可能用 index_merge，合并两个索引结果。

3. 改法：把 OR 改成 UNION ALL。

-- OR：dept_id 有索引，job 无索引，全表扫
SELECT * FROM emp WHERE dept_id=5 OR job='工程师';

-- 改 UNION ALL
SELECT * FROM emp WHERE dept_id=5
UNION ALL
SELECT * FROM emp WHERE job='工程师';

追问

• 追问：index_merge 好吗？应对：一般，合并索引结果有开销；不如建联合索引。
• 追问：OR 都是同一个列呢？应对：dept_id=5 OR dept_id=6 等价 dept_id IN (5,6)，走 range。

题目 13：LIKE '%xx' 前缀模糊怎么办

考点

• B+ 树索引不支持前导模糊
• 全文索引 FULLTEXT
• 外部搜索引擎 ES

参考答案

1. 为什么失效：B+ 树按索引列值排序存储，前导模糊无法定位起点。

2. 方案 1：全文索引（MySQL 8.0 InnoDB 支持）：

-- 建全文索引
CREATE FULLTEXT INDEX ft_name ON emp(name);
-- 查询
SELECT * FROM emp WHERE MATCH(name) AGAINST('张三' IN NATURAL LANGUAGE MODE);

3. 方案 2：反转字符串：存一份 name_reverse，把 LIKE '%张' 变成 LIKE '张%'（仅适合后缀模糊）。

4. 方案 3：ES/搜索引擎：模糊搜索、分词、相关性排序，生产模糊搜索首选。

追问

• 追问：FULLTEXT 中文支持好吗？应对：需要 ngram 分词器，8.0 已支持；复杂搜索还是 ES 强。
• 追问：LIKE '张%' 走索引吗？应对：走，前缀匹配是 range。

题目 14：索引列上做运算/函数为什么失效

考点

• B+ 树存原值
• 函数后值无序
• 改写成范围

参考答案

-- 失效：函数包列
SELECT * FROM emp WHERE DATE(hire_date)='2026-10-01';
SELECT * FROM emp WHERE age+1=20;

-- 有效：改写成范围/等值
SELECT * FROM emp
WHERE hire_date >= '2026-10-01' AND hire_date < '2026-10-02';
SELECT * FROM emp WHERE age=19;

1. 原因：B+ 树按列原值排序，对列做函数后顺序被打乱，无法利用索引定位。

2. 改法：把运算移到等号右边，或改写为范围查询。

3. 例外：8.0 支持函数索引（Functional Index），可以建 INDEX((DATE(hire_date)))，但维护成本高。

追问

• 追问：函数索引什么场景用？应对：业务上确实经常按某个函数查询列（如按日期统计），可以考虑。
• 追问：WHERE LOWER(name)='abc' 走索引吗？应对：普通索引不走，要建函数索引 (LOWER(name))。

题目 15：隐式类型转换为什么会让索引失效

考点

• 字符串列被数字查询
• 相当于对列做 CAST
• 与函数包列同理

参考答案

-- phone 是 VARCHAR，下面这条会全表扫
SELECT * FROM user WHERE phone = 13800000000;
-- MySQL 把它解释成 WHERE CAST(phone AS UNSIGNED) = 13800000000，索引失效

-- 正确：用字符串
SELECT * FROM user WHERE phone = '13800000000';

1. 原因：MySQL 类型转换规则：字符串与数字比较时，把字符串转成数字（CAST），相当于对索引列做函数。

2. 反向：数字列与字符串比较时，把字符串转数字，索引不失效。

3. 预防：代码里参数类型与表字段类型严格一致。

追问

• 追问：怎么发现隐式转换？应对：EXPLAIN 看到 type=ALL，SQL 写法没问题，就查字段类型与参数类型。
• 追问：bigint 与 int 比较呢？应对：同是数字不会触发字符串转换，索引正常走。

题目 16：LIMIT 100000,20 改成"书签式"怎么写

考点

• 记住上一页最大 id
• WHERE id > max_id LIMIT 20
• 不能跳页

参考答案

-- 第一页
SELECT * FROM orders ORDER BY id LIMIT 20;
-- 假设最后一条 id=100020

-- 第二页：记住 100020
SELECT * FROM orders WHERE id > 100020 ORDER BY id LIMIT 20;
-- 第三页：记住上一页最后 id

1. 优点：无论翻到第几页，都是一次 range 扫描，成本恒定。

2. 缺点：不能跳页（不能直接跳到第 100 页）。

3. 适用：APP 无限滚动、信息流，这是主流方案。

追问

• 追问：排序不是 id 而是 create_time 怎么办？应对：按 create_time 排序时用 (create_time, id) 联合书签，避免同时间戳歧义。
• 追问：要支持跳页怎么办？应对：限制最大页数，或用 ES。

题目 17：事务太长为什么会影响性能

考点

• undo 日志膨胀
• 锁持有时间长
• MVCC purge 延迟
• 主从延迟

参考答案

1. undo 膨胀：长事务持有 undo log 不提交，purge 线程不能清理，undo 表空间膨胀。

2. 锁持有时间长：行锁/间隙锁持有久，阻塞其他事务。

3. MVCC 问题：长事务看到的旧版本链长，读性能下降。

4. 主从延迟：大事务在从库回放慢，延迟增大。

5. 原则：事务只包数据库操作，不要把 RPC/HTTP/发邮件放事务里。

-- 坏：事务里包了外部调用
BEGIN;
UPDATE account SET balance=balance-100 WHERE id=1;
CALL_REMOTE_SERVICE();   -- 慢 RPC，事务一直挂着
COMMIT;

-- 好：先做外部调用，再开短事务

追问

• 追问：怎么发现长事务？应对：information_schema.innodb_trx 看 trx_started 与 now() 差。
• 追问：事务里能 sleep 吗？应对：开发环境调试可以，生产千万别。

题目 18：分批次处理大表 UPDATE/DELETE 怎么写

考点

• 小批量循环
• 每批 sleep
• 主键/索引定位

参考答案

-- 伪代码：循环删除 2025 年之前的 closed 订单，每批 2000
-- 应用层或存储过程循环执行：
DELETE FROM orders
WHERE status='CLOSED' AND create_time<'2025-01-01'
ORDER BY id
LIMIT 2000;
-- 应用层判断 affect_rows==2000 就继续，否则结束
-- 每批之间 SLEEP(1) 让从库追上

1. 每批小：2000~5000 行，事务小、锁时间短。

2. 按主键排序 LIMIT：利用主键索引定位，避免全表扫。

3. 每批 sleep：给主从复制喘息时间，避免延迟。

4. 业务低峰：凌晨执行，监控主从延迟。

追问

• 追问：每批都 WHERE create_time<'2025' 会不会重复扫？应对：第一批删了后，第二批 WHERE 自动跳过，配合主键 LIMIT 高效。
• 追问：用存储过程还是应用层循环？应对：应用层循环更灵活，可加监控与中断。

题目 19：SQL 优化到极限还是慢，下一步做什么

考点

• 架构层优化
• 缓存
• 读写分离
• 分库分表
• OLAP

参考答案

1. 加缓存：热点数据放 Redis，减少数据库访问。

2. 读写分离：查询走从库，主库只写。

3. 分库分表：单表数据量超过千万级，按业务维度水平拆分。

4. ES 检索：模糊搜索、复杂聚合、报表分析走 Elasticsearch。

5. OLAP 库：报表/BI 走 ClickHouse/Doris，与业务库隔离。

6. 业务层：限制最大页数、异步化、降级非核心查询。

追问

• 追问：什么时候该分库分表？应对：单表千万级、写 QPS 过万、单表 DDL 慢、索引维护成本高。
• 追问：缓存一致性怎么保证？应对：更新数据库后删缓存，或 Canal 监听 binlog 异步更新缓存。

题目 20：常见坑与注意事项

考点

• SQL 优化高频坑汇总
• 索引与写法配合
• 生产事故预防

参考答案

1. 坑 1：加了索引但 SQL 写法让索引失效，白加。

2. 坑 2：深分页 OFFSET 大，越翻越慢。

3. 坑 3：SELECT * 导致无法覆盖索引，回表次数多。

4. 坑 4：一次性大 UPDATE/DELETE 主从延迟、锁表。

5. 坑 5：事务里包外部调用，长事务拖垮库。

6. 坑 6：索引列隐式类型转换，数据库类型与代码类型不一致。

7. 坑 7：OR 条件一边无索引，整句全表扫。

8. 坑 8：统计信息过期，优化器走错索引，不 ANALYZE TABLE。

9. 坑 9：JOIN 太多表，优化器选优困难，维护成本高。

10. 坑 10：以为 EXPLAIN rows 是真实值，不实测响应时间。

追问

• 追问：加索引一定变快吗？应对：写多读少的表索引会拖慢写，要权衡。
• 追问：SQL 优化要量化吗？应对：必须，优化前后都要测响应时间、扫描行数、CPU 占比。

速查表：SQL 优化速记

| 优化点 | 做法 | 效果 |

|--------|------|------|

| 索引 | WHERE/JOIN/ORDER BY 列建联合索引 | type 从 ALL 升 ref/range |

| 避免 SELECT * | 只写需要列 | 覆盖索引、减少 I/O |

| 深分页 | 书签式 WHERE id>last_id LIMIT n | 响应时间恒定 |

| 大 COUNT | 汇总表/近似值 | O(1) 替代全表扫 |

| 批量写 | 多值 INSERT / LOAD DATA | 提升 10~100 倍 |

| 大更新 | 分批 LIMIT + sleep | 避免锁表/主从延迟 |

| OR 改 UNION ALL | 拆分条件 | 各自走索引 |

| 模糊搜索 | FULLTEXT / ES | 替代 LIKE '%x' |

| 长事务 | 事务只包 DB 操作 | undo 不膨胀 |

| JOIN | 被驱动表加索引 | type 从 ALL 升 ref |

复习清单

| 题目 | 核心结论 | 易错点 |

|------|----------|--------|

| 1 | 索引→写法→表结构→架构 四层优化 | 一上来就分库分表 |

| 2 | slow_query_log 定位慢 SQL | 阈值设太大抓不到 |

| 3 | 函数/运算/前导模糊/隐式转换失效 | OR 一边无索引整句失效 |

| 4 | 小结果集驱动大表 | 驱动表选错 |

| 5 | 深分页 OFFSET 大慢 | 书签式分页不能跳页 |

| 6 | SELECT * 浪费 I/O 与网络 | 以为数据量小无所谓 |

| 7 | InnoDB COUNT(*) 无元数据 | 带 WHERE 仍走全表 |

| 8 | 被驱动表 JOIN 列加索引 | JOIN 表过多 |

| 9 | 相关子查询改 JOIN | 改 JOIN 忘记 DISTINCT |

| 10 | 多值 INSERT + LOAD DATA | 一次 INSERT 几万条 |

| 11 | 大 UPDATE/DELETE 分批 | 用 TRUNCATE 前不备份 |

| 12 | OR 改 UNION ALL | index_merge 当最优 |

| 13 | 前导模糊走 FULLTEXT/ES | LIKE '%x' 想走索引 |

| 14 | 函数包列失效 | 运算放等号右边 |

| 15 | 字符串列用数字查询失效 | 代码参数类型不匹配 |

| 16 | 书签式分页 WHERE id>last_id | 同时间戳排序歧义 |

| 17 | 长事务 undo 膨胀 | 事务里包 RPC |

| 18 | 分批 UPDATE 每批 sleep | 不分批一次性大事务 |

| 19 | 缓存/读写分离/分库分表/ES | 缓存一致性不考虑 |

| 20 | 索引失效、深分页、大事务三大坑 | 加索引不测真实效果 |

练习题

1. 题目：SELECT * FROM orders WHERE user_id=1 LIMIT 100000, 20 很慢，怎么优化？

• 参考答案：改书签式 WHERE user_id=1 AND id>上一页最后id ORDER BY id LIMIT 20；或延迟关联先查 id 再回表。

2. 题目：phone 是 VARCHAR，SELECT * FROM user WHERE phone=13800000000 走全表扫描，为什么？

• 参考答案：隐式类型转换，MySQL 把 phone 列转数字比较，相当于 CAST(phone AS UNSIGNED)=13800000000，索引失效；应写成 phone='13800000000'。

3. 题目：要删除 2025 年之前的 1000 万行订单，怎么做才不影响线上？

• 参考答案：分批 DELETE，每批 2000~5000 行，按主键排序 LIMIT，每批 sleep 1 秒，业务低峰执行，监控主从延迟。

4. 题目：OR 条件 WHERE dept_id=5 OR job='工程师' 全表扫，dept_id 有索引 job 无索引，怎么改？

• 参考答案：改 UNION ALL：SELECT ... WHERE dept_id=5 UNION ALL SELECT ... WHERE job='工程师'。

5. 题目：一条 SQL 加了索引还是慢，EXPLAIN 看到 type=ref 但 rows=100 万，下一步怎么办？

• 参考答案：检查 WHERE 是否有更多过滤条件可加索引；检查是否 SELECT * 导致回表；评估是否要拆分 SQL 或加缓存。

下一篇预告：第 15 章 主从复制面试精讲