文章

数据库面试题 —— 从索引原理到分布式存储的深度问答

覆盖索引与 B+ 树、SQL 优化、事务 ACID、隔离级别与 MVCC、锁机制、日志系统、主从复制、分库分表八大核心主题，35 道高频面试题附原理图解

发表于
作者 xirain
42 分钟阅读
数据库面试题 —— 从索引原理到分布式存储的深度问答

数据库是后端工程师的”命根子”——几乎所有业务最终都会落到数据的存取上。面试中数据库题目不仅考察你会不会写 SQL，更考察你对存储引擎、事务模型、锁机制的理解深度。

这篇文章的组织思路：每道题先给出”一句话记忆点”，再展开原理，最后附上面试回答要点。以 MySQL InnoDB 为主线，兼顾通用数据库原理。

第一部分：索引与 B+ 树

Q1：为什么 MySQL 选用 B+ 树而不是 B 树、红黑树或哈希表？

记忆点：B+ 树矮胖（扇出大、层数少 → 磁盘 IO 少），叶子链表（范围查询快），非叶节点只存 key（一个页能装更多索引项）。

数据结构缺点
哈希表只能等值查询，不支持范围查询和排序
红黑树树高 O(log₂N)，数据量大时太高，磁盘 IO 多
B 树非叶节点也存数据，扇出比 B+ 树小，同样数据量树更高
B+ 树非叶节点只存 key，扇出极大；叶子节点用双向链表串联，范围扫描只需顺序遍历

InnoDB 一页 16KB，假设主键 bigint(8B) + 指针(6B) = 14B，一页约存 16KB/14B ≈ 1170 个 key。三层 B+ 树可存 1170 × 1170 × 16 ≈ 2000 万行数据，只需 3 次磁盘 IO。

Q2：聚簇索引和非聚簇索引（二级索引）的区别？

记忆点：聚簇索引 = 索引即数据（叶子节点存整行数据），二级索引的叶子节点存的是主键值，查到主键后还要”回表”。

1
2
3
4
5
6
7
8
9

聚簇索引（主键索引）:
  [非叶节点: 主键 key]
         |
  [叶子节点: 主键 key + 整行数据]  ← 数据和索引在一起

二级索引（name 索引）:
  [非叶节点: name key]
         |
  [叶子节点: name key + 主键值]  ← 还需要拿主键值回表查聚簇索引

回表：通过二级索引找到主键值，再拿主键值去聚簇索引查完整数据行。一次查询可能产生两次 B+ 树搜索。

覆盖索引：如果查询的字段在二级索引中全部包含，就不需要回表。例如 SELECT id, name FROM user WHERE name = 'Tom'，如果有 (name) 索引，叶子节点已经包含 name 和 id（主键），直接返回，无需回表。EXPLAIN 中 Extra 显示 Using index。

Q3：联合索引的最左前缀原则是什么？

记忆点：联合索引 (a, b, c) 相当于建了 (a)、(a, b)、(a, b, c) 三个索引。查询条件必须从最左列开始，中间不能跳列。

假设有联合索引 idx_abc(a, b, c)：

查询条件能否使用索引说明
WHERE a = 1走索引最左列
WHERE a = 1 AND b = 2走索引前两列
WHERE a = 1 AND b = 2 AND c = 3走索引全部列
WHERE b = 2不走索引缺少最左列 a
WHERE a = 1 AND c = 3部分走索引只用到 a，c 无法利用（中间断了 b）
WHERE a = 1 AND b > 2 AND c = 3部分走索引a 和 b 走索引，b 是范围查询后 c 无法走索引

面试加分： MySQL 8.0 引入了索引跳跃扫描（Index Skip Scan），在某些条件下即使缺少最左列也能使用索引，但效率有限，不能依赖。

Q4：什么时候索引会失效？

记忆点：对索引列做函数/运算、隐式类型转换、LIKE 左模糊、OR 连接非索引列、NOT IN/NOT EXISTS（部分情况）。

