分页查询的几种不同方式

mysql分页查询的几种方式

注：大写单词为mysql关键字，小写单词为参数

	sql语句	适用场景	缺点
方式一	SELECT * FROM tableName LIMIT #{offset},#{size}	数据库中数据不多时的分页查询	当使用LIMIT offset,size命令时，mysql会逐条遍历，抛弃前offset条数据，然后取size条数据，当offset非常大时性能下降明显
方式二	SELECT * FROM table WHERE id > #{id} LIMIT #{size}	需要连续取值时的分页查询	当要跨页查询时（例如当前是第1页，直接跳到第5页的数据），尤其是现在普遍使用雪花算法生成id，无法得知id的具体值
方式三	SELECT * FROM table WHERE id > #{id} ORDER BY id LIMIT #{offset},#{size}	数据不多且不连续查询	方式一和方式二结合，虽然解决了方式二的问题，当跨页查询的跨度太大时（例如从第5页跳转到第1000页），仍然会有方式一的问题
方式四	SELECT * FROM table AS a INNER JOIN(SELECT id FROM table ORDER BY id LIMIT #{offset},#{size}) AS b USING(id)	优化方式一	当offset很大时同样有性能问题
方式五	SELECT * FROM table WHERE id >(SELECT id FROM table ORDER BY id limit #{offset},1) LIMIT #{size}	优化方式四	相较于方式4不需要进行内联操作，但仍有同样的问题存在

基于缓存实现分页查询

问题关键点：基于以上的几种sql语句分析，分页查询的性能问题主要出现在当offset很大时，mysql需要逐条查询后丢弃数据，极端情况下几乎接近扫描全表，而不使用offset时（方式二）无法连续查询。

解决思路：使用方式二，找到一种快速定位id的方法即可解决扫描全表的问题

实现方案：（前提是使用自增且不重复的字段）

1、当向数据库新增或删除数据时，将数据的id维护到redis的zset集合中；

2、（可选）以数据的id为key，将整行数据存储到redis中

3、确定offset的值后，可以获取指定排名的数据id（zset使用跳表，查询的时间复杂度为O(logN)）

4、确定id后使用方式二进行分页查询（或根据id在redis中查询数据）

问题：

1、保证数据库和缓存数据的一致性是另一个待研究的领域

2、很难实现复杂业务场景的分页查询，如指定筛选条件的分页查询

ORDER BY 和 LIMIT 关键字共用时所引发的数据问题
现象：
ORDER BY 和 LIMIT 关键字混用时，当ORDER BY的字段内容不唯一时，可能会导致分页查询的数据有重复。

根据官方文档的解释：
如果order by的字段有多个行都有相同的值，mysql是会随机的顺序返回查询结果的，具体依赖对应的执行计划。也就是说如果排序的列是无序的，那么排序的结果行的顺序也是不确定的。

解决方案：
在使用ORDER BY时，将主键作为排序值即可避免此问题