什么是“默认订单依据"?省略了Order by子句的mysql Innodb查询? [英] What is the "Default order by" for a mysql Innodb query that omits the Order by clause?
问题描述
因此，我理解并发现了一些帖子，这些帖子表明在从DBMS检索数据时，不建议在SQL查询中省略order by子句.
So i understand and found posts that indicates that it is not recommended to omit the order by clause in a SQL query when you are retrieving data from the DBMS.
资源和已咨询帖子(将更新):
> SQL Server UNION-默认的ORDER是什么通过行为
问题:
如果您想了解更多信息，请参见下面问题的逻辑.
See logic of the question below if you want to know more.
我的问题是:在具有innoDB引擎的mysql下，有人知道DBMS如何有效地为我们提供结果吗?
My question is : under mysql with innoDB engine, does anyone know how the DBMS effectively gives us the results ?
我了解到它是依赖于实现的，可以，但是对于我当前的实现，有没有办法知道它?
I read that it is implementation dependent, ok, but is there a way to know it for my current implementation ?
此确切定义在哪里?
是MySQL，InnoDB和OS依赖的吗?
Is it from MySQL, InnoDB , OS-Dependent ?
那里没有某种清单吗?
Isn't there some kind of list out there ?
最重要的是，如果我省略order by子句并得到结果，我不能确定此代码是否仍可以在较新的数据库版本中使用，并且DBMS永远不会给我相同的结果，可以我吗?
用例&逻辑:
我当前正在编写一个CRUD API，我的数据库中有一个不包含"id"字段(尽管有PK)的表，所以当我显示该表的结果时，没有任何研究标准，我真的不知道应该使用什么来排序结果.我的意思是，我可以使用PK或永不为null的任何字段，但不会使其具有相关性.所以我想知道，因为我的CRUD应该适用于任何表，并且我不想通过为此特定表添加异常来解决此问题，所以我也可以简单地忽略order by子句.
I'm currently writing a CRUD API, and i have table in my DB that doesn't contain an "id" field (there is a PK though), and so when i'm showing the results of that table without any research criteria, i don't really have a clue on what i should use to order the results. I mean, i could use the PK or any field that is never null, but it wouldn't make it relevant. So i was wondering, as my CRUD is supposed to work for any table and i don't want to solve this problem by adding an exception for this specific table, i could also simply omit the order by clause.
最终注释:
在阅读其他文章，示例和代码示例时，我感觉自己想走得太远.我了解，众所周知，在请求中忽略Order By子句是一种不好的做法，并且没有可靠的默认order子句，更不用说除非您指定了它，否则根本没有定单.
As i'm reading other posts, examples and code samples, i'm feeling like i want to go too far. I understand that it is common knowledge that it's just a bad practice to omit the Order By clause in a request and that there is no reliable default order clause, not to say that there is no order at all unless you specify it.
我只是想知道它的定义位置，并且希望了解它在内部或至少在定义的位置(DBMS/存储引擎/取决于OS/其他/多个条件)如何工作.我认为这也将使其他人受益，并了解这里的内部机制.
I'd just love to know where this is defined, and would love to learn how this works internally or at least where it's defined (DBMS / Storage Engine / OS-Dependant / Other / Multiple criteria). I think it would also benefit other people to know it, and to understand the inners mechanisms in place here.
感谢您抽出宝贵时间来阅读！祝你有美好的一天.
Thanks for taking the time to read anyway ! Have a nice day.
推荐答案
在没有明确的ORDER BY的情况下，InnoDB的当前版本会按其读取的索引顺序返回行.哪个索引有所不同，但总是从某个索引读取.即使从表"中读取数据实际上也是一个索引，它是主键索引.
Without a clear ORDER BY, current versions of InnoDB return rows in the order of the index it reads from. Which index varies, but it always reads from some index. Even reading from the "table" is really an index—it's the primary key index.
与上面的评论一样，不能保证在下一版本的InnoDB中将保持相同.您应该将其视为一种偶然行为，没有记录在案，MySQL的制造商也不保证不会对其进行更改.
As in the comments above, there's no guarantee this will remain the same in the next version of InnoDB. You should treat it as a coincidental behavior, it is not documented and the makers of MySQL don't promise not to change it.
即使它们的实现没有改变，按索引顺序读取也会导致一些您可能不会想到的奇怪效果，并且不会给您有意义的查询结果集.
Even if their implementation doesn't change, reading in index order can cause some strange effects that you might not expect, and which won't give you query result sets that makes sense to you.
例如，默认索引是聚簇索引PRIMARY.这意味着索引顺序与主键中值的顺序相同(而不是您插入它们的顺序).
For example, the default index is the clustered index, PRIMARY. It means index order is the same as the order of values in the primary key (not the order in which you insert them).
mysql> create table mytable ( id int primary key, name varchar(20));
mysql> insert into mytable values (3, 'Hermione'), (2, 'Ron'), (1, 'Harry');
mysql> select * from mytable;
+----+----------+
| id | name |
+----+----------+
| 1 | Harry |
| 2 | Ron |
| 3 | Hermione |
+----+----------+
但是，如果您的查询使用另一个索引来读取表，例如，如果您仅访问二级索引的列，则会按以下顺序获得行:
But if your query uses another index to read the table, like if you only access column(s) of a secondary index, you'll get rows in that order:
mysql> alter table mytable add key (name);
mysql> select name from mytable;
+----------+
| name |
+----------+
| Harry |
| Hermione |
| Ron |
+----------+
这表明它正在通过使用name上的二级索引的索引扫描来读取表:
This shows it's reading the table by using an index-scan of that secondary index on name:
mysql> explain select name from mytable;
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | mytable | index | NULL | name | 83 | NULL | 3 | Using index |
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
在更复杂的查询中，预测InnoDB将用于给定查询的索引变得非常棘手.随着数据的变化，选择甚至可能每天都在变化.
In a more complex query, it can become very tricky to predict which index InnoDB will use for a given query. The choice can even change from day to day, as your data changes.
所有这些都表明:如果您关心查询结果集的顺序，应该只使用ORDER BY！
All this goes to show: You should just use ORDER BY if you care about the order of your query result set!
这篇关于什么是“默认订单依据"?省略了Order by子句的mysql Innodb查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
