【mysql优化实战】第五章 多表连接索引的作用

不管是单表还是多表，单条件还是多条件，如果想加快查询速度，我们知道就必须适当的使用索引。但是是不是只要加上索引就一定生效呢？怎样才能让其生效呢？这就是本章节要介绍的最左前缀原则。
什么是最左前缀原则？
说白了，就是你检索条件的字段的顺序与建立的索引字段的顺序只要相同，那么相同的这几个索引字段就会生效。

下面我们会通过例子来说明

首先，我们先列出当前我们两个表的字段
big_table

uid_table

以上是我们之前创建的2张表，big_table总共100W条记录，uid_table总共12条记录。其中只要两个主键有主键索引，其他的字段还未建立索引。

下面的演示中，我们采用的方式为：先不使用索引【最左前缀原则】进行一次查询，在创建索引，然后通过【最左前缀原则】查询。为了看出差别我们采用大表驱动小表（我们知道如果小表驱动大表，那么不用索引速度也会很快，为了看出索引的效果，我们用大表驱动小表。实际环境中，尽量用小表驱动大表）

例子1：只有big_table的uid为主键，有主键索引。uid_table中的uid不是主键，没有索引。我们看看当前情况下sql
 

--只有big_table的uid是主键，有索引
explain select 'name',sex from big_table t1 LEFT JOIN uid_table t2 on t1.uid = t2.uid 【1】【耗时：3.774s】
explain select 'name',sex from big_table t1 LEFT JOIN uid_table t2 on t1.passwd = t2.passwd 【2】【耗时：3.269s】

可以看出当只有驱动表big_table的uid为主键索引时，索引的效果并未生效，【1】与【2】都是进行了全表扫描。并没有因为【1】的条件中的uid为主键，还要主键索引，就提高了查询速度。

我们看看如果把uid_table的uid字段创建索引后，效果会不会有改变

ALTER TABLE uid_table ADD INDEX idx_uid (`uid`);

此时我们再来执行explain，看看效果

从t2的type为ref可以看出创建的索引其效果了。并且Extra中也通过Using index说明使用了索引。但是为什么执行速度还是那么慢呢？这是因为我们是用100W的大表做的驱动表，t1进行了全表扫描（type为ALL）所以导致了慢。如果想要不在全表扫描，那就必须给条件中设定一个值。（参考例子2）

从上面的例子可以看出，如果两个表仅仅是通过一个字段进行连接，没有其他的条件情况先，一定要小表驱动大表。

例子2：现在有个场景，必须big_table和uid_table左联后，然后找到big_table中uid为102194的name
下面是两种有可能大家会的写法，让我们看看差别

explain select 'name',sex from big_table t1 LEFT JOIN uid_table t2 on t1.uid ='102194' and t1.uid = t2.uid  【1】【耗时：2.816s】
explain select 'name',sex from big_table t1 LEFT JOIN uid_table t2 on t1.uid = t2.uid where t1.uid ='102194'【2】【耗时：0.042s】

注意观察，在where中写t1.uid ='102194'和在on中写t1.uid ='102194'的查询速度差距很大。这是为什么呢，下面通过explain来看看mysql是怎么来解析执行这两个语句的，看看具体差在哪里


注意看：当uid='1021914'放到where中时，mysql会进行优化，通过t1的type变为了const就可以看出，rows也变成了1，说明仅需要搜索一行就找到了结果。而不是全表扫描了。所以尽量将类似于uid=这样的条件写在where中，不用现在on中。只要where中的这个条件字段是索引，就可以加快检索速度。

例子3
uid_table的passwd建立索引，big_table的passwd不建立索引

uid_table的passwd建立索引后，在查询时的确起到作用了，从rows为1便可看出，但是瓶颈在于驱动表t1进行了全表扫描，所以最终的速度还是没有得到明显提升。

针对上面情况，我们试试删除uid_table的passwd的索引，然后给big_table的passwd建立索引看看效果

可以看出当给big_table的passwd字段建立索引后，explain的结果不同了，rows总共变成了27（26+1），自然可知查询速度快了。但是要注意的是，t2的type还是ALL，他也是全表扫描了。之所以会块，是因为这个表一共就26条记录，如果记录也是100W，那么同样还是会慢。这里要关注的同一条语句，索引建在不同的表中，效果是不同的。还有就是注意explain后表的执行顺序也变化了。

上面都是只给某一个表中的passwd建立索引，现在我们给两个表的passwd都建立上索引，再来看看效果：

可以看出当两个passwd都创建索引后t1和t2的rows都为1，key也可以看出使用了索引。type也不再为ALL了。说明因为on和where都是用了索引passwd，mysql会优化查询。

例子4
我们看一下on用uid，where用passwd的效果，uid和passwd现在在两个表都建立了索引

explain select 'name',sex from big_table t1 LEFT JOIN uid_table t2 on t1.uid = t2.uid where t2.passwd ='efd936e16d4c5fc3fe8f493fdd9d7205'【1】【耗时：0.001】

这个例子中on和where用的不是一个字段了。从上图explain结果可以看出，当where用的是哪个表的字段，mysql就会先执行那个表的where，然后在从驱动表（左表）中找到对应where后的那条语句的uid对应的那个记录。这里之所以rows都为1，是因为都是用了索引。
下面我们看一次，如果where 不用索引字段passwd，而是使用name字段，那么怎样？来看一下

可以看出where用的t2的name，所以从explain分析可以看出，mysql还是会先执行id为1的t2这个表，也就是先执行where t2.name='a'。但是不幸的是，type变成了ALL，说明全表扫描了。这就预示着如果这个t2表（uid_table）如果不是26条记录，而是100W条记录，那么检索速度就会很慢了。这也就可以看出，在用on和where一起时，最好都是用索引的字段。

 

 

最后，总结一下：
本章节主要让大家通过explain来看出这样几个点：
1. 当在left join中，on和where尽量使用索引
2. 当left join时，如果有=号这样的条件，尽量放在where中，不要放到on中，可参考例子2。
3. 当left join 时，通过explain可以看出，where中用的字段是哪个表的，mysql执行时会先从哪表中通过where字段进行一次查询，然后在从主驱动表（左表）中找到与从where执行后的记录中找到匹配的数据（例3，例4可以参考）