MySQL 面试：索引为什么这样建，事务怎样保证？

MySQL 的索引要按不同维度分类，再结合实际查询设计。以 MySQL 8.4 的 InnoDB 为例：

• 按约束：主键索引、唯一索引、普通索引。主键唯一且非空，一张表只有一个主键；唯一索引约束键值唯一，普通索引允许重复。可空列上的唯一索引允许多个 NULL，业务要求必填时还要配合 NOT NULL。
• 按列数：单列索引和联合索引。联合索引按定义的列顺序组织，通常从连续的最左前缀构造查找范围。
• 按数据组织：InnoDB 的聚簇索引保存行记录，通常由主键承担；普通二级索引的记录包含索引列和主键值，需要其他列时可再通过主键查找，也就是回表。
• 按结构与用途：InnoDB 常规索引采用 B+ 树；另外有文本检索用的 FULLTEXT、空间数据用的 SPATIAL。其他引擎还可能支持 Hash 索引，不能把它们都当作 InnoDB 常规索引的可选结构。

这些维度可以叠加：一个索引可以同时是“唯一、联合、二级索引”。覆盖索引描述的是某条查询所需数据能否由索引提供，也不是和主键、普通索引互斥的一种建索引类型。

项目设计时，我会按“查询模式与业务约束 → 候选索引 → 执行计划和实际负载验证 → 读写成本取舍”来说明。

InnoDB 使用 B+ 树组织普通索引，主要是为了兼顾少量页访问完成定位、有序范围扫描，以及数据持续增删改这几类需求。

1. 多路分支让树比较矮。 索引以页组织，非叶子页主要保存索引键和子页定位信息，单页能指向许多子页。相比逐层只有两个分支的树，定位同样多的数据通常需要经过更少的层级，减少潜在的磁盘读取。
2. 叶子页按键有序，适合范围查询。 找到范围起点后，可以沿叶子页链接继续扫描，不必为范围里的每条记录重新从根查找；索引顺序满足查询条件时，也有利于排序和分组。
3. 同一种结构能支撑点查与范围查。 聚簇索引的叶子页保存行记录，二级索引的叶子记录保存索引列与对应主键；需要完整行时，再按主键访问聚簇索引。

它的优势来自页组织和访问模式的配合，不能简单概括成“时间复杂度最低”。插入、删除仍有维护页、分裂或合并的成本；树高也不等于实际磁盘读取次数，因为索引页可能已经在 Buffer Pool 中。

聚簇索引与非聚簇索引的主要区别是索引如何组织行数据，以及找到索引记录后怎样定位完整行。以 MySQL 8.4 InnoDB 的普通 B-tree 索引为例：

• 聚簇索引的叶子记录保存行数据。 通常按主键组织，沿主键查到叶子记录，就定位到了这一行。每张 InnoDB 表只有一种聚簇组织。
• 二级索引的叶子记录保存索引列和定位行用的主键值。 用二级索引查完整行时，通常先找到主键，再访问聚簇索引，这一步叫回表。一张表可以有多个二级索引。
• 二级索引不一定需要回表取列。 如果查询所需列都在该索引中，可以使用覆盖索引；但实际访问还可能受事务可见性检查影响。

这是数据组织维度的划分，与唯一、普通、单列、联合等分类可以重叠。也不能把所有数据库的非聚簇索引都理解为“存主键”：例如 MyISAM 的索引使用数据记录地址定位行，与 InnoDB 二级索引不同。

如果条件只是写成 C=3 AND B=2 AND A=1，通常仍然可以利用 (A,B,C) 的三列等值前缀。最左前缀指的是索引定义中的列顺序，和这些 AND 条件在 WHERE 中的书写顺序没有直接关系。这里假设使用 MySQL 的普通 B-tree 联合索引，列和值类型适配，也没有给索引列额外套表达式。

(A,B,C) 按照 先 A、相同 A 内再按 B、相同 A 和 B 内再按 C 的顺序组织。因此，常规范围定位可以利用 (A)、(A,B)、(A,B,C) 这些连续的左前缀。缺少前面的列，后面的列就未必能直接确定一段连续的索引范围。

不过，“可以形成查找条件”和“优化器最终选择这个索引”还要分开：后者也取决于数据分布、返回行数、是否回表和其他可用索引的成本。

以 MySQL 常见的 B+ 树索引为例，“索引失效”通常指查询不能利用某个索引有效缩小扫描范围，或者优化器判断走它成本更高，选择了其他访问方式。

常见需要检查的情况有：

• 索引列参与函数、运算或不合适的类型转换：查询比较的是处理后的值，而普通索引按原值排序，通常不能直接定位。
• 联合索引缺少前导列条件：例如索引是 (a, b, c)，只按 b 查询，通常不能按普通的最左前缀方式定位；部分版本和查询满足条件时有跳跃扫描等例外。
• 字符串匹配以通配符开头：例如 LIKE '%abc%'，普通 B+ 树难以确定可查找的前缀范围。
• 匹配数据过多或条件组合代价较高：大量二级索引扫描加回表，可能不如直接扫描表；OR 的各个分支是否有合适访问路径，也会影响选择。

还要区分：不能按条件快速定位，不代表完全没扫描索引；扫描索引，也不代表扫描得少。 !=、OR、IS NULL 都不是天然的“索引失效关键字”，应结合索引定义、数据分布和执行计划判断。

EXPLAIN 用于看优化器准备怎样执行 SQL。排查慢查询时，我会先找到具体 SQL 和参数，再结合计划判断：从哪张表开始、怎样访问、预计处理多少行、是否需要大量回表、排序或中间结果，而不是只确认“用了索引”。