常见索引失效场景：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

-- 1. 对索引列使用函数
SELECT * FROM user WHERE YEAR(create_time) = 2026;  -- 失效
SELECT * FROM user WHERE create_time >= '2026-01-01' AND create_time < '2027-01-01';  -- 改写

-- 2. 对索引列做运算
SELECT * FROM user WHERE id + 1 = 10;  -- 失效
SELECT * FROM user WHERE id = 9;       -- 改写

-- 3. 隐式类型转换（字符串列用数字查询）
SELECT * FROM user WHERE phone = 13800138000;  -- phone 是 varchar，失效
SELECT * FROM user WHERE phone = '13800138000'; -- 正确

-- 4. LIKE 左模糊
SELECT * FROM user WHERE name LIKE '%Tom';   -- 失效
SELECT * FROM user WHERE name LIKE 'Tom%';   -- 走索引

-- 5. OR 连接未索引列
SELECT * FROM user WHERE name = 'Tom' OR age = 18;  -- 如果 age 没索引，整体失效

Q5：EXPLAIN 执行计划怎么看？重点关注哪些字段？

记忆点：重点看 type（访问类型）、key（实际用的索引）、rows（扫描行数）、Extra（额外信息）。type 从好到差：system > const > eq_ref > ref > range > index > ALL。

字段含义优化目标
type访问类型至少 range，避免 ALL（全表扫描）
possible_keys可能使用的索引—
key实际使用的索引确认命中预期索引
key_len索引使用长度联合索引看用了几列
rows预估扫描行数越小越好
filtered过滤比例越大越好
Extra额外信息Using index（覆盖索引好）、Using filesort（需优化）、Using temporary（需优化）

第二部分：SQL 优化

Q6：慢查询如何排查和优化？

记忆点：开启慢查询日志 → EXPLAIN 分析 → 加索引/改写 SQL → 验证。

排查步骤：

  1. 开启慢查询日志：
    1
2

    SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过 1 秒记录
  2. 用 EXPLAIN 分析：看 type、rows、Extra
  3. 常见优化手段：
    • 添加合适的索引（联合索引优于多个单列索引）
    • 避免 SELECT *，只查需要的列（减少网络传输 + 可能触发覆盖索引）
    • 大分页优化：LIMIT 100000, 10 → 延迟关联或游标分页
    • 子查询改 JOIN
    • 拆分复杂查询为多个简单查询

Q7：大分页（深度翻页）问题怎么解决？

记忆点：LIMIT offset, size 的 offset 越大越慢，因为 MySQL 要扫描 offset + size 行再丢弃前 offset 行。用”延迟关联”或”游标分页”解决。

1
2
3
4
5
6
7
8
9
10

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

-- 方案一：延迟关联（先查主键，再回表）
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 10) t
ON o.id = t.id;

-- 方案二：游标分页（记住上一页最后一条的 id）
SELECT * FROM orders WHERE id > 上一页最后的id ORDER BY id LIMIT 10;

游标分页要求排序字段唯一且有索引，是最推荐的方案。

Q8：COUNT(*) 、COUNT(1)、COUNT(列) 有什么区别？

记忆点：COUNT(*) = COUNT(1)，统计总行数（包含 NULL）；COUNT(列) 只统计该列非 NULL 的行数。在 InnoDB 中三者性能几乎相同，MySQL 会选最小的索引树来统计。

1
2
3

SELECT COUNT(*) FROM user;    -- 统计总行数，包括所有行
SELECT COUNT(1) FROM user;    -- 等价于 COUNT(*)
SELECT COUNT(name) FROM user; -- 只统计 name 不为 NULL 的行

InnoDB 为什么 COUNT(*) 慢？ 因为 InnoDB 支持 MVCC，不同事务看到的行数可能不同，无法像 MyISAM 那样直接存一个计数器，必须实际遍历。

