数据库监控与调优【十六】—— GROUP BY语句优化与DISTINCT语句优化

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语句优化措施：避免临时表，使用松散/紧凑索引扫描