mysql嵌套查詢和聯(lián)表查詢優(yōu)化方法

更新時間：2013年02月28日 10:44:51 作者：

本文描述了mysql嵌套查詢和聯(lián)表查詢優(yōu)化的方法，有些情況可以使用這種優(yōu)化方法，而有些情況，這種方法就無能為力了

嵌套查詢糟糕的優(yōu)化
在上面我提到過，不考慮特殊的情況，聯(lián)表查詢要比嵌套查詢更有效。盡管兩條查詢表達的是同樣的意思，盡管你的計劃是告訴服務器要做什么，然后讓它決定怎么做，但有時候你非得告訴它改怎么做。否則優(yōu)化器可能會做傻事。我最近就碰到這樣的情況。這幾個表是三層分級關系：category, subcategory和item。有幾千條記錄在category表，幾百條記錄在subcategory表，以及幾百萬條在item表。你可以忽略category表了，我只是交代一下背景，以下查詢語句都不涉及到它。這是創(chuàng)建表的語句：

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

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

我想找出某個category中item數(shù)大于2000的全部subcategory。首先，我找到一個subcategory item數(shù)大于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數(shù)大于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;  
 

在我的樣例數(shù)據(jù)里，查詢的結果有10行記錄，而且只用10多秒就完成了。EXPLAIN顯示出很好地使用了索引；從數(shù)據(jù)的規(guī)模來看，相當不錯了。查詢計劃是在索引上遍歷并計算出目標記錄。目前為止，非常好。

這回假設我要從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)繼續(xù)找出那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計劃從外到內執(zhí)行查詢，而不是從內到外。我會一個一個地介紹查詢的每個部分。

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

在外面的查詢，對每行都執(zhí)行一次里面的查詢，盡管沒有值被里面的查詢使用到，因為里面的查詢被“優(yōu)化”成引用外面的查詢。照此分析，查詢計劃變成了嵌套循環(huán)。外面的查詢的每一次循環(huán)，都執(zhí)行一次里面的查詢。下面就是優(yōu)化器重寫后的查詢計劃：

[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 得到優(yōu)化后的查詢。請留意在HAVING子句中指向的外部域。

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

我自己的原則是“有疑問，EXPLAIN看看”。如果面對的是一個大數(shù)據(jù)表，我會自然而然地產生疑問。

如何強制里面的查詢先執(zhí)行
上一節(jié)中的語句撞板只因為mysql把它當成相關的語句從外到里地執(zhí)行，而不是當成不相關語句從里到外執(zhí)行。讓mysql先執(zhí)行里面的查詢也是有辦法的，當成臨時表來實現(xiàn)，從而避免巨大的性能開銷。

mysql從臨時表來實現(xiàn)嵌套查詢（某種程度上被訛傳的衍生表）。這意味著mysql先執(zhí)行里面的查詢，并且把結果儲存在臨時表中，然后在其他的表里用到它。這就是我寫這個查詢時所期待的執(zhí)行方式。查詢語句修改如下：
[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會認為最里面是一個獨立的嵌套查詢先執(zhí)行，然后現(xiàn)在只剩下包著外面的嵌套，它已經(jīng)被裝進一個臨時表里，只有少量記錄，因此要快很多。依此分析，這是相當笨的優(yōu)化辦法；倒不如把它重寫成join方式。再說，免得被別人看到，當成多余代碼清理掉。

有些情況可以使用這種優(yōu)化方法，比如mysql拋出錯誤，嵌套查詢的表在其他地方被修改（譯注：另一篇文章 MySQL SELECT同時UPDATE同一張表）。不幸的是，對于臨時表只能在查詢語句中使用一次的情況，這種方法就無能為力了。

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

您可能感興趣的文章: