delete in子查询会不会索引?
前言
文章开篇前,先问大家一个问题:delete in子查询,是否会走索引呢?很多伙伴第一感觉就是:会走索引。最近我们出了个生产问题,就跟它有关。本文将跟大家一起探讨这个问题,并附上优化方案。
问题复现
MySQL版本是5.7
,假设当前有两张表account
和old_account
,表结构如下:
CREATE TABLE `old_account` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
`name` varchar(255) DEFAULT NULL COMMENT '账户名',
`balance` int(11) DEFAULT NULL COMMENT '余额',
`create_time` datetime NOT NULL COMMENT '创建时间',
`update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='老的账户表';
CREATE TABLE `account` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
`name` varchar(255) DEFAULT NULL COMMENT '账户名',
`balance` int(11) DEFAULT NULL COMMENT '余额',
`create_time` datetime NOT NULL COMMENT '创建时间',
`update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';
执行的SQL如下:
delete from account where name in (select name from old_account);
我们explain执行计划走一波,
从explain
结果可以发现:先全表扫描 account
,然后逐行执行子查询判断条件是否满足;显然,这个执行计划和我们预期不符合,因为并没有走索引。
但是如果换成把delete
换成select
,就会走索引。如下:
为什么select in子查询会走索引,delete in子查询却不会走索引呢?
原因分析
select in
子查询语句跟delete in
子查询语句的不同点到底在哪里呢?
我们执行以下SQL看看
explain select * from account where name in (select name from old_account);
show WARNINGS;
show WARNINGS 可以查看优化后,最终执行的sql
结果如下:
select `test2`.`account`.`id` AS `id`,`test2`.`account`.`name` AS `name`,`test2`.`account`.`balance` AS `balance`,`test2`.`account`.`create_time` AS `create_time`,`test2`.`account`.`update_time` AS `update_time` from `test2`.`account`
semi join (`test2`.`old_account`)
where (`test2`.`account`.`name` = `test2`.`old_account`.`name`)
可以发现,实际执行的时候,MySQL对select in子查询做了优化,把子查询改成join的方式,所以可以走索引。但是很遗憾,对于delete in子查询,MySQL却没有对它做这个优化。
优化方案
那如何优化这个问题呢?通过上面的分析,显然可以把delete in子查询
改为join的方式。我们改为join的方式后,再explain看下:
可以发现,改用join的方式是可以走索引的,完美解决了这个问题。
实际上,对于update或者delete子查询的语句,MySQL官网也是推荐join的方式优化
其实呢,给表加别名,也可以解决这个问题哦,如下:
explain delete a from account as a where a.name in (select name from old_account)
为什么加别个名就可以走索引了呢?
what?为啥加个别名,delete in子查询又行了,又走索引了?
我们回过头来看看explain的执行计划,可以发现Extra那一栏,有个LooseScan。
LooseScan是什么呢? 其实它是一种策略,是semi join子查询的一种执行策略。
因为子查询改为join,是可以让delete in子查询走索引;而加别名,会走LooseScan策略,而LooseScan策略,本质上就是semi join子查询的一种执行策略。
因此,加别名就可以让delete in子查询走索引啦!
总结
- 本博文分析了delete in子查询不走索引的原因,并附上解决方案。
delete in
在日常开发,是非常常见的,平时大家工作中,需要注意一下。同时呢,建议大家工作的时候,写SQL的时候,尽量养成一个好习惯,先用explain分析一下SQL。
这篇好文章是转载于:学新通技术网
- 版权申明: 本站部分内容来自互联网,仅供学习及演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,请提供相关证据及您的身份证明,我们将在收到邮件后48小时内删除。
- 本站站名: 学新通技术网
- 本文地址: /boutique/detail/tanfbeke
-
photoshop保存的图片太大微信发不了怎么办
PHP中文网 06-15 -
Android 11 保存文件到外部存储,并分享文件
Luke 10-12 -
word里面弄一个表格后上面的标题会跑到下面怎么办
PHP中文网 06-20 -
《学习通》视频自动暂停处理方法
HelloWorld317 07-05 -
photoshop扩展功能面板显示灰色怎么办
PHP中文网 06-14 -
微信公众号没有声音提示怎么办
PHP中文网 03-31 -
excel下划线不显示怎么办
PHP中文网 06-23 -
怎样阻止微信小程序自动打开
PHP中文网 06-13 -
excel打印预览压线压字怎么办
PHP中文网 06-22 -
TikTok加速器哪个好免费的TK加速器推荐
TK小达人 10-01