该博客是我在看了《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_traceSET optimizer_trace=‘enabled=on’;#2. 记录现在执行目标 sql 之前已经读取的行数select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = ‘Innodb_rows_read’;#3. 执行我们需要执行的 sqltodo#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实验1select 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;实验2select aid,sum(pv) as num from article_rank force index(idx_day) where day>20190115 group by aid order by num desc LIMIT 10;实验3select 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;实验4select aid,sum(pv) as num from article_rank force index(PRI) where day>20190115 group by aid order by num desc LIMIT 10;实验1mysql> 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)实验2mysql> 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)实验3mysql> 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)实验4mysql> 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_modeMySQL 会给每个线程分配一块内存用于排序，称为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默认值是256KBhttps://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)。实验结果分析在看了附录中的实验结果之后，我汇总了一些比较重要的数据对比信息指标indexquery_timefilesort_summary.examined_rowsfilesort_summary.sort_modefilesort_priority_queue_optimization.rows_estimateconverting_tmp_table_to_ondiskInnodb_rows_read实验1idx_day_aid_pv25.05649091additional_fields1057true6417027实验2idx_day42.06649091additional_fields1057true9625540实验3idx_aid_day_pv5.38649091rowid649101false14146056实验4PRI21.90649091additional_fields1057true17354569filesort_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最后结果是 32085132 + 1 = 6417027；实验2相比实验1，实验2中不仅需要对临时表存盘，同时因为索引是idx_day，不能使用覆盖索引，还需要每行都回表，所以最后结果是 32085133 + 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…
...