一次Group By+Order By性能优化分析

The SUM() and AVG() functions return a DECIMAL value for exact-value arguments (integer or DECIMAL), and a DOUBLE value for approximate-value arguments (FLOAT or DOUBLE). (Before MySQL 5.0.3, SUM() and AVG() return DOUBLE for all numeric arguments.)

但是通过我们上面打印信息可以看到两个字段的长度加起来是19，而optimizer_trace里的tmp_table_info.reclength是20。通过其他实验也发现table->s->reclength的长度就是table->field数组里面所有字段的字段长度和再加1。

总结执行流程

1. 尝试在堆上使用memory的内存临时表来存放group by的数据，发现内存不够；
2. 创建一张临时表，临时表上有两个字段，aid和num字段（sum(pv) as num）；
3. 从索引idx_day_aid_pv中取出1行，插入临时表。插入规则是如果aid不存在则直接插入，如果存在，则把pv的值累加在num上；
4. 循环遍历索引idx_day_aid_pv上20181220~20181224之间的所有行，执行步骤3；
5. 对临时表根据num的值做优先队列排序；
6. 取出最后留在堆（优先队列的堆）里面的10行数据，作为结果集直接返回，不需要再回表；

补充说明优先队列排序执行步骤分析：

1. 在临时表（未排序）中取出前 10 行，把其中的num和aid作为10个元素构成一个小顶堆，也就是最小的 num 在堆顶。
2. 取下一行，根据 num 的值和堆顶值作比较，如果该字大于堆顶的值，则替换掉。然后将新的堆做堆排序。
3. 重复步骤2直到第 552203 行比较完成。

优化

方案1 使用 idx_aid_day_pv 索引

# Query_time: 4.406927  Lock_time: 0.000200 Rows_sent: 10  Rows_examined: 1337315  
SET timestamp=1552791804;  
select aid,sum(pv) as num from article_rank force index(idx_aid_day_pv) where day>=20181220 and day<=20181224 group by aid order by num desc limit 10;  

扫描行数都是1337315，为什么执行消耗的时间上快了12倍呢？

索引示例

为了便于理解，，同样我也按照索引的规则先模拟idx_aid_day_pv索引的一小部分数据

group by 不需要临时表的情况

为什么性能上比 SQL1 高了，很多呢，原因之一是idx_aid_day_pv索引上aid是确定有序的，那么执行group by的时候，则不会创建临时表，排序的时候才需要临时表。如果印证这一点呢，我们通过下面的执行计划就能看到

使用idx_day_aid_pv索引的效果：

mysql> explain select aid,sum(pv) as num from article_rank force index(idx_day_aid_pv) where day>=20181220 and day<=20181224 group by aid order by null limit 10; 
 
+----+-------------+--------------+------------+-------+-------------------------------+----------------+---------+------+--------+----------+-------------------------------------------+ 
 
| id | select_type | table        | partitions | type  | possible_keys                 | key            | key_len | ref  | rows   | filtered | Extra                                     | 
 
+----+-------------+--------------+------------+-------+-------------------------------+----------------+---------+------+--------+----------+-------------------------------------------+ 
 
|  1 | SIMPLE      | article_rank | NULL       | range | idx_day_aid_pv,idx_aid_day_pv | idx_day_aid_pv | 4       | NULL | 404607 |   100.00 | Using where; Using index; Using temporary | 
 
+----+-------------+--------------+------------+-------+-------------------------------+----------------+---------+------+--------+----------+-------------------------------------------+  

注意我上面使用了order by null表示强制对group by的结果不做排序。如果不加order by null，上面的 sql 则会出现Using filesort

（编辑：PHP编程网 - 黄冈站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!