第三部分：事务与 ACID

Q9：什么是事务的 ACID 特性？

记忆点：原子性（要么全做要么全不做）、一致性（状态合法转换）、隔离性（并发事务互不干扰）、持久性（提交后永久保存）。原子性靠 undo log，持久性靠 redo log，隔离性靠锁 + MVCC，一致性是最终目标。

特性含义实现机制
Atomicity（原子性）事务是不可分割的最小工作单位undo log（回滚日志）
Consistency（一致性）事务前后数据库从一个合法状态到另一个合法状态由 A、I、D 共同保证
Isolation（隔离性）并发事务之间互不干扰锁 + MVCC
Durability（持久性）事务提交后数据永久保存redo log（重做日志）

Q10：事务的四种隔离级别分别解决了什么问题？

记忆点：读未提交（啥都不防）→ 读已提交（防脏读）→ 可重复读（防脏读+不可重复读，InnoDB 默认）→ 串行化（全防但最慢）。

先明确三种并发问题：

问题描述例子
脏读读到其他事务未提交的数据事务 A 修改了一行但未提交，事务 B 读到了修改后的值，A 回滚，B 读到的是脏数据
不可重复读同一事务内两次读同一行结果不同事务 A 第一次读 age=20，事务 B 修改 age=25 并提交，事务 A 再读 age=25
幻读同一事务内两次范围查询行数不同事务 A 查 WHERE age > 20 得 5 行，事务 B 插入一行 age=22 并提交，事务 A 再查得 6 行
隔离级别脏读不可重复读幻读说明
READ UNCOMMITTED可能可能可能几乎不用
READ COMMITTED防止可能可能Oracle 默认，每次 SELECT 生成新快照
REPEATABLE READ防止防止可能InnoDB 默认，事务开始时生成快照
SERIALIZABLE防止防止防止全部加锁，性能最差

面试加分： InnoDB 在 RR 级别下通过 MVCC + Next-Key Lock 大幅避免了幻读（快照读通过 MVCC 避免，当前读通过间隙锁避免），但并非 100% 杜绝。

Q11：什么是 MVCC？它是如何工作的？

记忆点：MVCC = 多版本并发控制，每行数据有多个版本（通过 undo log 链维护），读操作根据 Read View 判断该看哪个版本，实现”读不加锁、读写不冲突”。

InnoDB 的 MVCC 核心组件：

  1. 隐藏列：每行数据自带 DB_TRX_ID（最近修改的事务 ID）和 DB_ROLL_PTR（指向 undo log 的回滚指针）
  2. Undo Log 版本链：每次修改都把旧版本写入 undo log，通过 DB_ROLL_PTR 串成链表
  3. Read View（快照）：事务在做快照读时生成，包含：
    • m_ids：当前活跃（未提交）的事务 ID 列表
    • min_trx_id：活跃事务中最小的 ID
    • max_trx_id：下一个待分配的事务 ID
    • creator_trx_id：创建该 Read View 的事务 ID

可见性判断规则：

1
2
3
4
5
6
7
8
9

对于某行的 DB_TRX_ID：
1. 如果 trx_id == creator_trx_id → 可见（自己修改的）
2. 如果 trx_id < min_trx_id → 可见（修改该行的事务已提交）
3. 如果 trx_id >= max_trx_id → 不可见（修改该行的事务在创建快照之后才开始）
4. 如果 min_trx_id <= trx_id < max_trx_id：
   - trx_id 在 m_ids 中 → 不可见（该事务还未提交）
   - trx_id 不在 m_ids 中 → 可见（该事务已提交）

如果当前版本不可见，就沿 undo log 版本链往前找，直到找到可见的版本。

RC vs RR 的区别： RC 在每次 SELECT 时都生成新的 Read View，所以能看到其他事务已提交的修改；RR 只在事务第一次 SELECT 时生成 Read View，后续复用，所以看到的是一致性快照。

