MySQL中JOIN查询详解

一般而言，如果要设计一个小型数据库（指代码量少），但又要适应海量数据及访问的性能需求，最有效的方法莫过于针对主要应用场景

一般而言，如果要设计一个小型数据库（指代码量少），但又要适应海量数据及访问的性能需求，最有效的方法莫过于针对主要应用场景选择一个或几个性能优异的核心算法作为引擎，然后努力将一些非主要应用场景作为该算法的特例或变种植入到引擎当中。
    MySQL、PostgreSQL 等就是这么做的。 在 MySQL 的 SELECT 查询当中，其核心算法就是 JOIN 查询算法。其他的查询语句都相应向 JOIN 靠拢：单表查询被当作 JOIN 的特例；子查询被尽量转换为 JOIN 查询……
    这里将从 MySQL 5.0 的源代码入手，简要分析 MySQL 处理 JOIN 查询的流程和思路。

   1. MySQL SELECT处理流程
    下图是一个 SELECT SQL 传到 MySQL 服务端以后经过的主要函数流程图。图上每个小矩形框内代表一个函数，箭头的起点为调用者，终点为被调函数。箭头指向一个大框，则表示调用者调用了一组函数，顺序基本是从上向下。
    注：这里所说的 SQL 都不包含 UNION 子句，因为 MySQL 用了单独的 UNION 引擎来处理对应  SQL，而对于一般开发而言我们也很少使用 UNION 查询。 
    在上面所有函数中，mysql_excute_command() 函数是 MySQL 处理各类 SQL 语句的统一入口。SQL 语句在经过简单的语法解析以后，送到这里，由该函数作进一步分析，并调用相应的handle接口作后续处理。对于 SELECT 相关的语句，主要调用 handle_select() 和 mysql_select() 两个函数。
    handle_select() 可以处理 SELECT 中含有 UNION 子句的情况。在不含 UNION 的简单结构中，也会直接调用 mysql_select() 函数。
    mysql_select() 函数就是不带 UNION 子句的 SELECT 语句的入口点函数。通常状况下，每次调用以后，它会依次调用 JOIN 类的 prepare() 、optimize() 、exec() 三个函数来完成 SELECT 语句的预处理、优化、执行和结果输出功能。
    JOIN::prepare() 是一个前处理函数。主要进行参数合法性检查、语法分析并生成更准确的计算机描述、打开记录表、子查询转换等操作。
    JOIN::optimize() 是整个 SELECT 流程的关键所在，它负责对前面生成的各种描述结构进行各种优化。优化过程基于大量的规则进行，这些规则我们后面再详细讲述。 JOIN::optimize() 调用的一系列函数我们不一一述及，也将各函数内的优化规则总结、概括到后面去一并讲解。
    JOIN::exec() 也会进行一些运行时优化，这些优化过程会导致实际执行过程与 EXPLAIN 中显示的不一致。但大多数情况下，JOIN::exec() 会遵照前面优化的过程执行，，因此 JOIN 的流程也基本在 optimize() 中确定。
    JOIN::exec() 与 JOIN 最相关的部分是调用 do_select() 函数执行取数据的操作。do_select() 会调用 sub_select() 函数，该调用采用递归的方法将两两相邻的表按照依赖关系进行归并，逐步得到最终的结果集。
    结果集返回的操作也在 JOIN::exec() 中执行，或返回到临时表，或输入到文件，或发送到 socket。这些不是我们关注的重点，因此也一笔带过。

   2. MySQL优化规则
    MySQL 优化器的工作是基于规则设计的，如果规则存在缺陷，相应部分的应用也会有一些性能损失。与一些先进的大型数据库不同，MySQL 的这些性能损失可能是永久的（对固定版本而言）。因为大型数据库在执行过程中会对各种优化结果的执行情况进行统计评估以便自动改进后续的执行优化状况，而 MySQL 目前没有这些功能。因此，了解 MySQL 的优化规则，对于较好地设计 SQL 语句，提高执行效率有很大的指导意义。
    下面列出 MySQL 5 在处理SELECT查询时设计的一些规则。

    规则1：如果操作只涉及常表，则去除 DISTINCT 子句；否则如果只有一个表，在以下情况下会将 DISTINCT 转为 GROUP BY 查询：

