关于数据库:MySQL-临时表的原理以及优化手段

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 云服务器上部署一套环境，本人学习一下。