mysql中的分组 -- group by

目录

一、前言

关于联合索引：

官方文档：

二、group by语句3种执行逻辑：

2.1松散索引扫描（Loose Index Scan）

概念：

原理：

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

2.2紧凑索引扫描（Tight Index Scan）

概念：

原理：

使用紧凑索引扫描需要满足以下条件：

松散索引扫描和紧凑索引扫描的比较：

2.3内部内存临时表

三、group by语句的优化：

四、DISTINCT 优化

概念：

举例：

---

一、前言

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

关于联合索引：

• (where语句)条件查询时，联合索引的顺序不需要按照查询条件的顺序建立，只要联合索引的第一个字段在where条件存在，就会使用到该联合索引。

• (group by语句)分组时，联合索引必须按照分组条件的顺序建立，否则不会使用到索引！

官方文档：

MySQL :: MySQL 5.7 Reference Manual :: 8.2.1.15 GROUP BY Optimization

二、group by语句3种执行逻辑：

2.1松散索引扫描（Loose Index Scan）

概念：

• 利用索引有序的这个特性，在扫描索引时只需要扫描符合where条件的key的一部分，而不需要扫描所有符合where条件的key，因此称为松散的索引扫描。

原理：

• 松散索引扫描查找每个分组中第一个满足where条件的索引：
  • 当查询中没有where条件的时，松散索引扫描查找每个分组中第一个索引，松散索引扫描读取的索引节点的个数和分组的数量相同。
  • 当where条件中包含范围条件（BETWEEN、IN、>=、LIKE等操作）时，松散索引扫描查找每个分组中第一个满足范围条件的索引。

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

• group by指定的所有列是索引的一个最左前缀。
• 例如：表t1（ c1,c2,c3,c4）上建立了索引（c1,c2,c3）
  • group所有列是索引最左前缀，可以使用松散索引扫描。   eg：“group by c1,c2”        
  • group所有列非索引最左前缀，不会使用松散索引扫描。eg：“group by c2,c3” 和 “group by c1,c2,c4”

2.2紧凑索引扫描（Tight Index Scan）

概念：

• 当不满足松散索引扫描的条件时，mysql会扫描所有符合where条件的key，而不需要扫描所有符合where条件的key，因此称为紧密索引扫描。

原理：

• 如果有where条件，则扫描所有满足这些条件的索引。
• 若没有where条件，则扫描整个索引。
• 使用紧密索引扫描，只有在找到所有满足范围条件的索引后才执行分组操作。

使用紧凑索引扫描需要满足以下条件：

• group by指定的所有列 + where条件中等式条件的常量 可以构造出索引的最左前缀。（说明：group by指定的列的相对顺序不能变化）
• 例如：
• 表结构：table t1(c1,c2,c3,c4)- idx(c1,c2,c3)
  • 可以使用松散索引扫描：
    • SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3; 
    • SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;
  • 不会使用松散索引扫描：
    • SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c3, c1; 

松散索引扫描和紧凑索引扫描的比较：

不同点：

• 松散索引扫描不需要对指定的范围全扫描或索引全扫描。
• 紧凑索引扫描需要对对指定的范围全扫描(where条件)或索引树全扫描(无where条件)。

相同点：

• 通过松散索引扫描和紧凑索引扫描获取的分组的结果是有序的，如果sql中需要对分组的结果进行排序，则无需进行额外的排序操作。

2.3内部内存临时表

原理：

• 若不满足松散索引扫描和紧凑索引扫描的条件，则mysql会扫描整个表并创建一个临时表来进行分组操作。
• 若临时表的大小超过了min(tmp_table_size, max_heap_table_size)，则会借助硬盘来完成分组操作。

相关参数：

• tmp_table_size：
  • 内部(mysql自己创建的)内存临时表的最大值，实际上，内部内存临时表的最大值为min(tmp_table_size, max_heap_table_size)
  • 文档：https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmp_table_size
• max_heap_table_size:
  • 用户创建的(mysql外部创建的)内存表的最大值，这个参数是mysql用来计算内存表的最大行数，同时也用来限制内部内存临时表的大小。
  • 文档：https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

三、group by语句的优化：

按照分组字段的顺序建立联合索引。

四、DISTINCT 优化

概念：

在大多数情况下，distinct子句可以被看做是一种特殊的group by子句，所以针对group by语句的优化(松散索引扫描、紧凑索引扫描)同样也适用于distinct语句。

举例：

以下两个查询语句是等价的。

SELECT DISTINCT c1, c2, c3 FROM t1
WHERE c1 > const;

SELECT c1, c2, c3 FROM t1
WHERE c1 > const GROUP BY c1, c2, c3;