快捷導(dǎo)航

delete?in子查詢(xún)不走索引問(wèn)題分析

更新時(shí)間：2022年07月06日 17:11:50 作者：撿田螺的小男孩

這篇文章主要為大家介紹了delete?in子查詢(xún)不走索引的問(wèn)題分析，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪

引言

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

問(wèn)題復(fù)現(xiàn)

MySQL版本是5.7，假設(shè)當(dāng)前有兩張表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?'賬戶(hù)名',
??`balance`?int(11)?DEFAULT?NULL?COMMENT?'余額',
??`create_time`?datetime?NOT?NULL?COMMENT?'創(chuàng)建時(shí)間',
??`update_time`?datetime?NOT?NULL?ON?UPDATE?CURRENT_TIMESTAMP?COMMENT?'更新時(shí)間',
??PRIMARY?KEY?(`id`),
??KEY?`idx_name`?(`name`)?USING?BTREE
)?ENGINE=InnoDB?AUTO_INCREMENT=1570068?DEFAULT?CHARSET=utf8?ROW_FORMAT=REDUNDANT?COMMENT='老的賬戶(hù)表';
CREATE?TABLE?`account`?(
??`id`?int(11)?NOT?NULL?AUTO_INCREMENT?COMMENT?'主鍵Id',
??`name`?varchar(255)?DEFAULT?NULL?COMMENT?'賬戶(hù)名',
??`balance`?int(11)?DEFAULT?NULL?COMMENT?'余額',
??`create_time`?datetime?NOT?NULL?COMMENT?'創(chuàng)建時(shí)間',
??`update_time`?datetime?NOT?NULL?ON?UPDATE?CURRENT_TIMESTAMP?COMMENT?'更新時(shí)間',
??PRIMARY?KEY?(`id`),
??KEY?`idx_name`?(`name`)?USING?BTREE
)?ENGINE=InnoDB?AUTO_INCREMENT=1570068?DEFAULT?CHARSET=utf8?ROW_FORMAT=REDUNDANT?COMMENT='賬戶(hù)表';

執(zhí)行的SQL如下：

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

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

從explain結(jié)果可以發(fā)現(xiàn)：先全表掃描 account，然后逐行執(zhí)行子查詢(xún)判斷條件是否滿(mǎn)足；顯然，這個(gè)執(zhí)行計(jì)劃和我們預(yù)期不符合，因?yàn)?strong>并沒(méi)有走索引。

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

為什么select in子查詢(xún)會(huì)走索引，delete in子查詢(xún)卻不會(huì)走索引呢？

原因分析

select in子查詢(xún)語(yǔ)句跟delete in子查詢(xún)語(yǔ)句的不同點(diǎn)到底在哪里呢？

我們執(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)，實(shí)際執(zhí)行的時(shí)候，MySQL對(duì)select in子查詢(xún)做了優(yōu)化，把子查詢(xún)改成join的方式，所以可以走索引。但是很遺憾，對(duì)于delete in子查詢(xún)，MySQL卻沒(méi)有對(duì)它做這個(gè)優(yōu)化。

優(yōu)化方案

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

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

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

其實(shí)呢，給表加別名，也可以解決這個(gè)問(wèn)題哦，如下:

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

為什么加個(gè)別名就可以走索引了呢？

what？為啥加個(gè)別名，delete in子查詢(xún)又行了，又走索引了？

我們回過(guò)頭來(lái)看看explain的執(zhí)行計(jì)劃，可以發(fā)現(xiàn)Extra那一欄，有個(gè)LooseScan。

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

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

因此，加別名就可以讓delete in子查詢(xún)走索引啦！

總結(jié)

本博文分析了delete in子查詢(xún)不走索引的原因，并附上解決方案。delete in在日常開(kāi)發(fā)，是非常常見(jiàn)的，平時(shí)大家工作中，需要注意一下。同時(shí)呢，建議大家工作的時(shí)候，寫(xiě)SQL的時(shí)候，盡量養(yǎng)成一個(gè)好習(xí)慣，先用explain分析一下SQL，更多關(guān)于delete in子查詢(xún)索引的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: