数据的顺序：默认值、性能、确定性与分页
摘要
本文解释了SQL查询中不使用ORDER BY时未定义的默认排序行为，通过PostgreSQL示例展示了插入顺序、更新和删除如何改变结果，并警告不要依赖默认排序进行分页。
<p><a href="https://lobste.rs/s/8rjl7o/order_data_defaults_performance">评论</a></p>
查看缓存全文
缓存时间: 2026/07/15 11:43
# 数据的顺序：默认值、性能、确定性与分页
来源：https://binaryigor.com/the-order-of-data.html
## 默认值
从以下查询中我们能得到什么结果？
``
SELECT * FROM some_table
LIMIT N OFFSET K;
``
当未指定排序顺序时，数据库如何决定顺序？这重要吗？何时重要？
### Postgres
使用表：
``
CREATE TABLE account (
id UUID PRIMARY KEY,
name TEXT NOT NULL,
created_at TIMESTAMP NOT NULL
);
``
插入一些数据：
``
INSERT INTO account (id, name, created_at) VALUES
('7de14515-4165-4c67-8fa0-f2effa9a812b', 'acc-a', '2026-07-02T16:25:22Z'),
('dd614095-b567-446a-97be-803c17efa25b', 'acc-b', '2026-07-02T16:25:22Z'),
('bd3ef1b1-6e98-4eb9-ba5a-35188a81d7f7', 'acc-c', '2026-07-02T16:25:22Z'),
('445b479f-ecb5-4877-ad52-eb6c8e9ea207', 'acc-d', '2026-07-02T16:25:22Z'),
('68714013-a360-4965-ab87-4c646e0a65cd', 'acc-e', '2026-07-02T16:25:22Z');
``
在 SQL 标准中，当未指定`ORDER BY`时，预期行为是未定义的 —— 完全取决于具体数据库的实现：
``
SELECT * FROM account
LIMIT 3;
id | name | created_at
--------------------------------------+-------+---------------------
7de14515-4165-4c67-8fa0-f2effa9a812b | acc-a | 2026-07-02 16:25:22
dd614095-b567-446a-97be-803c17efa25b | acc-b | 2026-07-02 16:25:22
bd3ef1b1-6e98-4eb9-ba5a-35188a81d7f7 | acc-c | 2026-07-02 16:25:22
SELECT * FROM account
LIMIT 3 OFFSET 3;
id | name | created_at
--------------------------------------+-------+---------------------
445b479f-ecb5-4877-ad52-eb6c8e9ea207 | acc-d | 2026-07-02 16:25:22
68714013-a360-4965-ab87-4c646e0a65cd | acc-e | 2026-07-02 16:25:22
``
对于 Postgres，默认顺序似乎是插入顺序。让我们通过删除并重新插入一些行来稍微打乱一下：
``
DELETE FROM account
WHERE id IN (
'7de14515-4165-4c67-8fa0-f2effa9a812b',
'bd3ef1b1-6e98-4eb9-ba5a-35188a81d7f7'
);
INSERT INTO account (id, name, created_at) VALUES
('7de14515-4165-4c67-8fa0-f2effa9a812b', 'acc-a', '2026-07-02T16:25:22Z'),
('bd3ef1b1-6e98-4eb9-ba5a-35188a81d7f7', 'acc-c', '2026-07-02T16:25:22Z');
``
再次运行分页查询：
``
SELECT * FROM account
LIMIT 3;
id | name | created_at
--------------------------------------+-------+---------------------
dd614095-b567-446a-97be-803c17efa25b | acc-b | 2026-07-02 16:25:22
445b479f-ecb5-4877-ad52-eb6c8e9ea207 | acc-d | 2026-07-02 16:25:22
68714013-a360-4965-ab87-4c646e0a65cd | acc-e | 2026-07-02 16:25:22
SELECT * FROM account
LIMIT 3 OFFSET 3;
id | name | created_at
--------------------------------------+-------+---------------------
7de14515-4165-4c67-8fa0-f2effa9a812b | acc-a | 2026-07-02 16:25:22
bd3ef1b1-6e98-4eb9-ba5a-35188a81d7f7 | acc-c | 2026-07-02 16:25:22
``
产生了不同的结果，但仍然按插入顺序排序。
如果我们更新一行呢？
``
UPDATE account
SET name = 'acc-bb'
WHERE id = 'dd614095-b567-446a-97be-803c17efa25b';
SELECT * FROM account
LIMIT 3;
id | name | created_at
--------------------------------------+-------+---------------------
445b479f-ecb5-4877-ad52-eb6c8e9ea207 | acc-d | 2026-07-02 16:25:22
68714013-a360-4965-ab87-4c646e0a65cd | acc-e | 2026-07-02 16:25:22
7de14515-4165-4c67-8fa0-f2effa9a812b | acc-a | 2026-07-02 16:25:22
SELECT * FROM account
LIMIT 3 OFFSET 3;
id | name | created_at
--------------------------------------+--------+---------------------
bd3ef1b1-6e98-4eb9-ba5a-35188a81d7f7 | acc-c | 2026-07-02 16:25:22
dd614095-b567-446a-97be-803c17efa25b | acc-bb | 2026-07-02 16:25:22
``
不出所料，它变成了最后一个 —— 在 PostgreSQL 中，UPDATE 实际上就是 DELETE + INSERT（https://binaryigor.com/mysql-and-postgresql-different-approaches.html）毕竟。
正如我们所看到的，依赖默认排序顺序相当脆弱。**看似是插入顺序，但并不能保证**——它可能在运行 VACUUM (https://www.postgresql.org/docs/current/sql-vacuum.html)、DELETE + INSERT、UPDATE 之后改变，甚至可能受所使用的索引以及其他一些我们尚未意识到的因素影响。
### MySQL
准备相同的模式和数据：
``
CREATE TABLE account (
-- 很遗憾，MySQL 仍然没有原生 UUID 类型！ --
id CHAR(36) PRIMARY KEY,
name VARCHAR(255) NOT NULL,
created_at TIMESTAMP NOT NULL
);
INSERT INTO account (id, name, created_at) VALUES
('7de14515-4165-4c67-8fa0-f2effa9a812b', 'acc-a', '2026-07-02T16:25:22'),
('dd614095-b567-446a-97be-803c17efa25b', 'acc-b', '2026-07-02T16:25:22'),
('bd3ef1b1-6e98-4eb9-ba5a-35188a81d7f7', 'acc-c', '2026-07-02T16:25:22'),
('445b479f-ecb5-4877-ad52-eb6c8e9ea207', 'acc-d', '2026-07-02T16:25:22'),
('68714013-a360-4965-ab87-4c646e0a65cd', 'acc-e', '2026-07-02T16:25:22');
``
它应该像 Postgres 一样工作：
``
SELECT * FROM account
LIMIT 3;
+--------------------------------------+-------+---------------------+
| id | name | created_at |
+--------------------------------------+-------+---------------------+
| 445b479f-ecb5-4877-ad52-eb6c8e9ea207 | acc-d | 2026-07-02 16:25:22 |
| 68714013-a360-4965-ab87-4c646e0a65cd | acc-e | 2026-07-02 16:25:22 |
| 7de14515-4165-4c67-8fa0-f2effa9a812b | acc-a | 2026-07-02 16:25:22 |
+--------------------------------------+-------+---------------------+
SELECT * FROM account
LIMIT 3 OFFSET 3;
+--------------------------------------+-------+---------------------+
| id | name | created_at |
+--------------------------------------+-------+---------------------+
| bd3ef1b1-6e98-4eb9-ba5a-35188a81d7f7 | acc-c | 2026-07-02 16:25:22 |
| dd614095-b567-446a-97be-803c17efa25b | acc-b | 2026-07-02 16:25:22 |
+--------------------------------------+-------+---------------------+
``
实际上并非如此。这是什么顺序？行似乎按`id`（主键）排序 —— 让我们验证一下：
``
SELECT id FROM account
ORDER BY id;
+--------------------------------------+
| id |
+--------------------------------------+
| 445b479f-ecb5-4877-ad52-eb6c8e9ea207 |
| 68714013-a360-4965-ab87-4c646e0a65cd |
| 7de14515-4165-4c67-8fa0-f2effa9a812b |
| bd3ef1b1-6e98-4eb9-ba5a-35188a81d7f7 |
| dd614095-b567-446a-97be-803c17efa25b |
+--------------------------------------+
``
确实如此。那么，像 Postgres 一样删除并重新插入一些行会发生什么？
``
DELETE FROM account
WHERE id IN (
'7de14515-4165-4c67-8fa0-f2effa9a812b',
'bd3ef1b1-6e98-4eb9-ba5a-35188a81d7f7'
);
INSERT INTO account (id, name, created_at) VALUES
('7de14515-4165-4c67-8fa0-f2effa9a812b', 'acc-a', '2026-07-02T16:25:22'),
('bd3ef1b1-6e98-4eb9-ba5a-35188a81d7f7', 'acc-c', '2026-07-02T16:25:22');
SELECT * FROM account
LIMIT 3;
+--------------------------------------+-------+---------------------+
| id | name | created_at |
+--------------------------------------+-------+---------------------+
| 445b479f-ecb5-4877-ad52-eb6c8e9ea207 | acc-d | 2026-07-02 16:25:22 |
| 68714013-a360-4965-ab87-4c646e0a65cd | acc-e | 2026-07-02 16:25:22 |
| 7de14515-4165-4c67-8fa0-f2effa9a812b | acc-a | 2026-07-02 16:25:22 |
+--------------------------------------+-------+---------------------+
SELECT * FROM account
LIMIT 3 OFFSET 3;
+--------------------------------------+-------+---------------------+
| id | name | created_at |
+--------------------------------------+-------+---------------------+
| bd3ef1b1-6e98-4eb9-ba5a-35188a81d7f7 | acc-c | 2026-07-02 16:25:22 |
| dd614095-b567-446a-97be-803c17efa25b | acc-b | 2026-07-02 16:25:22 |
+--------------------------------------+-------+---------------------+
``
如预期，顺序没有改变。那么对于更新应该也是如此：
``
UPDATE account
SET name = 'acc-bb'
WHERE id = 'dd614095-b567-446a-97be-803c17efa25b';
SELECT * FROM account
LIMIT 3;
+--------------------------------------+-------+---------------------+
| id | name | created_at |
+--------------------------------------+-------+---------------------+
| 445b479f-ecb5-4877-ad52-eb6c8e9ea207 | acc-d | 2026-07-02 16:25:22 |
| 68714013-a360-4965-ab87-4c646e0a65cd | acc-e | 2026-07-02 16:25:22 |
| 7de14515-4165-4c67-8fa0-f2effa9a812b | acc-a | 2026-07-02 16:25:22 |
+--------------------------------------+-------+---------------------+
SELECT * FROM account
LIMIT 3 OFFSET 3;
+--------------------------------------+--------+---------------------+
| id | name | created_at |
+--------------------------------------+--------+---------------------+
| bd3ef1b1-6e98-4eb9-ba5a-35188a81d7f7 | acc-c | 2026-07-02 16:25:22 |
| dd614095-b567-446a-97be-803c17efa25b | acc-bb | 2026-07-02 16:25:22 |
+--------------------------------------+--------+---------------------+
``
确实如此。
总结一下，**MySQL 的行为与 Postgres 不同 —— 默认排序顺序似乎是主键的顺序 (https://binaryigor.com/mysql-and-postgresql-different-approaches.html#clustered-indexes-vs-heap-tables)**（考虑到 MySQL 底层的运作方式，这非常合理）。但同样，没有保证！
### MongoDB
再次处理相同的数据：
``
db.createCollection("account");
db.account.insertMany([
{
_id: '7de14515-4165-4c67-8fa0-f2effa9a812b',
name: 'acc-a',
createdAt: '2026-07-02T16:25:22Z'
},
{
_id: 'dd614095-b567-446a-97be-803c17efa25b',
name: 'acc-b',
createdAt: '2026-07-02T16:25:22Z'
},
{
_id: 'bd3ef1b1-6e98-4eb9-ba5a-35188a81d7f7',
name: 'acc-c',
createdAt: '2026-07-02T16:25:22Z'
},
{
_id: '445b479f-ecb5-4877-ad52-eb6c8e9ea207',
name: 'acc-d',
createdAt: '2026-07-02T16:25:22Z'
},
{
_id: '68714013-a360-4965-ab87-4c646e0a65cd',
name: 'acc-e',
createdAt: '2026-07-02T16:25:22Z'
},
]);
``
检查默认排序：
``
db.account.find()
.limit(3);
[
{
_id: '7de14515-4165-4c67-8fa0-f2effa9a812b',
name: 'acc-a',
createdAt: '2026-07-02T16:25:22Z'
},
{
_id: 'dd614095-b567-446a-97be-803c17efa25b',
name: 'acc-b',
createdAt: '2026-07-02T16:25:22Z'
},
{
_id: 'bd3ef1b1-6e98-4eb9-ba5a-35188a81d7f7',
name: 'acc-c',
createdAt: '2026-07-02T16:25:22Z'
}
]
db.account.find()
.limit(3)
.skip(3);
[
{
_id: '445b479f-ecb5-4877-ad52-eb6c8e9ea207',
name: 'acc-d',
createdAt: '2026-07-02T16:25:22Z'
},
{
_id: '68714013-a360-4965-ab87-4c646e0a65cd',
name: 'acc-e',
createdAt: '2026-07-02T16:25:22Z'
}
]
``
似乎与 Postgres 类似；同样，让我们删除并重新插入一些文档：
``
db.account.deleteMany({
_id: {
$in: [
'7de14515-4165-4c67-8fa0-f2effa9a812b',
'bd3ef1b1-6e98-4eb9-ba5a-35188a81d7f7'
]
}
});
db.account.insertMany([
{
_id: '7de14515-4165-4c67-8fa0-f2effa9a812b',
name: 'acc-a',
createdAt: '2026-07-02T16:25:22Z'
},
{
_id: 'bd3ef1b1-6e98-4eb9-ba5a-35188a81d7f7',
name: 'acc-c',
createdAt: '2026-07-02T16:25:22Z'
}
]);
``
结果：
``
db.account.find()
.limit(3);
[
{
_id: 'dd614095-b567-446a-97be-803c17efa25b',
name: 'acc-b',
createdAt: '2026-07-02T16:25:22Z'
},
{
_id: '445b479f-ecb5-4877-ad52-eb6c8e9ea207',
name: 'acc-d',
createdAt: '2026-07-02T16:25:22Z'
},
{
_id: '68714013-a360-4965-ab87-4c646e0a65cd',
name: 'acc-e',
createdAt: '2026-07-02T16:25:22Z'
}
]
db.account.find()
.limit(3)
.skip(3);
[
{
_id: '7de14515-4165-4c67-8fa0-f2effa9a812b',
name: 'acc-a',
createdAt: '2026-07-02T16:25:22Z'
},
{
_id: 'bd3ef1b1-6e98-4eb9-ba5a-35188a81d7f7',
name: 'acc-c',
createdAt: '2026-07-02T16:25:22Z'
}
]
``
再次与 Postgres 相同；为了完整性，更新呢？
``
db.account.updateOne(
{ _id: 'dd614095-b567-446a-97be-803c17efa25b' },
{ $set: { name: 'acc-bb' } }
);
db.account.find()
.limit(3);
[
{
_id: 'dd614095-b567-446a-97be-803c17efa25b',
name: 'acc-bb',
createdAt: '2026-07-02T16:25:22Z'
},
{
_id: '445b479f-ecb5-4877-ad52-eb6c8e9ea207',
name: 'acc-d',
createdAt: '2026-07-02T16:25:22Z'
},
{
_id: '68714013-a360-4965-ab87-4c646e0a65cd',
name: 'acc-e',
createdAt: '2026-07-02T16:25:22Z'
}
]
db.account.find()
.limit(3)
.skip(3);
[
{
_id: '7de14515-4165-4c67-8fa0-f2effa9a812b',
name: 'acc-a',
createdAt: '2026-07-02T16:25:22Z'
},
{
_id: 'bd3ef1b1-6e98-4eb9-ba5a-35188a81d7f7',
name: 'acc-c',
createdAt: '2026-07-02T16:25:22Z'
}
]
``
嗯，这不一样 —— 更新不影响默认排序顺序。这很可能是因为 MongoDB 实现了与 PostgreSQL 不同的**多版本并发控制 (MVCC)** 机制 (https://binaryigor.com/mysql-and-postgresql-different-approaches.html#multiversion-concurrency-control-mvcc-dead-tuples-vs-undo-logs)。
**因此，在 Mongo 中，默认顺序似乎也是插入顺序（没有保证）。**
## 性能
让我们向 Postgres 插入更多数据 —— 10 000 000 行 —— 并测量性能影响：
``
\timing
Timing is on.
TRUNCATE account;
TRUNCATE TABLE
Time: 11.552 ms
INSERT INTO account (id, name, created_at)
SELECT gen_random_uuid(), concat('acc', '-', n), now()
FROM generate_series(1, 10_000_000) AS n;
INSERT 0 10000000
Time: 53447.537 ms (00:53.448)
``
默认顺序查询：
``
SELECT * FROM account
LIMIT 5;
id | name | created_at
--------------------------------------+-------+----------------------------
92fea9d0-90c8-4ad1-9a54-e7d2704e5d00 | acc-1 | 2026-07-06 16:47:50.142234
497013a6-cb59-42d3-9773-d894d31b4f1c | acc-2 | 2026-07-06 16:47:50.142234
14551457-ee7a-436a-95cc-14a70ad53f90 | acc-3 | 2026-07-06 16:47:50.142234
45fc2c83-3a20-4e94-ac46-08afc48e02d8 | acc-4 | 2026-07-06 16:47:50.142234
8e610251-a20b-4f50-a69e-88a68d665016 | acc-5 | 2026-07-06 16:47:50.142234
Time: 0.694 ms
``
非常快 —— 主键（显然已索引）顺序也是如此：
``
SELECT * FROM account
ORDER BY id
LIMIT 5;
id | name | created_at
--------------------------------------+-------------+----------------------------
00000230-f6e3-4e6e-8280-9cffe9a619f4 | acc-313672 | 2026-07-06 16:47:50.142234
0000063f-8dca-402b-988f-c1eaf7d388e4 | acc-4230905 | 2026-07-06 16:47:50.142234
000007e6-1db1-4338-a605-e33a91de8522 | acc-9824082 | 2026-07-06 16:47:50.142234
00000b71-f20c-481b-a92d-9911262dd4b8 | acc-8346475 | 2026-07-06 16:47:50.142234
00000fc5-6bbc-4c04-b55f-12bc55308f1a | acc-187020 | 2026-07-06 16:47:50.142234
Time: 0.457 ms
``
未索引的列呢？
``
SELECT * FROM account
ORDER BY name
LIMIT 5;
id | name | created_at
--------------------------------------+-----------+----------------------------
92fea9d0-90c8-4ad1-9a54-e7d2704e5d00 | acc-1 | 2026-07-06 16:47:50.142234
60d799ed-ecd5-4e65-a4a1-07de717034ee | acc-10 | 2026-07-06 16:47:50.142234
5065bb28-74de-4cca-b0b4-85835ecd49d1 | acc-100 | 2026-07-06 16:47:50.142234
e03187f3-f18e-42d1-b454-2ed545a91411 | acc-1000 | 2026-07-06 16:47:50.142234
dcbdd86f-e911-4709-a641-45ff476061e5 | acc-10000 | 2026-07-06 16:47:50.142234
Time: 479.251 ms
SELECT * FROM account
ORDER BY created_at
LIMIT 5;
id | name | created_at
--------------------------------------+-------+----------------------------
497013a6-cb59-42d3-9773-d894d31b4f1c | acc-2 | 2026-0
相似文章
欢迎来到 ORDER BY 丛林
解释 ORDER BY 在 SQL 中处理裸标识符与表达式时的细微差别，使用 PostgreSQL 示例展示由于不同的代码路径，看似相同的查询可能产生不同的结果。
SQL的ORDER BY已走过漫漫长路
本文追溯了SQL的ORDER BY子句从20世纪80年代到现代标准的演变，澄清了关于语法和求值顺序的常见误解。
SQLite 通过预排序提升性能
本文展示了在将随机数据插入 SQLite 之前进行预排序，可以利用 B+ 树的顺序特性并减少页分裂，从而将插入性能提升 2-3 倍。
深入解析Postgres内部：数据库集群、数据库与表
一篇探讨PostgreSQL内部机制的技术文章，涵盖数据库集群、数据库、表、系统目录和对象标识符（OID）的逻辑与物理结构。
Postgres by Example
一份使用带注释的SQL示例的PostgreSQL实践入门，涵盖从基础到高级主题。