MySQL---聚集函数的优化

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某个列之和

创建数据：
CREATE TABLE t_key(id INT PRIMARY KEY, k1 INT NOT NULL UNIQUE KEY,
k2 INT NULL, k3p1 INT, k3p2 INT, col INT NULL,
KEY k1_idx_uqi (k1),
KEY k2_idx (k2),
KEY k3_idx (k3p1, k3p2)
);


INSERT INTO t_key VALUES (1,1,1,1,1,1),(2,2,2,1,2,2),(3,3,3,1,3,3),(4,4,4,2,1,4),

(5,5,5,2,2,5),(6,6,6,2,3,6),(7,7,7,3,1,7),(8,8,8,3,3,8),(9,9,9,4,1,9);

一 条件列有无索引决定着是否能够优化

E1：条件列无索引，不可优化
EXPLAIN SELECT MIN(col), MAX(col) FROM t_key WHERE col>3;
 

E2：条件列有索引(此例是普通索引)，执行MIN和MAX，可优化

MySQL官方文档解释：
Select tables optimized away:
The query contained only aggregate functions (MIN(), MAX()) that were all resolved using an index, or COUNT(*) for MyISAM, and no GROUP BY clause. The optimizer determined that only one row should be returned.
补充说明：
这句话没有把所有情况说出来，对于InnoDB表COUNT(*)也可以得到“Select tables optimized away”，参见E10。

E3：条件列有索引(此例是普通索引)，执行COUNT和SUM，可优化

二 对聚集函数使用不同类型的索引，是否有差别（普通索引支持的情况下，通常主键和唯一会支持的更好）。E2和E3都是使用了普通的索引
E4：支持使用主键索引进行优化

E5：支持使用唯一索引进行优化

三 没有WHERE条件，支持对聚集函数的优化
E6:

四 利用局部索引键的优化
E7: 利用局部索引键的前缀键优化

E8: 利用局部索引键的非前缀键优化

五 COUNT(*)的优化
E9：条件列有索引(此例是普通索引)，执行COUNT(*)，可优化

说明：此例对于MyISAM表也使用。

E10：条件列有索引(此例是普通索引)，执行COUNT(*)，可优化
 

六 索引上存在NULL值
E11：在k2_idx索引所在的k2列上，增加空值。
INSERT INTO t_key VALUES (11,11,NULL,11,1,1),(12,12,2,11,2,2),(13,13,NULL,11,3,3);
  EXPLAIN SELECT SUM(k2),COUNT(k2),AVG(k2), MIN(k2), MAX(k2) FROM t_key;

E12：只求MIN和MAX，注意结果和上一个不同
  EXPLAIN SELECT MIN(k2), MAX(k2) FROM t_key;

E13：与此相似的一个例子如下：
MySQL> EXPLAIN SELECT MIN(k3p1), MAX(k3p1) FROM t_key;

E14：只求COUNT/SUM/AVG，可以使用索引优化
MySQL> EXPLAIN SELECT SUM(k2),COUNT(k2),AVG(k2) FROM t_key;

七 原理解析
相关的优化代码，位于opt_sum.cc文件中，主入口函数为opt_sum_query().
函数分3种情况进行判断。
7.1 情况一 函数是MIN/MAX
调用find_key_for_maxmin()检查是否有索引可用于优化。可用的情况如MySQL的注视所言：
  Given a table with a compound key on columns (a,b,c), the following
  types of queries are optimised (assuming the table handler supports
  the required methods)

  SELECT COUNT(*) FROM t1[,t2,t3,...]
  SELECT MIN(b) FROM t1 WHERE a=const
  SELECT MAX(c) FROM t1 WHERE a=const AND b=const
  SELECT MAX(b) FROM t1 WHERE a=const AND b<const
  SELECT MIN(b) FROM t1 WHERE a=const AND b>const
  SELECT MIN(b) FROM t1 WHERE a=const AND b BETWEEN const AND const
  SELECT MAX(b) FROM t1 WHERE a=const AND b BETWEEN const AND const
如果有索引可以用于优化，则可以在执行计划的Extra列得到“Select tables optimized away”，但请观察，凡是有此值的时候（如E2/E10/E12/E13），执行计划中type和key的值都是NULL，这表明，在这个阶段，已经根据索引直接求出的最值，以后不需要再扫描索引了。

敏感的同学这时就会问：
Q：如E6/E7/E11/E14/E15，似乎类似如下示例，执行计划使用了索引扫描：
E15：执行计划中明确注明使用了索引
MySQL> EXPLAIN SELECT MAX(k3p2) FROM t_key;

从执行计划看，确实使用了索引扫描了。
从代码的角度看，这是opt_sum_query()函数认定不使用索引优化之后，MySQL调用JOIN::adjust_access_methods()完成的一个补救措施，用“启发式优化规则”确定在类似情况下，使用索引完成优化。
所以，我们从执行计划上就可以看出不同，注意type/key/extra列上的值的差异。

7.2 情况二 COUNT(*)
情况二中包括了COUNT(*)，此处深入探测这种情况
跟踪代码，可看到如下调用关系，可推知一定是使用了索引（注意此表是InnoDB表），直接对COUNT(*)进行了求值。
JOIN::optimize()->opt_sum_query()->get_exact_record_count()->handler::ha_records()->ha_innobase::records()->row_scan_index_for_mysql()->row_search_for_mysql()
但请注意，SQL语句有个前提条件，一定是不带有WHERE条件才行，如下。
E16：
MySQL > EXPLAIN SELECT COUNT(*) FROM t_key;

附加说明：
此处MySQL还有个优化点，对于E16中的SQL，如果是EXPLAN，则不必求值，直接给出正确的计划就可以。
如果去除EXPLAIN，则需要对COUNT(*)直接求值（贴心的代码实现，减少执行过程和并发操作）。

E17：但如果是带有WHERE条件，则不能像上面一样进行优化。
MySQL > EXPLAIN SELECT COUNT(*) FROM t_key WHERE k1<5;

但是MySQL提供了范围优化的方式，利用索引进行了优化。类同情况一的E15。

7.3 情况三 函数为SUM/AVG
不被opt_sum_query()优化，但类似于情况一的E15和情况二的E17。
不再多述。
E18：
MySQL > EXPLAIN SELECT SUM(k1) FROM t_key;

八 其他情况
E19：带有ORDER BY，可支持利用索引进行优化
MySQL> EXPLAIN SELECT MAX(k3p1) FROM t_key ORDER BY k3p1;

E20: 带有ORDER BY，可支持利用索引进行优化
MySQL> set sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

MySQL> EXPLAIN SELECT MAX(k3p1), k3p1 FROM t_key ORDER BY k3p1;