MySQL JOIN连接查询知识点

2019独角兽企业重金招聘Python工程师标准>>>

MySQL JOIN连接查询知识点

MySQL支持的join操作：JOIN、STRAIGHT_JOIN、LEFT JOIN、RIGHT JOIN。不支持FULL OUTER JOIN。

在MySQL中，JOIN, CROSS JOIN, 和INNER JOIN相互等价。但是在正常的SQL标准中，他们是不等价的。INNER JOIN通常和ON字句一起使用，CROSS JOIN以其他方式使用。

MySQL使用大名鼎鼎的Nested-Loop Join算法来执行Join操作的。

在使用join查询时需要考虑的因素

• 当没有连接条件的时候，INNER JOIN和','在语义上是等价的：在指定的表之间生成笛卡尔积(也就是说，第一个表中的每行跟第二个表的每行作连接)。
• 在ON子句指定如何连接两个表，在where子句约束要返回的结果集。
• 通过STRAIGHT_JOIN可以强制指定两个表的连接顺序，即左边的表在右边的表之前加载。

LEFT JOIN和RIGHT JOIN优化

MySQL以如下方式执行 A LEFT JOIN B join_condition：

• 表B被设置为依赖于表A和表A依赖的所有表。
• 表A被设置为依赖于left join条件中使用的所有表(除了表B)。
• left join条件被用来决定如何从表B检索行。(也就是说，不会使用where子句中的条件)
• 执行所有的标准join优化，一个表总是在他所依赖的所有表被读取之后才会被读取。如果有表循环依赖，MySQL会提示错误。
• 执行所有的标准where优化。
• 若表A中的某行匹配where子句，但是表B中没有匹配ON子句的行，则表B会生成一个所有列都为NULL的行。
• 若使用left join查询表中不存在的行，例如：where子句包含条件 col_name IS NULL，col_name列被声明为NOT NULL，MySQL在匹配到LEFT JOIN条件的一行后会停止查询更多的行。

注意：使用left join的时候先使用on子句的条件检索行，最后再根据where子句条件过滤结果集。而不会在检索表B的时候使用where条件过滤。但是在使用join的时候，过滤条件放在on子句和放在where子句是一样的。

STRAIGHT_JOIN说明

STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.

这句话的意思就是：除了STRAIGHT_JOIN可以指定左表在右表之前被读取之外，STRAIGHT_JOIN和JOIN是类似的。当MySQL的join优化器载入表的顺序不是最优的时候，可以使用STRAIGHT_JOIN来指定。

联表查询的基础知识

• 由于MySQL索引是基于B-Tree的，所以组合索引有”字段顺序“概念。当查询条件中有cat2 in (1,2)，而组合索引是(cat1,cat2)，则该查询无法使用到这个组合索引。
• MySQL表关联的算法是Nest Loop Join
• Join，是通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。
• EXPLAIN 结果中，对于序号相同的记录第一行出现的表就是驱动表（Important!）
• 对驱动表可以直接排序，对非驱动表（的字段排序）需要对循环查询的合并结果（临时表）进行排序！（Important!）
• 当进行多表连接查询时，驱动表的定义为：
  1. 指定了联接条件时，满足查询条件的记录行数少的表为[驱动表]；
  2. 未指定联接条件时，行数少的表为[驱动表]（Important!）。

参考：

• Join Syntax
• LEFT JOIN and RIGHT JOIN Optimization
• 慢查询优化-联表查询注意谁是驱动表

转载于:https://my.oschina.net/u/553773/blog/731922