跳转至

04 · MySQL 数据库八股文速成手册

核心概念

💡 定义(小白版):数据库就是帮我们存数据、还能又快又准查数据的软件仓库;关系型数据库把数据存进一张张表格(行=一条记录,列=一个属性),表与表之间还能用共同字段“关联”起来,MySQL 就是最常见的一种。 🏷️ 类比:数据库=图书馆;每张表=一个书架分类;行=一本本书;列=书名、作者、价格这些属性。借书单和书目表能对上,靠的就是共同的“书号”(关系)。 💡 定义(小白版):SQL 是和数据库对话的“普通话”:你想查什么、改什么,写一句 SQL,数据库听懂就照做。 🏷️ 类比:SQL 像点菜:SELECT 菜名 FROM 菜单 WHERE 价格<50 就是“从菜单里挑出 50 块以下的菜”。

(a) 🗂️ MySQL 八股全景:索引、事务与 SQL 三足鼎立

数据库八股分三大块:索引(数据结构 + 分类 + 失效 + EXPLAIN)、事务(ACID + 日志 + 隔离级别 + MVCC + 锁)、以及 SQL 手写(JOIN / 窗口函数 / 注入防护)。面试时先讲原理再用 SQL 落地,这张图就是整篇的地图。

(该图在 GitHub/VSCode + mermaid 插件中可渲染)

flowchart TB
    Db["🐬 MySQL 八股全景"]
    Db --> Tree["🌳 索引结构<br/>B 树 / B+ 树 / 哈希"]
    Tree --> CIdx["🗂️ 索引分类<br/>聚簇 / 回表 / 覆盖 / 最左前缀"]
    CIdx --> Invalid["💥 索引失效场景"]
    Tree --> Explain["🔍 EXPLAIN 与慢查询优化"]
    Db --> Acid["📜 事务 ACID 与日志体系"]
    Acid --> Iso["🎚️ 隔离级别"]
    Iso --> Mvcc["👁️ MVCC 版本链 + ReadView"]
    Iso --> Lock["🔒 锁机制与死锁"]
    Db --> Sql["🛠️ SQL 手写与注入防护"]
  • 🌳 索引结构:B+ 树是 InnoDB 默认索引:叶子节点串成链表、非叶子只存键,扇出大、树矮,三四层就能撑千万级数据,磁盘 IO 次数少。哈希索引等值查询 O(1) 但不支持范围查询,面试常考「为什么 MySQL 用 B+ 树而不用 B 树 / 哈希」。
  • 🗂️ 索引分类:聚簇索引的叶子直接存整行(主键),二级索引的叶子存主键值——查二级索引要「回表」再按主键查一次。覆盖索引让查询只扫索引就够、免回表;联合索引遵守最左前缀原则;ICP(索引下推)把过滤下放到索引层减少回表。
  • 💥 索引失效场景:对列套函数或运算、隐式类型转换、% 开头的 LIKE、OR 里含非索引列、!= / NOT IN 等否定条件都会让索引失效。这份清单是背诵版,面试直接报「场景 + 原因」(破坏了有序性 / 优化器放弃索引)。
  • 🔍 EXPLAIN 与慢查询优化:EXPLAIN 是定位慢 SQL 的第一步,重点看 type(system → const → ref → range → index → ALL 从优到劣)、key / key_len、Extra。慢查询优化口诀「先 EXPLAIN → 查索引失效 → 改写 SQL / 分页优化」要能背。
  • 📜 事务 ACID 与日志体系:ACID 四属性(原子 / 一致 / 隔离 / 持久)靠 undo log(回滚 + MVCC 版本链)、redo log(WAL:先写日志再落盘)、binlog(主从复制)落地;redo 与 binlog 的两阶段提交保证崩溃恢复不丢不重。
  • 🎚️ 隔离级别:读未提交 / 读已提交 / 可重复读 / 串行化对应脏读、不可重复读、幻读三种问题。MySQL 默认 RR 靠 next-key lock(记录锁 + 间隙锁)解决幻读,这是必背辨析。
  • 👁️ MVCC 版本链 + ReadView:每行有隐藏字段(trx_id、roll_pointer),undo log 串成版本链;ReadView 记录活跃事务列表,按「已提交且早于快照可见、活跃不可见、晚于快照不可见」判断可见性。快照读(普通 SELECT)靠 MVCC,当前读(UPDATE 等)靠加锁。
  • 🔒 锁机制与死锁:锁按粒度分表锁 / 行锁(InnoDB 行锁基于索引),按模式分共享锁与排他锁;乐观锁靠版本号 + 重试,悲观锁直接加锁。死锁四个必要条件与「超时回退」破解思路要会用代码演示。
  • 🛠️ SQL 手写与注入防护:JOIN 全家桶(INNER / LEFT / RIGHT)、聚合与 GROUP BY 陷阱、窗口函数(ROW_NUMBER / RANK)与 GROUP BY 的本质区别、CASE WHEN 行列转换、子查询与 CTE 都是手写题模板;SQL 注入必须用参数化查询(? 占位符)防护。

面向秋招算法/后端岗位的数据库面试突击手册。 覆盖:索引原理与失效、EXPLAIN、事务 ACID、隔离级别、MVCC、锁、慢查询优化与高频常识题。 每个主题都包含:定义/原理讲解、公式或伪代码、可直接运行的 Python 3 代码示例、对比表格、面试追问。 背诵建议:先读"定义"段建立框架,再看"对比表"抓差异,最后用"追问"自测。


1. 索引原理:B 树 vs B+ 树 vs 哈希索引

💡 定义(小白版):索引是数据库给某些列额外做的一份“排好序的目录”:查询时先查目录定位,再直接翻到目标位置,不必把整张表从头到尾扫一遍。 🏷️ 类比:没索引=在一本没有目录的厚书里逐页翻;有索引=先看目录找到页码,直接翻到那一页。代价是目录要占纸(占空间)、抄目录要花时间(拖慢写入)。

1.1 为什么需要索引

定义:索引(Index)是数据库维护的一种额外的有序数据结构,目的是把"全表扫描"(线性 O(n))变为"树查找"(对数 O(log n)),以空间换时间。

原理讲解:没有索引时,InnoDB 只能从第一行开始逐行扫描,直到找齐所有匹配行;有了索引,查询可以沿着索引树二分定位,只访问少量页面。索引的本质是"书的目录"——但目录本身也要占空间,所以是以写换读:插入/删除/更新时都要维护索引树。

伪代码(查找一次索引定位的成本模型):

全表扫描成本   = 总页数 × 每页 IO 成本
索引查询成本   = 树高 × 每层 IO 成本 + 回表成本(如果有)
当 总页数 >> 树高 时,索引收益显著

1.2 B 树(B-Tree)

💡 定义(小白版):B 树是一种“一个节点能装很多个键、还能自动长高”的平衡多路查找树,每个节点里既放关键字又放数据,树矮所以查询快。 🏷️ 类比:像多层档案柜:每个抽屉(节点)里按顺序放着一叠档案(key+数据),找档案时先看柜子上贴的标签范围,判断进哪个抽屉。

定义:B 树是一棵多路平衡搜索树,每个节点最多有 m 个子节点(称为 m 阶 B 树),每个节点内同时存放索引键(key)和数据(data/指针)。

结构图(3 阶 B 树示意,ASCII):

                [ 30 | 70 ]                ← 根节点:key 也有 data
               /     |     \
        [10|20]   [40|50]   [80|90]        ← 中间节点:key 也有 data
        /   \      /   \       \           ← 叶子节点在同一层
      [5] [15]  [35] [45]   [75]

特征: - 每个节点可存多个 key,减少树高 → 减少磁盘 IO; - 非叶子节点也存 data → 一个节点能容纳的 key 数量变少; - 节点间没有链表指针 → 范围查询需要中序遍历,跨节点跳转频繁。

1.3 B+ 树(B+ Tree,InnoDB 默认)

💡 定义(小白版):B+ 树是 B 树的升级版:上层节点只放“索引键”(相当于目录),所有数据全部放在最底层叶子节点,叶子之间用双向链表串成一条链。 🏷️ 类比:B+ 树=图书馆的“检索卡片柜”(上层只有编号,不摆书)+“一排排书架”(叶子层,书按编号顺序连成一串)。要找某一区间的所有书,顺着书架链走就行。

定义:B 树的变体。非叶子节点只存 key(纯索引),所有数据都存放在叶子节点,且叶子节点通过双向链表串起来。

结构图(示意):

                    [ 30 | 70 ]                 ← 非叶子:只有 key,无 data
                   /     |      \
              [10|20]  [40|50]   [80|90]        ← 非叶子:只有 key
               /         |           \
    [1|5|10]---[12|15|20]---[35|40|45]---[60|70|80|90]   ← 叶子:存全部数据
        ↑        ↑            ↑            ↑
        └────────┴─── 叶子间双向链表 ────────┘

特征: - 非叶子节点不存 data → 单个 16KB 页面可容纳更多 key → 树更矮 → 查询 IO 次数更少; - 叶子节点存全部数据 → 任何等值查询都要走到叶子,查询路径长度稳定(更利于预判成本); - 叶子间有链表 → 范围查询只需在链表上顺序扫描,无需回溯中序遍历; - 天然聚簇友好:数据按主键顺序组织在叶子页上,顺序插入不打乱已有页。

1.4 哈希索引

定义:对索引键做哈希函数映射到桶(bucket),等值查询 O(1),每个桶内用链表/树解决冲突。

优点:等值查询极快(= / IN)。

