mysql的group by 优化

Posted by 2023年1月19日 in 开源软件配置

一、group by

当我们执行 group by 操作在没有合适的索引可用的时候，通常先扫描整个表提取数据并创建一个临时表，然后按照 group by 指定的列进行排序。在这个临时表里面，对于每一个 group 的数据行来说是连续在一起的。完成排序之后，就可以发现所有的 groups，并可以执行聚集函数（aggregate function）。可以看到，在没有使用索引的时候，需要创建临时表和排序。在执行计划中通常可以看到“Using temporary; Using filesort”。

create table t1(id int primary key, a int, b int, index(a));
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();

一个常见的使用临时表的例子是 group by，我们来看一下这个语句：

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

这个语句的逻辑是把表 t1 里的数据，按照 id%10 进行分组统计，并按照 m 的结果排序后输出。它的 explain 结果如下：

mysql> explain select id%10 as m, count(*) as c from t1 group by m;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                        |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | PRIMARY,a     | a    | 5       | NULL | 1000 |   100.00 | Using index; Using temporary; Using filesort |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------+
1 row in set, 1 warning (0.01 sec)

在 Extra 字段里面，我们可以看到三个信息：

  • Using index，表示这个语句使用了覆盖索引，选择了索引 a，不需要回表；
  • Using temporary，表示使用了临时表；
  • Using filesort，表示需要排序。

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

1. 创建内存临时表，表里有两个字段 m 和 c，主键是 m；

2. 扫描表 t1 的索引 a，依次取出叶子节点上的 id 值，计算 id%10 的结果，记为 x；

3. 如果临时表中没有主键为 x 的行，就插入一个记录 (x,1); 如果表中有主键为 x 的行，就将 x 这一行的 c 值加 1；

4. 遍历完成后，再根据字段 m 做排序，得到结果集返回给客户端。

这个流程的流程图如下：

在使用group by 分组查询时，默认分组后，还会排序，可能会降低速度。如果你的需求并不需要对结果进行排序，那你可以在 SQL 语句末尾增加 order by null，也就是改成：

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

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

由于表 t1 中的 id 值是从 1 开始的，因此返回的结果集中第一行是 id=1；扫描到 id=10 的时候才插入 m=0 这一行，因此结果集里最后一行才是 m=0。

这个例子里由于临时表只有 10 行，内存可以放得下，因此全程只使用了内存临时表。但是，内存临时表的大小是有限制的，参数 tmp_table_size 就是控制这个内存大小的，默认是 16M。

如果我执行下面这个语句序列：

set tmp_table_size=1024;
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 的数据量很大，很可能这个查询需要的磁盘临时表就会占用大量的磁盘空间。

可以看出，相同的语句，由于调整 tmp_table_size 参数大小，查询结果排序方式却不同。这就是因为第一个查询使用的是内存临时表，上面已经提到了，是按照表 t1 的索引 a 顺序取出数据，模 10 得 0 的 id 是最后一行；第二个查询使用的是硬盘临时表，默认用 InnoDB 的引擎，主键是 id%10，因此存入硬盘后再按主键树顺序取出，0 就排到第一行了，InnoDB 表是顺序存储的。

group by 优化方法 — 索引

可以看到，不论是使用内存临时表还是磁盘临时表，group by 逻辑都需要构造一个带唯一索引的表，执行代价都是比较高的。如果表的数据量比较大，上面这个 group by 语句执行起来就会很慢，我们有什么优化的方法呢？

要解决 group by 语句的优化问题，你可以先想一下这个问题：执行 group by 语句为什么需要临时表？ group by 的语义逻辑，是统计不同的值出现的个数。但是，由于每一行的 id%100 的结果是无序的，所以我们就需要有一个临时表，来记录并统计结果。 那么，如果扫描过程中可以保证出现的数据是有序的，是不是就简单了呢？

确实是这样，如果可以确保输入的数据是有序的，那么计算 group by 的时候，就只需要从左到右，顺序扫描，依次累加。比如数据结构（0, 0, 1, 1, 2, 2），那么这个过程如下：

  • 当碰到第一个 1 的时候，已经知道累积了 X 个 0，结果集里的第一行就是 (0,X);
  • 当碰到第一个 2 的时候，已经知道累积了 Y 个 1，结果集里的第二行就是 (1,Y);

按照这个逻辑执行的话，扫描到整个输入的数据结束，就可以拿到 group by 的结果，不需要临时表，也不需要再额外排序。也就是说，如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存的，否则就需要额外的内存，来保存中间结果。

