Mysql join查詢的優化方法

join查詢是一個復雜的實現過程我們在實現join查詢時可以實現多表聯合查詢了，當然這時也會碰到性能不穩定的問題，下面我們來看Mysql join查詢的優化方法吧。

Mysql4.1開始支持SQL的子查詢。這個技術可以使用SELECT語句來創建一個單列的查詢結果，然後把這個結果作為過濾條件用在另一個查詢中。使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的SQL操作，同時也可以避免事務或者表鎖死，並且寫起來也很容易。但是，有些情況下，子查詢可以被更有效率的連接JOIN替代。

join的實現原理

join的實現是采用Nested Loop Join算法，就是通過驅動表的結果集作為循環基礎數據，然後一條一條的通過該結果集中的數據作為過濾條件到下一個表中查詢數據，然後合並結果。如果有多個join，則將前面的結果集作為循環數據，再一次作為循環條件到後一個表中查詢數據。

接下來通過一個三表join查詢來說明mysql的Nested Loop Join的實現方式。

 代碼如下 復制代碼

select m.subject msg_subject, c.content msg_content
from user_group g,group_message m,group_message_content c
where g.user_id = 1
and m.group_id = g.group_id
and c.group_msg_id = m.id

使用explain看看執行計劃：

 代碼如下 復制代碼

explain select m.subject msg_subject, c.content msg_content from user_group g,group_message m,
group_message_content c where g.user_id = 1 and m.group_id = g.group_id and c.group_msg_id = m.id\G;結果如下：

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: g
type: ref
possible_keys: user_group_gid_ind,user_group_uid_ind,user_group_gid_uid_ind
key: user_group_uid_ind
key_len: 4
ref: const
rows: 2
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: m
type: ref
possible_keys: PRIMARY,idx_group_message_gid_uid
key: idx_group_message_gid_uid
key_len: 4
ref: g.group_id
rows: 3
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: c
type: ref
possible_keys: idx_group_message_content_msg_id
key: idx_group_message_content_msg_id
key_len: 4
ref: m.id
rows: 2

Extra:從結果可以看出，explain選擇user_group作為驅動表，首先通過索引user_group_uid_ind來進行const條件的索引ref查找，然後用user_group表中過濾出來的結果集group_id字段作為查詢條件，對group_message循環查詢，然後再用過濾出來的結果集中的group_message的id作為條件與group_message_content的group_msg_id進行循環比較查詢，獲得最終的結果。

這個過程可以通過如下代碼來表示：

 代碼如下 復制代碼

for each record g_rec in table user_group that g_rec.user_id=1{
     for each record m_rec in group_message that m_rec.group_id=g_rec.group_id{
          for each record c_rec in group_message_content that c_rec.group_msg_id=m_rec.id
                pass the (g_rec.user_id, m_rec.subject, c_rec.content) row
          combination to output;
      }
}

如果去掉group_message_content表上面的group_msg_id字段的索引，執行計劃會有所不一樣。

 代碼如下 復制代碼

drop index idx_group_message_content_msg_id on group_message_content;
explain select m.subject msg_subject, c.content msg_content from user_group g,group_message m,
group_message_content c where g.user_id = 1 and m.group_id = g.group_id and c.group_msg_id = m.id\G;

得到的執行計劃如下：

 代碼如下 復制代碼 *************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: g
type: ref
possible_keys: user_group_uid_ind
key: user_group_uid_ind
key_len: 4
ref: const
rows: 2
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: m
type: ref
possible_keys: PRIMARY,idx_group_message_gid_uid
key: idx_group_message_gid_uid
key_len: 4
ref: g.group_id
rows: 3
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: c
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 96

Extra:Using where;Using join buffer因為刪除了索引，所以group_message_content的訪問從ref變成了ALL，keys相關的信息也變成了NULL，Extra信息也變成了Using Where和Using join buffer，也就是說需要獲取content內容只能通過對全表的數據進行where過濾才能獲取。Using join buffer是指使用到了Cache，只有當join類型為ALL，index，rang或者是index_merge的時候才會使用join buffer，它的使用過程可以用下面代碼來表示：

 代碼如下 復制代碼

for each record g_rec in table user_group{
      for each record m_rec in group_message that m_rec.group_id=g_rec.group_id{
           put (g_rec, m_rec) into the buffer
           if (buffer is full)
                 flush_buffer();
      }
}
flush_buffer(){
      for each record c_rec in group_message_content that c_rec.group_msg_id = c_rec.id{
            for each record in the buffer
                 pass (g_rec.user_id, m_rec.subject, c_rec.content) row combination to output;
      }
      empty the buffer;
}

在實現過程中可以看到把user_group和group_message的結果集放到join buffer中，而不用每次user_group和group_message關聯後馬上和group_message_content關聯，這也是沒有必要的；需要注意的是join buffer中只保留查詢結果中出現的列值，它的大小不依賴於表的大小，我們在偽代碼中看到當join buffer被填滿後，mysql將會flush buffer。

假設我們要將所有沒有訂單記錄的用戶取出來，可以用下面這個查詢完成：

 代碼如下 復制代碼 SELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

 
如果使用連接JOIN來完成這個查詢工作，速度將會快很多。尤其是當salesinfo表中對CustomerID建有索引的話，性能將會更好，查詢如下：

 代碼如下 復制代碼 SELECT * FROM customerinfo
    LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID
    WHERE salesinfo.CustomerID IS NULL

 
連接JOIN之所以更有效率一些，是因為 MySQL不需要在內存中創建臨時表來完成這個邏輯上的需要兩個步驟的查詢工作。

當兩個表相交時常常使用left join，當表中內容較多時往往結果數據集會非常大，甚至造成無法運行的後果。
數據庫提示如下錯誤信息

The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

解決方法1：按照提示執行相應代碼，我測試了

SET SQL_BIG_SELECTS=1

當執行sql語句之前新執行以上語句，查詢可以順利進行。

解決方法2：給做關聯的兩個字段添加索引，程序也順利執行了。

解決方法3：修改sql語句，減小結果集。

 代碼如下 復制代碼

修改前

SELECT w . * , r.sound_url
FROM sti_words AS w
LEFT JOIN sti_word_list_ja AS r ON w.word = r.word
WHERE w.insert_date = '2010-11-16'
AND w.user_id =54
GROUP BY w.word
ORDER BY w.id
LIMIT 0 , 30

修改後

select ww.*,r.sound_url
from(
SELECT w . *
FROM sti_words AS w
WHERE w.insert_date = '2010-11-16'
AND w.user_id =54
) as ww
LEFT JOIN sti_word_list_ja AS r ON ww.word = r.word
GROUP BY ww.word
ORDER BY ww.id
LIMIT 0 , 30

join語句的優化總結

1. 用小結果集驅動大結果集，盡量減少join語句中的Nested Loop的循環總次數；

2. 優先優化Nested Loop的內層循環，因為內層循環是循環中執行次數最多的，每次循環提升很小的性能都能在整個循環中提升很大的性能；

3. 對被驅動表的join字段上建立索引；

4. 當被驅動表的join字段上無法建立索引的時候，設置足夠的Join Buffer Size。