mysql中delete in子查詢不走索引問題分析

這篇文章為大家帶來了關於mysql的相關知識，其中主要為大家介紹了delete in子查詢不走索引的問題分析，有需要的朋友可以藉鑑參考下，希望能夠有所幫助。

推薦學習：mysql影片教學

#文章開篇前，先問大家一個問題：delete in子查詢，是否會走索引呢？很多夥伴第一感覺就是：會走索引。最近我們有個生產問題，就跟它有關。本文將跟大家一起探討這個問題，並附上優化方案。

問題復現

MySQL版本是5.7，假設目前有兩個表格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執行計畫走一波，

從explain結果可以發現：先全表掃描 account，然後逐行執行子查詢判斷條件是否滿足；顯然，這個執行計劃和我們預期不符合，因為並沒有走索引。

但是如果把delete換成select，就會走索引。如下：

為什麼select in子查詢會走索引，delete in子查詢卻不會走索引呢？

原因分析

select in子查詢語句跟delete in子查詢語句的不同點到底在哪裡呢？

我們執行以下SQL看看

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

show WARNINGS 可以查看最佳化後,最終執行的sql

##結果如下：

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#`2`.`account`2`.`account` #semi join (`test2`.`old_account`)

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

可以發現，實際執行的時候，MySQL對

select in子查詢

做了最佳化，把子查詢改成join的方式，所以可以走索引。但很遺憾，對於delete in子查詢，MySQL卻沒有對它做這個最佳化。 優化方案

那要如何優化這個問題呢？透過上面的分析，顯然可以把delete in子查詢改為

join

的方式。我們改為join的方式後，再explain看下：

可以發現，改用join的方式是

可以走索引的

，完美解決了這個問題。 實際上，對於update或delete子查詢的語句，

MySQL官網

也是推薦join的方式最佳化

##其實呢，給表加別名，也可以解決這個問題哦，如下:

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

為什麼加個別名就可以走索引了呢？

what

？為啥加個別名，delete in子查詢又行了，又走索引了？

我們回過頭來看看explain的執行計劃，可以發現Extra那一欄，有個LooseScan

。

LooseScan是什麼呢？

 其實它是一種策略，是

semi join子查詢的一種執行策略。 因為子查詢改為join，是可以讓delete in子查詢走索引;加別名呢

，會走

LooseScan策略，而LooseScan策略，本質上就是semi join子查詢的一種執行策略。 因此，加別名就可以讓delete in子查詢走索引啦！

推薦學習：

mysql影片教學

#

以上是mysql中delete in子查詢不走索引問題分析的詳細內容。更多資訊請關注PHP中文網其他相關文章！