承接上一篇文章 继续分析
select * expalin一下
select id expalin一下
都是全表扫描 都是使用where 都是文件排序(如果待排序的内容不能由所使用的索引直接完成排序的话)
filesort是通过相应的排序算法将取得的数据在内存中进行排序,所使用的内存区域也就是通过sort_buffer_size 系统变量所设置的排序区。这个排序区是每个Thread 独享的,可能同一时刻在MySQL 中存在多个 sort buffer 内存区域。
MySQL中filesort 的实现算法有两种:
MySQL主要通过比较所设定的系统参数 max_length_for_sort_data的大小和Query 语句所取出的字段类型大小总和来判定需要使用哪一种排序算法。如果 max_length_for_sort_data更大,则使用第二种优化后的算法(因为第一种还要取行指针信息),反之使用第一种算法。
所以,select * 的大小明显多一些,所以,使用了第二种优化后的算法。而仅select id数据大小比较小,故采用第一种。 验证: 首先查询 max_length_for_sort_data 大小
其次,反观sql 是首先选出大于某一个时刻的数据,然后再limit,offest,显然数据量已经有了。 大概有1843个。
修改,sql
此时id最后一个均为500,没毛病
调为1000个的时候,最后一个id又不一样,
如下
所以,必定存在一个大于500小于1000的中间临界值,超过这个大小,采用的filesort排序算法便不一样。
花费时间大概1分钟,通过2分法,很快找到了这个临界值 为722
当大小为722的时候,结果一致。从723开始,便开始不一致。为了再次验证这个想法, 将max_length_for_sort_data 大小,改大一点,只要限制为723结果一致,那么就算验证成功。
很惨,验证失败!
睡了一晚,茅塞顿开,低级错误,低级错误,疏忽了这个order by的字段,如果值一样的话,会按照其他字段(比如id) 如果想要保持结果一致,order by 后面带上多个字段即可