场景:order by排序时出现临时文件排序,一般是数据没有走索引,或者设置了索引但是索引顺序不一致。
当你用explain命令对查询语句进行分析时,往往遇到rder by排序时会出现using filesort。这个时候如果想要继续查看详细的执行过程,需要借助optimizer trace(优化器追踪),MySQL5.6版本之后开始引入的。
. 使用optimizer trace查看优化器的选择过程,重点关注"filesort_summary":{}里面的参数,参数里可以关注num_initial_chunks_spilled_to_disk(产生了多少临时文件,0表示完全基于内存排序)的值。
还可以通过show status like '%sort_merge_passes%':进行归并排序的次数。
如果num_initial_chunks_spilled_to_disk和sort_merge_passes的值比较大,这个时候可以适当调整sort_buffer_size的值。让MySQL尽量减少在排序过程中对需要排序的数据进行分段,从而避免使用临时表来进行交换排序。这个值不是越大越好,
-
sort_buffer_size
的默认值在不同的MySQL版本中有所不同。在某些版本中,默认值为256KB(262144字节),而在MySQL 5.7中,默认值为1MB(1048576字节)。
2.官方文档推荐的范围为256KB到2MB。
可以基于官方文档推荐的范围,结合实际数据量进行调整。
知识扩展:聊聊 order by