从一个MySQL left join优化的例子加深对查询计划的理解
今天遇到一个left join优化的问题，搞了一下午，中间查了不少资料，对MySQL的查询计划还有查询优化有了更进一步的了解，做一个简单的记录：
select c.* from hotel_info_original c
left join hotel_info_collection h
on c.hotel_type=h.hotel_type and c.hotel_id =h.hotel_id
where h.hotel_id is null
这个sql是用来查询出c表中有h表中无的记录，所以想到了用left join的特性（返回左边全部记录，右表不满足匹配条件的记录对应行返回null）来满足需求，不料这个查询非常慢。先来看查询计划： 
rows代表这个步骤相对上一步结果的每一行需要扫描的行数，可以看到这个sql需要扫描的行数为35773*8134，非常大的一个数字。本来c和h表的记录条数分别为40000+和10000+，这几乎是两个表做笛卡尔积的开销了（select * from c,h）。
于是我上网查了下MySQL实现join的原理，原来MySQL内部采用了一种叫做 nested loop join的算法。Nested Loop Join 实际上就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。如果还有第三个参与 Join，则再通过前两个表的 Join 结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此往复，基本上MySQL采用的是最容易理解的算法来实现join。所以驱动表的选择非常重要，驱动表的数据小可以显著降低扫描的行数。
那么为什么一般情况下join的效率要高于left join很多？很多人说不明白原因，只人云亦云，我今天下午感悟出来了一点。一般情况下参与联合查询的两张表都会一大一小，如果是join，在没有其他过滤条件的情况下MySQL会选择小表作为驱动表，但是left join一般用作大表去join小表，而left join本身的特性决定了MySQL会用大表去做驱动表，这样下来效率就差了不少，如果我把上面那个sql改成
select c.* from hotel_info_original c
join hotel_info_collection h
on c.hotel_type=h.hotel_type and c.hotel_id =h.hotel_id
查询计划如下： 
很明显，MySQL选择了小表作为驱动表，再配合(hotel_id,hotel_type)上的索引瞬间降低了好多个数量级。。。。。
另外，我今天还明白了一个关于left join 的通用法则，即：如果where条件中含有右表的非空条件（除开is null），则left join语句等同于join语句，可直接改写成join语句。
后记：
随着查看MySQL reference manual对这个问题进行了更进一步的了解。MySQL在执行join时会把join分为system/const/eq_ref/ref/range/index/ALl等好几类，连接的效率从前往后
依次递减，对于我的第一个sql，连接类型是index，所以几乎是全表扫描的效果。但是我很奇怪我在(hotel_id,hotel_type)两列上声明了unique key，根据官方文档连接类型应该是eq_ref才对，
这个问题一直困扰了我两天，在google和stackoverflow上都没有找到能够解释这个问题的文章，莫非我这个问题无解了？抱着解决这个问题的决心今天又翻看了一遍MySQL官方文档
关于优化查询的部分，看到了这样一句：这里的一个问题是MySQL能更高效地在声明具有相同类型和尺寸的列上使用索引。我感觉我找到了问题所在，于是我将original和 collection表的(hotel_type,hotel_id)的encoding和collation（决定字符比较的规则）全部改成统一的utf8_general_ci，然后再次运行第一条sql的查询计划，得到如下结果： 
连接类型已经由index优化到了ref，如果将hotel_type申明为not null可以优化到eq_ref，不过这里影响不大了，优化后这条sql能在0.01ms内运行完。
那么如何优化left join：
1、条件中尽量能够过滤一些行将驱动表变得小一点，用小表去驱动大表
2、右表的条件列一定要加上索引（主键、唯一索引、前缀索引等），最好能够使type达到range及以上（ref,eq_ref,const,system）
3、无视以上两点，一般不要用left join~~！
所谓连接查询，就是通过连接，使查询的数据从多个表中检索取得。在 SELECT 的 FROM 子句中写上所有有关的表名，就可以得到由几个表中的数据组合而成的查询结果。连接条件可在 FROM或 WHERE 子句中指定，WHERE 和 HAVING 子句可包含其搜索条件，以供进一步筛选连接之后的结果集。 目前可实现的连接有：自然连接(Natural Join)，内连接(Inner Join), 外连接(Outer Join), 交叉连接(Cross Join) etc.
谓词 JOIN USING 可按照指定的列实现表的等值连接。设有两个表t1, t2 具有相同的列 a, b, c, d, 如果不是对全部相同列做连接，而是是对列 a, b 做连接，可写成 t1 JOIN t2 USING(a,b)。
谓词 JOIN ON 可按照更一般性条件实现表的等值连接。eg: t1(a, b), t2(a, c), 可写成 t1 JOIN t2 ON t1.a = t2.a。
USING 后用于连接的列，也可用保留字 ON 指定， eg: ... USIGN(a)... 等价于 ... ON t1.a = t2.a..., 使用两者在结果集上体现的不同请见注 1。
示例表 t1, t2, 其数据如下：
mysql> SELECT * FROM t1;
+---+------+
| a | b |
+---+------+
| 1 | a |
| 3 | c |
| 5 | e |
| 7 | g |
+---+------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM t2;
+---+------+
| a | c |
+---+------+
| 2 | b |
| 4 | d |
| 6 | f |
| 7 | g |
+---+------+
4 rows in set (0.00 sec)
自然连接
mysql> SELECT * FROM t1, t2 WHERE t1.a = t2.a;
+---+------+---+------+
| a | b | a | c |
+---+------+---+------+
| 7 | g | 7 | g |
+---+------+---+------+
1 row in set (0.00 sec)
内连接
mysql> SELECT * FROM t1 INNER JOIN t2 USING (a);
+---+------+------+
| a | b | c |
+---+------+------+
| 7 | g | g |
+---+------+------+
1 row in set (0.00 sec)
它等价于：SELECT * FROM t1, t2 WHERE t1.a = t2.a; or SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a;。
外连接
外连接是连接的扩展。一般连接操作的结果表由符合连接条件的匹配元组连接起来的新元组构成，其余不符合连接条件的非匹配元组则被丢弃。外连接允许在结果表中保留非匹配元组，空缺部分填以NULL。其作用是在做连接操作时避免丢失信息。外连接有 3 类：
1 左外连接(Left Outer Join)。连接运算谓词为LEFT [OUTER] JOIN，其结果表中保留左关系的所有元组。eg:
mysql> SELECT * FROM t1 LEFT JOIN t2 USING(a);
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | a | NULL |
| 3 | c | NULL |
| 5 | e | NULL |
| 7 | g | g |
+---+------+------+
4 rows in set (0.00 sec)
等价于：SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a;。
2 右外连接(Right Outer Join)。连接运算谓词为RIGHT [OUTER] JOIN, 其结果表中保留右关系的所有元组。MySQL 对其的优化策略见注 2。
3 全外连接(Full Outer Join)。连接运算谓词为FULL [OUTER] JOIN, 其结果表中保留左右关系的所有元组。 MySQL 中， FULL JOIN... USING的结果集和INNER JOIN ... USING的结果集相同，且 MySQL 不支持 FULL OUTER JOIN 。eg:
mysql> SELECT * FROM t1 FULL JOIN t2 USING(a);
+---+------+------+
| a | b | c |
+---+------+------+
| 7 | g | g |
+---+------+------+
1 row in set (0.00 sec)
mysql> SELECT * FROM t1 FULL OUTER JOIN t2 USING(a);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'OUTER JOIN t2 USING(a)\' at line 1
交叉连接
交叉连接等同于做笛卡尔积。
mysql> SELECT * FROM t1 CROSS JOIN t2;
+---+------+---+------+
| a | b | a | c |
+---+------+---+------+
| 1 | a | 2 | b |
| 3 | c | 2 | b |
| 5 | e | 2 | b |
| 7 | g | 2 | b |
| 1 | a | 4 | d |
| 3 | c | 4 | d |
| 5 | e | 4 | d |
| 7 | g | 4 | d |
| 1 | a | 6 | f |
| 3 | c | 6 | f |
| 5 | e | 6 | f |
| 7 | g | 6 | f |
| 1 | a | 7 | g |
| 3 | c | 7 | g |
| 5 | e | 7 | g |
| 7 | g | 7 | g |
+---+------+---+------+
16 rows in set (0.00 sec)
它等价于：SELECT * FROM t1, t2; or SELECT * FROM t1 INNER JOIN t2;。
注：
-
USING 和 ON 显示的结果说明
为了使 MySQL 在解析 NATURAL JOIN 和 JOIN ... USING SQL 时采用 SQL 2003 的标准，从 MySQL 5.0.12 开始，这两种 SQL 的解析都有做调整, NATURAL JOIN 时， 查询结果中只会显示单独的唯一一列， 即 t1.a, t2.2 两列做自然连接后在结果中只有一列 a (= COELSCE(t1.a, t2.a)); 而 JOIN ... USING时，对 USING 中 指定使用做连接的列，查询结果中也只会显示单独的唯一一列， JOIN ... USING形式的变体如 LEFT JOIN ... USING, RIGHT JOIN ... USING 等解析优化也是采用同样的处理。除此之后，其他的连接方式还是遵照以前的规则。详细说明，可查看如下引用部分或MySQL 官方文档 JOIN Syntax 一节。
Join Processing Changes in MySQL 5.0.12
Beginning with MySQL 5.0.12, natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. The goal was to align the syntax and semantics of MySQL with respect to NATURAL JOIN and JOIN ... USING according to SQL:2003. However, these changes in join processing can result in different output columns for some joins. Also, some queries that appeared to work correctly in older versions must be rewritten to comply with the standard.
The single result column that replaces two common columns is defined using the coalesce operation. That is, for two t1.a and t2.a the resulting single join column a is defined as a = COALESCE(t1.a, t2.a), where:
COALESCE(x, y) = (CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END)
If the join operation is any other join, the result columns of the join consists of the concatenation of all columns of the joined tables. This is the same as previously.
mysql> SELECT * FROM t1 LEFT JOIN t2 USING (a); +---+------+------+ | a | b | c | +---+------+------+ | 1 | a | NULL | | 3 | c | NULL | | 5 | e | NULL | | 7 | g | g | +---+------+------+ 4 rows in set (0.01 sec) mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a; +---+------+------+------+ | a | b | a | c | +---+------+------+------+ | 1 | a | NULL | NULL | | 3 | c | NULL | NULL | | 5 | e | NULL | NULL | | 7 | g | 7 | g | +---+------+------+------+ 4 rows in set (0.01 sec) -
2 右外连接的优化策略
右外连接 SQL 在解析阶段都会转换为只包含左外连接的 SQL，一般遵循如下这样的转换方式：
T1, ...) RIGHT JOIN (T2,...) ON P(T1,...,T2,...) = (T2, ...) LEFT JOIN (T1,...) ON P(T1,...,T2,...)
有关其详细信息，请参考 MySQL 的官方文档 7.3.1.10. Outer Join Simplification 。
At the parser stage, queries with right outer joins operations are converted to equivalent queries containing only left join operations. In the general case, the conversion is performed according to the following rule:
T1, ...) RIGHT JOIN (T2,...) ON P(T1,...,T2,...) =
(T2, ...) LEFT JOIN (T1,...) ON P(T1,...,T2,...)
通过EXPLAIN EXTENDED, SHOW WARNINGS, 也能查看 MySQL 解析优化后的语句：
mysql> EXPLAIN EXTENDED SELECT * FROM t1 RIGHT JOIN t2 USING (a); +----+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+-------+ | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | | | 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | test.t2.a | 1 | 100.00 | | +----+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+-------+ 2 rows in set, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t2`.`a` AS `a`,`test`.`t2`.`c` AS `c`,`test`.`t1`.`b` AS `b` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`a` = `test`.`t2`.`a`)) where 1 | +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
浙公网安备 33010602011771号