优化MySQL（一）——连接查询

1.连接查询执行顺序问题

根据网上说的执行顺序：附上熟悉的鱼骨图

但真的是这样的吗，假设a表的数据量是100w，b表的数据量是100w，如果先笛卡尔乘积再筛选，那数据量肯定巨大的。

我们根据实际案例来看下：

course_section表，数据量9.7w ， course 表，264

正常情况下（select * from course_section a left join course b on a.course.id=b.id），左连接的驱动表会是course_section，通过explain查看执行计划，可以看出驱动表与设想相符合，驱动表为a表

然后我们一点点在where后添加筛选条件，先是对a表添加筛选条件，再查看执行计划，与不添加无差别，驱动表还是a表

然后我们再添加b表的筛选条件，会发现，执行计划发生了变化，驱动表变成了b表。

(官方)驱动表定义：

(1)满足查询条件的记录行数少的表为[驱动表]；

(2)未指定查询条件时，行数少的表为驱动表。

这里的指定查询条件可以知道，指的就是on后面的条件，那为什么添加了b的筛选条件后驱动表就变更了，这时候我们并不直观的看出where的筛选是否也算进查询条件里面，我们需要点更加直观的数据。a表再添加一条筛选，并且使用索引，可以看到，驱动表再次变更为a表，并且create_time上的索引生效，这也就说明了，mysql在做连接的时候就已经对数据进行了筛选

2.连接查询驱动表的选择

MySQL 表关联的算法是 Nest Loop Join，是通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果：

• EXPLAIN 结果中，第一行出现的表就是驱动表
• 对驱动表可以直接排序，对非驱动表(的字段排序)需要对循环查询的合并结果(临时表)进行排序(Important!)，即using temporary;
• [驱动表] 的定义为：1)满足查询条件的记录行数少的表为[驱动表]；2)未指定查询条件时，行数少的表为驱动表。
• 优化的目标是尽可能减少JOIN中Nested Loop的循环次数，以此保证：永远用小结果集驱动大结果集(Important!)！：A JOIN B，A为驱动，A中每一行和B进行循环JOIN，看是否满足条件，所以当A为小结果集时，越快。
• NestedLoopJoin实际上就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。如果还有第三个参与Join，则再通过前两个表的Join结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此往复。

上面总结了驱动表会根据实际情况改变，下面总结一下mysql选择驱动表的标准：

1.如果是外连接，毫无疑问，左连接左表为驱动表，右连接右表为驱动表，因为更换mysql驱动表更换的前提就是不能影响最后筛选出来的数据量。

2.所以外连接不会存在说更换驱动表的说法，真正会更换驱动表的连接就是内连接（join / inner join）。当外连接的被驱动表添加了筛选条件后，外连接就相当于内连接，只会返回符合条件的结果。

3.改变驱动表就意味着改变连接顺序，只有在不会改变最终输出结果的前提下才可以对驱动表做优化选择。在外连接情况下，很多时候改变驱动表会对输出结果有影响。mqsql会选择筛选数据预估结果集较少的表作为驱动表（每行查询字节数 * 预估的行数 = 预估结果集），

通过where预估结果行数，遵循以下规则：

• 如果where里没有相应表的筛选条件，无论on里是否有相关条件，默认为全表
• 如果where里有筛选条件，但是不能使用索引来筛选，那么默认为全表
• 如果where里有筛选条件，而且可以使用索引，那么会根据索引来预估返回的记录行数

3.优化连接查询

1.小结果集驱动大结果集

优化的目标是尽可能减少JOIN中Nested Loop的循环次数，尽量做到用小结果集驱动大结果集。对比这两个结果查询，你会使用哪种呢？但是看到这里的话又会有一个疑问了，如果第一种情况不是用id关联，或者第二种情况下a表的course_id没有索引，那么也不会有实际上这么快。所以第二个优化就是索引。

2.被驱动表的连接字段要添加索引，查询字段尽量使用索引，order by的字段尽量有索引

索引是以牺牲空间来加快查询时间，这种牺牲是可以接受的。

3.如果不清楚实际情况驱动表的选择情况，left join与right join尽量替换为join

反正基本上用的都是join

4.减少出现Using temporary（使用临时表）

一般情况下使用临时表的情况就是使用了排序，但是在驱动表上的字段进行排序是不需要用到临时表的，所以要清楚哪个表是临时表。如果一个千万级别的大表使用了临时表，那后果可能会是占用过多磁盘空间。

5.当出现下面的情况时，可能就需要考虑对sql进行优化了

• 出现了Using temporary；
• rows过多，或者几乎是全表的记录数；
• key 是 (NULL)；
• possible_keys 出现过多（待选）索引。