mysql分页后排序失效数据丢失解决方案

mysql使用Limit分页不加索引列会导致数据丢失、重复和索引失效

mysql官网对limit的详细说明及优化建议：https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html

官网

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.

......
If it is important to ensure the same row order with and without LIMIT, include additional columns in the ORDER BY clause to make the order deterministic. For example, if id values are unique, you can make rows for a given category value appear in id order by sorting like this:

官网推荐的 order by 索引列

SELECT id,word,nature,weight,order_num FROM unlp_hot_dictionary ORDER BY order_num, id DESC LIMIT 0,10;

排序介绍

//先根据status升序，然后结束时间降序，id降序；这里的id主键索引可以避免排序失效数据丢失
select id,coupon_id,status,end_time from user_coupon where user_id = '88888888' and discount_coupon_type < 3 and use_scope = 3  order by status asc,end_time,id desc LIMIT 0,10