经常看到有人问:"我该怎么为表做索引,才能使表联系更有效率?",经常,有些人给出了答案,但是这些答案并没有基于为表联系做索引的基本理论.

这篇文章的目的就是描述MYSQL表联结索引的基本理论,以一个很简单的示例开始,为大家展示MYSQL表联结的基本原理,然后再应用这些原理到更加复杂的4个表联结的请求。

我尽可能使用一些简单的测试数据,毕竟我们关心的是理论而不是表哪些表的哪些数据.因此我们就考虑这三个表:tblA,tblB,tblC. 每个表都有3列:col1,col2,col3(这样并不符合标准).现在列的类型,表的意义,以及计划要存储哪种数据,对我们来说没有关系.

SELECT*FROMtblA,tblB,tblC 
WHERE 
tblA.col1=tblB.col1 
ANDtblA.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|Usingwhere| 
|tblC|ALL|NULL|NULL|NULL|NULL|1000|Usingwhere| 
+-------+------+---------------+------+---------+------+------+-------------+ 

表实列出来使用这个命令,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|Usingwhere| 
|tblC|ALL|NULL|NULL|NULL|NULL|1000|Usingwhere| 
+-------+------+---------------+----------+---------+-----------+------+-------------+ 

正如我们看到的,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|Usingwhere| 
|tblC|ref|ndx_col1|ndx_col1|5|tblA.col2|1|Usingwhere| 
+-------+------+---------------+----------+---------+

有难度的部分?

真实环境中,你也许根本不会看到像上边的SQL请求.你更多可能遇到像这样的SQL:

SELECT 
COUNT(tblB.a_id)ascorrect, 
tblA.type, 
tblA.se_type 
FROMtblA, 
tblB, 
tblC, 
tblD 
WHERE 
tblA.ex_id=tblC.ex_id 
ANDtblC.st_ex_id=tblB.st_ex_id 
ANDtblB.q_num=tblA.q_num 
ANDtblB.se_num=tblA.se_num 
ANDtblD.ex_id=tblA.ex_id 
ANDtblD.exp<>tblB.se_num 
ANDtblB.ans=tblA.ans 
ANDtblA.ex_id=1001
ANDtblC.r_id=542 
GROUPBY 
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|Usingwhere;Usingtemporary;Usingfilesort| 
|tblB|ALL|NULL|NULL|NULL|NULL|87189|Usingwhere| 
|tblC|eq_ref|PRIMARY|PRIMARY|4|tblB.st_ex_id|1|Usingwhere| 
|tblD|eq_ref|PRIMARY|PRIMARY|4|tblA.ex_id|1|Usingwhere| 
+-------+--------+---------------+---------+---------+---------------+-------+----------------------------------------------+ 

首先一个决定的一个词影响一个联结:结果集. 一个结果集,显然来自一个请求的结果集.为了联结,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|Usingwhere;Usingtemporary;Usingfilesort| 
|tblB|ref|ndx_secn_qn|ndx_secn_qn|2|tblA.se_num,tblA.q_num|641|Usingwhere| 
|tblC|eq_ref|PRIMARY|PRIMARY|4|tblB.st_ex_id|1|Usingwhere| 
|tblD|eq_ref|PRIMARY|PRIMARY|4|tblA.ex_id|1|Usingwhere| 
+-------+--------+---------------+-------------+---------+------------------------+------+----------------------------------------------+ 

请求的结果集降低了 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|Usingwhere;Usingtemporary;Usingfilesort| 
|tblB|ref|ndx_secn_qn|ndx_secn_qn|2|tblA.se_num,tblA.q_num|641|Usingwhere| 
|tblC|eq_ref|PRIMARY|PRIMARY|4|tblB.st_ex_id|1|Usingwhere| 
|tblD|eq_ref|PRIMARY|PRIMARY|4|tblA.ex_id|1|Usingwhere| 
+-------+--------+---------------+-------------+---------+------------------------+------+- 

现在请求的结果集是641行!从94百万行降下来.你看几科是100%的减少量.进一步研究查询请求,我们还能处理掉,extra中的usring tempory,usring filesort.尽管最初看上去是挑战,我们看到,如果你第次以两个表处理,隔离并索引MYSQL需要的,为联结做过引并不困难:

结论:

使复杂的联结和知道在哪里索引成为简单的工作就要意识到两件事:

1.无论多复杂的请求,你仅仅以两个MYSQL表联结的途径处理在EXPLAIN列出来的清单中的顺序.

2.来自前一个表的值已经找到;我们工作就是帮助MYSQL使用索引这些找到的值在当前的表上来找到匹配的行.