教您如何為MYSQL表聯(lián)結(jié)做索引 讓表聯(lián)系更有效率(譯文）
經(jīng)常看到有人問(wèn):"我該怎么為表做索引,才能使表聯(lián)系更有效率?",經(jīng)常,有些人給出了答案,但是這些答案并沒(méi)有基于為表聯(lián)系做索引的基本理論.
這篇文章的目的就是描述MYSQL表聯(lián)結(jié)索引的基本理論,以一個(gè)很簡(jiǎn)單的示例開(kāi)始,為大家展示MYSQL表聯(lián)結(jié)的基本原理,然后再應(yīng)用這些原理到更加復(fù)雜的4個(gè)表聯(lián)結(jié)的請(qǐng)求。
我盡可能使用一些簡(jiǎn)單的測(cè)試數(shù)據(jù),畢竟我們關(guān)心的是理論而不是表哪些表的哪些數(shù)據(jù).因此我們就考慮這三個(gè)表:tblA,tblB,tblC. 每個(gè)表都有3列:col1,col2,col3(這樣并不符合標(biāo)準(zhǔn)).現(xiàn)在列的類型,表的意義,以及計(jì)劃要存儲(chǔ)哪種數(shù)據(jù),對(duì)我們來(lái)說(shuō)沒(méi)有關(guān)系.
- 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 |
- +-------+------+---------------+------+---------+------+------+-------------+
表實(shí)列出來(lái)使用這個(gè)命令,MYSQL當(dāng)處理所有聯(lián)結(jié)時(shí),使用一次掃描,多次聯(lián)結(jié)的方法.這就意味著mysql從第一個(gè)表讀一行,然后在第二個(gè)表中找一匹配行然后再在第三個(gè)表中找,等等.當(dāng)所有的表都找完后,MYSQL輸出查詢的列并通過(guò)表清單回溯直到在一個(gè)表中找到更多的匹配行.再?gòu)谋碇凶x取下行,再繼續(xù)處理下個(gè)表.
正如MYSQL手冊(cè)那個(gè)章節(jié)所說(shuō),當(dāng)用explain命令去輸出表時(shí),MYSQL先讀第一個(gè)表tblA,然后第二個(gè)表tblB,然后第三個(gè)表tblC,等等.來(lái)自前一個(gè)表的值被用于在當(dāng)前表中找匹配的行.在我們的例子中,tblA中的值被用于找tblB中的行,然后來(lái)自tblB中的值被用于找tblC中的行.一旦一次全掃描完成(找到匹配行,在tblA,tblB,tblC),MYSQL并不返回tblA,它將返回tblB去看是否有更多的行和與當(dāng)前來(lái)自tblA的值匹配.如果有,它得到這行,然后再到tblC中去匹配行.最重要的就是這是MYSQL連接的基本原理.
以EXPLAIN命令輸出的列,前一個(gè)表的值被用于查找當(dāng)前表的匹配行.
從原理到實(shí)際做索引
了解了MYSQL使用tblA中的值去找tblB的行.我們?cè)趺慈ピ黾铀饕齺?lái)幫助MYSQL?為了幫助MYSQL(或者人或者相關(guān)的事物)我們都必須知道它需要什么.MYSQL需要的就是怎樣的聯(lián)結(jié)方式.你的聯(lián)結(jié)方式就是MYSQL需要的.考慮一下tblA和tblB的聯(lián)結(jié):兩個(gè)表以tblA.col1 = tblB.col1,所以MYSQL需要一個(gè)tblB.col1,這樣mysql就能完成等式.因此,如果mysql需要tblB.col1,然后,我們就應(yīng)該給tblB.col1加索引,下邊就是新的explan的同一個(gè)請(qǐng)求:
- +-------+------+---------------+----------+---------+-----------+------+-------------+
- | 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現(xiàn)在使用了 ndx_col1來(lái)把tblB連到tblA上.當(dāng)MYSQL在tblB中找到行,就不會(huì)像之前那樣掃描,它使用tblA.col1的值和ndx_col1索引,直接取出匹配的行.這就是為什么tblB表的ref列是:"tblA.col1"tblC仍然是全表掃描.但是我們也可以用同樣的tblA和tblB的方式來(lái)修復(fù),通過(guò)查看MYSQL的需求:通過(guò)請(qǐng)求的"tblA.col2 = tblC.col1,"這部分,我們看到它需要tblC.col1因?yàn)槲覀円呀?jīng)有tblA.col2.把這列加上索引,explain就會(huì)出現(xiàn)下邊:
- +-------+------+---------------+----------+---------+| 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 |
- +-------+------+---------------+----------+---------+
有難度的部分?
真實(shí)環(huán)境中,你也許根本不會(huì)看到像上邊的SQL請(qǐng)求.你更多可能遇到像這樣的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;
一看上去有點(diǎn)令人畏懼的query:4個(gè)表.一個(gè)統(tǒng)計(jì)函數(shù),9個(gè)where條件,一個(gè)groupby .EXPLAIN強(qiáng)大之處就是
我們能不理所有這些現(xiàn)在,并很容易接近它每次兩個(gè)表,正如我們之前做的那樣,決定每一步mysql需要什么.
這是一個(gè)真正的需求.所有的表和列都重命名去保護(hù)原來(lái)的一致.開(kāi)始之前,先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 |
- +-------+--------+---------------+---------+---------+---------------+-------+----------------------------------------------+
首先一個(gè)決定的一個(gè)詞影響一個(gè)聯(lián)結(jié):結(jié)果集. 一個(gè)結(jié)果集,顯然來(lái)自一個(gè)請(qǐng)求的結(jié)果集.為了聯(lián)結(jié),MYSQL
需要讀每個(gè)表來(lái)估計(jì)有多少行數(shù)據(jù).壞情況結(jié)束了,因?yàn)槠渌黽here條件將減少請(qǐng)求產(chǎn)生的行數(shù).這個(gè) 請(qǐng)求產(chǎn)生的94百萬(wàn)行結(jié)果集.這就是為什么缺少索引是多么危險(xiǎn).幾千行的幾千倍,數(shù)量級(jí)已達(dá)到百萬(wàn)級(jí)了.
現(xiàn)在這個(gè)請(qǐng)求需要什么?從tblA和tblBga 開(kāi)始.找出請(qǐng)求中兩個(gè)表在哪里聯(lián)結(jié):
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做索引,因?yàn)樵谖也榭吹钠渌?qǐng)求中,這些列最經(jīng)常用到.折中是優(yōu)化的一部分.再專業(yè)也不能去絕對(duì)的為每一條單獨(dú)請(qǐng)求找到最合適的索引.相反,你只能找到最經(jīng)常使用的.在這個(gè)例子中,我們將性能改變.在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 |
- +-------+--------+---------------+-------------+---------+------------------------+------+----------------------------------------------+
請(qǐng)求的結(jié)果集降低了 99.3%達(dá)到692280行.但是為什么止于此呢?我們能很容易的處理tblA的全表掃描.往往,為第一個(gè)表索引,就像為單獨(dú)一個(gè)表做索引.在這種情況下,你查看請(qǐng)求的SQL,看是否表正在被另外的方式限制.在這個(gè)情況,我們幸運(yùn)看到tblA的條件:"AND tblA.ex_id = 1001". 因?yàn)槲覀冊(cè)趦?yōu)化的講座的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 |
- +-------+--------+---------------+-------------+---------+------------------------+------+-
現(xiàn)在請(qǐng)求的結(jié)果集是641行!從94百萬(wàn)行降下來(lái).你看幾科是100%的減少量.進(jìn)一步研究查詢請(qǐng)求,我們還能處理掉,extra中的usring tempory,usring filesort.盡管最初看上去是挑戰(zhàn),我們看到,如果你第次以兩個(gè)表處理,隔離并索引MYSQL需要的,為聯(lián)結(jié)做過(guò)引并不困難:
結(jié)論:
使復(fù)雜的聯(lián)結(jié)和知道在哪里索引成為簡(jiǎn)單的工作就要意識(shí)到兩件事:
1.無(wú)論多復(fù)雜的請(qǐng)求,你僅僅以兩個(gè)MYSQL表聯(lián)結(jié)的途徑處理在EXPLAIN列出來(lái)的清單中的順序.
2.來(lái)自前一個(gè)表的值已經(jīng)找到;我們工作就是幫助MYSQL使用索引這些找到的值在當(dāng)前的表上來(lái)找到匹配的行.
【編輯推薦】
MySQL批量導(dǎo)入數(shù)據(jù)的實(shí)現(xiàn)
