共计 12327 个字符，预计需要花费 31 分钟才能阅读完成。
该博客是我在看了《MySQL 实战 45 讲》之后的一次实践笔记。文章比较枯燥，如果你在这篇文章看到一些陌生的关键字，建议你也一定要去做实验，只有做实验且验证了各个数据的由来，才能真正弄懂。
背景
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
实验原理
Optimizer Trace 是 MySQL 5.6.3 里新加的一个特性，可以把 MySQL Optimizer 的决策和执行过程输出成文本，结果为 JSON 格式，兼顾了程序分析和阅读的便利。
利用 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’;
官方文档 https://dev.mysql.com/doc/int…
实验
我做了四次实验，具体执行的第三步的 sql 如下
实验
sql
实验 1
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;
实验 2
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;
实验 3
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;
实验 4
select aid,sum(pv) as num from article_rank force index(PRI) where day>20190115 group by aid order by num desc LIMIT 10;
实验 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 子句之后的结果。
存放的字段是 aid 和 num 两个字段。该临时表是如何存储的? row_length 为什么是 20? 另开三篇博客写了这个问题 https://mengkang.net/1334.htmlhttps://mengkang.net/1335.htmlhttps://mengkang.net/1336.html
第二步
因为 memory_table_size_exceeded 的原因，需要把临时表 intermediate_tmp_table 以 InnoDB 引擎存在磁盘。
mysql> show global variables like ‘%table_size’;
+———————+———-+
| Variable_name | Value |
+———————+———-+
| max_heap_table_size | 16777216 |
| tmp_table_size | 16777216 |
+———————+———-+
https://dev.mysql.com/doc/ref…https://dev.mysql.com/doc/ref…max_heap_table_sizeThis 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，而一行需要占的空间是 20 字节，那么最多只能容纳 floor(16777216/20) = 838860 行，所以 row_limit_estimate 是 838860。
我们统计下 group by 之后的总行数。
mysql> select count(distinct aid) from article_rank where `day`>’20190115′;
+———————+
| count(distinct aid) |
+———————+
| 649091 |
+———————+
649091 < 838860
问题：为什么会触发 memory_table_size_exceeded 呢？
数据写入临时表的过程如下：
在磁盘上创建临时表，表里有两个字段，aid 和 num，因为是 group by aid，所以 aid 是临时表的主键。实验 1 中是扫描索引 idx_day_aid_pv，依次取出叶子节点的 aid 和 pv 的值。如果临时表种没有对应的 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_buffer。sort_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
https://dev.mysql.com/doc/ref…Default Value (Other, 64-bit platforms, >= 5.6.4) 262144
排序的方式也是有多种的
<sort_key, rowid>
<sort_key, additional_fields>
<sort_key, packed_additional_fields>
additional_fields
初始化 sort_buffer，确定放入字段，因为我们这里是根据 num 来排序，所以 sort_key 就是 num，additional_fields 就是 aid；
把 group by 子句之后生成的临时表（intermediate_tmp_table）里的数据（aid,num）存入 sort_buffer。我们通过 number_of_tmp_files 值为 0，知道内存是足够用的，并没有使用外部文件进行归并排序；
对 sort_buffer 中的数据按 num 做快速排序；
按照排序结果取前 10 行返回给客户端；
rowid
根据索引或者全表扫描，按照过滤条件获得需要查询的排序字段值和 row ID；
将要排序字段值和 row ID 组成键值对，存入 sort buffer 中；
如果 sort buffer 内存大于这些键值对的内存，就不需要创建临时文件了。否则，每次 sort buffer 填满以后，需要在内存中排好序（快排），并写到临时文件中；
重复上述步骤，直到所有的行数据都正常读取了完成；
用到了临时文件的，需要利用磁盘外部排序，将 row id 写入到结果文件中；
根据结果文件中的 row ID 按序读取用户需要返回的数据。由于 row ID 不是顺序的，导致回表时是随机 IO，为了进一步优化性能（变成顺序 IO），MySQL 会读一批 row ID，并将读到的数据按排序字段顺序插入缓存区中 (内存大小 read_rnd_buffer_size)。
实验结果分析
在看了附录中的实验结果之后，我汇总了一些比较重要的数据对比信息
指标
index
query_time
filesort_summary.examined_rows
filesort_summary.sort_mode
filesort_priority_queue_optimization.rows_estimate
converting_tmp_table_to_ondisk
Innodb_rows_read
实验 1
idx_day_aid_pv
25.05
649091
additional_fields
1057
true
6417027
实验 2
idx_day
42.06
649091
additional_fields
1057
true
9625540
实验 3
idx_aid_day_pv
5.38
649091
rowid
649101
false
14146056
实验 4
PRI
21.90
649091
additional_fields
1057
true
17354569
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 排序。
关于内存表的排序详解，可以参考 MySQL 实战 45 讲的第 17 讲如何正确地显示随机消息
filesort_priority_queue_optimization.rows_estimate
根据优先队列排序算法所理解：
1. 取出 649091 行（未排序）的前 10 行，构成一个堆。2. 取下一行，根据 num（来源于 sum(pv)）的值和堆里面最小的值作比较，如果该字大于堆里面的值，则替换掉（原来堆的最小值被删掉）3. 该节点与其父节点的值继续作比较，如果大于父节点的值则二者替换。递归执行，直到根节点 4. 重复步骤 2，3 直到第 649091 行比较完成
根据这个分析，四个实验都应该是扫描 649091 行，但实际结果却是，实验 3 是 649091 + 10 行，其他的都是 1057 行。
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 索引，而查询的值是 aid 和 pv，所以是覆盖索引，不需要进行回表。但是可以看到在创建临时表（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；
需要注意，如果我们开启慢查询日志，慢查询日志里面的扫描行数和这里统计的不一样，内存临时表的扫描行数也算在内的。
耗时也是最短的。
为什么实验 3 使用的是 rowid 排序而不是 additional_fields 排序？同样是写入 649091 到内存临时表，为什么其他三种方式都会出现内存不够用的情况呢？莫非其他三种情况是先把所有的行写入到临时表，再遍历合并？
实验 4
实验 4 首先遍历主表，需要扫描 14146055 行，然后把符合条件的 3208513 行放入临时表，所以最后是 14146055 + 3208513 + 1 = 17354569。
参考
《MySQL 实战 45 讲》https://time.geekbang.org/column/article/73479https://time.geekbang.org/column/article/73795https://dev.mysql.com/doc/ref…https://juejin.im/entry/59019…
