MySQL 主键是否已经处于某种默认顺序

我刚刚在一个我刚刚开始使用的系统中偶然发现了几行我并不真正理解的代码。该系统有一个大表，可以保存大量具有唯一 ID 的实体，并在不再需要时将其删除，但绝不会重用它们。所以桌子看起来像这样

------------------------
| id |info1|info2|info3|
------------------------
| 1  | foo1| foo2| foo3|
------------------------
| 17 | bar1| bar2| bar3|
------------------------
| 26 | bam1| bam2| bam3|
------------------------
| 328| baz1| baz2| baz3|
------------------------
etc.

在代码库的一个地方有一个 while 循环，其目的是循环遍历数据库中的所有实体并对它们执行操作，现在可以这样解决

int lastId = fetchMaxId()
int id = 0
while (id = fetchNextId()){
  doStuffWith(id)
}

其中 fetchMaxId 是直接的

int fetchMaxId(){
  return sqlQuery("SELECT MAX(id) FROM Table")
}

但 fetchNextId 让我困惑。它的实现为

int fetchNextId(currentId, maxId){
  return sqlQuery("
    SELECT id FROM Table where id > :currentId and id <= :maxId LIMIT 1
  ")
}

这个系统已经投入生产好几年了，所以它显然是有效的，但是当我试图寻找一个解决方案来解释为什么它有效时，我只发现人们说了同样的话，我已经认为我知道了。 MySQL DB 返回结果的顺序不容易确定，也不应该依赖，因此如果您不想要特定的顺序，请使用 ORDER BY 子句。但有时您可以安全地忽略 ORDER BY 吗？该代码已运行 12 年，并在多次数据库更新后继续运行。我们只是幸运还是我在这里错过了什么？在我看到这段代码之前，我会说如果你打电话

fetchNextId(1, 328) 

你最终可能会得到 17 或 26 作为答案。

为什么这样做的一些线索可能是 id 列是相关表的主键，并且它设置为自动增量，但我找不到任何可以解释原因的文档

fetchNextId(1, 328)

当调用上面给出的表片段时，应该总是返回 17。

简短的回答是肯定的，主键有顺序，所有索引都有顺序，主键只是一个唯一索引。

正如您所说，您不应该依赖于按照数据存储的顺序返回数据，优化器可以自由地按照它喜欢的任何顺序返回数据，这将取决于查询计划。不过，我将尝试解释为什么您的查询已经有效了 12 年。

你的聚集索引只是你的表数据，你的聚集键定义了它的存储顺序。数据存储在叶子上，聚集键帮助根（和中间注释）充当指针来快速到达右叶检索数据。非聚集索引是一个非常相似的结构，但最低层仅包含一个指向聚集索引叶上正确位置的指针。

在 MySQL 中，主键和聚集索引是同义词，因此主键是有序的，但它们本质上是两个不同的东西。在其他 DBMS 中，您可以定义主键和聚集索引，当您执行此操作时，您的主键将成为唯一的非聚集索引，并带有返回聚集索引的指针。

用最简单的术语来说，您可以想象一个具有主键 ID 列和另一列 (A) 的表，您的聚集索引的 B 树结构将类似于：

Root Node
                                +---+
                                | 1 |
                                +---+
Intermediate Nodes

                    +---+       +---+       +---+
                    | 1 |       | 4 |       | 7 |
                    +---+       +---+       +---+

Leaf
            +-----------+   +-----------+   +-----------+
    ID ->   | 1 | 2 | 3 |   | 4 | 5 | 6 |   | 7 | 8 | 9 |
    A ->    | A | B | C |   | D | E | F |   | G | H | I |
            +-----------+   +-----------+   +-----------+

实际上，叶子页面会更大，但这只是一个演示。每个页面还有一个指向下一页和上一页的指针，以便于遍历树。因此，当您执行如下查询时：

SELECT ID, A
FROM T
WHERE ID > 5
LIMIT 1;

您正在扫描唯一索引，因此这很可能是顺序扫描。但很可能无法保证。

MySQL将扫描根节点，如果存在潜在的匹配，它将移动到中间节点，如果子句是这样的WHERE ID < 0那么 MySQL 就会知道没有任何结果，而无需超出根节点。

一旦它移动到中间节点，它就可以识别出需要从第二页（4 到 7 之间）开始搜索ID > 5。因此，它将从第二个叶子页开始顺序扫描叶子，已经识别出LIMIT 1一旦找到匹配项（在本例中为 6），它将停止并从叶子返回此数据。在这样一个简单的例子中，这种行为似乎是可靠且合乎逻辑的。我尝试通过选择一个我知道位于叶页末尾的 ID 值来强制异常，以查看叶页是否会以相反的顺序扫描，但到目前为止还无法产生这种行为，但这并不意味着它不会发生，或者 MySQL 的未来版本不会在我测试过的场景中执行此操作。

简而言之，只需添加一个 order by，或使用 MIN(ID) 即可完成。我不会浪费太多时间去深入研究查询优化器的内部工作原理，以了解需要什么样的碎片或数据范围来观察查询计划中聚集索引的不同顺序。