在mysql中如何为连接添加索引_在MySQL中如何为连接添加索引

http://hackmysql.com/case4

译文：

我先通过一个简单的例子说明在MySQL中如何为连接添加索引，然后再看一个有挑战性的例子。

简单的3个表的连接

表结构很简单，3个表tblA, tblB, tblC，每个表有3个字段：col1, col2, col3。在没有索引的情况下连接3个表

SELECT

*

FROM

tblA,

tblB,

tblC

WHERE

tblA.col1 = tblB.col1

ANDtblA.col2 = tblC.col1;

SELECT

*

FROM

tblA,

tblB,

tblC

WHERE

tblA.col1 = tblB.col1

AND tblA.col2 = tblC.col1;

explain的结果如下：

+-------+------+---------------+------+---------+------+------+-------------+

| 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结果：

+-------+------+---------------+----------+---------+-----------+------+-------------+

| 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：

+-------+------+---------------+----------+---------+-----------+------+-------------+

| 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)ascorrect,

tblA.type,

tblA.se_type

FROM

tblA,

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;

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的结果：

+-------+--------+---------------+---------+---------+---------------+-------+----------------------------------------------+

| 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中：

ANDtblB.q_num = tblA.q_num

ANDtblB.se_num = tblA.se_num

ANDtblB.ans = tblA.ans

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的结果：

+-------+--------+---------------+-------------+---------+------------------------+------+----------------------------------------------+

| 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索引：

+-------+--------+---------------+-------------+---------+------------------------+------+----------------------------------------------+

| 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的需求加索引，整个过程并不困难。

结论

为复杂的连接加索引要认识到两件事：不管sql多复杂，每次只看explain中的两个表

先前表中的值已经有了，我们的工作就是通过索引帮助MySQL在当前表中使用这些值来找到匹配行