Q12：快照读和当前读的区别？

记忆点：普通 SELECT 是快照读（读 MVCC 版本），加锁的 SELECT 和 DML 是当前读（读最新数据并加锁）。

1
2
3
4
5
6
7
8
9
10

-- 快照读（不加锁，读 MVCC 快照）
SELECT * FROM user WHERE id = 1;

-- 当前读（加锁，读最新版本）
SELECT * FROM user WHERE id = 1 FOR UPDATE;         -- 加排他锁
SELECT * FROM user WHERE id = 1 FOR SHARE;           -- 加共享锁（8.0+）
SELECT * FROM user WHERE id = 1 LOCK IN SHARE MODE;  -- 加共享锁
INSERT INTO user ...;   -- 当前读
UPDATE user SET ...;    -- 当前读
DELETE FROM user ...;   -- 当前读

第四部分：锁机制

Q13：InnoDB 有哪些锁？

记忆点：按粒度分——行锁（记录锁、间隙锁、临键锁）和表锁（意向锁、AUTO-INC 锁）。按模式分——共享锁（S）和排他锁（X）。

1
2
3
4
5
6
7
8
9
10
11
12

InnoDB 锁体系
├── 表级锁
│   ├── 意向共享锁（IS）：事务想获取行级 S 锁前，先加表级 IS 锁
│   ├── 意向排他锁（IX）：事务想获取行级 X 锁前，先加表级 IX 锁
│   └── AUTO-INC 锁：自增主键插入时短暂加锁
├── 行级锁
│   ├── Record Lock（记录锁）：锁住索引上的一条记录
│   ├── Gap Lock（间隙锁）：锁住索引记录之间的间隙，防止插入
│   └── Next-Key Lock（临键锁）：Record Lock + Gap Lock，左开右闭区间
└── 模式
    ├── S 锁（共享锁）：读锁，多个事务可同时持有
    └── X 锁（排他锁）：写锁，独占

Q14：什么是间隙锁（Gap Lock）和临键锁（Next-Key Lock）？

记忆点：间隙锁锁的是索引记录之间的”缝隙”，防止其他事务往缝隙里插入数据，是 InnoDB 在 RR 级别下防止幻读的关键。临键锁 = 记录锁 + 间隙锁。

假设表中 id 有值 1, 5, 10, 15：

1
2
3
4
5

间隙锁可能锁住的区间：
(-∞, 1)  (1, 5)  (5, 10)  (10, 15)  (15, +∞)

临键锁（Next-Key Lock）左开右闭：
(-∞, 1]  (1, 5]  (5, 10]  (10, 15]  (15, +∞)

1
2
3
4

-- 例：事务 A
SELECT * FROM t WHERE id > 5 AND id < 15 FOR UPDATE;
-- 会加 Next-Key Lock：(5, 10] 和 (10, 15]
-- 其他事务无法在 (5, 15] 范围内插入数据

面试加分： 间隙锁只在 RR 隔离级别下存在。在 RC 级别下没有间隙锁，所以 RC 不能防止幻读。

Q15：什么情况下会发生死锁？如何解决？

记忆点：两个事务互相等待对方持有的锁。InnoDB 有死锁检测（wait-for graph），检测到后回滚代价小的事务。

1
2
3
4
5
6
7

-- 死锁示例：
-- 事务 A                          -- 事务 B
BEGIN;                              BEGIN;
UPDATE t SET v=1 WHERE id=1;        UPDATE t SET v=2 WHERE id=2;
-- 持有 id=1 的 X 锁                -- 持有 id=2 的 X 锁
UPDATE t SET v=1 WHERE id=2;        UPDATE t SET v=2 WHERE id=1;
-- 等待 id=2 的 X 锁 ← 死锁！       -- 等待 id=1 的 X 锁 ← 死锁！

