以 MySQL InnoDB 为主线讲解，PostgreSQL 差异处会标注。
一、为什么需要索引？
无索引时，查找一条记录需要全表扫描（O(N)）。索引相当于书的目录，把查找复杂度降到 O(log N)。
代价：
- 占用磁盘空间（一个大表的索引可能数 GB）
- 写入变慢（每个索引都要维护）
- 维护成本（统计信息更新、碎片整理）
经验值：单表索引数量控制在 5 个以内，超过往往是表设计有问题。
二、索引数据结构选型
| 结构 | 查找 | 范围查询 | 适用 |
|---|---|---|---|
| 哈希表 | O(1) | ❌ 不支持 | Memory 引擎、Redis |
| 二叉搜索树 | O(log N) | ✅ | 内存场景 |
| B 树 | O(log N) | ✅ | 早期文件系统 |
| B+ 树 | O(log N) | ✅ 强 | MySQL InnoDB |
| LSM 树 | 写极快 | ✅ | RocksDB、HBase、TiDB |
为什么 MySQL 选 B+ 树？
- 磁盘 I/O 优化：B+ 树矮胖（一般 3-4 层就能存 2 千万行），每次查询只需 3-4 次磁盘 I/O
- 范围查询优秀：叶子节点用双向链表串起，区间扫描只需找到起点后顺序遍历
- 数据全在叶子节点：非叶子节点只存索引，单页能容纳更多 key，进一步降低树高
B+ 树示意（每个节点对应一个 16KB 数据页）：
[50, 100] ← 根节点
/ | \
[10,30] [60,80] [120,150] ← 中间节点
/ | \ / | \ / | \
[叶][叶][叶]... ← 叶子节点（双向链表，存实际数据）
三、聚簇索引 vs 二级索引
InnoDB 的核心特性：表数据本身就是按主键聚簇存储的。
3.1 聚簇索引（Clustered Index）
- 主键索引的叶子节点直接存放整行数据
- 一张表只能有一个聚簇索引（主键）
- 没显式定义主键时，InnoDB 用第一个 NOT NULL 唯一索引；都没有就生成隐藏的
ROW_ID
3.2 二级索引（Secondary Index / 辅助索引）
- 叶子节点存的是主键值，不是行数据
- 通过二级索引查询完整数据需要回表：先查二级索引拿主键 → 再查聚簇索引拿行
-- 假设 idx_name(name)
SELECT * FROM users WHERE name = 'Alice';
-- 步骤：
-- 1. 在 idx_name 中找到 name='Alice' → 得到 pk=42
-- 2. 在聚簇索引中找到 pk=42 → 拿到完整行
3.3 覆盖索引（Covering Index）：避免回表
-- 二级索引 idx_name_age(name, age)
SELECT name, age FROM users WHERE name = 'Alice';
-- ✅ 索引中已经有 name 和 age，无需回表
-- EXPLAIN 显示 Extra: Using index
优化技巧：把高频查询字段塞进二级索引，让它"覆盖"查询。
3.4 主键设计的金科玉律
- 必须递增（自增 ID 或雪花 ID 排序）：避免页分裂
- 必须短：每个二级索引都会存主键，短主键节省空间
- 避免 UUID v4：随机分布导致页分裂频繁，主键 36 字节 vs BIGINT 8 字节
四、最左前缀原则
联合索引 idx(a, b, c) 等同于建立了：(a)、(a,b)、(a,b,c) 三个索引。
| 查询条件 | 是否用索引 |
|---|---|
WHERE a = 1 | ✅ |
WHERE a = 1 AND b = 2 | ✅ |
WHERE a = 1 AND c = 3 | ✅ a 用索引，c 不用 |
WHERE b = 2 | ❌ |
WHERE a = 1 AND b > 2 AND c = 3 | ✅ a, b 用索引，c 不用（范围后失效） |
WHERE a = 1 ORDER BY b | ✅ 排序也走索引 |
核心规则：
- 必须从最左字段开始匹配
- 范围查询会让后面的字段失效（
>、<、BETWEEN、LIKE 'xx%'） =和IN不影响后续字段使用索引（优化器会自动调整顺序）
联合索引顺序设计
-- ❌ 错误：区分度低的在前
KEY idx(gender, age, name)
-- ✅ 正确：区分度高、等值查询的在前；范围查询放最后
KEY idx(name, age, gender)
五、索引失效的常见场景
-- 1. 函数 / 表达式（PostgreSQL 可建表达式索引）
WHERE YEAR(created_at) = 2026 -- ❌
WHERE created_at >= '2026-01-01' -- ✅
-- 2. 隐式类型转换
WHERE phone = 13800138000 -- ❌ phone 是 varchar
WHERE phone = '13800138000' -- ✅
-- 3. LIKE 左模糊
WHERE name LIKE '%lice' -- ❌
WHERE name LIKE 'Ali%' -- ✅
-- 4. OR 条件中有非索引列
WHERE name = 'Alice' OR age = 20 -- ❌（age 无索引时）
-- 改用 UNION
-- 5. NOT IN / != 通常失效
WHERE status != 1 -- ❌
-- 6. 优化器认为全表扫描更快（小表、低区分度）
六、EXPLAIN 实战
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 1;
| 字段 | 含义 | 关注点 |
|---|---|---|
type | 访问类型 | 重点：const > ref > range > index > ALL，要避免 ALL |
key | 实际用的索引 | NULL 说明没用索引 |
rows | 预估扫描行数 | 越小越好 |
Extra | 额外信息 | Using index（覆盖索引）✅；Using filesort、Using temporary ❌ |
type 简记：
const：主键 / 唯一索引等值ref：普通索引等值range：范围扫描index：扫描整个索引树ALL：全表扫描
七、事务与 ACID
| 特性 | 含义 | InnoDB 实现 |
|---|---|---|
| A Atomicity 原子性 | 全部成功或全部失败 | undo log（用于回滚） |
| C Consistency 一致性 | 事务前后数据合法 | 由 A、I、D 共同保障 |
| I Isolation 隔离性 | 并发事务互不干扰 | 锁 + MVCC |
| D Durability 持久性 | 提交后永久生效 | redo log（崩溃恢复） |
7.1 redo log vs undo log vs binlog
| 日志 | 层次 | 作用 |
|---|---|---|
| redo log | InnoDB 引擎 | 崩溃恢复，物理日志（页修改） |
| undo log | InnoDB 引擎 | 事务回滚 + MVCC 历史版本 |
| binlog | Server 层 | 主从复制、数据恢复，逻辑日志 |
提交流程（两阶段提交保证 redo 和 binlog 一致）：
1. 写 redo log（prepare 状态）
2. 写 binlog
3. 写 redo log（commit 状态）
八、隔离级别与并发问题
8.1 四种隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| Read Uncommitted | ✗ | ✗ | ✗ |
| Read Committed (PG 默认) | ✓ | ✗ | ✗ |
| Repeatable Read (MySQL 默认) | ✓ | ✓ | ✓（InnoDB 用间隙锁解决） |
| Serializable | ✓ | ✓ | ✓ |
✓ 表示防止该问题，✗ 表示存在该问题
8.2 三种异常的实例
-- 脏读：T1 读到 T2 未提交的修改
T2: UPDATE account SET balance = 200 WHERE id = 1; -- 未提交
T1: SELECT balance FROM account WHERE id = 1; -- 读到 200
T2: ROLLBACK; -- T1 看到的是脏数据
-- 不可重复读：同一事务内两次读同一行结果不同（聚焦更新/删除）
T1: SELECT balance FROM account WHERE id = 1; -- 100
T2: UPDATE ... ; COMMIT;
T1: SELECT balance FROM account WHERE id = 1; -- 200 ❌
-- 幻读：同一事务内两次范围查询行数不同（聚焦插入）
T1: SELECT * FROM orders WHERE amount > 100; -- 5 行
T2: INSERT INTO orders VALUES (..., 200); COMMIT;
T1: SELECT * FROM orders WHERE amount > 100; -- 6 行 ❌
九、MVCC 原理
Multi-Version Concurrency Control：通过保存数据的多个版本，让读写不互相阻塞。
9.1 隐藏字段
InnoDB 给每行加 3 个隐藏字段：
DB_TRX_ID：最近修改的事务 IDDB_ROLL_PTR：回滚指针，指向 undo log 中的上一版本DB_ROW_ID：隐藏自增 ID（无主键时使用）
9.2 版本链
当前行: id=1, balance=300, trx_id=103
↓ roll_ptr
undo: id=1, balance=200, trx_id=102
↓ roll_ptr
undo: id=1, balance=100, trx_id=101
9.3 ReadView：决定能看到哪个版本
事务发起 SELECT 时，生成 ReadView，包含：
creator_trx_id：当前事务 IDm_ids：当前活跃（未提交）事务列表min_trx_id/max_trx_id：活跃事务的最小/最大 ID
判断行版本可见性：
trx_id < min_trx_id：版本来自已提交事务，可见trx_id >= max_trx_id：版本来自更晚的事务，不可见- 在
m_ids中：未提交，不可见 - 否则：已提交，可见
9.4 RC vs RR 的核心差异
- Read Committed：每次 SELECT 生成新 ReadView → 看到最新已提交数据
- Repeatable Read：事务首次 SELECT 时生成 ReadView，后续复用 → 整个事务看到一致快照
9.5 快照读 vs 当前读
-- 快照读（普通 SELECT）：走 MVCC
SELECT * FROM users WHERE id = 1;
-- 当前读（加锁的 SELECT、所有写操作）：读最新版本 + 加锁
SELECT * FROM users WHERE id = 1 FOR UPDATE;
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;
UPDATE users SET ...;
DELETE FROM users WHERE ...;
陷阱：RR 隔离级别下，快照读不会幻读，但当前读会幻读。InnoDB 用间隙锁解决当前读的幻读。
十、锁机制
10.1 锁的粒度
| 锁 | 粒度 | 性能 | 引擎 |
|---|---|---|---|
| 表锁 | 整张表 | 高并发差 | MyISAM、特殊场景 |
| 行锁 | 单行 | 高并发好 | InnoDB |
| 页锁 | 一页 | 中 | BDB（已淘汰） |
10.2 行锁的三种实现
-- 假设有索引 idx_age，表中数据 age = [10, 20, 30]
Record Lock（记录锁）：锁定单条记录
SELECT * FROM users WHERE age = 20 FOR UPDATE; -- 锁住 age=20 的行
Gap Lock（间隙锁）：锁定记录之间的空隙
SELECT * FROM users WHERE age = 25 FOR UPDATE;
-- 锁住 (20, 30) 这个开区间，防止插入 25
Next-Key Lock（临键锁）= Record + Gap：左开右闭区间
-- RR 隔离级别下默认
SELECT * FROM users WHERE age > 15 AND age < 25 FOR UPDATE;
-- 锁住 (10, 20], (20, 30) 等区间
10.3 行锁失效 → 退化为表锁
当查询条件没用索引时，行锁失效退化为表锁（其实是锁住所有行）：
-- name 没有索引
UPDATE users SET ... WHERE name = 'Alice'; -- 锁全表
工程教训：写操作的 WHERE 条件必须命中索引。
10.4 死锁
-- 经典交叉死锁
T1: UPDATE account SET balance = 100 WHERE id = 1; -- 持有 id=1 锁
T2: UPDATE account SET balance = 200 WHERE id = 2; -- 持有 id=2 锁
T1: UPDATE account SET balance = 100 WHERE id = 2; -- 等待 id=2
T2: UPDATE account SET balance = 200 WHERE id = 1; -- 等待 id=1，死锁
InnoDB 自动检测死锁并回滚代价小的事务（Deadlock found 错误）。
避免死锁的工程实践：
- 多行操作时，按相同顺序加锁（如统一按 id 升序）
- 大事务拆小，减少持锁时间
- 设置合理的锁超时：
innodb_lock_wait_timeout（默认 50s） - 排查工具：
SHOW ENGINE INNODB STATUS看 LATEST DETECTED DEADLOCK
十一、实战案例
11.1 慢查询排查 SOP
-- 1. 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- 超过 1 秒记录
-- 2. 用 mysqldumpslow 或 pt-query-digest 分析
-- 3. EXPLAIN 看执行计划
EXPLAIN SELECT ...;
-- 4. 看是否走索引、是否回表过多
11.2 经典误区：分页深度查询
-- ❌ 慢：偏移越大越慢，需要扫描 100020 行
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;
-- ✅ 快：用主键游标
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;
-- ✅ 子查询优化（无法记录上次 id 时）
SELECT * FROM orders
WHERE id >= (SELECT id FROM orders ORDER BY id LIMIT 100000, 1)
LIMIT 20;
11.3 唯一索引并发插入
-- 防重复下单：靠 UNIQUE 约束 + 捕获 DuplicateKeyException
CREATE UNIQUE INDEX uk_order_no ON orders(order_no);
INSERT INTO orders(order_no, ...) VALUES('20260430001', ...);
-- 第二次插入抛 1062 错误，业务捕获后视为幂等成功
比"先查后插"靠谱：先查后插在并发下仍会重复。
11.4 大事务的危害
-- ❌ 危险：长事务持锁数小时
START TRANSACTION;
UPDATE big_table SET status = 1; -- 锁数百万行
-- 业务处理 30 分钟...
COMMIT;
后果：
- 阻塞其他事务
- undo log 无法清理 → 表空间膨胀
- MVCC 历史版本积累 → 查询变慢
- 主从复制延迟
原则：事务能短则短，IO 操作（HTTP 调用、消息发送）放事务外。
11.5 索引下推（ICP，5.6+）
-- 联合索引 idx(name, age)
SELECT * FROM users WHERE name LIKE 'A%' AND age = 20;
-- 旧版：拿到 name 匹配的所有行 → 回表 → Server 层过滤 age
-- ICP：在存储引擎层就过滤掉 age != 20 的，减少回表次数
-- EXPLAIN 显示 Extra: Using index condition
十二、PostgreSQL 主要差异点
| 特性 | MySQL InnoDB | PostgreSQL |
|---|---|---|
| 默认隔离级别 | RR | RC |
| 表组织 | 聚簇（按主键） | 堆表（HOT 更新优化） |
| MVCC | undo log + 版本链 | 行内多版本（VACUUM 清理） |
| 索引类型 | B+ 树为主 | B-tree、Hash、GIN、GiST、BRIN 等 |
| 表达式索引 | 8.0+ 支持 | 原生强大 |
| 部分索引 | ❌ | ✅ WHERE status = 'active' |
十三、避坑清单
- 主键用自增 BIGINT 或雪花 ID，不用 UUID v4
- 每张表必有主键，否则隐式 ROW_ID 不利于复制和性能
- 索引数量 ≤ 5，写多的表更少
- 联合索引顺序：等值 → 范围
- 避免 SELECT *：能走覆盖索引就走
- WHERE 条件不要用函数包字段
- 大事务拆小，事务内不做 IO
- 写操作 WHERE 必走索引，否则锁全表
- 死锁场景统一加锁顺序
- 慢查询日志要开，定期 review
- DDL 操作（加字段、改索引）走 pt-online-schema-change，避免锁表
- InnoDB 统计信息可能不准，必要时
ANALYZE TABLE刷新
十四、面试高频题速记
- Q：为什么 B+ 树而非 B 树？ A：B+ 树更矮（数据全在叶子，非叶子能放更多 key）、范围查询更快（叶子链表）
- Q：聚簇索引和非聚簇索引区别？ A：聚簇叶子存行数据，非聚簇叶子存主键
- Q：什么是回表？怎么避免？ A：二级索引查到主键后再查聚簇索引；用覆盖索引避免
- Q：联合索引最左前缀什么意思？ A：必须从最左字段开始匹配，范围查询后字段失效
- Q：InnoDB 怎么解决幻读？ A：快照读靠 MVCC，当前读靠 Next-Key Lock（间隙锁）
- Q：MVCC 实现原理？ A：隐藏字段 + undo log 版本链 + ReadView 可见性判断
- Q：RC 和 RR 在 MVCC 上的差异？ A：RC 每次 SELECT 生成 ReadView，RR 事务首次生成后复用
- Q：什么时候表锁？ A：写操作未走索引，或 ALTER TABLE 等 DDL
- Q：redo log 和 binlog 区别？ A：redo 是引擎层物理日志（崩溃恢复），binlog 是 Server 层逻辑日志（复制）
- Q：为什么用两阶段提交？ A：保证 redo log 和 binlog 的一致性，避免主从数据不一致