所以，我们自然想到索引。MySQL 建立的 B+Tree 索引原生就是有序的，如果通过读取索引就完成 group by 操作，那么就可避免创建临时表和排序。因此使用索引进行 group by 的最重要的前提条件是所有 group by 的参照列（分组依据的列）来自于同一个索引，且索引按照顺序存储所有的 key（即BTREE index，而HASH index没有顺序的概念）。

MySQL 有两种索引扫描方式完成 group by 操作，分别是松散索引扫描和紧凑索引扫描以及临时表实现 group by。在松散索引扫描方式下，分组操作和范围预测（如果有的话）一起执行完成的。在紧凑索引扫描方式下，先对索引执行范围扫描（range scan），再对结果元组进行分组。

松散索引扫描（Loose Index Scan）

何谓松散索引扫描实现 group by 呢？实际上就是当 MySQL 利用索引扫描来实现 group by 的时候，并不需要扫描所有满足条件的索引键即可完成操作得出结果。

松散索引扫描仅考虑索引中的一部分，当查询中没有 where 条件的时候，松散索引扫描读取的索引元组的个数和 groups 的数量相同，如果 where 条件包含范围查询，松散索引扫描查找每个 group 中第一个满足范围条件的键，并再次读取尽可能少的键。松散索引扫描只需要读取很少量的数据就可以完成 group by 操作，因而执行效率非常高。

使用松散索引扫描需要满足以下条件：

1. 查询在单一表上。

