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

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

 

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

主要包含如下知识点

  • 用数据说话证明慢日志的扫描行数到底是如何统计出来的
  • 从 group by 执行原理找出优化方案
  • 排序的实现细节
  • gdb 源码调试

背景

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

  1. CREATE TABLE `article_rank` (  
  2.   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,  
  3.   `aid` int(11) unsigned NOT NULL,  
  4.   `pv` int(11) unsigned NOT NULL DEFAULT '1',  
  5.   `day` int(11) NOT NULL COMMENT '日期 例如 20171016',  
  6.   PRIMARY KEY (`id`),  
  7.   KEY `idx_day_aid_pv` (`day`,`aid`,`pv`),  
  8.   KEY `idx_aid_day_pv` (`aid`,`day`,`pv`)  
  9. ENGINE=InnoDB DEFAULT CHARSET=utf8  

准备工作

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

安装

  • 下载源码
  • 编译安装
  • 创建 mysql 用户
  • 初始化数据库
  • 初始化 mysql 配置文件
  • 修改密码

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

开启慢日志

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

  1. slow_query_log=1  
  2. slow_query_log_file=xxx  
  3. long_query_time=0  
  4. log_queries_not_using_indexes=1  

性能分析

发现问题

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

  1. 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;  
  2. +----+-------------+--------------+------------+-------+-------------------------------+----------------+---------+------+--------+----------+-----------------------------------------------------------+ 
  3.  
  4. | id | select_type | table        | partitions | type  | possible_keys                 | key            | key_len | ref  | rows   | filtered | Extra                                                | 
  5.  
  6. +----+-------------+--------------+------------+-------+-------------------------------+----------------+---------+------+--------+----------+-----------------------------------------------------------+ 
  7.  
  8. |  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 | 
  9.  
  10. +----+-------------+--------------+------------+-------+-------------------------------+----------------+---------+------+--------+----------+-----------------------------------------------------------+ 

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

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

  1. # Time: 2019-03-17T03:02:27.984091Z  
  2. # User@Host: root[root] @ localhost []  Id:     6  
  3. # Query_time: 56.959484  Lock_time: 0.000195 Rows_sent: 10  Rows_examined: 1337315  
  4. SET timestamp=1552791747 
  5. 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统计之后的行数。 

  1. mysql> select count(*) from article_rank where day>=20181220 and day<=20181224;  
  2. +----------+  
  3. | count(*) |  
  4. +----------+  
  5. |   785102 |  
  6. +----------+  
  7. mysql> select count(distinct aid) from article_rank where day>=20181220 and day<=20181224;  
  8. +---------------------+  
  9. | count(distinct aid) |  
  10. +---------------------+  
  11. |              552203 |  
  12. +---------------------+  

发现满足条件的总行数(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这段数据的索引。 

  1. 查看 optimizer trace 信息  
  2. # 开启 optimizer_trace  
  3. set optimizer_trace='enabled=on' 
  4. # 执行 sql   
  5. select aid,sum(pv) as num from article_rank where day>=20181220 and day<=20181224 group by aid order by num desc limit 10;  
  6. # 查看 trace 信息  
  7. select trace from `information_schema`.`optimizer_trace`\G;  

摘取里面的执行结果如下 

  1.  
  2.   "join_execution": {  
  3.     "select#": 1,  
  4.     "steps": [  
  5.       {  
  6.         "creating_tmp_table": {  
  7.           "tmp_table_info": {  
  8.             "table": "intermediate_tmp_table",  
  9.             "row_length": 20,  
  10.             "key_length": 4,  
  11.             "unique_constraint": false,  
  12.             "location": "memory (heap)",  
  13.             "row_limit_estimate": 838860  
  14.           }  
  15.         }  
  16.       },  
  17.       {  
  18.         "converting_tmp_table_to_ondisk": {  
  19.           "cause": "memory_table_size_exceeded",  
  20.           "tmp_table_info": {  
  21.             "table": "intermediate_tmp_table",  
  22.             "row_length": 20,  
  23.             "key_length": 4,  
  24.             "unique_constraint": false,  
  25.             "location": "disk (InnoDB)",  
  26.             "record_format": "fixed"  
  27.           }  
  28.         }  
  29.       },  
  30.       {  
  31.         "filesort_information": [  
  32.           {  
  33.             "direction": "desc",  
  34.             "table": "intermediate_tmp_table",  
  35.             "field": "num"  
  36.           }  
  37.         ],  
  38.         "filesort_priority_queue_optimization": {  
  39.           "limit": 10,  
  40.           "rows_estimate": 1057,  
  41.           "row_size": 36,  
  42.           "memory_available": 262144,  
  43.           "chosen": true  
  44.         },  
  45.         "filesort_execution": [  
  46.         ],  
  47.         "filesort_summary": {  
  48.           "rows": 11,  
  49.           "examined_rows": 552203,  
  50.           "number_of_tmp_files": 0,  
  51.           "sort_buffer_size": 488,  
  52.           "sort_mode": "<sort_key, additional_fields> 
  53.         }  
  54.       }  
  55.     ]  
  56.   }  
  57.  

分析临时表字段

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

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

  1. Breakpoint 1, trace_tmp_table (trace=0x7eff94003088table=0x7eff94937200) at /root/newdb/mysql-server/sql/sql_tmp_table.cc:2306  
  2. warning: Source file is more recent than executable.  
  3. 2306      trace_tmp.add("row_length",table->s->reclength).  
  4. (gdb) p table->s->reclength  
  5. $1 = 20  
  6. (gdb) p table->s->fields  
  7. $22 = 2  
  8. (gdb) p (*(table->field+0))->field_name  
  9. $3 = 0x7eff94010b0c "aid"  
  10. (gdb) p (*(table->field+1))->field_name  
  11. $4 = 0x7eff94007518 "num"  
  12. (gdb) p (*(table->field+0))->row_pack_length()  
  13. $5 = 4  
  14. (gdb) p (*(table->field+1))->row_pack_length()  
  15. $6 = 15  
  16. (gdb) p (*(table->field+0))->type()  
  17. $7 = MYSQL_TYPE_LONG  
  18. (gdb) p (*(table->field+1))->type()  
  19. $8 = MYSQL_TYPE_NEWDECIMAL  
  20. (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 索引 

  1. # Query_time: 4.406927  Lock_time: 0.000200 Rows_sent: 10  Rows_examined: 1337315  
  2. SET timestamp=1552791804 
  3. 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索引的效果: 

  1. 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; 
  2.  
  3. +----+-------------+--------------+------------+-------+-------------------------------+----------------+---------+------+--------+----------+-------------------------------------------+ 
  4.  
  5. | id | select_type | table        | partitions | type  | possible_keys                 | key            | key_len | ref  | rows   | filtered | Extra                                     | 
  6.  
  7. +----+-------------+--------------+------------+-------+-------------------------------+----------------+---------+------+--------+----------+-------------------------------------------+ 
  8.  
  9. |  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 | 
  10.  
  11. +----+-------------+--------------+------------+-------+-------------------------------+----------------+---------+------+--------+----------+-------------------------------------------+  

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

使用idx_aid_day_pv索引的效果: 

  1. 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; 
  2.  
  3. +----+-------------+--------------+------------+-------+-------------------------------+----------------+---------+------+------+----------+--------------------------+ 
  4.  
  5. | id | select_type | table        | partitions | type  | possible_keys                 | key            | key_len | ref  | rows | filtered | Extra                    | 
  6.  
  7. +----+-------------+--------------+------------+-------+-------------------------------+----------------+---------+------+------+----------+--------------------------+ 
  8.  
  9. |  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 | 
  10.  
  11. +----+-------------+--------------+------------+-------+-------------------------------+----------------+---------+------+------+----------+--------------------------+  

查看 optimizer trace 信息 

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

摘取里面的执行结果如下 

  1.  
  2.   "join_execution": {  
  3.     "select#": 1,  
  4.     "steps": [  
  5.       {  
  6.         "creating_tmp_table": {  
  7.           "tmp_table_info": {  
  8.             "table": "intermediate_tmp_table",  
  9.             "row_length": 20,  
  10.             "key_length": 0,  
  11.             "unique_constraint": false,  
  12.             "location": "memory (heap)",  
  13.             "row_limit_estimate": 838860  
  14.           }  
  15.         }  
  16.       },  
  17.       {  
  18.         "filesort_information": [  
  19.           {  
  20.             "direction": "desc",  
  21.             "table": "intermediate_tmp_table",  
  22.             "field": "num"  
  23.           }  
  24.         ],  
  25.         "filesort_priority_queue_optimization": {  
  26.           "limit": 10,  
  27.           "rows_estimate": 552213,  
  28.           "row_size": 24,  
  29.           "memory_available": 262144,  
  30.           "chosen": true  
  31.         },  
  32.         "filesort_execution": [  
  33.         ],  
  34.         "filesort_summary": {  
  35.           "rows": 11,  
  36.           "examined_rows": 552203,  
  37.           "number_of_tmp_files": 0,  
  38.           "sort_buffer_size": 352,  
  39.           "sort_mode": "<sort_key, rowid> 
  40.         }  
  41.       }  
  42.     ]  
  43.   }  
  44.  

执行流程如下

      1. 创建一张临时表,临时表上有两个字段,aid和num字段(sum(pv) as num);

      2. 读取索引idx_aid_day_pv中的一行,然后查看是否满足条件,如果day字段不在条件范围内(20181220~20181224之间),则读取下一行;如果day字段在条件范围内,则把pv值累加(不是             在临时表中操作);

      3. 读取索引idx_aid_day_pv中的下一行,如果aid与步骤1中一致且满足条件,则pv值累加(不是在临时表中操作)。如果aid与步骤1中不一致,则把之前的结果集写入临时表;

      4. 循环执行步骤2、3,直到扫描完整个idx_aid_day_pv索引;

      5. 对临时表根据num的值做优先队列排序;

      6. 根据查询到的前10条的rowid回表(临时表)返回结果集。

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

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

该方案可行性

实验发现,当我增加一行20181219的数据时,虽然这行记录不满足我们的需求,但是扫描索引的也会读取这行。因为我做这个实验,只弄了20181220~201812245天的数据,所以需要扫描的行数正好是全表数据行数。

那么如果该表的数据存储的不是5天的数据,而是10天的数据呢,更或者是365天的数据呢?这个方案是否还可行呢?先模拟10天的数据,在现有时间基础上往后加5天,行数与现在一样785102行。 

  1. drop procedure if exists idata;  
  2. delimiter ;;  
  3. create procedure idata()  
  4. begin  
  5.   declare i int;  
  6.   declare aid int;  
  7.   declare pv int;  
  8.   declare post_day int;  
  9.   set i=1 
  10.   while(i<=785102)do  
  11.     set aid = round(rand()*500000);  
  12.     set pv = round(rand()*100);  
  13.     set post_day = 20181225 + i%5;  
  14.     insert into article_rank (`aid`,`pv`,`day`) values(aid, pv, post_day);  
  15.     set ii=i+1;  
  16.   end while;  
  17. end;;  
  18. delimiter ;  
  19. call idata();  
  20. # Query_time: 9.151270  Lock_time: 0.000508 Rows_sent: 10  Rows_examined: 2122417  
  21. SET timestamp=1552889936 
  22. 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;  

这里扫描行数2122417是因为扫描索引的时候需要遍历整个索引,整个索引的行数就是全表行数,因为我刚刚又插入了785102行。

当我数据量翻倍之后,这里查询时间明显已经翻倍。所以这个优化方式不稳定。

方案2 扩充临时表空间上限大小

默认的临时表空间大小是16MB 

  1. mysql> show global variables like '%table_size';  
  2. +---------------------+----------+  
  3. | Variable_name       | Value    |  
  4. +---------------------+----------+  
  5. | max_heap_table_size | 16777216 |  
  6. | tmp_table_size      | 16777216 |  
  7. +---------------------+----------+  

https://dev.mysql.com/doc/ref...

https://dev.mysql.com/doc/ref...

max_heap_table_size

This variable sets the maximum size to which user-created MEMORY tables are permitted to grow. The value of the variable is used to calculate MEMORY table MAX_ROWS values. Setting this variable has no effect on any existing MEMORY table, unless the table is re-created with a statement such as CREATE TABLE or altered with ALTER TABLE or TRUNCATE TABLE. A server restart also sets the maximum size of existing MEMORY tables to the global max_heap_table_size value.

tmp_table_size 

The maximum size of internal in-memory temporary tables. This variable does not apply to user-created MEMORY tables.

The actual limit is determined from whichever of the values of tmp_table_size and max_heap_table_size is smaller. If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk temporary table. The internal_tmp_disk_storage_engine option defines the storage engine used for on-disk temporary tables.

也就是说这里临时表的限制是16M,max_heap_table_size大小也受tmp_table_size大小的限制。

所以我们这里调整为32MB,然后执行原始的SQL 

  1. set tmp_table_size=33554432 
  2. set max_heap_table_size=33554432 
  1. # Query_time: 5.910553  Lock_time: 0.000210 Rows_sent: 10  Rows_examined: 1337315  
  2. SET timestamp=1552803869 
  3. select aid,sum(pv) as num from article_rank where day>=20181220 and day<=20181224 group by aid order by num desc limit 10;  

方案3 使用 SQL_BIG_RESULT 优化

告诉优化器,查询结果比较多,临时表直接走磁盘存储。 

  1. # Query_time: 6.144315  Lock_time: 0.000183 Rows_sent: 10  Rows_examined: 2122417  
  2. SET timestamp=1552802804 
  3. select SQL_BIG_RESULT aid,sum(pv) as num from article_rank where day>=20181220 and day<=20181224 group by aid order by num desc limit 10;  

扫描行数是 2x满足条件的总行数(785102)+group by 之后的总行数(552203)+limit 的值。

顺便值得一提的是: 当我把数据量翻倍之后,使用该方式,查询时间基本没变。因为扫描的行数还是不变的。实际测试耗时6.197484

总结

方案1优化效果不稳定,当总表数据量与查询范围的总数相同时,且不超出内存临时表大小限制时,性能达到更佳。当查询数据量占据总表数据量越大,优化效果越不明显;

方案2需要调整临时表内存的大小,可行;不过当数据库超过32MB时,如果使用该方式,还需要继续提升临时表大小;

方案3直接声明使用磁盘来放临时表,虽然扫描行数多了一次符合条件的总行数的扫描。但是整体响应时间比方案2就慢了0.1秒。因为我们这里数据量比较,我觉得这个时间差还能接受。

所以对比,选择方案3比较合适。

问题与困惑 

  1. # SQL1  
  2. select aid,sum(pv) as num from article_rank where day>=20181220 and day<=20181224 group by aid order by num desc limit 10;  
  3. # SQL2  
  4. 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;  
  1. SQL1 执行过程中,使用的是全字段排序后不需要回表为什么总扫描行数还要加上10才对得上?
  2. SQL1 与 SQL2 group by之后得到的行数都是552203,为什么会出现 SQL1 内存不够,里面还有哪些细节呢?
  3. trace 信息里的creating_tmp_table.tmp_table_info.row_limit_estimate都是838860;计算由来是临时表的内存限制大小16MB,而一行需要占的空间是20字节,那么最多只能容纳                             floor(16777216/20) = 838860行,而实际我们需要放入临时表的行数是785102。为什么呢?
  4. SQL1 使用SQL_BIG_RESULT优化之后,原始表需要扫描的行数会乘以2,背后逻辑是什么呢?为什么仅仅是不再尝试往内存临时表里写入这一步会相差10多倍的性能?
  5. 通过源码看到 trace 信息里面很多扫描行数都不是实际的行数,既然是实际执行,为什么 trace 信息里不输出真实的扫描行数和容量等呢,比如                                                                                   filesort_priority_queue_optimization.rows_estimate在SQL1中的扫描行数我通过gdb看到计算规则如附录图 1
  6. 有没有工具能够统计 SQL 执行过程中的 I/O 次数? 
责任编辑:庞桂玉 来源: segmentfault
相关推荐

2015-07-17 10:04:33

MKMapView优化

2021-01-08 13:52:15

Consul微服务服务注册中心

2020-02-10 10:15:31

技术研发指标

2021-01-24 11:46:26

自动化Web 优化

2020-06-05 08:53:31

接口性能实践

2021-10-09 10:24:08

NET爬虫内存

2020-08-10 11:00:02

Python优化代码

2020-10-19 19:45:58

MySQL数据库优化

2020-10-30 14:11:38

服务器SDK堆栈

2011-04-07 11:20:21

SQLServer

2011-09-27 10:35:44

2021-03-12 15:08:23

服务器性能优化

2011-02-22 09:29:23

jQueryJavaScript

2023-11-06 07:45:42

单据图片处理

2021-08-26 22:26:55

性能优化技术

2022-09-15 10:02:58

测试软件

2022-06-08 09:55:19

Data Catal字节跳动业务系统

2018-06-26 15:58:06

数据库MySQL索引优化

2019-01-21 11:17:13

CPU优化定位

2023-11-29 12:12:24

Oceanbase数据库
点赞
收藏

51CTO技术栈公众号