致命缺点: - 无法范围查询(>、<、BETWEEN 全部退化为全扫); - 无法排序,无法利用索引有序性做 ORDER BY; - 联合索引哈希后丢失列之间的顺序 → 不支持最左前缀; - InnoDB 的哈希索引是自适应哈希索引(AHI),由引擎自动为热点页建立,DBA 无法手动指定;Memory 引擎才支持手动哈希索引。

1.5 为什么 MySQL InnoDB 用 B+ 树(磁盘 IO 次数推导)

原理讲解:磁盘 IO 一次只能读一个页面(默认 16KB)。树高 = 查询需要的 IO 次数,因此目标是把树高压到 3~4 层。

公式(非叶子节点扇出估算):

非叶子节点可存 key 数 = 页面大小 / (索引键字节 + 指针字节)
                     = 16 * 1024 / (8 + 6) ≈ 1170   (bigint 主键 + 6B 指针)
叶子页可存行数       ≈ 16KB / 单行大小(约 1KB) ≈ 16 行
3 层 B+ 树可存行数   ≈ 1170 × 1170 × 16 ≈ 21,902,400 ≈ 2100 万行

即:2100 万行级别的表,主键查询只需 3 次磁盘 IO。如果用 B 树,非叶子节点还要存 data(假设 1KB),扇出只剩约 16,树高直接翻倍。

Python 3 可运行示例(扇出与容量估算):

# btree_fanout.py —— 计算 B+ 树容量,理解"矮树 = 少 IO"
PAGE_SIZE = 16 * 1024          # InnoDB 默认页大小 16KB
key_bytes = 8                  # bigint 主键 8 字节
ptr_bytes = 6                  # 页指针约 6 字节(InnoDB 行格式)
row_bytes = 1024               # 假设单行约 1KB

fanout = PAGE_SIZE // (key_bytes + ptr_bytes)   # 每个非叶子页能塞几个 key
rows_per_leaf = PAGE_SIZE // row_bytes          # 每个叶子页能塞几行

print(f"非叶子页扇出: {fanout}")                 # 约 1170
for h in (2, 3, 4):
    capacity = fanout ** (h - 1) * rows_per_leaf
    print(f"{h} 层 B+ 树可容纳约 {capacity:,} 行,即 IO 次数约 {h} 次")

1.6 对比表格

维度 B 树 B+ 树 哈希索引
非叶子节点内容 key + data 仅 key 桶(key 哈希后映射)
数据存放位置 每个节点都可能有 全部在叶子节点 桶内
叶子节点链表 无 有(双向链表) 无
等值查询 O(log n) O(log n) O(1)
范围查询 中序遍历,慢 链表顺序扫描,快 不支持
排序 / 最左前缀 支持 支持 不支持
查询稳定性 不稳定(可能中途命中) 稳定(必到叶子) 稳定
MySQL 使用场景 较少(如某些历史版本) InnoDB 默认 Memory 引擎 / AHI

1.7 面试追问

💡 答法提示:答索引类追问先抛结论(树更矮 / 扇出更大 / 叶子链表),再补一句磁盘 IO 推导(树高=IO 次数),最后点出“数据全在叶子、路径稳定”作为加分项——不用背全部,抓住“宽而矮 + 顺序扫描”两个关键词。

Q1:为什么 B+ 树比 B 树更适合做数据库索引?

标准回答三连:① 非叶子节点只存 key,扇出更大、树更矮,磁盘 IO 更少;② 叶子链表让范围查询和排序只需顺序扫描;③ 数据全在叶子,查询次数恒定,成本可预估。B 树非叶节点带 data,扇出小、范围查询要回溯中序遍历。

Q2:为什么不用红黑树/AVL?

红黑树是二叉的,树高约为 log₂n,16KB 页面只能存 2 个 key,扇出远小于 B+ 树;同样 2000 万行,红黑树树高 ~24 层,即 24 次磁盘 IO,而 B+ 树只需 3 层。内存中的 TreeMap 用红黑树没问题,因为内存 IO 便宜;磁盘场景必须"宽而矮"。

Q3:哈希索引那么快,为什么 MySQL 主索引不用它?

等值查询 O(1) 确实快,但数据库 80% 场景是范围查询、排序、前缀匹配;哈希无法利用有序性,联合索引哈希后最左前缀也失效。另外哈希碰撞和扩容会引入不确定性,磁盘随机 IO 反而更贵。

Q4:3 层 B+ 树大概能存多少数据?怎么算的?

见 1.5 公式:16KB 页、bigint 主键时约 2100 万行;把"树高=IO 次数"讲清楚即可拿分。

易错点:不要把 B+ 树说成"B 树的升级版只多了链表"——关键差异是"非叶子节点只存 key 不存 data",这才是扇出与树高的决定性因素;链表示范范围查询是第二层理由。


2. 索引分类:聚簇 / 非聚簇、回表、覆盖索引、最左前缀、ICP

💡 定义(小白版):索引常见五种:主键索引(每表一个、非空且不重复)、唯一索引(值不得重复)、普通索引(只加速查询、允许重复)、联合索引(把多列拼成一个索引)、全文索引(对长文本分词后检索)。 🏷️ 类比:主键索引=身份证号(一人一个);唯一索引=手机号(不能撞号);普通索引=姓名(重名也能查);联合索引=“省→市→区”三级地址;全文索引=查字典按“字义”而不是按“首字母”找。

2.1 聚簇索引 vs 非聚簇索引(二级索引)

💡 定义(小白版):聚簇索引的叶子节点直接存整行数据(InnoDB 的主键索引就是它,数据按主键顺序物理存放);非聚簇索引(二级索引)的叶子只存“索引列+主键值”,不保存整行。 🏷️ 类比:聚簇索引=按学号排座位的教室,座位号就是本人;二级索引=“姓名→学号”的花名册,查到学号后还得回教室找人。

定义: - 聚簇索引(Clustered Index):索引键的顺序就是数据行的物理存储顺序。InnoDB 的主键索引就是聚簇索引,叶子节点直接存放整行数据。 - 非聚簇索引 / 二级索引(Secondary Index):叶子节点存放的是索引键 + 主键值,而不是整行数据;数据和索引分开存储(MyISAM 的索引全部是非聚簇的,叶子存行指针)。

叶子节点差异(重点背):

主键索引(聚簇)叶子节点: [ 主键值 | 整行数据(user_id, name, age, ...) ]
二级索引(非聚簇)叶子节点: [ 索引列值 | 主键值 ]

2.2 回表(Back to Table)

💡 定义(小白版):用二级索引查到的是主键值,再拿这个主键值回主键索引树里查一次完整行,这第二次查询就叫回表。 🏷️ 类比:老师用“姓名花名册”点名,拿到你的学号后,还要回教室按学号找到你本人——先查册子、再找人,一共两步。

定义:通过二级索引查到的是主键值,还要再回主键索引树根据主键查一次完整行,这个过程叫回表(回表查询)。

伪代码(回表流程):

SELECT * FROM user WHERE name = '张三';
步骤1: 走二级索引 idx_name → 找到主键 id = 5      (1 次索引查询)
步骤2: 用 id=5 回主键聚簇索引 → 取出完整行          (第 2 次索引查询)
总 IO 次数 = 二级索引树高 + 聚簇索引树高

优化手段:尽量用覆盖索引避免回表。

2.3 覆盖索引(Covering Index)

💡 定义(小白版):如果一条查询要的列全部包含在某个索引里,数据库只扫索引就能拿到答案、不用回表,这个索引就叫覆盖索引。 🏷️ 类比:花名册上刚好印了“姓名+学号+电话”,查电话时翻名册就够了,不用再专门跑一趟教室——少一次跑腿(少一次回表 IO)。

定义:查询所需的所有列都包含在某个二级索引中,此时引擎可以直接返回索引里的值,无需回表。EXPLAIN 的 Extra 列会出现 Using index。

示例 SQL:

-- idx_age(age) 二级索引
SELECT age FROM user WHERE age > 20;   -- 只查 age,age 就在索引里 → 覆盖索引,不回表
SELECT *  FROM user WHERE age > 20;    -- 查所有列,age 索引没有 name → 需要回表

Python 3 可运行示例(用字典模拟聚簇/二级索引与回表成本):

# cover_index.py —— 模拟"回表"与"覆盖索引"的差别
rows = {1: ("张三", 25), 2: ("李四", 30), 3: ("王五", 20)}
# 二级索引 idx_age: age -> [主键列表]
idx_age = {25: [1], 30: [2], 20: [3]}

def query_by_age_cover(age: int) -> list:
    """覆盖索引:只返回 age,索引里就有,不回表"""
    pks = idx_age.get(age, [])
    return [f"age={age}" for _ in pks]

def query_by_age_full(age: int) -> list:
    """非覆盖索引:取 age 对应的主键,再回表拿整行"""
    pks = idx_age.get(age, [])
    return [rows[pk] for pk in pks]

print(query_by_age_cover(25))   # ['age=25'],不触碰 rows
print(query_by_age_full(25))    # [('张三', 25)],发生了回表

2.4 最左前缀原则(Leftmost Prefix)

💡 定义(小白版):联合索引 (a,b,c) 相当于依次建了 (a)、(a,b)、(a,b,c) 三个索引:查询必须从最左列 a 开始连续命中,跳过左边的列,右边的列就用不上。 🏷️ 类比:查“省→市→区→街道”的四层地址簿必须从“省”逐级往下翻;只报“区名”没法直接定位到你家在哪个省。

定义:联合索引 (a, b, c) 在逻辑上相当于建立了 (a)、(a,b)、(a,b,c) 三个索引。查询必须从最左列开始连续命中,否则无法利用该索引。

