Using temporary与Using filesort

迈不过友情╰ 2022-01-14 15:41 251阅读 0赞

通过`explain`查看sql的执行计划时，`Extra`字段的值往往会看到`Using where; Using index; Using temporary; Using filesort`，其中此次重点关注`Using temporary; Using filesort`。

## Using temporary ##

`Using temporary`表示由于排序没有走索引、使用`union`、子查询连接查询、使用某些视图等原因（详见[internal-temporary-tables][]），因此创建了一个内部临时表。注意这里的临时表可能是内存上的临时表，也有可能是硬盘上的临时表，理所当然基于内存的临时表的时间消耗肯定要比基于硬盘的临时表的实际消耗小。

查看sql执行时使用的是内存临时表还是硬盘临时表，需要使用如下命令：

[Created\_tmp\_tables][Created_tmp_tables]表示mysql创建的内部临时表的总数（包括内存临时表和硬盘临时表）；[Created\_tmp\_disk\_tables][Created_tmp_disk_tables]表示mysql创建的硬盘临时表的总数。

当mysql需要创建临时表时，选择内存临时表还是硬盘临时表取决于参数[tmp\_table\_size][tmp_table_size]和[max\_heap\_table\_size][max_heap_table_size]，内存临时表的最大容量为`tmp_table_size`和`max_heap_table_size`值的最小值，当所需临时表的容量大于两者的最小值时，mysql就会使用硬盘临时表存放数据。

用户可以在mysql的配置文件里修改该两个参数的值，两者的默认值均为16M。

tmp_table_size = 16M
    max_heap_table_size = 16M

查看`tmp_table_size`和`max_heap_table_size`值：

## Using filesort ##

如果问`Using filesort`是什么意思，大多数人应该会回答“基于硬盘的排序”或者“数据太多不适合内存，所以在硬盘上排序”。然而这些解释是错误的。

`Using filesort`仅仅表示没有使用索引的排序，事实上`filesort`这个名字很糟糕，并不意味着在硬盘上排序，`filesort`与文件无关。因此消除`Using filesort`的方法就是让查询sql的排序走索引。

`filesort`使用的算法是`QuickSort`，即对需要排序的记录生成元数据进行分块排序，然后再使用mergesort方法合并块。其中`filesort`可以使用的内存空间大小为参数[sort\_buffer\_size][sort_buffer_size]的值，默认为2M。当排序记录太多`sort_buffer_size`不够用时，mysql会使用临时文件来存放各个分块，然后各个分块排序后再多次合并分块最终全局完成排序。

[Sort\_merge\_passes][Sort_merge_passes]表示`filesort`执行过的文件分块合并次数的总和，如果该值比较大，建议增大`sort_buffer_size`的值。

#### filesort排序方式 ####

`filesort`使用的排序方法有两种：

第一种方法是对需要排序的记录生成`<sort_key,rowid>`的元数据进行排序，该元数据仅包含排序字段和rowid。排序完成后只有按字段排序的rowid，因此还需要通过rowid进行回表操作获取所需要的列的值，可能会导致大量的随机IO读消耗；

第二种方法是是对需要排序的记录生成`<sort_key,additional_fields>`的元数据，该元数据包含排序字段和需要返回的所有列。排序完后不需要回表，但是元数据要比第一种方法长得多，需要更多的空间用于排序。

参数[max\_length\_for\_sort\_data][max_length_for_sort_data]字段用于控制`filesort`使用的排序方法，当所有需要排序记录的字段数量总和小于`max_length_for_sort_data`时使用第二种算法，否则会用第一种算法。该值的默认值为1024。

mysql> set global max_length_for_sort_data = 1024;

[internal-temporary-tables]: https://dev.mysql.com/doc/refman/5.6/en/internal-temporary-tables.html
[Created_tmp_tables]: https://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html#statvar_Created_tmp_tables
[Created_tmp_disk_tables]: https://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html#statvar_Created_tmp_disk_tables
[tmp_table_size]: https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_tmp_table_size
[max_heap_table_size]: https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_max_heap_table_size
[sort_buffer_size]: https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_sort_buffer_size
[Sort_merge_passes]: https://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html#statvar_Sort_merge_passes
[max_length_for_sort_data]: https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_max_length_for_sort_data