在MySQL中如何為串連添加索引

我先通過一個簡單的例子說明在MySQL中如何為串連添加索引，然後再看一個有挑戰性的例子。

簡單的3個表的串連

表結構很簡單，3個表tblA, tblB, tblC，每個表有3個欄位：col1, col2, col3。
在沒有索引的情況下串連3個表

SELECT
*
FROM
tblA,
tblB,
tblC
WHERE
tblA.col1 = tblB.col1
AND tblA.col2 = tblC.col1;

explain的結果如下：

Java代碼

1. +-------+------+---------------+------+---------+------+------+-------------+   
2. | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |   
3. +-------+------+---------------+------+---------+------+------+-------------+   
4. | tblA  | ALL  | NULL          | NULL |    NULL | NULL | 1000 |             |   
5. | tblB  | ALL  | NULL          | NULL |    NULL | NULL | 1000 | Using where |   
6. | tblC  | ALL  | NULL          | NULL |    NULL | NULL | 1000 | Using where |   
7. +-------+------+---------------+------+---------+------+------+-------------+  

   +-------+------+---------------+------+---------+------+------+-------------+   | 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的手冊中(7.2.1)：
表以它們在處理查詢過程中將被MySQL讀入的順序被列出。MySQL用一遍掃描多次聯結（single-sweep multi-join）的方式解決所有聯結。這意味著MySQL從第一個表中讀一行，然後找到在第二個表中的一個匹配行，然後在第3個表中等等。當所有的表處理完後，它輸出選中的列並且返回表清單直到找到一個有更多的匹配行的表。從該表讀入下一行並繼續處理下一個表。
如手冊所說的，MySQL讀第一個表(tnlA)，然後第二個(tblB)，然後第三個(tblC)，像explain中輸出的一樣。先前的表中的值用來尋找當前表中的行。在我們的例子中，tblA中的值用來找tblB中的匹配行，然後tblB的值來找tblC的行。當一個完整的掃描結束(在表tblA,tblB,tblC中找到了結果)，MySQL不會返回tblA,它到tblB中查看是否有更多的行匹配當前tblA的值。如果有，它拿出這一行，然後再在tblC中找匹配的。記住 MySQL串連的基本原則是很重要的：先前的表中的值用來尋找當前表中的行。

按原理建索引

知道了MySQL使用從tblA中得到的值尋找tblB中的行，我們需要怎麼建索引來協助MySQL？為此我們要知道它需要什麼。考慮串連tblA和 tblB：它們通過“tblA.col1 = tblB.col1”來串連。我們已經有了tblA.col1的值，所以MySQL需要一個tblB.col1的值來完成等值操作。因此如果MySQL需要tblB.col1，我們就在tblB.col1上加索引。加了之後，這是新的explain結果：

Java代碼

1. +-------+------+---------------+----------+---------+-----------+------+-------------+   
2. | table | type | possible_keys | key      | key_len | ref       | rows | Extra       |   
3. +-------+------+---------------+----------+---------+-----------+------+-------------+   
4. | tblA  | ALL  | NULL          | NULL     |    NULL | NULL      | 1000 |             |   
5. | tblB  | ref  | ndx_col1      | ndx_col1 |       5 | tblA.col1 |    1 | Using where |   
6. | tblC  | ALL  | NULL          | NULL     |    NULL | NULL      | 1000 | Using where |   
7. +-------+------+---------------+----------+---------+-----------+------+-------------+  

+-------+------+---------------+----------+---------+-----------+------+-------------+| 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中的行時，使用了ndx_col1索引通過 tblA.col1的值直接得到匹配的行，而不是像以前需要做表掃描。這就是為什麼tblB的ref列說“tablA.col1”。tblC現在還是用表掃描，這可以通過同樣的方法解決。查看MySQL的需求：從sql中串連兩表的語句“tblA.col2 = tblC.col1”可以看出它需要tblC.col1因為我們已經有了tblA.col2。給這一列加上索引之後explain：

Java代碼

1. +-------+------+---------------+----------+---------+-----------+------+-------------+   
2. | table | type | possible_keys | key      | key_len | ref       | rows | Extra       |   
3. +-------+------+---------------+----------+---------+-----------+------+-------------+   
4. | tblA  | ALL  | NULL          | NULL     |    NULL | NULL      | 1000 |             |   
5. | tblB  | ref  | ndx_col1      | ndx_col1 |       5 | tblA.col1 |    1 | Using where |   
6. | tblC  | ref  | ndx_col1      | ndx_col1 |       5 | tblA.col2 |    1 | Using where |   
7. +-------+------+---------------+----------+---------+-----------+------+-------------+  

+-------+------+---------------+----------+---------+-----------+------+-------------+| 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。所以你可能更想看看這樣的：

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;

