SQL优化之一则MySQL中的DELETE、UPDATE 子查询的锁机制失效案例-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

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

简介: MySQL 下的 InnoDB 行锁,是通过以位图方式对 index page 加锁机制来实现的。

前言

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

事物、锁验证:

事物一:

image


事物二:

image

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

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

优化器实现:

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

事物一:

image

事物二:

image

不难看出 普通 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
本文作者:蔡亮

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章
最新文章
相关文章