1.什么是Using filesort
当sql出现order by排序的时候,排序时没有使用到索引(索引自带排序)就会触发Using filesort(文件排序)。
2.文件排序怎么底层如何处理的?
文件排序分为两种: 全字段排序和rowid排序。
注:排序时数据库会单独分配一部分内存(sort_buffer),他的大小(sort_buffer_size)可以设置。后面会详细说明。
1)全字段排序
数据库会将已匹配结果集的所有字段数据(select中的所有字段对应的数据)放入sort_buffer中,然后进行排序。当sort_buffer快满了之后, 数据库会将当前sort_buffer中已排序好的数据放入磁盘的一个临时文件中(这种情况很不友好,后面会告诉小伙伴们怎么优化)。之后再继续向sort_buffer中输送数据排序。直到所有结果集排序完成。数据库便会将现有的临时文件做归并排序合成一个大的文件输出,排序完成。
2)rowid排序
与全字段排序不同的是,rowid排序在向sort_buffer中输送数据时只会输送需排序字段和id的数据。(此时小伙伴们会发现,同样的结果集rowid排序向sort_buffer中输送的数据远小于全字段排序。这样便可以尽可能避免出现使用磁盘文件临时存储的情况。),排序完成之后。由于之前排序只排序了待排序字段,所以此时需要通过id将其它其他字段一 一对应,这也是之前向sort_buffer中输送数据时需要输送id的原因。
3.既然有两种排序,数据库引擎是如何选择的呢?
此时我们需要关注一个参数max_length_for_sort_data, 它表示MySQL用于排序行数据的长度的一个参数的阈值(输送到sort_buffer所有字段长度总和,字段长度与建表设置大小有关)。
当输送到sort_buffer所有字段长度总和 < max_length_for_sort_data时 选择的是全字段排序, 反之选择rowid排序。
4.总结与优化
1)总结:
我们做排序操作尽可能使用索引排序,如非不得已使用了文件排序我们可以选择优化文件排序
2)优化:
1. max_length_for_sort_data优化: 根据不同需求设置该参数大小,促使数据库引擎选择相对合适的排序方式
2. sort_buffer: 如果出现使用磁盘临时文件进行排序的, 我们可以调大sort_buffer。(不要太大,毕竟电脑的内存有限)