教您如何為MYSQL表聯結做索引 讓表聯系更有效率(譯文）
經常看到有人問:"我該怎么為表做索引,才能使表聯系更有效率?",經常,有些人給出了答案,但是這些答案并沒有基于為表聯系做索引的基本理論.
這篇文章的目的就是描述MYSQL表聯結索引的基本理論,以一個很簡單的示例開始,為大家展示MYSQL表聯結的基本原理,然后再應用這些原理到更加復雜的4個表聯結的請求。
我盡可能使用一些簡單的測試數據,畢竟我們關心的是理論而不是表哪些表的哪些數據.因此我們就考慮這三個表:tblA,tblB,tblC. 每個表都有3列:col1,col2,col3(這樣并不符合標準).現在列的類型,表的意義,以及計劃要存儲哪種數據,對我們來說沒有關系.
- SELECT * FROM tblA, tblB,tblC
- WHERE
- tblA.col1 = tblB.col1
- AND tblA.col2 = tblC.col1;
And EXPLAIN for the query:
- +-------+------+---------------+------+---------+------+------+-------------+
- | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +-------+------+---------------+------+---------+------+------+-------------+
- | tblA | ALL | NULL | NULL | NULL | NULL | 1000 | |
- | tblB | ALL | NULL | NULL | NULL | NULL | 1000 | Using where |
- | tblC | ALL | NULL | NULL | NULL | NULL | 1000 | Using where |
- +-------+------+---------------+------+---------+------+------+-------------+
表實列出來使用這個命令,MYSQL當處理所有聯結時,使用一次掃描,多次聯結的方法.這就意味著mysql從第一個表讀一行,然后在第二個表中找一匹配行然后再在第三個表中找,等等.當所有的表都找完后,MYSQL輸出查詢的列并通過表清單回溯直到在一個表中找到更多的匹配行.再從表中讀取下行,再繼續處理下個表.
正如MYSQL手冊那個章節所說,當用explain命令去輸出表時,MYSQL先讀第一個表tblA,然后第二個表tblB,然后第三個表tblC,等等.來自前一個表的值被用于在當前表中找匹配的行.在我們的例子中,tblA中的值被用于找tblB中的行,然后來自tblB中的值被用于找tblC中的行.一旦一次全掃描完成(找到匹配行,在tblA,tblB,tblC),MYSQL并不返回tblA,它將返回tblB去看是否有更多的行和與當前來自tblA的值匹配.如果有,它得到這行,然后再到tblC中去匹配行.最重要的就是這是MYSQL連接的基本原理.
以EXPLAIN命令輸出的列,前一個表的值被用于查找當前表的匹配行.
從原理到實際做索引
了解了MYSQL使用tblA中的值去找tblB的行.我們怎么去增加索引來幫助MYSQL?為了幫助MYSQL(或者人或者相關的事物)我們都必須知道它需要什么.MYSQL需要的就是怎樣的聯結方式.你的聯結方式就是MYSQL需要的.考慮一下tblA和tblB的聯結:兩個表以tblA.col1 = tblB.col1,所以MYSQL需要一個tblB.col1,這樣mysql就能完成等式.因此,如果mysql需要tblB.col1,然后,我們就應該給tblB.col1加索引,下邊就是新的explan的同一個請求:
- +-------+------+---------------+----------+---------+-----------+------+-------------+
- | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +-------+------+---------------+----------+---------+-----------+------+-------------+
- | tblA | ALL | NULL | NULL | NULL | NULL | 1000 | |
- | tblB | ref | ndx_col1 | ndx_col1 | 5 | tblA.col1 | 1 | Using where |
- | tblC | ALL | NULL | NULL | NULL | NULL | 1000 | Using where |
- +-------+------+---------------+----------+---------+-----------+------+-------------+
正如我們看到的,MYSQL現在使用了 ndx_col1來把tblB連到tblA上.當MYSQL在tblB中找到行,就不會像之前那樣掃描,它使用tblA.col1的值和ndx_col1索引,直接取出匹配的行.這就是為什么tblB表的ref列是:"tblA.col1"tblC仍然是全表掃描.但是我們也可以用同樣的tblA和tblB的方式來修復,通過查看MYSQL的需求:通過請求的"tblA.col2 = tblC.col1,"這部分,我們看到它需要tblC.col1因為我們已經有tblA.col2.把這列加上索引,explain就會出現下邊:
- +-------+------+---------------+----------+---------+| table | type | possible_keys | key | key_len | ref | rows | Extra |
- +-------+------+---------------+----------+---------+
- | tblA | ALL | NULL | NULL | NULL | NULL | 1000 | |
- | tblB | ref | ndx_col1 | ndx_col1 | 5 | tblA.col1 | 1 | Using where |
- | tblC | ref | ndx_col1 | ndx_col1 | 5 | tblA.col2 | 1 | Using where |
- +-------+------+---------------+----------+---------+
有難度的部分?
真實環境中,你也許根本不會看到像上邊的SQL請求.你更多可能遇到像這樣的SQL:
- SELECT
- COUNT(tblB.a_id) as correct,
- tblA.type,
- tblA.se_type
- FROM tblA,
- tblB,
- tblC,
- tblD
- WHERE
- tblA.ex_id = tblC.ex_id
- AND tblC.st_ex_id = tblB.st_ex_id
- AND tblB.q_num = tblA.q_num
- AND tblB.se_num = tblA.se_num
- AND tblD.ex_id = tblA.ex_id
- AND tblD.exp <> tblB.se_num
- AND tblB.ans = tblA.ans
- AND tblA.ex_id = 1001
- AND tblC.r_id = 542
- GROUP BY
- tblA.type,
- tblA.se_type;
一看上去有點令人畏懼的query:4個表.一個統計函數,9個where條件,一個groupby .EXPLAIN強大之處就是
我們能不理所有這些現在,并很容易接近它每次兩個表,正如我們之前做的那樣,決定每一步mysql需要什么.
這是一個真正的需求.所有的表和列都重命名去保護原來的一致.開始之前,先EXPLAIN:
- +-------+--------+---------------+---------+---------+---------------+-------+----------------------------------------------+
- | table | type | possible_keys | key | key_len | ref| rows | Extra |
- +-------+--------+---------------+---------+---------+---------------+-------+----------------------------------------------+
- | tblA | ALL | NULL | NULL | NULL | NULL | 1080 | Using where; Using temporary; Using filesort |
- | tblB | ALL | NULL | NULL | NULL | NULL | 87189 | Using where |
- | tblC | eq_ref | PRIMARY | PRIMARY | 4 | tblB.st_ex_id | 1 | Using where |
- | tblD | eq_ref | PRIMARY | PRIMARY | 4 | tblA.ex_id | 1 | Using where |
- +-------+--------+---------------+---------+---------+---------------+-------+----------------------------------------------+
首先一個決定的一個詞影響一個聯結:結果集. 一個結果集,顯然來自一個請求的結果集.為了聯結,MYSQL
需要讀每個表來估計有多少行數據.壞情況結束了,因為其它where條件將減少請求產生的行數.這個 請求產生的94百萬行結果集.這就是為什么缺少索引是多么危險.幾千行的幾千倍,數量級已達到百萬級了.
現在這個請求需要什么?從tblA和tblBga 開始.找出請求中兩個表在哪里聯結:
AND tblB.q_num = tblA.q_num
AND tblB.se_num = tblA.se_num
AND tblB.ans = tblA.ans
MYSQL至少需要 q_num,se_num,ans.我選擇把q_num和se_num做索引,因為在我查看的其它請求中,這些列最經常用到.折中是優化的一部分.再專業也不能去絕對的為每一條單獨請求找到最合適的索引.相反,你只能找到最經常使用的.在這個例子中,我們將性能改變.在tblB 上索引(se_num, q_num),EXPLAIN:
- +-------+--------+---------------+-------------+---------+------------------------+------+----------------------------------------------+
- | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +-------+--------+---------------+-------------+---------+------------------------+------+----------------------------------------------+
- | tblA | ALL | NULL | NULL | NULL | NULL | 1080 | Using where; Using temporary; Using filesort |
- | tblB | ref | ndx_secn_qn | ndx_secn_qn | 2 | tblA.se_num,tblA.q_num | 641 | Using where |
- | tblC | eq_ref | PRIMARY | PRIMARY | 4 | tblB.st_ex_id | 1 | Using where |
- | tblD | eq_ref | PRIMARY | PRIMARY | 4 | tblA.ex_id | 1 | Using where |
- +-------+--------+---------------+-------------+---------+------------------------+------+----------------------------------------------+
請求的結果集降低了 99.3%達到692280行.但是為什么止于此呢?我們能很容易的處理tblA的全表掃描.往往,為第一個表索引,就像為單獨一個表做索引.在這種情況下,你查看請求的SQL,看是否表正在被另外的方式限制.在這個情況,我們幸運看到tblA的條件:"AND tblA.ex_id = 1001". 因為我們在優化的講座的case 1:基本索引,我們所做的就是索引 ex_id 在表tblA上.
- +-------+--------+---------------+-------------+---------+------------------------+------+-
- | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +-------+--------+---------------+-------------+---------+------------------------+------+-
- | tblA | ref | ndx_ex_id | ndx_ex_id | 4 | const | 1 | Using where; Using temporary; Using filesort |
- | tblB | ref | ndx_secn_qn | ndx_secn_qn | 2 | tblA.se_num,tblA.q_num | 641 | Using where |
- | tblC | eq_ref | PRIMARY | PRIMARY | 4 | tblB.st_ex_id | 1 | Using where |
- | tblD | eq_ref | PRIMARY | PRIMARY | 4 | tblA.ex_id | 1 | Using where |
- +-------+--------+---------------+-------------+---------+------------------------+------+-
現在請求的結果集是641行!從94百萬行降下來.你看幾科是100%的減少量.進一步研究查詢請求,我們還能處理掉,extra中的usring tempory,usring filesort.盡管最初看上去是挑戰,我們看到,如果你第次以兩個表處理,隔離并索引MYSQL需要的,為聯結做過引并不困難:
結論:
使復雜的聯結和知道在哪里索引成為簡單的工作就要意識到兩件事:
1.無論多復雜的請求,你僅僅以兩個MYSQL表聯結的途徑處理在EXPLAIN列出來的清單中的順序.
2.來自前一個表的值已經找到;我們工作就是幫助MYSQL使用索引這些找到的值在當前的表上來找到匹配的行.
【編輯推薦】
