生产问题分析!Delete in子查询不走索引?!

运维 数据库运维
本博文分析了delete in子查询不走索引的原因,并附上解决方案。delete in在日常开发,是非常常见的,平时大家工作中,需要注意一下。同时呢,建议大家工作的时候,写SQL的时候,尽量养成一个好习惯,先用explain分析一下SQL。

[[426486]]

前言

大家好,我是捡田螺的小男孩。(求个星标置顶)

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

问题复现

MySQL版本是5.7,假设当前有两张表account和old_account,表结构如下:

  1. CREATE TABLE `old_account` ( 
  2.   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id'
  3.   `namevarchar(255) DEFAULT NULL COMMENT '账户名'
  4.   `balance` int(11) DEFAULT NULL COMMENT '余额'
  5.   `create_time` datetime NOT NULL COMMENT '创建时间'
  6.   `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
  7.   PRIMARY KEY (`id`), 
  8.   KEY `idx_name` (`name`) USING BTREE 
  9. ) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='老的账户表'
  10.  
  11. CREATE TABLE `account` ( 
  12.   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id'
  13.   `namevarchar(255) DEFAULT NULL COMMENT '账户名'
  14.   `balance` int(11) DEFAULT NULL COMMENT '余额'
  15.   `create_time` datetime NOT NULL COMMENT '创建时间'
  16.   `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
  17.   PRIMARY KEY (`id`), 
  18.   KEY `idx_name` (`name`) USING BTREE 
  19. ) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表'

执行的SQL如下:

  1. 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看看

  1. explain select * from account where name in (select name from old_account); 
  2. show WARNINGS; 

show WARNINGS 可以查看优化后,最终执行的sql

结果如下:

  1. select `test2`.`account`.`id` AS `id`,`test2`.`account`.`nameAS `name`,`test2`.`account`.`balance` AS `balance`,`test2`.`account`.`create_time` AS `create_time`,`test2`.`account`.`update_time` AS `update_time` from `test2`.`account`  
  2. semi join (`test2`.`old_account`) 
  3. 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的方式优化

其实呢,给表加别名,也可以解决这个问题哦,如下:

  1. 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。

本文整体思路参考同事的博文,已经经过他本人同意。也建议大家遇到问题时,多点思考,多点写写总结,避免重蹈覆辙。

我是捡田螺的小男孩,码字不易,看完文章有收获的话,可以把我公众号推给身边的程序员哈,感谢、比心~

本文转载自微信公众号「捡田螺的小男孩」,可以通过以下二维码关注。转载本文请联系捡田螺的小男孩公众号。

 

责任编辑:武晓燕 来源: 捡田螺的小男孩
相关推荐

2022-02-24 07:48:47

MySQL索引查询

2020-08-26 08:18:39

数据索引查询

2011-08-18 14:10:51

Oracle不走索引

2021-08-23 13:02:50

MySQLJOIN数据库

2019-11-14 16:23:07

MySQL索引数据库

2024-02-26 08:13:51

MySQLSQL性能

2020-01-22 16:36:52

MYSQL开源数据库

2024-01-24 07:30:45

MySQL数据库索引

2018-11-20 10:10:54

Redis数据库模糊查询

2020-11-17 09:01:09

MySQLDelete数据

2023-02-24 08:19:59

MySQL索引失效

2020-12-09 08:59:59

MongoDB复合索事故

2022-06-28 15:46:18

SQL语句索引

2022-02-06 10:58:37

Redis主从模式

2023-12-16 13:14:00

SQL子查询技术

2010-10-12 14:40:03

mysql索引

2021-04-12 09:36:14

JVM生产问题JVM FULL GC

2023-03-07 08:22:34

MySQL优化器

2010-07-21 09:50:12

SQL Server子

2021-04-02 07:46:52

SQL Server数据库知识笔记
点赞
收藏

51CTO技术栈公众号