经常看到有人问:"我该怎么为表做索引,才能使表联系更有效率?",经常,有些人给出了答案,但是这些答案并没有基于为表联系做索引的基本理论.
这篇文章的目的就是描述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使用索引这些找到的值在当前的表上来找到匹配的行.
【编辑推荐】