CREATE INDEX idx_abc ON t(a, b, c);
WHERE a = 1                 -- ✅ 用索引(前缀 a)
WHERE a = 1 AND b = 2       -- ✅ 用索引(前缀 a,b)
WHERE a = 1 AND b = 2 AND c = 3  -- ✅ 全命中
WHERE b = 2 AND c = 3       -- ❌ 没带 a,索引失效
WHERE a = 1 AND c = 3       -- ⚠️ 只用 a 定位,c 无法利用(中间断了)

原理:B+ 树对联合索引先按 a 排序、a 相同按 b、b 相同按 c;跳过了最左列,索引的有序性对查询条件毫无帮助。

2.5 索引下推(Index Condition Pushdown, ICP)

💡 定义(小白版):索引下推=把 WHERE 里能在索引层过滤的条件“下放”给存储引擎,在索引遍历时就先过滤掉不匹配的行,少回表、少传数据。 🏷️ 类比:先在海选名单(索引)上就把不符合条件的人划掉,再叫剩下的去面试(回表)——而不是让所有人都跑到面试现场再刷人。

定义:MySQL 5.6 引入的优化。以前是"索引只负责定位主键,回表拿到整行后再在 Server 层做 WHERE 过滤";ICP 允许把部分 WHERE 条件下推到存储引擎,在索引遍历时先过滤再回表,减少回表次数。

示例 SQL:

-- 联合索引 (age, name)
SELECT * FROM user WHERE age > 20 AND name LIKE '张%';
-- 无 ICP:先按 age 找到所有主键 → 全部回表 → Server 过滤 name
-- 有 ICP:遍历索引时先过滤 name LIKE '张%' → 只回表剩余少数行

EXPLAIN Extra 列出现 Using index condition 即表示触发了 ICP。

2.6 对比表格

概念 核心差异 关键标识
聚簇索引 叶子存整行,物理顺序=键顺序 主键唯一,InnoDB 必有
二级索引 叶子存 (键, 主键) 可多个,可能回表
回表 二级索引→主键→整行,二次 IO 性能杀手,应消除
覆盖索引 查询列全在索引中,免回表 EXPLAIN 见 Using index
最左前缀 联合索引必须从最左列连续用 跳列即失效
ICP 过滤下推,先过滤后回表 EXPLAIN 见 Using index condition

2.7 面试追问

💡 答法提示:回表、覆盖、最左前缀三类题都可先用“目录/花名册”类比一句话定性,再落到“叶子节点存主键值”这个机制事实;追问“为什么存主键不存指针”答“行地址会随页分裂变化”。

Q1:为什么二级索引叶子存主键值而不是行指针?

因为 InnoDB 表是聚簇的,数据行的物理位置可能随页分裂/合并变化;如果存指针,指针要随搬迁更新,成本高。存主键值,回表时走聚簇索引查找,位置变化不影响二级索引的正确性。

Q2:覆盖索引是不是索引列越多越好?

不是。索引是额外存储,写操作要同步维护;列的宽度也影响扇出与树高。只应把高频查询中确实需要的列放进联合索引,避免盲目加列。

Q3:最左前缀有个"跳跃扫描"是什么?

MySQL 8.0 引入了 SKIP SCAN(跳跃扫描),允许某些"跳过中间列"的查询(如 b=2 AND c=3 而 a 值域很小)通过枚举 a 的取值来用索引。它是优化器特性,不是通用解法,别把它当原则。

Q4:ICP 和覆盖索引什么区别?

覆盖索引是"干脆不回表";ICP 是"减少回表次数"。ICP 主要减少回表行数,覆盖索引是零回表,两者可叠加。

易错点:面试常说"二级索引叶子存主键",MyISAM 恰恰相反(存行指针、数据和索引分离),两者别混;InnoDB 才是二级索引存主键。


3. 索引失效场景清单(背诵版)

💡 定义(小白版):明明建了索引,可数据库优化器却选择不用它、改回全表扫描,这种情况就叫“索引失效”。 🏷️ 类比:目录印好了,但你把要找的词改写成“倒着读”或“变了形”,翻目录就对不上了——索引也有自己的“查找格式”,一旦不匹配就失效。

以下每种场景都是高频面试题。注意:MySQL 优化器最终决策依赖数据分布与统计信息,个别场景(如 !=)在覆盖索引下仍可能走索引,面试回答核心是"为什么多数情况下会失效",并补充"需以 EXPLAIN 验证"。

3.1 对索引列使用函数或运算

WHERE YEAR(create_time) = 2023      -- ❌ 对索引列套函数,索引失效
WHERE create_time >= '2023-01-01'
  AND create_time <  '2024-01-01'   -- ✅ 改写为范围查询,可走索引
WHERE id + 1 = 10                   -- ❌ 索引列参与运算
WHERE id = 10 - 1                   -- ✅ 常数运算放右侧,可走索引

原理:索引保存的是列的原始值,函数/运算后的结果与索引键不匹配,B+ 树无法二分定位,只能全扫。

3.2 隐式类型转换

-- phone 列是 varchar,传入数字 138...
WHERE phone = 13800138000           -- ❌ 数字被转成字符串再比,类型不匹配导致失效
WHERE phone = '13800138000'         -- ✅ 显式字符串,可走索引

原理:MySQL 对字符串列与数字比较时,会把列本身隐式 CAST 后再比较,等价于对索引列套了函数。反过来的场景(数字列比字符串)也可能失效,需看 EXPLAIN。

3.3 % 开头的 LIKE

WHERE name LIKE '张%'   -- ✅ 前缀匹配,可走索引(范围查找)
WHERE name LIKE '%张'   -- ❌ 后缀匹配,索引失效
WHERE name LIKE '%张%'  -- ❌ 双侧通配,索引失效

原理:B+ 树按键排序,"张%"能确定起始位置做范围扫描;"%张"无法确定起始点,只能全扫。

3.4 OR 条件中含有非索引列

-- id 有索引,name 无索引
WHERE id = 1 OR name = '张三'   -- ❌ 只要 OR 一侧无索引,整个条件退化全表扫描
-- 改写:
WHERE id = 1
UNION ALL
WHERE name = '张三'             -- ✅ 两侧各自走索引后合并

原理:OR 语义是"满足其一即可",优化器需要同时访问两条路径,无法用单个索引完成,多数情况选择全扫(UNION 可拆解)。

3.5 !=、<>、NOT IN 否定条件

WHERE age != 20          -- ⚠️ 大概率失效(若覆盖索引 + 选择性高,可能走全索引扫描)
WHERE age <> 20          -- ⚠️ 同上
WHERE id NOT IN (1,2,3)  -- ⚠️ 大概率失效,数据占比较高时优化器选全扫

原理:B+ 树擅长"命中一段有序区间",否定条件的命中范围几乎覆盖全表,优化器会核算成本;除非数据极少,否则全扫更划算。

3.6 IS NULL / IS NOT NULL

WHERE age IS NULL        -- ✅ MySQL 8 中可走索引(index/ref 取决于列是否允许 NULL)
WHERE age IS NOT NULL    -- ⚠️ 多数情况下优化器选全表扫描(命中比例太大)

易错点:很多八股说"IS NULL 一定失效",这是旧版结论;MySQL 8 支持对可空列的空值建立索引记录,是否失效以 EXPLAIN 为准。

3.7 最左前缀不满足

-- 联合索引 (a, b, c)
WHERE b = 1              -- ❌ 跳过最左列 a
WHERE a = 1 AND c = 3    -- ⚠️ 只用 a,c 被"断列"(见 2.4)

3.8 汇总对照表

场景 是否失效 核心原因
索引列套函数/运算 ❌ 失效 键值与索引序不匹配
隐式类型转换 ❌ 失效 等价于对列 CAST
LIKE '%xx' ❌ 失效 无起始点可定位
OR 含非索引列 ❌ 失效 无法单索引覆盖
!= / <> / NOT IN ⚠️ 多数失效 命中面太大,成本高
IS NULL ✅ MySQL 8 可用 空值可入索引
最左前缀不满足 ❌ 失效 有序性用不上

Python 3 可运行示例(验证"函数导致失效"的直觉):

# index_invalid.py —— 模拟索引失效:函数破坏有序性后只能线性扫
import time

def linear_scan(data, cond):
    return [x for x in data if cond(x)]

def index_lookup(index, key):
    return index.get(key, [])

data = list(range(100_000))
index = {x: x for x in data}          # 模拟 B+ 树等值查找

# 命中索引:O(1)
t = time.perf_counter(); index_lookup(index, 99999); t1 = time.perf_counter() - t
# 对列套函数:等价全扫 O(n)
t = time.perf_counter(); linear_scan(data, lambda x: x % 2 == 0 and x < 99999); t2 = time.perf_counter() - t
print(f"索引命中耗时: {t1*1e6:.1f}us   函数过滤全扫耗时: {t2*1e3:.1f}ms")

3.9 面试追问

💡 答法提示:索引失效题背口诀“函数/运算、隐式转换、左模糊、OR 非索引列、否定条件、跳过最左列”,每一条都能用“破坏了索引的有序性/无法前缀匹配”一句话解释,再拿 EXPLAIN 验证收尾。

Q1:为什么 like '%张%' 用全文索引能救?

全文索引(FULLTEXT)不依赖 B+ 树的有序前缀,而是把文本分词后建倒排索引,所以子串匹配可以命中。但它是为"文档检索"设计,普通 varchar 业务列慎用。

Q2:隐式类型转换一定失效吗?

