MySQL 会给每个线程分配一块内存用于排序,称为 sort_buffer。
假设找出在杭州居住的人,按名字排序前1000个人(假设city有索引,那么非常舒服,不用全表扫描)
select city,name,age from t where city='杭州' order by name limit 1000;
全字段排序
具体流程是,先按照索引把所有满足city=’杭州’的数据的city、name、age字段都放到sort_buffer里(当然是一行一行扫描出来的),然后对sort_buffer中的数据进行排序,最后输出前1000个。
按 name 排序这个动作,可能在内存中完成,也可能需要使用外部排序,这取决于排序所需的内存和线程被分配用于排序的内存的大小sort_buffer_size。如果要排序的数据量小于 sort_buffer_size,排序就在内存中完成。但如果排序数据量太大,内存放不下,则不得不利用磁盘临时文件辅助排序。
number_of_tmp_files 代表用了12个外部临时文件辅助排序,外部排序一般使用归并排序算法。MySQL 将需要排序的数据分成 12 份,每一份单独排序后存在这些临时文件中。然后把这 12 个有序文件再合并成一个有序的大文件。
这里全部数据会被放到硬盘中,内存主要是用于计算排序过程和交互的
sort_mode 里面的 packed_additional_fields 的意思是,排序过程对字符串做了“紧凑”处理。即使 name 字段的定义是 varchar(16),在排序过程中还是要按照实际长度来分配空间的,也就是说杭州被分配的是2个字符。
rowid排序
只对原表的数据读了一遍,剩下的操作都是在 sort_buffer 和临时文件中执行的。但这个算法有一个问题,就是如果查询要返回的字段很多的话,那么 sort_buffer 里面要放的字段数太多,这样内存里能够同时放下的行数很少,要分成很多个临时文件,排序的性能会很差。
所以可以只把主键和要排序的name放到sort_buffer中(执行过程与全字段排序一样),只不过排序好后,多了一次主键索引查找结果。
辅助排序的文件少了,sort_mode也变了,因为参与排序的字段变了。最重要的是虽然扫描行数仍然是4000,但实际上参与排序过程要读5000行,因为按照排序索引还要查1000行再给出结果
如何选择?
MySQL 的一个设计思想:如果内存够,就要多利用内存,尽量减少磁盘访问,IO 是数据库中最昂贵的操作!!!!!,也就是说优先全字段排序,那么什么时候才会用rowid呢?
当单行数据长度超过该值,MySQL 就会觉得如果还用全字段排序,会导致 sort buffer 容纳下的行数太少,从而转为使用 rowId 排序。通过参数 max_length_for_sort_data 可以控制用于排序的行数据最大长度,默认值为 1024 字节。也就是说被select放到sort_buffer里的字段一行超过1024字节,就会用rowid排序。
如何回避高昂的排序成本
可以看到实际上排序是很高成本的操作,但其实并不是所有order by都要执行上述排序操作,因为这一切都是数据无序存储的锅,假设有一个 city 和 name 的联合索引,那么符合city的自动就已经是按name排好序的了
可以看到没有Using filesort了,也就是不用外部排序了。只扫描前1000条数据就够
再进一步,假设索引就能确定一条数据,而不用主键了,就会更快
但这并不是鼓励无脑建立索引,因为建立维护索引是有代价的,需要权衡