1 长期表

sort buffer、内存长期表和join buffer，这三个数据结构都是用来寄存语句执行过程中的两头数据，以辅助SQL语句的执行的。其中，在排序的时候用到了sort buffer，在应用join语句的时候用到了join buffer。

而应用长期表的时候，Explain的Extra字段中具备Using temporary标记。union、group by、distinct等等查问都有可能应用到长期表。

2 union长期表优化

应用union的时候，就须要用到内存长期表来进行去重。

union语句的执行流程为：

  1. 创立一个内存长期表。
  2. 执行第一个子查问，失去值，并存入长期表中。
  3. 执行第二个子查问：顺次拿每一行数据和长期表中的每一行数据比拟，如果反复则不会插入，这样就实现了去重的性能
  4. 从长期表中按行取出数据，返回后果，并删除长期表。在最初这一步还能够对长期表进行其余操作，比方limit、ORDER BY。

如果应用union all，则不须要去重，也就不须要长期表了。在执行的时候，就顺次执行子查问，失去的后果间接作为后果集的一部分，发给客户端。因而，除非的确须要服务器打消反复的行，否则就肯定要应用UNION ALL，这一点很重要。如果没有ALL关键字，MySQL会给长期表加上DISTINCT选项，这会导致对整个长期我的数据做唯一性查看。这样做的代价十分高。如果不须要这些查看，那么甚至都不须要长期表。

另外，防止对于union之后的后果集进行操作，也能防止长期表的应用，通常须要手工地将MHERE、LIMIT、ORDER BY等子句“下推”到UNION的各个子查问中，以便优化器能够充分利用这些条件进行优化，使得union的后果就是最终的后果（例如，间接将这些子句冗余地写一份到各个子查问)。

3 group by长期表优化

另外一个应用长期表的例子是group by，group by还具备暗藏的排序的语句，即在对某些字段进行分组之后，将数据再依据这些字段进行排序，最初返回排序后的后果。

如下sql：

  select id%10 as m, count(*) as c from t1 group by m;

这个语句的执行流程是这样的：

  1. 创立内存长期表，表里有两个字段m和c，主键是m；

  2. 扫描表t1的索引a，顺次取出叶子节点上的id值，计算id%10的后果，记为x；

    1. 如果长期表中没有主键为x的行，就插入一个记录(x,1);
    2. 如果表中有主键为x的行，就将x这一行的c值加1；
  3. 遍历实现后，再依据字段m做排序，失去后果集返回给客户端。

此时，Explain的Extra字段中具备Using temporary; Using filesort标记。

如果并不需要对后果进行排序，那能够在SQL语句开端减少order by null，即：

  select id%10 as m, count(*) as c from t1 group by m order by null;

这样就跳过了最初排序的阶段，间接从长期表中取数据返回。

内存长期表的大小是有限度的，参数tmp_table_size就是管制这个内存大小的，默认是16M。如果要解决的数据超过了最大大小，那么MySQL会把内存长期表转成磁盘长期表，而磁盘长期表默认应用的引擎是InnoDB，因而会按主键顺序存储数据，所以最终取出的后果还是默认有序的。

对于Group By的长期表的优化，同样是应用索引：因为如果进行Group By字段是有序的，那么在解决时（比方计算每组数量、个数等等），因为跟着的字段有索引，那么雷同的值必定是在一起的、间断的，所以间接程序扫描输出的数据即可，不须要长期表，也不须要再额定排序。

总结：

  1. 如果语句执行过程能够一边读数据，一边间接失去后果，是不须要额定内存的，否则就须要额定的内存，来保留两头后果；
  2. join_buffer是无序数组，sort_buffer是有序数组，长期表是二维表构造；

如果执行逻辑须要用到二维表个性，就会优先思考应用长期表。比方咱们的例子中，union须要用到惟一索引束缚， group by还须要用到另外一个字段来存累积计数。

另外，对于distinct查问来说，如果无奈应用索引，则也会应用到长期表，也会进行分组，它和group by的区别是不须要排序。想尝试的小伙伴能够在3A云服务器上部署一套环境，本人学习一下。