GROUP BY Optimization
常规的匹配group by(分组)操作子句是扫整表并且创建包含连续的分组行的临时表, 利用临时表得到group数据,运用appregate functions(聚合函数)(有的话)。有些情况下,MYSQL 可能更好的通过使用Index access来避免临时表的创建。
利用indexes access避免临时表最重要的先决条件是:所有的group by子句中的列属性必须来至同一个index(联合),并且index的存储以它的关键字顺序(BTree index,hash index不是顺序的).是否用index access而不是creation of temporary table也依赖查询语句引用的index部分属性,指定的条件部分(where),和select子句中的聚合函数。
Loose Index Scan(松散索引扫描)
最有效率处理group by方法是当直接使用index获得检索分组列属性。此时MYSQL会利用index关键字有序的属性(BTREE),该属性确保在一个index中查找组信息而不用考虑该index中所有的关键字(是否满足where子句条件)。该access 方法只考虑一个index中一部分关键字,所以它被成为loose index scan.当查询语句中没有where子句时,loose index scan读取
需要的key,比读全部key要小。如果where子句包含范围条件,loose index scan查找每个组中第一个key满足range条件,再次读取尽可能少的key数量,需要满足以下几点:
1:该查询只覆盖一个表,
2:group by子句中的列属性满足最左前缀原则,并且没有别的非index中的列属性。(instinct关键字也通用适用),e.g. 表t1 有一个index(c1,c2,c3),如果查询含有group by c1,c2,则loose index sacn是合适的,但group by c2,c3则不合适(最左前缀),group by c1,c2,c4也不合适(c4不为该index中的列属性)。
3:select子句中的列属性只能包含min(),max()聚合函数,并且它们都引用group by中一个列属性。
4:查询语句中的index关键字(except group by子句的那些index关键字)必须为常量(意味着,他们必须通过 = constra的形式被引用),除非是min(),max();
5:index中所有列关键字,列值必须完整索引,而不是一个前缀索引,e.g. c1 varchar(20), index (c1(10)),该索引不会被loose index scan使用。
查询语句使用loose index scan时, explain输出Using index for group-by信息;
假设在table t1(c1,c2,c3,c4)上存在一个index idx(c1,c2,c3), loose index scan方法在一下几种情况下会被使用:
SELECT c1, c2 FROM t1 GROUP BYc1, c2;SELECT DISTINCT c1, c2 FROMt1;SELECT c1, MIN(c2) FROM t1 GROUP BYc1;SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BYc1, c2;SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BYc1, c2;SELECT c2 FROM t1 WHERE c1 < const GROUP BYc1, c2;SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;
一下 查询不会使用loose index scan:
1:select中出现的except group by 列的index列关键字,只能以 = 或 min(),max()出现;
SELECT c1, SUM(c2) FROM t1 GROUP BY c1;
2:最左前缀
SELECT c1, c2 FROM t1 GROUP BY c2, c3;
3:同1解释
SELECT c1, c3 FROM t1 GROUP BY c1, c2;
松散索引扫描也可以使用在其他形式的聚合函数: AVG(DISTINCT),SUM(DISTINCT)(单列),COUNT(DISTINCT)(多列)支持。条件:
1: 没有group by或者 distinct子句在查询中
2:前面提到的限制依然适用
适用:
SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROMt1;SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;
不适用:
SELECT DISTINCT COUNT(DISTINCT c1) FROMt1;SELECT COUNT(DISTINCT c1) FROM t1 GROUP BY c1;
Tight Index Scan(紧凑索引扫描)
紧凑索引扫描要不是全索引扫描要不是区间索引扫描。
当松散索引扫描不能使用的时候,依然可以避免创建临时表。如果where子句中有range条件,那么只读取满足条件的key。
否则执行全索引扫描。因为算法读取所有满足range条件的key,或者如果没有条件扫描整个索引,我们称为紧凑索引扫描。
使用紧凑索引扫描,分组操作只有在所有key找完之后执行。
算法在使用等式比较所有查询引用的列生效,只有等式常量能够填上查询key的间隙,才有可能形成索引的前缀,使用索引前缀来进行索引查找。
这样mysql可以避免额外的排序操作直接可以从索引中顺序获取。假设index(c1,c2,c3)在表table(c1,c2,c3,c4),下面的查询不支持松散索引扫描,但是支持紧凑索引扫描
1:虽然有空隙但是已经被where c2=’a’填补
SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;
2: 虽然group by不是和索引第一位匹配,但是where中提供了和常量的比较
SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;
mysql> desc select distinct first_name fromemployees ;+----+-------------+-----------+-------+---------------+-----------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+-----------+---------+------+------+--------------------------+
| 1 | SIMPLE | employees | range | idx_fn_ln | idx_fn_ln | 16 | NULL | 2495 | Using index for group-by |
+----+-------------+-----------+-------+---------------+-----------+---------+------+------+--------------------------+
1 row in set (0.00sec)
mysql> desc select first_name,min(last_name) from employees group by first_name;+----+-------------+-----------+-------+---------------+-----------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+-----------+---------+------+------+--------------------------+
| 1 | SIMPLE | employees | range | idx_fn_ln | idx_fn_ln | 16 | NULL | 2495 | Using index for group-by |
+----+-------------+-----------+-------+---------------+-----------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
mysql> desc select first_name, last_name from employees group bylast_name;+----+-------------+-----------+-------+---------------+-----------+---------+------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+-----------+---------+------+--------+----------------------------------------------+
| 1 | SIMPLE | employees | index | idx_fn_ln | idx_fn_ln | 34 | NULL | 299290 | Using index; Using temporary; Using filesort |
+----+-------------+-----------+-------+---------------+-----------+---------+------+--------+----------------------------------------------+
1 row in set (0.01 sec) // mostleftprefix ,创建临时表,filesort
mysql> desc select first_name,last_name from employees where first_name = "Mello" group bylast_name;+----+-------------+-----------+------+---------------+-----------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+--------------------------+
| 1 | SIMPLE | employees | ref | idx_fn_ln | idx_fn_ln | 16 | const | 1 | Using where; Using index |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+--------------------------+
1 row in set (0.00 sec) // 没有出现using temporary,filesort = > tight index scan