mysql排序查询原理分析

业务场景

数据表aa

字段:id,(jpa-uuid),标签名tag(noraml索引)，标签排序sort_number,层级编码level_code,父id parent,创建时间等基本字段。

后台实体参考

在 JPA 中使用 Hibernate 的 uuid（uuid: 采用128位的uuid算法生成主键，uuid被编码为一个32位16进制数字的字符串。占用空间大（字符串类型）） 。GeneratedValue默认配置，如果不指定主键生成策略，默认为AUTO。

 

业务说明：标签树列表展示，根据排序号排序，如果排序号一样，早的在上，晚的在下。

后台查询代码,jpaQuery.selectFrom(xx).orderBy(xx.sortNumber.asc())；

查询效果：

发现在标签修改时，如果排序号改为之前已有的，并未使用时间排序代码，也会出现排序号一样，早的在上，晚的在下，why?

试了下如果不加ordeyby语句，查询出来并未按排序号排序。

测试如果修改了创建时间和id信息，排序依旧没变，这就无法达到排序号一样按时间排序。

原因分析

参考没有 ORDER BY 子句的 SELECTS 的默认排序顺序是什么？

https://forums.mysql.com/read.php?21,239471,239688#msg-239688

SELECT * FROM tbl -- this will do a "table scan". If the table has never had any DELETEs/REPLACEs/UPDATEs, the records will happen to be in the insertion order, hence what you observed.

If you had done the same statement with an InnoDB table, they would have been delivered in PRIMARY KEY order, not INSERT order. Again, this is an artifact of the underlying implementation, not something to depend on.

如果您对 InnoDB 表执行相同的语句，它们将按 PRIMARY KEY 顺序而不是 INSERT 顺序交付。但是，not something to depend on。

查看mysql当前默认的存储引擎:

mysql> show variables like '%storage_engine%';

看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎):

mysql> show create table 表名(aa);

发现是InnoDB。

mysql官网 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.”

翻译--

“如果多行在 ORDER BY 列中具有相同的值，则服务器可以自由地以任何顺序返回这些行，并且可能会根据整体执行计划以不同的方式返回。 换句话说，这些行的排序顺序相对于无序的列是不确定的。”

总结

排序字段必须加排序条件。

补充：

关于mysql如何保存单条记录，mysql查询数据库读取数据的顺序，有待研究