FOR UPDATE与索引

在 MySQL 中使用 FOR UPDATE时，底层根据您查询条件中使用的索引类型来决定加锁的粒度和范围，这直接影响到并发性能。简单来说，​使用主键或唯一索引可以精准锁定目标行，而使用普通索引或无索引则可能导致范围扩大甚至锁表。

为了让你快速掌握核心规律，我先把不同索引类型下 FOR UPDATE的加锁行为总结在下面的表格中：

🔍 底层加锁机制详解

为了更直观地展示 MySQL 如何根据不同的索引条件决定加锁策略，下图描绘了其核心决策流程：

flowchart TD
    A[执行带FOR UPDATE的查询] --> B{评估WHERE条件使用的索引}
    
    B -- "主键索引" --> C[在聚簇索引上加<br>记录锁（Record Lock）]
    B -- "唯一索引" --> D[在唯一索引上加记录锁<br>并回溯到聚簇索引行加锁]
    B -- "普通索引" --> E[在普通索引匹配项加记录锁<br>并回溯聚簇索引行加锁]
    B -- "无索引" --> F[全表扫描<br>对所有扫描到的行加记录锁]
    
    C --> G[锁定精确的单一行]
    D --> G
    
    E --> H{事务隔离级别是否为<br>REPEATABLE-READ（默认）?}
    H -- 是 --> I[增加间隙锁（Gap Lock）<br>形成临键锁（Next-Key Lock）]
    H -- 否（READ-COMMITTED） --> J[仅加记录锁，不加间隙锁]
    
    I --> K[锁定一个范围<br>（行+间隙），防止幻读]
    J --> L[仅锁定现有行]
    
    F --> M[效果等同于表锁<br>并发性能最差]
    
    G --> N[并发性能最佳]
    L --> O[并发性能较好]
    K --> P[并发性能中等，<br>注意避免过大范围]

上图清晰地展示了不同索引条件下的加锁路径。其背后的原理与规则如下：

1. ​使用主键或唯一索引查询​

   • 这是最理想的情况。MySQL 能通过索引精确定位到唯一的一行数据。

   • 它会在聚簇索引（主键索引）的这行记录上设置一个记录锁。

   • ​效果​：只有对这一行的并发写操作会被阻塞，其他行的操作不受任何影响，并发性最高。

2. ​使用普通索引（非唯一索引）查询​

   • 这种情况稍复杂。因为索引不唯一，可能有多行数据满足条件。

   • MySQL 会为所有匹配的索引项加记录锁，同时回溯到聚簇索引，为对应的数据行加记录锁。

   • ​关键点：间隙锁​：在 MySQL 默认的 REPEATABLE-READ隔离级别下，为了防止幻读，InnoDB 还会在匹配的索引记录之间的间隙上加锁（Gap Lock）。记录锁和间隙锁合称临键锁。这会锁定一个范围，而不仅仅是存在的行。例如，WHERE d_name = 'sales'可能会锁定所有 d_name为 'sales' 的行及其前后的间隙，阻止其他事务插入新的 'sales' 记录。

   • ​效果​：并发性依然较好，但锁定的范围可能比你直观看到的数据行更大，会阻塞范围内的插入操作。

3. ​无索引查询​

   • 这是最危险的情况。由于没有索引可以快速定位数据，MySQL 只能进行全表扫描。

   • 为了确保数据一致性，在扫描过程中，InnoDB 会对它扫描过的每一行都加上记录锁。

   • ​效果​：这在实际效果上等同于锁定了整个表。其他事务无法对表中的任何记录进行写操作（如 UPDATE, DELETE, SELECT ... FOR UPDATE），并发性能极差，在高并发系统中可能引发灾难性后果。

⚠️ 特别注意：潜在陷阱与优化策略

1. ​索引失效导致表锁​：即使你使用了索引字段，但如果写法导致 MySQL 的优化器决定不使用索引​（例如对索引字段进行函数运算、数据类型不匹配、或者表很小优化器认为全表扫描更快），依然会退化为无索引查询，产生表锁。可以通过 EXPLAIN命令查看 SQL 的执行计划，确认是否真正使用了索引。

2. ​解决无索引问题的根本方法​：为经常作为 WHERE条件的字段创建合适的索引。这是提升 FOR UPDATE并发性能最有效的措施。

3. ​强制使用索引​：在极端情况下，如果优化器判断失误，你可以使用 FORCE INDEX (index_name)语法强制 MySQL 使用指定的索引，避免表锁。

💡 总结

记住这个核心原则：​FOR UPDATE的锁粒度直接依赖于查询能否通过索引快速、精确地定位数据。​​

• ​追求行锁​：确保你的 WHERE条件使用主键或唯一索引。

• ​警惕范围锁​：使用普通索引时，要意识到可能会锁定一个范围（间隙锁）。

• ​避免表锁​：​绝不在未建立索引的字段上使用 FOR UPDATE。

在实际开发中，务必在测试环境中验证加锁行为，并使用 SHOW ENGINE INNODB STATUS等命令观察锁信息，这样才能写出正确、高效的高并发代码。