Mysql优化-group by 和 distinct

数据库目录-CSDN博客

1. 核心

1. 实质是先排序后进行分组，遵照索引建的最佳左前缀
2. where高于having，能写在where限定的条件就不要去having限定了。
3. group by 使用索引的原则几乎跟order by一致 ，唯一区别是group by 即使没有过滤条件用到索引，也可以直接使用索引。

explain select c2,c3 from test03 where c1='a1' and c4='a4' group by c2,c3;
explain select c2,c3 from test03 where c1='a1' and c4='a4' group by c3,c2;

2. 松散索引扫描(Loose Index Scan)

无需扫描满足条件的所有索引键即可返回结果

1. 查询作用在单张表上
2. GROUP指定的所有字段要符合最左前缀原则，且没有其他字段，比如有索引index(c1,c2,c3), 如果GROUP BY c1,c2则可以使用松散索扫描；但GROUP BY c2,c3、GROUP BY c1,c2,c4则不能使用。
3. 如果存在聚合函数，只支持MIN/MAX,并且如果同时使用了MIN和MAX,则必须作用在同一个字段。聚合函数作用的字段必须在索引中并且要紧跟GROUP BY所指定的字段，

索引index(c1,c2,c3)
SELECT c1,c2,MIN(c3),MAX(c3)FROM t1 GROUP BY c1,c2

如果查询中存在除GROUP BY指定的列以外的其他部分，则必须以常量的形式出现
SELECT c1,c3 FROM t1 GROUP BY c1,c2:不能使用
SELECT c1,c3 FROM t1 WHERE c3=3 GROUP BY c1,c2: 可以

索引必须索引整个字段的值，不能是前缀索引

特定聚合函数用法能用上松散索引扫描的条件

AVG(DISTINCT)、SUM(DISTINCT)、COUNT(DISTINCT),其中

AVG(DISTINCT)、SUM(DISTINCT)可接受单个参数；COUNT(DISTINCT)可接受多个参数

查询中必须不存在GROUP BY或DISTINCT语句
SELECT COUNT(DISTINCT c1),SUM(DISTINCT c1)FROM t1
SELECT COUNT(DISTINCT c1,c2),COUNT(DISTINCT c2,c1)FROM t1;

3. 可以使用索引示例

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;

4. 不能使用索引示例

聚合函数不是MIN()或MAX()
SELECT c1,SUM(c2)FROM t1 GROUP BY c1;
不符合最左前缀原则
SELECT c1,c2 FROM t1 GROUP BY c2,c3;

查询了c3字段，但是c3字段上没有等值查询
SELECT c1,c3 FROM t1 GROUP BY c1,c2;
-- 改成 则可以使用
SELECT c1,c3 FROM t1 WHERE c3=const GROUP BY c1,c2;

5. 紧凑索引扫描(Tight Index Scan)

1. 需要扫描满足条件的所有索引键才能返回结果
2. 性能一般比松散索引扫描差，但一般都可接受

6. 临时表(Temporary table)

1. 紧凑索引扫描也没有办法使用的话MySQL将会读取需要的数据
2. 并创建一个临时表，用临时表实现GROUP BY操作