Join 的实现原理及优化思路前面我们已经了解了MySQL Query Optimizer 的工作原理，学习了Query 优化的基本原则和思 路，理解了索引选择的技巧，这一节我们将围绕Query 语句中使用非常频繁，且随时可能存在性能隐患 的Join 语句，继续我们的Query 优化之旅。 Join 的实现原理 在寻找Join 语句的优化思路之前，我们首先要理解在MySQL 中是如何来实现Join 的，只要理解 了实现原理之后，优化就比较简单了。下面我们先分析一下MySQL 中Join 的实现原理。 在MySQL 中，只有一种Join 算法，就是大名鼎鼎的Nested Loop Join，他没有其他很多数据库 所提供的Hash Join，也没有Sort Merge Join。顾名思义，Nested Loop Join 实际上就是通过驱动表 的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数 据，然后合并结果。如果还有第三个参与Join，则再通过前两个表的Join 结果集作为循环基础数据， 再一次通过循环查询条件到第三个表中查询数据，如此往复。 下面我们将通过一个三表Join 语句示例来说明MySQL 的Nested Loop Join 实现方式。 注意：由于要展示Explain 中的一个在MySQL 5.1.18 才开始出现的输出信息（在之前版本中只是 没有输出信息，实际执行过程并没有变化），所以下面的示例环境是MySQL5.1.26。 Query 如下：
select m.subject msg_subject, c.content msg_content 为了便于示例，我们通过如下操作为group_message 表增加了一个group_id 的索引： create index idx_group_message_gid_uid on group_message(group_id); 然后看看我们的Query 的执行计划：
sky@localhost : example 11:17:04> explain select m.subject msg_subject, c.content 我们可以看出，MySQL Query Optimizer 选择了user_group 作为驱动表，首先利用我们传入的条 件user_id 通过该表上面的索引user_group_uid_ind 来进行const 条件的索引ref 查找，然后以 user_group 表中过滤出来的结果集的group_id 字段作为查询条件，对group_message 循环查询，然 后再通过user_group 和group_message 两个表的结果集中的group_message 的id 作为条件与 group_message_content 的group_msg_id 比较进行循环查询，才得到最终的结果。 这个过程可以通过如下表达式来表示：
for each record g_rec in table user_group that g_rec.user_id=1{ 下图可以更清晰的标识出实际的执行情况：
假设我们去掉group_message_content 表上面的group_msg_id 字段的索引，然后再看看执行计划会变成怎样：
sky@localhost : example 11:25:36> drop index idx_group_message_content_msg_id on 我们看到不仅仅user_group 表的访问从ref 变成了ALL，此外，在最后一行的Extra 信息从没有任何内容变成为Using where; Using join buffer，也就是说，对于从ref 变成ALL 很容易理解，没有可以使用的索引的索引了嘛，当然得进行全表扫描了，Using where 也是因为变成全表扫描之后，我们需要取得的content 字段只能通过对表中的数据进行where 过滤才能取得，但是后面出现的Using join buffer 是一个啥呢？ 实际上，这里的Join 正是利用到了我们在之前“MySQL Server 性能优化”一章中所提到的一个Cache 参数相关的内容，也就是我们通过join_buffer_size 参数所设置的Join Buffer。实际上，Join Buffer 只有当我们的Join 类型为ALL（如示例中），index，rang 或者是index_merge 的时候才能够使用，所以，在我们去掉group_message_content 表的group_msg_id 字段的索引之前，由于Join 是ref 类型的，所以我们的执行计划中并没有看到有使用Join Buffer。 当我们使用了Join Buffer 之后，我们可以通过下面的这个表达式描述出示例中我们的Join 完成过程：
for each record g_rec in table user_group{ 当然，如果通过类似于上面的图片来展现或许大家会觉得更容易理解一些，如下：
通过上面的示例，我想大家应该对MySQL 中Nested Join 的实现原理有了一个了解了，也应该清楚MySQL 使用Join Buffer 的方法了。当然，这里并没有涉及到外连接的内容，实际对于外连接来说，可能存在的区别主要是连接顺序以及组合空值记录方面。 Join 语句的优化 在明白了MySQL 中Join 的实现原理之后，我们就比较清楚的知道该如何去优化一个一个Join 语句了。
如何减少Nested Loop 的循环总次数？最有效的办法只有一个，那就是让驱动表的结果集尽可能的小，这也正是在本章第二节中的优化基本原则之一“永远用小结果集驱动大的结果集”。为什么？因为驱动结果集越大，意味着需要循环的次数越多，也就是说在被驱动结果集上面所需要执行的查询检索次数会越多。比如，当两个表（表A 和表B） Join 的时候，如果表A 通过WHERE 条件过滤后有10 条记录，而表B 有20 条记录。如果我们选择表A 作为驱动表，也就是被驱动表的结果集为20，那么我们通过Join 条件对被驱动表（表B）的比较过滤就会有10 次。反之，如果我们选择表B 作为驱动表，则需要有20 次对表A 的比较过滤。 当然，此优化的前提条件是通过Join 条件对各个表的每次访问的资源消耗差别不是太大。如果访问存在较大的差别的时候（一般都是因为索引的区别），我们就不能简单的通过结果集的大小来判断需要 Join 语句的驱动顺序，而是要通过比较循环次数和每次循环所需要的消耗的乘积的大小来得到如何驱动更优化。 2. 优先优化Nested Loop 的内层循环； 不仅仅是在数据库的Join 中应该做的，实际上在我们优化程序语言的时候也有类似的优化原则。内层循环是循环中执行次数最多的，每次循环节约很小的资源，在整个循环中就能节约很大的资源。 3. 保证Join 语句中被驱动表上Join 条件字段已经被索引； 保证被驱动表上Join 条件字段已经被索引的目的，正是针对上面两点的考虑，只有让被驱动表的Join 条件字段被索引了，才能保证循环中每次查询都能够消耗较少的资源，这也正是优化内层循环的实际优化方法。 4. 当无法保证被驱动表的Join 条件字段被索引且内存资源充足的前提下，不要太吝惜Join Buffer 的设置； 当在某些特殊的环境中，我们的Join 必须是All，Index，range 或者是index_merge 类型的时候，Join Buffer 就会派上用场了。在这种情况下，Join Buffer 的大小将对整个Join 语句的消耗起到非常关键的作用。 8.6 ORDER BY，GROUP BY 和DI STI NCT 优化
除了常规的Join 语句之外，还有一类Query 语句也是使用比较频繁的，那就是ORDER BY，GROUP BY 以及DISTINCT 这三类查询。考虑到这三类查询都涉及到数据的排序等操作，所以我将他们放在了一起，下面就针对这三类Query 语句做基本的分析。 在MySQL 中，ORDER BY 的实现有如下两种类型：
下面我们就针对这两种实现方式做一个简单的分析。首先分析一下第一种不用排序的实现方式。同样还是通过示例来说话吧：
sky@localhost : example 09:48:41> EXPLAIN 看看上面的这个Query 语句，明明有ORDER BY user_id，为什么在执行计划中却没有排序操作呢？其实这里正是因为MySQL Query Optimizer 选择了一个有序的索引来进行访问表中的数据（idx_group_message_gid_uid），这样，我们通过group_id 的条件得到的数据已经是按照group_id和user_id 进行排序的了。而虽然我们的排序条件仅仅只有一个user_id，但是我们的WHERE 条件决定了返回数据的group_id 全部一样，也就是说不管有没有根据group_id 来进行排序，返回的结果集都是完全一样的。我们可以通过如下的图示来描述整个执行过程：
图中的Table A 和Table B 分别为上面Query 中的group_message 和gruop_message_content这两个表。 这种利用索引实现数据排序的方法是MySQL 中实现结果集排序的最佳做法，可以完全避免因为排序计算所带来的资源消耗。所以，在我们优化Query 语句中的ORDER BY 的时候，尽可能利用已有的索引来避免实际的排序计算，可以很大幅度的提升ORDER BY 操作的性能。在有些Query 的优化过程中，即使为了避免实际的排序操作而调整索引字段的顺序，甚至是增加索引字段也是值得的。当然，在调整索引之前，同时还需要评估调整该索引对其他 Query 所带来的影响，平衡整体得失。 如果没有索引利用的时候，MySQL 又如何来实现排序呢？这时候MySQL 无法避免需要通过相关的排序算法来将存储引擎返回的数据进行排序运算了。下面我们再针对这种实现方式进行相应的分析。在MySQL 第二种排序实现方式中，必须进行相应的排序算法来实现数据的排序。MySQL 目前可以通过两种算法来实现数据的排序操作。
上面第一种排序算法是MySQL 一直以来就有的排序算法，而第二种则是从MySQL4.1 版本才开始增加的改进版排序算法。第二种算法与第一种相比较，主要优势就是减少了数据的二次访问。在排序之后不需要再一次回到表中取数据，节省了IO 操作。当然，第二种算法会消耗更多的内存，正是一种典型的通过内存空间换取时间的优化方式。下面我们同样通过一个实例来看看当MySQL 不得不使用排序算法的时候的执行计划，仅仅只是更改一下排序字段：
sky@localhost : example 10:09:06> explain 大概一看，好像整个执行计划并没有什么区别啊？但是细心的读者朋友可能已经发现，在group_message 表的Extra 信息中，多了一个“Using filesort”的信息，实际上这就是MySQL QueryOptimizer 在告诉我们，他需要进行排序操作才能按照客户端的要求返回有序的数据。执行图示如下：
这里我们看到了，MySQL 在取得第一个表的数据之后，先根据排序条件将数据进行了一次filesort，也就是排序操作。然后再利用排序后的结果集作为驱动结果集来通过 Nested Loop Join 访问第二个表。当然，大家不要误解，这个filesort 并不是说通过磁盘文件进行排序，仅仅只是告诉我们进行了一个排序操作。 上面，我们看到了排序结果集来源仅仅只是单个表的比较简单的 filesort 操作。而在我们实际应用中，很多时候我们的业务要求可能并不是这样，可能需要排序的字段同时存在于两个表中，或者MySQL 在经过一次Join 之后才进行排序操作。这样的排序在MySQL 中并不能简单的里利用Sort Buffer 进行排序，而是必须先通过一个临时表将之前Join 的结果集存放入临时表之后在将临时表的数据取到Sort Buffer 中进行操作。下面我们通过再次更改排序要求来示例这样的执行计划，当我们选择通过group_message_content 表上面的content 字段来进行排序之后：
sky@localhost : example 10:22:42> explain 这时候的执行计划中出现了“Using temporary”，正是因为我们的排序操作需要在两个表Join 之后才能进行，下图展示了这个Query 的执行过程：
首先是Table A 和Table B 进行Join，然后结果集进入临时表，再进行filesort，最后得到有序的结果集数据返回给客户端。 上面我们通过两个不同的示例展示了当MySQL 无法避免要使用相应的排序算法进行排序操作的时候的实现原理。虽然在排序过程中所使用的排序算法有两种，但是两种排序的内部实现机制大体上差不多。 当我们无法避免排序操作的时候，我们又该如何来优化呢？很显然，我们应该尽可能让MySQL 选择使用第二种算法来进行排序。这样可以减少大量的随机IO 操作，很大幅度的提高排序工作的效率。 1. 加大max_length_for_sort_data 参数的设置；
在MySQL 中，决定使用第一种老式的排序算法还是新的改进算法的依据是通过参数max_length_for_sort_data 来决定的。当我们所有返回字段的最大长度小于这个参数值的时候，MySQL 就会选择改进后的排序算法，反之，则选择老式的算法。所以，如果我们有充足的内存让MySQL 存放需要返回的非排序字段的时候，可以加大这个参数的值来让MySQL 选择使用改进版的排 2. 去掉不必要的返回字段； 当我们的内存并不是很充裕的时候，我们不能简单的通过强行加大上面的参数来强迫MySQL 去使用改进版的排序算法，因为如果那样可能会造成MySQL 不得不将数据分成很多段然后进行排使用序，这样的结果可能会得不偿失。在这种情况下，我们就需要去掉不必要的返回字段，让我们的返回结果长度适应 max_length_for_sort_data 参数的限制。 3. 增大sort_buffer_size 参数设置； 增大sort_buffer_size 并不是为了让MySQL 可以选择改进版的排序算法，而是为了让MySQL可以尽量减少在排序过程中对需要排序的数据进行分段，因为这样会造成MySQL 不得不使用临时表来进行交换排序。 GROUP BY 的实现与优化 由于GROUP BY 实际上也同样需要进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY 的实现过程中，与ORDER BY 一样也可以利用到索引。 在MySQL 中，GROUP BY 的实现同样有多种（三种）方式，其中有两种方式会利用现有的索引信息来完成GROUP BY，另外一种为完全无法使用索引的场景下使用。下面我们分别针对这三种实现方式做一个分析。 1. 使用松散（Loose）索引扫描实现GROUP BY 何谓松散索引扫描实现GROUP BY 呢？实际上就是当MySQL 完全利用索引扫描来实现GROUP BY 的时候，并不需要扫描所有满足条件的索引键即可完成操作得出结果。 下面我们通过一个示例来描述松散索引扫描实现GROUP BY，在示例之前我们需要首先调整一下group_message 表的索引，将gmt_create 字段添加到group_id 和user_id 字段的索引中：
sky@localhost : example 08:49:45> create index idx_gid_uid_gc 我们看到在执行计划的Extra 信息中有信息显示“Using index for group-by”，实际上这就是告诉我们，MySQL Query Optimizer 通过使用松散索引扫描来实现了我们所需要的GROUP BY 操作。 下面这张图片描绘了扫描过程的大概实现：
要利用到松散索引扫描实现GROUP BY，需要至少满足以下几个条件：
为什么松散索引扫描的效率会很高？ 因为在没有WHERE 子句，也就是必须经过全索引扫描的时候， 松散索引扫描需要读取的键值数量与分组的组数量一样多，也就是说比实际存在的键值数目要少很多。而在WHERE 子句包含范围判断式或者等值表达式的时候， 松散索引扫描查找满足范围条件的每个组的第1 个关键字，并且再次读取尽可能最少数量的关键字。 2. 使用紧凑（Tight）索引扫描实现GROUP BY 紧凑索引扫描实现GROUP BY 和松散索引扫描的区别主要在于他需要在扫描索引的时候，读取所有满足条件的索引键，然后再根据读取恶的数据来完成GROUP BY 操作得到相应结果。
sky@localhost : example 08:55:14> EXPLAIN 这时候的执行计划的Extra 信息中已经没有“Using index for group-by”了，但并不是说MySQL的GROUP BY 操作并不是通过索引完成的，只不过是需要访问WHERE 条件所限定的所有索引键信息之后才能得出结果。这就是通过紧凑索引扫描来实现GROUP BY 的执行计划输出信息。 下面这张图片展示了大概的整个执行过程：
在MySQL 中，MySQL Query Optimizer 首先会选择尝试通过松散索引扫描来实现GROUP BY 操作，当发现某些情况无法满足松散索引扫描实现GROUP BY 的要求之后，才会尝试通过紧凑索引扫描来实现。 当GROUP BY 条件字段并不连续或者不是索引前缀部分的时候，MySQL Query Optimizer 无法使用松散索引扫描，设置无法直接通过索引完成GROUP BY 操作，因为缺失的索引键信息无法得到。但是，如果Query 语句中存在一个常量值来引用缺失的索引键，则可以使用紧凑索引扫描完成GROUP BY 操作，因为常量填充了搜索关键字中的“差距”，可以形成完整的索引前缀。这些索引前缀可以用于索引查找。而如果需要排序GROUP BY 结果，并且能够形成索引前缀的搜索关键字，MySQL 还可以避免额外的排序操作，因为使用有顺序的索引的前缀进行搜索已经按顺序检索到了所有关键字。 3. 使用临时表实现GROUP BY MySQL 在进行GROUP BY 操作的时候要想利用所有，必须满足GROUP BY 的字段必须同时存放于同一个索引中，且该索引是一个有序索引（如Hash 索引就不能满足要求）。而且，并不只是如此，是否能够利用索引来实现GROUP BY 还与使用的聚合函数也有关系。 前面两种GROUP BY 的实现方式都是在有可以利用的索引的时候使用的，当MySQL Query Optimizer 无法找到合适的索引可以利用的时候，就不得不先读取需要的数据，然后通过临时表来完成GROUP BY 操作。
sky@localhost : example 09:02:40> EXPLAIN 这次的执行计划非常明显的告诉我们MySQL 通过索引找到了我们需要的数据，然后创建了临时表，又进行了排序操作，才得到我们需要的GROUP BY 结果。整个执行过程大概如下图所展示：
当MySQL Query Optimizer 发现仅仅通过索引扫描并不能直接得到GROUP BY 的结果之后，他就不得不选择通过使用临时表然后再排序的方式来实现GROUP BY 了。 在这样示例中即是这样的情况。group_id 并不是一个常量条件，而是一个范围，而且GROUP BY字段为user_id。所以MySQL 无法根据索引的顺序来帮助GROUP BY 的实现，只能先通过索引范围扫描得到需要的数据，然后将数据存入临时表，然后再进行排序和分组操作来完成GROUP BY。 对于上面三种MySQL 处理GROUP BY 的方式，我们可以针对性的得出如下两种优化思路：
至于如何利用好这两种思路，还需要大家在自己的实际应用场景中不断的尝试并测试效果，最终才能得到较佳的方案。此外，在优化GROUP BY 的时候还有一个小技巧可以让我们在有些无法利用到索引的情况下避免filesort 操作，也就是在整个语句最后添加一个以null 排序（ORDER BY null）的子句，大家可以尝试一下试试看会有什么效果。 DISTINCT 的实现与优化
DISTINCT 实际上和GROUP BY 的操作非常相似，只不过是在GROUP BY 之后的每组中只取出一条记录而已。所以，DISTINCT 的实现和GROUP BY 的实现也基本差不多，没有太大的区别。同样可以通过松散索引扫描或者是紧凑索引扫描来实现，当然，在无法仅仅使用索引即能完成DISTINCT 的时候，MySQL只能通过临时表来完成。但是，和GROUP BY 有一点差别的是，DISTINCT 并不需要进行排序。也就是说，在仅仅只是DISTINCT 操作的Query 如果无法仅仅利用索引完成操作的时候，MySQL 会利用临时表 下面我们就通过几个简单的Query 示例来展示一下DISTINCT 的实现。 1. 首先看看通过松散索引扫描完成DISTINCT 的操作：
sky@localhost : example 11:03:41> EXPLAIN SELECT DISTINCT group_id
我们可以很清晰的看到，执行计划中的Extra 信息为“Using index for group-by”，这代表什么意思？为什么我没有进行GROUP BY 操作的时候，执行计划中会告诉我这里通过索引进行了GROUP BY 呢？其实这就是于DISTINCT 的实现原理相关的，在实现DISTINCT 的过程中，同样也是需要分组的，然后再从每组数据中取出一条返回给客户端。而这里的Extra 信息就告诉我们，MySQL 利用松散索引扫描就完成了整个操作。当然，如果MySQL Query Optimizer 要是能够做的再人性化一点将这里 2. 我们再来看看通过紧凑索引扫描的示例：
sky@localhost : example 11:03:53> EXPLAIN SELECT DISTINCT user_id 这里的显示和通过紧凑索引扫描实现GROUP BY 也完全一样。实际上，这个Query 的实现过程中，MySQL 会让存储引擎扫描group_id = 2 的所有索引键，得出所有的user_id，然后利用索引的已排序特性，每更换一个user_id 的索引键值的时候保留一条信息，即可在扫描完所有gruop_id = 2 的索引键的时候完成整个DISTINCT 操作。 3. 下面我们在看看无法单独使用索引即可完成DISTINCT 的时候会是怎样：
sky@localhost : example 11:04:40> EXPLAIN SELECT DISTINCT user_id 当MySQL 无法仅仅依赖索引即可完成DISTINCT 操作的时候，就不得不使用临时表来进行相应的操作了。但是我们可以看到，在MySQL 利用临时表来完成DISTINCT 的时候，和处理GROUP BY 有一点区别，就是少了filesort。实际上，在MySQL 的分组算法中，并不一定非要排序才能完成分组操作的，这一点在上面的GROUP BY 优化小技巧中我已经提到过了。实际上这里MySQL 正是在没有排序的情况下实现分组最后完成DISTINCT 操作的，所以少了filesort 这个排序操作。 4. 最后再和GROUP BY 结合试试看：
sky@localhost : example 11:05:06> EXPLAIN SELECT DISTINCT max(user_id) 最后我们再看一下这个和GROUP BY 一起使用带有聚合函数的示例，和上面第三个示例相比，可以看到已经多了filesort 排序操作了，因为我们使用了MAX 函数的缘故。 对于DISTINCT 的优化，和GROUP BY 基本上一致的思路，关键在于利用好索引，在无法利用索引的时候，确保尽量不要在大结果集上面进行DISTINCT 操作，磁盘上面的IO 操作和内存中的IO 操作性能完全不是一个数量级的差距。 8.7 小结本章重点介绍了MySQL Query 语句相关的性能调优的部分思路和方法，也列举了部分的示例，希望能够帮助读者朋友在实际工作中开阔一点点思路。虽然本章涉及到的内容包含了最初的索引设计，到编写高效Query 语句的一些原则，以及最后对语句的调试，但Query 语句的调优远不只这些内容。很多的调优技巧，只有到在实际的调优经验中才会真正体会，真正把握其精髓。所以，希望各位读者朋友能多做实验，以理论为基础，以事实为依据，只有这样，才能不断提升自己对Query 调优的深入认识。 (责任编辑：IT) |