理解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与索引无关; （除覆盖索引除外）