背景

Mysql 版本 :5.7
业务需求:需要统最近一个月阅读量最大的10篇文章
为了对比后面实验效果,我加了3个索引

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` (`day`),
  KEY `idx_day_aid_pv` (`day`,`aid`,`pv`),
  KEY `idx_aid_day_pv` (`aid`,`day`,`pv`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

实验原理

利用performance_schema库里面的session_status来统计innodb读取行数
利用performance_schema库里面的optimizer_trace来查看语句执行的详细信息

下面的实验都使用如下步骤来执行

#0. 如果前面有开启 optimizer_trace 则先关闭
SET optimizer_trace="enabled=off";

#1. 开启 optimizer_trace
SET optimizer_trace='enabled=on';

#2. 记录现在执行目标 sql 之前已经读取的行数
select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read';

#3. 执行我们需要执行的 sql
todo

#4. 查询 optimizer_trace 详情
select trace from `information_schema`.`optimizer_trace`\G;

#5. 记录现在执行目标 sql 之后读取的行数
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

实验

我做了四次实验,具体执行的第三步的 sql 如下

实验1

mysql> select `aid`,sum(`pv`) as num from article_rank force index(idx_day_aid_pv) where `day`>'20190115' group by aid order by num desc LIMIT 10;
# 结果省略
10 rows in set (25.05 sec)
{
  "steps": [
    {
      "join_preparation": "略"
    },
    {
      "join_optimization": "略"
    },
    {
      "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": 649091,
              "number_of_tmp_files": 0,
              "sort_buffer_size": 488,
              "sort_mode": "<sort_key, additional_fields>"
            }
          }
        ]
      }
    }
  ]
}
mysql> select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
Query OK, 1 row affected (0.00 sec)

mysql> select @b-@a;
+---------+
| @b-@a   |
+---------+
| 6417027 |
+---------+
1 row in set (0.01 sec)

实验2

mysql> select `aid`,sum(`pv`) as num from article_rank force index(idx_day) where `day`>'20190115' group by aid order by num desc LIMIT 10;
# 结果省略
10 rows in set (42.06 sec)
{
  "steps": [
    {
      "join_preparation": "略"
    },
    {
      "join_optimization": "略"
    },
    {
      "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": 649091,
              "number_of_tmp_files": 0,
              "sort_buffer_size": 488,
              "sort_mode": "<sort_key, additional_fields>"
            }
          }
        ]
      }
    }
  ]
}
mysql> select @b-@a;
+---------+
| @b-@a   |
+---------+
| 9625540 |
+---------+
1 row in set (0.00 sec)

实验3

mysql> select `aid`,sum(`pv`) as num from article_rank force index(idx_aid_day_pv) where `day`>'20190115' group by aid order by num desc LIMIT 10;
# 省略结果
10 rows in set (5.38 sec)
{
  "steps": [
    {
      "join_preparation": "略"
    },
    {
      "join_optimization": "略"
    },
    {
      "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": 649101,
              "row_size": 24,
              "memory_available": 262144,
              "chosen": true
            },
            "filesort_execution": [
            ],
            "filesort_summary": {
              "rows": 11,
              "examined_rows": 649091,
              "number_of_tmp_files": 0,
              "sort_buffer_size": 352,
              "sort_mode": "<sort_key, rowid>"
            }
          }
        ]
      }
    }
  ]
}
mysql> select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
Query OK, 1 row affected (0.00 sec)

mysql> select @b-@a;
+----------+
| @b-@a    |
+----------+
| 14146056 |
+----------+
1 row in set (0.00 sec)

实验4

mysql> select `aid`,sum(`pv`) as num from article_rank force index(PRI) where `day`>'20190115' group by aid order by num desc LIMIT 10;# 省略查询结果
10 rows in set (21.90 sec)
{
  "steps": [
    {
      "join_preparation": "略"
    },
    {
      "join_optimization": "略"
    },
    {
      "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": 649091,
              "number_of_tmp_files": 0,
              "sort_buffer_size": 488,
              "sort_mode": "<sort_key, additional_fields>"
            }
          }
        ]
      }
    }
  ]
}
mysql> select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
Query OK, 1 row affected (0.00 sec)

mysql> select @b-@a;
+----------+
| @b-@a    |
+----------+
| 17354569 |
+----------+
1 row in set (0.00 sec)

执行流程举例说明

看下本案例中的 sql 去掉强制索引之后的语句

select `aid`,sum(`pv`) as num from article_rank where `day`>20190115 group by aid order by num desc LIMIT 10;

我们以实验1为例

第一步

因为该 sql 中使用了 group by,所以我们看到optimizer_trace在执行时(join_execution)都会先创建一张临时表creating_tmp_table)来存放group by子句之后的结果。

第二步

因为memory_table_size_exceeded的原因,需要把临时表intermediate_tmp_tableInnoDB引擎存在磁盘。

mysql> show global variables like '%table_size';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
| tmp_table_size      | 16777216 |
+---------------------+----------+

也就是说这里临时表的限制是16M,而一行需要占的空间是20字节,那么最多只能容纳floor(16777216/20) = 838860行,所以row_limit_estimate838860

我们统计下group by之后的总行数。

mysql> select count(distinct aid) from article_rank where `day`>'20190115';
+---------------------+
| count(distinct aid) |
+---------------------+
|              649091 |
+---------------------+
649091 < 838860

数据写入临时表的过程如下:

在磁盘上创建临时表,表里有两个字段,aidnum,因为是 group by aid,所以aid是临时表的主键。
实验1中是扫描索引idx_day_aid_pv,依次取出叶子节点的aidpv的值。
如果临时表种没有对应的 aid就插入,如果已经存在的 aid,则把需要插入行的 pv 累加在原来的行上。

第三步

intermediate_tmp_table里面的num字段做desc排序

filesort_summary.examined_rows

排序扫描行数统计,我们统计下group by之后的总行数。(前面算过是649091)

所以每个实验的结果中filesort_summary.examined_rows 的值都是649091
filesort_summary.number_of_tmp_files的值为0,表示没有使用临时文件来排序。

filesort_summary.sort_mode

MySQL 会给每个线程分配一块内存用于排序,称为sort_buffersort_buffer的大小由sort_buffer_size来确定。

mysql> show global variables like 'sort_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.01 sec)

也就说是sort_buffer_size默认值是256KB

排序的方式也是有多种的

  • <sort_key, rowid>
  • <sort_key, additional_fields>
  • <sort_key, packed_additional_fields>
additional_fields
  1. 初始化sort_buffer,确定放入字段,因为我们这里是根据num来排序,所以sort_key就是numadditional_fields就是aid
  2. group by 子句之后生成的临时表(intermediate_tmp_table)里的数据(aid,num)存入sort_buffer。我们通过number_of_tmp_files值为0,知道内存是足够用的,并没有使用外部文件进行归并排序;
  3. sort_buffer中的数据按num做快速排序;
  4. 按照排序结果取前10行返回给客户端;
rowid
  1. 根据索引或者全表扫描,按照过滤条件获得需要查询的排序字段值和row ID;
  2. 将要排序字段值和row ID组成键值对,存入sort buffer中;
  3. 如果sort buffer内存大于这些键值对的内存,就不需要创建临时文件了。否则,每次sort buffer填满以后,需要在内存中排好序(快排),并写到临时文件中;
  4. 重复上述步骤,直到所有的行数据都正常读取了完成;
  5. 用到了临时文件的,需要利用磁盘外部排序,将row id写入到结果文件中;
  6. 根据结果文件中的row ID按序读取用户需要返回的数据。由于row ID不是顺序的,导致回表时是随机IO,为了进一步优化性能(变成顺序IO),MySQL会读一批row ID,并将读到的数据按排序字段顺序插入缓存区中(内存大小read_rnd_buffer_size)。

实验结果分析

在看了附录中的实验结果之后,我汇总了一些比较重要的数据对比信息

filesort_summary.examined_rows

实验1案例中已经分析过。

mysql> select count(distinct aid) from article_rank where `day`>'20190115';
+---------------------+
| count(distinct aid) |
+---------------------+
|              649091 |
+---------------------+

filesort_summary.sort_mode

同样的字段,同样的行数,为什么有的是additional_fields排序,有的是rowid排序呢?

我们说 additional_fields 对比 rowid 来说,减少了回表,也就减少了磁盘访问,会被优先选择。但是要注意这是对于 InnoDB 来说的。而实验3是内存表,使用的是 memory 引擎。回表过程只是根据数据行的位置,直接访问内存得到数据,不会有磁盘访问(可以简单的理解为一个内存中的数组下标去找对应的元素)。排序的列越少越好占的内存就越小,所以就选择了 rowid 排序。

filesort_priority_queue_optimization.rows_estimate

根据优先队列排序算法所理解:

1.取出 649091 行(未排序)的前 10 行,构成一个堆。
2.取下一行,根据 num (来源于sum(pv))的值和堆里面最小的值作比较,如果该字大于堆里面的值,则替换掉(原来堆的最小值被删掉)
3.该节点与其父节点的值继续作比较,如果大于父节点的值则二者替换。递归执行,直到根节点
4.重复步骤2,3直到第 649091 行比较完成

converting_tmp_table_to_ondisk

是否创建临时表。同样是写入 649091 到内存临时表,为什么其他三种方式都会出现内存不够用的情况呢?

Innodb_rows_read

上面实验中每次在统计@b-@a的过程中,我们查询了OPTIMIZER_TRACE这张表,需要用到临时表,而 internal_tmp_disk_storage_engine 的默认值是 InnoDB。如果使用的是 InnoDB 引擎的话,把数据从临时表取出来的时候,会让 Innodb_rows_read 的值加 1。

我们先查询下面两个数据,下面需要使用到

mysql> select count(*) from article_rank;
+----------+
| count(*) |
+----------+
| 14146055 |
+----------+

mysql> select count(*) from article_rank where `day`>'20190115';
+----------+
| count(*) |
+----------+
|  3208513 |
+----------+

实验1

因为满足条件的总行数是3208513,因为使用的是idx_day_aid_pv索引,而查询的值是aidpv,所以是覆盖索引,不需要进行回表。
但是可以看到在创建临时表(creating_tmp_table)之后,因为超过临时表内存限制(memory_table_size_exceeded),所以这3208513行数据的临时表会写入磁盘,使用的依然是InnoDB引擎。
所以实验1最后结果是 3208513*2 + 1 = 6417027

实验2

相比实验1,实验2中不仅需要对临时表存盘,同时因为索引是idx_day,不能使用覆盖索引,还需要每行都回表,所以最后结果是 3208513*3 + 1 = 9625540

实验3

实验3中因为最左列是aid,无法对day>20190115表达式进行过滤筛选,所以需要遍历整个索引(覆盖所有行的数据)。
但是本次过程中创建的临时表(memory 引擎)没有写入磁盘,都是在内存中操作,所以最后结果是14146055 + 1 = 14146056

耗时也是最短的。

实验4

实验4首先遍历主表,需要扫描14146055行,然后把符合条件的3208513行放入临时表 ,所以最后是14146055 + 3208513 + 1 = 17354569

参考

02-13 14:57