不一定,取决于哪一侧被转换。规则:字符串列 vs 数字时,MySQL 把字符串列转成数字(列上套 CAST)→ 失效;反过来数字列 vs 字符串数字,优化器可能把常量转成数字,仍走索引。所以"看 EXPLAIN、写显式类型"才是标准答案。

Q3:一条 SQL 明明没毛病却全表扫描,还能怎么办?

① EXPLAIN 看 rows/type 确认原因;② FORCE INDEX 或 USE INDEX 强行指定(不推荐生产用);③ ANALYZE TABLE 更新统计信息;④ 改写 SQL 满足可索引形式;⑤ 优化器认为全扫更便宜时,考虑让查询走覆盖索引提高成本优势。

易错点:背失效场景时别绝对化。面试官追问"一定失效吗",答"多数情况失效,与优化器成本模型相关,以 EXPLAIN 为准"比死背结论得分高。


4. EXPLAIN 解读

💡 定义(小白版):EXPLAIN 是 MySQL 给 SQL 查询“体检”的命令:在 SQL 前面加 EXPLAIN,就能看到数据库打算怎么查(走没走索引、扫描多少行),不用真正执行。 🏷️ 类比:EXPLAIN=导航的地图预览:出发前先看规划路线(走什么路、经过几个路口),而不是真跑一趟才知道堵不堵。

4.1 什么是 EXPLAIN

💡 定义(小白版):EXPLAIN 的返回值叫“执行计划”,就是数据库优化器给这条 SQL 选定的具体执行方案:先查哪张表、走哪个索引、预估扫多少行。 🏷️ 类比:执行计划=外卖 App 显示的“预计配送路线”:骑手走哪条路、途经几个取餐点都标得清清楚楚。

定义:EXPLAIN SELECT ... 输出 MySQL 优化器对这条 SQL 的执行计划:访问方式(type)、用哪个索引(key)、扫描多少行(rows)、额外动作(Extra)。

4.2 type 访问类型(从优到劣)

type 含义 说明
system 单行系统表 极端场景,几乎不出现
const 主键/唯一键等值 WHERE id = 1,最多一行,最快
eq_ref 被驱动表按主键/唯一键等值连接 典型 join 场景
ref 普通二级索引等值 WHERE name = 'xx'(非唯一)
range 索引范围扫描 > < BETWEEN LIKE 'xx%'
index 全索引扫描 遍历整个索引树,比 ALL 好一点
ALL 全表扫描 最差,需要警惕

排序口诀:"系统 const,eq_ref 接 ref;range 之后 index,最后全扫 ALL"。

4.3 关键列解读

key        :实际使用的索引名(为 NULL 说明没走索引)
key_len    :使用的索引键字节长度(可算出用了联合索引的几列)
rows       :优化器估算的扫描行数(越小越好)
filtered   :经过 WHERE 过滤后的剩余比例(估算)
Extra      :额外信息,见下表

key_len 计算示例(Python 3):

# key_len.py —— 计算 varchar/char 列在 utf8mb4 下的 key_len
def key_len_utf8mb4(char_len, nullable=True):
    # utf8mb4 每字符最多 4 字节;varchar 额外 2 字节存长度;NULL 标记 1 字节
    return char_len * 4 + 2 + (1 if nullable else 0)

print("varchar(20) nullable:", key_len_utf8mb4(20))   # 83
print("varchar(20) not null:", key_len_utf8mb4(20, False))  # 82
# EXPLAIN 里 key_len=83 → 说明联合索引 (a varchar(20), b int) 中 a 用了 83 字节
# 若 key_len=88,说明 b(int 4B + NULL 1B) 也参与进来了

4.4 Extra 常见值

Extra 含义 好坏
Using index 覆盖索引,不回表 优
Using where 存储引擎返回后在 Server 层过滤 中
Using index condition 触发索引下推 ICP 良
Using filesort 结果需额外排序(非索引序) 需优化
Using temporary 需要临时表(GROUP BY/ORDER BY 不一致等) 需优化
Using index; Using temporary 临时表建立在索引上 中

Using filesort 优化:让 ORDER BY 列与索引前缀一致即可消除:

-- 联合索引 (age, create_time)
SELECT * FROM t WHERE age = 20 ORDER BY create_time;  -- ✅ 索引序即结果序,无 filesort
SELECT * FROM t WHERE age > 20 ORDER BY create_time;  -- ⚠️ 范围后排序无法完全利用,可能 filesort

4.5 面试追问

💡 答法提示:EXPLAIN 追问先按“type 从好到差”排出访问类型,再讲 rows(预估扫描行数)与 Extra(Using index 是加分项);拿一条真实 SQL 走一遍 EXPLAIN 比背定义更有说服力。

Q1:type 从 ref 变 range 意味着什么?

从等值查询变成范围查询,扫描范围变大、rows 变多,但仍在走索引;一般无需紧张,注意 range 后若出现 filesort/temporary 才需要治理。

Q2:Using index 和 Using index condition 能同时出现吗?

能。前者表示覆盖索引免回表,后者表示 ICP 下推过滤;它们针对不同的执行环节,可以同时出现在 Extra 里。

Q3:key_len 有什么用?

① 验证联合索引"用了几列";② 检查是否发生了隐式类型转换(key_len 异常变大);③ 定位 NULL 位和字符集开销。能口算出 key_len 是加分项。

易错点:Using index(覆盖)≠ Using index condition(ICP),背混了会被追问;rows 是估算值,不是精确行数。


5. 事务 ACID 与日志体系(undo / redo / WAL / 两阶段提交)

💡 定义(小白版):事务是把一组数据库操作打包成一个“要么全成、要么全不成”的整体:一起提交,或一起回滚,绝不允许做一半。 🏷️ 类比:事务=转账:扣你 100 和加他 100 必须同时成功;若中途断网,两边都要撤销,绝不能只扣不加。 💡 定义(小白版):事务日志是数据库的“记账本”,主要有三种:redo log=重做日志,记录“改了什么”,断电后重放恢复(持久性);undo log=撤销日志,记录“改之前长啥样”,回滚/翻旧版用(原子性 + MVCC);binlog=归档日志,记录完整 SQL/行变化,用来做主从复制和备份。 🏷️ 类比:redo 像“已发货清单”(丢了货照单补发);undo 像“原价签”(要退货就照原价签改回去);binlog 像“配送全程录像”(别的门店照录像复刻一份同款)。

5.1 ACID 定义

💡 定义(小白版):ACID 是事务的四个基本要求:原子性(Atomicity,要么全做要么全不做)、一致性(Consistency,做之前之后数据都要符合规则)、隔离性(Isolation,事务之间互不干扰)、持久性(Durability,一旦提交就永久生效,断电不丢)。 🏷️ 类比:ACID 四兄弟=一包跳伞装备:合上伞包(原子性)、系好每根绳扣(一致性)、各跳各的不互相扯(隔离性)、落地签完字才算完成、风雨不改(持久性)。

特性 定义 一句话例子
原子性 Atomicity 事务要么全成功,要么全回滚,不存在中间态 转账要么扣款+入账都成功,要么都失败
一致性 Consistency 事务前后,数据库完整性约束不被破坏 余额总和不变;外键/唯一约束恒成立
隔离性 Isolation 并发事务互不干扰,效果等价于串行 两个事务同时改同一行,结果确定
持久性 Durability 事务提交后,修改永久保存,不怕宕机 提交后断电,数据仍在

5.2 实现手段对照

ACID 特性 核心机制 说明
原子性 undo log(回滚日志) 记录"如何撤销",回滚时按 undo 逆序执行
一致性 undo log + 约束 + 应用逻辑 undo 支持回滚恢复一致;业务保证语义一致
隔离性 锁 + MVCC 锁防写写冲突,MVCC 提供快照读
持久性 redo log(重做日志)+ WAL 先写日志后落盘,崩溃后重放

原理讲解(undo log):执行 UPDATE 时,先把旧值写入 undo log(逻辑日志:记录"把该行改回旧值"),同时通过 roll_pointer 串成版本链(第 7 节 MVCC 依赖)。事务回滚时逆序执行 undo,即撤销所有已做修改,恢复原子性。undo 还有一个作用是供 MVCC 读旧版本,所以 undo 不会立即删除,需要 purge 线程清理。

原理讲解(redo log):InnoDB 对数据页的修改先记录 redo log(物理日志:记录"某个页的某个偏移改成什么值"),刷盘时机靠 WAL 策略控制。

5.3 redo log 的 WAL 原理与两阶段提交

💡 定义(小白版):redo log 是“重做日志”:记录事务对数据页做过哪些修改;WAL(Write-Ahead Logging)是“先写日志、后改数据”的策略——先把“我要改什么”记到日志里落盘,再真正去改数据页。 🏷️ 类比:WAL 像记账:先把每一笔账记到小本子上(日志),等有空了再誊到总账本(数据页)。断电时按小本子重誊即可,不会错账。

WAL(Write-Ahead Logging,预写日志): - 定义:数据落盘之前,先保证日志落盘;提交事务时只需 fsync redo log(顺序写,快),数据页可以脏页留在 buffer pool 稍后异步刷盘。 - 为什么快:redo log 是顺序追加写(append-only),磁盘顺序写远快于随机写;数据页是随机写。WAL 把"一次随机写 + 一次顺序写"变成"一次顺序写 + 后台异步随机写"。 - 崩溃恢复:宕机时内存中未落盘的数据页丢失,但 redo log 里有最新记录 → 重启时重放 redo log(REDO) 恢复数据;未提交事务的修改由 undo 回滚(UNDO 阶段)。

