推薦學習: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`)select in子查詢where (`test2`.`account`.`name` = `test2`.`old_account`.`name`)
可以發現,實際執行的時候,MySQL對
做了最佳化,把子查詢改成join的方式,所以可以走索引。但很遺憾,對於delete in子查詢,MySQL卻沒有對它做這個最佳化。 優化方案
的方式。我們改為join的方式後,再explain看下:
可以發現,改用join的方式是
可以走索引的,完美解決了這個問題。 實際上,對於update或delete子查詢的語句,
MySQL官網也是推薦join的方式最佳化
##其實呢,給表加別名,也可以解決這個問題哦,如下:
explain delete a from account as a where a.name in (select name from old_account)
為什麼加個別名就可以走索引了呢?
我們回過頭來看看explain的執行計劃,可以發現Extra那一欄,有個LooseScan
。
LooseScan是什麼呢?
其實它是一種策略,是semi join子查詢的一種執行策略。 因為子查詢改為join,是可以讓delete in子查詢走索引;加別名呢
,會走LooseScan策略,而LooseScan策略,本質上就是semi join子查詢的一種執行策略。 因此,加別名就可以讓delete in子查詢走索引啦!
推薦學習:mysql影片教學
#
以上是mysql中delete in子查詢不走索引問題分析的詳細內容。更多資訊請關注PHP中文網其他相關文章!