MySQL---聚集函数的优化

待我称王封你为后i 2024-02-18 21:21 69阅读 0赞

<table> 
 <tbody> 
  <tr> 
   <th>函数</th> 
   <th>说明</th> 
  </tr> 
 </tbody> 
 <tbody> 
  <tr> 
   <td>AVG()</td> 
   <td>返回某列的平均值</td> 
  </tr> 
  <tr> 
   <td>COUNT()</td> 
   <td>返回某列的行数</td> 
  </tr> 
  <tr> 
   <td>MAX()</td> 
   <td>返回某列的最大值</td> 
  </tr> 
  <tr> 
   <td>MIN()</td> 
   <td>返回某列的最小值</td> 
  </tr> 
  <tr> 
   <td>SUM()</td> 
   <td>返回某个列之和</td> 
  </tr> 
 </tbody> 
</table>

创建数据：  
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;  
![70][]

**E2：条件列有索引(此例是普通索引)，执行MIN和MAX，可优化**  
![70 1][]  
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，可优化**  
![70 2][]

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

**三 没有WHERE条件，支持对聚集函数的优化  
E6:**  
![70 5][]

**四 利用局部索引键的优化  
E7: 利用局部索引键的前缀键优化**  
![70 6][]  
**E8: 利用局部索引键的非前缀键优化**  
![70 7][]

**五 COUNT(\*)的优化  
E9：条件列有索引(此例是普通索引)，执行COUNT(\*)，可优化**  
![70 8][]  
说明：此例对于MyISAM表也使用。

**E10：条件列有索引(此例是普通索引)，执行COUNT(\*)，可优化**  
![70 9][]

**六 索引上存在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;  
![70 10][]

**E12：只求MIN和MAX，注意结果和上一个不同**  
EXPLAIN SELECT MIN(k2), MAX(k2) FROM t\_key;  
![70 11][]  
**E13：与此相似的一个例子如下：**  
MySQL> EXPLAIN SELECT MIN(k3p1), MAX(k3p1) FROM t\_key;  
![70 12][]

**E14：只求COUNT/SUM/AVG，可以使用索引优化**  
MySQL> EXPLAIN SELECT SUM(k2),COUNT(k2),AVG(k2) FROM t\_key;  
![70 13][]

**七 原理解析**  
相关的优化代码，位于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;  
![70 14][]  
从执行计划看，确实使用了索引扫描了。  
从代码的角度看，这是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;  
![70 15][]  
附加说明：  
此处MySQL还有个优化点，对于E16中的SQL，如果是EXPLAN，则不必求值，直接给出正确的计划就可以。  
如果去除EXPLAIN，则需要对COUNT(\*)直接求值（贴心的代码实现，减少执行过程和并发操作）。

**E17：但如果是带有WHERE条件，则不能像上面一样进行优化。**  
MySQL > EXPLAIN SELECT COUNT(\*) FROM t\_key WHERE k1<5;  
![70 16][]  
但是MySQL提供了范围优化的方式，利用索引进行了优化。类同情况一的E15。

**7.3 情况三 函数为SUM/AVG**  
不被opt\_sum\_query()优化，但类似于情况一的E15和情况二的E17。  
不再多述。  
E18：  
MySQL > EXPLAIN SELECT SUM(k1) FROM t\_key;  
![70 17][]

**八 其他情况  
E19：带有ORDER BY，可支持利用索引进行优化**  
MySQL> EXPLAIN SELECT MAX(k3p1) FROM t\_key ORDER BY k3p1;  
![70 18][]

**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;  
![70 19][]

[70]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/6aa969962ed14e21ba1a4833c1a0f18d.png
[70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/6832a8a04d2b45959ad0117564606e3a.png
[70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/f4266cba261244e8b1fe8cfbc8a4d15b.png
[70 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/7619e84d9bde4c39a1a1890e0f714f69.png
[70 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/bcdae1c1845e417d9cf35f737b3b8d70.png
[70 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/c179f4e12f7e45fd8fe359d44bd883c2.png
[70 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/d4f92070bfd945bfa451bb1098112a84.png
[70 7]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/73bb57a679f04819adef451714be0cfc.png
[70 8]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/0242a97ce16845d68af917250fad0047.png
[70 9]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/6d585ee544ec44e1a62cf2037cf9ec42.png
[70 10]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/241a3607032a4dc790125a669adeba75.png
[70 11]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/c2c1c09838cd43e49b8c441caf0b86f0.png
[70 12]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/ae1410fa183f4f179bcd83ac81f3f8f2.png
[70 13]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/3ff56c9c85654b80a2fa4fd8fb9e931e.png
[70 14]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/4747bcd3d0ae46b29c39b41d01fb6486.png
[70 15]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/9dc9dd73a3ba4b6c90c1fe31e688697f.png
[70 16]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/ac1a96d7c11340039198089441b27360.png
[70 17]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/6213e3ec49fc4d3d9fe2956526bf4be3.png
[70 18]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/976acaa23e5146c1ad024ae23cfc0375.png
[70 19]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/a4727c413887401ca91a145c33e1ab6c.png