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 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 行锁机制的行为，才有能设计出正真的高并发系统和更好的运维数据库。

作者：蔡亮。

转载：意向文章下方留言。