乍一看是很複雜的：有4個表，有彙總函式，有9個where條件，還有一個group by。explain的偉大之處在於我們現在可以忽略這些，每次只看兩個表，判斷每一步MySQL需要什麼。這是一個實際的查詢，只是欄位名有一些改動。explain的結果：

Java代碼

1. +-------+--------+---------------+---------+---------+---------------+-------+----------------------------------------------+   
2. | table | type   | possible_keys | key     | key_len | ref           | rows  | Extra                                        |   
3. +-------+--------+---------------+---------+---------+---------------+-------+----------------------------------------------+   
4. | tblA  | ALL    | NULL          | NULL    |    NULL | NULL          |  1080 | Using where; Using temporary; Using filesort |   
5. | tblB  | ALL    | NULL          | NULL    |    NULL | NULL          | 87189 | Using where                                  |   
6. | tblC  | eq_ref | PRIMARY       | PRIMARY |       4 | tblB.st_ex_id |     1 | Using where                                  |   
7. | tblD  | eq_ref | PRIMARY       | PRIMARY |       4 | tblA.ex_id    |     1 | Using where                                  |   
8. +-------+--------+---------------+---------+---------+---------------+-------+----------------------------------------------+  

+-------+--------+---------------+---------+---------+---------------+-------+----------------------------------------------+| 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條件通常會減少很多的行數。但這個查詢的結果集有9400萬行。這就是沒有索引串連很危險的原因；幾千行乘幾千行你就會有一個上百萬的結果集了。
那麼現在這個查詢需要什嗎？從tblA和tblB開始。在sql中：

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中的一個。我選擇在se_num和q_num上加索引因為在幾乎所有其他的查詢中我都會需要它們。折中是最佳化的一部分，多數人沒有時間去為每一個查詢找最優的索引方案，只能是找到一個對於大多數情況而言最優的方案。在tblB上加索引(se_num, q_num)，explain的結果：

Java代碼

1. +-------+--------+---------------+-------------+---------+------------------------+------+----------------------------------------------+   
2. | table | type   | possible_keys | key         | key_len | ref                    | rows | Extra                                        |   
3. +-------+--------+---------------+-------------+---------+------------------------+------+----------------------------------------------+   
4. | tblA  | ALL    | NULL          | NULL        |    NULL | NULL                   | 1080 | Using where; Using temporary; Using filesort |   
5. | tblB  | ref    | ndx_secn_qn   | ndx_secn_qn |       2 | tblA.se_num,tblA.q_num |  641 | Using where                                  |   
6. | tblC  | eq_ref | PRIMARY       | PRIMARY     |       4 | tblB.st_ex_id          |    1 | Using where                                  |   
7. | tblD  | eq_ref | PRIMARY       | PRIMARY     |       4 | tblA.ex_id             |    1 | Using where                                  |   
8. +-------+--------+---------------+-------------+---------+------------------------+------+----------------------------------------------+  

+-------+--------+---------------+-------------+---------+------------------------+------+----------------------------------------------+| 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的表掃描。因為它是第一個表，我們並不需要為串連加索引，這在tblB上已經做過了。一般來說，給第一個表加索引可以把它當成只在這一個表上查詢的情況。在這個例子中很幸運，tblA是："AND tblA.ex_id = 1001"。我們只需要加ex_id索引：

Java代碼

1. +-------+--------+---------------+-------------+---------+------------------------+------+----------------------------------------------+   
2. | table | type   | possible_keys | key         | key_len | ref                    | rows | Extra                                        |   
3. +-------+--------+---------------+-------------+---------+------------------------+------+----------------------------------------------+   
4. | tblA  | ref    | ndx_ex_id     | ndx_ex_id   |       4 | const                  |    1 | Using where; Using temporary; Using filesort |   
5. | tblB  | ref    | ndx_secn_qn   | ndx_secn_qn |       2 | tblA.se_num,tblA.q_num |  641 | Using where                                  |   
6. | tblC  | eq_ref | PRIMARY       | PRIMARY     |       4 | tblB.st_ex_id          |    1 | Using where                                  |   
7. | tblD  | eq_ref | PRIMARY       | PRIMARY     |       4 | tblA.ex_id             |    1 | Using where                                  |   
8. +-------+--------+---------------+-------------+---------+------------------------+------+----------------------------------------------+  

+-------+--------+---------------+-------------+---------+------------------------+------+----------------------------------------------+| 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行。相比開始的9400萬，可以說了下降了100%。如果繼續研究這個查詢我們還可以去掉temp table和filesort，但現在查詢已經很快了，也已經說明了如何為串連加索引。儘管最初看這個查詢很麻煩，但可以看到只要每次獨立的看兩張表，為 MySQL的需求加索引，整個過程並不困難。

結論

為複雜的串連加索引要認識到兩件事：

1. 不管sql多複雜，每次只看explain中的兩個表

2. 先前表中的值已經有了，我們的工作就是通過索引協助MySQL在當前表中使用這些值來找到匹配行