Mysql8.0 数据按照时间排序不稳定引发的思考

一 、mysql order by 原理

内部排序

指的是待排序记录存放在计算机随机存储器中进行的排序过程,也就是排序全部在服务器内存中进行。

思考：mysql 的排序过程全部是在内存中进行的吗。

外部排序

指的是待排序记录的数量很大，以致内存一次不能容纳全部记录，在排序过程中尚需对外存进行访问的排序过程。

不稳定排序

对于mysql 来说

排序这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数

sort_buffer_size。sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

mysql sort_buffer 排序使用了快速排序，快速排序是一种不稳定排序， 在时间相同的时候，不能保证每次排序数据位置相同。

二、根据时间排序发生的问题

数据更新前
数据更新之后 id 623 排到末尾

虽然根据时间排序规则，返回的结果没有逻辑问题，但对于用户来说就是消息列表更新之后， 消息顺序发生变动。
这里需要进一步深入mysql 源码理解 整个排序过程的细节，为什么更新操作之后，排序顺序会变

分析完问题 接下来 看看解决问题的几种思路

增加排序字段

根据时间和id 排序，这样的排序会保证了结果的稳定性，但是对于mysql来说， 排序是一个比较耗费资源的操作，本来一个排序字段可以完成的操作尽量不要增加排序字段。

精确publishTime

mysql8.0 中 的datetime 字段默认小数后0位， 那么智能精确到秒，增加小数点后位数 6 精确到微秒。

批量写入数据表的时候 使用 now(6) 来生成微秒的时间数据。

如果是java 中entity 中赋值时间字段，则需要获取微秒时间。

MYSQL 获取微秒时间

now() 只能返回精确到秒的时间

获取精确到毫秒可以使用now（3）
精确到微秒 now(6)

mysql8.0 仅支持微秒

SELECT
	NOW( ) AS A,
	NOW( 3 ) AS b,
	NOW( 6 ) AS c;

java 中获取微秒时间

获取系统时间毫秒：

 public Date() {
        this(System.currentTimeMillis());
    }

一般系统时间精确到毫秒可以有效区分，但是遇到高并发系统，就需要进一步精确到微秒了
1毫秒=1000微秒=1000纳秒

获取微秒：

Date.from(Instant.now().truncatedTo(ChronoUnit.MICROS))

改变ChronoUnit.MICROS 可以选择返回的时间精度

java.time中的类解析为nanoseconds，比旧的日期时间类和Joda-Time使用的milliseconds更精细。

时钟实施

在jdk11 中

Instant

对象可以提供精确到纳秒的时间，而且是线程安全的。

可以通过

  System.out.println(ZonedDateTime.now(ZoneId.of("America/Montreal")));
  System.out.println(Clock.system(ZoneId.of("Asia/Shanghai")).instant());
  System.out.println(Date.from(Clock.system(ZoneId.of("Asia/Shanghai")).instant()));

获取精确时间。

mysql和java 获取的时间都是基于所在服务器时间，如果遇到时间不一致问题，先确保unix 服务器时间精确。