以 MySQL 8.4 的传统表格格式为例，重点看：

• type：表的访问方式，例如精确查找、范围访问、全索引扫描或全表扫描。
• possible_keys、key、key_len：候选索引、实际选择的索引，以及所用键长度；结合索引列定义判断是否符合预期。
• rows、filtered：预计检查的行数和表条件过滤后的保留比例，用于判断扫描量与后续连接的规模。
• Extra：覆盖、索引下推、额外排序、临时表等信息，用来解释访问之后还在做哪些工作。

普通计划主要给出优化器估算。需要验证真实执行情况时，可使用 EXPLAIN ANALYZE 查看实际耗时、行数和循环次数；它会执行语句，使用时需要考虑数据规模与负载。

慢 SQL 排查应结合接口表现与数据库证据，按发现问题、定位原因、针对性优化、验证结果四步进行：

1. 发现问题：结合接口监控、慢查询日志和语句统计，记录具体 SQL、参数、调用频率及影响范围。先区分单条查询耗时高，还是一次请求中重复查询过多。
2. 定位原因：区分连接等待、锁等待与执行成本。连接问题检查连接池和释放情况，锁问题检查阻塞事务；执行成本则通过执行计划分析扫描、连接和排序等环节。
3. 针对性优化：根据证据减少重复查询、改写条件或调整索引，而不是统一采用“加索引、加缓存”。
4. 验证结果：先确认结果一致，再在可比的数据、缓存和并发条件下对比扫描量与延迟；新增索引时同时评估写入和存储代价。

例如，已有 (tenant_id, created_at) 联合索引时，查询某租户一天的订单，若对时间列使用 DATE()，仍可能扫描该租户的大量历史记录。可尝试改为当天零点至次日零点的范围条件，再核对执行计划、扫描量和结果。

事务隔离级别规定：并发事务的修改能在什么时候被其他事务看见，以及哪些并发异常不允许发生。 SQL 标准定义了四级：

• 读未提交（Read Uncommitted）：允许看到其他事务尚未提交的修改，可能出现脏读、不可重复读和幻读。
• 读已提交（Read Committed，RC）：不允许脏读，但同一事务前后两次读取之间，可能看到其他事务新提交的修改，仍允许不可重复读和幻读。
• 可重复读（Repeatable Read，RR）：不允许脏读和不可重复读；按标准的最低保证，仍允许幻读。数据库实现可以提供比标准更强的保证。
• 可串行化（Serializable）：并发事务的执行效果等价于按某种顺序逐个执行，防止上述异常及其他不可串行化的结果；实现通常需要更多冲突控制。

以 MySQL 8.4 InnoDB 为例，文档默认级别是 RR。RC 和 RR 的普通一致性读主要借助 MVCC：RC 每次读取建立新快照，RR 在同一事务中复用快照；锁定读和更新则使用当前记录及锁机制，不能把它们当成一直读取旧快照。

MVCC（多版本并发控制）让不同事务根据自己的可见性规则，读取同一行的不同版本，减少普通查询与写操作之间的阻塞。以 MySQL 8.4 InnoDB 为例，关键是记录中的事务信息、undo 历史版本，以及决定哪些版本可见的 Read View。

事务修改记录时，InnoDB 更新当前记录，并保存撤销和构造旧版本所需的 undo 信息。一致性读先检查当前版本是否对自己的 Read View 可见；不可见，就沿回滚指针利用 undo 构造更早版本，直到找到可见版本，或确认该行在这个视图中不存在。Read View 是可见性信息，不是给整个数据库复制一份数据。

READ COMMITTED 下，每次一致性读建立新视图；REPEATABLE READ 下，通常由第一次一致性读建立视图，之后沿用，同时可以看到本事务自己的修改。MVCC 主要帮助非锁定的一致性读；写操作和 SELECT ... FOR UPDATE 等锁定读仍需要相应的锁，不能把它理解成数据库完全无锁。

以 MySQL 8.4、InnoDB 普通持久表，并开启 binlog 为例，三类日志各有分工：undo log 支持回滚和历史版本读取，redo log 支持崩溃恢复，binlog 记录可供复制与备份恢复的数据变更。 提交时，MySQL 通过内部两阶段提交，让 InnoDB 的提交结果与 binlog 中的事务记录保持一致；undo 并不是其中独立的第三个提交阶段。

• undo log 保存撤销修改所需的信息，例如更新前的列值。事务回滚时用它撤销修改；一致性读也可以沿版本链构造符合 Read View 的历史记录。它不是简单保存一条“反向 SQL”，提交后也不一定立即清理，其他事务可能仍需要旧版本。
• redo log 记录 InnoDB 数据页相关的修改，使数据库能在崩溃后重做尚未完整写入数据文件的变化。它配合 WAL：脏页写入数据文件前，相关 redo 必须先满足持久化要求，因此提交不必等待所有修改过的数据页都写盘。
• binlog 位于 MySQL Server 层，以事件记录数据变更，主要用于复制，以及恢复备份之后继续重放增量。它可以采用 ROW、STATEMENT 等格式；ROW 记录行变化，不能把所有 binlog 都说成原始 SQL。它也不能代替 InnoDB 的页级崩溃恢复。

可以把三者理解为：undo 负责“需要撤销或读取旧版本时怎么办”，redo 负责“内存里的页修改还没完全落到数据文件时怎么办”，binlog 负责“怎样把已记录的数据变更用于其他实例或历史恢复”。