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

文 / @WordPress主题

如何解决MySQL中delete in子查询不走索引的问题?

在MySQL中,当我们使用delete in子查询的时候,很多人都有一个误解,即认为它会走索引。但是,最近的一个生产问题表明,这种想法是错误的。那么我们该如何解决这个问题呢?

先来看看问题的本质:

我们假设有两张表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的结果来看,它实际上是先全表扫描了account表,然后逐行执行子查询判断条件是否满足。很显然,这个执行计划并没有走索引,因此效率很低。

那么,我们该如何解决这个问题呢?

优化方案:

其实,我们可以把delete in子查询改为join的方式。这样做的原理是,MySQL对select in子查询做了优化,把子查询改成join的方式,所以可以走索引。但是很遗憾,对于delete in子查询,MySQL却没有对它做这个优化。

因此,我们的解决方案就是:将子查询改成join的方式或者给表加别名。

具体实现如下:

将子查询改成join的方式:

delete a from account a join old_account b on a.name = b.name;

可以发现,改用join的方式是可以走索引的,完美解决了这个问题。

给表加别名:

在使用delete in子查询的时候,给表加一个别名即可解决问题,如下所示:

delete a from account a where a.name in (select name from old_account);

可以发现,加别名后,就可以走索引了。

至此,MySQL中delete in子查询不走索引的问题就得到了解决。

结语:

通过本文的介绍,相信大家对MySQL中delete in子查询不走索引的问题有了更深入的了解。当我们在实际开发中遇到类似的问题时,就可以采取相应的优化措施了。

添加UTHEME为好友
扫码添加UTHEME微信为好友
· 分享WordPress相关技术文章,主题上新与优惠动态早知道。
· 微信端最大WordPress社群,限时免费入群。