外观
数据库速记手册
摘要
以 MySQL 8.0 / 8.4、InnoDB 为主要范围。先判断结果是否正确,再定位时间花在哪里,最后改变索引、事务或容量。其他数据库的隔离、计划和诊断语法不能直接照搬。
快速入口
可执行流程、结果分支与模板:高频问题。
查重、漏行、金额放大:知识速记中的 JOIN、NULL 与聚合。
慢 SQL、连接超时、等锁:常见问题排查。
计划、事务、复制状态:工具与参数。
提交断线、误删恢复:易错点与记忆提示。
知识速记
| 知识点 | 核心结论与条件 |
|---|---|
| 一次查询 | 客户端耗时包含借连接、请求发送、执行及结果读取;服务器执行快不能证明调用快 |
| 数据粒度 | JOIN 按匹配关系组合行。两侧各有多条明细时,先确认连接基数,必要时先按目标粒度聚合 |
| 条件位置 | ON 决定匹配,WHERE 筛选结果行,HAVING 筛选分组;外连接右表条件放 WHERE 可能丢失未匹配行 |
| NULL | 普通比较可能得到 UNKNOWN,WHERE 只保留 TRUE;判断缺失用 IS NULL,警惕含 NULL 的 NOT IN |
| 聚合与窗口 | GROUP BY 收缩为组,窗口函数保留明细行;COUNT(*) 数行,COUNT(列) 排除 NULL |
| 联合索引 | 列顺序决定有序访问范围;范围定位、过滤、覆盖、排序是不同收益,不能只看“用了索引” |
| B+ 树 | 高扇出降低查找层数,但范围扫描、回表与结果传输仍可能很大;命中索引不等于工作量小 |
| 深分页 | OFFSET 通常仍要处理被跳过的记录;游标分页需要确定性排序及唯一补充键,不能直接替代任意跳页 |
| MVCC | 普通快照读按视图选择版本,锁定读有不同规则;InnoDB RC、RR 的视图时机要分别理解 |
| 锁范围 | 索引路径、隔离级别、唯一性和记录是否存在共同影响锁;返回一行不能证明只锁一行 |
| 业务事务 | ACID 不自动处理余额约束、重复请求和响应丢失;条件更新、唯一约束与结果查询仍有责任 |
| 日志与恢复 | Redo 面向崩溃恢复,Undo 支撑回滚与版本,Binlog 用于复制和历史恢复链;三者不互相替代 |
| 副本与备份 | 已提交、已传输、已应用、当前读可见是不同水位;副本会传播误删,不能替代备份 |
SQL:连接、分组与 NULL 怎么直接判断
JOIN 不是把两张表按行号拼起来,而是为每一行寻找所有满足 ON 的行。例如同一订单有 2 条支付、3 条商品明细,直接连接两张明细表会得到 6 行;两侧金额都可能被重复累加。要得到每订单一行,先分别按订单号汇总,再连接汇总结果。DISTINCT 只去掉相同结果行,不保证金额汇总正确。
- INNER JOIN:只保留匹配行;LEFT JOIN:左行没有匹配时仍保留,右侧补 NULL。
- ON:决定哪些行匹配;WHERE:筛选连接后的行;HAVING:筛选聚合后的组。
- GROUP BY:每组产生一条聚合结果;窗口函数:保留明细,同时计算组内排名或累计值。
- COUNT(*) 数行,COUNT(x) 数 x 非 NULL 的行,COUNT(DISTINCT x) 数非 NULL 的不同值。
- NULL 表示缺失或未知,不等于 0 或空字符串。比较 NULL 得到 UNKNOWN;WHERE 只接受 TRUE。
- NOT IN 子查询含 NULL 时可能导致预期外的 UNKNOWN;排除匹配项通常可用有明确关联条件的 NOT EXISTS。
下面仅是查询形状示例,表与字段需替换为业务结构。要保留无成功支付的订单,把右侧状态条件放在 ON:
sql
SELECT o.id, p.amount
FROM orders AS o
LEFT JOIN payments AS p
ON p.order_id = o.id AND p.status = 'SUCCESS';若改成 WHERE p.status='SUCCESS',无匹配订单的 NULL 行会被排除。查询返回多行时,先问“一行代表什么”,再加聚合或去重。
索引:选列顺序与读懂计划
InnoDB 主键索引叶子保存整行,二级索引通常保存索引列和主键。二级索引找到主键后再查整行称为回表;所需列全在索引中可避免这一步,称为覆盖索引。
联合键 (tenant_id, created_at, id) 按这个顺序排序。tenant_id 等值加 created_at 范围可形成连续区间;只按 created_at 查询不能默认获得同样的连续范围收益。范围条件后面的列仍可能用于过滤或覆盖,不能简单说“范围后全失效”。ORDER BY 能否避免排序也取决于等值前缀、方向和实际计划。
| 计划信息 | 应读出的意思 | 下一步 |
|---|---|---|
| key | 实际选择的索引,不是所有候选索引 | 检查其列顺序是否服务当前条件 |
| rows / filtered | 估算扫描及过滤比例,不是实际行数 | 与实际执行结果对照判断估算偏差 |
| actual rows / loops | 运行时每节点的输出与重复次数 | 多次循环可放大总工作量,不能只看单次输出 |
| Using index | 常见覆盖访问线索 | 不等于扫描少或响应快 |
| Using filesort | 需要额外排序的线索 | 不单独证明发生磁盘排序 |
分页 ORDER BY created_at, id 要补唯一键,否则同时间记录的先后不稳定。深 OFFSET 仍有跳过成本;顺序翻页可保存上页最后的 (created_at,id) 再查后续范围,但这不是任意页码跳转的等价替代。
事务、隔离和锁:记住的是具体规则
ACID:原子性表示事务全成或全败;一致性表示在正确操作与约束下保持规则;隔离性控制并发影响;耐久性说明提交确认后的持久化承诺,仍受实际日志和存储配置约束。
| 现象 | 一个直接例子 |
|---|---|
| 脏读 | 读到别人还没提交、随后回滚的余额 |
| 不可重复读 | 重读同一行,读到别人新提交的值 |
| 幻读 | 重复条件查询,符合条件的行集合变化 |
| 丢失更新 | 按旧读值写回,覆盖别人的修改 |
| 写偏差 | 两个事务读同一规则、修改不同行,合起来破坏跨行约束 |
InnoDB RC 每次普通一致性读建立新的语句视图;RR 通常复用事务第一次普通一致性读的视图。FOR UPDATE 是锁定读,与普通快照读不同。RR 不意味着所有读方式都永远返回同一集合,也不自动保护跨行业务规则。
记录锁保护索引记录,间隙锁限制特定索引间隙插入,Next-Key 结合记录与前方间隙。锁范围由隔离、索引访问路径、唯一条件和记录存在性决定。唯一键命中、范围搜索和缺失记录不能当作同一种加锁情形。
同一业务修改多行时统一加锁顺序;业务允许时用条件更新和受影响行数判断竞争。例如余额扣减形状为 UPDATE ... SET balance=balance-金额 WHERE id=目标 AND balance>=金额,不先把旧余额读回程序再无条件覆盖。具体 SQL 必须补上租户、币种、请求去重及事务边界。
等锁、连接满和恢复:拿到结果后怎么做
等锁时找出“等待事务 → 阻塞事务 → 阻塞者持有什么、正在等什么”。等待者 SQL 很短并不说明持有者也短;事务可能执行完 SQL 后还在等远端。死锁受害事务回滚后,清理状态并按完整业务事务有界重试。锁等待超时不应一概当作整个事务已撤销,需确认 innodb_rollback_on_timeout 等环境规则。
连接池 pending 上涨且 active 达上限,先查借用至归还的持续时间;若大量连接被空闲事务持有,修缩短持有和关闭路径。若 SQL 运行时间随并发上涨,继续加池容量可能加重数据库竞争。池的 active 是借用连接数,不等于正在使用 CPU 的 SQL 数。
Redo 恢复页修改,Undo 支撑回滚和历史版本,Binlog 记录复制/恢复链所需事件。已提交误删不能用 ROLLBACK;需要备份加连续日志,在独立实例恢复到目标时间并核对业务状态。复制延迟至少区分接收、持久化、应用与当前查询可见,副本应用完成也不刷新旧事务快照。
不建表也能判断 COUNT 与 NULL
以下是 MySQL 8 的只读 CTE 示例,不依赖业务表。COUNT(*) 预期为 2,COUNT(amount) 预期为 1,SUM(amount) 预期为 0,分别表示行数、已知金额行数、已知金额合计。没有已知金额与已知金额恰为零不能混为一个业务状态。
sql
WITH sample AS (
SELECT 1 AS id, CAST(NULL AS DECIMAL(10,2)) AS amount
UNION ALL
SELECT 2 AS id, CAST(0 AS DECIMAL(10,2)) AS amount
)
SELECT COUNT(*) AS row_count,
COUNT(amount) AS known_amount_count,
SUM(amount) AS total_amount
FROM sample;常见问题排查
| 现象 | 检查顺序 | 关键证据与下一步 |
|---|---|---|
| 查询结果重复或金额放大 | 核对业务粒度 → 单独统计两侧匹配数 → 检查 ON 与聚合位置 | 建最小反例对照预期;先修语义,再看计划,勿用 DISTINCT 掩盖模型错误。 |
| 有索引仍慢 | 固定 SQL、参数与实例 → 看估算计划 → 对照实际扫描及循环次数 → 看结果量 | 估错、范围太宽、回表或排序分别处理;新索引还需评估写入与存储成本。 |
| 获取连接超时 | 区分池等待与新建连接失败 → 看借用数、等待数、持有时间 → 查长事务与下游 | 池满而数据库空闲可能是连接持有过久;数据库过载时扩大池可能更慢。 |
| 更新一直等锁 | 查等待者 → 找阻塞事务 → 对照持有者 SQL 与事务开始时间 | 记录锁对象和事务边界;持有者可能正在等应用或远端。不要只终止等待者后宣布解决。 |
| 死锁或锁超时 | 保存错误码与死锁报告 → 还原加锁顺序 → 明确撤销范围 | InnoDB 死锁通常回滚受害事务;锁超时的撤销行为依配置。清理后有界重试完整业务事务。 |
| 写后读到旧值 | 确认主副本路由 → 查复制应用水位 → 查读事务视图 | 副本追上不刷新已有旧视图;需要读己之写时明确路由或一致性承诺。 |
| 提交时连接断开 | 保存业务请求标识 → 查询已提交结果 → 再判断重试 | 超时不证明回滚。去重记录与业务修改应同事务提交,避免重复扣款。 |
| 已提交误删 | 停止继续扩大损失 → 保存备份、日志与时间点 → 独立实例恢复并验证 | 已提交删除不能靠普通 ROLLBACK 恢复;需要完整恢复链及业务对账。 |
高频问题:操作与结果判断
以下 SQL 以 MySQL 8.0、InnoDB 为基线;先确认连接的实例。查询业务表的示例使用占位表名,不在生产库创建演示数据。
锁等待:找出阻塞连接,再决定处理对象
先读取等待关系;需要读取 sys 视图及底层监控信息的权限。
sql
SELECT waiting_pid, waiting_query, wait_age,
blocking_pid, blocking_query
FROM sys.innodb_lock_waits
ORDER BY wait_age_secs DESC;
SELECT trx_mysql_thread_id, trx_started, trx_state, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;
SELECT w.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx,
w.BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx,
b.OBJECT_SCHEMA, b.OBJECT_NAME, b.INDEX_NAME,
b.LOCK_TYPE, b.LOCK_MODE, b.LOCK_DATA
FROM performance_schema.data_lock_waits AS w
JOIN performance_schema.data_locks AS b
ON b.ENGINE = w.ENGINE
AND b.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID;示例结果:waiting_pid=81、blocking_pid=52、wait_age=00:00:18、blocking_query=NULL。含义是连接 81 等待连接 52;NULL 不代表没有阻塞,52 可能处于空闲但事务未提交状态。用第二条查询关联 52 的事务开始时间,再从连接所属应用找业务事务和远端等待。
- 阻塞者正在正常执行且在业务预算内:先限制新增竞争请求,观察等待是否下降。
- 阻塞者事务长期不结束:由所属应用完成提交或回滚;不能替未知业务事务直接提交。
- 必须取消时:先确认连接仍是同一业务连接。取消当前语句不等于结束事务;结束连接会使未提交事务回滚,回滚本身也可能耗时。这里不把 KILL 当作默认步骤。
- 查询为空但应用仍卡住:转查连接池等待、元数据锁和其他实例,不能断言没有锁问题。
第三条查询补充阻塞锁所在表、索引和锁模式;LOCK_DATA 可能为 NULL,监控快照也可能随事务结束而变化。它不能还原阻塞事务执行过的全部 SQL,历史路径仍需应用日志。
验收:等待关系消失、阻塞事务结束,连接池等待下降,并核对失败请求有没有部分成功。字段定义见 MySQL 8.0 innodb_lock_waits(Oracle 官方镜像)。
1213 与 1205:重试之前先明确事务状态
| 错误 | 默认 InnoDB 行为 | 应用处理 |
|---|---|---|
| 1213 Deadlock | 回滚选中的整个事务 | 释放资源,以同一业务身份有界重试完整事务,不只重跑最后一条 SQL |
| 1205 Lock wait timeout | 默认回滚当前语句;innodb_rollback_on_timeout 开启时行为不同 | 显式结束当前事务再决定重试,避免继续提交先前语句形成部分结果 |
重试必须受总 deadline、次数和幂等约束;事务内不放无界远程调用。重复出现时比较加锁顺序、索引扫描范围和事务持续时间,而不是无限增大等待时间。事务边界由 Spring 等框架管理时,回滚与重试应放在正确的代理调用边界。
引擎回滚范围见 InnoDB 错误处理。应用选择先结束事务再完整重试,是为了维持业务原子边界;不是说 1205 默认一定回滚整个事务。
慢 SQL:计划中的行数怎样变成检查动作
sql
EXPLAIN SELECT id FROM orders
WHERE tenant_id = 7 AND status = 'NEW'
ORDER BY created_at DESC LIMIT 20;观察 type、possible_keys、key、rows、filtered、Extra。rows 是估计扫描量,rows × filtered / 100 可粗看该表过滤后行数;多表连接还会放大,不是最终实际行数。
| 计划线索 | 下一步 |
|---|---|
| key=NULL、type=ALL、rows 很大 | 检查过滤条件、数据选择性与已有索引;全表扫描小表可能合理 |
| 有 key 但 rows 仍大 | 看是否只使用联合索引前缀、范围过宽、隐式转换或统计估计失真 |
| Using filesort | 没直接按索引序得到所需排序;不等于一定写磁盘,结合结果规模判断 |
| 估计小却实际慢 | 查锁等待、回表、返回字节量、冷热缓存和实际执行行数 |
此例可评估 (tenant_id, status, created_at) 的收益,但要同时计算写入和存储成本。EXPLAIN ANALYZE 会执行语句,不能把它当作普通只读计划查看;应在可控数据与执行预算下使用。
连接满:是连接泄漏还是正常请求积压
sql
SHOW GLOBAL STATUS WHERE Variable_name IN
('Threads_connected', 'Threads_running', 'Max_used_connections');
SHOW VARIABLES LIKE 'max_connections';
SHOW FULL PROCESSLIST;Threads_connected 是连接数,Threads_running 是非休眠线程线索,不等于 CPU 正在运行的线程数。长时间 Sleep 且无事务可能只是池中空闲连接;Sleep 加长事务则需要查事务归还路径。
数据库连接不多、应用 pool pending 高:检查池上限、借用时长、连接校验和是否连错实例。数据库接近上限:汇总所有副本和服务的池配置,而不是每个服务分别扩池。大量连接集中等待同一 SQL/锁:先处理瓶颈,再看连接容量。验收应同时看池等待、连接借用时长、超时和数据库负载。
四级隔离:选择什么能避免哪类问题
| 隔离级别 | InnoDB 普通读取的主要规则 | 不能据此保证什么 |
|---|---|---|
| READ UNCOMMITTED | 可能读到未提交修改 | 不能作为可靠业务判断依据 |
| READ COMMITTED | 每次一致性读建立新快照 | 同一事务两次查询可能变化 |
| REPEATABLE READ | 通常复用首次一致性读建立的快照 | 快照读与锁定读不应混作同一时间视图 |
| SERIALIZABLE | 更强隔离;普通 SELECT 的锁行为受事务和 autocommit 等条件影响 | 并发和等待成本不会消失 |
以上是 InnoDB 的实践提示,不是其他数据库的通用实现表。库存扣减优先用带条件的原子更新并检查影响行数,而不是“先查库存,再无条件写回”;跨行约束还需要明确事务和锁定范围。
工具与参数
以下在已确认实例的 SQL 客户端使用。观察权限和 Performance Schema 配置可能限制结果;不要为采样重置累计统计。下表给出常用字段的含义。
| 操作 | 看什么 | 条件与边界 |
|---|---|---|
SELECT VERSION(), @@hostname, @@port, CONNECTION_ID(); | 版本、服务器与当前会话 | 先证明连对实例,代理地址与后端实例不是一回事 |
SHOW FULL PROCESSLIST; | 当前连接、状态、SQL | 是快照;Sleep 不等于泄漏,完整 SQL 可能含敏感值 |
SHOW ENGINE INNODB STATUS; | 事务、等待、最近死锁 | 最近记录可能覆盖,保存时间与实例;部分信息需要 PROCESS 权限 |
SELECT * FROM performance_schema.data_lock_waits; | 锁等待者与阻塞关系 | MySQL 8 系列;还需关联事务和 SQL,单独一张表不解释责任路径 |
EXPLAIN SELECT ...; | 估算访问路径、索引、行数 | 这里的省略号需换成真实查询;估算不是实测。 |
EXPLAIN ANALYZE SELECT ...; | 实际执行、循环与耗时 | MySQL 8.0.18 起支持;会执行查询,先在有预算的测试环境使用,不能当纯静态检查 |
SHOW REPLICA STATUS; | 通道、接收、应用和错误 | 较新 MySQL 8 命名;旧版命令不同,延迟秒数不能独立证明最新数据可见 |
max_connections、池容量 | 实例连接上限与应用借用上限 | 汇总所有实例和数据源预算;上限不代表数据库有能力并发执行这么多 SQL |
易错点与记忆提示
- 先粒度,再条件,再计划:仅在结果语义已明确后讨论访问成本。
- 锁看访问路,不只看返回行:有唯一点查也要核对记录是否存在与实际计划。
- 超时是未知,重试要去重:尤其是提交响应丢失。
- 备份要能恢复:检查日志链、恢复时长和最终业务约束,而非只检查备份文件存在。
- 历史事实与当前资料分开:订单地址快照不应随客户资料更新。
- 一次快不代表容量够:比较同窗口负载、等待与资源,不能只报平均 SQL 耗时。
参考入口
MySQL 8.0 EXPLAIN 文档;具体环境的可执行语法、权限和版本差异按产品手册确认。完整实验可按末尾链接继续阅读。
原理与完整示例
需要原因、源码或完整实验时再阅读:
- 05 - JOIN 为什么会把数据查重:从关系代数理解多表查询
- 25 - 慢 SQL 的完整排查流程:从监控告警到执行计划
- 23 - 数据库连接突然耗尽:从连接池排到慢 SQL
- 19 - 一条 SELECT 为什么会锁住别人
- 20 - 死锁不是“数据库卡死”:如何复现、读图和修复
- 18 - MVCC 如何用历史版本减少读写等待
- 16 - 转账业务怎样保证不多钱也不少钱:ACID 的工程含义
- 28 - 备份成功不等于能恢复:数据库恢复演练指南
- 26 - DBA 常用命令实战:会查会停,更要知道风险
- 14 - EXPLAIN 到底怎么看:从执行计划找到真正的慢点
- 04 - 数据库范式不是教条:冗余字段到底该不该加
- 24 - CPU 100% 但磁盘不忙:数据库性能瓶颈怎么判断
- A - 数据库实验与证据验证方法
- 01 - 一条 SQL 从客户端发送到返回结果,数据库经历了什么
- 06 - WHERE、HAVING 和 ON 放错位置,结果为什么会变
- 10 - SQL 中的 NULL 为什么不等于“空”
- 07 - GROUP BY 为什么经常算错:聚合、去重与窗口函数
- 12 - 联合索引怎么设计:最左匹配不是背口诀
- 11 - B+ Tree 索引为什么适合数据库,而不是普通二叉树
- 08 - 分页越往后越慢:LIMIT 深分页的原理与改写
- 22 - Binlog、Redo Log、Undo Log 到底有什么区别
- 27 - 主从延迟导致“刚写入就查不到”,该怎么解决