登录  /  注册

mysql中delete in子查询不走索引问题分析

WBOY
发布: 2022-09-08 17:46:34
转载
2477人浏览过

推荐学习:mysql视频教程

文章开篇前,先问大家一个问题:delete in子查询,是否会走索引呢?很多伙伴第一感觉就是:会走索引。最近我们有个生产问题,就跟它有关。本文将跟大家一起探讨这个问题,并附上优化方案。

问题复现

MySQL版本是5.7,假设当前有两张表accountold_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中文网其它相关文章!

智能AI问答
PHP中文网智能助手能迅速回答你的编程问题,提供实时的代码和解决方案,帮助你解决各种难题。不仅如此,它还能提供编程资源和学习指导,帮助你快速提升编程技能。无论你是初学者还是专业人士,AI智能助手都能成为你的可靠助手,助力你在编程领域取得更大的成就。
来源:脚本之家网
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
最新问题
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板
关于我们 免责申明 意见反馈 讲师合作 广告合作 最新更新
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号 技术交流群
PHP中文网订阅号
每天精选资源文章推送
PHP中文网APP
随时随地碎片化学习
PHP中文网抖音号
发现有趣的

Copyright 2014-2024 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号