order by + limit分页时数据重复问题及解决方法

问题描述：mysql version 5.6.8command 表结构

CREATE TABLE

command

(

ID INT NOT NULL,

NAME VARCHAR(16),

DESCRIPTION VARCHAR(32),

INDEX idx_command_id (ID)

)

ENGINE=InnoDB DEFAULT CHARSET=utf8;

表数据

order by + limit分页查询

查询第1页

select * from command order by age limit 0,4;

查询第2页

select * from command order by age limit 4,4;

可以看到第2页中查出了第1页中存在的重复数据

原因分析：

查看以上语句的执行计划

可以看到，order by limit时Mysql会进行优化，使用的是内存中的filesort文件排序，in memory filesort 使用的是优先级队列（priority queue），优先级队列使用的二叉堆；

使用 priority queue 的目的，就是在不能使用索引有序性的时候，如果要排序，并且使用了limit n，那么只需要在排序的过程中，保留n条记录即可这样虽然不能解决所有记录都需要排序的开销，但是只需要 sort buffer 少量的内存就可以完成排序。

因此，在limit n时，只会堆排序前n个，且是不稳定排序，因此并不能保证字段值相同时的相对顺序，因此分页时可能造成重复；

MySQL 5.5 没有这个优化，所以也就不会出现这个问题，5.6版本之后编程客栈才出现了这种情况。

解决方案：

1. 新加一个排序字段，android这个字段绝对有序，在第1个排序字段重复时， 使用第2个字段排序

2. 利用索引的有序性，如给id加上主键约束，排序字段添加索引

explain select id,age from command order by age limit 4,4

可以看到查询走了索引，排序就稳定了，没什么问题

（3）一些常见的数据库排序问题

不加order by的时候的排序问题

用户在使用oracle或MySQL的时候，发现MyandroidSQL总是有序的，Oracle却很混乱，这个主要是因为Oracle是堆表，MySQL是索引聚簇表的原因。

所以没有order by的时候，数据库并不保证记录返回的顺序性，并且不保证每次返回都一致的。

分页问题

分页重复的问题

如前面所描述的，分页是在数据库提供的排序功能的基础上，衍生出来的应用需求，数据库并不保证分页的重复问开发者_Spark题。

到此这篇关于order by +&nbpythonsp;limit分页时数据重复的文章就介绍到这了,更多相关order by  limit分页时数据重复内容请搜索我们以前的文章或继续浏览下面的android相关文章希望大家以后多多支持我们！