这样delete居然不走索引

这篇具有很好参考价值的文章主要介绍了这样delete居然不走索引。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

背景

由于业务变迁,合规要求,我们需要删除大量非本公司的数据,涉及到上百张表,几个T的数据清洗。我们的做法是先从基础数据出发,将要删除的数据id收集到一张表,然后再由上往下删除子表,多线程并发处理。
我们使用的是阿里的polardb,完全兼容mysql协议,5.7版本,RC隔离级别。删除过程一直很顺利,突然有一天报了大量:“Lock wait timeout exceeded; try restarting transaction”。从日志上看是获取锁失败了,马上想到出现死锁了,但我们使用RC,这个隔离级别下会出现不可重复读和幻读,但没有间隙锁等,并发效率比较高,在我们实际应用过程中,也很少遇到加锁失败的问题。

单从日志看我们确实先入为主了,以为是死锁问题,但sql比较简单,表数据量在千万级别,其中task_id和uid均有索引,如下:

delete from t_table_1 where task_id in (select id from t_table_2 where uid = #{uid})

拿到报错的参数,查询要删除的数据也不多,联系dba同学确认没有死锁日志,但出现大量慢sql,那为什么这条sql会是慢sql呢?

问题复现

表结构简化如下:

CREATE TABLE `t_table_1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `task_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_task_id` (`task_id`)
) ENGINE=InnoDB;

CREATE TABLE `t_table_2` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `uid` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_uid` (`uid`)
) ENGINE=InnoDB;

开始我们拿sql到数据库查询平台查库执行计划,无奈这个平台有bug,delete语句无法查看,所以我们改成select,“应该”是一样。这个“应该”加了双引号,导致我们走了一点弯路。

EXPLAIN SELECT * from t_table_1 where task_id in (select id from t_table_2 where uid = 1)

explain后可以看到是走了索引的

这样delete居然不走索引

到这里可以总结:
1.没有死锁,这点比较肯定,因为没有日志,也符合我们的理解。
2.有慢sql,这点比较奇怪,通过explain select语句是走索引的,但数据库慢日志记录到,全表扫描,不会错。

那是select和delete的执行计划不同吗?正常来说应该是一样的,delete无非就是先查,加锁,再删。
拿到本地环境执行再次查看执行计划,发现确实不同,select的是一样的,但delete的变成全表扫描了。

这样delete居然不走索引

首先这就符合问题现象了,虽然没有死锁,但每个delete语句都全表扫描,相当于全表加锁,后面的请求就只能等待释放锁,等到超时就出现“Lock wait timeout exceeded”。
那为什么delete会不走索引呢,接下来我们分析一下。

分析

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(),但这不一定,例如我关了这个参数:

set optimizer_switch='semijoin=off';

这里我们先不用管这个参数的作用,下面会说到。
关闭后上面的sql就变成先扫描外部的t_table_1,然后再逐行去匹配子查询了,假设t_table_1的数据量非常大,那全表扫描时间就会很长,我们可以通过optimizer_trace证明一下。
optimizer_trace是mysql一个跟踪功能,可以跟踪优化器做的各种决策,包括sql改写,成本计算,索引选择详细过程,并将跟踪结果记录到INFORMATION_SCHEMA.OPTIMIZER_TRACE表中。

set session optimizer_trace="enabled=on";
set OPTIMIZER_TRACE_MAX_MEM_SIZE=10000000; -- 防止内容过多被截断   
SELECT * from t_table_1 where task_id in (select id from t_table_2 where uid = 1)
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

输出结果比较长,这里我只挑选主要信息

"steps": [
    {
        "expanded_query": "/* select#2 */ select `t_table_2`.`id` from `t_table_2` where (`t_table_2`.`uid` = 1)"
    },
    {
        "transformation": {
            "select#": 2,
            "from": "IN (SELECT)",
            "to": "semijoin",
            "chosen": false
        }
    },
    {
        "transformation": {
            "select#": 2,
            "from": "IN (SELECT)",
            "to": "EXISTS (CORRELATED SELECT)",
            "chosen": true,
            "evaluating_constant_where_conditions": [
            ]
        }
    }
]

"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简写一下就是

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:

explain extended SELECT * from t_table_1 where task_id in (select id from t_table_2 where uid = 1);
show warnings;

接着我们打开上面的参数开关,再次optimizer_trace跟踪一下

set optimizer_switch='semijoin=on';

得到如下:

"steps": [
    {
        "expanded_query": "/* select#2 */ select `t_table_2`.`id` from `t_table_2` where (`t_table_2`.`uid` = 1)"
    },
    {
        "transformation": {
            "select#": 2,
            "from": "IN (SELECT)",
            "to": "semijoin",
            "chosen": true
        }
    }
]

"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简写一下就是

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呢?同样保持开关打开,跟踪如下:

"steps": [
    {
        "expanded_query": "/* select#2 */ select `t_table_2`.`id` from `t_table_2` where (`t_table_2`.`uid` = 1)"
    },
    {
        "transformation": {
            "select#": 2,
            "from": "IN (SELECT)",
            "to": "semijoin",
            "chosen": false
        }
    },
    {
        "transformation": {
            "select#": 2,
            "from": "IN (SELECT)",
            "to": "EXISTS (CORRELATED SELECT)",
            "chosen": true,
            "evaluating_constant_where_conditions": [
            ]
        }
    }
]

可以看到和关闭semijoin一样,对于delete优化器也是选择了exists,我们表是千万级别,全表扫描加锁,其它操作语句自然都会超时获取不到锁而失败。

semijoin

