[[426486]]

前言

大家好，我是撿田螺的小男孩。(求個星標置頂)

文章開篇前，先問大家一個問題：delete in子查詢，是否會走索引呢?很多伙伴第一感覺就是：會走索引。最近我們有個生產(chǎn)問題，就跟它有關(guān)。本文將跟大家一起探討這個問題，并附上優(yōu)化方案。

問題復現(xiàn)

MySQL版本是5.7，假設當前有兩張表account和old_account,表結(jié)構(gòu)如下：

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 '創(chuàng)建時間', 
  `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 '創(chuàng)建時間', 
  `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='賬戶表'; 

執(zhí)行的SQL如下：

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

我們explain執(zhí)行計劃走一波，

從explain結(jié)果可以發(fā)現(xiàn)：先全表掃描 account，然后逐行執(zhí)行子查詢判斷條件是否滿足;顯然，這個執(zhí)行計劃和我們預期不符合，因為并沒有走索引。

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

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

原因分析

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

我們執(zhí)行以下SQL看看

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

show WARNINGS 可以查看優(yōu)化后,最終執(zhí)行的sql

結(jié)果如下：

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

可以發(fā)現(xiàn)，實際執(zhí)行的時候，MySQL對select in子查詢做了優(yōu)化，把子查詢改成join的方式，所以可以走索引。但是很遺憾，對于delete in子查詢，MySQL卻沒有對它做這個優(yōu)化。

優(yōu)化方案

那如何優(yōu)化這個問題呢?通過上面的分析，顯然可以把delete in子查詢改為join的方式。我們改為join的方式后，再explain看下：

可以發(fā)現(xiàn)，改用join的方式是可以走索引的，完美解決了這個問題。

實際上，對于update或者delete子查詢的語句，MySQL官網(wǎng)也是推薦join的方式優(yōu)化

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

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

為什么加個別名就可以走索引了呢?

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

我們回過頭來看看explain的執(zhí)行計劃，可以發(fā)現(xiàn)Extra那一欄，有個LooseScan。

LooseScan是什么呢? 其實它是一種策略，是semi join子查詢的一種執(zhí)行策略。

因為子查詢改為join，是可以讓delete in子查詢走索引;加別名呢，會走LooseScan策略，而LooseScan策略，本質(zhì)上就是semi join子查詢的一種執(zhí)行策略。

因此，加別名就可以讓delete in子查詢走索引啦!

總結(jié)

本博文分析了delete in子查詢不走索引的原因，并附上解決方案。delete in在日常開發(fā)，是非常常見的，平時大家工作中，需要注意一下。同時呢，建議大家工作的時候，寫SQL的時候，盡量養(yǎng)成一個好習慣，先用explain分析一下SQL。

本文整體思路參考同事的博文，已經(jīng)經(jīng)過他本人同意。也建議大家遇到問題時，多點思考，多點寫寫總結(jié)，避免重蹈覆轍。

我是撿田螺的小男孩，碼字不易，看完文章有收獲的話，可以把我公眾號推給身邊的程序員哈，感謝、比心~

本文轉(zhuǎn)載自微信公眾號「撿田螺的小男孩」，可以通過以下二維碼關(guān)注。轉(zhuǎn)載本文請聯(lián)系撿田螺的小男孩公眾號。