MySQL 临时表的原理以及优化方法

目录

1 临时IcztZkHlyx表

sort buffer、内存临时表和join buffer，这三个数据结构都是用来存放语句执行过程中的中间数据，以辅助SQL语句的执行的。其中，在排序的时候用到了sort buffer，在使用joi编程客栈n语句的时候用到了join buffer。

而使用临时表的时候，Explain的Extra字段中具有Using temporary标记。union、group by、distinct等等查询都有可能使用到临时表。

2 union临时表优化

使用uniphpon的时候，就需要用到内存临时表来进行去重。

union语句的执行流程为：

如果使用union all，则不需要去重，也就不需要临时表了。在执行的时候mssql 临时表，就依次执行子查询，得到的结果直接作为结果集的一部分，发给客户端。因此，除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL，这一点很重要。如果没有ALL关键字，My编程SQL会给临时表加上DISTINCT选项，这会导致对整个临时我的数据做唯一性检查。这样做的代价非常高。如果不需要这些检查，那么甚至都不需要临时表。

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

3 group by临时表优化

另外一个使用临时表的例子是group by，group by还具有隐藏的排序的语句，即在对某些字段进行分组之后，将数据再根据这些字段进行排序，最后返回排序后的结果。

如下sql：

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

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

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

如果并不需要对结果进行排序，那可以在SQL语句末尾增加order by null，即：

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

这样就跳过了最后排序的阶段，直接从临时表中取数据返回。

内存临时表的大小是有限制的，参数tmp_table_size就是控制这个内存大小的，默认是16M。如果要处理的数据超过了最大大小，那么mysql会把内存临时表转成磁盘临时表，而磁盘临时表默认使用的引擎是InnoDB，因此会按主键顺序存储数据，所以最终取出的结果还是默认有序的。

对于Group By的临时表的优化，同样是使用索引：因为如果进行Group By字段是有序的，那么在处理时（比如计算每组数量、个数等等），因为跟着的字段有索引，那么相同的值肯定是在一起的、连续的，所以直接顺序扫描输入的数据即可，不需要临时表，也不需要再额外排序。

总结：

如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表。比如我们的例子中，union需要用到唯一索引约束， group by还需要用到另外一个字段来存累积计数。

另外，对于distinct查询来说，如果无法使用索引，则也会使用到临时表，也会进行分组，它和group by的区别是不需要排序。

到此这篇关于MySQL 临时表的原理以及优化方法的文章就介绍到这了,更多相关MySQL 临时表 内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

本文标题: MySQL 临时表的原理以及优化方法

（编辑：开发网_开封站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!