来谈谈MySQL的临时表，到底是个什么东西，以及怎么样产生的

介绍临时表之前，我们首先来看这么一句语句：

CREATE TABLE `words` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `word` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

这是一个单词表，除了一个主键id之外，只有一个word字段，再来看下面这段sql：

select word from words order by rand() limit 3;

这段sql的语义其实就是按照随意的规则进行排序，然后取出前三个单词并返回，这段sql看起来很简单，但实际上要做的事还是比较繁琐的：

（1）创建一个临时表。这个临时表使用的是 memory 引擎，表里有两个字段，第一个字段是 double 类型，为了后面描述方便，记为字段 R，第二个字段是 varchar(64) 类型，记为字段 W。并且，这个表没有建索引。从 words 表中，按主键顺序取出所有的 word 值。

（2）对于每一个 word 值，调用 rand() 函数生成一个大于 0 小于 1 的随机小数，并把这个随机小数和 word 分别存入临时表的 R 和 W 字段中，到此，扫描行数是 10000。

（3）现在临时表有 10000 行数据了，接下来你要在这个没有索引的内存临时表(memory引擎的特性之后再说)上，按照字段 R 排序。

（4）初始化 sort_buffer。sort_buffer 中有两个字段，一个是 double 类型，另一个是整型。从内存临时表中一行一行地取出 R 值和位置信息（我后面会和你解释这里为什么是“位置信息”），分别存入 sort_buffer 中的两个字段里。这个过程要对内存临时表做全表扫描，此时扫描行数增加 10000，变成了 20000。

（5）在 sort_buffer 中根据 R 的值进行排序。注意，这个过程没有涉及到表操作，所以不会增加扫描行数。排序完成后，取出前三个结果的位置信息，依次到内存临时表中取出 word 值（这里还是有涉及到回表的操作，但是是回的内存表，所以没有磁盘io的消耗），返回给客户端。这个过程中，访问了表的三行数据，总扫描行数变成了 20003。

　　当然除了内存临时表之外还有磁盘临时表，磁盘临时表是在内存不足时才会产生，所以尽可能的保证内存临时表的空间要足够大，否则磁盘的操作要比内存的操作要耗时的多！！

　　OK，我们再来看一个例子，首先定义一下表结构：

create table t1(id int primary key, a int, b int, index(a));

现在我们有这么一个sql：

explain select b,count(*) as c from t1 group by b

sql的语义很简单，对b字段进行分组，并且通过聚合函数统计每个组的个数并返回，但是需要注意的是b字段没有建立索引，于是返回的结果里：

　　可以看到有使用到临时表，但是后面居然还跟了个using filesort，这是为啥？？我们可以先来看一下这句sql的执行流程：

（1）创建内存临时表，表里有两个字段 b 和 c，主键是 m；扫描表 t1，依次取出b的值；

（2）如果临时表中没有对应的b的值的行，就插入一个记录 (x,1);如果表中有对应的b的值的行，就将这一行的 c 值加 1；遍历完成后，再根据字段 b 做排序，得到结果集返回给客户端；

　　注意到了没，最后有一步是对b做排序，这一步是在内存中的sort_buffer区域完成的，这一步的诞生导致了using filesort，但是我们的sql寓意里并不需要排序啊，那么可以使用order by null使得group by 之后不需要做任何排序，执行完成后你会发现using filesort消失了。。。但是using temporary还是在的，那么我们可以尝试在b字段加索引，继续执行上面的sql，如果b有索引的话，那么b索引树的肯定是这样子的：

　　1-2-2-2-5-5-5-5
　　是一个有序的链表，mysql在扫描这个索引时，直接轧过去，到2的时候自然知道1的个数只有一个，到5的时候自然知道2的个数只有3个，统计的复杂度就是n，但是如果m没有索引，那么就是无序的，在统计时，扫到2的时候，得去一个中间表看是否存在，存在+1，不存在添加。。。到这里也就清晰了，因为b有了索引，所以这个统计操作，只需要遍历一遍索引即可：

可以看到临时表没有再使用了，因为有了索引后，b字段不再需要临时表进行辅助统计，结果perfect！

 

　　最后再提一提关于distinct和group by