数据库

一、为什么需要理解数据库？

数据库不是简单的 CRUD 工具。索引设计、事务隔离级别、SQL 优化直接影响系统性能。理解数据库原理，才能写出高效 SQL，避免慢查询拖垮应用。

二、索引

2.1 为什么索引能加速查询？

原理： 索引是一种排序的数据结构（通常是 B+Tree），将无序的数据变成有序的查找结构。没有索引时需要全表扫描（O(n)），有索引时通过 B+Tree 查找（O(log n)）。

B+Tree 为什么适合数据库索引？

            [30 | 70]
           /    |    \
    [10|20]  [40|50|60]  [80|90|100]
     /  |  \    ...         ...
    ↓   ↓   ↓
   数据  数据  数据（叶子节点用双向链表连接，支持范围查询）

• 非叶子节点只存 key，不存数据 → 一个节点能存更多 key → 树更矮 → 磁盘 IO 更少
• 叶子节点形成有序双向链表 → 范围查询只需找到起点，沿链表遍历
• 每个节点占一个磁盘页（16KB），一次 IO 读一个页

2.2 索引类型

索引类型	数据结构	特点	适用场景
聚簇索引（主键索引）	B+Tree	叶子节点存整行数据，一个表只有一个	主键查询
非聚簇索引（二级索引）	B+Tree	叶子节点存主键值，需要回表	非主键查询
联合索引	B+Tree	多列组合，遵循最左前缀原则	多条件查询
覆盖索引	概念	查询列都在索引中，无需回表	高频查询
哈希索引	Hash	O(1) 等值查询，不支持范围	Memory 引擎

2.3 回表与覆盖索引

回表： 二级索引查到主键后，再到聚簇索引查完整行数据。多一次磁盘 IO。

-- 假设 idx_name 是 name 列的二级索引，主键是 id
SELECT id, name FROM users WHERE name = '张三';  -- 覆盖索引，不回表
SELECT * FROM users WHERE name = '张三';          -- 需要回表查完整行

如何避免回表？ 高频查询只 SELECT 需要的列，建联合索引把这些列都包含进去。

2.4 最左前缀原则

联合索引 (a, b, c) 相当于创建了 (a)、(a,b)、(a,b,c) 三个索引。查询条件必须从最左列开始匹配：

-- 索引 (a, b, c)
WHERE a = 1 AND b = 2 AND c = 3  -- 全匹配 ✅
WHERE a = 1 AND b = 2            -- 匹配 a, b ✅
WHERE a = 1 AND c = 3            -- 只匹配 a，c 跳过 b 无法匹配 ❌
WHERE b = 2 AND c = 3            -- 不以 a 开头 ❌
WHERE a = 1 AND b > 2 AND c = 3  -- 只匹配 a, b，c 是范围后失效 ❌

为什么有最左前缀？ B+Tree 按 (a, b, c) 的顺序排序。a 先排序，a 相同再排 b，b 相同再排 c。跳过 a 就无法利用索引的有序性。

2.5 索引失效场景

-- 1. 函数/运算/类型转换
SELECT * FROM users WHERE YEAR(created_at) = 2024;  -- 失效
SELECT * FROM users WHERE created_at >= '2024-01-01';  -- 正确

-- 2. 前导模糊
SELECT * FROM users WHERE name LIKE '%张三';  -- 失效
SELECT * FROM users WHERE name LIKE '张三%';  -- 有效

-- 3. 不等于
SELECT * FROM users WHERE status != 1;  -- 通常失效

-- 4. OR 连接非索引列
SELECT * FROM users WHERE id = 1 OR name = '张三';  -- name 无索引 → 失效

-- 5. 隐式类型转换
SELECT * FROM users WHERE phone = 13800138000;  -- phone 是 varchar → 失效
SELECT * FROM users WHERE phone = '13800138000';  -- 正确

三、事务

3.1 ACID

特性	含义	实现方式
原子性	要么全做，要么全不做	Undo Log（回滚日志）
一致性	事务前后数据满足约束	其他三个特性保证
隔离性	并发事务互不干扰	MVCC + 锁
持久性	提交后数据不丢失	Redo Log（重做日志）

