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

引言

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

問題復現

MySQL版本是5.7,假設當前有兩張表accountold_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 `test2`.`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子查詢走索引啦!

總結

本博文分析瞭delete in子查詢不走索引的原因,並附上解決方案。delete in在日常開發,是非常常見的,平時大傢工作中,需要註意一下。同時呢,建議大傢工作的時候,寫SQL的時候,盡量養成一個好習慣,先用explain分析一下SQL,更多關於delete in子查詢索引的資料請關註WalkonNet其它相關文章!

推薦閱讀: