mysql 非少数索引的等值查询为什么要加gap锁

在 MySQL 中，非少数索引的等值查询需要加 gap 锁的原因是防止幻读。

什么是幻读

幻读是指在一个事务中，查询结果包含了其他事务已经提交但当前事务还未提交的插入记录。

非少数索引的等值查询示例

sql
SELECT * FROM table_name WHERE id = 1;


如果 id 列不是唯一索引（即允许有多个记录具有相同 id 值），并且当前事务未对该表加任何锁，则会出现幻读问题。

幻读产生的机制

假设以下情景：

• 事务 A 查找到 id = 1 的记录，但并未加锁。
• 事务 B 在事务 A 查询后插入了一条 id = 1 的新记录。
• 事务 B 提交。
• 事务 A 再次查询 id = 1 的记录。

由于事务 A 未加锁，因此它看不到事务 B 插入的新记录。这就导致事务 A 查询结果中缺少一条记录，从而发生了幻读。

gap 锁如何防止幻读

为了防止幻读，MySQL 在非少数索引的等值查询上加 gap 锁。gap 锁的作用是：

• 锁定索引中查询值两端的间隙（gaps），即没有索引键值的范围。

在上面的示例中，当事务 A 执行查询时，它将获取 id = 1 两端的间隙锁。这样，在事务 A 查询期间，事务 B 无法在 id = 1 的两端插入新记录。

如果事务 B 尝试插入 id = 1 的新记录，它将被 gap 锁阻塞，直到事务 A 提交或回滚。这确保了事务 A 在查询期间不会发生幻读。

gap 锁的开销

gap 锁会带来一些开销，因为它需要锁定更多的范围。对于查询量较大的系统，这可能会导致性能问题。因此，在设计索引时，应权衡幻读风险与性能开销之间的平衡。

避免 gap 锁的建议

在某些情况下，可以通过以下方式避免使用 gap 锁：

• 创建唯一索引，这将防止幻读。
• 使用范围查询代替等值查询，这不会获取 gap 锁。
• 在事务中对表加锁，这将阻止其他事务修改数据。

但是，这些方法也可能带来其他开销或限制。因此，在实际应用中需要根据具体情况做出权衡。

在 MySQL 中，对于非唯一索引的等值查询，即使用 = 这样的等式条件来查询数据，数据库需要额外添加 Gap 锁来确保数据的一致性。

理解 Gap 锁

Gap 锁是一种范围锁，用于锁定索引中的一个范围，而不仅仅是特定行的索引项。它可以防止其他事务在该范围内插入或删除数据，以确保正在进行查询的事务能够看到完整一致的数据。

非唯一索引的挑战

非唯一索引的特点是允许对同一值存在多个索引项。这意味着对于一个非唯一索引的等值查询，可能存在多个匹配行的索引项。

当一个事务使用非唯一索引进行等值查询时，数据库无法仅基于索引项来确定要返回的行集。数据库必须扫描索引并检查每个匹配的索引项对应的行，以确定哪些行满足查询条件。

Gap 锁的作用

在这种情况下，Gap 锁就派上用场了。它锁定索引中的一个范围，即从查询中指定的索引值到下一个更大的索引值（对于递增索引）或下一个更小的索引值（对于递减索引）。

通过锁定这个范围，Gap 锁可以防止其他事务在事务执行查询期间向该范围内插入或删除数据。这确保了事务看到的行集与查询开始时相同，从而保证了查询结果的完整性和一致性。

具体示例

假设我们有一个名为 users 的表，其中有一个非唯一索引 idx_username 索引列 username。如果我们执行以下查询：

sql
SELECT * FROM users WHERE username = 'Alice';


由于 username 是一个非唯一索引，因此可能存在多个具有相同 username 值的行。为了确保查询返回完整一致的行集，数据库将使用 Gap 锁锁定 idx_username 索引中 Alice 索引项到下一个更大索引项（假设 username 是递增的）之间的范围。

这将防止其他事务在查询执行期间在该范围内插入或删除数据，确保事务能够看到与查询开始时相同的数据状态。

避免死锁

Gap 锁对于非唯一索引的等值查询至关重要，因为它防止了死锁。如果没有 Gap 锁，两个事务都可以获得对相同数据范围的读锁，并等待另一个事务释放锁。这可能导致死锁。而 Gap 锁通过锁定整个范围，避免了这种情况的发生。

结论

综上所述，对于非唯一索引的等值查询，MySQL 使用 Gap 锁来锁定索引中的一个范围，以确保查询看到的数据集在查询期间保持完整一致。这防止了其他事务在同一范围内插入或删除数据，从而避免了死锁的可能性。

在 MySQL 中，当对非少数索引（即辅助索引）进行等值查询时，需要获取 gap 锁。这是为了防止幻读现象，即在事务读取数据时，其他事务对数据进行了插入或删除操作，导致读取结果不一致。

幻读现象是如何发生的？

假设有一个名为 users 的表，其中包含以下记录：


id | name
----+------
1 | Alice
2 | Bob
3 | Charlie


现在，事务 A 执行以下查询：


SELECT id, name FROM users WHERE id = 2;


在事务 A 正在读取 id 为 2 的记录时，另一个事务 B 执行以下操作：


INSERT INTO users (id, name) VALUES (2.5, 'Eve');


事务 B 挿入了一条新的记录 Eve，其 id 为 2.5。由于 users 表的 id 列是非少数索引，因此 Eve 将被插入到 id 为 2 和 3 之间的 gap 中。

当事务 A 提交时，它将看到 id 为 2 的记录，以及 id 为 2.5 的新记录。但是，这与事务 A 开始读取数据时的实际数据库状态不一致。

gap 锁的作用

为了防止幻读，MySQL 在事务 A 执行等值查询时，会在 id 为 2 和 3 之间的 gap 上获取 gap 锁。这将阻止事务 B 在该 gap 中插入或删除记录，从而保证事务 A 读取到的数据与数据库的实际状态一致。

为什么非少数索引需要 gap 锁？

非少数索引不同于主键索引，后者可以唯一标识表中的每一行。对于非少数索引，多个行可以具有相同的值，因此在索引中存在 gap。这些 gap 可以由插入或删除操作来填充。

如果没有 gap 锁，则事务在读取数据时，其他事务可以向索引中插入或删除记录，从而改变 gap 的位置。这可能导致幻读现象，因为读取到的数据与实际数据库状态不一致。

gap 锁的优点和缺点

优点：

• 防止幻读现象
• 确保事务读取到的数据与数据库的实际状态一致

缺点：

• 可能会导致并发性能下降，因为 gap 锁会阻止其他事务并发访问数据
• 对于范围查询，gap 锁可能导致死锁

结论

在 MySQL 中，对非少数索引进行等值查询时，需要获取 gap 锁，以防止幻读现象。虽然 gap 锁可以确保数据一致性，但也可能影响并发性能。因此，在设计查询时，应仔细考虑索引使用和并发要求，以找到最佳的平衡。