3.2 隔离级别

级别	脏读	不可重复读	幻读	实现方式
READ UNCOMMITTED	是	是	是	无锁
READ COMMITTED	否	是	是	每次快照读生成新 ReadView
REPEATABLE READ（默认）	否	否	部分（间隙锁防）	事务开始时生成 ReadView
SERIALIZABLE	否	否	否	读加共享锁

RR 隔离级别下如何解决幻读？ InnoDB 通过 Next-Key Lock（行锁 + 间隙锁）防止其他事务在间隙中插入数据。

3.3 MVCC 原理

为什么需要 MVCC？ 读写互不阻塞——读不阻塞写，写不阻塞读。通过版本链实现，每行数据有隐藏列 DB_TRX_ID（最近修改的事务 ID）和 DB_ROLL_PTR（回滚指针）。

ReadView（快照读）： SELECT 时创建一个 ReadView，包含当前活跃事务列表。通过 DB_TRX_ID 判断数据版本是否可见，找到可见版本返回。

• RC（读已提交）： 每次 SELECT 生成新 ReadView
• RR（可重复读）： 事务中第一次 SELECT 生成 ReadView，后续复用

注意： UPDATE、DELETE、SELECT ... FOR UPDATE 是当前读（读最新版本），不走 MVCC 快照。

四、SQL 优化

4.1 EXPLAIN 关键字段

EXPLAIN SELECT * FROM orders WHERE user_id = 1;

字段	含义	关注值
type	访问类型	ALL（全表）< index < range < ref < eq_ref < const（最优）
key	实际使用的索引	NULL 表示没走索引
rows	预估扫描行数	越小越好
Extra	额外信息	Using filesort（文件排序，差）、Using temporary（临时表，差）、Using index（覆盖索引，好）

4.2 常见优化策略

-- 1. SELECT 只查需要的列，不用 SELECT *
SELECT id, name FROM users WHERE status = 1;

-- 2. 分页优化：大偏移量用子查询
SELECT * FROM orders WHERE id >= (
    SELECT id FROM orders ORDER BY id LIMIT 100000, 1
) ORDER BY id LIMIT 20;

-- 3. JOIN 代替子查询
SELECT * FROM orders o JOIN users u ON o.user_id = u.id WHERE u.name = '张三';

-- 4. IN 数量控制（< 1000）
SELECT * FROM orders WHERE id IN (1, 2, 3, ..., 1000);

-- 5. 批量插入代替逐条插入
INSERT INTO users (name, email) VALUES ('a', '[email protected]'), ('b', '[email protected]'), ...;

五、锁机制

锁类型	粒度	特点
表锁	整张表	开销小，并发低
行锁	单行	开销大，并发高
间隙锁	索引间隙	防止幻读
Next-Key Lock	行 + 间隙	InnoDB 默认，防幻读

死锁场景： 事务A锁住行1等行2，事务B锁住行2等行1 → 互相等待 → 死锁。InnoDB 自动检测死锁，回滚代价小的事务。

六、面试要点

Q：B+Tree 为什么适合数据库索引？

多路平衡，树矮（3-4 层存千万数据），磁盘 IO 少；叶子节点有序链表，范围查询高效；非叶子节点只存 key，一个节点能存更多 key。

Q：联合索引的最左前缀原则？

联合索引 (a,b,c) 按 a→b→c 排序，查询必须从 a 开始才能用索引。跳过 a 或用范围查询后续列会失效。

Q：MVCC 的实现原理？

通过隐藏列 DB_TRX_ID 和 DB_ROLL_PTR 维护版本链，ReadView 判断版本可见性。RC 每次读生成新 ReadView，RR 复用第一次的 ReadView，实现可重复读。

Q：如何排查慢查询？

EXPLAIN 分析执行计划，看 type（ALL 全表扫描）、key（是否走索引）、rows（扫描行数）、Extra（Using filesort/temporary）。开启慢查询日志定位具体 SQL，再针对性优化索引。