|
MySQL 算法的艺术源于 order by 排序的巧用，什么样的配置选择什么样的排序模式是有一定的规定和诀窍，这个就是mysql对于排序的底层原理的选择。 今天就给我大家介绍一下关于mysql底层的排序模式的选择和使用。 一：排序模式 二：如何选择排序模式 三：排序算法 1.1一般的排序模式分为以下几种： sort_key, rowid，表明排序缓冲区元组包含排序键值和原始表行的行id，排序后需要使用行id进行回表，这种算法也称为 original filesort algorithm，回表排序算法 sort_key, additional_fields，表明排序缓冲区元组包含排序键值和查询所需要的列，排序后直接从缓冲区元组取数据，无需回表，这种算法也称为modified filesort algorithm(不回表排序)； sort_key, packed_additional_fields：类似上一种形式，但是附加的列(如varchar类型)紧密地打包在一起，而不是使用固定长度的编码
2.1：排序模式的选择需要考虑的因素就是：max_length_for_sort_data 一般查询该属性值的方法如下： show variables like '%sort_buffer%'
这个属性默认值大小为1024字节 如果查询列和排序列占用的大小超过这个值，那么会转而使用sort_key, rowid模式； 如果不超过，那么所有列都会放入sort buffer中，使用sort_key, additional_fields或者sort_key, packed_additional_fields模式； 如果查询的记录太多，那么会使用sort_key, packed_additional_fields对可变列进行压缩。
3.1:排序算法同样的原理也是通过判断数据量的大小来选择不同的排序算法。 如果排序取的结果很小，小于内存，那么会使用优先级队列进行堆排序 
如下: SELECT * from users u where u.city ='昆明市' ORDER by name LIMIT 10
这样的sql一般的适用于排序量很小，并且小于内存值选择的排序方式就是优先级队列进行数据堆的排序。 在使用limit加order by排序的时则有两种情况下的排序选择 SELECT * from users u where u.city ='昆明市' ORDER by name LIMIT 100,1
limit a ，b 当a太大的时候，如：limit 900,10（表数据为1000条记录）a相对于数据源来说太大了的时候，排序就会自动选择最后面的数据开始排序，那么选择使用sort buffer进行快速排序 当a排序不太大的时候，则排序就是自动选择优先级队列进行排序。
如果参与排序的数据sort buffer装不下了，那么我们会一批一批的给sort buffer进行内存快速排序，结果放入排序临时文件，最终使对所有排好序的临时文件进行归并排序。 order by 总结 order by字段尽量使用固定长度的字段类型，因为排序字段不支持压缩； order by字段如果需要用可变长度，应尽量控制长度，道理同上； 查询中尽量不用用select *，避免查询过多，导致order by的时候sort buffer内存不够导致外部排序，或者行大小超过了max_length_for_sort_data导致走了sort_key, rowid排序模式，使得产生了更多的磁盘读，影响性能； 尝试给排序字段和相关条件加上联合索引，能够用到覆盖索引最佳。
|
