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

如何解决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微信为好友

· 分享WordPress相关技术文章，主题上新与优惠动态早知道。
· 微信端最大WordPress社群，限时免费入群。