GROUP BY 可以通过索引实现（不用对索引排序），ORDER BY 只需对选择出的记录集排序（该情况下，优化器会对 GROUP BY 和 ORDER BY 进行额外的优化）。
没有使用 LIMIT，所以要作全表扫描。
    全表扫描发生的情况通常为以下两种：

查询中使用了 SQL_CALC_FOUND_ROWS。
使用的 ORDER BY 子句无法优化。
    当 SELECT 语句包含了 LIMIT 子句（这里和后文提及 LIMIT 子句的时候，默认是没有 SQL_CALC_FOUND_ROWS 子句存在的情况）时，优化器将不使用这一优化规则，因为该情况下优化器将创建临时表放置 LIMIT 所限制的记录数，然后返回。
    注：LIMIT 子句跟 DISTINCT、GROUP BY、ORDER BY等子句共存的状况比较复杂。此时使用 LIMIT 子句除了减少了发送记录过程中的耗时以外，通常不应期望有更多的速度提高。因为后面三个子句中的任一个都可能会使得不管是否存在 LIMIT 子句都要做同样多，甚至更多一点点的计算。

    这里顺便介绍常表的概念。 所谓常表，包括以下类型：

一个没有记录或只有一行记录的表。
一个表的表达式受 WHERE 子句限制，表达式形式为“column = constant”，并且该 column 是该表的 PRIMARY KEY 或 UNIQUE KEY（假设该 UNIQUE 列同时被定义为  NOT NULL）。
    规则2：优化器在以下情况会考虑创建临时表：

SELECT 语句中存在 DISTINCT 子句（基于准则1可以优化掉的 DISTINCT 已经优化掉了）。
对表链（table list）中第二及后面的表施加了 ORDER BY 或 GROUP BY 操作。
使用了不同的 ORDER BY 和 GROUP BY 顺序，或排序操作较为复杂。
用户希望我们缓冲结果。
使用了 LIMIT 子句。
    是否要创建临时表，会在所有表都读入之前确定。

    规则3：尽量将 OUTER JOIN 转换为 INNER JOIN，并尽可能地嵌套。相应地，ON 子句的条件表达式也会被移动到 WHERE 子句。
    如果嵌套循环JOIN的 WHERE 子句或 ON 子句中有一个条件表达式剔除了内表中某属性为 NULL 的所有值，则  OUTER JOIN 可以替换为  INNER JOIN 。

    例如，下面的查询中：
SELECT * FROM t1 LEFT JOIN t2 ON t2.a=t1.a WHERE t2.b SELECT * FROM t1 INNER JOIN t2 ON t2.a=t1.a WHERE t2.b SELECT * FROM t1, t2 ON t2.a=t1.a WHERE t2.b

    类似地，下面的查询：
SELECT * FROM t1 LEFT JOIN (t2, t3) ON t2.a=t1.a t3.b=t1.b WHERE t2.c SELECT * FROM t1, (t2, t3) WHERE t2.c

    一个转换可能会触发另一个
SELECT * FROM t1 LEFT JOIN t2 ON t2.a=t1.a
         LEFT JOIN t3 ON t3.b=t2.b
         WHERE t3 IS NOT NULL    将转换为：
SELECT * FROM t1 LEFT JOIN t2 ON t2.a=t1.a, t3
         WHERE t3 IS NOT NULL AND t3.b=t2.b    再转换为：
SELECT * FROM t1, t2, t3
         WHERE t3 IS NOT NULL AND t3.b=t2.b AND t2.a=t1.a

    规则4：尽量将多个等式转换连等式。

    规则5：ORDER BY 操作尽量施加在结果集而不是源集上。
    但在 JOIN 操作的 ON 子句中有等式或不等式（指不包括"!="在内的其他比较符号）且等式两边没有常数时，可能会先对源集进行排序，然后进行归并联接。

    规则6：如果某个索引可以获取所有 SELECT 语句需要的列，则优先考虑该索引。

    规则7：尽量将子查询转换为 JOIN。
    大多数情况下，子查询可能需要较多的临时表存储，并且查询速度较之 JOIN 要慢得多。

    规则8：在允许的情况下，对 JOIN 的各个表重排次序，提高执行的速度。
    数据量较小的表可能会被放在最前面先处理，数据量较大的表会稍后处理。但如果 ON 子句明确指定了依赖关系，根据依赖关系处理，顺序不可调整。