semijoin翻译过来是半连接,是mysql针对in/exists子查询进行优化的一种技术,参见文档。
可以使用SHOW VARIABLES LIKE 'optimizer_switch';查看semijoin是否开启。
上面使用IN-TO-EXISTS改写后,外层表变成驱动表,效率很差,那如果使用inner join呢,使用条件过滤后,用小表驱动大表,但join查询结果是会重复的,和子查询语义不一定相同。如:

SELECT class.class_num, class.class_name
    FROM class
    INNER JOIN roster
    WHERE class.class_num = roster.class_num;

这样会查询出多条相同class_num的记录,如果子查询,那么查询出来的class_num是不一样的,也就是去重。当然也可以加上distinct,但这样效率比较低。

SELECT class_num, class_name
    FROM class
    WHERE class_num IN
        (SELECT class_num FROM roster);

semijoin有以下几种策略,以下是官方的解释:

Duplicate Weedout: Run the semijoin as if it was a join and remove duplicate records using a temporary table.

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.

LooseScan: Scan a subquery table using an index that enables a single value to be chosen from each subquery's value group.

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被修复,地址

这样delete居然不走索引

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

更多分享,欢迎关注我的github:https://github.com/jmilktea/jtea文章来源地址https://www.toymoban.com/news/detail-760500.html

到了这里,关于这样delete居然不走索引的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 如何更换电脑背景图片?这样做很简单!

    【我的电脑背景图片是系统自带的壁纸,不太好看,我想把它换成自己的照片。有没有小伙伴知道电脑怎么更换背景图片?在线求个简单的方法!】 在日常生活中,我们常常需要更换电脑背景图片来增强视觉体验或者个性化电脑界面。那么,如何更换 电脑背景图片 呢?如何

    2023年04月19日
    浏览(40)
  • ElasticSearch索引生命周期管理--DELETE

    概要 ElasticSearch中的索引生命周期管理,也就是ilm(Manage the index lifecycle),是指定了索引在不同周期下的处理策略。 ilm 的对象是索引而不是索引中的数据。 ilm 包括四个阶段:hot 、warm、cold和delete。 hot、warm和cold表示索引的使用情况,delete可以指定索引完成rollover后的旧索引

    2024年02月13日
    浏览(40)
  • 如何运用云服务开拓业务、加速创新?这家出海企业是这样做的

    对于中小企业和创业者来说,应该怎样运用云计算等新兴技术,来帮助企业开拓业务、加速创新呢? 作为业内首个“沉浸式体验直播互动元宇宙平台”,来自FunPlus游戏公司的Pwnk项目现身说法,相信能够为广大企业带来不少启示和借鉴。 互动游戏的全新玩法 众所周知,近年

    2024年02月13日
    浏览(41)
  • ElasticSearch删除索引中的数据(delete_by_query)

    在 Elasticsearch 中,要删除两个月以前的数据,可以通过以下步骤: 计算当前时间的两个月前的日期,可以使用 Python 的 datetime 模块来实现。 构造 Elasticsearch 的删除请求,使用 Elasticsearch-Py 库来与 Elasticsearch 进行交互。         这样就可以删除索引中两个月以前的数据。需

    2024年02月04日
    浏览(48)
  • Mysql中如果建立了索引,索引所占的空间随着数据量增长而变大,这样无论写入还是查询,性能都会有所下降,怎么处理?

    索引所占空间的增长确实会对MySQL数据库的写入性能和查询性能造成影响,这主要是由于索引数据过多时会导致磁盘I/O操作变得非常频繁,从而使性能下降。为此,可以采取以下几种方式来减缓这种影响:   1. 限制索引的大小:可以考虑为索引指定大小限制,在存储时仅存储

    2023年04月23日
    浏览(51)
  • Elasticsearch集群索引写入失败[FORBIDDEN/12/index read-only / allow delete (api)]处理流程

    操作系统:CentOS 7.3 软件版本:elasticsearch-6.7.2 正常将数据写入到Elasticsearch时,发现写入失败,出现如下报错 检查Elasticsearch集群的active master节点的日志,并没有发现error,但有WARN告警,显示与 flood stage disk watermark [90%] 有关。 上下文有 low disk watermark [80%] 的INFO日志信息,再次

    2024年02月10日
    浏览(58)
  • 8. 高性能业务表结构设计和索引知识深化

    本文是按照自己的理解进行笔记总结,如有不正确的地方,还望大佬多多指点纠正,勿喷。 本节课内容: 1.什么是表设计的第一、第二、第三范式? 2.什么叫反范式化设计? 3.工作中的反范式实践 4.InnoDB中的聚集索引和辅助索引 5.什么是回表和MRR? 6. InnoDB中的AHI自适应哈希索引

    2024年02月05日
    浏览(40)
  • 怎样使用GPT案例:使用GPT获得OPPO终止ZEKU芯片业务需要的背景知识

    以下引用 @郑昀的微博: #IT那些事儿# 神仙打架那些事儿 主角:美国公司高通(总部在加州),英国公司ARM(总部在剑桥,但2016年被孙正义收购,2022年孙正义从软银退隐之后将全身心投入到ARM的变革工作中) 第一回合:2021年1月13日,高通希望通过收购 Nuvia 推出自研 CPU 架构

    2024年02月06日
    浏览(44)
  • 使用okHttp不走代理问题

    背景 某日使用okhttp设置代理并发送爬虫请求时,发现部分url请求没有走代理直接和目标url建立了连接,伪代码如下。初始化okhttpClient时设置了proxySelecter代理,但是调用okhttpClient.newCall请求时并没用调用proxySelecter.select函数获取代理,日志也没有打印。 okHttp 代理 Android | 彻底理

    2024年02月14日
    浏览(38)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包