预防死锁的方法：

  1. 按固定顺序访问资源（比如按 id 从小到大加锁）
  2. 控制事务粒度，尽量短事务
  3. 合理建索引，避免因索引缺失导致锁升级为表锁
  4. 使用 innodb_lock_wait_timeout 设置锁等待超时

第五部分：日志系统

Q16：redo log、undo log、binlog 各自的作用？

记忆点：redo log 保证持久性（崩溃恢复），undo log 保证原子性（事务回滚 + MVCC），binlog 保证数据一致性（主从复制 + 数据恢复）。

日志层级作用写入时机
redo logInnoDB 引擎层崩溃恢复：记录”物理”修改（某页某偏移写了什么）事务执行中不断写入，commit 时刷盘
undo logInnoDB 引擎层事务回滚 + MVCC 版本链修改数据前写入
binlogServer 层主从复制、数据恢复：记录”逻辑”操作（SQL 或行变更）事务 commit 时写入

Q17：redo log 的两阶段提交（2PC）是怎么回事？

记忆点：为了保证 redo log 和 binlog 的一致性，InnoDB 使用内部两阶段提交——先 prepare redo log，再写 binlog，最后 commit redo log。

1
2
3
4
5
6
7
8
9

事务 commit 流程：
1. InnoDB 写 redo log，标记为 prepare 状态
2. Server 层写 binlog
3. InnoDB 将 redo log 标记为 commit 状态

崩溃恢复时：
- 如果 redo log 是 prepare 且 binlog 完整 → 提交事务
- 如果 redo log 是 prepare 但 binlog 不完整 → 回滚事务
- 如果 redo log 是 commit → 提交事务

为什么需要两阶段提交？ 如果先写 redo log 再写 binlog，中间崩溃会导致 redo log 有但 binlog 没有，主从数据不一致。反过来也一样。两阶段提交保证两个日志要么都有要么都没有。

Q18：WAL（Write-Ahead Logging）机制是什么？

记忆点：先写日志再写数据页。修改数据时先把变更记录到 redo log（顺序写，快），脏页后续由后台线程异步刷盘（随机写，慢）。用顺序 IO 代替随机 IO 大幅提升性能。

1
2
3
4
5
6
7

传统方式（不用 WAL）：
  修改数据 → 直接写磁盘数据页（随机 IO，慢）

WAL 方式：
  修改数据 → 写 redo log（顺序 IO，快）→ 修改内存中的 Buffer Pool → 返回成功
                                              ↓
                                     后台线程异步刷脏页到磁盘

redo log 是固定大小的循环写文件，比如 4 个文件每个 1GB，write pos 和 checkpoint 之间是可用空间。当 write pos 追上 checkpoint 时必须刷盘腾出空间。

第六部分：主从复制

Q19：MySQL 主从复制的原理？

记忆点：三个线程——主库 Binlog Dump 线程 + 从库 IO 线程 + 从库 SQL 线程。主库写 binlog → 从库拉取写 relay log → 从库回放 relay log。

1
2
3
4
5
6
7
8
9
10
11
12
13

主库（Master）                    从库（Slave）
    |                                |
  [写操作]                           |
    |                                |
  [写 binlog]                        |
    |                                |
  [Binlog Dump Thread] --binlog→  [IO Thread]
                                     |
                                  [写 relay log]
                                     |
                                  [SQL Thread]
                                     |
                                  [回放到从库数据]

Q20：MySQL 主从复制有哪些模式？

记忆点：异步复制（默认，主库不等从库，可能丢数据）→ 半同步复制（至少一个从库确认收到 relay log 才返回）→ 全同步复制（所有从库回放完才返回，性能差）。

模式数据安全性性能说明
异步复制低（主库崩溃可能丢数据）最高默认模式
半同步复制较高中等rpl_semi_sync_master_wait_point 控制等待点
全同步复制最高最低很少使用
GTID 复制——基于全局事务 ID，简化故障切换

