MySQL关于group by的优化

MySQL :: MySQL 8.0 Reference Manual :: 10.2.1.17 GROUP BY Optimizationhttps://dev.mysql.com/doc/refman/8.0/en/group-by-optimization.html

10.2.1.17 GROUP BY Optimization

满足GROUP BY子句的最通用方法是扫描整个表并创建一个新的临时表，其中每个组的所有行都是连续的，然后使用这个临时表来发现组并应用聚合函数(如果有的话)。在某些情况下，MySQL能够做得更好，并且通过使用索引访问来避免创建临时表。

为GROUP BY使用索引的最重要的先决条件是，所有GROUP BY列引用来自同一索引的属性，并且索引按顺序存储其键(例如，对于BTREE索引是如此，但对于HASH索引则不是如此)。临时表的使用是否可以被索引访问取代还取决于查询中使用索引的哪些部分、为这些部分指定的条件以及所选择的聚合函数。

有两种方法可以通过索引访问执行GROUP BY查询，下面几节将详细介绍。第一种方法将分组操作与所有范围谓词(如果有的话)一起应用。第二个方法首先执行范围扫描，然后对结果元组进行分组。

松散索引扫描

紧索引扫描

在某些条件下，松散索引扫描也可以在没有GROUP BY的情况下使用。请参见跳过扫描范围访问方法。

松散索引扫描

处理GROUP BY的最有效方法是使用索引直接检索分组列。使用这种访问方法，MySQL使用键排序的一些索引类型的属性(例如，BTREE)。此属性允许在索引中使用查找组，而不必考虑索引中满足所有WHERE条件的所有键。这种访问方法只考虑索引中键的一小部分，因此称为松散索引扫描。当没有WHERE子句时，松散索引扫描读取的键与组的数量一样多，这可能比所有键的数量要小得多。如果WHERE子句包含范围谓词(请参阅第10.8.1节“使用EXPLAIN优化查询”中关于范围连接类型的讨论)，松散索引扫描查找满足范围条件的每个组的第一个键，并再次读取尽可能少的键。这在以下条件下是可能的:

查询是针对单个表的。

GROUP BY只命名构成索引最左边前缀的列，不命名其他列。(如果查询有一个DISTINCT子句，而不是groupby，则所有不同的属性都引用构成索引最左边前缀的列。)例如，如果表t1在(c1,c2,c3)上有索引，则如果查询具有GROUP BY c1,c2，则适用松散索引扫描。如果查询具有GROUP BY c2, c3(列不是最左边的前缀)或GROUP BY c1, c2, c4 (c4不在索引中)，则不适用。

在选择列表中使用的唯一聚合函数(如果有的话)是MIN()和MAX()，它们都引用同一列。列必须在索引中，并且必须紧跟着GROUP BY中的列。

除了查询中引用的GROUP BY之外，索引的任何其他部分都必须是常量(也就是说，它们必须在使用常量的等式中被引用)，除了MIN()或MAX()函数的参数。

对于索引中的列，必须索引完整的列值，而不仅仅是一个前缀。例如，对于c1 VARCHAR(20)， INDEX (c1(10))，索引仅使用c1值的前缀，不能用于松散索引扫描。

如果松散索引扫描适用于查询，EXPLAIN输出将在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()之外，还有其他聚合函数:

SELECT c1, SUM(c2) FROM t1 GROUP BY c1

GROUP BY子句中的列不构成索引的最左边的前缀:

SELECT c1, c2 FROM t1 GROUP BY c2, c3

该查询引用了键中GROUP BY部分之后的部分，并且该部分与常量不相等:

SELECT c1, c2 FROM t1 GROUP BY c1, c2

如果查询包含WHERE c3 = const，则可以使用松散索引扫描。

除了已经支持的MIN()和MAX()引用外，松散索引扫描访问方法还可以应用于选择列表中其他形式的聚合函数引用:

支持AVG(DISTINCT)、SUM(DISTINCT)和COUNT(DISTINCT)。AVG(DISTINCT)和SUM(DISTINCT)接受一个参数。COUNT(DISTINCT)可以有多个列参数。

优化案例：

 elfo表总1800w行。过滤条件过滤后是300w行，group by后600行

desc SELECT elfo.edi_no, elfo.owner_code, uuid() AS ROWNUM__ FROM elfo WHERE 1 = 1         AND elfo.edi_no <> 'erp-stockstreammergesingle-send'         AND elfo.owner_code IN ('672061', '672062', '672063', '672064', '672065', '672066', '672067', '672068', '672069', '672070', '672071', '672072', '672073', '672075', '672076', '672077', '672078', '672079', '672080', '672081', '672082', '672083', '672085', '672086')         AND elfo.job_status = '00' GROUP BY elfo.edi_no, elfo.owner_code\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: elfo
   partitions: NULL
         type: range
possible_keys: edi_log_front_owner_code_IDX,owner_code_IDX,idx_multiple_index
          key: idx_multiple_index
      key_len: 680
          ref: NULL
         rows: 573
     filtered: 100.00
        Extra: Using where; Using index for group-by
1 row in set, 1 warning (0.01 sec)

root@localhost 13:35:31 [sipi_edi_db]> 

优化方案：

创建的索引

ALTER TABLE `elfo` 
DROP INDEX `idx_multiple_index`,
ADD INDEX `idx_multiple_index`(`edi_no`, `owner_code`, `job_status`, `edlf_id`)