MariaDB分页查询结果重复问题求助：重建数据库后恢复正常

问题现象 #

• 分页查询每页10条数据，按preference表的preference_group_id字段排序时，preferences 5和6在第一页出现后，第二页再次重复出现
• 无论是否使用DISTINCT子句，问题均存在，说明该子句与问题无关
• 通过导入完整SQL文件重建数据库后，问题消失

核心原因分析 #

1. 排序维度不足：仅按preference_group_id排序时，若存在多条记录的该字段值相同，MariaDB无法保证相同值记录的固定排序顺序。分页查询时，数据库可能在不同的OFFSET偏移下，对同组数据的排序逻辑发生变化，导致部分记录跨页重复。
2. 索引/统计信息异常：数据库的索引损坏、统计信息过期，会让查询优化器生成错误的执行计划，破坏分页的逻辑一致性。重建数据库相当于重置了所有索引和统计信息，因此问题消失。

解决办法 #

1. 补充唯一排序字段（推荐） #

在排序条件中追加唯一标识字段（比如表的主键preference_id），确保排序结果的确定性，从根源避免分页重复：

-- 第一页
SELECT * FROM preference
ORDER BY preference_group_id, preference_id
LIMIT 10 OFFSET 0;

-- 第二页
SELECT * FROM preference
ORDER BY preference_group_id, preference_id
LIMIT 10 OFFSET 10;

2. 修复索引与统计信息 #

如果无法修改查询语句，可以尝试重建表的索引并更新统计信息，修复可能存在的数据库内部异常：

-- 重建表与索引（InnoDB引擎）
ALTER TABLE preference ENGINE=InnoDB;

-- 更新表统计信息，帮助优化器生成正确执行计划
ANALYZE TABLE preference;

3. 验证数据完整性 #

检查是否存在数据异常（比如逻辑上的重复数据），确保表的主键或唯一约束正常生效，排除脏数据导致的排序混乱。

内容的提问来源于stack exchange，提问作者Mark Smith