Q21：主从延迟怎么解决？

记忆点：从库 SQL 线程是单线程回放（5.6 之前），是延迟的根因。解决方案：并行复制、读主库兜底、中间件路由。

常见解决方案：

  1. 并行复制：MySQL 5.7+ 支持基于逻辑时钟的并行复制，8.0 支持 WRITESET 模式
  2. 强制走主库：对时效性要求高的读操作直接查主库
  3. 半同步复制：确保从库至少收到 binlog
  4. 监控延迟：SHOW SLAVE STATUS 的 Seconds_Behind_Master

第七部分：分库分表

Q22：什么时候需要分库分表？

记忆点：单表数据量超过 2000 万行或单库连接数/IO 成为瓶颈时考虑。先尽量优化（索引、缓存、读写分离），实在不行再分。

1
2
3
4
5
6
7

优化顺序（从简单到复杂）：
1. SQL 优化 + 索引优化
2. 读写分离（主写从读）
3. 缓存（Redis）
4. 垂直分库（按业务拆分）
5. 水平分表（单表数据量大）
6. 水平分库（单库瓶颈）

Q23：水平分表和垂直分表的区别？

记忆点：水平分表按行拆（同结构多张表），垂直分表按列拆（热冷数据分离）。

1
2
3
4
5
6
7
8
9
10
11

垂直分表（按列拆分）：
user 表 → user_base(id, name, age) + user_detail(id, bio, avatar, address)
适用场景：表列数太多，把不常用的大字段拆出去

水平分表（按行拆分）：
order 表 → order_0, order_1, ... order_15（按 user_id % 16 路由）
适用场景：单表数据量太大

垂直分库（按业务拆分）：
电商数据库 → 用户库、订单库、商品库
适用场景：单库压力大，不同业务耦合

Q24：水平分表的路由策略有哪些？

记忆点：哈希取模（均匀但扩容难）、范围分片（易扩容但热点）、一致性哈希（扩容友好）。

策略优点缺点
哈希取模 id % N数据均匀分布扩容需要迁移大量数据
范围分片 id 1-100万 → 表1扩容简单，加新表即可热点问题（新数据集中在最新表）
一致性哈希扩容只需迁移少量数据实现复杂
查表法（路由表）灵活多一次查询开销

Q25：分库分表后会带来哪些问题？

记忆点：分布式事务、跨库 JOIN、全局唯一 ID、跨库排序分页、数据迁移。这些是面试高频追问点。

问题解决方案
分布式事务Seata（AT 模式）、TCC、本地消息表、最大努力通知
跨库 JOIN全局表（各库冗余一份小表）、应用层组装、宽表冗余
全局唯一 IDSnowflake 雪花算法、UUID、数据库号段模式（Leaf）
排序分页各分片取数据在应用层归并排序
数据迁移双写方案、影子表、数据对比校验

第八部分：MySQL 引擎与实战

Q26：InnoDB 和 MyISAM 的区别？

记忆点：InnoDB 支持事务、行锁、外键、MVCC，MyISAM 不支持。InnoDB 是 MySQL 5.5+ 的默认引擎。现在几乎所有场景都用 InnoDB。

特性InnoDBMyISAM
事务支持不支持
锁粒度行锁表锁
外键支持不支持
MVCC支持不支持
崩溃恢复支持（redo log）不支持
COUNT(*)遍历（慢）直接读计数器（快）
全文索引5.6+ 支持支持
存储结构聚簇索引（.ibd）非聚簇（.MYD + .MYI）

Q27：Buffer Pool 是什么？为什么重要？

记忆点：Buffer Pool 是 InnoDB 的内存缓存区，所有数据页的读写都先经过 Buffer Pool。命中率越高，磁盘 IO 越少，性能越好。通常设为物理内存的 60%-80%。

1
2
3
4
5
6
7
8
9
10

