翼度科技»论坛 编程开发 mysql 查看内容

这样delete居然不走索引

10

主题

10

帖子

30

积分

新手上路

Rank: 1

积分
30
背景

由于业务变迁,合规要求,我们需要删除大量非本公司的数据,涉及到上百张表,几个T的数据清洗。我们的做法是先从基础数据出发,将要删除的数据id收集到一张表,然后再由上往下删除子表,多线程并发处理。
我们使用的是阿里的polardb,完全兼容mysql协议,5.7版本,RC隔离级别。删除过程一直很顺利,突然有一天报了大量:“Lock wait timeout exceeded; try restarting transaction”。从日志上看是获取锁失败了,马上想到出现死锁了,但我们使用RC,这个隔离级别下会出现不可重复读和幻读,但没有间隙锁等,并发效率比较高,在我们实际应用过程中,也很少遇到加锁失败的问题。
单从日志看我们确实先入为主了,以为是死锁问题,但sql比较简单,表数据量在千万级别,其中task_id和uid均有索引,如下:
  1. delete from t_table_1 where task_id in (select id from t_table_2 where uid = #{uid})
复制代码
拿到报错的参数,查询要删除的数据也不多,联系dba同学确认没有死锁日志,但出现大量慢sql,那为什么这条sql会是慢sql呢?
问题复现

表结构简化如下:
  1. CREATE TABLE `t_table_1` (
  2.   `id` bigint(20) NOT NULL AUTO_INCREMENT,
  3.   `task_id` bigint(20) NOT NULL,
  4.   PRIMARY KEY (`id`),
  5.   KEY `idx_task_id` (`task_id`)
  6. ) ENGINE=InnoDB;
  7. CREATE TABLE `t_table_2` (
  8.   `id` bigint(20) NOT NULL AUTO_INCREMENT,
  9.   `uid` bigint(20) NOT NULL,
  10.   PRIMARY KEY (`id`),
  11.   KEY `idx_uid` (`uid`)
  12. ) ENGINE=InnoDB;
复制代码
开始我们拿sql到数据库查询平台查库执行计划,无奈这个平台有bug,delete语句无法查看,所以我们改成select,“应该”是一样。这个“应该”加了双引号,导致我们走了一点弯路。
  1. EXPLAIN SELECT * from t_table_1 where task_id in (select id from t_table_2 where uid = 1)
复制代码
explain后可以看到是走了索引的

到这里可以总结:
1.没有死锁,这点比较肯定,因为没有日志,也符合我们的理解。
2.有慢sql,这点比较奇怪,通过explain select语句是走索引的,但数据库慢日志记录到,全表扫描,不会错。
那是select和delete的执行计划不同吗?正常来说应该是一样的,delete无非就是先查,加锁,再删。
拿到本地环境执行再次查看执行计划,发现确实不同,select的是一样的,但delete的变成全表扫描了。

首先这就符合问题现象了,虽然没有死锁,但每个delete语句都全表扫描,相当于全表加锁,后面的请求就只能等待释放锁,等到超时就出现“Lock wait timeout exceeded”。
那为什么delete会不走索引呢,接下来我们分析一下。
分析
  1. select * from t_table_1 where task_id in (select id from t_table_2 where uid = #{uid})
复制代码
回到这条简单sql,包含子查询,按照我们的理解,mysql应该是先执行子查询:select id from t_table_2 where uid = #{uid},然后再执行外部查询:select * from t_table_1 where task_id in(),但这不一定,例如我关了这个参数:
  1. set optimizer_switch='semijoin=off';
复制代码
这里我们先不用管这个参数的作用,下面会说到。
关闭后上面的sql就变成先扫描外部的t_table_1,然后再逐行去匹配子查询了,假设t_table_1的数据量非常大,那全表扫描时间就会很长,我们可以通过optimizer_trace证明一下。
optimizer_trace是mysql一个跟踪功能,可以跟踪优化器做的各种决策,包括sql改写,成本计算,索引选择详细过程,并将跟踪结果记录到INFORMATION_SCHEMA.OPTIMIZER_TRACE表中。
  1. set session optimizer_trace="enabled=on";
  2. set OPTIMIZER_TRACE_MAX_MEM_SIZE=10000000; -- 防止内容过多被截断   
  3. SELECT * from t_table_1 where task_id in (select id from t_table_2 where uid = 1)
  4. SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
复制代码
输出结果比较长,这里我只挑选主要信息
  1. "steps": [
  2.     {
  3.         "expanded_query": "/* select#2 */ select `t_table_2`.`id` from `t_table_2` where (`t_table_2`.`uid` = 1)"
  4.     },
  5.     {
  6.         "transformation": {
  7.             "select#": 2,
  8.             "from": "IN (SELECT)",
  9.             "to": "semijoin",
  10.             "chosen": false
  11.         }
  12.     },
  13.     {
  14.         "transformation": {
  15.             "select#": 2,
  16.             "from": "IN (SELECT)",
  17.             "to": "EXISTS (CORRELATED SELECT)",
  18.             "chosen": true,
  19.             "evaluating_constant_where_conditions": [
  20.             ]
  21.         }
  22.     }
  23. ]
  24. "expanded_query": "/* select#1 */ select `t_table_1`.`id` AS `id`,`t_table_1`.`task_id` AS `task_id` from `t_table_1` where <in_optimizer>(`t_table_1`.`task_id`,<exists>(/* select#2 */ select `t_table_2`.`id` from `t_table_2` where ((`t_table_2`.`uid` = 1) and (<cache>(`t_table_1`.`task_id`) = `t_table_2`.`id`)))) limit 0,1000"
复制代码
sql简写一下就是
  1. select * from t_table_1 t1 where exists (select t2.id from t_table_2 t2 where t2.uid = 1 and t1.task_id = t2.id)
复制代码
可以看到in可以改成semijoin或exists,最终优化器选择了exists,因为我们关闭了semijoin开关。
按照这条sql逻辑查询,将会遍历t_table_1表的每一行,然后代入子查询看是否匹配,当t_table_1表的行数很多时,耗时将会很长。
通过explain观察执行计划可以看到t_table_1进行了全表扫描。
备注:想查看优化器改下后生成的sql,也可以通过show extended + show warnings:
  1. explain extended SELECT * from t_table_1 where task_id in (select id from t_table_2 where uid = 1);
  2. show warnings;
复制代码
接着我们打开上面的参数开关,再次optimizer_trace跟踪一下
  1. set optimizer_switch='semijoin=on';
复制代码
得到如下:
  1. "steps": [
  2.     {
  3.         "expanded_query": "/* select#2 */ select `t_table_2`.`id` from `t_table_2` where (`t_table_2`.`uid` = 1)"
  4.     },
  5.     {
  6.         "transformation": {
  7.             "select#": 2,
  8.             "from": "IN (SELECT)",
  9.             "to": "semijoin",
  10.             "chosen": true
  11.         }
  12.     }
  13. ]
  14. "expanded_query": "/* select#1 */ select `t_table_1`.`id` AS `id`,`t_table_1`.`task_id` AS `task_id` from `t_table_1` semi join (`t_table_2`) where (1 and (`t_table_2`.`uid` = 1) and (`t_table_1`.`task_id` = `t_table_2`.`id`)) limit 0,1000"
复制代码
sql简写一下就是
  1. select * from t_table_1 semi join t_table_2 where (`t_table_2`.`uid` = 1 and `t_table_1`.`task_id` = `t_table_2`.`id`)"
复制代码
可以看到优化器这次选择将in转换成semijoin了,观察执行计划可以看到走了索引。
那如果换成delete呢?同样保持开关打开,跟踪如下:
  1. "steps": [
  2.     {
  3.         "expanded_query": "/* select#2 */ select `t_table_2`.`id` from `t_table_2` where (`t_table_2`.`uid` = 1)"
  4.     },
  5.     {
  6.         "transformation": {
  7.             "select#": 2,
  8.             "from": "IN (SELECT)",
  9.             "to": "semijoin",
  10.             "chosen": false
  11.         }
  12.     },
  13.     {
  14.         "transformation": {
  15.             "select#": 2,
  16.             "from": "IN (SELECT)",
  17.             "to": "EXISTS (CORRELATED SELECT)",
  18.             "chosen": true,
  19.             "evaluating_constant_where_conditions": [
  20.             ]
  21.         }
  22.     }
  23. ]
复制代码
可以看到和关闭semijoin一样,对于delete优化器也是选择了exists,我们表是千万级别,全表扫描加锁,其它操作语句自然都会超时获取不到锁而失败。
semijoin

semijoin翻译过来是半连接,是mysql针对in/exists子查询进行优化的一种技术,参见文档
可以使用SHOW VARIABLES LIKE 'optimizer_switch';查看semijoin是否开启。
上面使用IN-TO-EXISTS改写后,外层表变成驱动表,效率很差,那如果使用inner join呢,使用条件过滤后,用小表驱动大表,但join查询结果是会重复的,和子查询语义不一定相同。如:
  1. SELECT class.class_num, class.class_name
  2.     FROM class
  3.     INNER JOIN roster
  4.     WHERE class.class_num = roster.class_num;
复制代码
这样会查询出多条相同class_num的记录,如果子查询,那么查询出来的class_num是不一样的,也就是去重。当然也可以加上distinct,但这样效率比较低。
  1. SELECT class_num, class_name
  2.     FROM class
  3.     WHERE class_num IN
  4.         (SELECT class_num FROM roster);
复制代码
semijoin有以下几种策略,以下是官方的解释:
  1. Duplicate Weedout: Run the semijoin as if it was a join and remove duplicate records using a temporary table.
  2. FirstMatch: When scanning the inner tables for row combinations and there are multiple instances of a given value group, choose one rather than returning them all. This "shortcuts" scanning and eliminates production of unnecessary rows.
  3. LooseScan: Scan a subquery table using an index that enables a single value to be chosen from each subquery's value group.
  4. Materialize the subquery into an indexed temporary table that is used to perform a join, where the index is used to remove duplicates. The index might also be used later for lookups when joining the temporary table with the outer tables; if not, the table is scanned. For more information about materialization, see Section 8.2.2.2, “Optimizing Subqueries with Materialization”.
复制代码
以Duplicate Weedout为例,mysql会先将roster的记录以class_num为主键添加到一张临时表,达到去重的目的。接着扫描临时表,每行去匹配外层表,满足条件则放到结果集,最终返回。
具体使用哪种策略是优化器根据具体情况分析得出的,可以从explain的extra字段看到。
那么为什么delete没有使用semijoin优化呢?
这其实是mysql的一个bug,bug地址,描述场景和我们的一样。
文中还提到这个问题在mysql 8.0.21被修复,地址

大致就是解释了一下之前版本没有支持的原因,提到主要是因为单表没有可以JOIN的对象,没法进行一系列的优化,所以单表的UPDATE/DELETE是无法用semijoin优化的。
这个优化还有一些限制,例如不能使用order by和limit,我们还是应该尽量避免使用子查询。
在我们的场景通过将子查询改写为join即可走索引,现在也明白为什么老司机们都说尽量用join代替了子查询了吧。
更多分享,欢迎关注我的github:https://github.com/jmilktea/jtea

来源:https://www.cnblogs.com/jtea/p/17927541.html
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?立即注册

x

举报 回复 使用道具