前言

开发与维护人员避免不了与 in/exists、not in/not exists 子查询打交道，接触过的人可能知道 in/exists、not in/not exists 相关子查询会使 SELECT 查询变慢，没有 join 连接效率，却不知道 DELETE、UPDATE 下的子查询却可能导致更严重的锁问题，直接导致 MySQL InnoDB 行锁机制失效，锁升级，严重影响 数据库 的并发和性能。对大表或高并发的表的执行 DELETE、UPDATE 子查询操作，甚至可能导致业务长时间不可用。

MySQL 下的 InnoDB 行锁，是通过以位图方式对 index page 加锁机制来实现的。而不是直接对相应的数据行和相关的 data page 加锁，这样的加锁实现就导致了其行锁实现的不稳定性。InnoDB这种行锁实现特点意味着：只有通过有效索引条件检索数据行，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！UPDATE、DELETE 子查询条件下优化器的实现导致子查询下的行锁机制失效，行锁升级，对更多无关的行数据加锁，进而影响数据库并发和性能 。

一、UPDATE、DELETE 子查询锁机制失效解析及优化方案

下面以普通的 UPDATE 关联子查询更新来详解子查询对锁机制的影响及具体优化解决方案：

子查询下的事务、锁机制分析： 优化器实现：

UPDATE pay_stream a SET a.return_amount =(SELECT b.cash_amount FROM pay_main b WHERE a.pay_id = b.pay_id AND b.user_name = '1388888888');

id  select_type         table   partitions  type    possible_keys          key      key_len  ref                         rows  filtered  Extra        ------  ------------------  ------  ----------  ------  ---------------------  -------  -------  ------------------------  ------  --------  -------------
     1  UPDATE              a       (NULL)      index   (NULL)                 PRIMARY  98       (NULL)                    155041    100.00  (NULL)       
     2  DEPENDENT SUBQUERY  b       (NULL)      eq_ref  PRIMARY,idx_user_name  PRIMARY  98       settlement_data.a.pay_id       1      5.00  Using where 

从执行计划可以看出该 update 子查询，优化器先执行了 id 为2的 （DEPENDENT SUBQUERY ）相关子查询部分，然后通过对 PRIMARY 以索引全扫描方式对全表 155041 行数据加锁主锁，来执行的 update 操作，阻碍了了表的update、delete并发操作。

事物、锁验证：

事物一：

事物二：

事务二果真被事务一阻塞，事务一的子查询操作的确锁住了不相关的数据行，阻碍了数据库的并发操作。

关联更新下的事物、锁机制分析：

优化器实现：

UPDATE pay_stream a INNER JOIN pay_main b ON a.pay_id = b.pay_id SET a.return_amount = b.cash_amount WHERE b.user_name = '1388888888'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra ------ ----------- ------ ---------- ------ --------------------- ------------- ------- ------------------------ ------ -------- -------- 1 SIMPLE b (NULL) ref PRIMARY,idx_user_name idx_user_name 387 const 1 100.00 (NULL) 1 UPDATE a (NULL) eq_ref PRIMARY PRIMARY 98 settlement_data.b.pay_id 1 100.00 (NULL)

从执行计划可以看出，优化器先执行了通过 idx_user_name 索引执行了 b 表的检索操作，然后再通过 eq_ref 方式关联 PRIMARY 更新了一行数据，并没引起行锁升级，影响表的并发操作。事务机制验证如下：