读数据流程：
  读请求 → Buffer Pool 中找（命中直接返回）
                     ↓ 未命中
              从磁盘加载到 Buffer Pool → 返回

写数据流程：
  写请求 → 修改 Buffer Pool 中的页（变成脏页）
            + 写 redo log
                     ↓
              后台线程异步将脏页刷盘

淘汰策略：改进的 LRU（分为 young 和 old 两个区域），避免全表扫描等一次性大量读取冲走热数据。

Q28：char 和 varchar 的区别？

记忆点：char 定长（浪费空间、无需存长度、比较快），varchar 变长（节省空间、需额外 1-2 字节存长度）。短固定长度用 char（如国家代码），其余用 varchar。

特性CHAR(N)VARCHAR(N)
存储方式固定 N 个字符，不足补空格实际长度 + 1-2 字节长度前缀
空间可能浪费节省
检索效率略高（定长，偏移计算简单）略低
适用场景固定长度（MD5、UUID、国家代码）长度不定的字符串

Q29：varchar(100) 和 varchar(200) 存同样的数据，有区别吗？

记忆点：实际存储空间相同，但 varchar(200) 在排序和临时表时会分配更多内存（按最大长度分配），应该”用多少定多少”。

此外，varchar 定义的是字符数而非字节数（在 utf8mb4 下一个字符最多 4 字节）。varchar(100) 最大存 100 个字符 = 最多 400 字节。

Q30：MySQL 中 NULL 值需要注意什么？

记忆点：NULL 参与运算结果都是 NULL，NULL 的比较必须用 IS NULL / IS NOT NULL，COUNT(列) 会跳过 NULL，NULL 值影响索引效率。尽量将列设为 NOT NULL DEFAULT ‘‘。

1
2
3
4
5
6
7
8
9
10
11

-- NULL 的坑
SELECT NULL = NULL;     -- 结果是 NULL（不是 true）
SELECT NULL <> NULL;    -- 结果是 NULL（不是 true）
SELECT NULL + 1;        -- 结果是 NULL

-- 正确判断 NULL
SELECT * FROM user WHERE name IS NULL;
SELECT * FROM user WHERE name IS NOT NULL;

-- IFNULL / COALESCE 处理 NULL
SELECT COALESCE(name, '未知') FROM user;

第九部分：Redis 基础（高频搭配考点）

Q31：Redis 为什么这么快？

记忆点：纯内存操作 + 单线程避免锁竞争 + IO 多路复用（epoll）+ 高效数据结构（跳表、压缩列表、SDS）。

原因说明
基于内存数据存在内存中，读写速度纳秒级
单线程模型避免上下文切换和锁竞争（6.0+ 网络 IO 多线程，命令执行仍单线程）
IO 多路复用epoll 监听多个 socket，单线程高效处理大量连接
高效数据结构SDS（预分配减少 realloc）、跳表（有序集合 O(logN) 操作）、压缩列表/listpack

Q32：Redis 的持久化方式？RDB 和 AOF 的区别？

记忆点：RDB 是某个时刻的全量快照（体积小、恢复快、可能丢数据），AOF 记录每条写命令（数据安全、文件大、恢复慢）。实际生产两者都开。

特性RDBAOF
原理fork 子进程生成数据快照追加记录每条写命令
文件大小小（二进制压缩）大（文本命令，需定期重写）
恢复速度快慢（回放所有命令）
数据安全性可能丢最后一次快照后的数据最多丢 1 秒（appendfsync everysec）
性能影响fork 时可能短暂阻塞持续写文件，影响较小

Redis 4.0+ 混合持久化：AOF 重写时将 RDB 快照放在 AOF 文件头部，后续增量用 AOF 格式追加。兼顾恢复速度和数据安全。

Q33：缓存穿透、缓存击穿、缓存雪崩的区别和解决方案？

记忆点：穿透（查不存在的数据）、击穿（热点 key 过期）、雪崩（大量 key 同时过期）。