两阶段提交(Two-Phase Commit, 2PC,redo log 与 binlog 之间):

阶段一 PREPARE:事务执行完,先写 redo log(状态 prepare)并落盘
阶段二 COMMIT :写 binlog(归档/主从同步用)并落盘
               最后把 redo log 标记为 commit
崩溃恢复时的判定规则:
  - redo 有 prepare 且 binlog 完整  → 补上 commit(事务成立)
  - redo 有 prepare 但 binlog 缺失  → 回滚(事务不成立)

为什么需要 2PC:redo log 负责"本机崩溃恢复",binlog 负责"主从复制/备份"。如果没有两阶段提交,可能出现:redo 已 commit 而 binlog 没写(主库数据在,从库丢数据),或 binlog 已写而 redo 没 commit(从库比主库多数据)——两者不一致。2PC 保证要么两边都成立,要么都不成立。

Python 3 可运行示例(模拟 WAL + 两阶段提交的崩溃判定):

# wal_2pc.py —— 模拟 redo/binlog 两阶段提交与崩溃恢复判定
def recover(redo_state: str, binlog_ok: bool) -> str:
    """返回崩溃恢复决策:commit 或 rollback"""
    if redo_state == "prepare" and binlog_ok:
        return "commit(补齐 redo 的 commit 标记)"
    if redo_state == "prepare" and not binlog_ok:
        return "rollback(undo 回滚,事务不成立)"
    if redo_state == "commit":
        return "commit(已提交,直接重放)"
    return "rollback(尚未 prepare,直接丢弃)"

# 模拟三种崩溃点
print(recover("prepare", True))    # 崩溃发生在两阶段之间且 binlog 已落盘
print(recover("prepare", False))   # 崩溃发生在写 binlog 之前
print(recover("commit",  True))    # 崩溃发生在提交之后

5.4 面试追问

💡 答法提示:日志与提交类追问先分清楚“redo=本机恢复、binlog=主从复制”两个职责,再背“prepare → binlog → commit”三连流程;回答时把 undo 说成“撤销”、redo 说成“重做”就不会翻车。

Q1:为什么先写 redo log 而不是先改数据页?

数据页修改是随机写且可能在内存 buffer pool 中尚未落盘;redo log 是顺序写、体积小、可即时 fsync。WAL 保证"日志先持久化",即使数据页没刷盘,崩溃后重放日志即可恢复,把昂贵随机写推迟到后台批量执行。

Q2:binlog 和 redo log 的区别?

① 层级:redo 是 InnoDB 存储引擎层,binlog 是 MySQL Server 层;② 内容:redo 是物理日志(页修改),binlog 是逻辑日志(SQL/行映像);② 用途:redo 管崩溃恢复,binlog 管主从复制与归档;④ 写入:redo 循环写、空间固定,binlog 追加写、可一直累积。它们靠两阶段提交保持一致。

Q3:两阶段提交能省掉吗?

在"单机 + 无主从"的极简场景可简化,但生产环境有主从复制,redo 与 binlog 必须一致,否则崩溃恢复与从库数据会出现分叉。所以 2PC 是 InnoDB 标配。

Q4:undo log 什么时候清理?

undo 通过 roll_pointer 形成版本链,如果还有老事务/长查询在读取旧版本,旧 undo 必须保留;由后台 purge 线程在"无任何 ReadView 引用该版本"后回收。这也是"大事务/长事务拖垮 undo 膨胀"的根因。

易错点:① 别把 undo/redo 说反——undo 撤销、redo 重做;② 别把 binlog 说成 InnoDB 的日志,它是 Server 层;③ 两阶段提交的判定规则要背准确(prepare + binlog 完整 → commit)。


6. 隔离级别

6.1 定义与问题

💡 定义(小白版):脏读=读到别人还没提交、随时可能撤销的“半成品数据”;不可重复读=自己事务内两次读同一行,值却变了;幻读=自己事务内两次查询,行数变多/少了(凭空多出或消失几行)。 🏷️ 类比:脏读=看到别人草稿纸上的答案就抄,结果人家划掉了;不可重复读=上午问工资是 1 万,下午再问变成 2 万;幻读=数了一遍货架上有 10 瓶水,眨个眼变 12 瓶。

定义:SQL 标准定义四种隔离级别,解决三个并发读问题。

  • 脏读(Dirty Read):读到另一个事务未提交的修改。对方回滚后,读到的是"幽灵数据"。
  • 不可重复读(Non-Repeatable Read):同一事务内两次读同一行,结果不一致(因为别的事务已提交修改)。重点在"行数据被改"。
  • 幻读(Phantom Read):同一事务内两次执行同一查询,结果集行数发生变化(别的事务插入/删除了行)。重点在"结果集的行数/新行"。

6.2 四种隔离级别对照表

💡 定义(小白版):隔离级别是数据库用来“几成熟地隔离并发事务”的四档旋钮:读未提交=完全不设防;读已提交=提交了才让读;可重复读=我事务里的画面永远定格在第一次读;串行化=所有事务排队一个个来。 🏷️ 类比:四档像餐厅后厨的四个卫生等级:串行化最严格(一桌一厨一菜,最慢)、读未提交最宽松(先吃的可能吃到半生不熟的菜)。

隔离级别 脏读 不可重复读 幻读 实现要点
读未提交 Read Uncommitted ❌ 可能 ❌ 可能 ❌ 可能 不加读锁,直接读最新
读已提交 Read Committed(RC) ✅ 解决 ❌ 可能 ❌ 可能 每次 SELECT 生成新 ReadView
可重复读 Repeatable Read(RR) ✅ ✅ 解决 ⚠️ MySQL 用 next-key lock 解决 事务内复用首个 ReadView
可串行化 Serializable ✅ ✅ ✅ 解决 全部串行(锁冲突退化为排队)

表内"❌ 可能"表示存在该问题;MySQL 的 RR 比标准更严,通过 next-key lock 顺带解决了幻读,这是 MySQL 默认隔离级别设为 RR 的原因之一(标准里 RR 是不防幻读的)。

6.3 MySQL 默认 RR 如何用 next-key lock 解决幻读

原理讲解:当前读(SELECT ... FOR UPDATE / UPDATE / DELETE)时,InnoDB 对扫描到的记录加记录锁(record lock),同时对记录两侧的间隙加间隙锁(gap lock),二者合称 next-key lock(临键锁)。

索引键: 10  20  30  40
事务A: UPDATE t SET x=1 WHERE id BETWEEN 20 AND 30;   -- 加 next-key lock
      锁定区间: (10,20] (20,30] (30,40] 及两端间隙
事务B: INSERT INTO t VALUES (25);   -- ❌ 命中间隙 (20,30),被阻塞

效果:事务 A 第一次查询没有 25 这个键,事务 B 想插入 25 也被间隙锁挡住 → 第二次查询依然查不到 25 → 幻读被锁机制消灭。

Python 3 可运行示例(模拟间隙锁阻塞插入):

# gap_lock.py —— 用 set 模拟 next-key lock 阻止"幻读式插入"
keys = {10, 20, 30, 40}                       # 已存在的索引键

def next_key_lock(lo: int, hi: int) -> list:
    """返回被 next-key lock 覆盖的键值域(记录+间隙)"""
    locked = set()
    all_keys = sorted(keys | {lo, hi})
    for k in all_keys:
        if lo <= k <= hi:
            locked.add(k)
    # 间隙:任何落在 (20,30] 附近的插入值都算被锁
    return sorted(locked)

locked = next_key_lock(20, 30)
for v in (15, 20, 25, 30, 35):
    print(f"插入 {v}: {'被阻塞' if 20 <= v <= 30 else '允许'}")  # 25 被间隙锁挡住

6.4 面试追问

💡 答法提示:隔离级别追问核心是“读异常怎么被哪一级挡住”:按 脏读→不可重复读→幻读 逐级升级,记住 MySQL 的 RR 靠 next-key lock 顺带防幻读(比 SQL 标准更强)这一点是高分关键。

Q1:RR 解决了幻读,为什么还要 Serializable?

RR 的幻读防护只对当前读 + 走索引的场景生效;全表扫描、不走索引的插入场景仍可能"漏锁"。Serializable 用更重的锁把所有读写都串行化,牺牲并发换绝对正确,一般只在强一致 + 低并发场景使用。

Q2:RC 下为什么不可重复读但能避免脏读?

RC 每次 SELECT 都生成新 ReadView,只认已提交版本,所以读不到未提交的脏数据;但两次 SELECT 之间对方提交了修改,第二次 ReadView 就能看到新版本 → 不可重复读。

Q3:公司生产一般用哪个隔离级别?

MySQL 默认 RR;但互联网公司常手动改为 RC,因为:① RR 的间隙锁放大锁冲突、易死锁;② RC 只锁记录行,并发度更高;③ 业务层面"读到已提交最新值"通常更符合需求。这也是阿里面试经典题。

易错点:SQL 标准里 RR 不解决幻读,MySQL 用 next-key lock 才解决——这两句话分开说,面试官最烦把"MySQL 特性"当成"标准特性"。


7. MVCC 原理(多版本并发控制)

💡 定义(小白版):MVCC(多版本并发控制)=一行数据在内存里同时保留多个历史版本,读事务挑一个“合适的老版本”来看,写事务改最新版互不打架,从而读写并行不互斥。 🏷️ 类比:MVCC 像存档游戏:每个玩家(事务)可以读自己存档时刻的画面,别人怎么改都不影响你看到的旧画面,等你提交时再合并。

7.1 隐藏字段

