罗罗编程学习记录
本页内容 展开查看

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 取参数化后的模板,避免被单次偶发干扰。

原理流程

  1. 按耗时/调用次数排序慢查询清单,挑 Top SQL。
  2. 取该 SQL 在接近生产数据量下的计划与实测。
  3. 对照计划节点定位瓶颈,再决定改写、加索引或治理数据量。

代码 / 命令

-- 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 饱和、并发争抢。

原理流程

  1. 取静态计划 + 实测计划(ANALYZE/辅助工具)。
  2. 从根节点向下找代价/耗时最高的节点。
  3. 分类根因后对症:路径问题加/改索引,行数问题更新统计或改写,运算问题减数据量/调工作区,环境问题查锁与资源。

代码 / 命令

-- 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 不能随机跳页,只支持上一页/下一页式翻页。

原理流程

  1. 第一页按排序键取 N 行,记录末行键值。
  2. 后续页用 WHERE key < :lastKey(或 >)继续定位,索引有序扫描 N 行。
  3. 避免扫描丢弃前 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 * 可能带出意外列。

原理流程

  1. 明确页面/接口实际需要的列。
  2. 只在 SELECT 列出这些列,核对是否被索引覆盖。
  3. 大文本/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,让优化器规划访问路径。
  • 展开判断:现代优化器可能自动展开子查询;计划未展开且代价高时再人工改写。

原理流程

  1. 判断子查询是否相关、是否被计划物化。
  2. 相关子查询:改写成连接或半连接。
  3. 核对改写前后结果集语义一致(去重、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 行为不同。

原理流程

  1. 明确目标:存在性判断、值匹配还是取列。
  2. 选对应写法;检查 NULL 与重复行语义。
  3. 计划对比执行方式,确认未产生额外放大。

代码 / 命令

-- 存在性: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 保留重复。
  • 字段对齐:各分支列数与类型一致。

原理流程

  1. 拆 OR 条件为独立查询分支。
  2. 每分支补对应索引,独立命中。
  3. 合并结果;按是否允许重复选 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;PG work_mem。

原理流程

  1. 检查排序列/分组列是否被索引有序覆盖。
  2. 先加过滤条件减少参与排序聚合的行数。
  3. 实测是否落盘;落盘时评估减数据量优先,再调工作区。

代码 / 命令

-- 先按时间过滤,再按用户分组排序
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 大结果集读取与分页流式处理要注意什么?

频率 ★★★☆☆ | 难度 进阶

一句话结论

一次性把大结果集拉到应用层会撑爆内存、拉长事务;应分批/游标读取,限制每批大小,处理完释放,避免长事务持有锁与快照。

概念解释

  • 结果集过大:传输、内存与序列化成本随行数膨胀。
  • 分批读取:按主键/游标分批处理,控制单批规模。
  • 长事务风险:长时间持有连接与快照,阻碍版本回收与并发。
  • 游标/流式:数据库端游标或驱动流式读取,逐批拉取。

原理流程

  1. 估算结果集规模,禁止一次性全量加载。
  2. 按稳定排序键分批,每批独立提交。
  3. 监控事务时长与连接占用,避免批处理跨长事务。

代码 / 命令

-- 按主键分批,循环处理
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:一次连接查出;注意一对多放大行数。

原理流程

  1. 识别循环内调用数据库的代码模式。
  2. 收集主记录 ID 集合,批量查关联数据。
  3. 内存按 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 下需按可见性判断,无固定行数常量。
  • 近似计数:基于统计信息/系统视图估算,快但不精确。
  • 预聚合:计数器表/汇总表维护业务计数。

原理流程

  1. 区分需求是精确计数还是大致数量。
  2. 精确计数:缩小过滤范围、利用覆盖索引;大表评估成本。
  3. 非精确:用估算值或预聚合计数器。

代码 / 命令

-- 计数(注意 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 硬扫大表。

概念解释

  • 前缀匹配:'前缀%' 可走索引范围扫描。
  • 全文索引:按词项倒排,支持关键词检索与相关性排序。
  • 专用搜索:复杂分词、相关性、聚合检索交给搜索引擎。
  • 取舍:数据量、中文分词质量、更新频率与运维成本。

原理流程

  1. 判断搜索是前缀、子串还是关键词语义检索。
  2. 前缀用普通索引;关键词用全文/倒排索引。
  3. 复杂检索场景评估专用搜索引擎,避免大表 %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、批量更新/删除。
  • 事务粒度:逐条提交形成大量小事务;批量需控制单批规模避免大事务。

原理流程

  1. 识别代码中循环内的数据库调用。
  2. 收集数据为集合,改批量查询/写入。
  3. 控制批量大小,平衡往返次数与单批事务规模。

代码 / 命令

-- 反模式:循环单条插入
-- 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 两侧类型不一致会触发隐式转换,连接列无法有效走索引,嵌套循环内层退化为扫描;应保证关联两侧列类型、字符集与长度一致。

概念解释

  • 类型提升:连接两侧类型不同时按规则转换,被转换列等价于套函数。
  • 索引失效:转换后键序破坏,内层查找无法定位。
  • 字符集不一致:字符串连接字符集不同也会转换,影响索引使用与比较结果。

原理流程

  1. 核对连接两侧列类型、长度、字符集/排序规则。
  2. 计划中连接内层是否扫描全表或出现转换函数。
  3. 统一类型后重跑计划对比。

代码 / 命令

-- 风险: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、外连接条件错位。

原理流程

  1. 收集慢/高耗 SQL,按反模式清单逐项核对。
  2. 归类到路径、结构、规模、语义四类问题。
  3. 对应改写或结构调整,计划验证。

代码 / 命令

-- 反模式集合示例
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 改写版本可切回;新增索引可下线。

原理流程

  1. 测试库取接近生产的数据快照,跑前后两版 SQL。
  2. 对比结果集与聚合值,再对比计划与实测耗时/缓冲。
  3. 灰度上线并监控慢日志与资源指标,保留回滚路径。

代码 / 命令

-- 结果一致性校验:差值为空即两边一致(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)。
  • 方案分级:先低成本改写,再评估索引与结构调整。
  • 闭环:上线后验证指标改善,沉淀到慢查询案例库。

原理流程

  1. 监控/慢日志发现 Top SQL,提取模板与参数。
  2. 复现并取计划,定位瓶颈节点与根因。
  3. 形成方案,校验语义与性能,灰度发布并监控。
  4. 复盘是否需要补索引规范、反模式拦截或容量治理。

代码 / 命令

发现(慢日志/监控)
  → 复现(同数据量/参数)
  → 计划分析(路径/行数/连接/排序/锁)
  → 根因归类
  → 方案:改写 SQL / 调整索引 / 减少数据量 / 调工作区
  → 一致性校验 + 计划实测对比
  → 灰度上线 + 指标监控
  → 复盘沉淀

常见误区

  • 误区:定位到慢 SQL 后直接上生产改。正解:必须先复现、校验语义与计划,灰度观察;直接改生产风险高。
  • 误区:优化完一条就结束。正解:同类反模式可能在多处出现,应复盘是否需要规范拦截或索引评审机制。

追问

  • 问:优化后又变慢怎么排查?答:重新取计划,区分是数据增长、统计过期、计划漂移还是新增竞争;回到定位链路重新走一遍。
  • 问:慢查询案例库记录什么?答:SQL 模板、根因、方案、前后计划对比与指标变化,供同类问题快速参照。

参考来源