📖 本篇简介：索引与事务是 MySQL 面试的两大核心，也是日常开发最常用的两块知识。本篇以表格化形式汇总索引类型、索引失效场景、聚簇与二级索引、隔离级别、ACID 实现、redo/undo/binlog 对比、EXPLAIN 字段等高频知识点。所有示例基于 MySQL 8.0 语法。读完你能把这张速查卡打印出来，面试前、上线前、Code Review 前翻一翻。
本篇适合已经系统学过索引与事务原理、需要一份考前/上线前速查材料的工程师。建议配合第 1、2、3、4 章一起使用，那里有详细的原理讲解。
速查卡的价值不在于"学会新东西"，而在于"快速回忆起已经学过的东西"。面试前 10 分钟翻一遍，比从头看文档高效得多。
目录
- 索引类型速查表
- 聚簇索引与二级索引对比
- 索引生效与失效场景速查
- 事务隔离级别对比表
- ACID 实现机制速查
- redo log / undo log / binlog 对比
- EXPLAIN 关键字段速查
- 索引设计常用命令
- B+ 树索引原理速记
- 索引设计原则速记
- 常见索引误区澄清
- MVCC 原理速记
- 事务常见问题速查
- 索引与事务面试高频问答
- 索引维护常用命令
- 事务相关参数速查
- 自测：索引与事务速查
索引类型速查表
| 索引类型 | 说明 | 适用场景 | 注意事项 |
|---|---|---|---|
| B+ 树索引 | InnoDB 默认索引，多路平衡查找树 | 等值查询、范围查询、排序 | 最左前缀匹配 |
| 哈希索引 | Memory 引擎默认，自适应哈希索引 | 等值查询 | 不支持范围、不支持排序 |
| 全文索引 | FULLTEXT，倒排索引 | 文本搜索 | 中文需 ngram 分词器 |
| 空间索引 | SPATIAL，GIS 地理数据 | 经纬度查询 | 需 NOT NULL |
| 主键索引 | 聚簇索引，数据按主键组织 | 所有 InnoDB 表 | 一张表只有一个 |
| 唯一索引 | UNIQUE，列值唯一 | 防重复业务字段 | 可空 |
| 普通索引 | INDEX，加速查询 | 高频 WHERE 列 | 最左前缀 |
| 联合索引 | INDEX(a,b,c) | 多列组合查询 | 最左前缀原则 |
| 覆盖索引 | 索引包含查询所需所有列 | 避免回表 | Extra=Using index |
| 函数索引 | MySQL 8.0 支持，索引表达式 | WHERE UPPER(name)='ABC' | 8.0+ 才有 |
| 降序索引 | DESC 索引 | ORDER BY x DESC | 8.0+ 真正降序 |
| 不可见索引 | INVISIBLE | 灰度切换索引 | 不影响优化器 |
| 跳跃索引 | Skip Scan，8.0 优化 | 联合索引中间列查询 | 8.0+ 自动 |
-- 创建各类索引示例（本示例基于 MySQL 8.0）
CREATE TABLE user (
id BIGINT PRIMARY KEY, -- 主键索引
phone VARCHAR(32) UNIQUE, -- 唯一索引
name VARCHAR(64),
city VARCHAR(32),
age INT,
INDEX idx_city_age (city, age), -- 联合索引
FULLTEXT INDEX idx_name_ft (name) WITH PARSER ngram
);
聚簇索引与二级索引对比
| 对比项 | 聚簇索引 | 二级索引（非聚簇） |
|---|---|---|
| 叶子节点存什么 | 整行数据 | 索引列 + 主键值 |
| 一张表有几个 | 只有 1 个（主键） | 可以有多个 |
| 查询方式 | 直接定位数据 | 先查二级索引拿主键，再回表查数据 |
| 插入性能 | 顺序插入快，随机插入页分裂 | 相对独立，影响小 |
| 空间占用 | 数据本身 | 额外存储 |
| 回表 | 不需要 | 需要（除非覆盖索引） |
关键结论：InnoDB 表必须有主键（没有就找第一个非空唯一索引，再没有就生成隐藏的 6 字节 ROWID）。二级索引叶子节点存的是主键值，所以主键越短，二级索引空间越小。
为什么主键要短：每个二级索引叶子节点都存主键值，主键 8 字节和 36 字节，二级索引空间差 4 倍。所以用 BIGINT 自增比 UUID 字符串省空间。
索引生效与失效场景速查
| 场景 | 是否走索引 | 原因 |
|---|---|---|
| WHERE a = 1 | 走 | 等值匹配最左前缀 |
| WHERE a = 1 AND b = 2 | 走 (a,b) | 联合索引全匹配 |
| WHERE b = 2 | 不走 | 违反最左前缀 |
| WHERE a = 1 AND c = 3（索引 a,b,c） | 走 a | 中间断了，b 用不上 |
| WHERE a > 1 AND b = 2 | 走 a | 范围查询后 b 失效 |
| WHERE LEFT(a,3) = 'abc' | 不走 | 函数包裹列 |
| WHERE a + 1 = 2 | 不走 | 表达式包裹列 |
| WHERE a LIKE 'abc%' | 走 | 前缀匹配 |
| WHERE a LIKE '%abc' | 不走 | 左前缀模糊 |
| WHERE a = 1 OR b = 2 | 可能不走 | OR 两边都要有索引 |
| WHERE a = 1 AND b = '2'（b 是数字） | 不走 b | 隐式类型转换 |
| SELECT * FROM t WHERE a = 1 | 走 a | 走索引后回表 |
| WHERE a IS NULL | 走 | B+ 树存 NULL |
| WHERE a IN (1,2,3) | 走 | IN 是多值等值 |
| WHERE a BETWEEN 1 AND 10 | 走 | 范围查询 |
| ORDER BY a | 走 | 索引有序 |
| WHERE a = 1 OR a = 2 | 走 | 同一列 OR |
-- 索引失效典型写法（本示例基于 MySQL 8.0）
-- 失效：函数包裹列
SELECT * FROM orders WHERE DATE(created_at) = '2026-10-01';
-- 正确：范围查询
SELECT * FROM orders
WHERE created_at >= '2026-10-01 00:00:00'
AND created_at < '2026-10-02 00:00:00';
事务隔离级别对比表
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 性能 | 适用场景 |
|---|---|---|---|---|---|
| READ UNCOMMITTED | 会 | 会 | 会 | 最高 | 几乎不用 |
| READ COMMITTED | 不会 | 会 | 会 | 高 | 大多数互联网业务 |
| REPEATABLE READ（默认） | 不会 | 不会 | 不会* | 中 | InnoDB 默认 |
| SERIALIZABLE | 不会 | 不会 | 不会 | 最低 | 强一致场景 |
\* InnoDB 在 RR 级别通过 next-key lock 解决了幻读，这是 InnoDB 对标准 RR 的增强。
并发问题定义：
| 问题 | 定义 |
|---|---|
| 脏读 | 读到其他事务未提交的数据 |
| 不可重复读 | 同一事务两次读同一行，结果不同（其他事务 UPDATE 并提交了） |
| 幻读 | 同一事务两次范围查询，结果集行数不同（其他事务 INSERT 并提交了） |
-- 查看与设置隔离级别（本示例基于 MySQL 8.0）
SELECT @@transaction_isolation;
SET GLOBAL transaction_isolation = 'READ-COMMITTED';
-- 生产建议：一般业务用 RC，金融强一致用 RR
ACID 实现机制速查
| 特性 | 含义 | 实现机制 |
|---|---|---|
| 原子性 Atomicity | 事务要么全成功要么全回滚 | undo log（回滚日志） |
| 一致性 Consistency | 事务前后数据一致 | 由 AID + 业务约束共同保证 |
| 隔离性 Isolation | 并发事务互不干扰 | 锁 + MVCC |
| 持久性 Durability | 提交后数据不丢 | redo log（重做日志）+ 刷盘 |
一句话记忆：undo 保原子，redo 保持久，锁 + MVCC 保隔离，一致性是最终目标。
一致性怎么来：数据库层（约束、触发器、事务）+ 业务层（代码逻辑）共同保证。数据库保证不了业务一致性，比如转账 A 扣钱 B 加钱，得靠事务包起来。
redo log / undo log / binlog 对比
| 对比项 | redo log | undo log | binlog |
|---|---|---|---|
| 所属层 | InnoDB 引擎层 | InnoDB 引擎层 | MySQL Server 层 |
| 内容 | 物理日志（页修改） | 逻辑日志（反向操作） | 逻辑日志（SQL/行变更） |
| 作用 | 崩溃恢复，保持久性 | 事务回滚，保原子性 | 主从复制、PITR |
| 写入方式 | 循环写，固定大小 | 随事务生成 | 追加写 |
| 是否可删除 | 覆盖写，自动循环 | 事务提交后 purge | 按保留期清理 |
| 格式 | 物理页 | 逻辑反向 | STATEMENT/ROW/MIXED |
| 写入时机 | 事务执行时 | 事务执行时 | 事务提交时 |
| 是否必选 | 是 | 是 | 主从复制必须 |
| 刷盘策略 | redo log buffer 刷盘 | undo 随事务 | binlog 刷盘 |
| 崩溃恢复 | 前滚 redo | 回滚未提交事务 | 用于 PITR |
两阶段提交流程：
1. 写 redo log，状态 prepare
2. 写 binlog
3. 写 redo log commit
4. 崩溃恢复：prepare 状态的事务看 binlog 有没有，有就提交，没有就回滚
-- 两阶段提交（redo prepare -> binlog write -> redo commit）
-- 保证 redo 和 binlog 数据一致
-- 崩溃恢复时：redo prepare 状态的事务，看 binlog 是否完整
-- 完整就提交，不完整就回滚
EXPLAIN 关键字段速查
| 字段 | 含义 | 关注点 |
|---|---|---|
| id | 查询序号 | id 越大越先执行 |
| select_type | 查询类型 | SIMPLE/PRIMARY/SUBQUERY 等 |
| table | 表名 | 哪张表 |
| type | 访问类型 | 从好到差：const > eq_ref > ref > range > index > ALL |
| possible_keys | 可能用到的索引 | |
| key | 实际用到的索引 | NULL 就是没走索引 |
| key_len | 索引使用长度 | 判断联合索引用了几列 |
| ref | 索引比较来源 | const/func/列名 |
| rows | 预估扫描行数 | 越大越要优化 |
| filtered | 过滤后百分比 | |
| Extra | 额外信息 | Using index（覆盖索引）、Using filesort、Using temporary |
-- EXPLAIN 示例（本示例基于 MySQL 8.0）
EXPLAIN SELECT * FROM orders WHERE user_id = 10086;
-- type=ref, key=idx_user, rows=10, Extra=NULL
-- 这是好的执行计划
type 列优先级：system > const > eq_ref > ref > range > index > ALL。看到 ALL 就要优化，看到 index 也要警惕。
Extra 列常见值：
| Extra 值 | 含义 | 是否好 |
|---|---|---|
| Using index | 覆盖索引，不用回表 | 好 |
| Using where | 服务器层过滤 | 一般 |
| Using filesort | 额外排序，要优化 | 差 |
| Using temporary | 临时表，要优化 | 差 |
| Using index condition | 索引下推 ICP | 好 |
| Select tables optimized away | 直接优化掉 | 极好 |
-- 8.0 EXPLAIN ANALYZE（实际执行计划）
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 10086;
索引设计常用命令
-- 查看表上的索引（本示例基于 MySQL 8.0）
SHOW INDEX FROM orders;
-- 添加索引
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);
-- 删除索引
ALTER TABLE orders DROP INDEX idx_user_status;
-- 查看索引使用情况
SELECT * FROM sys.schema_index_statistics
WHERE table_schema = 'app' AND table_name = 'orders';
-- 查看没用过的索引
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'app';
B+ 树索引原理速记
| 特性 | 说明 |
|---|---|
| 结构 | 多路平衡查找树，非叶子节点只存键，不存数据 |
| 叶子节点 | 存所有数据（聚簇）或主键（二级），双向链表 |
| 高度 | 3~4 层，千万级数据一次查询 3~4 次 IO |
| 范围查询 | 叶子节点链表，找到起点后顺序扫描 |
| 为什么不用哈希 | 哈希不支持范围、不支持排序 |
| 为什么不用二叉树 | 二叉树太高，磁盘 IO 次数多 |
为什么 B+ 树矮胖：每个节点是一个数据页（16KB），能存几百个键，所以树高很低。千万级数据 3 层就够了，每次查询 3 次磁盘 IO。
为什么不用红黑树：红黑树太高，千万级数据可能 20+ 层，每次查询 20+ 次磁盘 IO，慢死。
为什么叶子节点要链表：范围查询时找到起点后，顺着链表扫就行，不用回到根节点再找下一个。
索引设计原则速记
| 原则 | 说明 | 反例 |
|---|---|---|
| 选择性高的列放前面 | 区分度高的列放联合索引最左 | (gender, user_id) 不如 (user_id, gender) |
| 最左前缀 | 查询条件要从索引最左列开始 | WHERE b=2 用不上索引(a,b) |
| 覆盖索引 | 查询列都在索引里，避免回表 | SELECT * 很难覆盖 |
| 索引不是越多越好 | 每个索引都拖慢写入 | 一张表 20 个索引 |
| 前缀索引 | 长字符串列用前 N 个字符 | VARCHAR(255) 全列索引 |
| 避免索引膨胀 | 索引列越短越好 | 用 VARCHAR(1000) 做索引 |
| 排序分组用索引 | ORDER BY/GROUP BY 字段建索引 | 避免 Using filesort |
| 少 SELECT * | 只查需要的列，便于覆盖索引 | SELECT * |
| 区分度判断 | COUNT(DISTINCT col)/COUNT(*) > 0.1 才值得 | gender 列 |
-- 前缀索引示例（本示例基于 MySQL 8.0）
ALTER TABLE article ADD INDEX idx_title_prefix (title(20));
-- 只索引 title 前 20 个字符，节省空间
常见索引误区澄清
| 误区 | 正解 |
|---|---|
| 索引越多查询越快 | 索引拖慢写入，占用空间，优化器选错索引 |
| 全表扫描一定慢 | 小表全表扫比走索引还快（不用回表） |
| 索引建了就一定走 | 优化器可能选错，统计信息过期会走错 |
| 联合索引顺序无所谓 | 顺序决定最左前缀是否生效 |
| 主键一定要自增 | UUID 也可以，但趋势递增更好 |
| 加了索引查询就一定变快 | 小表全表扫可能更快，优化器有成本估算 |
| 联合索引 (a,b) 和 (b,a) 一样 | 最左前缀不同，适用查询不同 |
| 索引建了就不用管 | 索引会失效，要定期审查没用过的索引 |
MVCC 原理速记
MVCC（多版本并发控制）让读写不互相阻塞，是 InnoDB 高并发的基础。
| 组成部分 | 说明 |
|---|---|
| 隐藏列 | trx_id（最近修改事务ID）、roll_pointer（指向 undo log） |
| undo log | 旧版本链表，记录每次修改前的值 |
| ReadView | 读视图，记录活跃事务列表，判断哪个版本可见 |
可见性判断：
1. 版本的 trx_id < ReadView 最小活跃事务 ID → 可见（已提交）
2. 版本的 trx_id 在活跃列表里 → 不可见（未提交）
3. 版本的 trx_id > ReadView 最大活跃事务 ID → 不可见（后启动的事务）
不可见就顺着 roll_pointer 往前找 undo log 里的上一个版本，直到找到可见的为止。
-- RC vs RR 的 ReadView 生成时机（本示例基于 MySQL 8.0）
-- RC：每条 SELECT 都生成新 ReadView，所以能看到其他事务刚提交的
-- RR：事务第一次 SELECT 生成 ReadView，之后复用，所以可重复读
事务常见问题速查
| 问题 | 原因 | 解决方案 |
|---|---|---|
| 死锁 | 两事务循环等锁 | 统一加锁顺序、降隔离级别 |
| 锁等待超时 | innodb_lock_wait_timeout | 缩短事务、加索引 |
| 长事务 | 事务里做了慢操作 | 事务内禁 RPC、分批提交 |
| 幻读 | RR 下范围查询插入 | next-key lock 已解决 |
| 不可重复读 | RC 下同一事务两次读结果不同 | 升 RR |
| 长事务占用 undo | undo purge 不掉，表膨胀 | 缩短事务 |
| 大事务复制延迟 | 从库回放大事务慢 | 拆分大事务 |
| 死锁检测开销 | 死锁检测本身耗 CPU | 降并发 |
-- 查看当前事务（本示例基于 MySQL 8.0）
SELECT trx_id, trx_started, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;
-- 查看锁等待
SELECT * FROM information_schema.innodb_lock_waits;
索引与事务面试高频问答
| 问题 | 一句话答案 |
|---|---|
| 为什么 InnoDB 用 B+ 树不用二叉树 | B+ 树矮胖，磁盘 IO 少，叶子节点链表适合范围查询 |
| 聚簇索引和非聚簇索引区别 | 聚簇索引叶子存整行，非聚簇叶子存主键 |
| 为什么自增主键比 UUID 好 | 趋势递增减少页分裂，8 字节比 36 字节省空间 |
| MVCC 怎么实现 | 隐藏列 + undo log + ReadView |
| RR 和 RC 区别 | ReadView 生成时机不同，RC 每次 SELECT 都生成新的 |
| redo log 为什么要刷盘 | 保证持久性，崩溃后能恢复 |
| binlog 为什么要ROW格式 | ROW 记录每行变更，主从一致，可闪回 |
| 什么是最左前缀 | 联合索引查询要从最左列开始，中间断了后面用不上 |
| 什么是覆盖索引 | 查询列都在索引里，不用回表 |
| 什么是回表 | 二级索引查完拿主键再去聚簇索引查 |
| 为什么索引不能太多 | 每个索引拖慢写入，占空间，优化器易选错 |
| 前缀索引怎么用 | 长字符串列只索引前 N 个字符，省空间 |
| 哈希索引为什么不常用 | Memory 引擎才有，不支持范围和排序 |
| 事务的 ACID 分别靠什么保证 | undo 保原子、redo 保持久、锁+MVCC 保隔离 |
| 什么是两阶段提交 | redo prepare -> binlog write -> redo commit，保证一致 |
| RC 和 RR 怎么选 | 互联网一般用 RC，金融强一致用 RR |
索引维护常用命令
-- 分析表，更新统计信息（本示例基于 MySQL 8.0）
ANALYZE TABLE orders;
-- 检查表
CHECK TABLE orders;
-- 优化表（回收碎片）
OPTIMIZE TABLE orders;
-- 查看索引大小
SELECT index_name, stat_value*@@innodb_page_size/1024/1024 AS size_mb
FROM mysql.innodb_index_stats
WHERE table_name = 'orders';
-- 查看没用过的索引（8.0 sys schema）
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'app';
什么时候要 ANALYZE TABLE：大批量导入/删除后、统计信息过期导致优化器选错索引时。
什么时候要 OPTIMIZE TABLE：大量删除后表碎片多时。OPTIMIZE 会重建表，期间锁表，业务低峰期做。
事务相关参数速查
| 参数 | 默认值 | 说明 |
|---|---|---|
| transaction_isolation | REPEATABLE-READ | 隔离级别 |
| innodb_lock_wait_timeout | 50 秒 | 锁等待超时 |
| innodb_rollback_on_timeout | OFF | 超时是否回滚整个事务 |
| autocommit | ON | 自动提交 |
| max_binlog_cache_size | 18.4M | binlog 缓存 |
| innodb_flush_log_at_trx_commit | 1 | redo 刷盘策略，1 最安全 |
| sync_binlog | 1 | binlog 刷盘策略，1 最安全 |
| innodb_flush_method | | O_DIRECT 推荐 |
自测：索引与事务速查
题 1：联合索引 (a,b,c)，下面哪个查询走不到索引？
-- A. WHERE a = 1 AND b = 2 AND c = 3
-- B. WHERE a = 1 AND b = 2
-- C. WHERE b = 2 AND c = 3
-- D. WHERE a = 1 AND c = 3
<details>
<summary>参考答案</summary>
C 和 D 都走不到完整索引。C 违反最左前缀，b 和 c 都用不上；D 走 a，c 因为中间断了 b 用不上。正确答案是 C（完全不走索引）和 D（只走 a 列）。
</details>
题 2：RR 隔离级别下，InnoDB 怎么解决幻读？
<details>
<summary>参考答案</summary>
通过 next-key lock（记录锁 + 间隙锁）锁住查询范围，其他事务无法在范围内插入新行，从而避免幻读。这是 InnoDB 对标准 RR 的增强。
</details>
题 3：redo log 和 binlog 的区别？
<details>
<summary>参考答案</summary>
redo log 是 InnoDB 引擎层的物理日志，用于崩溃恢复，保证持久性；binlog 是 Server 层的逻辑日志，用于主从复制和 PITR。两者通过两阶段提交保证一致。
</details>
题 4：什么是覆盖索引？
<details>
<summary>参考答案</summary>
查询所需的所有列都在索引里，不需要回表查聚簇索引。EXPLAIN 的 Extra 列显示 Using index。覆盖索引能大幅减少随机 IO。
</details>
题 5：EXPLAIN 的 type=ALL 意味着什么？
<details>
<summary>参考答案</summary>
全表扫描。必须优化，要么加索引，要么改写 SQL。type 列至少要到 range 以上才算合格。
</details>
题 6：什么是回表？为什么要避免？
<details>
<summary>参考答案</summary>
二级索引叶子节点存的是主键值，查完整行需要拿主键再去聚簇索引查一次，这就是回表。回表是随机 IO，次数多了慢。避免方法：建覆盖索引，把查询列都放进索引。
</details>
题 7：为什么 InnoDB 推荐自增主键？
<details>
<summary>参考答案</summary>
自增主键趋势递增，插入时总是在 B+ 树最后一页追加，不会页分裂。UUID 随机，插入位置分散，页分裂频繁，写入性能差。而且 BIGINT 8 字节比 VARCHAR(36) 省空间，二级索引叶子节点携带成本低。
</details>
题 8：RC 和 RR 在 MVCC 实现上有什么区别？
<details>
<summary>参考答案</summary>
RC 级别每次 SELECT 都生成新的 ReadView，所以能看到其他事务刚提交的修改；RR 级别只在事务第一次 SELECT 时生成 ReadView，整个事务期间用同一个，所以可重复读。
</details>
题 9：为什么函数包裹索引列会让索引失效？
<details>
<summary>参考答案</summary>
B+ 树索引存的是列的原始值。对列套函数后，查询条件变成一个新表达式，索引里找不到对应的值。比如 DATE(created_at)='2026-10-01'，索引里存的是完整 datetime，无法直接匹配。
</details>
题 10：事务的隔离级别越高越好吗？
<details>
<summary>参考答案</summary>
不是。隔离级别越高，并发性能越差，锁越严。大多数互联网业务用 RC 就够了，只有金融强一致场景才需要 RR。根据业务选型，不要盲目追高。
</details>
题 11：什么是索引下推（ICP）？
<details>
<summary>参考答案</summary>
Index Condition Pushdown。把 WHERE 里的索引列条件下推到存储引擎层过滤，不用回表后再过滤。Extra 显示 Using index condition。减少回表次数。
</details>
题 12：为什么 LIKE '%xxx' 走不了索引？
<details>
<summary>参考答案</summary>
B+ 树按值排序，左模糊意味着起点不确定，无法定位 B+ 树的起点，只能全表扫。右模糊 'xxx%' 能定位起点，所以走索引。
</details>
自测评分：12 道题答对 10 道以上，索引与事务这块面试基本稳了；答对 6~9 道，建议回第 1~4 章再看一遍原理；答对 6 道以下，先把 B+ 树和 MVCC 画出来再说。
使用建议：这张速查卡建议打印出来贴工位旁边，每次 Code Review 看到慢 SQL 就翻一遍 type 和 Extra 列。
面试前 10 分钟：过一遍索引失效场景表和隔离级别表，这两块是必考题。
上线前：确认 SQL 走了预期索引，EXPLAIN 看 key 和 rows 列。
（本章完）
—— END ——
祝你面试顺利。
（完）
下一篇预告：第 78 章 速查卡：锁与MVCC
文章回复
0 条公开回复