一次 group by + order by 性能优化分析

原文：我的个人博客 https://mengkang.net/1302.html 工作了两三年，技术停滞不前，迷茫没有方向，不如看下我的直播：

PHP 进阶之路 （金三银四跳槽必考，一般人我不告诉他）。

最近通过一个日志表做排行的时候发现特别卡，最后问题得到了解决，梳理一些索引和 MySQL 执行过程的经验，但是最后还是有 5 个谜题没解开 ，希望大家帮忙解答下。

主要包含如下知识点：

• 用数据说话证明慢日志的扫描行数到底是如何统计出来的

• 从 group by 执行原理找出优化方案

• 排序的实现细节

• gdb 源码调试

背景

需要分别统计本月、本周被访问的文章的 TOP10。日志表如下：

CREATE TABLE `article_rank` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `aid` int(11) unsigned NOT NULL,
  `pv` int(11) unsigned NOT NULL DEFAULT '1',
  `day` int(11) NOT NULL COMMENT '日期 例如 20171016',
  PRIMARY KEY (`id`),
  KEY `idx_day_aid_pv` (`day`,`aid`,`pv`),
  KEY 




    
`idx_aid_day_pv` (`aid`,`day`,`pv`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

准备工作

为了能够清晰的验证自己的一些猜想，在虚拟机里安装了一个 debug 版的 mysql，然后开启了慢日志收集，用于统计扫描行数。

安装

• 下载源码

• 编译安装

• 创建 mysql 用户

• 初始化数据库

• 初始化 mysql 配置文件

• 修改密码

如果你兴趣，具体可以参考我的博客，一步步安装 https://mengkang.net/1335.html。

开启慢日志

编辑配置文件，在 [ mysqld ] 块下添加：

slow_query_log=1
slow_query_log_file=xxx
long_query_time=0
log_queries_not_using_indexes=1

性能分析

发现问题

假如我需要查询 2018 - 12 - 20 ~ 2018 - 12 - 24 这 5 天浏览量最大的 10 篇文章的 sql 如下，首先使用 explain 看下分析结果：

mysql




    
> explain select aid,sum(pv) as num from article_rank where day>=20181220 and day<=20181224 group by aid order by num desc 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; Using filesort |
+----+-------------+--------------+------------+-------+-------------------------------+----------------+---------+------+--------+----------+-----------------------------------------------------------+

系统默认会走的索引是 idx_day_aid_pv ，根据 Extra 信息我们可以看到，使用 idx_day_aid_pv 索引的时候，会走覆盖索引，但是会使用临时表，会有排序。

我们查看下慢日志里的记录信息：

# Time: 2019-03-17T03:02:27.984091Z
# User@Host: root[root] @ localhost []  Id:     6
# Query_time: 56.959484  Lock_time: 0.000195 Rows_sent: 10  Rows_examined: 1337315
SET timestamp=1552791747;
select aid,sum(pv) as num from article_rank where day>=20181220 and day<=20181224 group by aid order by num desc limit 10;

为什么扫描行数是 1337315

我们查询两个数据，一个是满足条件的行数，一个是 group by 统计之后的行数。

mysql>




    
 select count(*) from article_rank where day>=20181220 and day<=20181224;
+----------+
| count(*) |
+----------+
|   785102 |
+----------+

mysql> select count(distinct aid) from article_rank where day>=20181220 and day<=20181224;
+---------------------+
| count(distinct aid) |
+---------------------+
|              552203 |
+---------------------+

发现：满足条件的总行数（785102） + group by 之后的总行数（552203）+ limit 的值 = 慢日志里统计的 Rows_examined 。

要解答这个问题，就必须搞清楚上面这个 sql 到底分别都是如何运行的。

执行流程分析

索引示例

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

day      | aid      | pv      | id
-------- | -------- | ------- | -------  
20181220 | 1        | 23      | 1234  
20181220 | 3        | 2       | 1231
20181220 | 4        | 1       | 1212  
20181220 | 7        | 2       | 1221 




    
20181221 | 1        | 5       | 1257
20181221 | 10       | 1       | 1251 
20181221 | 11       | 8       | 1258 

因为索引 idx_day_aid_pv 最左列是 day ，所以当我们需要查找 20181220 ~ 20181224 之间的文章的 pv 总和的时候，我们需要遍历 20181220 ~ 20181224 这段数据的索引。

查看 optimizer trace 信息

# 开启 optimizer_trace
set optimizer_trace='enabled=on';
# 执行 sql 
select aid,sum(pv) as num from article_rank where day




    
>=20181220 and day<=20181224 group by aid order by num desc limit 10;
# 查看 trace 信息
select trace from `information_schema`.`optimizer_trace`\G;

摘取里面最后的执行结果如下：

{
  "join_execution": {
    "select#": 1,
    "steps": [
      {
        "creating_tmp_table": {
          "tmp_table_info": {
            "table": "intermediate_tmp_table",
            "row_length": 20,
            




    
"key_length": 4,
            "unique_constraint": false,
            "location": "memory (heap)",
            "row_limit_estimate": 838860
          }
        }
      },
      {
        "converting_tmp_table_to_ondisk": {
          "cause": "memory_table_size_exceeded",
          "tmp_table_info": {
            "table": "intermediate_tmp_table",
            "row_length": 20,
            "key_length": 4,
            




    
"unique_constraint": false,
            "location": "disk (InnoDB)",
            "record_format": "fixed"
          }
        }
      },
      {
        "filesort_information": [
          {
            "direction": "desc",
            "table": "intermediate_tmp_table",
            "field": "num"
          }
        ],
        "filesort_priority_queue_optimization": {
          "limit":




    
 10,
          "rows_estimate": 1057,
          "row_size": 36,
          "memory_available": 262144,
          "chosen": true
        },
        "filesort_execution": [
        ],
        "filesort_summary": {
          "rows": 11,
          "examined_rows": 552203,
          "number_of_tmp_files": 0,
          "sort_buffer_size": 488,
          "sort_mode": ""




    
        }
      }
    ]
  }
}

分析临时表字段

mysql gdb 调试更多细节：https://mengkang.net/1336.html。

通过 gdb 调试确认临时表上的字段是 aid 和 num ：

Breakpoint 1, trace_tmp_table (trace=0x7eff94003088, table=0x7eff94937200) at /root/newdb/mysql-server/sql/sql_tmp_table.cc:2306
warning: Source file is more recent than executable.
2306      trace_tmp.add("row_length",table->s->reclength).
(gdb)




    
 p table->s->reclength
$1 = 20
(gdb) p table->s->fields
$2 = 2
(gdb) p (*(table->field+0))->field_name
$3 = 0x7eff94010b0c "aid"
(gdb) p (*(table->field+1))->field_name
$4 = 0x7eff94007518 "num"
(gdb) p (*(table->field+0))->row_pack_length()
$5 = 4
(gdb) p (*(




    
table->field+1))->row_pack_length()
$6 = 15
(gdb) p (*(table->field+0))->type()
$7 = MYSQL_TYPE_LONG
(gdb) p (*(table->field+1))->type()
$8 = MYSQL_TYPE_NEWDECIMAL
(gdb)

通过上面的打印，确认了字段类型，一个 aid 是 MYSQL_TYPE_LONG ，占 4 字节， num 是 MYSQL_TYPE_NEWDECIMAL ，占 15 字节。

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 索引的一小部分数据：

aid      | day      | pv      | id
-------- | -------- | ------- | -------  
1        | 20181220 | 23      | 1234
1        |




    
 20181221 | 5       | 1257
3        | 20181220 | 2       | 1231
3        | 20181222 | 22      | 1331
3        | 20181224 | 13      | 1431
4        | 20181220 | 1       | 1212  
7        | 20181220 | 2       | 1221 
10       | 20181221 | 1       | 1251 
11




    
       | 20181221 | 8       | 1258 

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 。

使用 idx_aid_day_pv 索引的效果：

mysql> explain 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 null limit 10;
+----+-------------+--------------+------------+-------+-------------------------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table        | partitions | type  | possible_keys                 | key            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------------+------------+-------+-------------------------------+----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | article_rank | NULL       | index | idx_day_aid_pv,idx_aid_day_pv | idx_aid_day_pv | 12      | NULL |   10 |    11.11 | Using where;




    
 Using index |
+----+-------------+--------------+------------+-------+-------------------------------+----------------+---------+------+------+----------+--------------------------+

查看 optimizer trace 信息

# 开启optimizer_trace
set optimizer_trace='enabled=on';
# 执行 sql 
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;
# 查看 trace 信息
select trace from `information_schema`.`optimizer_trace`\G;

摘取里面最后的执行结果如下：

{
  "join_execution": {
    "select#": 1,
    




    
"steps": [
      {
        "creating_tmp_table": {
          "tmp_table_info": {
            "table": "intermediate_tmp_table",
            "row_length": 20,
            "key_length": 0,
            "unique_constraint": false,
            "location": "memory (heap)",
            "row_limit_estimate": 838860
          }
        }
      },
      {
        "filesort_information": [
          {
            "direction": "desc",
            "table": "intermediate_tmp_table",
            "field": "num"
          }
        ],
        "filesort_priority_queue_optimization": {
          "limit": 10,
          "rows_estimate": 552213,
          "row_size": 24,
          "memory_available": 262144,
          "chosen": true
        },