mysql内部临时表和分组优化

Posted on |

mysql什么时候使用到内部临时表，通过explain可以看到extra信息里面会显示using temporary。我们知道针对mysql的order by操作，维护了一个内存空间sort_buffer，超出这个内存之后会采用磁盘辅助排序，extra信息中会有using filesort。那么mysql什么时候会有using temporary并且如何进行优化。

内部临时表，存储过程类似sort_buffer的进化过程，先存内存，后退化到磁盘辅助。可以查看到mysql为内部临时表分配的内存大小：

1
2
3
4
5
6
-- 查看配置temp_table_size大小
show variables like "tmp_table%"
-- result 1KB 超过1KB之后用磁盘存储
tmp_table_size	1024
-- 可以设定temp_table_size内存的大小,global去除只对当前session生效
set global tmp_table_size=1024

union操作using temporary

union操作的语义，将两个结果集整合到一个结果集，union操作会有using temporary，用到内部临时表。主要由于union操作需要去重，如果是union all操作会直接将结果返回给客户端，不在内部临时表做去重操作。

group by操作using temporary

group by操作的过程：

  • 1.根据结果集的字段建立内部临时表，分组id，count两个字段。
  • 2.全表扫描需要分组的表，如果内部临时表没有对应的分组id，新增一条记录。
  • 3.如果内部临时表有对应的分组id，直接count++累加。
  • 4.根据id进行排序order by将结果存在内存或者持久化到临时磁盘表。

group by操作索引优化

group by操作explain如果extra信息中有using temporary和using filesort，在数据量比较大的情况下会造成慢查询。mysql 5.7以上版本提供关联列更新操作，generated column关联某列数据的更新。

1
2
-- 增加一列，列的值为id % 100（分组字段） ，然后为该列建立索引（建立索引避免了排序）。generated column操作会关联列，继续新增一条记录即使没有制定值也会通过关联的列的值计算。
alter table t1 add column z int generated always as (id % 100),add index z_index(z);

通过加索引，避免了使用临时表，同时避免了使用排序（取出来即为有序），也就是extra中不会出现using temporary和using filesort。那么这个过程为什么避免了？

  • 没有索引的情况下，需要临时表来记录，因为过程无序，并且最终需要排序。
  • 有索引的情况下本身有序，不需要记录累加的结果，结果在扫描过过程中得到并且是有序的。

group by操作存储优化

内存临时表容量，也就是tmp_table_size不够大的时候，会退化为磁盘存储，在数据量大的时候直接指定使用磁盘存储。

1
2
-- 通过SQL_BIG_RESULT这种方式指定不走内存内存临时表，直接用磁盘临时表。
select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;

总结

  • sort_buffer是有序数组，tmp_table_size临时表是二维表结构，具备表的语义，join_buffer是无序数组，均可查看配置大小。

  • 执行过程中需要使用二维表结构语义的，优先使用临时表。union操作需要检验唯一约束，需要使用临时表存储。group by需要在过程中用另一个字段计算累加结果。

  • 扫描过程中可以直接得到结果的，不需要使用临时表，需要计算的要使用临时表。