踩过的坑————以时间排序的问题

在以时间排序时，时间如果都是一样的时候，分页可能造成数据混乱问题。

example:

select

express_no, revenue, create_time

from user_settle_detail

where type = 1 AND delete_flag = 1

AND user_id = #{userId}

AND date_format(create_time, '%Y-%m-%d') = #{time}

order by create_time desc

limit #{offset}, #{pageSize}

官网解释：

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.

One factor that affects the execution plan is LIMIT, so an ORDER BY query with and without LIMIT may return rows in different orders.

1 解决方案： order by create_time desc，id asc

存在的问题：

order by 后面的字段如果在where 中存在则索引有效，如果不存在则索引失效。

如果在order by后面多加一个字段的话，会使creat_time上的索引失效

下面的例子中：

example:

还有一种方案：也能够解决这个问题

大家不要像我一样傻傻的掉进这个连续的坑里。