GROUP BY语句优化与DISTINCT语句优化
GROUP BY优化处理方式
- 松散索引扫描(Loose Index Scan)
- 紧凑索引扫描(Tight Index Scan)
- 临时表(Temporary table)
- 性能依次递减
松散索引扫描
- 无需扫描满足条件的所有索引键即可返回结果
例如:
-- 先创建组合索引作用emp_no和salary字段
ALTER TABLE salaries ADD INDEX salaries_emp_no_salary_index ( emp_no, salary );
-- 查询每个员工拿到过的最少的工资是多少【比惨大会】
/**
* 执行前先分析这条sql执行:
* 因为有这个 [emp_no, salary] 组合索引,所以里面的数据先按emp_no排序再按salary排序,大概是这样的
* [10001, 50000]
* [10001, 51000]
* ...
* [10002, 30000]
* [10002, 32000]
* ...
* 1.先扫描emp_no = 10001的数据,并计算出最小的salary是多少,[10001, 50000]
* 2.扫描emp_no = 10002的数据,并计算出最小的salary是多少,[10002, 30000]
* ...
* n.遍历出每个员工的最小薪资,并返回
*
* ====================================
*
* 如果不作任何处理,MySQL是以上步骤操作的
* 但是这里因为加了组合索引作用emp_no和salary字段,先按emp_no排序再按salary排序,而且是GROUP BY emp_no,所以上面的步骤可以改进
* 改进(松散索引扫描)
* 1.先扫描emp_no = 10001的数据,取出第一条 => 就是这个员工工资最小的数据
* 2.直接跳过所有emp_no = 10001的数据,继续扫描emp_no = 10002的数据,取出第一条
* ....
* n.以此类推,取出每个员工的最小薪资,并返回
*/
SELECT emp_no, min( salary ) FROM salaries GROUP BY emp_no;
-- 可以看到Extra里面显示Using index for group-by,表示使用了松散索引扫描
EXPLAIN SELECT emp_no, min( salary ) FROM salaries GROUP BY emp_no;
使用松散索引扫描的条件
- 查询作用在单张表上
- GROUP指定的所有字段要符合最左前缀原则,且没有其他字段
- 比如有索引index(c1,c2,c3),如果GROUP BY c1,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 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可以使用松散索引扫描
- 索引必须索引整个字段的值,不能是前缀索引
- 比如有字段c1 VARCHAR(20),但如果该字段使用的是前缀索引index(c1(10))而不是index(c1),无法使用松散索引扫描
能使用松散索引扫描的SQL一览
假设有index(c1,c2,c3)作用在表t1(c1,c2,c3,c4)上,下面这些SQL都能使用松散索引扫描
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;
不能使用松散索引扫描的SQL一览
假设有index(c1,c2,c3)作用在表t1(c1,c2,c3,c4)上,下面这些SQL不能使用松散索引扫描
-- 聚合函数不是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 WHERE c3 = const GROUP BY c1, c2;则可以使用
SELECT c1, c3 FROM t1 GROUP BY c1, c2;
特定聚合函数用法能用上松散索引扫描的条件
- AVG(DISTINCT)、SUM(DISTINCT)、COUNT(DISTINCT),其中AVG(DISTINCT)、SUM(DISTINCT)可接受单个参数;而COUNT(DISTINCT)可接受多个参数
- 查询中必须不存在GROUP BY或DISTINCT语句
- 满足前面所有使用松散索引扫描的条件
能使用松散索引扫描的特定聚合函数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;
紧凑索引扫描
- 需要扫描满足条件的所有索引键才能返回结果
- 性能一般比松散索引扫描差,但一般都可以接受
例子:
-- 紧凑索引扫描(EXPLAIN-Extra没有明显标识)
EXPLAIN SELECT emp_no, sum( salary ) FROM salaries GROUP BY emp_no;
临时表
- 紧凑索引扫描也没有办法使用的话,MySQL将会读取需要的数据,并创建一个临时表,用临时表实现GROUP BY操作
例子:
-- 可以看到Extra里面显示Using temporary,表示使用了临时表
EXPLAIN SELECT max( hire_date ) FROM employees GROUP BY hire_date;
如何优化GROUP BY语句?
- 如果GROUP BY使用了临时表,想办法用上松散索引扫描或者紧凑索引扫描
DISTINCT优化
- DISTINCT是在GROUP BY操作之后,每组只取1条
- 和GROUP BY优化思路一样
总结
- 处理GROUP BY的三种方式
- 松散索引扫描,性能最好,explain中Extra字段会有Using index for group-by
- 紧凑索引扫描,性能第二,explain中无明显标识
- 临时表,性能最差,explain中会有Using temporary
- GROUP BY和DISTINCT语句优化措施:避免临时表,使用松散/紧凑索引扫描