理解mysql_order by排序原理及优化

一、排序算法
a. 一次扫描性能高，减少了io随机请求的次数
b. 排序操作是在内存(sort_buffer)里面进行的， 先select结果再进行排序，如果结果值大于max_length_for_sort_data 阀值设置需要二次io读取
c. 关联查询会产生临时表再进行order by

二、优化思路
a. 尽量减少io的读取 max_length_for_sort_data 阀值设置
b. 尽量使用有序排序输出。 运用覆盖索引、主键索引
c. 控制select 字段数，不需要的字段没必要输入
d. …….

一次扫描算法（4.1版本以后支持）
1)、根据条件取出所有字段信息
2)、在sort_buffer中进行排序, 如果sort_buffer较小，产生临时表，在临时表存储排序结果
选用一次排序扫描还是二次排序扫描， 需要根据参数来： max_length_for_sort_data
如果取的值 大于 max_length_for_sort_data 设置的值，采用二次扫描。
两个参数：
max_length_for_sort_data: 确定选用哪种扫描策略
sort_buffer: 排序过程中确定是否需要创建临时表存储排序结果。每个线程共享。

两次扫描算法
1)、根据条件取出排序字段和行指针信息
2)、在sort_buffer中进行排序, 如果sort_buffer较小，产生临时表，在临时表存储排序结果
3)、根据排序结果的行号去主索引结构中获取数据
例如：select * from t where filed=‘abc’ order by field2
此语句会经过2次扫描
第1次搜索出符合条件的记录
第2次根据行号去获取字段
如果field字段上有索引，解析规则：use index, Using filesort
如果field字段上没有索引，则解析规则：Using where; Using filesort
说明：order by与索引无关; （除覆盖索引除外）