1.什么是临时表
2.临时表的使用场景
union
groupby
3.groupby 如何优化
总结
1.什么是临时表内部临时表是sql语句执行过程中,用来存储中间结果的的数据表,其作用类似于:join语句执行过程中的joinbuffer,order by语句执行过程中的sortBuffer一样。
这个表是mysql自己创建出来的,对客户端程序不可见。那么mysql什么时候会创建内部临时表呢?创建的内部临时表的表结构又是怎么样的呢?
2.临时表的使用场景在mysql中常见的使用临时表的场景,有两个:unoin语句和groupby语句。
为了更好的了解内部临时表在unoin和groupby中是如何起作用的,我们先了解一下unoin和groupby的执行流程。
为了方便下文的描述,我们建立如下表结构:
CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) default null,
PRIMARY KEY (`id`) USING BTREE,
key (`a`) using BTREE
) ENGINE=InnoDB;
建立表t1,其中id为主键,a为普通索引,然后向表中插入1000条数据:
drop procedure idata;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=1000)do
insert into `t1` values(i,i,i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
union
我们都知道union的语义是对 unoin两端的结果集取并集,也就是两个结果集加起来,重复的数据行,只取其中一行。这里需要注意,unoin是有在多个数据集中排重的语义的。
下面我们执行下面这条语句:
(select 1000 as f) union (select id from t1 order by id desc limit 2);
在这条语句的语义是:将t1中的数据,按照id倒序排列后,取出前两行数据的id,与"1000"取并集。
这条语句在mysql中的执行流程如下:
1.创建一个内存临时表,这个内存临时表只有一个整形字段f,并且f字段为主键(因为要进行排重)。
2.执行第一个子查询,得到1000这个值,放入到内存临时表中。
3.执行第二个子查询:取出第一个满足条件数据行中的id=1000,尝试写入临时表,这时会出现违反唯一性约束的情况,导致插入失败,然后继续执行。取出第二行数据的id=999,插入成功。
4.从临时表中取出数据,返回客户端结果,并删除临时表。
同时,我们可以查看上述查询语句的执行计划,来验证上述执行流程:
从以上过程中,我们可以知道,内存临时表的作用:通过唯一键约束,实现了union的语义。
如果把上述语句中的union,替换成 union all的话,那查询语句就失去了"去重"的语义了。那么,mysql在执行查询语句的过程中,是否还会使用临时表呢?
我们使用以下查询语句进行验证:
(select 1000 as f) union all (select id from t1 order by id desc limit 2);
通过查询sql的执行计划,我们会发现,查询语句执行过程中,不在需要临时表了。
整个查询语句的执行流程如下:
1.执行第一个子查询,将查询的结果,作为结果集的一部分,返回给客户端。
2.执行第二个子查询,将查询的结果作为结果集的一部分,返回给客户端。
groupby除了unoin查询语句在执行过程中会使用临时表外,groupby 查询语句在执行过程中,也会使用临时表。为了方便说明问题,我们执行如下查询语句:
select id%10 as m, count(1) as c from t1 group by m order by m;
该语句的语义,将表中所有数据中的id值,对10进行取模,并将取模后的结果进行分组,然后统计出每组数据的个数。查询语句执行计划如下:
该查询语句的执行流程如下:
1.创建临时表,表中有两个字段:m和c,其中m为主键,因为group by字段m的值,必须是唯一的。
2.扫描表t1的索引a,依次取出叶子结点上的id值,并计算id%10,将计算结果记为x,如果临时表中没有m=x的行,就插入一个记录(x,1)。如果表中有m=x数据行,那么就将x这一行的c值加1。
3.遍历完成后,在根据字段m做排序,得到最终结果返回给客户端。
对于步骤3中的排序流程,可以参考 如何优化sql中的orderBy。
3.groupby 如何优化通过上面的描述,我们知道了groupby的执行流程。groupby在执行过程中,需要建立一个带有唯一键索引的临时表,其中唯一键索引字段就是groupby的字段。这个执行代价还是比较高的,而且这个临时表还是一次性的。
为了提高groupby语句的执行性能,我们可以从"不使用临时表"的角度下手。首先我们可以这样想:要想让groupby的过程中不使用临时表,我们就要知道,临时表在groupby的过程中,解决了什么问题?如果,我们能找到另外一种不使用临时表,也能解决这个问题的方案,那么我们就可以不使用临时表了。
首先,我们知道,在日常开发过程中,我们使用groupby主要就是为了实现:将表中所有的数据,按照指定字段进行分组。把字段值相同的数据划分为一个组,然后在对组内的数据执行聚合函数,聚合函数计算的结果,作为结果集中的一行数据。
而在这个过程中,临时表的作用就是在扫描数据表的时候,对每行数据属于哪个组,进行记录,同时执行聚合函数的逻辑。之所以需要一个临时表来记录每行数据属于哪个组,主要是因为表中的数据,按照"group by字段"维度,不是有序的。
如果表中的数据本身就是按照"groupby字段"有序的话,也就是属于同一个组的数据都分布在一起,那么就不需要临时表,也可以对数据进行分组。 举例如下图,如果执行groupby,同时计算每组数据个数。执行流程大致如下:
1.从左到右扫描数据,并依次累加,当遇到第一个2时,说明已经积累了3个1了,此时结果集的第一行数据就是(1,3)。
2.当遇到第一个3的时候,说明已经积累了2个2了,此时结果集的第二行数据就是(2,2);
3.按照以上逻辑逐个计算,就可以得到最终结果。
在mysql中,如果分组字段上有索引的话,执行查询过程中,mysql就不会建立临时表了。
我们可以执行如下查询语句进行验证:
explain
select id as m from t1 group by id;
通过查看执行计划,我们可以发现,因为分组字段id,是主键,本身是有序的。这里并没有使用临时表:
但是很多时候,分组字段并不是表中的一个具体字段。而是通过一定计算后的逻辑字段,如:
select id%10 as m from t1 group by m
这里分组字段m,并不是t1表中的一个字段,而是对id对10取模后的一个逻辑字段。为了让分组字段有序,下面给大家介绍两种优化手段。
1.生成伴生字段,并建立索引
从mysql 5.7开始,支持了generated column机制,来实现字段数据的关联更新。如下语句:
alter table t1 add column z int generated always as (id % 10), add index (z);
为t1表增加字段z,z的字段值为id值与10取模后的结果,同时在z上添加索引。这样当我们再执行:
explain
select id%100 as m,count(*) as c from t1 group by m
或者:
explain
select z as m,count(id) as c from t1 group by m
执行计划如下:
此时就不在使用临时表了。
上面的伴生字段的方案,需要我们向表中添加额外字段,如果业务场景比较复杂,分组的场景比较多,使用伴生字段方案需要在表中增加的额外字段就会比较多。这将会使我们的数据表结果变得比较复杂。
2.直接对分组字段进行排序
如果我们可以预估到,在执行groupby语句时,分组后的数据量比较大,使用的内存临时表可能都无法存储,那么内存临时表就会被替换成磁盘临时表,这个替换的阈值,由变量"tmp_table_size"控制,该变量的默认值为16M,如果在查询语句执行过程,需要存放到临时表中的数据量超过16M,那么使用的临时表就会变成磁盘临时表,磁盘临时表默认的存储引擎是InnoDB,磁盘临时表的性能相比内存临时表性能更低。
对于这种情况,mysql提供了 SQL_BIG_RESULT语句,该语句的作用就是告诉优化器:这个语句涉及到的数据量比较大,直接使用磁盘临时表。但是这里使用的磁盘临时表,会调整存储的数据结构,数据结构不再是B+树,而是数组。
下面我们举例说明,执行如下查询语句的的流程如下:
explain
select sql_big_result id%100 as m,count(id) as c from t1 group by m ;
执行流程:
1.初始化sort_buffer,确定放入一个整形字段,记为m。
2.扫描t1索引a,依次取出叶子节点中的主键id的值,并对100取模,然后插入到sort_buffer中。
3.数据表扫描完后,对sort_buffer中的m进行排序。
4.排序后,就得到了一个针对分组字段的有序数组。
有了针对分组字段的有序数组,那么就可以通过遍历该数组实现groupby的语义了。
通过查看上述查询语句的执行计划,可以发现,不在使用临时表了。
总结为了保证groupby的执行性能,在使用groupby的时候要做到以下几点:
1.尽量让 group by 过程用上表的索引,确认方法是 explain 结果里没有 Using temporary 和 Using filesort。
2.如果 group by 需要统计的数据量不大,尽量只使用内存临时表;也可以通过适当调大 tmp_table_size 参数,来避免用到磁盘临时表。
3.如果数据量实在太大,使用 SQL_BIG_RESULT 这个提示,来告诉优化器直接使用排序算法得到 group by 的结果。
以上为个人经验,希望能给大家一个参考,也希望大家多多支持易知道(ezd.cc)。