数据库Group by语句调优和Distinct语句调优

数据库Group by语句调优

松散索引扫描(Loose Index Scan)：性能最好 explain中会有Using index for group-by

紧凑索引扫描(Tight Index Scan)： 性能第二 explain中无明显标识

临时表(Temporary table)： 性能最差 explain中会有Using temporary

性能依次递减

优化措施： 避免临时表，使用松散/紧凑索引扫描。

1、松散索引扫描

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

什么是松散索引？

查询每个员工发到的最小工资？

SELECT emp_no, MIN(salary)
FROM salaries
GROUP BY emp_no

 salaries表的索引为emp_no与salary

 分析这条SQL执行情况　　

执行：

EXPLAIN SELECT emp_no, MIN(salary)
FROM salaries
GROUP BY emp_no

 可以发现type为range，Extra为Using index for group-by

使用松散索引扫描的条件

1、条件1

1) 查询作用在单张表上

2) GROUP指定的所有字段要符合最左前缀原则，且没有其他字段。

 比如索引为index(c1,c2,c3), 如果group by  c1, c2则可以使用松散索引扫描； 但是group by c2, c2; group by c1, c2, c4 则不能使用。

 2、条件2

如果存在聚合函数，只支持MIN()/MAX(),并且如果同时使用了MIN()和MAX(),则必须作用在同一个字段。聚合函数作用的字段必须在索引中，并且要紧跟GROUP BY所指定的字段。

 比如索引为index(c1,c2,c3), SELECT c1，c2，MIN(c3), MAX(c3) from t1 group by c1,c2 可以使用松散索引扫描。

3、条件3

如果查询中存在除GROUP BY指定的列以外的其他部分，则必须以常量的形式出现。

select c1，c3, from t1 group by c1，c2 ：不能使用

select c1，c3, from t1 where c3 = 3  group by c1，c2 ：可以使用

条件4

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

比如有字段c1 varchar(20), 但是如果该字段使用的是前缀索引index(c1(10)) 前10个字符，而不是index(c1), 无法使用松散索引扫描

 能使用松散索引扫描的SQL一览

假设index(c1,c2,c3) 作用在表t1(c1,c2,c3,c4), 下面这些SQL都能使用松散索引扫描

不能使用松散索引扫描的SQL

假设index(c1,c2,c3) 作用在表t1(c1,c2,c3,c4), 下面这些SQL不能使用松散索引扫描 

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

 假设index(c1,c2,c3) 作用在表t1(c1,c2,c3,c4), 下面这些SQL都能使用松散索引扫描 

 select count(distinct c1), sum(distinct c1) from t1;

select count(distinct c1, c2), count(distinct c2, c1) from t1;

2、紧凑索引扫描

需要扫描满足条件的所有索引键才能返回结果

性能一般比松散索引扫描差，但一般都可接受。

EXPLAIN SELECT emp_no, SUM(salary)
FROM salaries
GROUP BY emp_no

聚合函数时sum，肯定无法使用松散索引扫描，退而求其次，使用紧凑索引扫描。执行后结果如下图所示：

 type是index，发生了全索引扫描。Extra为Using index，表示使用了覆盖索引。

3、临时表

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

-- employees 表没有索引
EXPLAIN
SELECT MAX(hire_date)
FROM employees
GROUP BY hire_date

 

 Extra： Using temporary 表示使用了临时表。

4、如何优化GROUP BY 语句

如果GROUP BY使用了临时表，想办法用上松散索引扫描或紧凑索引扫描。（优化方案是创建索引）

5、DISTINCT优化

distinct是在group by操作之后，每组只取1条

和group by优化思路一样