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 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   

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 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  

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

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值