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

作者:蔡亮。

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

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2018-07-25

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏撸码那些事

MySQL——索引优化实战

上篇文章中介绍了索引的基本内容,这篇文章我们继续介绍索引优化实战。在介绍索引优化实战之前,首先要介绍两个与索引相关的重要概念,这两个概念对于索引优化至关重要。

1432
来自专栏Netkiller

新闻数据库分表案例

文章节选自《Netkiller Architect 手札》 6.3. 新闻数据库分表案例 这里我通过一个新闻网站为例,解决分表的问题 避免开发中经常拼接表,我采...

4126
来自专栏MYSQL轻松学

MYSQL常用SQL汇总

1、查看当前应用连接,连接数突增排查 select user,SUBSTRING_INDEX(host,':',1) as ip , count(*) as c...

3886
来自专栏北京马哥教育

【干货】zabbix报警信息提取 |从数据库入手

zabbix报警信息提取 在日常的监控中,我们除了日常的zabbix操作外,我们有的时候还涉及到与其他公司 进行数据对接。由于别的公司的数据对接很多时候并不是按...

3995
来自专栏性能与架构

Mysql 索引你了解多少?

前言 Mysql 的索引是我们常用的,但实际了解多少呢?下面通过几个案例小问题来测验下,后面会有答案及相关解释 测试问题 问题1 下面的索引适合这个查询吗? C...

3589
来自专栏web编程技术分享

第三节 - mysql 建表

3825
来自专栏技术博文

discuz 表结构

discuz 主题表pre_forum_thread 注解 tid mediumint(8) unsigned NOT NULL auto_increment ...

8687
来自专栏乐沙弥的世界

MySQL SQL更新锁定

版权声明:本文为博主原创文章,欢迎扩散,扩散请务必注明出处。

2112
来自专栏撸码那些事

MySQL——索引优化实战

上篇文章中介绍了索引的基本内容,这篇文章我们继续介绍索引优化实战。在介绍索引优化实战之前,首先要介绍两个与索引相关的重要概念,这两个概念对于索引优化至关重要。

964
来自专栏PhpZendo

使用 MySQL Scheduler 和 Event 周期性创建数据表

使用 MySQL Scheduler 和 Event 周期性创建数据表,下面提供的是按月建表计划任务及事件通过 ON SCHEDULE EVERY 1 MINU...

2002

扫码关注云+社区

领取腾讯云代金券