mysql嵌套查詢和聯(lián)表查詢優(yōu)化方法
更新時(shí)間：2013年02月28日 10:44:51 作者：
本文描述了mysql嵌套查詢和聯(lián)表查詢優(yōu)化的方法，有些情況可以使用這種優(yōu)化方法，而有些情況，這種方法就無(wú)能為力了
嵌套查詢?cè)愀獾膬?yōu)化
在上面我提到過(guò)，不考慮特殊的情況，聯(lián)表查詢要比嵌套查詢更有效。盡管兩條查詢表達(dá)的是同樣的意思，盡管你的計(jì)劃是告訴服務(wù)器要做什么，然后讓它決定怎么做，但有時(shí)候你非得告訴它改怎么做。否則優(yōu)化器可能會(huì)做傻事。我最近就碰到這樣的情況。這幾個(gè)表是三層分級(jí)關(guān)系：category, subcategory和item。有幾千條記錄在category表，幾百條記錄在subcategory表，以及幾百萬(wàn)條在item表。你可以忽略category表了，我只是交代一下背景，以下查詢語(yǔ)句都不涉及到它。這是創(chuàng)建表的語(yǔ)句：
[sql]
create table subcategory (
id int not null primary key,
category int not null,
index(category)
) engine=InnoDB;
create table item(
id int not null auto_increment primary key,
subcategory int not null,
index(subcategory)
) engine=InnoDB;
我又往表里面填入一些樣本數(shù)據(jù)
[sql]
insert into subcategory(id, category)
select i, i/100 from number
where i <= 300000;
insert into item(subcategory)
select id
from (
select id, rand() * 20 as num_rows from subcategory
) as x
cross join number
where i <= num_rows;
create temporary table t as
select subcategory from item
group by subcategory
having count(*) = 19
limit 100;
insert into item (subcategory)
select subcategory
from t
cross join number
where i < 2000;
再次說(shuō)明，這些語(yǔ)句運(yùn)行完需要一點(diǎn)時(shí)間，不適合放在產(chǎn)品環(huán)境中運(yùn)行。思路是往item里插入隨機(jī)行數(shù)的數(shù)據(jù)，這樣subcategory就有1到2018之間個(gè)item。這不是實(shí)際中的完整數(shù)據(jù)，但效果一樣。
我想找出某個(gè)category中item數(shù)大于2000的全部subcategory。首先，我找到一個(gè)subcategory item數(shù)大于2000的，然后把它的category用在接下來(lái)的查詢中。這是具體的查詢語(yǔ)句：
[sql]
select c.id
from subcategory as c
inner join item as i on i.subcategory = c.id
group by c.id
having count(*) > 2000;
-- choose one of the results, then
select * from subcategory where id = ????
-- result: category = 14
我拿到一個(gè)合適的值14，在以下的查詢中會(huì)用到它。這是用來(lái)查詢category 14 中所有item數(shù)大于2000的subcategory的語(yǔ)句：
[sql]
select c.id
from subcategory as c
inner join item as i on i.subcategory = c.id
where c.category = 14
group by c.id
having count(*) > 2000;
在我的樣例數(shù)據(jù)里，查詢的結(jié)果有10行記錄，而且只用10多秒就完成了。EXPLAIN顯示出很好地使用了索引；從數(shù)據(jù)的規(guī)模來(lái)看，相當(dāng)不錯(cuò)了。查詢計(jì)劃是在索引上遍歷并計(jì)算出目標(biāo)記錄。目前為止，非常好。
這回假設(shè)我要從subcategory取出全部的字段。我可以把上面的查詢當(dāng)成嵌套，然后用JOIN，或者SELECT MAX之類(既然分組集對(duì)應(yīng)的值都是唯一的)，但也寫(xiě)成跟下面的一樣的，有木有？
[sql]
select * from subcategory
where id in (
select c.id
from subcategory as c
inner join item as i on i.subcategory = c.id
where c.category = 14
group by c.id
having count(*) > 2000
);
跑完這條查詢估計(jì)要從破曉到夕陽(yáng)沉入大地。我不知道它要跑多久，因?yàn)槲覜](méi)打算讓它無(wú)休止地跑下去。你可能認(rèn)為，單從語(yǔ)句上理解，它會(huì):a)計(jì)算出里面的查詢，找出那10個(gè)值，b)繼續(xù)找出那10條記錄，并且在primary索引上去找會(huì)非常地快。錯(cuò)，這是實(shí)際上的查詢計(jì)劃：
[sql]
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: subcategory
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 300783
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: c
type: ref
possible_keys: PRIMARY,category
key: category
key_len: 4
ref: const
rows: 100
Extra: Using where; Using index; Using temporary; Using filesort
*************************** 3. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: i
type: ref
possible_keys: subcategory
key: subcategory
key_len: 4
ref: c.id
rows: 28
Extra: Using index
如何你不熟悉如何分析mysql的語(yǔ)句查詢計(jì)劃，請(qǐng)看大概意思：mysql計(jì)劃從外到內(nèi)執(zhí)行查詢，而不是從內(nèi)到外。我會(huì)一個(gè)一個(gè)地介紹查詢的每個(gè)部分。
外面的查詢簡(jiǎn)單地變成了SELECT * FROM subcategory。雖然里面的查詢對(duì)subcategory有個(gè)約束（WHERE category = 14），但出于某些原因mysql沒(méi)有將它作用于外面的查詢。我不知道是神馬原因。我只知道它掃描了整張表(這就是 type:ALL 表示的意思)，并且沒(méi)有使用任何的索引。這是在10幾萬(wàn)行記錄的表上掃描。
在外面的查詢，對(duì)每行都執(zhí)行一次里面的查詢，盡管沒(méi)有值被里面的查詢使用到，因?yàn)槔锩娴牟樵儽弧皟?yōu)化”成引用外面的查詢。照此分析，查詢計(jì)劃變成了嵌套循環(huán)。外面的查詢的每一次循環(huán)，都執(zhí)行一次里面的查詢。下面就是優(yōu)化器重寫(xiě)后的查詢計(jì)劃：
[sql]
select * from subcategory as s
where <in_optimizer>(
s.id,<exists>(
select c.id
from subcategory as c
join item as i
where ((i.subcategory = c.id) and (c.category = 14))
group by c.id
having ((count(0) > 2000)
and (<cache>(s.id) = <ref_null_helper>(c.id))))
)
你可以通過(guò)在EXPLAIN EXTENDED 后面帶上SHOW WARNINGS 得到優(yōu)化后的查詢。請(qǐng)留意在HAVING子句中指向的外部域。
我舉這個(gè)例子并非有意抨擊mysql的優(yōu)化策略。眾所皆知mysql在有些情況下還不能很好地優(yōu)化嵌套查詢，這個(gè)問(wèn)題已經(jīng)被廣泛報(bào)告過(guò)。我想指出的是，開(kāi)發(fā)者有必要檢查查詢語(yǔ)句確保它們不是被糟糕地優(yōu)化。大多數(shù)情況下，安全起見(jiàn)若非是非必要，避免使用嵌套——尤其是WHERE...IN() 和 WHERE...NOT IN語(yǔ)句。
我自己的原則是“有疑問(wèn)，EXPLAIN看看”。如果面對(duì)的是一個(gè)大數(shù)據(jù)表，我會(huì)自然而然地產(chǎn)生疑問(wèn)。
如何強(qiáng)制里面的查詢先執(zhí)行
上一節(jié)中的語(yǔ)句撞板只因?yàn)閙ysql把它當(dāng)成相關(guān)的語(yǔ)句從外到里地執(zhí)行，而不是當(dāng)成不相關(guān)語(yǔ)句從里到外執(zhí)行。讓mysql先執(zhí)行里面的查詢也是有辦法的，當(dāng)成臨時(shí)表來(lái)實(shí)現(xiàn)，從而避免巨大的性能開(kāi)銷。
mysql從臨時(shí)表來(lái)實(shí)現(xiàn)嵌套查詢（某種程度上被訛傳的衍生表）。這意味著mysql先執(zhí)行里面的查詢，并且把結(jié)果儲(chǔ)存在臨時(shí)表中，然后在其他的表里用到它。這就是我寫(xiě)這個(gè)查詢時(shí)所期待的執(zhí)行方式。查詢語(yǔ)句修改如下：
[sql]
select * from subcategory
where id in (
select id from (
select c.id
from subcategory as c
inner join item as i on i.subcategory = c.id
where c.category = 14
group by c.id
having count(*) > 2000
) as x
);
我所做的就是把嵌套包著原來(lái)的嵌套查詢。mysql會(huì)認(rèn)為最里面是一個(gè)獨(dú)立的嵌套查詢先執(zhí)行，然后現(xiàn)在只剩下包著外面的嵌套，它已經(jīng)被裝進(jìn)一個(gè)臨時(shí)表里，只有少量記錄，因此要快很多。依此分析，這是相當(dāng)笨的優(yōu)化辦法；倒不如把它重寫(xiě)成join方式。再說(shuō)，免得被別人看到，當(dāng)成多余代碼清理掉。
有些情況可以使用這種優(yōu)化方法，比如mysql拋出錯(cuò)誤，嵌套查詢的表在其他地方被修改（譯注：另一篇文章 MySQL SELECT同時(shí)UPDATE同一張表 ）。不幸的是，對(duì)于臨時(shí)表只能在查詢語(yǔ)句中使用一次的情況，這種方法就無(wú)能為力了。
來(lái)源 http://blog.csdn.net/afeiqiang/article/details/8620038
在上面我提到過(guò)，不考慮特殊的情況，聯(lián)表查詢要比嵌套查詢更有效。盡管兩條查詢表達(dá)的是同樣的意思，盡管你的計(jì)劃是告訴服務(wù)器要做什么，然后讓它決定怎么做，但有時(shí)候你非得告訴它改怎么做。否則優(yōu)化器可能會(huì)做傻事。我最近就碰到這樣的情況。這幾個(gè)表是三層分級(jí)關(guān)系：category, subcategory和item。有幾千條記錄在category表，幾百條記錄在subcategory表，以及幾百萬(wàn)條在item表。你可以忽略category表了，我只是交代一下背景，以下查詢語(yǔ)句都不涉及到它。這是創(chuàng)建表的語(yǔ)句：
[sql]
復(fù)制代碼 代碼如下:
create table subcategory (
id int not null primary key,
category int not null,
index(category)
) engine=InnoDB;
create table item(
id int not null auto_increment primary key,
subcategory int not null,
index(subcategory)
) engine=InnoDB;
我又往表里面填入一些樣本數(shù)據(jù)
[sql]
復(fù)制代碼 代碼如下:
insert into subcategory(id, category)
select i, i/100 from number
where i <= 300000;
insert into item(subcategory)
select id
from (
select id, rand() * 20 as num_rows from subcategory
) as x
cross join number
where i <= num_rows;
create temporary table t as
select subcategory from item
group by subcategory
having count(*) = 19
limit 100;
insert into item (subcategory)
select subcategory
from t
cross join number
where i < 2000;
再次說(shuō)明，這些語(yǔ)句運(yùn)行完需要一點(diǎn)時(shí)間，不適合放在產(chǎn)品環(huán)境中運(yùn)行。思路是往item里插入隨機(jī)行數(shù)的數(shù)據(jù)，這樣subcategory就有1到2018之間個(gè)item。這不是實(shí)際中的完整數(shù)據(jù)，但效果一樣。
我想找出某個(gè)category中item數(shù)大于2000的全部subcategory。首先，我找到一個(gè)subcategory item數(shù)大于2000的，然后把它的category用在接下來(lái)的查詢中。這是具體的查詢語(yǔ)句：
[sql]
復(fù)制代碼 代碼如下:
select c.id
from subcategory as c
inner join item as i on i.subcategory = c.id
group by c.id
having count(*) > 2000;
-- choose one of the results, then
select * from subcategory where id = ????
-- result: category = 14
我拿到一個(gè)合適的值14，在以下的查詢中會(huì)用到它。這是用來(lái)查詢category 14 中所有item數(shù)大于2000的subcategory的語(yǔ)句：
[sql]
復(fù)制代碼 代碼如下:
select c.id
from subcategory as c
inner join item as i on i.subcategory = c.id
where c.category = 14
group by c.id
having count(*) > 2000;
在我的樣例數(shù)據(jù)里，查詢的結(jié)果有10行記錄，而且只用10多秒就完成了。EXPLAIN顯示出很好地使用了索引；從數(shù)據(jù)的規(guī)模來(lái)看，相當(dāng)不錯(cuò)了。查詢計(jì)劃是在索引上遍歷并計(jì)算出目標(biāo)記錄。目前為止，非常好。
這回假設(shè)我要從subcategory取出全部的字段。我可以把上面的查詢當(dāng)成嵌套，然后用JOIN，或者SELECT MAX之類(既然分組集對(duì)應(yīng)的值都是唯一的)，但也寫(xiě)成跟下面的一樣的，有木有？
[sql]
復(fù)制代碼 代碼如下:
select * from subcategory
where id in (
select c.id
from subcategory as c
inner join item as i on i.subcategory = c.id
where c.category = 14
group by c.id
having count(*) > 2000
);
跑完這條查詢估計(jì)要從破曉到夕陽(yáng)沉入大地。我不知道它要跑多久，因?yàn)槲覜](méi)打算讓它無(wú)休止地跑下去。你可能認(rèn)為，單從語(yǔ)句上理解，它會(huì):a)計(jì)算出里面的查詢，找出那10個(gè)值，b)繼續(xù)找出那10條記錄，并且在primary索引上去找會(huì)非常地快。錯(cuò)，這是實(shí)際上的查詢計(jì)劃：
[sql]
復(fù)制代碼 代碼如下:
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: subcategory
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 300783
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: c
type: ref
possible_keys: PRIMARY,category
key: category
key_len: 4
ref: const
rows: 100
Extra: Using where; Using index; Using temporary; Using filesort
*************************** 3. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: i
type: ref
possible_keys: subcategory
key: subcategory
key_len: 4
ref: c.id
rows: 28
Extra: Using index
如何你不熟悉如何分析mysql的語(yǔ)句查詢計(jì)劃，請(qǐng)看大概意思：mysql計(jì)劃從外到內(nèi)執(zhí)行查詢，而不是從內(nèi)到外。我會(huì)一個(gè)一個(gè)地介紹查詢的每個(gè)部分。
外面的查詢簡(jiǎn)單地變成了SELECT * FROM subcategory。雖然里面的查詢對(duì)subcategory有個(gè)約束（WHERE category = 14），但出于某些原因mysql沒(méi)有將它作用于外面的查詢。我不知道是神馬原因。我只知道它掃描了整張表(這就是 type:ALL 表示的意思)，并且沒(méi)有使用任何的索引。這是在10幾萬(wàn)行記錄的表上掃描。
在外面的查詢，對(duì)每行都執(zhí)行一次里面的查詢，盡管沒(méi)有值被里面的查詢使用到，因?yàn)槔锩娴牟樵儽弧皟?yōu)化”成引用外面的查詢。照此分析，查詢計(jì)劃變成了嵌套循環(huán)。外面的查詢的每一次循環(huán)，都執(zhí)行一次里面的查詢。下面就是優(yōu)化器重寫(xiě)后的查詢計(jì)劃：
[sql]
復(fù)制代碼 代碼如下:
select * from subcategory as s
where <in_optimizer>(
s.id,<exists>(
select c.id
from subcategory as c
join item as i
where ((i.subcategory = c.id) and (c.category = 14))
group by c.id
having ((count(0) > 2000)
and (<cache>(s.id) = <ref_null_helper>(c.id))))
)
你可以通過(guò)在EXPLAIN EXTENDED 后面帶上SHOW WARNINGS 得到優(yōu)化后的查詢。請(qǐng)留意在HAVING子句中指向的外部域。
我舉這個(gè)例子并非有意抨擊mysql的優(yōu)化策略。眾所皆知mysql在有些情況下還不能很好地優(yōu)化嵌套查詢，這個(gè)問(wèn)題已經(jīng)被廣泛報(bào)告過(guò)。我想指出的是，開(kāi)發(fā)者有必要檢查查詢語(yǔ)句確保它們不是被糟糕地優(yōu)化。大多數(shù)情況下，安全起見(jiàn)若非是非必要，避免使用嵌套——尤其是WHERE...IN() 和 WHERE...NOT IN語(yǔ)句。
我自己的原則是“有疑問(wèn)，EXPLAIN看看”。如果面對(duì)的是一個(gè)大數(shù)據(jù)表，我會(huì)自然而然地產(chǎn)生疑問(wèn)。
如何強(qiáng)制里面的查詢先執(zhí)行
上一節(jié)中的語(yǔ)句撞板只因?yàn)閙ysql把它當(dāng)成相關(guān)的語(yǔ)句從外到里地執(zhí)行，而不是當(dāng)成不相關(guān)語(yǔ)句從里到外執(zhí)行。讓mysql先執(zhí)行里面的查詢也是有辦法的，當(dāng)成臨時(shí)表來(lái)實(shí)現(xiàn)，從而避免巨大的性能開(kāi)銷。
mysql從臨時(shí)表來(lái)實(shí)現(xiàn)嵌套查詢（某種程度上被訛傳的衍生表）。這意味著mysql先執(zhí)行里面的查詢，并且把結(jié)果儲(chǔ)存在臨時(shí)表中，然后在其他的表里用到它。這就是我寫(xiě)這個(gè)查詢時(shí)所期待的執(zhí)行方式。查詢語(yǔ)句修改如下：
[sql]
復(fù)制代碼 代碼如下:
select * from subcategory
where id in (
select id from (
select c.id
from subcategory as c
inner join item as i on i.subcategory = c.id
where c.category = 14
group by c.id
having count(*) > 2000
) as x
);
我所做的就是把嵌套包著原來(lái)的嵌套查詢。mysql會(huì)認(rèn)為最里面是一個(gè)獨(dú)立的嵌套查詢先執(zhí)行，然后現(xiàn)在只剩下包著外面的嵌套，它已經(jīng)被裝進(jìn)一個(gè)臨時(shí)表里，只有少量記錄，因此要快很多。依此分析，這是相當(dāng)笨的優(yōu)化辦法；倒不如把它重寫(xiě)成join方式。再說(shuō)，免得被別人看到，當(dāng)成多余代碼清理掉。
有些情況可以使用這種優(yōu)化方法，比如mysql拋出錯(cuò)誤，嵌套查詢的表在其他地方被修改（譯注：另一篇文章 MySQL SELECT同時(shí)UPDATE同一張表 ）。不幸的是，對(duì)于臨時(shí)表只能在查詢語(yǔ)句中使用一次的情況，這種方法就無(wú)能為力了。
來(lái)源 http://blog.csdn.net/afeiqiang/article/details/8620038
相關(guān)文章
這篇文章主要介紹了MySQL的基本操作語(yǔ)句,是MySQL入門(mén)學(xué)習(xí)中的基礎(chǔ)知識(shí),需要的朋友可以參考下2015-07-07
InnoDB 類型MySql恢復(fù)表結(jié)構(gòu)與數(shù)據(jù)
MySQL中.frm文件：保存了每個(gè)表的元數(shù)據(jù)，包括表結(jié)構(gòu)的定義等，該文件與數(shù)據(jù)庫(kù)引擎無(wú)關(guān)。MySQL中.ibd文件：InnoDB引擎開(kāi)啟了獨(dú)立表空間(my.ini中配置innodb_file_per_table = 1)產(chǎn)生的存放該表的數(shù)據(jù)和索引的文件。2018-01-01
MySQL性能優(yōu)化神器Explain的基本使用分析
這篇文章主要給大家介紹了關(guān)于MySQL性能優(yōu)化神器Explain的基本使用分析，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家學(xué)習(xí)或者使用MySQL具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-08-08
這篇文章主要介紹了Mysql中Identity 的相關(guān)資料,并附示例代碼，需要的朋友可以參考下2016-09-09
索引的種類眾所周知,索引類似于字典的目錄,可以提高查詢的效率,下面這篇文章主要給大家介紹了關(guān)于mysql哪些查詢情況不走索引的相關(guān)資料,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-04-04
關(guān)于MySQL 優(yōu)化的100個(gè)的建議
MYSQL 如此方便和穩(wěn)定，以至于我們?cè)陂_(kāi)發(fā) WEB 程序的時(shí)候很少想到它。即使想到優(yōu)化也是程序級(jí)別的，比如，不要寫(xiě)過(guò)于消耗資源的 SQL 語(yǔ)句。但是除此之外，在整個(gè)系統(tǒng)上仍然有很多可以優(yōu)化的地方。2016-01-01
遠(yuǎn)程連接mysql數(shù)據(jù)庫(kù)注意點(diǎn)記錄
有時(shí)候我們需要遠(yuǎn)程連接mysql數(shù)據(jù)庫(kù)，那么就需要注意如下問(wèn)題，需要的朋友可以參考下2012-08-08
MySQL多版本并發(fā)控制MVCC深入學(xué)習(xí)
這篇文章主要介紹了MySQL多版本并發(fā)控制MVCC，小編覺(jué)得挺不錯(cuò)的，現(xiàn)在分享給大家，也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2021-11-11
count(1)、count(*)與count(列名)的執(zhí)行區(qū)別詳解
這篇文章主要介紹了count(1)、count(*)與count(列名)的執(zhí)行區(qū)別詳解，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2020-11-11
MySQL入門(mén)(三) 數(shù)據(jù)庫(kù)表的查詢操作【重要】
本節(jié)比較重要，對(duì)數(shù)據(jù)表數(shù)據(jù)進(jìn)行查詢操作，其中可能大家不熟悉的就對(duì)于INNER JOIN(內(nèi)連接)、LEFT JOIN(左連接)、RIGHT JOIN(右連接)等一些復(fù)雜查詢。 通過(guò)本節(jié)的學(xué)習(xí)，可以讓你知道這些基本的復(fù)雜查詢是怎么實(shí)現(xiàn)的，,需要的朋友可以參考下2018-07-07