💡 定义(小白版):每行数据除了业务字段,还藏着两个引擎维护的“隐形标签”:trx_id(这行最后一次是谁改的)和 roll_pointer(指向这行的旧版本)。 🏷️ 类比:像快递包裹上的“改件记录”:每个包裹贴着最近谁动过它(trx_id),背面还钉着“上一版签收单”的链接(roll_pointer),一路可以追溯到最初的样式。

定义:InnoDB 每行记录自带两个隐藏列: - trx_id:最近一次修改/创建这行的事务 id; - roll_pointer:指向 undo log 中该行的旧版本,串成版本链。

行数据(最新版)  ──roll_pointer──▶  undo log 版本1 ──▶  undo log 版本2 ──▶ ...
trx_id = 120                                   trx_id = 110

7.2 undo log 版本链

原理讲解:每次 UPDATE 不直接覆盖旧值,而是把旧值写入 undo log 并让新行通过 roll_pointer 指过去。这样一行数据在逻辑上存在多个版本,配合 ReadView 就能"读到一个一致的历史快照"——这就是 MVCC(Multi-Version Concurrency Control,多版本并发控制) 的数据基础。

7.3 ReadView 生成规则与可见性判断

定义:ReadView(读视图)是生成那一刻的"事务快照",包含四个字段:

字段 含义
m_ids 生成时刻仍处于活跃状态(未提交)的事务 id 列表
min_trx_id m_ids 中的最小值
max_trx_id 下一个待分配的事务 id(大于所有已存在的 id)
creator_trx_id 生成该 ReadView 的当前事务 id

可见性判断伪代码(判断某个版本 trx_id 是否可见):

func is_visible(trx_id, read_view):
    if trx_id == read_view.creator_trx_id:      # 自己改的,必可见
        return True
    if trx_id < read_view.min_trx_id:           # 提交早于快照开启,可见
        return True
    if trx_id >= read_view.max_trx_id:          # 快照之后才开始的事务,不可见
        return False
    if trx_id in read_view.m_ids:               # 快照时仍活跃未提交,不可见
        return False
    return True                                 # 在 [min,max) 且已提交,可见

判断逻辑一句话:从版本链头部(最新版)往后找,第一个可见的版本就是当前读到的数据。

Python 3 可运行示例:

# readview.py —— 完整模拟 ReadView 可见性判断
class ReadView:
    def __init__(self, m_ids, creator_trx_id, next_trx_id):
        self.m_ids = set(m_ids)
        self.min_trx_id = min(self.m_ids)
        self.max_trx_id = next_trx_id          # 下一个待分配 id
        self.creator_trx_id = creator_trx_id

    def is_visible(self, trx_id):
        if trx_id == self.creator_trx_id:
            return True
        if trx_id < self.min_trx_id:
            return True
        if trx_id >= self.max_trx_id:
            return False
        return trx_id not in self.m_ids

# 场景:事务 5 开启快照时,事务 3、4 仍在活跃,事务 1、2 已提交
rv = ReadView(m_ids=[3, 4], creator_trx_id=5, next_trx_id=6)
for tid in (1, 2, 3, 4, 5, 6):
    print(f"trx_id={tid} 可见? {rv.is_visible(tid)}")
# 输出:1,2 可见(已提交且早于快照);3,4 不可见(活跃);5 可见(自己);6 不可见(晚于快照)

7.4 快照读 vs 当前读

💡 定义(小白版):快照读=普通 SELECT,不加锁、靠 MVCC 读一个“历史快照版本”;当前读=SELECT FOR UPDATE / UPDATE / DELETE,必须加锁读最新数据。 🏷️ 类比:快照读=看监控回放(看的是录好的历史画面,不打扰现场);当前读=直接进考场核对试卷(读最新状态,还要占住座位不许别人改)。

类型 语句 是否加锁 读哪个版本
快照读 普通 SELECT 不加锁(MVCC) 读 ReadView 决定的可见版本,不阻塞写
当前读 SELECT ... FOR UPDATE、LOCK IN SHARE MODE、UPDATE、DELETE 加锁 读最新已提交版本,并加锁防止并发修改

RR 与 RC 的 ReadView 差异(高频考点): - RC:每次 SELECT 都生成新 ReadView → 可能读到别的事务新提交的数据 → 不可重复读; - RR:事务内第一次 SELECT 生成 ReadView,之后复用 → 整个事务内读到的版本链起点不变 → 可重复读。

7.5 面试追问

💡 答法提示:MVCC 追问抓住“四字段 ReadView + 三规则可见性”讲透一次,再对比 RC/RR 的 ReadView 生成时机(每 SELECT 一次 vs 首次生成复用)——这一句差异足以串起整章。

Q1:MVCC 解决的是"读写冲突"还是"写写冲突"?

只解决读写并发(读不阻塞写、写不阻塞读)。写写冲突仍由行锁解决——两个事务同时 UPDATE 同一行,后到者要等锁。

Q2:RR 下"先快照读后 UPDATE"为什么还能锁到别人已提交的数据?

UPDATE 是当前读,不走 ReadView,而是读最新版本并加锁,所以可能覆盖快照读的"旧认识"——这就是 MVCC + 锁混合模型里著名的"先快照读后当前读的不一致性",面试加分点。

Q3:为什么 RC 每 SELECT 一次 ReadView 就慢?

不慢,ReadView 只是内存里的一个小结构(4 个字段),生成成本 O(活跃事务数);复用它的收益是"隔离语义",性能影响可忽略。

易错点:可见性判断别背反——"trx_id 在 m_ids 中 = 不可见"(活跃未提交),"trx_id < min_trx_id = 可见"(已早提交)。以及 max_trx_id 是"下一个要分配的值",不是"已存在的最大 id"。


8. 锁机制

8.1 锁的粒度与类型

💡 定义(小白版):共享锁 S=“只读锁”,大家都能同时读、但不能写;排他锁 X=“独占锁”,我拿了别人既不能读也不能写;行锁锁住一行、表锁锁住整张表、间隙锁锁住行与行之间的“空位”、意向锁是“我准备锁(子)行”的表级声明。 🏷️ 类比:S/X 像图书馆座位:共享读=多人同读一本样书;排他写=借走这本书别人不能碰。行锁=只锁你坐的位子;表锁=整间自习室锁门;间隙锁=座位之间“不准加塞”的空位封条;意向锁=进门先举手说“我要占位子”,免得别人问每把椅子。

按模式分: - 共享锁 S(Shared Lock):读锁,SELECT ... LOCK IN SHARE MODE;多个事务可同时持 S; - 排他锁 X(Exclusive Lock):写锁,SELECT ... FOR UPDATE / UPDATE / DELETE;S 与 X、X 与 X 均互斥。

兼容矩阵:

S X
S ✅ 兼容 ❌ 互斥
X ❌ 互斥 ❌ 互斥

按粒度分:

锁 作用范围 特点
表锁 整张表 开销小、并发差(MyISAM 仅表锁)
行锁 单行记录 开销大、并发好(InnoDB 才有)
间隙锁 GAP 索引记录之间的空隙 防插入,防幻读
next-key lock 记录锁 + 两侧间隙 RR 隔离级别默认(8.3 节)

8.2 乐观锁 vs 悲观锁

💡 定义(小白版):悲观锁=先假定一定会有人抢,直接上锁堵住别人再说;乐观锁=先假定没事,更新时检查一下版本号有没有被改过,被改就重来。CAS(Compare And Swap)就是乐观锁的经典实现:先比后换,一致才更新。 🏷️ 类比:悲观锁=进会议室先反锁门,谁想进都得等;乐观锁=不锁门,出门前看一眼白板上是否有人改过内容,被改过就重写一份。

维度 乐观锁 悲观锁
思路 假定冲突少,提交时校验 假定冲突多,操作前加锁
实现 版本号 / CAS(Compare And Swap) SELECT ... FOR UPDATE、数据库行锁
冲突成本 冲突时重试 冲突时阻塞等待
适用 读多写少、冲突概率低 写多、冲突概率高、强一致

版本号乐观锁伪代码:

UPDATE goods
   SET stock = stock - 1, version = version + 1
 WHERE id = 100 AND version = 3        -- 版本号对上才更新
-- 受影响行数 = 0  → 说明版本已被别人改过,重试

Python 3 可运行示例(乐观锁 CAS 重试):

# optimistic_lock.py —— 模拟版本号乐观锁与重试
class Goods:
    def __init__(self, stock, version=0):
        self.stock, self.version = stock, version

def try_buy(goods: Goods, n: int, expect_version: int) -> bool:
    """CAS:只有版本号匹配才扣库存并自增版本"""
    if goods.version == expect_version:
        if goods.stock >= n:
            goods.stock -= n
            goods.version += 1
            return True
    return False

g = Goods(stock=5, version=3)
for i in range(3):
    ok = try_buy(g, 2, expect_version=3)     # 3 个线程并发都拿 version=3
    print(f"第{i+1}个请求 CAS 结果: {ok}")    # 只有第一个成功,其余返回 False 需重试
print(f"最终库存: {g.stock}, 版本: {g.version}")

8.3 死锁:原因、必要条件与解决

💡 定义(小白版):死锁=两个事务互相等对方手里的锁,谁都不肯放手,于是永远卡死;破解靠“超时回滚”或“死锁检测后杀掉一个事务”。 🏷️ 类比:死锁=窄桥上两车对向堵死:A 等你退、B 等你退,谁都不退就永远堵着——必须交警(数据库)强制让一辆车倒回去。

定义:两个以上事务互相持有对方需要的锁,且都在等待对方释放,形成循环等待,事务无法推进。

