《Mysql必讀mysql嵌套查詢、聯表查詢的優化詳解》要點：
本文介紹了Mysql必讀mysql嵌套查詢、聯表查詢的優化詳解，希望對您有用。如果有疑問，可以聯系我們。
導讀：本節內容：優化mysql嵌套查詢、聯表查詢的具體方法.在不考慮特殊的情況下,聯表查詢要比嵌套查詢更有效.盡管兩條查詢表達的是同樣的意思...
MYSQL必讀本節內容：
優化mysql嵌套查詢、聯表查詢的具體方法.
MYSQL必讀在不考慮特殊的情況下,聯表查詢要比嵌套查詢更有效.
盡管兩條查詢表達的是同樣的意思,盡管你的計劃是告訴服務器要做什么,然后讓它決定怎么做,但有時候你非得告訴它改怎么做.否則優化器可能會做傻事.
MYSQL必讀有如下的情況：
幾個表是三層分級關系：category, subcategory和item.
有幾千條記錄在category表,幾百條記錄在subcategory表,以及幾百萬條在item表.
在理解下面的實例時,可以忽略category表了.
MYSQL必讀創建表的語句：
?
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;?
MYSQL必讀又往表里面填入一些樣本數據
?
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;?
MYSQL必讀這些語句運行完需要一點時間,不適合放在產品環境中運行.
思路是往item里插入隨機行數的數據,這樣subcategory就有1到2018之間個item.這不是實際中的完整數據,但效果一樣.
MYSQL必讀要求：找出某個category中item數大于2000的全部subcategory.
首先,找到一個subcategory item數大于2000的,然后把它的category用在接下來的查詢中.
MYSQL必讀查詢語句：
?
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?
MYSQL必讀拿到一個合適的值14,在以下的查詢中會用到它.
這是用來查詢category 14 中所有item數大于2000的subcategory的語句：
?
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;?
MYSQL必讀在樣例數據中,查詢的結果有10行記錄,而且只用10多秒就完成了.
EXPLAIN顯示出很好地使用了索引；從數據的規模來看,相當不錯了.
查詢計劃是在索引上遍歷并計算出目標記錄.
MYSQL必讀假設要從subcategory取出全部的字段.
可以把上面的查詢當成嵌套,然后用JOIN,或SELECT MAX之類(既然分組集對應的值都是唯一的),但也寫成跟下面的一樣：
?
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?
);?
?
MYSQL必讀注意：以上這條查詢很消耗時間,請慎重執行.
大家可能會理解：
從單從語句上理解,它會:a)計算出里面的查詢,找出那10個值,b)繼續找出那10條記錄,并且在primary索引上去找會非常地快.
錯,這是實際上的查詢計劃：
?
*************************** 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的語句查詢計劃,請看大概意思：mysql計劃從外到內執行查詢,而不是從內到外.
MYSQL必讀外面的查詢簡單地變成了SELECT * FROM subcategory.雖然里面的查詢對subcategory有個約束(WHERE category = 14),但出于某些原因mysql沒有將它作用于外面的查詢.我不知道是神馬原因.
我只知道它掃描了整張表(這就是 type:ALL 表示的意思),并且沒有使用任何的索引.
這是在10幾萬行記錄的表上掃描.
MYSQL必讀在外面的查詢,對每行都執行一次里面的查詢,盡管沒有值被里面的查詢使用到,因為里面的查詢被“優化”成引用外面的查詢.照此分析,查詢計劃變成了嵌套循環.
外面的查詢的每一次循環,都執行一次里面的查詢.
MYSQL必讀優化器重寫后的查詢計劃：
?
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))))?
)?
MYSQL必讀可以通過在EXPLAIN EXTENDED 后面帶上SHOW WARNINGS 得到優化后的查詢.請把穩在HAVING子句中指向的外部域.
MYSQL必讀眾所皆知mysql在有些情況下還不能很好地優化嵌套查詢,這個問題已經被廣泛報告過.
MYSQL必讀注意：
開發者有必要檢查查詢語句確保它們不是被糟糕地優化.
大多數情況下,平安起見若非是非必要,避免使用嵌套——尤其是WHERE...IN() 和 WHERE...NOT IN語句.
MYSQL必讀我的原則是“有疑問,EXPLAIN看看”.
如果面對的是一個大數據表,我會自然而然地產生疑問.
MYSQL必讀如何強制里面的查詢先執行?
上一節中的語句撞板只因為mysql把它當成相關的語句從外到里地執行,而不是當成不相關語句從里到外執行.
讓mysql先執行里面的查詢也是有方法的,當成臨時表來實現,從而避免巨大的性能開銷.
MYSQL必讀mysql從臨時表來實現嵌套查詢(某種程度上被訛傳的衍生表).
這意味著mysql數據庫先執行里面的查詢,并且把結果儲存在臨時表中,然后在其他的表里用到它.
MYSQL必讀這也是我寫這個查詢時所期待的執行方式.
MYSQL必讀查詢語句修改如下：
?
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必讀以上代碼所做的是：
把嵌套包著原來的嵌套查詢.
mysql會認為最里面是一個獨立的嵌套查詢先執行,然后現在只剩下包著外面的嵌套,它已經被裝進一個臨時表里,只有少量記錄,因此要快很多.
依此分析,這是相當笨的優化方法；
倒不如把它重寫成join方式.再說,免得被別人看到,當成多余代碼清理掉.
MYSQL必讀有些情況可以使用這種優化辦法,比如mysql拋出錯誤,嵌套查詢的表在其他地方被修改(參考：MySQL SELECT同時UPDATE同一張表 ).
不過,對于臨時表只能在查詢語句中使用一次的情況,這種辦法就無能為力了.
維易PHP培訓學院每天發布《Mysql必讀mysql嵌套查詢、聯表查詢的優化詳解》等實戰技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培養人才。
轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/13711.html
