假设我们去掉 group_message_content 表上面的 group_msg_id 字段的索引，然后再看看执行计划会变成怎样：
sky@localhost : example 11:25:36> drop index idx_group_message_content_msg_id on group_message_content;
Query OK, 96 rows affected (0.11 sec)
sky@localhost : example 10:21:06> explain
-> select m.subject msg_subject, c.content msg_content
-> from user_group g,group_message m,group_message_content c
-> where g.user_id = 1
-> and m.group_id = g.group_id
-> and c.group_msg_id = m.id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: g
type: ref
possible_keys: idx_user_group_uid
key: idx_user_group_uid
key_len: 4
ref: const
rows: 2
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: m
type: ref
possible_keys: PRIMARY,idx_group_message_gid_uid
key: idx_group_message_gid_uid
key_len: 4
ref: example.g.group_id
rows: 3
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: c
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 96
Extra: Using where; Using join buffer
我们看到不仅仅 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{
for each record m_rec in group_message that m_rec.group_id=g_rec.group_id{
put (g_rec, m_rec) into the buffer
if (buffer is full)
flush_buffer();
}
}
flush_buffer(){
for each record c_rec in group_message_content that
c_rec.group_msg_id = c_rec.id{
for each record in the buffer
pass (g_rec.user_id, m_rec.subject, c_rec.content) row combination to output;
}
empty the buffer;
}
当然，如果通过类似于上面的图片来展现或许大家会觉得更容易理解一些，如下：
通过上面的示例，我想大家应该对 MySQL 中 Nested Join 的实现原理有了一个了解了，也应该清楚 MySQL 使用 Join Buffer 的方法了。当然，这里并没有涉及到 外连接的内容，实际对于外连接来说，可能存在的区别主要是连接顺序以及组合空值记录方面。
Join 语句的优化
在明白了 MySQL 中 Join 的实现原理之后，我们就比较清楚的知道该如何去优化一个一个 Join 语句了。
1. 尽可能减少 Join 语句中的 Nested Loop 的循环总次数；
如何减少 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 和 DISTINCT 优化
除了常规的 Join 语句之外，还有一类 Query 语句也是使用比较频繁的，那就是 ORDER BY，GROUP BY 以及 DISTINCT 这三类查询。考虑到这三类查询都涉及到数据的排序等操作，所以我将他们放在了一起，下面就针对这三类 Query 语句做基本的分析。
ORDER BY 的实现与优化
在 MySQL 中，ORDER BY 的实现有如下两种类型：
◆ 一种是通过有序索引而直接取得有序的数据，这样不用进行任何排序操作即可得到满足客户端要求的有序数据返回给客户端；
◆ 另外一种则需要通过 MySQL 的排序算法将存储引擎中返回的数据进行排序然后再将排序后的数据返回给客户端。
下面我们就针对这两种实现方式做一个简单的分析。首先分析一下第一种不用排序的实现方式。同样还是通过示例来说话吧：
sky@localhost : example 09:48:41> EXPLAIN
-> SELECT m.id,m.subject,c.content
-> FROM group_message m,group_message_content c
-> WHERE m.group_id = 1 AND m.id = c.group_msg_id
-> ORDER BY m.user_id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: m
type: ref
possible_keys: PRIMARY,idx_group_message_gid_uid
key: idx_group_message_gid_uid
key_len: 4
ref: const
rows: 4
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: c
type: ref
possible_keys: group_message_content_msg_id
key: group_message_content_msg_id
key_len: 4
ref: example.m.id
rows: 11
Extra:
看看上面的这个 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 来进行排序，返回的结果集都是完全一样的。我们可以通过如下的图示来描述整个执行过程：