死锁四个必要条件(缺一不可): 1. 互斥:资源一次只能被一个事务占用; 2. 请求与保持:已持有资源,又去请求他人资源; 3. 不可剥夺:已获得的资源不能强行抢走; 4. 循环等待:A 等 B、B 等 A 的环路。

MySQL 的处理: - 死锁检测:InnoDB 通过 wait-for graph 检测环路,主动回滚代价较小的事务(抛出 Deadlock found when trying to get lock),不回滚双方——所以 MySQL 里死锁"不会永久卡死"; - 死锁超时:innodb_lock_wait_timeout(默认 50s)兜底。

预防与治理: - 固定加锁顺序:所有事务按相同顺序访问表/行(如先锁小 id 再锁大 id); - 缩小事务与锁范围:事务里别做耗时操作,索引要能精确定位,少锁行; - 用等值条件少用范围:减少间隙锁参与,降低冲突面; - 表拆分/隔离不同热点行。

Python 3 可运行示例(模拟死锁并展示"统一加锁顺序"可避免):

# deadlock.py —— 两个线程交叉加锁:演示死锁 + 用超时避免卡死
import threading, time

lock_a, lock_b = threading.Lock(), threading.Lock()

def worker(name, first, second, use_timeout=False):
    got_first = first.acquire(timeout=1)
    if not got_first:
        print(f"{name}: 第一步就超时"); return
    print(f"{name}: 拿到 {first}")
    time.sleep(0.05)                        # 制造交叉等待窗口
    got_second = second.acquire(timeout=1)
    if got_second:
        print(f"{name}: 拿到 {second}, 执行完成")
        second.release()
    else:
        print(f"{name}: 等 {second} 超时 → 释放已持锁, 主动规避死锁")
    first.release()

t1 = threading.Thread(target=worker, args=("T1", lock_a, lock_b))
t2 = threading.Thread(target=worker, args=("T2", lock_b, lock_a))
t1.start(); t2.start(); t1.join(); t2.join()
# 输出会看到一方超时主动回退——这就是"可剥夺/超时"打破死锁的思想

8.4 面试追问

💡 答法提示:锁与死锁追问用“S/X 兼容矩阵 + 粒度+模式两个维度”打底,死锁必答“四个必要条件 + 超时/检测回滚”两个解法;能补充“固定加锁顺序、缩短事务”这两个工程手段即算完整。

Q1:间隙锁什么时候会锁住"不存在的行"?

RR 级别下,UPDATE/DELETE/FOR UPDATE 扫描到索引区间时,除了给存在的记录加记录锁,还给记录间的空隙加 gap lock,阻止其他事务往空隙插入新行——新行在锁期间"不存在但也进不来",以此防幻读。

Q2:如何判断一条 SQL 是否产生死锁?怎么避免?

看 SHOW ENGINE INNODB STATUS 的 LATEST DETECTED DEADLOCK 段(涉及哪两个事务、哪条 SQL);避免:固定加锁顺序、缩短事务、降低 RR 间隙锁(如改 RC)。

Q3:乐观锁一定比悲观锁好吗?

不一定。乐观锁在冲突率高时重试成本爆炸(每次 CAS 失败都浪费一轮 IO);悲观锁在低冲突时反而增加锁等待开销。选型依据:冲突概率 × 重试成本 vs 锁等待成本。

易错点:死锁 ≠ 阻塞超时。MySQL 死锁由检测器主动回滚一方快速暴露;而"锁等待超时"是单方长时间等锁。别把两者混为一谈。


9. 慢查询优化流程

💡 定义(小白版):慢查询=执行时间超过阈值的 SQL。数据库会把它们记进“慢查询日志”,日志里每一条都是待优化的“嫌疑犯”。 🏷️ 类比:慢查询日志=医院的长队叫号机:超过 1 分钟还没叫完的号(SQL)单独记下来,医生(DBA)专门看这些“超时病历”。

9.1 开启慢查询日志

-- 全局开启慢日志,记录执行超过 1 秒的 SQL
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;          -- 阈值(秒),生产一般 0.1~1
SET GLOBAL log_queries_not_using_indexes = ON;   -- 记录没走索引的 SQL(可选)
-- 查看
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

注意:long_query_time 只对新会话生效,线上改完后要重新连接。

9.2 标准优化流程(背诵版口诀)

1) 定位:慢日志找出最慢/最高频的 SQL
2) 分析:EXPLAIN 看 type/key/key_len/rows/Extra
3) 索引:加/改索引,消除 filesort、temporary、全表扫描
4) 改写:优化 SQL 写法(见 9.3/9.4)
5) 架构:缓存(Redis) / 读写分离 / 分库分表(万不得已)
6) 验证:复测耗时与执行计划,确认收益

9.3 SQL 改写技巧

① 避免 SELECT *: - SELECT * 会让二级索引无法覆盖 → 无谓回表; - 表结构变更(加列)会让 * 结果变化,不便于缓存/兼容; - 应只查需要的列,尽量让查询命中覆盖索引。

② 深翻页优化(重点):LIMIT 100000, 10 需要扫描并丢弃前 10 万行,越翻越慢。

方法一:延迟关联(deferred join)——先只查主键,再 join 回表:

-- 慢:LIMIT 100000,10,扫描 100010 行
SELECT * FROM t ORDER BY id LIMIT 100000, 10;

-- 快:先在覆盖索引上快速定位主键,再回表取 10 行
SELECT a.* FROM t a
JOIN (SELECT id FROM t ORDER BY id LIMIT 100000, 10) b
  ON a.id = b.id;

方法二:游标 / 键集分页(keyset pagination)——记住上一页最后一条的 id,增量翻页:

-- 首页
SELECT * FROM t WHERE id > 0 ORDER BY id LIMIT 10;
-- 第二页:记住 last_id=10
SELECT * FROM t WHERE id > 10 ORDER BY id LIMIT 10;

复杂度对比(伪代码):

offset 分页成本 = O(offset + limit)      —— 随翻页线性增长
游标分页成本   = O(limit)                 —— 恒定,推荐

Python 3 可运行示例:

# deep_paging.py —— 对比 offset 分页与游标分页的扫描量
rows = list(range(1_000_000))

def offset_page(rows, page, size):
    start = page * size
    return rows[start:start + size], start + size      # 扫描 start 行

def cursor_page(rows, last_id, size):
    # 从 last_id 起顺序取 size 个(数据按 id 有序时)
    idx = rows.index(last_id) if last_id else -1
    return rows[idx + 1: idx + 1 + size], rows[idx + 1] if rows else None

off, scanned = offset_page(rows, 100000, 10)   # 第 10 万页 → 扫描百万行
cur, last = cursor_page(rows, 0, 10)           # 恒定扫描 10 行
print(f"offset 分页需扫描约 {scanned:,} 行; 游标分页只扫描 {len(cur)} 行")

9.4 分库分表与主从复制(概念层面)

分库分表:

方案 做法 适用
垂直分库 按业务拆库(用户库/订单库/商品库) 业务模块解耦、单库瓶颈
垂直分表 大表拆宽表(字段拆分) 行宽、冷热字段分离
水平分表 按分片键拆多张同构表(user_0..user_15) 单表数据量过亿

水平分片规则:取模(id % 16)、范围(按时间/ID 区间)、一致性哈希(扩容少迁移)。注意跨片 join 与分布式事务代价。

主从复制 + 读写分离: - 原理:主库写 binlog → 从库 IO 线程拉取写入中继日志 relay log → SQL 线程重放; - 读写分离:写走主库,读走从库(一主多从),读写分离中间件(MyCat/ShardingSphere)或应用层路由; - 一致性风险:主从延迟导致"刚写入读不到";强一致场景须走主库。

9.5 面试追问

💡 答法提示:慢查询优化题按“定位(慢日志)→ 诊断(EXPLAIN)→ 改写(索引/语句)→ 架构(读写分离/分库分表)”四步走,先讲清每一步“看什么、解决什么”,比背一长串技巧更显思路。

Q1:延迟关联为什么快?

子查询 SELECT id FROM t ORDER BY id LIMIT 100000,10 可以只走二级索引(覆盖,无需回表),在索引树上跳过 10 万行很便宜;外层再用主键 join 只回表 10 行。总成本 ≈ 索引扫描 + 10 次回表,而不是 10 万次回表。

Q2:分库分表什么时候做?怎么做选型?

单表超过千万~亿行、单库连接数/IO 成为瓶颈、且优化索引无法解决时考虑。先垂直拆分(成本低),再考虑水平拆分;分片键选择必须匹配主要查询模式,避免"无分片键的全局扫"。

Q3:主从延迟怎么治?

① 延迟敏感查询走主库或 Redis 缓存;② 半同步复制(主库等至少一个从库 ack);③ 并行复制/组复制;④ 监控 seconds_behind_master。

易错点:分页优化被问到别只答"limit 改大",要答出延迟关联和游标两种方案及各自成本;SELECT * 的坑是破坏覆盖索引,不是"多查几列"这么简单。


10. 常见题合集

10.1 自增主键 vs UUID 主键

💡 定义(小白版):自增主键=数据库自动发号、1、2、3…递增的整型主键;UUID 主键=一串随机生成的全局唯一字符串。自增写起来顺、UUID 天生不重复但乱序。 🏷️ 类比:自增主键=电影院按排队顺序发号,座位永远从最后一排往后坐(顺序写,不折腾);UUID=每人发个全球唯一身份证号,但进场是随机乱坐(容易来回换座位=页分裂)。

