mysql对无索引的字段进行排序后limit，有可能导致分页查询重复出现问题

发现问题

mysql对无索引字段进行排序后limit ，当被排序字段有相同值时并且在limit范围内，取的值并不是正常排序后的值，有可能第一页查询的记录，重复出现在第二页的查询记录中，导致分页结果查询错乱问题。

也就是说，当排在第N行的数据可取key1、 key2 时 ， 排序结果可能是key1，也可能是key2。

问题重演

 

 

如上以页大小15、按发生时间event_time（不是索字段）倒序进行分页查询，第一页与第二页查询中出现大量重复记录，由此可证。

具体sql:

SELECT
    se.* 
FROM
    fp_ship_event se
order by  se.event_time desc
limit 15,15

原因及解决方法

https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html 
这是mysql对limit的优化。 
原文：

If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns. 
是说如果order by的列有相同的值时， mysql会随机选取这些行，具体根据执行计划有所不同。

官方推荐解决方法：

order by 的列中包含一个索引列

 

具体sql:

SELECT
    se.* 
FROM
    fp_ship_event se
order by  se.event_time desc , se.id asc
limit 15,15

个人另一种解决方法：

增加一个行号，按行号进行分页（按行号排序或者按行号进行范围查询亦可），类似oracle那样进行分页

SELECT
    * 
FROM
    (
    SELECT
        temp.*,
        @rowNum := @rowNum + 1 AS rownum 
    FROM
        ( SELECT se.* FROM fp_ship_event se ORDER BY se.event_time DESC ) temp,
        ( SELECT @rowNum := 0 ) b 
    ) temp2 
ORDER BY
    temp2.rownum ASC 
    LIMIT 15,
    15

或者

 SELECT
        *
        FROM
        (
        SELECT
        temp.*,
        @rowNum := @rowNum + 1 AS rownum
        FROM
        (
        SELECT
        se.*
        FROM
        fp_ship_event se
        ORDER BY
        se.event_time DESC
        ) temp,
        ( SELECT @rowNum := 0 ) b
        ) temp2
        WHERE
        temp2.rownum > 15 and temp2.rownum <= 30

本文地址：https://blog.csdn.net/sinat_34806137/article/details/109462169