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

This article brings you relevant knowledge about mysql. 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

5.7

, assuming there are currently two tables account and old_account, the table structure is as follows: <pre class="brush:sql;">CREATE TABLE `old_account` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT &amp;#39;主键Id&amp;#39;, `name` varchar(255) DEFAULT NULL COMMENT &amp;#39;账户名&amp;#39;, `balance` int(11) DEFAULT NULL COMMENT &amp;#39;余额&amp;#39;, `create_time` datetime NOT NULL COMMENT &amp;#39;创建时间&amp;#39;, `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT &amp;#39;更新时间&amp;#39;, PRIMARY KEY (`id`), KEY `idx_name` (`name`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT=&amp;#39;老的账户表&amp;#39;; CREATE TABLE `account` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT &amp;#39;主键Id&amp;#39;, `name` varchar(255) DEFAULT NULL COMMENT &amp;#39;账户名&amp;#39;, `balance` int(11) DEFAULT NULL COMMENT &amp;#39;余额&amp;#39;, `create_time` datetime NOT NULL COMMENT &amp;#39;创建时间&amp;#39;, `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT &amp;#39;更新时间&amp;#39;, PRIMARY KEY (`id`), KEY `idx_name` (`name`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT=&amp;#39;账户表&amp;#39;;</pre>The executed SQL is as follows:

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

Let’s explain the execution plan,

From the

explain

results, we can find: firstscan the whole table account, 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, because did not go to index . But if

delete

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

Why does the

select in

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

What is the difference between the select in

subquery statement and the delete in subquery 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 ：

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`)

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

select in subquery

and changed the subquery to a join method, so the index can be used. But unfortunately, MySQL did not optimize it for delete 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

join

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

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

which can use indexing, which is a perfect solution solved this problem.

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

also recommends join method optimization

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

? Why is it possible to add an alias, delete in subquery, and use the index again?

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

.

What is LooseScan?

In fact, it is a strategy, an execution strategy for

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

, it will use the

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

Recommended learning:

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!