Analysis of the problem that delete in subquery does not go to the index in mysql

This article brings you relevant knowledge aboutmysql. It mainly introduces the analysis of the problem of delete in subquery without indexing. Friends in need can refer to it. I hope can help.

Recommended learning:mysql video tutorial

Before the article begins, let me ask you a question:delete in subquery , will it be indexed?? The first impression of many partners is that they know how to index. Recently we had a production problem related to it. This article will discuss this issue with everyone and attach an optimization plan.

Problem Reproduction

MySQL version is

, assuming there are currently two tablesaccountandold_account, the table structure is as follows:

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='账户表';

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

Let’s explain the execution plan,

From the

results, we can find: firstscan the whole tableaccount, and then execute the subquery row by row to determine whether the conditions are met; obviously, this execution plan and We expected it not to match, becausedid not go to index.But if

is replaced withselect, the index will be used. As follows:

Why does the

subquery go through the index, but the delete in subquery does not go through the index?Cause Analysis

subquery statement and thedelete insubquery statement?Let’s execute the following SQL to see

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

show WARNINGS

You can view the final executed sql after optimization

The results are as follows ：

semi join (`test2`.`old_account`)

where (`test2`.`account`.`name` = `test2`.`old_account`.`name`)

It can be found that during actual execution, MySQL optimized the

and changed the subquery to a join method, so the index can be used. But unfortunately, MySQL did not optimize it fordelete in subquery.Optimization plan

So how to optimize this problem? Through the above analysis, it is obvious that the delete in subquery can be changed to

. After we change to the join method, let's explain again:

#We can find that the change to the join method is

In fact, for update or delete subquery statements,MySQL official website

In fact, Adding an alias to the table can also solve this problem, as follows:

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

Why can adding an alias enable indexing?

what

Let’s go back and look at explain’s execution plan. We can find that in the Extra column, there isLooseScan

What is LooseScan?

semi join subquery.Because the subquery is changed to join, the delete in subquery can be indexed;If you add an alias

LooseScan strategy, and the LooseScan strategy is essentially It is an execution strategy ofsemi join subquery.Therefore, adding an alias allows the delete in subquery to be indexed!

mysql video tutorial

The above is the detailed content of Analysis of the problem that delete in subquery does not go to the index in mysql. For more information, please follow other related articles on the PHP Chinese website!