推荐学习:mysql视频教程
文章开篇前,先问大家一个问题: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子查询走索引啦!
推荐学习:mysql视频教程
以上是mysql中delete in子查询不走索引问题分析的详细内容。更多信息请关注PHP中文网其他相关文章!

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于架构原理的相关内容,MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层,下面一起来看一下,希望对大家有帮助。

mysql的msi与zip版本的区别:1、zip包含的安装程序是一种主动安装,而msi包含的是被installer所用的安装文件以提交请求的方式安装;2、zip是一种数据压缩和文档存储的文件格式,msi是微软格式的安装包。

方法:1、利用right函数,语法为“update 表名 set 指定字段 = right(指定字段, length(指定字段)-1)...”;2、利用substring函数,语法为“select substring(指定字段,2)..”。

在mysql中,可以利用char()和REPLACE()函数来替换换行符;REPLACE()函数可以用新字符串替换列中的换行符,而换行符可使用“char(13)”来表示,语法为“replace(字段名,char(13),'新字符串') ”。

转换方法:1、利用cast函数,语法“select * from 表名 order by cast(字段名 as SIGNED)”;2、利用“select * from 表名 order by CONVERT(字段名,SIGNED)”语句。

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于MySQL复制技术的相关问题,包括了异步复制、半同步复制等等内容,下面一起来看一下,希望对大家有帮助。

在mysql中,可以利用REGEXP运算符判断数据是否是数字类型,语法为“String REGEXP '[^0-9.]'”;该运算符是正则表达式的缩写,若数据字符中含有数字时,返回的结果是true,反之返回的结果是false。

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了mysql高级篇的一些问题,包括了索引是什么、索引底层实现等等问题,下面一起来看一下,希望对大家有帮助。


热AI工具

Undresser.AI Undress
人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool
免费脱衣服图片

Clothoff.io
AI脱衣机

AI Hentai Generator
免费生成ai无尽的。

热门文章

热工具

记事本++7.3.1
好用且免费的代码编辑器

PhpStorm Mac 版本
最新(2018.2.1 )专业的PHP集成开发工具

SublimeText3 Mac版
神级代码编辑软件(SublimeText3)

EditPlus 中文破解版
体积小,语法高亮,不支持代码提示功能

mPDF
mPDF是一个PHP库,可以从UTF-8编码的HTML生成PDF文件。原作者Ian Back编写mPDF以从他的网站上“即时”输出PDF文件,并处理不同的语言。与原始脚本如HTML2FPDF相比,它的速度较慢,并且在使用Unicode字体时生成的文件较大,但支持CSS样式等,并进行了大量增强。支持几乎所有语言,包括RTL(阿拉伯语和希伯来语)和CJK(中日韩)。支持嵌套的块级元素(如P、DIV),