05.1.3.3 慢查询定位与改写
所属:全栈开发面试题库 / 05 数据库与存储 / 05.1 关系型数据库通用能力 / 05.1.3 SQL 优化与执行计划
覆盖范围
- 慢查询入口:慢日志、监控指标、性能视图
- 定位链路:语句 → 计划 → 根因(路径/行数/连接/排序/锁等待)
- 高频改写:深分页、子查询转连接、EXISTS/IN/JOIN、N+1、聚合与计数
- 常见反模式与上线前验证流程
- MySQL 与 PostgreSQL 定位工具对照
高频题速览
| 编号 | 题目 | 难度 | 频率 |
|---|---|---|---|
| 05.1.3.3-001 | 慢查询怎么定位 | 基础 | ★★★★★ |
| 05.1.3.3-003 | 深分页与 keyset 分页 | 进阶 | ★★★★★ |
| 05.1.3.3-010 | N+1 查询问题 | 进阶 | ★★★★★ |
| 05.1.3.3-005 | 子查询改写为 JOIN | 进阶 | ★★★★☆ |
| 05.1.3.3-008 | 排序与分组优化 | 进阶 | ★★★★☆ |
定位与改写流程
慢日志/监控 → 取代表性 SQL → 看执行计划 → 定位瓶颈节点
→ 分类根因:缺索引 / 索引失效 / 行数膨胀 / 连接顺序差 / 排序落盘 / 锁等待
→ 改写或加索引 → 计划与实测对比 → 校验结果一致后上线
05.1.3.3-001 慢查询一般从哪里定位?
频率 ★★★★★ | 难度 基础
一句话结论
先从慢查询日志/监控指标找出高频或高耗时 SQL,再取代表性语句看执行计划;不要凭感觉猜哪条慢,定位顺序是:指标 → 语句 → 计划 → 根因。
概念解释
- 慢日志:记录超过阈值执行时间的语句,含耗时、扫描行数、执行次数。
- 监控指标:QPS、慢查询数、锁等待、缓冲命中率、IO 与连接数。
- 性能视图:引擎内置视图记录语句累积耗时、调用次数与扫描行数。
- 代表性语句:同一类 SQL 取参数化后的模板,避免被单次偶发干扰。
原理流程
- 按耗时/调用次数排序慢查询清单,挑 Top SQL。
- 取该 SQL 在接近生产数据量下的计划与实测。
- 对照计划节点定位瓶颈,再决定改写、加索引或治理数据量。
代码 / 命令
-- MySQL:确认慢日志开关与阈值
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
-- PostgreSQL:慢日志通过 log_min_duration_statement 控制
SHOW log_min_duration_statement;
-- PG:按累积耗时查活动/历史语句(视图随版本,具体见 05.3.4)
-- SELECT query, total_exec_time, calls FROM pg_stat_statements ORDER BY total_exec_time DESC;
常见误区
- 误区:响应慢就一定是某条 SQL 慢。正解:也可能是锁等待、连接池满、CPU/IO 饱和或网络问题;先看数据库整体指标再定位语句。
- 误区:慢日志里每条都要优化。正解:按调用次数 × 单次耗时估算总影响;偶发长查询与高频小慢查询治理优先级不同。
追问
- 问:扫描行数大但耗时不长的语句要管吗?答:关注其趋势与资源占用;高扫描行比例会随数据增长恶化,应结合计划判断。
- 问:慢日志会记录所有慢操作类型吗?答:慢日志主要记录语句执行;锁等待、事务长时间持锁需结合锁视图与事务监控另行排查。
05.1.3.3-002 拿到一条慢 SQL,完整定位链路怎么走?
频率 ★★★★☆ | 难度 进阶
一句话结论
按「看计划访问路径 → 对比估算与实际行数 → 检查连接顺序与算法 → 检查排序/临时表/落盘 → 排除锁等待与资源瓶颈」的顺序逐层定位,先区分是访问路径问题、行数膨胀问题还是执行环境问题。
概念解释
- 路径问题:全表扫描、回表过多、索引失效。
- 行数问题:中间结果集膨胀、估算偏差。
- 运算问题:排序落盘、临时表、聚合代价高。
- 环境问题:锁等待、缓存未命中、CPU/IO 饱和、并发争抢。
原理流程
- 取静态计划 + 实测计划(ANALYZE/辅助工具)。
- 从根节点向下找代价/耗时最高的节点。
- 分类根因后对症:路径问题加/改索引,行数问题更新统计或改写,运算问题减数据量/调工作区,环境问题查锁与资源。
代码 / 命令
-- PG:实测计划 + 缓冲,一次看清路径、行数、耗时、落盘
EXPLAIN (ANALYZE, BUFFERS) <慢 SQL>;
-- MySQL:EXPLAIN + Handler 计数观察实际读取
EXPLAIN <慢 SQL>;
-- 排查锁等待(示意,具体视图见 05.1.2 / 05.2.4 / 05.3.4)
-- 查当前阻塞链、等待事件、长事务
常见误区
- 误区:慢 SQL 直接加索引就行。正解:先定位根因;若是连接顺序、排序落盘或锁等待,加索引不一定解决。
- 误区:计划没问题就一定是数据库慢。正解:还要看应用层是否循环调用、网络往返、连接池等待与上游阻塞。
追问
- 问:如何区分是 SQL 本身慢还是被锁拖住?答:计划耗时正常但语句等待久,看锁等待与阻塞链;计划中扫描/排序代价高才是 SQL 自身问题。
- 问:一条 SQL 有多个瓶颈先治哪个?答:先消除扫描行数最大、代价占比最高的节点;瓶颈消除后再重测,避免一次改多处无法归因。
05.1.3.3-003 深分页为什么慢?怎么优化?
频率 ★★★★★ | 难度 进阶
一句话结论
LIMIT N OFFSET M 要扫描并丢弃前 M 行,越翻越慢;改用 keyset(游标)分页:以上一页最后一条的排序键为条件继续取,配合索引有序,每次只定位下一页的少量行。
概念解释
- 偏移分页(offset):先读前 M+N 行再丢弃 M 行,扫描量随页码线性增长。
- keyset 分页(seek):记住上一页末行的排序键值,下一页从该值之后继续取。
- 前提:排序键有索引且唯一(或加主键打破并列)。
- 局限:keyset 不能随机跳页,只支持上一页/下一页式翻页。
原理流程
- 第一页按排序键取 N 行,记录末行键值。
- 后续页用
WHERE key < :lastKey(或>)继续定位,索引有序扫描 N 行。 - 避免扫描丢弃前 M 行;新增数据不影响已翻页位置时需评估一致性。
代码 / 命令
-- 慢:扫描并丢弃前 100000 行
SELECT * FROM orders ORDER BY id DESC LIMIT 20 OFFSET 100000;
-- keyset:以上一页最后一条 id 为游标
SELECT * FROM orders
WHERE id < :lastId
ORDER BY id DESC LIMIT 20;
-- 排序字段不唯一时,加主键做二级排序打破并列
SELECT * FROM orders
WHERE (created_at, id) < (:lastCreatedAt, :lastId)
ORDER BY created_at DESC, id DESC LIMIT 20;
-- 过渡方案:子查询先取主键再回表(仍扫前 M 个主键,缓解有限)
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id DESC LIMIT 20 OFFSET 100000) t
ON o.id = t.id;
常见误区
- 误区:加个索引深分页就不慢了。正解:索引有序解决排序,但 offset 仍要扫描丢弃前 M 行;根本优化是改 keyset。
- 误区:keyset 排序字段随便选一个就行。正解:排序字段必须有索引且能唯一确定位置;重复值需补主键做次级排序,否则会漏行/重行。
追问
- 问:业务必须支持跳页怎么办?答:可限制最大页码,或用搜索条件收敛范围;跳页场景结合搜索引擎/快照方案。
- 问:翻页过程中有新数据写入会漏行吗?答:按键值游标方向与排序方向一致时,新增数据落在边界之外的处理需明确;游标分页的一致性语义要在业务上讲清。
05.1.3.3-004 为什么要避免 SELECT *?
频率 ★★★★☆ | 难度 基础
一句话结论
SELECT * 取全部列,容易破坏覆盖索引、增加回表与网络传输,还可能取出用不到的大字段;只取必要列,既利于覆盖索引,也减少 IO 与内存占用。
概念解释
- 覆盖破坏:多出的列不在索引中,被迫回表取整行。
- 传输与内存:宽行/大字段拉长网络传输与结果集内存占用。
- 稳定性风险:表结构变更时
SELECT *可能带出意外列。
原理流程
- 明确页面/接口实际需要的列。
- 只在
SELECT列出这些列,核对是否被索引覆盖。 - 大文本/LOB 列单独按需读取。
代码 / 命令
-- 不推荐:取全部列
SELECT * FROM orders WHERE user_id = 1001;
-- 推荐:只取需要的列,配合覆盖索引
SELECT id, status, amount, created_at
FROM orders WHERE user_id = 1001;
常见误区
- 误区:
SELECT *数据库会自动只返回需要的列。正解:*展开为表全部列,优化器不会替你裁剪未使用列。 - 误区:小表无所谓
SELECT *。正解:小表当前便宜,但表变宽、数据增长后问题才显现;养成只取必要列的习惯。
追问
- 问:大字段(TEXT/BLOB)为什么要单独读?答:大字段离页存储或占用宽行,取出会显著增加 IO 与内存;列表接口不应携带大字段。
- 问:覆盖索引与
SELECT *矛盾吗?答:矛盾;覆盖索引要求返回列入索引,SELECT *几乎不可能被普通二级索引覆盖。
05.1.3.3-005 子查询为什么常改写为 JOIN?
频率 ★★★★☆ | 难度 进阶
一句话结论
相关子查询按外层逐行触发内层,容易反复扫描;改写为连接后可由优化器统一选索引与连接顺序,中间结果集更可控;非相关子查询先确认能否被展开,再决定是否改写。
概念解释
- 相关子查询:内层引用外层列,外层每行执行一次内层。
- 改写连接:把子查询条件并入
JOIN,让优化器规划访问路径。 - 展开判断:现代优化器可能自动展开子查询;计划未展开且代价高时再人工改写。
原理流程
- 判断子查询是否相关、是否被计划物化。
- 相关子查询:改写成连接或半连接。
- 核对改写前后结果集语义一致(去重、NULL 处理)。
代码 / 命令
-- 相关子查询:外层每行触发一次内层 EXISTS 判断
SELECT u.name FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 1000
);
-- 改写为半连接风格:先筛订单再关联用户
SELECT DISTINCT u.name
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.amount > 1000;
-- 注意:DISTINCT/去重语义需与原 EXISTS 保持一致
常见误区
- 误区:所有子查询都要手动改成 JOIN。正解:优化器可展开/转半连接时自动处理;先看计划,未展开且慢再改写。
- 误区:改写为 JOIN 后结果一定一致。正解:连接可能放大行数(一对多),需用
DISTINCT/聚合核对原语义,尤其 EXISTS 去重场景。
追问
- 问:派生表什么时候该保留?答:需要先聚合/过滤形成中间结果再关联时,派生表有其语义价值;优化器能否展开看计划。
- 问:JOIN 比子查询快是绝对结论吗?答:不是;取决于索引、数据量与计划,应以实测计划对比为准。
05.1.3.3-006 EXISTS、IN、JOIN 怎么选?
频率 ★★★☆☆ | 难度 进阶
一句话结论
判断存在性优先 EXISTS(匹配到即停);匹配值列表用 IN;需要取对方列数据用 JOIN。现代优化器常把 IN/EXISTS 转半连接执行,差异主要在 NULL 语义与去重行为。
概念解释
- EXISTS:半连接语义,内层找到一条匹配即停止,不关心返回列。
- IN:值列表匹配;列表含 NULL 时比较结果为空,需注意语义。
- JOIN:取对方字段;一对多会放大行数,必要时
DISTINCT/聚合。 - NULL 差异:
NOT IN遇 NULL 结果为空;NOT EXISTS对 NULL 行为不同。
原理流程
- 明确目标:存在性判断、值匹配还是取列。
- 选对应写法;检查 NULL 与重复行语义。
- 计划对比执行方式,确认未产生额外放大。
代码 / 命令
-- 存在性:EXISTS
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
-- 值匹配:IN
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);
-- 取列:JOIN
SELECT u.name, o.amount
FROM users u JOIN orders o ON o.user_id = u.id;
常见误区
- 误区:
IN子查询遇到 NULL 也能正常判断。正解:IN/NOT IN列表含 NULL 时比较结果为 UNKNOWN,可能导致查不到行;否定场景优先核对。 - 误区:JOIN 一定比 EXISTS 快。正解:只需判断存在时 EXISTS 匹配即停;JOIN 会取列并可能放大结果,场景不同。
追问
- 问:大表
IN (子查询)性能如何?答:现代优化器常转半连接;若计划物化子查询且结果集大,评估改 JOIN 或拆分。 - 问:
NOT EXISTS与NOT IN哪个更稳?答:否定查询中NOT EXISTS对 NULL 处理更直观;NOT IN需特别确认子查询列无 NULL。
05.1.3.3-007 OR 条件如何改写以利用索引?
频率 ★★★☆☆ | 难度 进阶
一句话结论
OR 两侧各有索引时可改写为 UNION ALL(需去重用 UNION),让每个分支独立走索引;单侧无索引则整体难走索引,应从业务条件与索引设计上解决。
概念解释
- 分支独立:UNION 后每个
SELECT单独规划计划。 - 去重语义:
UNION去掉重复行并可能排序;UNION ALL保留重复。 - 字段对齐:各分支列数与类型一致。
原理流程
- 拆
OR条件为独立查询分支。 - 每分支补对应索引,独立命中。
- 合并结果;按是否允许重复选
UNION ALL/UNION。
代码 / 命令
-- 原 OR
SELECT * FROM users WHERE city = '昆明' OR level = 5;
-- 改写:分支各自走索引(重复行处理按需选 UNION)
SELECT * FROM users WHERE city = '昆明'
UNION ALL
SELECT * FROM users WHERE level = 5;
常见误区
- 误区:拆成 UNION 就一定更快。正解:若分支命中行数多、合并去重代价高,未必优于原计划;以实测为准。
- 误区:
UNION ALL可以随意替代UNION。正解:可能产生重复行;业务不允许重复时必须UNION或在应用层去重。
追问
- 问:跨多个表的 OR 怎么拆?答:通常拆成多个查询在应用层合并;单表 OR 才适合 UNION 改写。
- 问:索引合并与 UNION 改写哪个优先?答:先看优化器索引合并计划;合并代价高再改 UNION,避免过度改写。
05.1.3.3-008 排序与分组(ORDER BY / GROUP BY)慢怎么优化?
频率 ★★★★☆ | 难度 进阶
一句话结论
优先让排序/分组利用索引有序(过滤列在前、排序列在后、方向一致);无法走索引时减少参与排序的行数、评估工作区大小与落盘;分组先过滤再聚合,避免对全表分组。
概念解释
- 索引有序:索引叶子按键序排列,免掉 Sort/filesort。
- 先过滤后聚合:
WHERE先收缩行,再GROUP BY/ORDER BY。 - 落盘判断:排序/哈希超出内存工作区写临时文件,磁盘 IO 上升。
- 工作区:MySQL
sort_buffer_size、tmp_table_size;PGwork_mem。
原理流程
- 检查排序列/分组列是否被索引有序覆盖。
- 先加过滤条件减少参与排序聚合的行数。
- 实测是否落盘;落盘时评估减数据量优先,再调工作区。
代码 / 命令
-- 先按时间过滤,再按用户分组排序
SELECT user_id, count(*) AS cnt
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY user_id
ORDER BY cnt DESC;
-- 索引思路:覆盖过滤 + 分组列;排序 cnt 是聚合结果,索引无法直接有序
-- 计划关注:Using filesort / Using temporary / Sort Method: External Merge Disk
常见误区
- 误区:排序慢就把排序缓冲区调大。正解:先减少参与排序的行数与行数规模;缓冲区只能缓解内存不足,解决不了扫描量过大。
- 误区:
GROUP BY一定需要临时表。正解:分组列与索引顺序一致时可利用索引有序分组,免临时表排序。
追问
- 问:按聚合结果(如 count(*))排序能走索引吗?答:不能;聚合值需先算出来再排序,只能减少参与聚合的行数。
- 问:分组后取 Top N 有什么优化?答:先过滤收缩范围,必要时用索引/增量聚合;超大分组需考虑预聚合表。
05.1.3.3-009 大结果集读取与分页流式处理要注意什么?
频率 ★★★☆☆ | 难度 进阶
一句话结论
一次性把大结果集拉到应用层会撑爆内存、拉长事务;应分批/游标读取,限制每批大小,处理完释放,避免长事务持有锁与快照。
概念解释
- 结果集过大:传输、内存与序列化成本随行数膨胀。
- 分批读取:按主键/游标分批处理,控制单批规模。
- 长事务风险:长时间持有连接与快照,阻碍版本回收与并发。
- 游标/流式:数据库端游标或驱动流式读取,逐批拉取。
原理流程
- 估算结果集规模,禁止一次性全量加载。
- 按稳定排序键分批,每批独立提交。
- 监控事务时长与连接占用,避免批处理跨长事务。
代码 / 命令
-- 按主键分批,循环处理
SELECT * FROM orders
WHERE id > :lastId
ORDER BY id LIMIT 500;
-- 处理完记录 lastId,继续下一批,直到无结果
-- 避免:SELECT * FROM 大表 直接全量加载到应用内存
常见误区
- 误区:分页批处理可以放在一个大事务里跑完整批。正解:长事务持锁与快照,阻塞回收与并发;按批提交,控制单批事务时长。
- 误区:流式读取就不需要限制批大小。正解:驱动流式只是不一次性缓存全量,应用仍需控制节奏与内存,防止处理跟不上拉取。
追问
- 问:批处理如何避免漏掉新增数据?答:按只读快照或固定条件分批;新增数据落在处理范围外的语义需明确,必要时二次扫描。
- 问:大结果集导出怎么做?答:分批/游标导出,避免单事务全表快照;工具与参数见 05.1.4.4。
05.1.3.3-010 什么是 N+1 查询?怎么解决?
频率 ★★★★★ | 难度 进阶
一句话结论
先查主列表 1 次,再对每条主记录单独查关联数据 N 次,共 1+N 次数据库往返;解决方式是**批量关联查询(JOIN/IN 批量取再内存拼装)**或批量预取,把 N 次合并为少量批次。
概念解释
- 1 次主查询:取出主记录列表。
- N 次关联查询:循环内按主记录 ID 逐条查关联表。
- 批量关联:一次性取主表 ID 集合,
IN批量查关联表,应用层按 ID 拼装。 - JOIN:一次连接查出;注意一对多放大行数。
原理流程
- 识别循环内调用数据库的代码模式。
- 收集主记录 ID 集合,批量查关联数据。
- 内存按 ID 分组拼装;一对多关系注意去重与行数。
代码 / 命令
-- 反模式:循环内每条订单查一次用户(N 次)
-- SELECT * FROM orders WHERE ...;
-- for each order: SELECT * FROM users WHERE id = order.user_id;
-- 方案一:JOIN 一次查出
SELECT o.id, o.amount, u.name
FROM orders o JOIN users u ON u.id = o.user_id
WHERE o.created_at >= '2026-01-01';
-- 方案二:先取订单,再批量按 user_id 查用户,应用层拼装
SELECT * FROM orders WHERE created_at >= '2026-01-01';
SELECT * FROM users WHERE id IN (:userIds);
常见误区
- 误区:用 JOIN 解决 N+1 就万事大吉。正解:一对多 JOIN 会放大行数,多关联表时结果集膨胀;批量取再拼装在多对多场景更可控。
- 误区:ORM 懒加载不算 N+1 问题。正解:框架懒加载在遍历时逐条触发查询,正是典型 N+1;需在查询层批量预取关联。
追问
- 问:批量
IN列表过长怎么办?答:分批拆分 ID 列表,避免单条语句参数过多;结合索引定位。 - 问:N+1 在数据库层面有表现吗?答:慢日志会出现大量结构相同、参数不同的小查询;按 SQL 模板聚合调用次数即可发现。
05.1.3.3-011 COUNT 类查询怎么优化?
频率 ★★★☆☆ | 难度 进阶
一句话结论
COUNT(*)/COUNT(1) 统计总行数在不同引擎实现不同:InnoDB 无内置精确行数,需扫描索引/表;大表精确计数代价高,可按业务接受度用估算值、近似计数或预聚合。
概念解释
- COUNT 语义:
COUNT(*)统计行数;COUNT(col)忽略该列为 NULL 的行;COUNT(1)与COUNT(*)通常等价。 - InnoDB 计数:MVCC 下需按可见性判断,无固定行数常量。
- 近似计数:基于统计信息/系统视图估算,快但不精确。
- 预聚合:计数器表/汇总表维护业务计数。
原理流程
- 区分需求是精确计数还是大致数量。
- 精确计数:缩小过滤范围、利用覆盖索引;大表评估成本。
- 非精确:用估算值或预聚合计数器。
代码 / 命令
-- 计数(注意 COUNT(col) 忽略 NULL)
SELECT count(*) FROM orders WHERE status = 'PAID';
-- PG 近似行数(来自统计信息,不精确)
SELECT reltuples::bigint FROM pg_class WHERE relname = 'orders';
-- 预聚合:按维度维护汇总表,定时/增量更新计数
常见误区
- 误区:
COUNT(*)、COUNT(1)、COUNT(col)完全一样。正解:COUNT(*)/COUNT(1)统计行;COUNT(col)不统计该列为 NULL 的行。 - 误区:大表随时要精确总数。正解:多数列表页只展示大致数量;精确计数应离线/预聚合,避免在线全表扫描。
追问
- 问:为什么 MyISAM 的 count(*) 很快?答:MyISAM 维护表级行数常量;InnoDB 受 MVCC 可见性约束不能直接取常量,具体见 05.2.1。
- 问:带条件的 count 能走索引吗?答:覆盖索引(过滤列 + 可从索引计数)可避免回表;具体执行方式看计划。
05.1.3.3-012 模糊搜索在关系库里怎么取舍?
频率 ★★★☆☆ | 难度 进阶
一句话结论
普通 B+ 树只支持前缀匹配;任意子串/全文检索应使用全文索引(倒排)或专用搜索引擎。关系库内先用前缀匹配评估,确有全文需求再引入倒排/ES,不要用前导 LIKE 硬扫大表。
概念解释
- 前缀匹配:
'前缀%'可走索引范围扫描。 - 全文索引:按词项倒排,支持关键词检索与相关性排序。
- 专用搜索:复杂分词、相关性、聚合检索交给搜索引擎。
- 取舍:数据量、中文分词质量、更新频率与运维成本。
原理流程
- 判断搜索是前缀、子串还是关键词语义检索。
- 前缀用普通索引;关键词用全文/倒排索引。
- 复杂检索场景评估专用搜索引擎,避免大表
%xx%扫描。
代码 / 命令
-- 前缀:可走索引
SELECT * FROM goods WHERE name LIKE '无线%';
-- MySQL 全文索引(示意,分词器与语法以版本为准)
-- CREATE FULLTEXT INDEX ft_name ON goods(name);
-- SELECT * FROM goods WHERE MATCH(name) AGAINST('耳机' IN NATURAL LANGUAGE MODE);
-- PG:tsvector + GIN 索引(详见 05.3 / 05.5.2.3)
常见误区
- 误区:给大文本列加普通索引就能做站内搜索。正解:普通索引不支持子串与分词检索;全文检索需要倒排机制。
- 误区:关系库全文索引能替代搜索引擎。正解:中文分词、相关性排序、多字段聚合、扩展性上专用引擎更强;边界场景需实测。
追问
- 问:中文全文检索要注意什么?答:分词器质量决定召回;英文按空格分词,中文需分词词典/插件,详见 05.5.2.3。
- 问:LIKE 与全文索引能混用吗?答:可以并存;前缀匹配走普通索引,关键词检索走全文索引,按查询形态选择。
05.1.3.3-013 为什么避免在业务循环里执行 SQL?
频率 ★★★★☆ | 难度 基础
一句话结论
循环内逐条执行 SQL 造成多次网络往返、短事务与索引反复查找,吞吐低且放大锁与连接占用;应改为批量语句或集合操作,减少往返次数。
概念解释
- 往返开销:每条 SQL 一次网络交互 + 解析执行,累积延迟高。
- 批量操作:
IN批量查、批量INSERT ... VALUES、批量更新/删除。 - 事务粒度:逐条提交形成大量小事务;批量需控制单批规模避免大事务。
原理流程
- 识别代码中循环内的数据库调用。
- 收集数据为集合,改批量查询/写入。
- 控制批量大小,平衡往返次数与单批事务规模。
代码 / 命令
-- 反模式:循环单条插入
-- for item: INSERT INTO t (a, b) VALUES (?, ?);
-- 批量插入(控制每批行数)
INSERT INTO t (a, b) VALUES
(1, 'x'), (2, 'y'), (3, 'z');
-- 批量查询
SELECT * FROM t WHERE id IN (1, 2, 3, 4);
常见误区
- 误区:批量操作越大越好。正解:单批过大会形成大事务、长锁与回滚压力;按批次大小与事务时长控制。
- 误区:循环单条写在低并发下没问题就不用改。正解:数据量与并发增长后往返开销线性放大;结构问题应早改。
追问
- 问:批量写入失败如何回滚?答:按批组织事务,单批失败可重试/补偿;跨批一致性用对账与重试机制保障。
- 问:逐条写入有什么不可替代的场景?答:需要逐条返回自增键、逐条业务校验或外部副作用时保留逐条;其余集合操作优先批量。
05.1.3.3-014 连接字段类型不一致会有什么影响?
频率 ★★★☆☆ | 难度 进阶
一句话结论
JOIN ON 两侧类型不一致会触发隐式转换,连接列无法有效走索引,嵌套循环内层退化为扫描;应保证关联两侧列类型、字符集与长度一致。
概念解释
- 类型提升:连接两侧类型不同时按规则转换,被转换列等价于套函数。
- 索引失效:转换后键序破坏,内层查找无法定位。
- 字符集不一致:字符串连接字符集不同也会转换,影响索引使用与比较结果。
原理流程
- 核对连接两侧列类型、长度、字符集/排序规则。
- 计划中连接内层是否扫描全表或出现转换函数。
- 统一类型后重跑计划对比。
代码 / 命令
-- 风险:orders.user_id 为 BIGINT,users.id 为 VARCHAR(或反之)
SELECT o.*, u.name
FROM orders o JOIN users u ON u.id = o.user_id;
-- 修正:两侧主键/外键类型与字符集保持一致
常见误区
- 误区:关联字段数字能对上就行,类型无所谓。正解:类型不一致触发转换,连接索引失效;长字符串比较还可能有排序规则差异。
- 误区:字符集不同只是显示问题。正解:比较与连接都会受排序规则影响,可能导致索引失效或比较结果不符合预期。
追问
- 问:如何检查两列类型是否一致?答:查数据字典/表定义对照列类型、长度、字符集与排序规则。
- 问:历史表类型不一致怎么改?答:在低峰期按兼容方式逐步迁移列类型,先保证新数据一致,再清理历史;变更方案见 05.1.4.5。
05.1.3.3-015 常见 SQL 反模式有哪些?
频率 ★★★★☆ | 难度 基础
一句话结论
高频反模式包括:SELECT *、索引列套函数、前导通配 LIKE、循环单条 SQL、大事务、深分页 offset、嵌套过深的子查询、在条件中做隐式转换、无过滤的全表聚合;评审时逐条对照排除。
概念解释
- 访问反模式:破坏索引定位的写法。
- 结构反模式:子查询嵌套深、循环调用、N+1。
- 规模反模式:大事务、一次性大批量、无界结果集。
- 语义反模式:
NOT IN含 NULL、外连接条件错位。
原理流程
- 收集慢/高耗 SQL,按反模式清单逐项核对。
- 归类到路径、结构、规模、语义四类问题。
- 对应改写或结构调整,计划验证。
代码 / 命令
-- 反模式集合示例
SELECT * FROM t WHERE DATE(col) = '2026-11-19'; -- 函数包裹索引列
SELECT * FROM t WHERE name LIKE '%abc'; -- 前导通配
SELECT * FROM t WHERE id NOT IN (1, 2, NULL); -- NULL 语义风险
-- 循环单条写入、大事务未分批、无 WHERE 的全表更新/删除
常见误区
- 误区:反模式只要数据库版本新就自动规避。正解:优化器改善有限,写法本身决定访问路径;反模式仍需人工改写。
- 误区:反模式清单背下来就行。正解:要结合计划判断实际代价;某些写法在小数据量下无害,规模化后才暴露。
追问
- 问:无
WHERE的UPDATE/DELETE风险是什么?答:锁全表/大量行、长事务、回滚量大;必须带过滤条件并分批,见 05.1.3.5。 - 问:嵌套子查询深就一定慢吗?答:不一定;关键看是否展开、中间结果集多大;计划比嵌套层数更能说明问题。
05.1.3.3-016 慢查询改写后上线前要验证什么?
频率 ★★★★☆ | 难度 进阶
一句话结论
上线前验证四件事:语义一致(结果集/行数/聚合值不变)、计划改善(路径、扫描行数、回表、落盘对比)、实测性能(接近生产数据量下多次取样)、回滚方案(改写可快速回退,索引变更可下线)。
概念解释
- 语义校验:前后结果一致,避免改写改变业务含义。
- 计划对比:访问路径、扫描量、连接与排序改善。
- 性能取样:热/冷缓存分别观察,多次取稳定值。
- 回滚预案:SQL 改写版本可切回;新增索引可下线。
原理流程
- 测试库取接近生产的数据快照,跑前后两版 SQL。
- 对比结果集与聚合值,再对比计划与实测耗时/缓冲。
- 灰度上线并监控慢日志与资源指标,保留回滚路径。
代码 / 命令
-- 结果一致性校验:差值为空即两边一致(PG 示例)
(<改写前 SQL>) EXCEPT (<改写后 SQL>);
(<改写后 SQL>) EXCEPT (<改写前 SQL>);
-- 计划与实测对比
EXPLAIN (ANALYZE, BUFFERS) <改写前 SQL>;
EXPLAIN (ANALYZE, BUFFERS) <改写后 SQL>;
常见误区
- 误区:测试库快就可以直接上生产。正解:测试数据量与分布常不足;计划与性能需在接近生产规模验证。
- 误区:改写只看耗时不核对结果。正解:先保证语义一致,再谈性能;连接去重、NULL 处理最易出错。
追问
- 问:上线后如何确认改善持续有效?答:监控慢日志模板、扫描行数、耗时与索引使用;观察数据增长后计划是否漂移。
- 问:新增索引上线注意什么?答:评估写入代价与建索引期间的锁/资源影响;低峰期操作,大表用在线索引构建方案,见 05.1.4.5。
05.1.3.3-017 MySQL 与 PostgreSQL 慢查询定位工具有什么对照?
频率 ★★★☆☆ | 难度 进阶
一句话结论
两者都有慢日志与语句统计:MySQL 用慢日志、EXPLAIN、性能 schema 与 Handler 状态;PG 用 log_min_duration_statement、pg_stat_statements、EXPLAIN (ANALYZE, BUFFERS);定位主线一致,工具与视图名称不同。
概念解释
| 环节 | MySQL | PostgreSQL |
|---|---|---|
| 慢日志 | slow_query_log / long_query_time |
log_min_duration_statement |
| 语句统计 | 性能 schema / 慢日志聚合 | pg_stat_statements |
| 计划 | EXPLAIN(估算) |
EXPLAIN (ANALYZE, BUFFERS)(实测) |
| 行读取观测 | Handler_* 会话状态 |
计划实际行数 / 缓冲 |
| 锁等待 | 锁状态/阻塞视图 | 锁视图与阻塞链 |
原理流程
- 通用主线不变:先统计找 Top SQL,再看计划定位节点;具体视图与参数分别见 05.2.4 与 05.3.4。
代码 / 命令
-- MySQL
SHOW VARIABLES LIKE 'slow_query_log';
EXPLAIN <SQL>;
-- PostgreSQL
SHOW log_min_duration_statement;
EXPLAIN (ANALYZE, BUFFERS) <SQL>;
常见误区
- 误区:PG 的
ANALYZE输出能直接搬到 MySQL 解读。正解:节点名可对照,但格式、缓冲口径与实测能力不同;MySQL 实测需辅助工具。 - 误区:慢查询只在数据库层解决。正解:应用层调用模式(N+1、循环调用、缺索引的上层查询)同样要排查。
追问
- 问:跨库团队如何统一慢查询治理?答:统一按 SQL 模板聚合、记录扫描行数与耗时;底层工具差异通过监控层抹平。
- 问:语句统计能看到被缓存的重复查询吗?答:统计按语句模板/文本聚合调用次数与累积耗时;参数化与未参数化文本会分别统计。
05.1.3.3-018 一条慢 SQL 从发现到落地的完整处理流程是什么?
频率 ★★★★☆ | 难度 进阶
一句话结论
完整流程:发现 → 复现 → 计划分析 → 根因归类 → 方案(改 SQL/加索引/治理数据量/调参数)→ 一致性校验 → 灰度上线 → 监控复盘;每步留痕,避免凭经验直接改。
概念解释
- 复现:在相同数据规模与参数下稳定重现慢现象。
- 根因归类:路径、行数、运算、环境四类(见 05.1.3.3-002)。
- 方案分级:先低成本改写,再评估索引与结构调整。
- 闭环:上线后验证指标改善,沉淀到慢查询案例库。
原理流程
- 监控/慢日志发现 Top SQL,提取模板与参数。
- 复现并取计划,定位瓶颈节点与根因。
- 形成方案,校验语义与性能,灰度发布并监控。
- 复盘是否需要补索引规范、反模式拦截或容量治理。
代码 / 命令
发现(慢日志/监控)
→ 复现(同数据量/参数)
→ 计划分析(路径/行数/连接/排序/锁)
→ 根因归类
→ 方案:改写 SQL / 调整索引 / 减少数据量 / 调工作区
→ 一致性校验 + 计划实测对比
→ 灰度上线 + 指标监控
→ 复盘沉淀
常见误区
- 误区:定位到慢 SQL 后直接上生产改。正解:必须先复现、校验语义与计划,灰度观察;直接改生产风险高。
- 误区:优化完一条就结束。正解:同类反模式可能在多处出现,应复盘是否需要规范拦截或索引评审机制。
追问
- 问:优化后又变慢怎么排查?答:重新取计划,区分是数据增长、统计过期、计划漂移还是新增竞争;回到定位链路重新走一遍。
- 问:慢查询案例库记录什么?答:SQL 模板、根因、方案、前后计划对比与指标变化,供同类问题快速参照。
参考来源
- MySQL 8.0 Reference Manual: The Slow Query Log:https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html
- MySQL 8.0 Reference Manual: Optimization Overview:https://dev.mysql.com/doc/refman/8.0/en/optimization-overview.html
- PostgreSQL Documentation: Using EXPLAIN:https://www.postgresql.org/docs/current/using-explain.html
- PostgreSQL Documentation: pg_stat_statements:https://www.postgresql.org/docs/current/pgstat-statements.html