MySQL数据库调优————GROUP BY及DISTINCT优化

GROUP BY

三种处理GROUP BY的方式

• 松散索引扫描（Loose Index Scan）
• 紧凑索引扫描（Tight Index Scan）
• 临时表（Temporary table）
  三种方式的性能一次递减

松散索引扫描

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

我们使用如下索引

执行SQL

select emp_no,min(salary)
from salaries
group by emp_no;

结果

当Extra出现Using index for group-by就说明使用了松散扫描。
上面的语句，在执行过程中一般情况下应该是查询出类似[person[i]，salary[j]]，[person[i]，salary[j+1]]，[person[i]，salary[j+2]]…[person[i+1]，salary[k]]，[person[i+1]，salary[k+1]]，[person[i+1]，salary[k+2]]…。然后将person[i]的进行计算得到最小的salary，再计算person[i+1]的最小salary，但是索引是有序的，查询出来的语句已经是先按person排序，再按salary排序，也就是说每个person的第一个salary就是最小的，中间的扫描所有每个person的salary并计算最小值的过程是可以省略的直接取每个person的第一个salary即可，这就是松散索引扫描，无需扫描所有的满足条件的索引。

使用松散索引扫描的条件

• 查询作用再单张表上
• GROUP BY指定的所有字段要符合最左前缀原则，且没有其他字段
  • 比如有索引index(c1，c2，c3)，且有GROUP BYc1，c2则可以使用松散索引扫描；但GROUP BY c2，c3、GROUP BY c1，c2，c4则不能使用
• 如果存在聚合函数，只支持MIN()/MAX()，并且如果同时使用了MIN()和MAX()，则必须作用再同一个字段。集合函数作用的字段必须再索引中，并且要紧跟GROUP BY所指定的字段
  • 比如有索引index(c1，c2，c3)，SELECT c1，c2 MIN(c3)，MAX(c3) FROM t GROUP BY c1，c2可以使用松散索引扫描
• 如果查询中存在除GROUP BY指定的列以外的其他部分，则必须以常量的形式出现
  • SELECT c1，c3 FROM t GROUP BY c1，c2；不能使用
  • SELECT c1，c3 FROM t WHERE c3 = 3 GROUP BY c1，c2；可以使用
• 索引必须是索引整个字段的值，不能是前缀索引

能使用松散索引扫描的SQL

假设有index(c1,c2,c3)作用再表t(c1,c2,c3,c4)上，下面这些语句都能使用松散索引扫描：

SELECT c1,c2 FROM t GROUP BY c1,c2;
SELECT DISTINCT c1,c2 FROM t;
SELECT c1,MIN(c2)FROM t GROUP BY c1;
SELECT c1,c2 FROM t WHERE c1 < const GROUP BY c1,c2;
SELECT MAX(c3),MIN(c3),c1,c2 FROM t WHERE c2 > const GROUP BY c1,c2;
SELECT c2 FROM t WHERE c1 < const GROUP BY c1,c2;
SELECT c1,c2 FROM t WHERE c3 = const GROUP BY c1,c2;

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

– 聚合函数不是MIN()或MAX()
SELECT c1,SUM(c2) from t GROUP BY c1;
– 不符合最左前缀原则
SELECT c1,c2 FROM t GROUP BY c2,c3;
– 查询了c3字段，但c3
字段上没有等值查询
SELECT c1,c3 FROM t GROUP BY c1,c2;

紧凑索引扫描

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

explain 
select emp_no,sum(salary)
from salaries
group by emp_no;

结果

紧凑索引扫描在Extra中是没有特别标识的。

临时表

• 在无法使用松散索引扫描以及紧凑索引扫描的请款下，MySQL将会读取需要的数据，并创建一张临时表，用临时表实现GROUP BY操作。

explain
select max(hire_date)
from employees
group by hire_date;

结果

一旦使用了临时表，在Extra中将会出现Using temporary。

GROUP BY的优化

• 如果GROUP BY使用了临时表，那么就需要想办法用上松散索引扫描或者紧凑索引扫描。

DISTINCT

DISTINCT优化

• DISTINCT实在GROUP BY操作之后，每组只取一条
• 和GROUP BY优化思路一致