2. group by 指定的所有列是索引的一个最左前缀，并且没有其它的列。比如表 t1(c1,c2,c3,c4）上建立了索引 (c1,c2,c3）。如果查询包含 “group by c1,c2”，那么可以使用松散索引扫描。但是 “group by c2,c3” (不是索引最左前缀) 和 “group by c1,c2,c4” (c4字段不在索引中)无法使用。

3. 如果在选择列表 select list 中存在聚集函数，只能使用 min() 和 max() 两个聚集函数，并且指定的是同一列（如果 min() 和 max() 同时存在），这一列必须在索引中，且紧跟着 group by 指定的列。比如 select t1,t2,min(t3),max(t3) from t1 group by c1,c2。这源于索引的有序排序，优化器意识到 min/max 位于最左/右块，从而避免范围扫描。

4. 如果查询中存在除了 group by 指定的列之外的索引其他部分，那么必须以常量的形式出现（除了min() 和 max() 两个聚集函数）。比如 select c1,c3 from t1 group by c1,c2 不能使用松散索引扫描。而 select c1,c3 from t1 where c3 = 3 group by c1,c2 可以使用松散索引扫描。

5. 索引中的列必须索引整个数据列的值，而不是一个前缀索引。比如，c1 varchar(20), INDEX (c1(10))，这个索引没发用作松散索引扫描。

如果查询能够使用松散索引扫描，那么执行计划中 Extra 中提示 “using index for group-by”。

假设 t1(c1,c2,c3,c4) 表上有一个索引 idx(c1,c2,c3)，松散索引扫描访问方法可用于以下查询：

SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT c1, c2 FROM t1;
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;

执行以下查询无法使用松散索引扫描：

  • 除了 MIN() 或 MAX() 之外还有聚合功能，比如 SUM() ：
  • SELECT c1, SUM(c2) FROM t1 GROUP BY c1;
  • group by 子句中的列不会形成索引的最左前缀：
  • SELECT c1, c2 FROM t1 GROUP BY c2, c3;
  • 查询列与 group by 不相等：
  • SELECT c1, c3 FROM t1 GROUP BY c1, c2;

要查询包含 where c3=const 可以使用松散索引扫描。

除了已经支持的 MIN() 和 MAX() 引用之外，松散索引扫描访问方法可以应用于选择列表中的其他形式的聚合函数引用，如：AVG(DISTINCT)，SUM(DISTINCT)、COUNT(DISTINCT)。AVG(DISTINCT) 和 SUM(DISTINCT) 采取单个列参数，COUNT(DISTINCT) 可以有多个列参数。

假设 t1(c1,c2,c3,c4) 表上有一个索引 idx(c1,c2,c3)，松散索引扫描访问方法可用于以下查询：

SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;
SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;

为什么松散索引扫描的效率会很高?

因为在没有 where 子句，也就是必须经过全索引扫描的时候，松散索引扫描需要读取的键值数量与分组的组数量一样多，也就是说比实际存在的键值数目要少很多。而在 where 子句包含范围判断式或者等值表达式的时候， 松散索引扫描查找满足范围条件的每个组的第1个关键字，并且再次读取尽可能最少数量的关键字。

紧凑索引扫描（Tight Index Scan）

group by 在无法使用松散索引扫描时，还可以选择紧凑索引扫描，若两者都不可选，则只能借助临时表。

紧密索引扫描可以是完整索引扫描，也可以是范围索引扫描，具体取决于查询条件。

如果 where 条件有范围扫描，那么紧凑索引扫描仅读取满足这些条件的键（索引元组），否则执行全索引扫描。这种方式读取所有 where 条件定义的范围内的键，或者扫描整个索引，因而称作紧凑索引扫描。对于紧凑索引扫描，只有在所有满足范围条件的键被找到之后才会执行分组操作。

要使紧凑索引扫描起作用，在查询中存在常量相等的 where 条件字段（索引中的字段），且该字段在 group by 指定的字段的前面或者中间，来自于相等条件的常量能够填充搜索键中的间隙，因而可以构成一个索引的完整前缀，索引前缀能够用于索引查找。而如果需要排序 group by 结果，并且能够形成索引前缀的搜索关键字，还可以避免额外的排序操作，因为使用有顺序的索引的前缀进行搜索已经按顺序检索到了所有关键字。

紧凑索引扫描实现 group by 和松散索引扫描的区别主要在于他需要在扫描索引的时候，读取所有满足条件的索引键，然后再根据读取的数据来完成 group by 操作得到相应结果。

这时候的执行计划的 Extra 信息中已经没有 “Using index for group-by” 了，但并不是说 MySQL 的 group by 操作并不是通过索引完成的，只不过是需要访问 where 条件所限定的所有索引键信息之后才能得出结果。这就是通过紧凑索引扫描来实现 group by 的执行计划输出信息。

假设 t1(c1,c2,c3,c4) 表上有一个索引 idx(c1,c2,c3)，以下查询不适用于之前描述的松散索引扫描访问方法，但仍然可以使用紧凑索引扫描访问方法。

  • 这 group by 有一个间隙，但它被覆盖的条件为 c2 = ‘a’：
  • SELECT c1, c2, c3 FROM t1 WHERE c2 = ‘a’ GROUP BY c1, c3;
  • 这 group by 不是从列的第一部分开始，但是有一个条件为该部分提供了一个常数：
  • SELECT c1, c2, c3 FROM t1 WHERE c1 = ‘a’ GROUP BY c2, c3;

在 MySQL 中，MySQL Query Optimizer 首先会选择尝试通过松散索引扫描来实现 group by 操作，当发现某些情况无法满足松散索引扫描实现 group by 的要求之后，才会尝试通过紧凑索引扫描来实现。

当 group by 条件字段并不连续或者不是索引前缀部分的时候，MySQL Query Optimize 无法使用松散索引扫描，设置无法直接通过索引完成 group by 操作，因为缺失的索引键信息无法得到。但是，如果查询语句中存在一个常量值来引用缺失的索引键，则可以使用紧凑索引扫描完成 group by 操作，因为常量填充了搜索关键字中的“间隙”，可以形成完整的索引前缀，这些索引前缀可以用于索引查找。而如果需要排序 group by 结果，并且能够形成索引前缀的搜索关键字，MySQL 还可以避免额外的排序操作，因为使用有顺序的索引的前缀进行搜索已经按顺序检索到了所有关键字。

group by 优化方法 — 直接排序

如果我们明明知道，一个 group by 语句中需要放到临时表上的数据量特别大，却还是要按照“先放到内存临时表，插入一部分数据后，发现内存临时表不够用了再转成磁盘临时表”，看上去就有点儿傻。

在 group by 语句中加入 SQL_BIG_RESULT 这个提示（hint），就可以告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。

MySQL 的优化器一看，磁盘临时表是 B+ 树存储，存储效率不如数组来得高。所以，既然你告诉我数据量很大，那从磁盘空间考虑，还是直接用数组来存吧。

因此，下面这个语句

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

的执行流程就是这样的：

1. 初始化 sort_buffer，确定放入一个整型字段，记为 m；

2. 扫描表 t1 的索引 a，依次取出里面的 id 值, 将 id%100 的值存入 sort_buffer 中；

3. 扫描完成后，对 sort_buffer 的字段 m 做排序（如果 sort_buffer 内存不够用，就会利用磁盘临时文件辅助排序）；

4. 排序完成后，就得到了一个有序数组。

根据有序数组，得到数组里面的不同值，以及每个值的出现次数。这一步的逻辑，已经从前面了解过了。执行流程如下图。

从执行计划上来看，从 Extra 字段可以看到，这个语句的执行没有再使用临时表，而是直接用了排序算法，认为用 sort_buffer 直接排序性能更好。

以上就介绍了MySQL查询优化MySQL分组查询Group By实现原理详解，包括了MySQL查询优化方面的内容，希望对MySQL有兴趣的朋友有所帮助。

二、group by 与 distinct

上面讲完 group by 的原理之后，还有一种与去重的语句 distinct。如果我们不需要对分组做聚合操作，那么 group by 与 distinct 谁的性能更好呢？

如果表 t 的字段 a 上没有索引，那么下面这两条语句的性能是不是相同的：

select a from t group by a order by null;
select distinct a from t;

首先需要说明的是，这种 group by 的写法，并不是 SQL 标准的写法。标准的 group by 语句，是需要在 select 部分加一个聚合函数，比如：

select a,count(*) from t group by a order by null;

这条语句的逻辑是：按照字段 a 分组，计算每组的 a 出现的次数。在这个结果里，由于做的是聚合计算，相同的 a 只出现一次。

没有了 count(*) 以后，也就是不再需要执行“计算总数”的逻辑时，第一条语句的逻辑就变成是：按照字段 a 做分组，相同的 a 的值只返回一行。而这就是 distinct 的语义，所以不需要执行聚合函数时，distinct 和 group by 这两条语句的语义和执行流程是相同的，因此执行性能也相同。

这两条语句的执行流程是下面这样的。

1. 创建一个临时表，临时表有一个字段 a，并且在这个字段 a 上创建一个唯一索引；

2. 遍历表 t，依次取数据插入临时表中：如果发现唯一键冲突，就跳过；否则插入成功；

3. 遍历完成后，将临时表作为结果集返回给客户端。

三、排序不一致问题

源起，阿里云论坛有人反应 MySQL 5.6 分页有重复值（排序字段没有用索引，或则直接是全表扫描），MariaDB 已经是优化后的方案，和 5.6 一致。阿里数据库月报也对此进行了回复：MySQL · 答疑解惑 · MySQL Sort 分页

测试表和数据：

create table t1(id int primary key, c1 int, c2 varchar(128));
insert into t1 values(1,1,'a');
insert into t1 values(2,2,'b');
insert into t1 values(3,2,'c');
insert into t1 values(4,2,'d');
insert into t1 values(5,3,'e');
insert into t1 values(6,4,'f');
insert into t1 values(7,5,'g');

假设每页 3 条记录，第一页 limit 0,3 和第二页 limit 3,3 查询结果如下：

# MySQL 5.6;
mysql> select * from t1 order by c1 limit 0,3;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 |    1 | a    |
|  3 |    2 | c    |
|  4 |    2 | d    |
+----+------+------+
3 rows in set (0.00 sec)

mysql> select * from t1 order by c1 limit 3,3;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  4 |    2 | d    |
|  5 |    3 | e    |
|  6 |    4 | f    |
+----+------+------+
3 rows in set (0.01 sec)

我们可以看到 id 为 4 的这条记录居然同时出现在两次查询中，这明显是不符合预期的，而且在 5.5 版本中没有这个问题。

使用优先队列排序的目的就是在不能使用索引有序性的时候，如果要排序，并且使用了 limit n，那么只需要在排序的过程中，保留 n 条记录即可，这样虽然不能解决所有记录都需要排序的开销，但是只需要 sort buffer 少量的内存就可以完成排序，上面已经说明。之所以 MySQL 5.6 出现了第二页数据重复的问题，是因为使用了优先队列排序，其使用了堆排序的排序方法，而堆排序是一个不稳定的排序方法，也就是相同的值（例子中的值2）可能排序出来的数据和读出来的数据顺序不一致，无法保证排序前后数据位置的一致，所以导致分页重复的现象。

避免这个问题在阿里月报有说：MySQL · 答疑解惑 · MySQL Sort 分页

但在 MySQL 5.7 版本中此问题又没有了。

# MySQL 5.7
mysql> select * from t1 order by c1 limit 0,3;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 |    1 | a    |
|  2 |    2 | b    |
|  3 |    2 | c    |
+----+------+------+
3 rows in set (0.00 sec)

mysql> select * from t1 order by c1 limit 3,3;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  4 |    2 | d    |
|  5 |    3 | e    |
|  6 |    4 | f    |
+----+------+------+
3 rows in set (0.00 sec)

You May Also Like

No Picture

npm 报错digital envelope routines::unsupported

No Picture

腾讯地图地图选点组件的使用