Order By Limit不稳定性

前置

MySQL 5.7官网给出了 order by limit一起使用，可能会产生不确定性：order by limit
不确定如图2.54

解决不确定性

order by limit一起使用时，order by后字段中需要有主键id或唯一键

场景1 Order By索引

1.1 背景

• user表联合索引为city_name、主键id

select * from user where city = 'shanghai' order by name limit 0,5

• user表联合索引city_name的叶子节点存储的数据如下

1.2 不确定性产生原因

1.2.1 正常情况下

• explain此SQL，Extra不会显示filesort即不需要排序;
• 联合索引为city_name，所以数据在插入的时候就已经按照city_name排序好了；
• 上述SQL取数时，直接从联合索引中拿出排序好的数据，并返回即可，所以存的时候是什么样顺序，取的时候也一定是什么样的顺序，不会产生不确定性。

1.2.2 但是

1）情况1

• 假如两次查询期间发生了数据新增、删除、更新等操作，而且这些变更操作正好有city为shanghai的数据
• 就可能导致联合索引city_name数据页发生了变化（重排数据顺序，甚至分页）等
• 此时会导致查询的不确定性(漏查和重复查)
• eg:第一次查询limit 0,5结果id为: [20,3,40,5,60]
  • 此时insert了一条数据(100,“shanghai”, “ma朋”)
  • 联合索引就可能发生了变化，id=100的这条数据，会排在id=40的数据前面(city同为shanghai,ma朋排序先于tiant)
  • 第二次查询limit0,5结果id集变为[20,3,100,40,5]

2）情况2

• 假如user表除了city_name索引还有city_address联合索引，则查询会产生不确定性原因；
• 两次查询可能会选择不同的联合索引，返回的数据自然不同。

1.3 补充

上述场景1和

先从db查询SQLselect *from t where city = 'shanghai' limit 0,5;
再Java程序中按照name排序返回

的这种查询方式区别：

• 假如每次查询都force index city_name联合索引，则和场景1查询没有任何区别；
• 本质都是使用city_name联合索引进行查询；
• 若还有其他city_address索引，则补充SQL每次执行时可能选择city_address也可能选择city_name

1.4 场景1总结

• 如果业务上可以保障查询期间不会有数据变更，并且每次查询都能使用相同的联合索引；

• 则场景1不会产生数据不确定性并

• 且下面两条SQL查询基本无区别代码块

  SELECT * from user where city = 'shanghai' order by name limit 0,5;
  SELECT * from user where city = 'shanghai'limit 0,5;
  

场景2 Order by id