问题定义解决方案
缓存穿透查询的数据缓存和数据库都不存在，每次请求都打到数据库布隆过滤器；缓存空值（设短过期时间）
缓存击穿某个热点 key 突然过期，大量并发请求同时打到数据库互斥锁（setnx）重建缓存；热点 key 永不过期 + 逻辑过期
缓存雪崩大量 key 同一时间过期 或 Redis 宕机，请求涌向数据库过期时间加随机值打散；多级缓存；限流降级；Redis 集群高可用

Q34：Redis 的过期策略和内存淘汰策略？

记忆点：过期策略 = 惰性删除 + 定期删除。内存淘汰策略在内存满时触发，默认 noeviction（不淘汰，写入报错），常用 allkeys-lru。

过期策略（如何删除已过期的 key）：

  • 惰性删除：访问 key 时检查是否过期，过期则删除
  • 定期删除：每隔一段时间随机抽取一批 key 检查并删除过期的

内存淘汰策略（内存满时如何腾空间）：

策略说明
noeviction不淘汰，写入报错（默认）
allkeys-lru从所有 key 中淘汰最久未使用的
allkeys-lfu从所有 key 中淘汰使用频率最低的（4.0+）
volatile-lru从设了过期时间的 key 中淘汰最久未使用的
volatile-ttl从设了过期时间的 key 中淘汰即将过期的
allkeys-random随机淘汰

Q35：如何保证数据库和缓存的一致性？

记忆点：先更新数据库，再删除缓存（Cache Aside 模式）。不推荐先删缓存再更新数据库（并发下会被旧数据回填）。延迟双删可进一步减少不一致窗口。

1
2
3
4
5
6
7
8
9
10
11

Cache Aside（旁路缓存）策略：
  读：先查缓存 → 命中返回 → 未命中查数据库 → 写入缓存 → 返回
  写：先更新数据库 → 再删除缓存

为什么是"删除"缓存而不是"更新"缓存？
  → 因为可能有多个写请求并发，先写的反而后更新缓存，导致缓存是旧值

延迟双删（进一步保证）：
  1. 先删缓存
  2. 更新数据库
  3. 延迟 N 毫秒后再删一次缓存（兜底，清除期间可能被旧数据回填的缓存）

面试高频追问

Q：MySQL 一条 SELECT 语句的执行流程？

1
2
3
4
5
6
7
8

客户端 → 连接器（认证、权限）
       → 查询缓存（8.0 已移除）
       → 解析器（词法分析、语法分析 → AST）
       → 预处理器（表/列是否存在、权限检查）
       → 优化器（选择索引、决定 JOIN 顺序、生成执行计划）
       → 执行器（调用存储引擎接口读数据）
       → 存储引擎（InnoDB：Buffer Pool → 磁盘）
       → 返回结果集

Q：MySQL 一条 UPDATE 语句的执行流程？

1
2
3
4
5
6
7

在 SELECT 的基础上额外：
1. 将旧数据写入 undo log（用于回滚和 MVCC）
2. 修改 Buffer Pool 中的数据页（变成脏页）
3. 写 redo log（prepare 状态）
4. 写 binlog
5. 提交事务：redo log 标记为 commit
6. 后台线程异步刷脏页到磁盘

Q：如何设计数据库表？有哪些范式？实际中怎么取舍？

三大范式：

  1. 1NF：列不可再分（原子性）
  2. 2NF：满足 1NF 且非主属性完全依赖主键（消除部分依赖）
  3. 3NF：满足 2NF 且非主属性不传递依赖主键（消除传递依赖）

实际中：为了查询性能会适当反范式化（冗余字段避免 JOIN），尤其在读多写少的场景。用空间换时间，但要注意数据一致性的维护。

数据库
数据库 MySQL 面试 索引 B+树 事务 MVCC 锁 主从复制 分库分表
本文由作者按照 CC BY 4.0 进行授权