联合查询为什么小表要放在前面

在Mysql的查询优化里有一条经验,在联合查询时,SQL里要把小表放在大表的前面。小表是指数据量少的表。这个经验的原理是什么呢?这就要从Mysql的遍历方式开始说起。

遍历方式

Mysql的联合查询是按照嵌套查询的方式进行遍历。先从第一个表取出一条数据,在第二个表里搜索符合条件的数据。再从第二个表里取出一条数据,在第三个表里搜索符合条件的数据。一直递归到最后一个表,如过有符合条件的数据就放到结果集里。最后一张表没有符合条件的数据后,就再回到倒数第二张表,找到符合条件的下一条数据。直到第一张表的数据都遍历完为止。

这种遍历方式的成本是多少,我们可以简单估算一下。

遍历的成本

嵌套遍历的成本,即查找的次数公式是 (cost1cnt0) + (cost2cnt1) + … + (costN*cntN-1)。cost代表找到一条满足条件数据需要扫描的行数,cnt是需要找的行数,cnt0是常数1。

没走的索引的情况

没有用到索引的情况,就要全表扫描,所以cost就是表的行数。公式里表的行数我们是没发控制,能控制的是调整表的顺序,产生不同的cnt。这里就有几个规则:

  1. 第一个表需要全部扫描,把数据行少的表放前面也许更划算
  2. 将数据行多的表放后面,countN * cntN-1公式里的cntN-1的值更小的概率越大,即减少大表的总遍历次数

但是,在没有实际执行之前,是不知道每个cnt的值。所以,什么样的表顺序更优其实是不确定。例如,table1、table2分别有有100、1000条数据。从table1开始遍历如果有50条数据满足条件,那么成本就是100 + 50 * 1000 = 50100。从table2开始遍历如果有10条数据满足条件,那么成本就是1000 + 10 * 100 = 2000。这样看,table2放前面更好。

走索引的情况

走索引的情况联合查询的方式会有一些区别。先从第一个表取出一条数据,在第二个表的索引里搜索符合条件的数据。用索引搜索的时间复杂度是O(logn),count值就从数据行数变成了log(count),公式还是一样。

所以,按照经验是将小表放到前面会好一些,但也不是绝对的。大部分情况下查询优化器会帮我们去选择表的顺序,所以我们没必要刻意的去调整表的顺序。通过explain命令可以看到优化后的执行顺序。

explain select * from table1, table2 where table1.oid = table2.oid;
+--+-----------+---------+----------+----+-------------+-------------+-------+-------+----+--------+-----+
|id|select_type|table    |partitions|type|possible_keys|key          |key_len|ref    |rows|filtered|Extra|
+--+-----------+---------+----------+----+-------------+-------------+-------+-------+----+--------+-----+
|1 |SIMPLE     |table1   |null      |ALL |null         |null         |null   |null   |31  |100     |null |
|1 |SIMPLE     |table2   |null      |ALL |null         |null         |null   |null   |203 |100     |null |
+--+-----------+---------+----------+----+-------------+-------------+-------+-------+----+--------+-----+

如果优化器选错了顺序,我们可以通过straight_join关键字去忽略优化器的顺序优化。

select straight_join * from table1, table2 where table1.oid = table2.oid;
+--+-----------+---------+----------+----+-------------+-------------+-------+-------+----+--------+-----+
|id|select_type|table    |partitions|type|possible_keys|key          |key_len|ref    |rows|filtered|Extra|
+--+-----------+---------+----------+----+-------------+-------------+-------+-------+----+--------+-----+
|1 |SIMPLE     |table2   |null      |ALL |null         |null         |null   |null   |203 |100     |null |
|1 |SIMPLE     |table1   |null      |ALL |null         |null         |null   |null   |31  |100     |null |
+--+-----------+---------+----------+----+-------------+-------------+-------+-------+----+--------+-----+