preface

Hello, everyone. I am a little boy picking up field snails.

Delete in subquery: Delete in subquery: delete in subquery Many partners first feeling is: will go to the index. We had a production problem recently, and it was related to it. This article will discuss this problem with you and attach the optimization plan.

  • Public number: a boy picking up snails

Problem of repetition

MySQL > alter table old_account; MySQL > alter table old_account;

CREATE TABLE 'old_account' (' id 'int(11) NOT NULL AUTO_INCREMENT COMMENT' id ', 'name' varchar(255) DEFAULT NULL COMMENT 'balance ',' balance 'int(11) DEFAULT NULL COMMENT' balance ', 'create_time' datetime NOT NULL COMMENT 'create time ', 'update_time' datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'UPDATE time ', PRIMARY KEY (' id'), KEY `idx_name` (`name`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT=' old '; CREATE TABLE 'account' (' id 'int(11) NOT NULL AUTO_INCREMENT COMMENT' id ', 'name' varchar(255) DEFAULT NULL COMMENT 'balance ',' balance 'int(11) DEFAULT NULL COMMENT' balance ', 'create_time' datetime NOT NULL COMMENT 'create time ', 'update_time' datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'UPDATE time ', PRIMARY KEY (' id'), KEY `idx_name` (`name`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT=' table ';Copy the code

The following SQL is executed:

delete from account where name in (select name from old_account);
Copy the code

We explain the execution plan to go a wave,

From the explain results, it can be found that: first scan the account in the whole table, then execute the sub-query line by line to determine whether the conditions are met; Obviously, this execution plan is not what we expected, because there is no indexing.

But if you replace delete with SELECT, it will go through the index. 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’s the difference between a SELECT in subquery and a DELETE in subquery?

Let’s execute the following SQL to see

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

Show WARNINGS allows you to view the SQL executed 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`)
Copy the code

MySQL > select IN; select in; join; Unfortunately, MySQL does not optimize the DELETE in subquery.

Optimization scheme

So how do you optimize this problem? From the above analysis, it is clear that you can change the DELETE in subquery to join. After we change to join mode, we will explain:

It can be found that the use of join can go through the index, which perfectly solves this problem.

In fact, for update or DELETE subqueries, MySQL’s official website also recommends join optimization

Alter table alias alter table alias alter table alias

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

Why add a different name can go to the index?

What? Why add individual name, delete in subquery again row, again index?

If we go back and look at the execution plan of Explain, we can find the column “Extra”, there is aLooseScan.

What is a LooseScan? It is actually a policy, an execution strategy for semi Join subqueries.

Because the subquery is changed to join, it is possible to delete in subquery. The LooseScan policy is essentially an execution strategy for semi Join sub-queries.

Therefore, add an alias to the delete in subquery to remove the index.

conclusion

  • This blog has analyzed itDelete in The subquery does not delete the indexAnd the solution is attached.delete inIn daily development, is very common, usually we work, need to pay attention to. At the same time, I suggest you work, write SQL, as far as possible to develop a good habit, first use explain to analyze SQL.
  • The whole idea of this article refers to my colleague’s blog and has been agreed by him. It is also suggested that when you encounter problems, you should think more and write more summaries to avoid repeating the same mistakes.
  • I am a little boy picking up snail, code word is not easy, read the article harvest words, ask for a praise, public number (pick up snail little boy) ask for a concern, thank you, than heart ~