mysql - "SELECT * FROM table_name;"的 MySQL 行顺序是什么？

Question

假设向 MySQL 数据库发出以下查询:

SELECT * FROM table_name;

请注意，没有 ORDER BY 子句。

我的问题是:

MySQL 是否保证结果集行的排列顺序？

更具体地说，我可以假设行将按插入顺序返回吗？即行插入表中的顺序相同。

Answer 1

不，没有任何保证。除非您使用 ORDER BY 子句指定顺序，否则顺序完全取决于内部实现细节。 IE。 RDBMS 引擎最方便的。

在实践中，行可能以其原始插入顺序(或更准确地说是行在物理存储中的存在顺序)返回，但您不应依赖于此。如果您将您的应用程序移植到另一个品牌的 RDBMS，或者即使您升级到可能以不同方式实现存储的较新版本的 MySQL，行可能会以其他顺序返回。

后一点适用于任何符合 SQL 的 RDBMS。

---

更新:在实践中，InnoDB 默认按照从索引中读取行的顺序返回行，因此顺序取决于优化器使用的索引。根据查询中的列和条件，它可能会选择不同的索引。

下面是一个使用 InnoDB 的演示:我创建一个表并插入行，这样我插入的值的顺序与主键的顺序相反。

CREATE TABLE foo (id SERIAL PRIMARY KEY, bar CHAR(10), baz CHAR(10), KEY(bar));

INSERT INTO foo (bar, baz) VALUES
  ('test5', 'test5'), ('test5', 'test5'),
  ('test4', 'test4'), ('test4', 'test4'), 
  ('test3', 'test3'), ('test3', 'test3'), 
  ('test2', 'test2'), ('test2', 'test2'), 
  ('test1', 'test1'), ('test1', 'test1');

默认情况下，如果没有使用索引，则行按主键顺序返回，因为它们是从聚集索引(主键)中读取的。

select * from foo;
+----+-------+-------+
| id | bar   | baz   |
+----+-------+-------+
|  1 | test5 | test5 |
|  2 | test5 | test5 |
|  3 | test4 | test4 |
|  4 | test4 | test4 |
|  5 | test3 | test3 |
....

但是如果我们使用一个使用索引的查询，它会按照该索引的顺序读取行。请注意，当存在联系时，联系的索引条目按主键升序存储。这是它们返回的顺序。

select * from foo where bar between 'test2' and 'test4';
+----+-------+-------+
| id | bar   | baz   |
+----+-------+-------+
|  7 | test2 | test2 |
|  8 | test2 | test2 |
|  5 | test3 | test3 |
|  6 | test3 | test3 |
|  3 | test4 | test4 |
|  4 | test4 | test4 |
+----+-------+-------+

---

使用不同的存储引擎意味着不同的实现，默认顺序可能不同。在 MyISAM 的情况下，行按照它们创建的顺序存储。

下面是我的意思的演示:

CREATE TABLE foo (id SERIAL PRIMARY KEY, bar CHAR(10));

-- create rows with id 1 through 10
INSERT INTO foo (bar) VALUES
  ('testing'), ('testing'), ('testing'), ('testing'), ('testing'), 
  ('testing'), ('testing'), ('testing'), ('testing'), ('testing');

DELETE FROM foo WHERE id BETWEEN 4 AND 7;

+----+---------+
| id | bar     |
+----+---------+
|  1 | testing |
|  2 | testing |
|  3 | testing |
|  8 | testing |
|  9 | testing |
| 10 | testing |
+----+---------+

现在我们有六行。此时的存储包含第 3 行和第 8 行之间的间隙，删除中间行后留下。删除行不会对这些间隙进行碎片整理。

-- create rows with id 11 through 20 
INSERT INTO foo (bar) VALUES
  ('testing'), ('testing'), ('testing'), ('testing'), ('testing'), 
  ('testing'), ('testing'), ('testing'), ('testing'), ('testing');

SELECT * FROM foo;

+----+---------+
| id | bar     |
+----+---------+
|  1 | testing |
|  2 | testing |
|  3 | testing |
| 14 | testing |
| 13 | testing |
| 12 | testing |
| 11 | testing |
|  8 | testing |
|  9 | testing |
| 10 | testing |
| 15 | testing |
| 16 | testing |
| 17 | testing |
| 18 | testing |
| 19 | testing |
| 20 | testing |
+----+---------+

注意 MySQL 在将新行附加到表末尾之前如何重新使用通过删除行打开的空间。另请注意，第 11 行到第 14 行以相反的顺序插入这些空间，从末尾向后填充。

因此，行的存储顺序与插入顺序不完全相同。