04 · MySQL 数据库八股文速成手册¶
面向秋招算法/后端岗位的数据库面试突击手册。 覆盖:索引原理与失效、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 可运行示例(扇出与容量估算):
核心概念¶
💡 定义(小白版):数据库就是帮我们存数据、还能又快又准查数据的软件仓库;关系型数据库把数据存进一张张表格(行=一条记录,列=一个属性),表与表之间还能用共同字段“关联”起来,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 注入必须用参数化查询(? 占位符)防护。
配图来源:手绘 SVG(images/db_bplus.svg)
# ---- matplotlib 中文字体(防图内中文乱码)----
import matplotlib
matplotlib.rcParams['font.sans-serif'] = ['Microsoft YaHei', 'SimHei', 'Songti SC', 'Arial Unicode MS', 'DejaVu Sans']
matplotlib.rcParams['axes.unicode_minus'] = False
# -------------------------------------------
# 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} 次")
非叶子页扇出: 1170 2 层 B+ 树可容纳约 18,720 行,即 IO 次数约 2 次 3 层 B+ 树可容纳约 21,902,400 行,即 IO 次数约 3 次 4 层 B+ 树可容纳约 25,625,808,000 行,即 IO 次数约 4 次
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)],发生了回表
['age=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")
索引命中耗时: 75.0us 函数过滤全扫耗时: 11.5ms
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) 也参与进来了
varchar(20) nullable: 83 varchar(20) not null: 82
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 像记账:先把每一笔账记到小本子上(日志),等有空了再誊到总账本(数据页)。断电时按小本子重誊即可,不会错账。
💡 定义(小白版):两阶段提交(2PC)=让“redo log(本机恢复)和 binlog(主从复制)两份日志”先商量好再一起定稿:先各自准备(prepare),确认都没问题后一起盖章(commit),避免一份写了另一份没写导致主库从库数据对不上。 🏷️ 类比:2PC 像婚礼流程:先“订婚”(prepare,双方家长都点头),再“领证”(commit,两本证同时盖好章)——不会出现只有一方家长知道、证却已领的乌龙。
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)) # 崩溃发生在提交之后
commit(补齐 redo 的 commit 标记) rollback(undo 回滚,事务不成立) commit(已提交,直接重放)
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 被间隙锁挡住
插入 15: 允许 插入 20: 被阻塞 插入 25: 被阻塞 插入 30: 被阻塞 插入 35: 允许
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 可运行示例:
配图来源:手绘 SVG(images/db_mvcc.svg)
# 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 不可见(晚于快照)
trx_id=1 可见? True trx_id=2 可见? True trx_id=3 可见? False trx_id=4 可见? False trx_id=5 可见? True trx_id=6 可见? False
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}")
第1个请求 CAS 结果: True 第2个请求 CAS 结果: False 第3个请求 CAS 结果: False 最终库存: 3, 版本: 4
8.3 死锁:原因、必要条件与解决¶
💡 定义(小白版):死锁=两个事务互相等对方手里的锁,谁都不肯放手,于是永远卡死;破解靠“超时回滚”或“死锁检测后杀掉一个事务”。 🏷️ 类比:死锁=窄桥上两车对向堵死:A 等你退、B 等你退,谁都不退就永远堵着——必须交警(数据库)强制让一辆车倒回去。
定义:两个以上事务互相持有对方需要的锁,且都在等待对方释放,形成循环等待,事务无法推进。
死锁四个必要条件(缺一不可):
- 互斥:资源一次只能被一个事务占用;
- 请求与保持:已持有资源,又去请求他人资源;
- 不可剥夺:已获得的资源不能强行抢走;
- 循环等待: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()
# 输出会看到一方超时主动回退——这就是"可剥夺/超时"打破死锁的思想
T1: 拿到 <locked _thread.lock object at 0x000001CFBE531110> T2: 拿到 <locked _thread.lock object at 0x000001CFBE530E10>
T2: 等 <locked _thread.lock object at 0x000001CFBE531110> 超时 → 释放已持锁, 主动规避死锁T1: 等 <locked _thread.lock object at 0x000001CFBE530E10> 超时 → 释放已持锁, 主动规避死锁
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)} 行")
offset 分页需扫描约 1,000,010 行; 游标分页只扫描 10 行
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 打包成一个可反复调用的“函数”;触发器=在某表增/删/改前后“自动触发执行”的一段 SQL。 🏷️ 类比:视图=你收藏的一个“筛选好的购物清单”(不当真表存,随点随生成);存储过程=教员工“标准开票流程”,喊一个口令(过程名)就整套执行;触发器=超市门口感应门,一有人进出(增删改)就自动开关门(跑一段代码)。
# icp.py —— 索引下推(ICP)示意:先在索引内过滤再回表 vs 先回表再过滤
import random
random.seed(1)
rows = [(random.randint(18, 90), random.choice(['北京', '上海', '杭州', '广州'])) for _ in range(300)]
def no_icp(query_age, query_city):
"""无 ICP: 二级索引只筛 age, 大量回表后再过滤 city"""
idx_hits = [r for r in rows if r[0] > query_age]
return sum(1 for r in idx_hits if r[1] == query_city), len(idx_hits)
def with_icp(query_age, query_city):
"""有 ICP: 索引内同时过滤 city, 回表次数显著减少"""
idx_hits = [r for r in rows if r[0] > query_age and r[1] == query_city]
return len(idx_hits), len(idx_hits)
n1, m1 = no_icp(60, '杭州')
n2, m2 = with_icp(60, '杭州')
print(f'无 ICP: 回表 {m1} 次, 命中 {n1} 行')
print(f'有 ICP: 回表 {m2} 次, 命中 {n2} 行')
print('→ MySQL 5.6+ 默认启用 ICP: WHERE 里的其他列(非索引列)也能在索引遍历时过滤')
无 ICP: 回表 124 次, 命中 30 行 有 ICP: 回表 30 次, 命中 30 行 → MySQL 5.6+ 默认启用 ICP: WHERE 里的其他列(非索引列)也能在索引遍历时过滤
11. 索引失效 10 场景清单(🔴 背诵清单)¶
- 对索引列做函数/运算:
WHERE YEAR(create_time)=2024→ 函数破坏有序性 - 隐式类型转换:varchar 列查
WHERE name = 123→ 隐式 CAST 失效 - 前导模糊:
LIKE '%abc'(后缀匹配走不了;中缀更不行) - OR 连接非索引列:
WHERE a=1 OR b=2(a 有索引 b 无索引 → 全扫) - 违反最左前缀:复合索引 (a,b,c) 却直接查 b 或 c
- 负向条件:
!=、NOT IN、NOT LIKE通常走不了索引 - 范围查询之后的条件:复合索引 (a,b) 中
a>10 AND b=1→ b 用不上(范围列右失效) - NULL 判断:
IS NULL部分情况失效(可建哨兵值规避) - 排序与分组不匹配:
ORDER BY用了索引不含的列 → filesort - 数据量太少 / 区分度太低:优化器觉得全表扫更快
12. 事务 ACID 与实现原理(🔴 必背)¶
| 特性 | 含义 | 靠什么实现 |
|---|---|---|
| A 原子性 | 全做或全不做 | undo log:回滚时反向执行 |
| C 一致性 | 约束始终满足 | 业务 + 原子/隔离/持久共同保证 |
| I 隔离性 | 事务互不干扰 | 锁 + MVCC(undo 版本链 + ReadView) |
| D 持久性 | 提交后不丢 | redo log(先写日志后写库, WAL) |
面试点:redo 和 undo 的分工¶
- redo log(物理日志,记录"页改成了什么"):崩溃恢复用,保证 D
- undo log(逻辑日志,记录"改之前的样子"):事务回滚 + MVCC 版本链,保证 A 和 I
- WAL(Write-Ahead Logging):先写 redo log 再刷数据页 → 刷盘可批量、异步(group commit)
# isolation_matrix.py —— 三类读异常 × 四个隔离级别矩阵
levels = ['读未提交', '读已提交', '可重复读', '序列化']
abnormal = {
'脏读': [True, False, False, False],
'不可重复读': [True, True, False, False],
'幻读': [True, True, True, False],
}
print(f"{'异常':<8}" + ''.join(f'{l:>9}' for l in levels))
for name, flags in abnormal.items():
print(f'{name:<8}' + ''.join(f'{"⚠️出现" if f else "✔不出现":>9}' for f in flags))
print('\n注: InnoDB 默认 RR 用 next-key 锁额外挡住幻读 → 读并发性实际≈序列化')
print('(MySQL 的 RR 与标准 SQL 的 RR 定义不同, 这是最重要的易错点)')
异常 读未提交 读已提交 可重复读 序列化 脏读 ⚠️出现 ✔不出现 ✔不出现 ✔不出现 不可重复读 ⚠️出现 ⚠️出现 ✔不出现 ✔不出现 幻读 ⚠️出现 ⚠️出现 ⚠️出现 ✔不出现 注: InnoDB 默认 RR 用 next-key 锁额外挡住幻读 → 读并发性实际≈序列化 (MySQL 的 RR 与标准 SQL 的 RR 定义不同, 这是最重要的易错点)
13. MVCC 核心背诵:undo 版本链 + ReadView(🔴 必背)¶
ReadView 四个字段¶
m_ids : 生成时刻活跃(未提交)事务 id 列表
min_trx_id : m_ids 最小值
max_trx_id : 尚未分配的最大事务 id(m_ids 最大值 + 1)
creator_trx_id: 当前事务自己的 id
可见性规则(被访问版本的事务 id = trx_id)¶
trx_id == creator_trx_id→ 自己改的,可见trx_id < min_trx_id→ 早于所有活跃事务,已提交,可见trx_id >= max_trx_id→ 未来事务,不可见min_trx_id <= trx_id < max_trx_id 且 trx_id ∈ m_ids→ 活跃未提交,不可见;否则可见- 不可见则沿 undo 版本链向前找下一个版本,重复判断
RC 与 RR 的区别:RC 每次 SELECT 都生成新 ReadView;RR 只有第一次 SELECT 生成并复用 → 这就是"可重复读"的来源。
14. 快照读 vs 当前读(一致性读,🔴 高频辨析)¶
| 类型 | SQL | 加锁? | 读哪儿 |
|---|---|---|---|
| 快照读(普通 SELECT) | SELECT ... |
不加锁(乐观) | undo 版本链(可见版本) |
| 当前读(锁定读) | SELECT ... FOR UPDATE / LOCK IN SHARE MODE / UPDATE / DELETE / INSERT |
加锁 | 最新已提交版本 |
经典事故(面试题):
-- 会话A
BEGIN;
SELECT amount FROM account WHERE id=1; -- 快照读: 100
-- 会话B 此时把 amount 改成 50 并提交
UPDATE account SET amount=50 WHERE id=1; -- 当前读: 读到 50! (不是快照里的 100)
→ 快照读与当前读结果不一致是正常现象;需要一致性时用 FOR UPDATE 的当前读并加锁。
# selectivity.py —— 索引区分度(选择性)计算: 决定该不该建索引
import random
random.seed(1)
n = 10000
cols = {
'sex': [random.choice(['M', 'F']) for _ in range(n)],
'city': [random.choice(['北京', '上海', '广州', '深圳', '杭州', '成都']) for _ in range(n)],
'user_id': list(range(n)),
'log_time': [random.randint(0, 86400) for _ in range(n)],
}
for name, vals in cols.items():
sel = len(set(vals)) / n
tag = '✅ 适合索引' if sel > 0.1 else '⛔ 区分度太低, 索引意义不大'
print(f'{name:<9} 区分度={sel:6.2%} {tag}')
print('经验值: 区分度 > 10% 才值得建独立索引; 性别(2 值)建索引≈全扫')
sex 区分度= 0.02% ⛔ 区分度太低, 索引意义不大 city 区分度= 0.06% ⛔ 区分度太低, 索引意义不大 user_id 区分度=100.00% ✅ 适合索引 log_time 区分度=94.50% ✅ 适合索引 经验值: 区分度 > 10% 才值得建独立索引; 性别(2 值)建索引≈全扫
15. 主从复制与高可用(🟡 掌握)¶
💡 定义(小白版):主从复制=把一台主库(Master)上发生的每次写操作,通过 binlog 日志“复读”到一台或多台从库(Slave)上,让从库拥有主库的完整副本;读写分离=写操作只走主库,读操作分流到从库,减轻主库压力。 🏷️ 类比:主库=正本账本,从库=复印本:老板(写)只改正本,店员每天照正本抄一份复本(复制),顾客查账读复本(读写分离),正本就不怕被翻烂。
binlog 三种格式¶
| 格式 | 内容 | 问题 |
|---|---|---|
| STATEMENT | 记录 SQL | 非确定性函数(now()/uuid())主从不一致 |
| ROW(默认) | 记录行变更前后 | 日志量大但最可靠 |
| MIXED | 自动切换 | 安全语句用 STATEMENT, 否则 ROW |
复制链路¶
主库写 binlog → 从库 IO 线程拉取存中继日志(relay log) → SQL 线程重放
半同步复制(解决主从数据不一致)¶
- 异步(默认):主库提交即返回,从库延迟 → 主机故障可能丢数据
- 半同步:主库等至少一个从库 ack 才返回成功 → 最多丢一个事务窗口
- 全同步:等所有从库 ack → 写入慢,几乎不用
主从延迟解决方案¶
① 强制走主库(读最新写)② 缓存补偿(延迟期间读 Redis)③ 并行复制/多线程回放 ④ 数据校验 + 延迟报警
16. 分库分表策略(🟡 掌握,系统设计必考)¶
💡 定义(小白版):分库分表=单库/单表装不下时,把一个大库拆成多个库、一张大表拆成多张小表:垂直拆=按业务/字段切,水平拆=按数据行切(同一张表拆成 order_0、order_1…)。 🏷️ 类比:一个仓库放不下所有货,就按“服装区/食品区”分仓库(垂直分库),再按“1-999 号货架”细分(水平分表),每个仓库都轻装上阵。
分库 vs 分表¶
- 垂直分库:按业务模块拆库(订单库/用户库/库存库)→ 解耦 + 各自扩容
- 垂直分表:大表按字段拆分(常用字段 + 低频大字段)
- 水平分表:同一张表按 key 拆成多张(order_0 ... order_99)
水平分片键选择¶
| 方式 | 原理 | 优点 | 缺点 |
|---|---|---|---|
| Range 分片 | 按 ID/时间区间 | 连续、方便范围查询 | 热点(新数据集中) |
| Hash 分片 | hash(key) % N |
分布均匀 | 扩容要搬数据(一致性哈希缓解) |
| 中间件 | 一致性哈希/槽位 | 平滑扩缩容 | 引入代理层(MyCat/ShardingSphere) |
分库分表带来的问题(必背)¶
① 跨库 join 难 → 冗余字段/应用层组装;② 分布式事务 → TCC/Saga;③ 全局唯一 ID → 雪花/号段;④ 跨分片排序分页苦;⑤ 扩容期间数据迁移
17. 三大范式与反范式(🟡 掌握)¶
💡 定义(小白版):范式是设计表时的“去重复、消冗余”规范:第一范式=每格只装一个值;第二范式=每列必须完整依赖主键;第三范式=非主键列之间也不能互相依赖。反范式=故意存一点冗余,换查询更快。 🏷️ 类比:1NF=一格不能塞两颗糖;2NF=“订单详情”不能只靠订单号一半做主键;3NF=字典里不能写“北京→河北省”(城市不该依赖别的非主键列);反范式=通讯录里顺手存个“部门名”,省得每次查部门。
| 范式 | 要求 | 通俗理解 |
|---|---|---|
| 1NF | 字段不可再分 | 每列是原子值 |
| 2NF | 满足 1NF 且非主属性完全依赖主键 | 表内每一列都依托完整主键(不能只依赖主键一部分) |
| 3NF | 满足 2NF 且无传递依赖 | 非主键列不能依赖其他非主键列(如 省 依赖 城市) |
反范式(面试谈取舍)¶
- 3NF 消除冗余但 join 多、慢;读多写少场景反范式:冗余缓存列(如订单表冗余用户名)
- 策略:核心写库保持 3NF;查询/报表库冗余设计 + 定时同步
18. InnoDB vs MyISAM(🔴 必背对比表)¶
💡 定义(小白版):InnoDB 和 MyISAM 是 MySQL 的两种存储引擎:InnoDB 支持事务、行锁、外键、崩溃恢复(默认推荐);MyISAM 不支持事务、只支持表锁,但全表扫描/压缩读更快,属于“老派”引擎。 🏷️ 类比:InnoDB=带收银台、会员积分、断电能对账的“正规商场”(事务+行锁);MyISAM=只挂一个大锁、人人都能翻但改时全场锁门的“地摊市集”(表锁,简单但并发差)。
| 维度 | InnoDB | MyISAM |
|---|---|---|
| 事务 | 支持(ACID) | 不支持 |
| 锁粒度 | 行锁(+间隙锁) | 表锁 |
| 外键 | 支持 | 不支持 |
| 索引 | 聚簇索引(主键) | 非聚簇(索引存行指针) |
| 崩溃恢复 | redo log 恢复 | 依赖修复表 |
| 并发 | 高 | 低 |
| 适用 | OLTP(业务主库) | 读多写少/数仓(8.0 后已少用) |
MySQL 8.0:默认存储引擎 InnoDB,MyISAM 系统表被移除,面试提"MyISAM 了解即可"。
# count_compare.py —— count(*) / count(1) / count(列) 语义辨析 + 行数估算
rows = [{'id': 1, 'name': 'a'}, {'id': 2, 'name': None}, {'id': 3, 'name': 'c'}]
def count(rows, what):
if what == '*':
return sum(1 for _ in rows) # 统计行数, 含 NULL 行
if what == '1':
return sum(1 for _ in rows) # 常量, 统计行数
return sum(1 for r in rows if r[what] is not None) # 只统计该列非 NULL
print('3 行数据, name 有 1 个 NULL:')
print(f'count(*)= {count(rows, "*")} count(1)= {count(rows, "1")} count(name)= {count(rows, "name")}')
print('\n要点: count(*) 与 count(1) 等价(都数行); count(列) 跳过 NULL → 别用它数行数')
print('性能: InnoDB 下 count(*) 走最小二级索引扫描, 数据量大依然慢 → 用计数表/Redis 近似')
3 行数据, name 有 1 个 NULL: count(*)= 3 count(1)= 3 count(name)= 2 要点: count(*) 与 count(1) 等价(都数行); count(列) 跳过 NULL → 别用它数行数 性能: InnoDB 下 count(*) 走最小二级索引扫描, 数据量大依然慢 → 用计数表/Redis 近似
19. 连接池与性能调优常识(🟢 了解 + 面试聊资)¶
连接池参数¶
initialSize初始连接 /maxActive最大连接 /maxIdle最大空闲 /minIdle最小空闲- 不设 maxActive 的悲剧:QPS 上来后连接数暴涨 → 数据库连接耗尽
- 连接池大小经验:
核心数 × 2 + 有效磁盘数(PostgreSQL 官方公式);IO 型应用不宜过大(每连接 ~10MB 内存+线程)
常见性能调优手段(背诵清单)¶
① 慢查询日志 + EXPLAIN 定位 ② 索引覆盖高频查询 ③ 大字段/大表拆分 ④ 读写分离 ⑤ 缓存(Memcached/Redis)前置 ⑥ 批量写入代替逐条 ⑦ 关闭不必要的统计/SQL 直查
面试追问:如何定位慢 SQL¶
💡 答法提示:慢 SQL 定位按“开日志 → 看 EXPLAIN → 对症修”三步答:先说 slow_query_log 抓到 SQL,再看 EXPLAIN 的 type/key/rows 判断是否没走索引,最后给修复动作(加索引/改写/避免 SELECT *)即可拿满。
开启 slow_query_log → 分析 rows_examined 与 type → EXPLAIN 看是否走索引 → 常见修复:加索引/改写 JOIN/避免 SELECT *。
20. 面试高频 SQL 手写题清单(🟡 掌握模板)¶
1) 分组 TopN(每个部门工资前三)¶
SELECT dept_id, emp_name, salary
FROM (
SELECT dept_id, emp_name, salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) rn
FROM emp
) t WHERE rn <= 3;
2) 连续登录 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(*) >= 3;
3) 留存率/次日留存¶
SELECT COUNT(DISTINCT b.user_id) / COUNT(DISTINCT a.user_id) AS day1_retention
FROM (SELECT DISTINCT user_id FROM login_log WHERE login_date = '2024-01-01') a
LEFT JOIN (SELECT DISTINCT user_id FROM login_log WHERE login_date = '2024-01-02') b
ON a.user_id = b.user_id;
4) 行列转换(行转列)¶
SELECT user_id,
MAX(CASE WHEN item = '数学' THEN score END) AS math_score,
MAX(CASE WHEN item = '语文' THEN score END) AS chinese_score
FROM scores GROUP BY user_id;
面试建议:窗口函数(ROW_NUMBER/RANK/DENSE_RANK)+ GROUP BY + CASE WHEN 三件套必须默写。
21. 高频追问速答(1 分钟版)¶
- 为什么 B+ 树而不是 B 树做索引:B+ 树内节点不存数据 → 扇出大、树矮、IO 少;叶子成链表 → 范围查询友好
- 为什么用 B+ 树不用红黑树:红黑树高约 log2(n)≈20,每层一次磁盘 IO;B+ 树 3~4 层搞定千万级
- 唯一索引和普通索引查询区别:唯一索引命中即停;普通索引继续扫到边界(性能差异可忽略,但更新时唯一索引要额外检查)
- 为什么 RR 是 MySQL 默认:历史用 binlog 做复制,statement 模式下 RC 会主从不一致;RR + 间隙锁保证可重复读同时与 ROW 日志兼容
- 什么情况索引失效但全表扫反而快:小表/区分度低(优化器成本估算)
- 删除数据后表文件没变小怎么办:
OPTIMIZE TABLE重建表回收碎片(大表注意锁与空间)
22. EXPLAIN 核心列背诵(🔴 线上定位慢 SQL 必用)¶
type(访问类型, 从好到差)¶
system > const > eq_ref > ref > range > index > ALL
(系统表)(主键/唯一命中)(联接主键)(普通索引等值)(范围)(全索引扫)(全表扫)
- 目标:range 及以上;
ALL且表大 → 基本可以认定需要索引优化 - const:主键/唯一索引等值 → 最多 1 行,最快
- ref:普通索引等值(
WHERE city='杭州'走 city 索引) - index:遍历整个索引树——虽然不碰聚簇数据页,但全量索引也慢
其他关键列¶
| 列 | 含义 | 关注点 |
|---|---|---|
| key | 实际用的索引 | NULL → 没走索引 |
| key_len | 用到的索引字节数 | 键越短 IO 越少 |
| rows | 预估扫描行数 | 与真实差距大 → 统计信息旧, ANALYZE TABLE |
| Extra | Using filesort / Using temporary / Using index | filesort/temporary 尽量消除 |
追问:Using filesort 出现在什么情况?→ ORDER BY 列没被索引覆盖;解决:让排序列进索引或覆盖索引。
23. SQL 语言总览(🔴 必背分类 + 语法骨架)¶
💡 定义(小白版):SQL 按用途分五大类:DDL(建表改表)、DML(增删改数据)、DQL(查数据)、DCL(管权限)、TCL(管事务);其中查询是最常考的核心。 🏷️ 类比:五大类像厨房分工:DDL=买锅换灶(改结构)、DML=炒菜加料(改内容)、DQL=试菜品尝(查)、DCL=发门禁卡(权限)、TCL=决定整桌菜一起上还是撤单(事务)。
SQL 五大类¶
| 类别 | 全称 | 代表语句 | 作用 |
|---|---|---|---|
| DQL | Data Query | SELECT |
查询(面试 80% 都是它) |
| DML | Data Manipulation | INSERT / UPDATE / DELETE |
增删改 |
| DDL | Data Definition | CREATE / ALTER / DROP / TRUNCATE |
表结构定义 |
| DCL | Data Control | GRANT / REVOKE |
权限控制 |
| TCL | Transaction Control | BEGIN / COMMIT / ROLLBACK / SAVEPOINT |
事务控制 |
SELECT 骨架(背诵)¶
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 在分组之前、不能用聚合函数;HAVING 在分组之后、可以用聚合函数;
SELECT中的别名不能用于 WHERE(但可用于 ORDER BY/GROUP BY)。
# sql_base.py —— sqlite3 内存库: 建表 + 插入(后续各 cell 的公共数据模型)
import sqlite3
conn = sqlite3.connect(':memory:')
c = conn.cursor()
c.executescript('''
CREATE TABLE dept(dept_id INTEGER PRIMARY KEY, dept_name TEXT);
CREATE TABLE emp(
emp_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
dept_id INTEGER,
salary INTEGER,
hiredate TEXT -- 存 'YYYY-MM-DD'
);
INSERT INTO dept VALUES (1,'研发部'),(2,'产品部'),(3,'市场部');
INSERT INTO emp VALUES
(1,'张三',1,20000,'2020-03-01'),
(2,'李四',1,18000,'2019-07-15'),
(3,'王五',1,25000,'2022-01-10'),
(4,'赵六',2,15000,'2021-05-20'),
(5,'钱七',2,16000,'2020-11-02'),
(6,'孙八',3,12000,'2023-08-01'),
(7,'周九',3,11000,'2024-02-28'),
(8,'吴十',NULL,9000,'2024-06-15'); -- 无部门员工(演示 JOIN 用)
''')
print('员工表:')
for row in c.execute('SELECT * FROM emp ORDER BY emp_id'):
print(' ', row)
print('\nSQLite 版本:', sqlite3.sqlite_version, '| 表已就绪, 下面每个 cell 可独立跑 SQL')
员工表: (1, '张三', 1, 20000, '2020-03-01') (2, '李四', 1, 18000, '2019-07-15') (3, '王五', 1, 25000, '2022-01-10') (4, '赵六', 2, 15000, '2021-05-20') (5, '钱七', 2, 16000, '2020-11-02') (6, '孙八', 3, 12000, '2023-08-01') (7, '周九', 3, 11000, '2024-02-28') (8, '吴十', None, 9000, '2024-06-15') SQLite 版本: 3.50.4 | 表已就绪, 下面每个 cell 可独立跑 SQL
24. 子句执行顺序与 WHERE/HAVING 辨析(🔴 面试第一问)¶
为什么顺序重要¶
优化器按逻辑顺序处理,顺序决定了:
- WHERE 先过滤行 → 再分组聚合:提前过滤能大幅减少聚合数据量(下推优化)
- HAVING 后过滤组:只能用聚合结果(COUNT/SUM/AVG…)或分组列
- SELECT 别名:
WHERE salary * 1.1 > 20000可以(列存在),但WHERE 别名 > …不行(别名是 SELECT 阶段才生成的)
三条铁律(背)¶
- WHERE 不能写聚合函数(
WHERE COUNT(*) > 1❌ → 用 HAVING) - HAVING 可以写聚合函数,但条件里引用的列必须出现在 GROUP BY 或聚合中
- 能放 WHERE 就放 WHERE(先减行数),HAVING 只放分组后才知道的过滤
# sql_order.py —— 用真实执行验证 WHERE(先) vs HAVING(后) 与别名规则
import sqlite3
conn = sqlite3.connect(':memory:')
c = conn.cursor()
c.executescript('''
CREATE TABLE emp(name TEXT, dept TEXT, salary INT);
INSERT INTO emp VALUES
('A','dev',100),('B','dev',200),('C','ops',150),('D','ops',50);
''')
# ① WHERE 先过滤再聚合: 只统计 salary>100 的员工按部门平均
print('① WHERE 过滤后聚合:')
for r in c.execute("SELECT dept, AVG(salary) FROM emp WHERE salary > 100 GROUP BY dept"):
print(' ', r)
# ② HAVING 用聚合过滤: 部门平均工资 > 120 才输出
print('② HAVING 过滤组:')
for r in c.execute("SELECT dept, AVG(salary) m FROM emp GROUP BY dept HAVING m > 120"):
print(' ', r) # 别名 m 可用在 HAVING(SELECT 阶段别名其实要 MySQL 才宽松; 此处演示语义)
# ③ 对比: 同一条件 WHERE 与 HAVING 差异(WHERE 无法用聚合)
try:
c.execute('SELECT dept FROM emp WHERE COUNT(*) > 1 GROUP BY dept').fetchall()
except sqlite3.OperationalError as e:
print('③ WHERE 用聚合报错:', e)
print(' → 验证铁律 1: 聚合过滤必须放 HAVING')
① WHERE 过滤后聚合:
('dev', 200.0)
('ops', 150.0)
② HAVING 过滤组:
('dev', 150.0)
③ WHERE 用聚合报错: misuse of aggregate: COUNT()
→ 验证铁律 1: 聚合过滤必须放 HAVING
25. JOIN 全家桶(🔴 原理 + 应用场景)¶
💡 定义(小白版):JOIN 就是把两张表按共同字段“并桌拼数据”:INNER JOIN=只留两边都匹配的行;LEFT JOIN=左表全保留,右表没有的补 NULL;RIGHT JOIN=右表全保留,左表没有的补 NULL。 🏷️ 类比:INNER JOIN=只叫双方都在场的同学开会;LEFT JOIN=左表同学必须到场,右表能来几个算几个、空位写“缺席”;RIGHT JOIN=反过来。
| 类型 | 返回行 | 保留哪边 | 典型场景 |
|---|---|---|---|
| INNER JOIN | 两边都匹配 | 都不保留 | 只要有效关联数据 |
| LEFT JOIN | 左表全行 | 左表(右无匹配补 NULL) | 主表 + 可选附属信息 |
| RIGHT JOIN | 右表全行 | 右表 | 与 LEFT 对称(MySQL 早期不支持) |
| FULL OUTER JOIN | 两边全行 | 两边 | 全量对账 |
| CROSS JOIN | 笛卡尔积 | — | 生成排列组合/枚举 |
ON 与 WHERE 过滤位置(高频坑)¶
-- LEFT JOIN 时: 右表条件放 ON → 只影响匹配(仍保留左表行, 补 NULL)
-- 右表条件放 WHERE → 等于把不匹配行过滤掉(退化为 INNER)
为什么 LEFT JOIN 结果可能比左表行数多¶
若左表某行在右表匹配到 多行 → 结果行数膨胀(一对多)。面试答"先查重复/唯一性"。
# sql_join.py —— INNER / LEFT / ON vs WHERE 过滤位置 实战
import sqlite3
conn = sqlite3.connect(':memory:')
c = conn.cursor()
c.executescript('''
CREATE TABLE dept(dept_id INT PRIMARY KEY, dept_name TEXT);
CREATE TABLE emp(emp_id INT, name TEXT, dept_id INT);
INSERT INTO dept VALUES (1,'研发'),(2,'产品'),(9,'运维');
INSERT INTO emp VALUES (1,'张三',1),(2,'李四',1),(3,'赵六',2),(4,'无部门',NULL);
''')
print('① INNER JOIN(只匹配):')
for r in c.execute('''
SELECT e.name, d.dept_name FROM emp e
INNER JOIN dept d ON e.dept_id = d.dept_id ORDER BY e.emp_id'''):
print(' ', r)
print('② LEFT JOIN(左表全保留, 无匹配补 NULL):')
for r in c.execute('''
SELECT e.name, d.dept_name FROM emp e
LEFT JOIN dept d ON e.dept_id = d.dept_id ORDER BY e.emp_id'''):
print(' ', r)
print('③ 右表条件放 ON(保留左表, 无匹配补 NULL):')
for r in c.execute('''
SELECT e.name, d.dept_name FROM emp e
LEFT JOIN dept d ON e.dept_id = d.dept_id AND d.dept_name='研发'
ORDER BY e.emp_id'''):
print(' ', r)
print('④ 同一条件放 WHERE(过滤左表 → 退化为 INNER):')
for r in c.execute('''
SELECT e.name, d.dept_name FROM emp e
LEFT JOIN dept d ON e.dept_id = d.dept_id
WHERE d.dept_name='研发' ORDER BY e.emp_id'''):
print(' ', r)
① INNER JOIN(只匹配):
('张三', '研发')
('李四', '研发')
('赵六', '产品')
② LEFT JOIN(左表全保留, 无匹配补 NULL):
('张三', '研发')
('李四', '研发')
('赵六', '产品')
('无部门', None)
③ 右表条件放 ON(保留左表, 无匹配补 NULL):
('张三', '研发')
('李四', '研发')
('赵六', None)
('无部门', None)
④ 同一条件放 WHERE(过滤左表 → 退化为 INNER):
('张三', '研发')
('李四', '研发')
26. 聚合函数与 GROUP BY 陷阱(🔴 高频踩坑)¶
五个聚合函数¶
| 函数 | 语义 | 注意 |
|---|---|---|
| COUNT(*) | 行数 | 含 NULL 行 |
| COUNT(列) | 该列非 NULL 数 | 计数别用 COUNT(可空列) |
| SUM / AVG | 求和 / 平均 | NULL 自动跳过; 全 NULL 得 NULL |
| MAX / MIN | 最大 / 最小 | 文本列按字典序 |
GROUP BY 三大陷阱(背)¶
- SELECT 中出现非分组列:如
SELECT name, dept_id, COUNT(*) GROUP BY dept_id——标准 SQL 报错;MySQL 宽松模式取随机行(ONLY_FULL_GROUP_BY 打开后也报错) - COUNT(*) vs COUNT(列):
COUNT(commission)不计 NULL,可能比行数少 - HAVING 与 WHERE 混用顺序:先 WHERE 再 GROUP 再 HAVING,条件位置放错影响性能与结果
# sql_group.py —— GROUP BY + HAVING + 聚合函数实战
import sqlite3
conn = sqlite3.connect(':memory:')
c = conn.cursor()
c.executescript('''
CREATE TABLE emp(name TEXT, dept TEXT, salary INT, bonus INT);
INSERT INTO emp VALUES
('A','dev',100,10),('B','dev',200,NULL),('C','ops',150,5),('D','ops',50,NULL);
''')
print('① 按部门统计(人数/总薪/平均/最高):')
for r in c.execute('''
SELECT dept, COUNT(*) 人数, SUM(salary) 总薪,
AVG(salary) 平均, MAX(salary) 最高
FROM emp GROUP BY dept'''):
print(' ', r)
print('② COUNT(*) vs COUNT(bonus)(NULL 不计):')
for r in c.execute('''
SELECT dept, COUNT(*) 行数, COUNT(bonus) 有奖金人数
FROM emp GROUP BY dept'''):
print(' ', r)
print('③ HAVING 过滤组(总薪>200):')
for r in c.execute('''
SELECT dept, SUM(salary) FROM emp GROUP BY dept HAVING SUM(salary) > 200'''):
print(' ', r)
print('④ 面试追问: 分组后取每组最大值所在行 → 用窗口函数(下一节)或自连接')
① 按部门统计(人数/总薪/平均/最高):
('dev', 2, 300, 150.0, 200)
('ops', 2, 200, 100.0, 150)
② COUNT(*) vs COUNT(bonus)(NULL 不计):
('dev', 2, 1)
('ops', 2, 1)
③ HAVING 过滤组(总薪>200):
('dev', 300)
④ 面试追问: 分组后取每组最大值所在行 → 用窗口函数(下一节)或自连接
27. 窗口函数(🔴 面试必考, 与 GROUP BY 的本质区别)¶
一句话区别¶
- GROUP BY:多行合并成一行(行数变少)
- 窗口函数 OVER():每行保留,同时在窗口内算聚合/排名(行数不变)
常用四类¶
| 类别 | 函数 | 场景 |
|---|---|---|
| 排名 | ROW_NUMBER / RANK / DENSE_RANK | TopN(同分处理不同) |
| 偏移 | LAG / LEAD | 环比、同比、前后行 |
| 聚合窗口 | SUM/AVG/COUNT OVER (PARTITION BY ... ORDER BY ...) | 累计、分组占比 |
| 分组序号 | NTILE | 分桶、四分位 |
三种排名区别(背)¶
| 函数 | 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 | 并列不跳号 |
# sql_window.py —— 窗口函数实战: 部门工资排名 / 累计求和 / 环比
import sqlite3
conn = sqlite3.connect(':memory:')
c = conn.cursor()
c.executescript('''
CREATE TABLE emp(emp_id INT, name TEXT, dept TEXT, salary INT);
CREATE TABLE sales(month TEXT, amount INT);
INSERT INTO emp VALUES
(1,'A','dev',100),(2,'B','dev',200),(3,'C','dev',200),(4,'D','ops',150),(5,'E','ops',50);
INSERT INTO sales VALUES ('2024-01',10),('2024-02',15),('2024-03',12);
''')
print('① 部门内工资排名(RANK 并列跳号 vs DENSE_RANK 不跳):')
for r in c.execute('''
SELECT name, dept, salary,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) rk,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) drk
FROM emp ORDER BY dept, rk'''):
print(' ', r)
print('② 累计求和 SUM OVER (ORDER BY):')
for r in c.execute('''
SELECT month, amount,
SUM(amount) OVER (ORDER BY month) 累计
FROM sales'''):
print(' ', r)
print('③ LAG 环比(与上月比):')
for r in c.execute('''
SELECT month, amount,
LAG(amount) OVER (ORDER BY month) 上月,
amount - LAG(amount) OVER (ORDER BY month) 环比增量
FROM sales'''):
print(' ', r)
① 部门内工资排名(RANK 并列跳号 vs DENSE_RANK 不跳):
('B', 'dev', 200, 1, 1)
('C', 'dev', 200, 1, 1)
('A', 'dev', 100, 3, 2)
('D', 'ops', 150, 1, 1)
('E', 'ops', 50, 2, 2)
② 累计求和 SUM OVER (ORDER BY):
('2024-01', 10, 10)
('2024-02', 15, 25)
('2024-03', 12, 37)
③ LAG 环比(与上月比):
('2024-01', 10, None, None)
('2024-02', 15, 10, 5)
('2024-03', 12, 15, -3)
28. 子查询与 CTE(🟡 掌握: IN/EXISTS/标量 + WITH 可读性)¶
三种子查询¶
| 类型 | 位置 | 例子 | 注意 |
|---|---|---|---|
| 标量子查询 | SELECT 列 | SELECT (SELECT MAX(s) FROM t) |
必须返回 1 行 1 列 |
| 行子查询 | FROM | SELECT * FROM (SELECT ...) t |
派生表必须有别名 |
| 列子查询 | WHERE | WHERE id IN (SELECT ...) |
IN 与 EXISTS 语义差异 |
IN vs EXISTS(高频题)¶
- 小表驱动大表 / 大表驱动小表时代:EXISTS 半连接更早终止;现代优化器多会改写成 SEMI JOIN,性能差异趋同
- 语义:IN 把子查询结果集全算出来再比对;EXISTS 逐行探测、命中即停
- 注意 NULL:
NOT IN (子查询含 NULL)→ 永远为假(经典坑!)
CTE 优势¶
WITH top_dept AS (SELECT dept_id, AVG(salary) m FROM emp GROUP BY dept_id)
SELECT * FROM top_dept WHERE m > 15000;
- 可读性(拆步骤)、可复用(同查询多处引用)、可递归(WITH RECURSIVE 树形/序列)
# sql_subquery.py —— 子查询 / EXISTS / 递归 CTE 实战
import sqlite3
conn = sqlite3.connect(':memory:')
c = conn.cursor()
c.executescript('''
CREATE TABLE emp(name TEXT, dept TEXT, salary INT);
INSERT INTO emp VALUES ('A','dev',100),('B','dev',200),('C','ops',50);
''')
print('① 标量子查询: 高于全公司平均的员工:')
for r in c.execute('''
SELECT name FROM emp
WHERE salary > (SELECT AVG(salary) FROM emp)'''):
print(' ', r)
print('② EXISTS 语义(部门内有高薪员工的部门):')
for r in c.execute('''
SELECT DISTINCT dept FROM emp e
WHERE EXISTS (SELECT 1 FROM emp x WHERE x.dept=e.dept AND x.salary>150)'''):
print(' ', r)
print('③ 递归 CTE: 生成 1..5 序列:')
for r in c.execute('''
WITH RECURSIVE seq(n) AS (
SELECT 1 UNION ALL SELECT n+1 FROM seq WHERE n < 5
) SELECT * FROM seq'''):
print(' ', r)
print('④ NOT IN 含 NULL 陷阱:')
c.execute("INSERT INTO emp VALUES ('X','null_dept',10)")
for r in c.execute('''
SELECT name FROM emp
WHERE dept NOT IN (SELECT dept FROM emp WHERE salary > 150)'''):
print(' ', r) # SQLite 会把 NULL 部门行排除——结果为空(经典坑演示)
print(' → 用 NOT EXISTS 或 WHERE dept IS NOT NULL 修复')
① 标量子查询: 高于全公司平均的员工:
('B',)
② EXISTS 语义(部门内有高薪员工的部门):
('dev',)
③ 递归 CTE: 生成 1..5 序列:
(1,)
(2,)
(3,)
(4,)
(5,)
④ NOT IN 含 NULL 陷阱:
('C',)
('X',)
→ 用 NOT EXISTS 或 WHERE dept IS NOT NULL 修复
29. CASE WHEN 条件聚合(🟡 行列转换必用)¶
两种写法¶
CASE 表达式 WHEN 值 THEN 结果 [...] ELSE 兜底 END -- 简单 CASE
CASE WHEN 条件 THEN 结果 [...] ELSE 兜底 END -- 搜索 CASE(推荐, 可写范围)
行转列标准模板(背)¶
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;
(配 MAX 是因为同一组可能多行, 取非 NULL 值; 若科目唯一可直接 MAX)
条件聚合¶
统计"各状态订单数":COUNT(CASE WHEN status='paid' THEN 1 END)——NULL 不计数的特性被利用。
# sql_case.py —— CASE WHEN 行转列 + 条件聚合实战
import sqlite3
conn = sqlite3.connect(':memory:')
c = conn.cursor()
c.executescript('''
CREATE TABLE scores(user_id INT, subject TEXT, score INT);
INSERT INTO scores VALUES
(1,'数学',90),(1,'语文',85),(1,'英语',88),
(2,'数学',70),(2,'语文',95),(2,'英语',80);
''')
print('① 行转列(每人三科成绩一列):')
for r in c.execute('''
SELECT user_id,
MAX(CASE WHEN subject='数学' THEN score END) 数学,
MAX(CASE WHEN subject='语文' THEN score END) 语文,
MAX(CASE WHEN subject='英语' THEN score END) 英语
FROM scores GROUP BY user_id'''):
print(' ', r)
print('② 条件聚合: 各分数段人数:')
for r in c.execute('''
SELECT
COUNT(CASE WHEN score>=90 THEN 1 END) 优秀,
COUNT(CASE WHEN score>=80 AND score<90 THEN 1 END) 良好,
COUNT(CASE WHEN score<80 THEN 1 END) 待提升
FROM scores'''):
print(' ', r)
print('③ 分桶: 用 CASE 给工资分档并按档聚合:')
c.execute('''CREATE TABLE emp(name TEXT, salary INT)''')
c.executemany('INSERT INTO emp VALUES (?,?)',
[('A',100),('B',200),('C',300)])
for r in c.execute('''
SELECT CASE WHEN salary<150 THEN '低' WHEN salary<250 THEN '中' ELSE '高' END 档,
COUNT(*) 人数
FROM emp GROUP BY 1'''):
print(' ', r)
① 行转列(每人三科成绩一列):
(1, 90, 85, 88)
(2, 70, 95, 80)
② 条件聚合: 各分数段人数:
(2, 3, 1)
③ 分桶: 用 CASE 给工资分档并按档聚合:
('中', 1)
('低', 1)
('高', 1)
30. 常用函数速查(🟢 了解即可, 面试问 SQL 题会用到)¶
字符串¶
| 函数 | 作用 | |---|---| | LENGTH / CHAR_LENGTH | 字节数 / 字符数 | | CONCAT / || | 拼接 | | SUBSTR(s, pos, len) | 截取(MySQL 从 1 数) | | REPLACE / TRIM | 替换 / 去空格 | | UPPER / LOWER | 大小写 | | INSTR / LIKE | 查找位置 / 模糊匹配 |
日期(各库差异最大)¶
- MySQL:
NOW() / DATE_SUB(d, INTERVAL 1 DAY) / DATE_FORMAT(d,'%Y-%m-%d') / DATEDIFF(a,b) - SQLite:
date('now') / julianday(d) / strftime('%Y', d) - 面试高频:
DATE_SUB(curdate(), INTERVAL 1 DAY)拿昨天;DATEDIFF算间隔
数值 / 通用¶
| 函数 | 作用 |
|---|---|
| ROUND(x, n) / ABS / MOD | 四舍五入 / 绝对值 / 取模 |
| COALESCE(a, b, ...) | 返回第一个非 NULL(标准) |
| IFNULL(a, b) | 两参版(MYSQL/SQLite) |
| NULLIF(a, b) | a=b 返回 NULL |
# sql_funcs.py —— 字符串 / 日期 / 通用函数实战
import sqlite3
conn = sqlite3.connect(':memory:')
c = conn.cursor()
c.executescript('''
CREATE TABLE t(name TEXT, hiredate TEXT, bonus INT);
INSERT INTO t VALUES (' Zhang San ','2024-03-05',NULL),
('Li Si','2023-12-01',500);
''')
print('① 字符串: 去空格/大写/截取/拼接:')
for r in c.execute('''
SELECT TRIM(name) 清理, UPPER(SUBSTR(TRIM(name),1,1)) 首字母,
TRIM(name) || '@x.com' 邮箱
FROM t'''):
print(' ', r)
print('② 日期: 入职年/月, 距今天数:')
for r in c.execute('''
SELECT name, strftime('%Y', hiredate) 年, strftime('%m', hiredate) 月,
CAST(julianday('now') - julianday(hiredate) AS INT) 入职天数
FROM t'''):
print(' ', r)
print('③ COALESCE/IFNULL 兜底 NULL:')
for r in c.execute('''
SELECT name, COALESCE(bonus, 0) 奖金兜底
FROM t'''):
print(' ', r)
print(' 注意: SQLite 字符串按字典序可比较; 面试写 MySQL 版记得 DATE_FORMAT/NOW()')
① 字符串: 去空格/大写/截取/拼接:
('Zhang San', 'Z', 'Zhang San@x.com')
('Li Si', 'L', 'Li Si@x.com')
② 日期: 入职年/月, 距今天数:
(' Zhang San ', '2024', '03', 942)
('Li Si', '2023', '12', 1037)
③ COALESCE/IFNULL 兜底 NULL:
(' Zhang San ', 0)
('Li Si', 500)
注意: SQLite 字符串按字典序可比较; 面试写 MySQL 版记得 DATE_FORMAT/NOW()
31. DML 与事务控制(🟡 掌握: 事务是 ACID 的 I 落点)¶
💡 定义(小白版):DML 是增删改数据的三个命令(INSERT 插入 / UPDATE 修改 / DELETE 删除);事务控制命令(BEGIN / COMMIT / ROLLBACK)决定这些改动是“一起生效”还是“一起作废”。 🏷️ 类比:DML 是在白纸上写字,事务控制是“定稿”按钮:没点 COMMIT 前,随时可以 ROLLBACK 把纸擦干净。
DML 三兄弟¶
INSERT INTO t VALUES (...); -- 插入(可多行/INSERT ... SELECT)
UPDATE t SET 列=新值 WHERE 条件; -- 更新(务必带 WHERE!)
DELETE FROM t WHERE 条件; -- 删除(TRUNCATE 是 DDL, 清空不可回滚)
事务四命令¶
BEGIN / START TRANSACTION -- 开启
COMMIT -- 提交(持久化)
ROLLBACK -- 回滚(按 undo 恢复)
SAVEPOINT sp / ROLLBACK TO sp -- 部分回滚(嵌套)
事务的三大坑(背)¶
- MySQL 默认 autocommit=1:单条 DML 自动提交;多条业务要显式 BEGIN
- DDL 隐式提交:CREATE/ALTER/DROP 会先 COMMIT 当前事务,且不可回滚
- 长事务危害:锁持有久 → 阻塞并发 + undo 膨胀 + 主从延迟;拆小事务分批提交
# sql_txn.py —— 事务: 回滚 / 提交 / SAVEPOINT 部分回滚实战
import sqlite3
conn = sqlite3.connect(':memory:', isolation_level=None) # 手动事务模式: 显式 BEGIN/COMMIT
c = conn.cursor()
c.execute('CREATE TABLE account(uid INT, bal INT)')
c.execute("INSERT INTO account VALUES (1, 100), (2, 0)")
def show(tag):
rows = c.execute('SELECT * FROM account ORDER BY uid').fetchall()
print(f' {tag}: {rows}')
show('初始')
# ① 转账中途失败 → 回滚(原子性)
try:
c.execute('BEGIN')
c.execute('UPDATE account SET bal=bal-50 WHERE uid=1')
c.execute('UPDATE account SET bal=bal+50 WHERE uid=2')
raise RuntimeError('扣款后网络超时') # 模拟第二步失败
except RuntimeError as e:
c.execute('ROLLBACK')
print('① 转账失败已回滚:', e)
show('回滚后')
# ② 全部成功 → COMMIT 持久化
c.execute('BEGIN')
c.execute('UPDATE account SET bal=bal-50 WHERE uid=1')
c.execute('UPDATE account SET bal=bal+50 WHERE uid=2')
c.execute('COMMIT')
print('② 转账成功已提交:')
show('提交后')
# ③ SAVEPOINT 部分回滚
c.execute('BEGIN')
c.execute('UPDATE account SET bal=bal+10 WHERE uid=1')
c.execute('SAVEPOINT sp1')
c.execute('UPDATE account SET bal=bal-1000 WHERE uid=2') # 错误操作
c.execute('ROLLBACK TO sp1') # 只撤销 sp1 之后
c.execute('COMMIT')
print('③ SAVEPOINT 部分回滚(uid2 的 -1000 被撤销, uid1 的 +10 保留):')
show('最终')
初始: [(1, 100), (2, 0)] ① 转账失败已回滚: 扣款后网络超时 回滚后: [(1, 100), (2, 0)] ② 转账成功已提交: 提交后: [(1, 50), (2, 50)] ③ SAVEPOINT 部分回滚(uid2 的 -1000 被撤销, uid1 的 +10 保留): 最终: [(1, 60), (2, 50)]
32. 索引与执行计划(🔴 EXPLAIN 是慢 SQL 定位第一步)¶
为什么索引快¶
- 全表扫 = 逐行读数据页(O(n) 磁盘 IO)
- 索引 = B+ 树,等值/范围查询 O(log n) 次磁盘 IO
- 覆盖索引(查询列全在索引)→ 连回表都免了
EXPLAIN 关键列(SQLite: EXPLAIN QUERY PLAN / MySQL: EXPLAIN)¶
| 列 | 含义 | 关注点 |
|---|---|---|
| type (MySQL) | const/ref/range/index/ALL | 目标是 range 及以上 |
| key | 实际使用的索引 | NULL = 没走索引 |
| rows | 预估扫描行数 | 越小越好 |
| detail (SQLite) | 扫描方式描述 | 出现 SCAN TABLE = 全表扫 |
什么时候索引失效¶
函数/运算、隐式类型转换、前导模糊、违反最左前缀、负向条件(见 11 章清单)。
# sql_explain.py —— EXPLAIN QUERY PLAN: 无索引全扫 vs 有索引检索 对比
import sqlite3
conn = sqlite3.connect(':memory:')
c = conn.cursor()
c.execute('CREATE TABLE emp(emp_id INT, name TEXT, salary INT)')
# 插入 5000 行, 工资随机, 姓氏循环编号(有重复方便等值查询)
c.executemany('INSERT INTO emp VALUES (?,?,?)',
[(i, f'name{i%500}', (i*37) % 100000) for i in range(5000)])
def plan(sql, tag):
rows = c.execute(f'EXPLAIN QUERY PLAN {sql}').fetchall()
print(f'{tag}:')
for r in rows:
print(' ', r[3])
plan("SELECT * FROM emp WHERE name='name42'", '① 无索引等值查询(全表扫)')
c.execute('CREATE INDEX idx_emp_name ON emp(name)')
plan("SELECT * FROM emp WHERE name='name42'", '② 建索引后等值查询(走索引)')
plan("SELECT * FROM emp WHERE name LIKE 'name4%'", '③ 前导匹配范围查询(走索引)')
plan("SELECT * FROM emp WHERE name LIKE '%name42%'", '④ 中缀模糊(索引失效, 全扫)')
plan("SELECT emp_id FROM emp WHERE name='name42'", '⑤ 覆盖索引查询(免回表)')
① 无索引等值查询(全表扫):
SCAN emp
② 建索引后等值查询(走索引):
SEARCH emp USING INDEX idx_emp_name (name=?)
③ 前导匹配范围查询(走索引):
SCAN emp
④ 中缀模糊(索引失效, 全扫):
SCAN emp
⑤ 覆盖索引查询(免回表):
SEARCH emp USING INDEX idx_emp_name (name=?)
33. 面试高频 SQL 题精讲(🔴 模板 + 变体)¶
1) 连续登录 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;
核心思想:日期 − 连续序号 = 恒定值 → 同组即连续。
2) TopN(每组前三)¶
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) rn
FROM emp
) t WHERE rn <= 3;
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);
4) 中位数(无内置函数时)¶
SELECT AVG(salary) FROM (
SELECT salary, ROW_NUMBER() OVER (ORDER BY salary) rn,
COUNT(*) OVER () cnt FROM emp
) t WHERE rn BETWEEN cnt/2.0 AND cnt/2.0 + 1;
# sql_consecutive.py —— 连续登录 N 天(真实 SQL 跑通) + 去重变体
import sqlite3
conn = sqlite3.connect(':memory:')
c = conn.cursor()
c.executescript('''
CREATE TABLE login_log(user_id INT, login_date TEXT);
INSERT INTO login_log VALUES
(1,'2024-01-01'),(1,'2024-01-02'),(1,'2024-01-03'),(1,'2024-01-05'),
(2,'2024-01-01'),(2,'2024-01-02'),(2,'2024-01-04'),
(3,'2024-01-01'),(3,'2024-01-02'),(3,'2024-01-03');
''')
print('连续登录 ≥3 天的用户(SQLite 用 julianday 把日期转数字):')
rows = c.execute('''
SELECT user_id
FROM (
SELECT user_id, login_date,
julianday(login_date) - ROW_NUMBER()
OVER (PARTITION BY user_id ORDER BY login_date) AS grp
FROM login_log
) t
GROUP BY user_id, grp
HAVING COUNT(*) >= 3''')
print(' ', rows.fetchall())
print('若同一天可能多条登录(去重后判断): 先 SELECT DISTINCT user_id, login_date 再套上述逻辑')
print('MySQL 版把 julianday(x) 换成 DATE_SUB(x, INTERVAL rn DAY)')
连续登录 ≥3 天的用户(SQLite 用 julianday 把日期转数字): [(1,), (3,)] 若同一天可能多条登录(去重后判断): 先 SELECT DISTINCT user_id, login_date 再套上述逻辑 MySQL 版把 julianday(x) 换成 DATE_SUB(x, INTERVAL rn DAY)
34. SQL 注入与参数化防护(🔴 安全必考)¶
💡 定义(小白版):SQL 注入=用户输入里夹带 SQL 代码、被当成命令执行,从而绕过登录、删库改表;参数化查询=用
?占位符把“数据”和“SQL 结构”分开,让输入永远只是数据,这是最有效的防线。 🏷️ 类比:SQL 注入=有人在点餐单的“备注”栏写“再来一份免费牛排”,服务员照单全收;参数化查询=把备注单单独交给后厨看,备注只是备注,永远变不成菜品指令。
什么是注入¶
用户输入被拼进 SQL 字符串 → 输入里的引号/注释/语句改变了原 SQL 语义。
经典攻击载荷¶
-- 输入: ' OR '1'='1
SELECT * FROM users WHERE name = '' OR '1'='1'; -- 绕过登录, 返回全部用户
-- 输入: '; DROP TABLE users; --
SELECT * FROM users WHERE name = ''; DROP TABLE users; --';
三道防线(背)¶
- 参数化查询/预编译(首选):占位符
?,输入只当数据不当 SQL - ORM 参数绑定:SQLAlchemy/MyBatis
#{}(MyBatis${}会拼接 = 危险) - 输入校验 + 最小权限账户:白名单校验、DB 账户不给 DROP/权限隔离
# sql_injection.py —— 拼接注入 vs 参数化防护 实战对比
import sqlite3
conn = sqlite3.connect(':memory:')
c = conn.cursor()
c.executescript('''
CREATE TABLE users(name TEXT PRIMARY KEY, pwd TEXT);
INSERT INTO users VALUES ('admin','secret'),('alice','123');
''')
def login_vulnerable(name, pwd):
"""危险版: 字符串拼接"""
sql = f"SELECT * FROM users WHERE name='{name}' AND pwd='{pwd}'"
return sql, c.execute(sql).fetchall()
def login_safe(name, pwd):
"""安全版: 参数化占位符"""
sql = 'SELECT * FROM users WHERE name=? AND pwd=?'
return sql, c.execute(sql, (name, pwd)).fetchall()
# 攻击者输入
payload_name = "' OR '1'='1"
payload_pwd = "' OR '1'='1"
sql1, rows1 = login_vulnerable(payload_name, payload_pwd)
print('① 拼接版 SQL:')
print(' ', sql1)
print(' 登录结果(被绕过, 拿到全部账号):', [r[0] for r in rows1])
sql2, rows2 = login_safe(payload_name, payload_pwd)
print('② 参数化版:')
print(' 登录结果(安全):', rows2)
print('\n面试补充: 预编译后输入只是字符串字面量, 不再参与语法解析 → 注入无效')
① 拼接版 SQL:
SELECT * FROM users WHERE name='' OR '1'='1' AND pwd='' OR '1'='1'
登录结果(被绕过, 拿到全部账号): ['admin', 'alice']
② 参数化版:
登录结果(安全): []
面试补充: 预编译后输入只是字符串字面量, 不再参与语法解析 → 注入无效
35. SQL 高频追问速答(1 分钟版)¶
- WHERE 和 HAVING 区别:WHERE 分组前过滤行、不能用聚合;HAVING 分组后过滤组、能用聚合
- COUNT(*)/COUNT(1)/COUNT(列):前两者数行等价;后者跳过 NULL
- LEFT JOIN 结果比左表多:右表一对多匹配 → 先查右表 key 是否唯一
- IN vs EXISTS:语义上 IN 先物化子集、EXISTS 逐行半连接;现代优化器常等价改写;注意 NOT IN + NULL 陷阱
- 窗口函数 vs GROUP BY:窗口不合并行、能同时看到明细与聚合;GROUP BY 合并行
- SELECT 执行顺序:FROM→JOIN→WHERE→GROUP BY→HAVING→SELECT→DISTINCT→ORDER BY→LIMIT
- union vs union all:union 去重(默认, 有排序成本)、union all 全保留更快
- 主键 vs 唯一索引:主键非空且每表一个;唯一索引允许 NULL(部分库)、可多个
- 怎么定位慢 SQL:慢查询日志 → EXPLAIN 看 type/key/rows → 加索引/改写/覆盖索引
- SQL 注入怎么防:参数化预编译为主,ORM 绑定、白名单校验、最小权限账户兜底