维度 自增主键(bigint AUTO_INCREMENT) UUID 主键
存储大小 8 字节(bigint) 16 字节(含连字符 36 字符)
顺序性 顺序递增 随机无序
页分裂 基本不分裂(追加写入) 频繁页分裂(随机插入中间位置)
二级索引 更小更高效(叶子存 8B 主键) 更大,索引膨胀
分布式 需解决冲突(雪花/号段/集中分配) 天然唯一,无需协调
隐私 暴露业务量(订单号可猜) 不暴露

结论:单机/集群可控场景用自增 bigint;分布式、无中心协调、需隐藏业务量的场景用 UUID / 雪花 ID(Snowflake)。雪花 ID 是折中:64 位、趋势递增、无中心依赖。

10.2 为什么要用 bigint 自增而不是 int

原理讲解: - INT 有符号最大 2,147,483,647(约 21 亿),BIGINT 最大约 9.2 × 10¹⁸; - 主键自增是长期只增不减的,互联网表随业务增长很容易触及 int 上限;一旦触顶,主键不能复用,改列类型代价极大(锁表重建); - 8 字节相对 4 字节的存储与索引开销差异很小,上限收益 >> 存储成本,所以新表一律 bigint。

10.3 一条 UPDATE 的执行流程(重点背诵)

1. 连接器    :校验权限、复用/新建连接
2. 分析器    :词法/语法分析,生成语法树
3. 优化器    :选择执行计划(走哪个索引、join 顺序)
4. 执行器    :调用 InnoDB 接口执行
5. InnoDB 内部(以 UPDATE t SET x=1 WHERE id=5 为例):
   a. 从 buffer pool 读 id=5 的页(不在则从磁盘载入)
   b. 给该行加 X 锁(当前读最新版)
   c. 把旧值写入 undo log(roll_pointer 挂到版本链)
   d. 更新内存页为新值 x=1,标记脏页
   e. 写 redo log(prepare 状态)→ 写 binlog → redo 标 commit(两阶段提交)
   f. 返回影响行数给客户端;脏页由后台异步刷盘

追问点:为什么 a 步要先"读到内存"?因为 InnoDB 所有修改都在 buffer pool 内存页上进行,磁盘只负责页面载入与异步刷盘——这也是 WAL 策略的根基。

10.4 count(*) vs count(1) vs count(字段)

写法 语义 性能
count(*) 统计行数,不取具体列值 InnoDB 中已优化为遍历最小二级索引/主键计数,最快(推荐)
count(1) 每行记一个常量 1,统计行数 与 count(*) 几乎等价,无实质差异
count(字段) 统计该字段非 NULL 的行数 需判断字段是否为 NULL,比前两者慢;且字段无索引时只能扫主键

易错点:很多人以为 count(1) 比 count(*) 快——在 InnoDB(5.7+)没有差别,都走最小索引树扫描;count(字段) 反而最慢(要判 NULL)。MyISAM 用表内计数器 O(1),InnoDB 无计数器需实时扫描。

10.5 面试追问

💡 答法提示:常识题合集(自增 vs UUID、UPDATE 执行流程、count 区别)建议先答“是什么”再答“为什么选它”;count 题记住三句口诀“count() 与 count(1) 等价、count(列) 跳过 NULL、count() 最快”即可覆盖。

Q1:自增主键在 RR 下会不会有"空洞"?

会。AUTO_INCREMENT 只保证递增不保证连续:事务回滚、INSERT ... ON DUPLICATE KEY、innodb_autoinc_lock_mode 不同模式都会产生跳号。所以主键的语义是"唯一标识",不是"连续编号"。

Q2:为什么说 UUID 主键会导致页分裂?

B+ 树叶子按主键排序,UUID 随机插入落在已有页中间,页空间不够就拆页(页分裂)、产生碎片、增加 IO;自增主键永远追加到末尾页,几乎不分裂。

Q3:count 大表的优化手段?

① 走最小二级索引(比主键树更小);② 缓存计数(Redis 维护,注意一致性);③ 用近似值(SHOW TABLE STATUS 的 rows 是估算);④ 分区/汇总表定时统计;⑤ 业务上避免 SELECT count(*) FROM 大表 的实时统计。


附录:速查口诀

索引选型   :B+ 树矮宽有序,哈希只吃等值查
聚簇非聚簇 :主键叶存全行,二级叶存主键
失效清单   :函数/转换、%前、OR、否定、断前缀
EXPLAIN    :type 排行 const>ref>range>index>ALL,Extra 认 Using index
ACID       :原子 undo,持久 redo,隔离锁+MVCC
两阶段     :redo prepare → binlog → redo commit
隔离级别   :脏读/不可重复读/幻读,RR 靠 next-key 锁杀幻读
MVCC       :版本链 + ReadView 四字段,快照读 vs 当前读
死锁       :互斥+保持+不可剥夺+循环等待;固定加锁顺序可解
慢查询     :慢日志 → explain → 索引 → 改写(延迟关联/游标)

本文档所有 Python 示例均为标准库实现,可直接 python xxx.py 运行。


SQL 语言速查(配合 04-数据库.ipynb §23-§35 实战)

五类 SQL 与 SELECT 骨架

DQL: SELECT  |  DML: INSERT/UPDATE/DELETE  |  DDL: CREATE/ALTER/DROP
DCL: GRANT/REVOKE  |  TCL: BEGIN/COMMIT/ROLLBACK/SAVEPOINT
SELECT 列, 聚合(列)              -- ⑤ 投影/聚合
FROM 表                         -- ① 数据源
[JOIN 表 ON 条件]               -- ② 连接
[WHERE 行过滤]                  -- ③ 先于分组, 不能用聚合
[GROUP BY 分组列]               -- ④ 分组
[HAVING 组过滤]                 -- ⑥ 分组后, 能用聚合
[ORDER BY 排序列]               -- ⑦ 排序
[LIMIT n]                       -- ⑧ 分页/取前 n

执行顺序口诀:FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT 三条铁律:WHERE 不能用聚合;能放 WHERE 就放 WHERE;HAVING 引用的列必须在分组或聚合中。

JOIN 全家桶

类型 保留 场景
INNER 两边都匹配 只要有效关联
LEFT 左表全行, 右无匹配补 NULL 主表 + 可选附属
RIGHT 右表全行 与 LEFT 对称
FULL OUTER 两边全行 全量对账
CROSS 笛卡尔积 排列组合/枚举

坑① LEFT JOIN 时右表条件放 WHERE 会退化为 INNER(应放 ON);坑② 右表一对多 → 结果行膨胀。

聚合与窗口函数

  • 聚合:COUNT(*)数行 / COUNT(列)非 NULL / SUM / AVG / MAX / MIN;NULL 自动跳过
  • GROUP BY 陷阱:SELECT 非分组列报错;COUNT(可空列)可能少;先 WHERE 后分组
  • 窗口函数:不合并行、明细与聚合同见;ROW_NUMBER/RANK/DENSE_RANK(并列占位差异)、LAG/LEAD 环比、SUM OVER 累计
  • 三种排名:1,2,3,3,5 → ROW_NUMBER 1,2,3,4,5;RANK 1,2,3,3,5;DENSE_RANK 1,2,3,3,4

行转列(CASE WHEN 模板)

SELECT user_id,
       MAX(CASE WHEN subject='数学' THEN score END) AS math,
       MAX(CASE WHEN subject='语文' THEN score END) AS chinese
FROM scores GROUP BY user_id;

条件聚合:COUNT(CASE WHEN status='paid' THEN 1 END)(NULL 不计特性)。

事务四命令与坑

BEGIN / COMMIT / ROLLBACK / SAVEPOINT(部分回滚)
坑① MySQL autocommit=1 需显式 BEGIN;坑② DDL 隐式提交不可回滚;坑③ 长事务锁久、undo 膨胀、拖慢主从 → 拆小事务。

面试高频题模板

-- 连续登录 N 天: 日期 - 行号 = 恒定值 → 同组即连续
SELECT user_id FROM (
  SELECT user_id, login_date,
         DATE_SUB(login_date, INTERVAL ROW_NUMBER()
                  OVER (PARTITION BY user_id ORDER BY login_date) DAY) grp
  FROM login_log
) t GROUP BY user_id, grp HAVING COUNT(*) >= N;

-- 每组 TopN
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) rn FROM emp
) t WHERE rn <= 3;

-- 次日留存
SELECT COUNT(DISTINCT b.user_id) / COUNT(DISTINCT a.user_id)
FROM (SELECT user_id FROM login WHERE dt='2024-01-01') a
LEFT JOIN (SELECT user_id FROM login WHERE dt='2024-01-02') b USING (user_id);

SQL 注入防护(必考)

第一防线: 参数化预编译(? 占位符, 输入只当数据)   ← 首选
第二防线: ORM 参数绑定(SQLAlchemy / MyBatis #{}; ${} 是拼接=危险)
第三防线: 输入白名单校验 + 最小权限 DB 账户

高频追问速答

  1. WHERE vs HAVING:前者分组前行过滤、无聚合;后者分组后组过滤、可用聚合
  2. COUNT(*) = COUNT(1) 数行;COUNT(列) 跳过 NULL
  3. LEFT JOIN 变多:右表一对多 → 查右表 key 唯一性
  4. IN vs EXISTS:现代优化器常等价改写;NOT IN + NULL 是经典坑
  5. 窗口 vs GROUP BY:窗口不合并行
  6. union 去重 / union all 全保留更快
  7. 主键非空每表一个;唯一索引可多个、可 NULL
  8. 慢 SQL:慢日志 → EXPLAIN(type/key/rows) → 加索引/覆盖索引/改写

教程来源:Akun-python/autumn-recruitment。 MIT 许可证全文。