1、内存表,指的是使用 Memory 引擎的表,建表语法是 create table … engine=memory。这种表的数据都保存在内存里,系统重启的时候会被清空,但是表结构还在。除了这两个特性看上去比较“奇怪”外,从其他的特征上看,它就是一个正常的表。
2、临时表,可以使用各种引擎类型 。如果是使用 InnoDB 引擎或者 MyISAM 引擎的临时表,写数据的时候是写到磁盘上的。当然,临时表也可以使用 Memory 引擎。
临时表就特别适合我们文章开头的 join 优化这种场景。临时表允许重名,线程私有,异常不用自动删除数据。在实际应用中,临时表一般用于处理比较复杂的计算逻辑。在 binlog_format='row’的时候,临时表的操作不记录到 binlog 中,也省去了不少麻烦,这也可以成为你选择 binlog_format 时的一个考虑因素。
典型应用场景:
分表后需要limit 查询,比如limit 100。
可单独创建一个临时表,在所有分库上上执行limit 100,再将数据插入临时表,然后从临时表中limit 100,即可得出整表的limit 100值。
内部临时表:
sort buffer、内存临时表和 join buffer,这三个数据结构都是用来存放语句执行过程中的中间数据,以辅助 SQL 语句的执行的。
union+union all
可以看到union使用临时表,而union all不使用。因为union需要用临时表来暂存数据以去重,而union all不需要,直接依次执行并返回客户端结果。
group by:
select id%10 as m, count(*) as c from t1 group by m;
如果你的需求并不需要对结果进行排序,那你可以在 SQL 语句末尾增加 order by null,也就是改成:select id%10 as m, count(*) as c from t1 group by m order by null;
内存临时表的大小是有限制的,参数 tmp_table_size 就是控制这个内存大小的,默认是 16M。
set tmp_table_size=1024; 可临时将该参数调小
不论是使用内存临时表还是磁盘临时表,group by 逻辑都需要构造一个带唯一索引的表,执行代价都是比较高的。如果表的数据量比较大,上面这个 group by 语句执行起来就会很慢。
group by字段加索引,索引有序。如果是 MySQL 5.6 及之前的版本,你也可以创建普通列和索引,来解决这个问题。alter table t1 add column z int generated always as(id % 100), add index(z);
如果数据量很大,使用hint直接告诉mysql走磁盘临时表,而不是内存临时表。MySQL 的优化器一看,磁盘临时表是 B+ 树存储,存储效率不如数组来得高。所以,既然你告诉我数据量很大,那从磁盘空间考虑,还是直接用数组来存吧。
select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;
MySQL 什么时候会使用内部临时表?
1、如果语句执行过程可以一边读数据,一边直接得到结果,是不需要额外内存的,否则就需要额外的内存,来保存中间结果;
2、join_buffer 是无序数组,sort_buffer 是有序数组,临时表是二维表结构;
3、如果执行逻辑需要用到二维表特性,就会优先考虑使用临时表。比如我们的例子中,union 需要用到唯一索引约束, group by 还需要用到另外一个字段来存累积计数。
group by 的一些使用的指导原则:
1、如果对 group by 语句的结果没有排序要求,要在语句后面加 order by null;
2、尽量让 group by 过程用上表的索引,确认方法是 explain 结果里没有 Using temporary 和 Using filesort;
3、如果 group by 需要统计的数据量不大,尽量只使用内存临时表;也可以通过适当调大 tmp_table_size 参数,来避免用到磁盘临时表;
4、如果数据量实在太大,使用 SQL_BIG_RESULT 这个提示,来告诉优化器直接使用排序算法得到 group by 的结果。