SQL优化 - group by优化

分组操作原理

分组操作的实现方案一般有hash和排序操作这两种：

• hash：就是使用一个map，根据group by的列作为key，遍历表中数据集，放入map
• 排序：将数据集按照group by的列进行排序

MySQL用的是排序方案

1. 新建一个临时表
2. 扫描原始数据，按照group by的列进行排序，保证group by列值相同的行都在一起，然后按这个顺序插入临时表
3. 使用这个临时表来进行分组并应用聚合函数

如果group by的列使用到索引，那前面2步的开销一般可以省掉，也就是通过索引替代临时表，举个例子：

-- emp_no 上有索引, 耗时 0.4s
SELECT emp_no, MAX(salary) FROM salaries
GROUP BY emp_no LIMIT 10;-- from_date 上无索引, 耗时 2.665s
SELECT from_date, MAX(salary) FROM salaries
GROUP BY from_date LIMIT 10;

GROUP BY使用索引的最重要的前提条件是

1. 所有GROUP BY列都引用同一索引
2. 索引按顺序存储，BTREE索引是按顺序存储，但HASH索引则不是

同时，临时表的使用是否可以用索引访问代替，还取决于查询中使用了索引的哪些部分、为这些部分指定的条件以及所选的聚合函数。

Loose Index Scan

松散索引扫描可避免访问索引中的所有条目，并根据前缀列进行筛选。举个例子：

DROP TABLE IF EXISTS t;
CREATE TABLE t (pk_col1 INT NOT NULL,pk_col2 INT NOT NULL,c1 CHAR(64) NOT NULL,c2 CHAR(64) NOT NULL,PRIMARY KEY(pk_col1, pk_col2),KEY c1_c2_idx (c1, c2)
) ENGINE=INNODB;
INSERT INTO t VALUES (1,1,'a','b'), (1,2,'a','b'),(1,3,'a','c'), (1,4,'a','c'),(2,1,'a','d'), (3,1,'a','b'),(4,1,'d','b'), (4,2,'e','b'),(5,3,'f','c'), (5,4,'k','c'),(6,1,'y','d'), (6,2,'f','b');

执行下面的查询：

mysql> SELECT c1, MIN(c2) FROM t GROUP BY c1;
+----+---------+
| c1 | MIN(c2) |
+----+---------+
| a  | b       |
| d  | b       |
| e  | b       |
| f  | b       |
| k  | c       |
| y  | d       |
+----+---------+
6 rows in set (0.04 sec)

执行计划如下：

mysql> EXPLAIN SELECT c1, MIN(c2) FROM t GROUP BY c1;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t     | NULL       | range | c1_c2_idx     | c1_c2_idx | 256     | NULL |    7 |   100.00 | Using index for group-by |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set (0.03 sec)

Extra中出现了Using index for group-by表示group by操作使用到了Loose Index Scan这个特性

什么情况下使用到松散索引扫描？

在下面一些情况下是可以使用松散索引扫描的：
• 查询针对一个单表。
• GROUP BY包括索引的第1个连续部分(如果对于GROUP BY，查询有一个DISTINCT子句，则所有DISTINCT的属性指向索引开头)。
• 如果使用聚集函数，只能使用MIN()和MAX()，并且它们均指向相同的列。
• 索引的任何其它部分（除了那些来自查询中引用的GROUP BY）必须为常数(也就是说，必须按常量数量来引用它们)，但MIN()或MAX() 函数的参数例外。