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 < 5 条件 t2.b < 5 剔除了 NULL 项，该查询首先被转换为:
SELECT * FROM t1 INNER JOIN t2 ON t2.a=t1.a WHERE t2.b < 5 然后转换为等价形式：
SELECT * FROM t1, t2 ON t2.a=t1.a WHERE t2.b < 5 AND t2.a=t1.a
类似地，下面的查询：
SELECT * FROM t1 LEFT JOIN (t2, t3) ON t2.a=t1.a t3.b=t1.b WHERE t2.c < 5 转化为：
SELECT * FROM t1, (t2, t3) WHERE t2.c < 5 AND t2.a=t1.a t3.b=t1.b
一个转换可能会触发另一个
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 子句明确指定了依赖关系，根据依赖关系处理，顺序不可调整。
3. 建议兼总结
研究MySQL源代码，本来是期望从理解 MySQL 的处理流程中找到一些 SQL 优化的新思路。但是，很遗憾，在阅读 MySQL 源代码的过程中发现 JOIN 算法其实是尽量简单化、核心化。在洋洋洒洒上万行的代码中，JOIN 相关的算法最核心的只不过若干行基于嵌套循环的排序归并算法，真正占了绝大多数的代码是在对各种条件进行优化、内外联结条件转换、各种子句执行顺序的重新排列等等繁琐的处理，这些处理的最终目的只有一个：让 SELECT SQL 能尽快按照 JOIN 的核心算法执行并输出结果。另外还有多处的代码只是为了某一种特定的 SELECT 语句的优化，有补丁代码的性质。
所以这样看来，研究 MySQL 源代码可以对一些问题获得比较确切的答案，但很难据此找到脱离于数据库理论之外的其他措施来大幅提高数据库查询的性能。MySQL JOIN 算法的思路和速度看起来都不错，即便是 Oracle 之类的大型数据库的 JOIN 算法在速度方面未必能有多少的提高。Oracle 在 CBO 下的 Hash JOIN 在两表大小相差很大的情况下会有较好的性能表现，但其使用面较窄，对于小型数据库而言，不是首要发展方向（MySQL 的 index JOIN 过程中也使用了类似 Hash JOIN 的算法，但未作重点发展）。
尽管如此，从代码分析里面还是可以获得一些小技巧，从而充分利用MySQL的核心算法优势，同时避开其缺点。
- 1. 多用 JOIN，少用 IN 和 UNION。
MySQL 在 JOIN 流程的优化上花了大量的精力，速度也确实很快。然而大多数子查询或 UNION，人容易理解，但计算机不容易理解，造成优化功能的先天缺陷，执行速度会慢不少。 - 2. 对于多表查询，如果可以确定表按照某一固定次序处理可以获得较好的效率，则建议加上 STRAIGHT_JOIN 子句，以减少优化器对表进行重排序优化的过程。
该子句一方面可以用于优化器无法给出最优排列的 SQL 语句；另一方面同样适用于优化器可以给出最优排列的 SQL 语句，因为 MySQL 算出最优排列也需要耗费较长的流程。
对于后一状况，可以根据 EXPLAIN 的提示选定表的顺序，并加上 STRAIGHT_JOIN 子句固定该顺序。该状况下的使用前提是几个表之间的数据量比例会一直保持在某一顺序，否则在各表数据此消彼长之后会适得其反。
对于经常调用的 SQL 语句，这一方法效果较好；同时操作的表越多，效果越好。 - 3. 用类型相同的列作比较。
虽然 MySQL 在目前的版本中对不同类型的列之间的比较提供了一定的支持，但是事实证明，这些不同类型之间的比较引起性能的不稳定，甚至有时会匪夷所思。而且强制类型转换会浪费大量的时间，且常常迫使优化器无法优化而遍历所有记录，这显然是我们不希望的。因此对于经常查询的列，一定要注意类型匹配。 - 4. 尽量将常数进行类型转换，以使得比较表达式两端的类型匹配。这一点不要指望优化器，它做得并不好。
- 5. 条件表达式中尽量不要对可以使用索引的列使用类型转换或其他函数，否则很可能导致优化器无法使用索引。
- 6. 字符串的比较会比整数比较慢很多，因此设计数据库时，如果某些字段可以用数字表示，则尽量不要用字符串。这个好像不用多解释。
参考文档：《MySQL Internals Manual》、《Pro MySQL》
