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

在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 |
+--+-----------+---------+----------+----+-------------+-------------+-------+-------+----+--------+-----+