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并发操作。

事物、锁验证：

事物一：

事物二：

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

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

优化器实现：

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 更新了一行数据，并没引起行锁升级，影响表的并发操作。事务机制验证如下：

事物一：

事物二：

不难看出普通 join 关联更新只对需要更新的数据行加索，更有利于数据库的并发操作。

二、其它场景下UPDATE 、DELETE子查询的优化方案

in/exists 子查询
in 子查询下优化器实现：

UPDATE pay_stream a   SET a.return_amount = 0 WHERE a.pay_id IN (SELECT b.pay_id 
  FROM pay_main b WHERE  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)  155044    100.00  Using where  
     2  DEPENDENT SUBQUERY  b       (NULL)      unique_subquery  PRIMARY,idx_user_name  PRIMARY  98       func         1      5.00  Using where  DELETE a FROM pay_stream a WHERE a.pay_id IN (SELECT b.pay_id 
  FROM pay_main b 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  Using index  
     1  DELETE       a       (NULL)      eq_ref  PRIMARY                PRIMARY        98       settlement_data.b.pay_id       1    100.00  (NULL)

exists 子查询下优化器实现：
UPDATE pay_stream a SET a.return_amount = 0 WHERE `js
EXISTS (SELECT b.pay_id
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)                    155044    100.00  Using where  
 2  DEPENDENT SUBQUERY  b       (NULL)      eq_ref  PRIMARY,idx_user_name  PRIMARY  98       settlement_data.a.pay_id       1      5.00  Using whereDELETE a FROM pay_stream a WHERE EXISTS (SELECT 1

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  DELETE              a       (NULL)      ALL     (NULL)                 (NULL)   (NULL)   (NULL)                    155044    100.00  Using where  
 2  DEPENDENT SUBQUERY  b       (NULL)      eq_ref  PRIMARY,idx_user_name  PRIMARY  98       settlement_data.a.pay_id       1      5.00  Using where

inner join 下优化器实现：

UPDATE pay_stream a INNER JOIN pay_main b ON a.pay_id = b.pay_id SET a.return_amount = 0
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  Using index  
 1  UPDATE       a       (NULL)      eq_ref  PRIMARY                PRIMARY        98       settlement_data.b.pay_id       1    100.00  (NULL)    DELETE a FROM pay_stream a INNER JOIN pay_main b ON a.pay_id = b.pay_id  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  Using index  
 1  DELETE       a       (NULL)      eq_ref  PRIMARY                PRIMARY        98       settlement_data.b.pay_id       1    100.00  (NULL)


从上述的优化器行为不难看出，inner join 联表的情况下，只对需更新的数据行加索，并发性能最高；exitsts 子查询在 delete 与 update 操作下，均为全索引扫描，并发最差；in 子查询在 update 操作下与 exists 一样为全索引扫描，而在 delete 操作下为主键操作，只对对应的行更新的数据行加索，并发次之。

not in /not exists 子查询 

not in 子查询下优化器实现：

UPDATE pay_stream a SET a.return_amount = 0
WHERE a.pay_id NOT IN (SELECT b.pay_id

 FROM pay_main b WHERE  b.pay_time > '2017-08-12 00:00:00'); 

id  select_type         table   partitions  type             possible_keys                  key      key_len  ref       rows  filtered  Extra        ------  ------------------  ------  ----------  ---------------  -----------------------------  -------  -------  ------  ------  --------  -------------
 1  UPDATE              a       (NULL)      index            (NULL)                         PRIMARY  98       (NULL)  155182    100.00  Using where  
 2  DEPENDENT SUBQUERY  b       (NULL)      unique_subquery  PRIMARY,IDX_PAY_MAIN_PAY_TIME  PRIMARY  98       func         1     46.46  Using where  DELETE a FROM pay_stream a WHERE a.pay_id NOT IN (SELECT b.pay_id

FROM pay_main b WHERE b.pay_time >= '2017-08-12 00:00:00');

id  select_type         table   partitions  type             possible_keys                  key      key_len  ref       rows  filtered  Extra        ------  ------------------  ------  ----------  ---------------  -----------------------------  -------  -------  ------  ------  --------  -------------
 1  DELETE              a       (NULL)      ALL              (NULL)                         (NULL)   (NULL)   (NULL)  155182    100.00  Using where  
 2  DEPENDENT SUBQUERY  b       (NULL)      unique_subquery  PRIMARY,IDX_PAY_MAIN_PAY_TIME  PRIMARY  98       func         1     46.46  Using where


not exists 子查询下优化器实现：

UPDATE pay_stream a SET a.return_amount = 0
WHERE NOT EXISTS (SELECT b.pay_id

FROM pay_main b WHERE a.pay_id = b.pay_id AND b.pay_time > '2017-08-12 00:00:00');

id  select_type         table   partitions  type    possible_keys                  key      key_len  ref                rows  filtered  Extra        ------  ------------------  ------  ----------  ------  -----------------------------  -------  -------  ---------------  ------  --------  -------------
 1  UPDATE              a       (NULL)      index   (NULL)                         PRIMARY  98       (NULL)           155182    100.00  Using where  
 2  DEPENDENT SUBQUERY  b       (NULL)      eq_ref  PRIMARY,IDX_PAY_MAIN_PAY_TIME  PRIMARY  98       settle.a.pay_id       1     46.46  Using whereDELETE a FROM pay_stream a WHERE NOT EXISTS (SELECT 1
      FROM pay_main b         WHERE a.pay_id = b.pay_id AND b.pay_time >= '2017-08-12 00:00:00');

id  select_type         table   partitions  type    possible_keys                  key      key_len  ref                rows  filtered  Extra        ------  ------------------  ------  ----------  ------  -----------------------------  -------  -------  ---------------  ------  --------  -------------
 1  DELETE              a       (NULL)      ALL     (NULL)                         (NULL)   (NULL)   (NULL)           155182    100.00  Using where  
 2  DEPENDENT SUBQUERY  b       (NULL)      eq_ref  PRIMARY,IDX_PAY_MAIN_PAY_TIME  PRIMARY  98      settle.a.pay_id       1     46.46  Using where

left join 下优化器实现：

UPDATE pay_stream a LEFT JOIN pay_main b
ON a.pay_id = b.pay_id AND b.pay_time >= '2017-08-12 00:00:00'
SET a.return_amount = 0
WHERE b.pay_id IS NULL;

id  select_type  table   partitions  type    possible_keys                  key      key_len  ref                rows  filtered  Extra                    ------  -----------  ------  ----------  ------  -----------------------------  -------  -------  ---------------  ------  --------  -------------------------
 1  UPDATE       a       (NULL)      ALL     (NULL)                         (NULL)   (NULL)   (NULL)           155182    100.00  (NULL)                   
 1  SIMPLE       b       (NULL)      eq_ref  PRIMARY,IDX_PAY_MAIN_PAY_TIME  PRIMARY  98       settle.a.pay_id       1    100.00  Using where; Not exists  DELETE a FROM pay_stream a LEFT JOIN pay_main b

ON a.pay_id = b.pay_id AND b.pay_time >= '2017-08-12 00:00:00'
WHERE b.pay_id IS NULL;

id  select_type  table   partitions  type    possible_keys                  key      key_len  ref                rows  filtered  Extra                    ------  -----------  ------  ----------  ------  -----------------------------  -------  -------  ---------------  ------  --------  -------------------------
 1  DELETE       a       (NULL)      ALL     (NULL)                         (NULL)   (NULL)   (NULL)           155182    100.00  (NULL)                   
 1  SIMPLE       b       (NULL)      eq_ref  PRIMARY,IDX_PAY_MAIN_PAY_TIME  PRIMARY  98       settle.a.pay_id       1    100.00  Using where; Not exists


从上述优化器的行为分析不难看出，left join 完全持有 a 表表锁，其间表完全失去了并发写入、更新操作；not in 与 not exists 执行计划类似，delete 操作下持有表锁，完全不支持并发，update 操作下以 PRIMARY 索引全扫描的方式，锁住了表中数据行，阻碍了对表的 delete，update 操作，却不妨碍 insert 的并发操作，MySQL 5.6 之后的优化器对 not in 子查询做了相关优化工作，检索效率高于 not exists。综上所述：delete、update下的 not in 子查询性能和并发度最高。

MySQL 优化器以及 InnoDB 行锁机制特性，增加了 UPDATE、DELETE 下子查询复杂的度，在 MySQL 数据库程序开发数据库维护过程中，真正了解优化器的实现和 InnoDB 行锁机制的行为，才有能设计出正真的高并发系统和更好的运维数据库。

原文发布时间为：2018-07-26
本文作者：蔡亮

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

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像