SQL优化之一则MySQL中的DELETE、UPDATE 子查询的锁机制失效案例

关注“数据和云”，精彩不容错过

前言

开发与维护人员避免不了与 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并发操作。 

事物、锁验证： 

事物一： 

事物二：