mysql巢狀查詢和聯表查詢最佳化方法

巢狀查詢糟糕的最佳化
在上面我提到過，不考慮特殊的情況，聯表查詢要比巢狀查詢更有效。儘管兩條查詢表達的是同樣的意思，儘管你的計劃是告訴伺服器要做什麼，然後讓它決定怎麼做，但有時候你非得告訴它改怎麼做。否則最佳化器可能會做傻事。我最近就碰到這樣的情況。這幾個表是三層分級關係：category, subcategory和item。有幾千條記錄在category表，幾百條記錄在subcategory表，以及幾百萬條在item表。你可以忽略category表了，我只是交代一下背景，以下查詢語句都不涉及到它。這是建立表的語句：

[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;

我又往表裡面填入一些樣本資料

[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;

再次說明，這些語句運行完需要一點時間，不適合放在產品環境中運行。思路是往item裡插入隨機行數的資料，這樣subcategory就有1到2018之間個item。這不是實際中的完整資料，但效果一樣。

我想找出某個category中item數大於2000的全部subcategory。首先，我找到一個subcategory item數大於2000的，然後把它的category用在接下來的查詢中。這是具體的查詢語句：

[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

我拿到一個合適的值14，在以下的查詢中會用到它。這是用來查詢category 14 中所有item數大於2000的subcategory的語句：

[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;

在我的範例資料裡，查詢的結果有10行記錄，而且只用10多秒就完成了。EXPLAIN顯示出很好地使用了索引；從資料的規模來看，相當不錯了。查詢計劃是在索引上遍曆並計算出目標記錄。目前為止，非常好。

這回假設我要從subcategory取出全部的欄位。我可以把上面的查詢當成嵌套，然後用JOIN，或者SELECT MAX之類(既然分組集對應的值都是唯一的)，但也寫成跟下面的一樣的，有木有？

[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
);

跑完這條查詢估計要從破曉到夕陽沉入大地。我不知道它要跑多久，因為我沒打算讓它無休止地跑下去。你可能認為，單從語句上理解，它會:a)計算出裡面的查詢，找出那10個值，b)繼續找出那10條記錄，並且在primary索引上去找會非常地快。錯，這是實際上的查詢計劃：

[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的語句查詢計劃，請看大概意思：mysql計劃從外到內執行查詢，而不是從內到外。我會一個一個地介紹查詢的每個部分。

外面的查詢簡單地變成了SELECT * FROM subcategory。雖然裡面的查詢對subcategory有個約束（WHERE category = 14），但出於某些原因mysql沒有將它作用於外面的查詢。我不知道是神馬原因。我只知道它掃描了整張表(這就是 type:ALL 表示的意思)，並且沒有使用任何的索引。這是在10幾萬行記錄的表上掃描。

在外面的查詢，對每行都執行一次裡面的查詢，儘管沒有值被裡面的查詢使用到，因為裡面的查詢被“最佳化”成引用外面的查詢。照此分析，查詢計劃變成了嵌套迴圈。外面的查詢的每一次迴圈，都執行一次裡面的查詢。下面就是最佳化器重寫後的查詢計劃：

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

你可以通過在EXPLAIN EXTENDED 後面帶上SHOW WARNINGS 得到最佳化後的查詢。請留意在HAVING子句中指向的外部域。

我舉這個例子並非有意抨擊mysql的最佳化策略。眾所皆知mysql在有些情況下還不能很好地最佳化巢狀查詢，這個問題已經被廣泛報告過。我想指出的是，開發人員有必要檢查查詢語句確保它們不是被糟糕地最佳化。大多數情況下，安全起見若非是非必要，避免使用嵌套——尤其是WHERE...IN() 和 WHERE...NOT IN語句。

我自己的原則是“有疑問，EXPLAIN看看”。如果面對的是一個大資料表，我會自然而然地產生疑問。

如何強制裡面的查詢先執行
上一節中的語句撞板只因為mysql把它當成相關的語句從外到裡地執行，而不是當成不相關語句從裡到外執行。讓mysql先執行裡面的查詢也是有辦法的，當成暫存資料表來實現，從而避免巨大的效能開銷。

mysql從暫存資料表來實現巢狀查詢（某種程度上被訛傳的衍生表）。這意味著mysql先執行裡面的查詢，並且把結果儲存在暫存資料表中，然後在其他的表裡用到它。這就是我寫這個查詢時所期待的執行方式。查詢語句修改如下：
[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
);

我所做的就是把嵌套包著原來的巢狀查詢。mysql會認為最裡面是一個獨立的巢狀查詢先執行，然後現在只剩下包著外面的嵌套，它已經被裝進一個暫存資料表裡，只有少量記錄，因此要快很多。依此分析，這是相當笨的最佳化辦法；倒不如把它重寫成join方式。再說，免得被別人看到，當成多餘代碼清理掉。

有些情況可以使用這種最佳化方法，比如mysql拋出錯誤，巢狀查詢的表在其他地方被修改（譯註：另一篇文章 MySQL SELECT同時UPDATE同一張表 ）。不幸的是，對於暫存資料表只能在查詢語句中使用一次的情況，這種方法就無能為力了。

來源 http://blog.csdn.net/afeiqiang/article/details/8620038