默认情况下如何在MySql中对数据进行排序 [英] How Data is ordered in MySql by Default
问题描述
如果我在MySQL数据库中有1000行，我知道使用TOP或LIMIT可以使查询更快，但是我想知道，默认情况下数据库是如何排序的?
If i have 1000s of rows in MySQL database, i know using TOP or LIMIT makes query faster, but i want to know, how data is ordered is database by default?
以及默认情况下如何制作order by time或username，以便轻松快捷地使用TOP或LIMIT?
And how to make order by time or username by default so that using TOP or LIMIT is easy and faster?
推荐答案
在InnoDB 中，行以主键顺序存储.如果您不使用LIMIT而没有ORDER BY，则即使您以随机顺序插入行，也始终会获得具有最低主键值的行.
In InnoDB, rows are stored in primary key order. If you use LIMIT with no ORDER BY, you'll always get the rows with the lowest primary key values, even if you inserted them in random order.
create table foo (id int primary key, x char(1), y int) engine=InnoDB;
insert into foo values (5, 'A', 123);
insert into foo values (9, 'B', 234);
insert into foo values (2, 'C', 345);
insert into foo values (4, 'D', 456);
insert into foo values (1, 'E', 567);
select * from foo;
+----+------+------+
| id | x | y |
+----+------+------+
| 1 | E | 567 |
| 2 | C | 345 |
| 4 | D | 456 |
| 5 | A | 123 |
| 9 | B | 234 |
+----+------+------+
在MyISAM 中，行存储在适合的位置.最初，这意味着将行添加到数据文件中，但是当您删除行并插入新行时，已删除行留下的间隙将被新行重复使用.
In MyISAM, rows are stored wherever they fit. Initially, this means rows are appended to the data file, but as you delete rows and insert new ones, the gaps left by deleted rows will be re-used by new rows.
create table bar (id int primary key, x char(1), y int) engine=MyISAM;
insert into bar values (1, 'A', 123);
insert into bar values (2, 'B', 234);
insert into bar values (3, 'C', 345);
insert into bar values (4, 'D', 456);
insert into bar values (5, 'E', 567);
select * from bar;
+----+------+------+
| id | x | y |
+----+------+------+
| 1 | A | 123 |
| 2 | B | 234 |
| 3 | C | 345 |
| 4 | D | 456 |
| 5 | E | 567 |
+----+------+------+
delete from bar where id between 3 and 4;
insert into bar values (6, 'F', 678);
insert into bar values (7, 'G', 789);
insert into bar values (8, 'H', 890);
select * from bar;
+----+------+------+
| id | x | y |
+----+------+------+
| 1 | A | 123 |
| 2 | B | 234 |
| 7 | G | 789 | <-- new row fills gap
| 6 | F | 678 | <-- new row fills gap
| 5 | E | 567 |
| 8 | H | 890 | <-- new row appends at end
+----+------+------+
使用InnoDB的另一种例外情况是，您是从二级索引而不是一级索引中检索行.当您在EXPLAIN输出中看到正在使用索引"注释时，就会发生这种情况.
Another exception case if you use InnoDB is if you are retrieving rows from a secondary index instead of from the primary index. This happens when you see the "Using index" note in the EXPLAIN output.
alter table foo add index (x);
select id, x from foo;
+----+------+
| id | x |
+----+------+
| 5 | A |
| 9 | B |
| 2 | C |
| 4 | D |
| 1 | E |
+----+------+
如果使用联接有更复杂的查询，它将变得更加复杂，因为您将获得按访问的第一个表的默认顺序返回的行(其中第一个"取决于优化程序选择表的顺序) )，则联接表中的行将取决于上一个表中的行的顺序.
If you have more complex queries with joins, it gets even more complicated, because you'll get the rows returned by the default order of the first table accessed (where "first" is dependent on the optimizer choosing the order of tables), then rows from the joined table will be dependent on the order of rows from the previous table.
select straight_join foo.*, bar.* from bar join foo on bar.x=foo.x;
+----+------+------+----+------+------+
| id | x | y | id | x | y |
+----+------+------+----+------+------+
| 1 | E | 567 | 5 | E | 567 |
| 5 | A | 123 | 1 | A | 123 |
| 9 | B | 234 | 2 | B | 234 |
+----+------+------+----+------+------+
select straight_join foo.*, bar.* from foo join bar on bar.x=foo.x;
+----+------+------+----+------+------+
| id | x | y | id | x | y |
+----+------+------+----+------+------+
| 5 | A | 123 | 1 | A | 123 |
| 9 | B | 234 | 2 | B | 234 |
| 1 | E | 567 | 5 | E | 567 |
+----+------+------+----+------+------+
最重要的是，最好明确一点:使用LIMIT时，请指定ORDER BY.
The bottom line is that it's best to be explicit: when you use LIMIT, specify an ORDER BY.
这篇关于默认情况下如何在MySql中对数据进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
