MySQL SELECT ... FOR UPDATE阻塞及pid索引未使用问题咨询

问题现象 #

打开两个会话窗口：

• 第一个窗口执行：

  begin;
  select * from users where pid = 100 for update;
  

  该查询未匹配到任何数据，但第二个窗口执行以下语句时直接陷入阻塞：
• 第二个窗口执行：

  select * from users where pid = 1 for update;
  

  只有在第一个窗口执行commit;后，第二个窗口才会继续执行。这明显和行级锁（Row-Level-Lock）的预期行为不符。

补充的表信息与执行计划：

users表索引详情 #

执行show index from users;得到结果：

+----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table          | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users          |          0 | PRIMARY     |            1 | id          | A         |       14754 |     NULL | NULL   |      | BTREE      |         |               |
| users          |          1 | pid         |            1 | pid         | A         |       14754 |     NULL | NULL   |      | BTREE      |         |               |
+----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

EXPLAIN执行结果 #

执行explain select * from users where pid=1;的结果：

+----+-------------+----------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table          | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+----------------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | users          | ALL  | pid           | NULL | NULL    | NULL | 15204 | Using where |
+----+-------------+----------------+------+---------------+------+---------+------+-------+-------------+

执行explain select * from users where id=18035;的结果：

+----+-------------+----------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table          | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+----------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | users          | const | PRIMARY       | PRIMARY | 8       | const |    1 | NULL  |
+----+-------------+----------------+-------+---------------+---------+---------+-------+------+-------+

疑问：明明users表存在pid索引，为什么EXPLAIN显示key为NULL，查询不走索引？

核心原因解析 #

1. 会话阻塞的真相：全表扫描引发的间隙锁冲突 #

看起来是行级锁的问题，但本质是查询未走索引导致的全表扫描，触发了InnoDB的间隙锁（Gap Lock）机制：

• 第一个会话的select ... for update因为没走pid索引，只能全表扫描所有行。即使没匹配到pid=100的数据，InnoDB会对扫描过程中涉及的所有间隙加间隙锁，防止其他事务插入符合条件的数据。
• 第二个会话同样走全表扫描执行for update时，需要对所有行及间隙加锁，必然和第一个会话持有的间隙锁冲突，因此被阻塞。直到第一个会话提交释放所有锁，第二个会话才能继续。

2. pid索引未被使用的常见原因 #

从现有信息来看，主要可能有以下几种情况：

• 优化器成本判断：MySQL优化器会对比全表扫描和索引查询的成本。如果表数据量不大，优化器可能认为全表扫描的IO成本比走索引后回表的成本更低，因此选择全表扫描。
• 统计信息过时：索引的Cardinality（基数）统计数据可能不准确，导致优化器误判索引的选择性。比如当前统计的pid基数是14754，但实际表行数是15204，看起来选择性很高，但如果统计信息过时，优化器可能还是觉得全表扫描更划算。
• 数据类型不匹配：如果pid字段的类型和查询中的1类型不一致（比如pid是VARCHAR类型，查询用了数字1），会触发隐式类型转换，导致索引失效，只能走全表扫描。

验证与解决办法 #

1. 强制使用索引验证
   用FORCE INDEX强制查询使用pid索引，看看是否还会出现阻塞：

   -- 第一个会话执行
   begin;
   select * from users FORCE INDEX (pid) where pid = 100 for update;
   -- 第二个会话执行
   select * from users FORCE INDEX (pid) where pid = 1 for update;
   

   此时查询会走索引，只会在pid=100对应的索引间隙加锁，不会影响pid=1的查询，也就不会出现阻塞。

2. 更新表统计信息
   执行以下语句更新users表的统计信息，让优化器获取准确的索引基数：

   ANALYZE TABLE users;
   

   更新后再执行EXPLAIN select * from users where pid=1;，大概率会看到优化器选择pid索引。

3. 检查数据类型一致性
   确认users表中pid字段的类型（比如INT/VARCHAR等），确保查询条件中的值类型和字段类型完全匹配，避免隐式转换导致索引失效。

内容的提问来源于stack exchange，提问作者Wyatt