MySQL常见面试题——索引相关

更新: 5/9/2025 字数: 0 字 时长: 0 分钟

ACID是什么

ACID这个词汇经常听一些老程序员提，最开始的时候感觉很高端，然后去查了一下，发现其实就是事务的四个性质/标准，这个东西不光在MySQL中存在，而是普适于所有的事务机制的

• Atomicity/原子性：一个事务的操作要么全部完成，要么全部寄掉
• Consistency/一致性：事务操作的对象之间数据保持一致
• Isolation/隔离性：允许并发操作（其实是说同时执行多个事务互不影响）
• Duration/持久性： 事务结束后数据的修改要持久，不能说一会之后又因为某些奇奇怪怪的故障变回去了，也可以说是数据要持久化

脏读/幻读/不可重复读？

这三个都是关于并发相关的问题，只有在并发环境下处理多个事务之间会出现的问题

首先我们得理解一个事——这三个问题其实都是由于时间与状态之间的矛盾而导致的

我们认为/规定一个事务应该是在一个时间点的状态下完成的，但是由于事务包含多个操作，这些操作自然不可能同时完成，同是由于ACID还要求事务应该支持并发，这就导致了A事务的某个操作可能还在进行的过程中B事务就对表中A事务使用的数据进行了修改，这时由于我们希望事务的操作是在一个时间点的状态下完成的，这就发生了矛盾

脏读

A事务读到了B事务正在修改过了但尚未提交的数据

由于一个事务在提交前有可能发生回滚操作，这时A事务使用B事务尚未提交但已经修改了的数据，B事务再将数据回滚，直接烂完

幻读

一个事务内按照某个条件查询对应条件的数据的数量/列数，结果查询的数据前后不一致

A事务查询的过程中B事务可能进行新增操作并完成提交，这时A事务再次查询就会出现与上一次查询不同的数据量

不可重复读

一个事务内对一条数据进行多次查询，前后数据不一致

A事务查询的过程中B事务可能对A事务查询的数据进行修改操作并完成提交，这时A事务再次查询就会出现与上一次查询不同的数据

MySQL如何解决上面的这些问题？

• 锁：MySQL使用了多种锁来保证一致性
• 事务隔离机制：MySQL通过多种事务隔离机制来控制各个事务之间的隔离层度
• 多版本并发控制/MVCC：通过在数据库中保存不同版本的数据来实现不同事务的隔离，在读操作时会选取合适的版本来进行读取

MySQL的隔离等级

所谓的隔离等级其实就是对事务的隔离层度进行的分级，按隔离程度的高低由低到高会区分为：

• 读未提交：指一个事务在还没提交的时候，他做的变更可以被其他的事务看到（也就是基本不隔离）
• 读提交：指事务提交后变更才可以被看到（也就是最符合逻辑的隔离方式）
• 可重复读：在事务执行前会保存一个快照，然后整个事务都是针对快照进行扫描和查询，这也是InnoDB种默认的隔离机制。这里需要注意的是（这种状态已经不顾及其他事务的隔离等级，要求事务执行中看到的数据始终是一个时刻的） 需要注意的是，可重复读会照成幻读，具体原因看下一个问题
• 串行化：对一条记录上锁，若多个事务对该记录进行读写操作，如果发生读写冲突，则阻塞进行

可重复读为什么会出现幻读

这个操作其实是有点反直觉的，因为根据上面对可重复读的介绍，那么保存了初始的版本的情况下理论上事务应该是不会出现幻读的，那为什么实际上会出现幻读呢？

这是由快照的样子和扫描机制导致的。

在使用了MVCC的数据库中，数据库会维护一个事务序列，每个事务都会分配一个唯一且有序的TxId

所谓分配的”快照“其实是一个特定的TxId，以及一个未提交事务的列表，当事务执行时，会给这个事务分配一个TxId，然后事务完成时，会把事务影响过的数据的版本修改为TxId

而在可重复读的条件下，一个事务执行中可以读到的数据就是该行版本小于该事务的TxId的数据

但是有一个问题：就是整个的查询过程其实和其他的隔离机制没有很大区别，只是多了一个对TxId的询问的过程罢了，进而也就导致了其实事务是能观测到一行数据的存在与否

比如这样一套过程：

• 事务 A 启动，获取快照（比如，快照 TxID = 50）。此时数据库里有 TxID ≤ 50 的已提交数据。

• 事务 A 执行第一次查询：

  SELECT COUNT(*) FROM products WHERE price BETWEEN 100 AND 200

  • 数据库需要找到所有 price 在 100 到 200 之间的行。它可能会使用 price 上的索引，扫描索引树中这个范围内的条目。
  • 扫描器遍历索引或数据页，找到符合 price BETWEEN 100 AND 200 条件的潜在行。
  • 对于找到的每一条潜在行，数据库会应用事务 A 的快照可见性规则（快照 TxID = 50）来确定哪个版本可见。由于此时只有 TxID ≤ 50 的已提交数据可见，它统计出符合条件且可见的行，比如是 10 行。

• 并发事务 B 启动（TxID = 51）。

• 事务 B 插入新行：

  INSERT INTO products (name, price) VALUES ('新产品', 150)

  • 数据库在 products 表中创建一个新行，price = 150。这行数据的创建 TxID 是 51。
  • 数据库同时更新索引，在 price 索引中为新插入的行添加一个条目。

• 事务 B 提交。 新插入的行现在是已提交状态，其创建 TxID 是 51。

• 事务 A 执行第二次查询：

  SELECT COUNT(*) FROM products WHERE price BETWEEN 100 AND 200 --再次查询

  • 数据库再次执行扫描操作。它再次遍历 price 索引中 100 到 200 的范围。
  • 这一次，扫描器会发现事务 B 插入的新行的索引条目。这个条目就在扫描范围之内。
  • 扫描器找到了这个新的潜在行。数据库仍然会尝试应用事务 A 的快照可见性规则（快照 TxID = 50）。新行的创建 TxID 是 51。根据可见性规则，事务 A 的快照是看不到这行数据的具体内容的（因为 51 > 50）。

然而由于这个扫描操作本身其实和正常的查询是没有区别的，也就是说当查询经过id=5的时候，试图去询问该数据的TxId，却发现这个本不该存在的数据存在了，这个存在/新增的状态就会被扫描察觉到，进而影响最后的查询结果

这个问题的根本在于在于：快照管的是“内容版本”，扫描管的是“范围/结构里的条目是否存在”。两者结合时，新/旧条目的出现/消失改变了扫描结果，导致了幻读。

串行化怎么解决了幻读

通过上 S型的 next-key 锁的方式阻止对正在执行的事务进行增删改操作

事务是不是越高越好

当然不是，事务等级越高开销越高，效率也就越低

为什么InnoDB会选择可重复读作为默认的隔离等级

因为串行化引入了锁机制，大幅度降低了并发的效率

并且InnoDB实际在不可重复上通过了特殊的机制（引入间隙锁和临键锁）来限制了幻读，进而导致幻读的减少

同样是上锁，为什么可重复读的并发效率高于串行化

对于普通的 SELECT 语句，这个读取过程通常不需要加锁，因此不会阻塞其他事务的写操作。间隙锁和临键锁仅在扫描索引范围时会使用

这些锁会阻塞其他事务向被锁定的间隙中插入数据，以及阻塞对被锁定行的修改/删除。

而串行化对所有的操作都上了锁