主流网站开发语言,wordpress模板建站,wordpress站群远程管理软件,博山信息港更好的阅读体验#xff0c;请点击 YinKai s Blog。 在 MySQL 中 group by 用于按照一个或多个列对结果集进行分组。在讨论 group by 怎么优化之前#xff0c;我们先来看看 group by 的执行流程#xff0c;这样我们才能对症下药。
group by 执行流程
我们先用下面的 … 更好的阅读体验请点击 YinKai s Blog。 在 MySQL 中 group by 用于按照一个或多个列对结果集进行分组。在讨论 group by 怎么优化之前我们先来看看 group by 的执行流程这样我们才能对症下药。
group by 执行流程
我们先用下面的 sql 语句创建一个表并输入一些数据模拟真实环境。
create table t1(id int primary key, a int, b int, index(a));
delimiter ;;
create procedure idata()
begindeclare i int;set i1;while(i1000)doinsert into t1 values(i, i, i);set ii1;end while;
end;;
delimiter ;
call idata(); 然后我们执行下面的语句
select id%10 as m, count(*) as c from t1 group by m order by m; 这个语句的逻辑是把表 t1 里的数据按照 id%10 进行分组统计并按照 m 的结果排序后输出。它的 explain 结果如下 在 Extra 字段我们可以看到三个信息
Using index表示这个语句使用了索引覆盖选择了索引 a不需要回表Using temporary表示使用了临时表Using filesort表示需要排序
这个语句的执行过程是
创建内存临时表表里有两个字段 m 和 c主键是 m扫描表 t1 的索引 a依次取出叶子结点上的 id 值计算 id%10 的结果记为 x 如果临时表中没有主键为 x 的行就插入一个记录 (x, 1)如果临时表中有主键为 x 的行就将 x 这一行的 c 值加 1 遍历完成后再根据字段 m 做排序得到的结果返回给客户端。
这个流程的执行图如下 图中最后一步对内存临时表的排序过程如下 上面的例子由于临时表只有 10 行内存可以放得下因此只使用了内存临时表。但内存临时表是有大小限制的可以通过参数 tmp_table_size 修改默认是 16M。
如果我执行下面这个语句序列
set tmp_table_size1024;
select id%100 as m, count(*) as c from t1 group by m order by null limit 10; 把内存临时表的大小限制为最大 1024 字节并把语句改成 id % 100这样返回结果里有 100 行数据。但是这时的内存临时表大小不够存下这 100 行数据也就是说执行过程中会发现内存临时表大小到达了上限1024 字节。
那么这时候就会把内存临时表转成磁盘临时表磁盘临时表默认使用的是 InnoDB结果如下 如果这个表 t1 的数据量很大很可能这个查询的磁盘临时表需要用到很大的磁盘空间查询生成大型临时表占用大量磁盘空间可能导致查询变慢引起磁盘空间不足影响系统稳定性。
因此这就是为什么我们需要去优化 group_by 的原因。
group by 优化方法——索引
要解决 group by 的优化问题我们需要从根本上去解决问题即执行 group by 语句创建的临时表。
group by 的语义逻辑是统计不同的值出现的个数。但是由于每一行的 id%100 的结果是无序的所以我们就需要有一个临时表来记录并统计结果。
那我们假想出现的数据都是有序的看看 group by 会怎么做。 如果我们可以确保输入的数据都是有序的那么计算 group by 的时候就只需要从左往右顺序扫描依次累加即
当碰到第一个 1 的时候已经知道累积了 X 个 0结果集里的第一行就是 (0,X);当碰到第一个 2 的时候已经知道累积了 Y 个 1结果集里的第二行就是 (1,Y);
按照这个逻辑执行的话扫描到整个输入的数据结束就可以拿到 group by 的结果不需要临时表也不需要再额外排序。
不难想到InnoDB 的索引就可以满足这个输入有序的条件。
我们可以 MySQL5.7 版本的 generated column 机制用来实现列数据的关联更新。你可以用下面的方法创建一个列 z然后在 z 列上创建一个索引
alter table t1 add column z int generated always as(id % 100), add index(z); 这样索引 z 上的数据就是类似上图那样有序的了。上面的 group by 语句就可以改成
select z, count(*) as c from t1 group by z; 优化后的 group by 语句的 explain 结果如下图所示 从 Extra 字段可以看出这个语句的执行不再需要临时表了也不需要排序了。
group by 优化方法 – 直接排序
如果能使用创建索引的方式来优化那再好不过万一要是遇到了不适合创建索引的创建我们又该怎么办呢
当我们明确知道一个 GROUP BY 语句中涉及的数据量非常大而 MySQL 的默认行为是首先尝试在内存中创建临时表然后在内存不足的情况下将其转为磁盘临时表我们可能希望直接走磁盘临时表的方式以避免不必要的内存消耗。MySQL 提供了一个查询提示 SQL_BIG_RESULT 来实现这一点。
具体而言你可以在 GROUP BY 语句中加入 SQL_BIG_RESULT 提示告诉优化器由于数据量较大请直接使用磁盘临时表。这样优化器会考虑在磁盘上存储临时表而不是首先尝试在内存中完成这一操作。
以下是使用 SQL_BIG_RESULT 提示的一个示例
SELECT SQL_BIG_RESULT id % 100 AS m, COUNT(*) AS c FROM t1 GROUP BY m;这个查询的执行流程可以描述为
初始化 sort_buffer确定放入一个整型字段 m。扫描表 t1 的索引 a依次取出其中的 id 值将 id % 100 的值存入 sort_buffer 中。扫描完成后对 sort_buffer 的字段 m 进行排序。如果 sort_buffer 内存不足将会利用磁盘临时文件辅助排序。排序完成后得到一个有序数组。根据有序数组获取数组中的不同值以及每个值的出现次数。
这样通过使用 SQL_BIG_RESULT 提示你可以明确告知 MySQL 优化器考虑到数据量很大直接使用磁盘临时表。
执行 explain 的结果如下图 从 Extra 字段可以看到这个语句的执行没有再使用临时表而是直接用了排序算法。
小结
现在我们来总结一下使用 group by 需要注意的一些点
如果对 group by 语句的结果没有排序要求要在语句后面加 order by null使用 group by 的时候尽可能用上表的索引确认的方法是查看 explain 结果里有没有 Using temporary 和 Using filesort如果 gruop by 需要统计的数据量不大尽量只使用内存临时表也可以通过适当调大 tmp_table_size 参数避免使用磁盘临时表如果数据量实在太大使用 SQL_BIG_RESULT 这个提示来告诉优化器直接使用排序算法得到 group by 的结果。
最后我们来看一看文章开头的问题 MySQL中 group by 怎么优化 尽可能保证 group by 语句上存在索引这样有助于数据引擎更有效地执行分组操作我们可以通过查看执行计划 explain 的输出来确认是否使用了索引。如果内存临时表足够容纳 group by 的结果集的话可以适当增加内存临时表的参数大小使 MySQL 更倾向于使用内存临时表因为内存的读写速度远高于磁盘这样可以显著提高查询性能。如果 GROUP BY 的字段是通过某个表达式计算而来考虑使用生成列并在生成列上创建索引。在 GROUP BY 的数据量非常大且无法通过其他手段优化时可以使用 SQL_BIG_RESULT 提示**让优化器直接使用排序算法而不是创建临时表**这样 MySQL 就可以直接通过遍历数组获取我们想要的结果。
