原创

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');
  • 1
  • 2
  • 3
  • 4
  • 5
    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 
  • 1
  • 2
  • 3
  • 4

从执行计划可以看出该 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';
  • 1
  • 2
  • 3
    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)  
  • 1
  • 2
  • 3
  • 4
  • 5

从执行计划可以看出,优化器先执行了通过 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) 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

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 where DELETE 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 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

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) 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

从上述的优化器行为不难看出,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 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

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 where DELETE 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 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19

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  
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18

从上述优化器的行为分析不难看出,left join 完全持有 a 表表锁,其间完全失去了并发;not in 与 not exists 执行计划类似,delete 操作下持有表锁,完全不支持并发,update 操作下以 PRIMARY 索引全扫描的方式,锁住了表中数据行,阻碍了对表的 delete,update 操作,但不妨碍 insert 的并发操作,但 (DEPENDENT SUBQUERY) 相关子查询下 unique_subquery 的检索效率高于 eq_ref 方式,delete、update下的 not in 子查询性能和并发支持最高。

MySQL 优化器以及 InnoDB 行锁机制特性,增加了 UPDATE、DELETE 下子查询复杂的度,数据库技术也在不断进步,在 MySQL 数据库程序开发数据库维护过程中,真正了解优化器的实现和 InnoDB 行锁机制的行为,才有能设计出正真的高并发系统和形成良好的运维习惯。

正文到此结束
Loading...