程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql嵌套查詢和聯表查詢優化方法

mysql嵌套查詢和聯表查詢優化方法

編輯: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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved