没有主键时，InnoDB会有什么行为？

This afternoon, Arjen Lentz and I were discussing InnoDB’s behavior without a declared PRIMARY KEY, and the topic felt interesting enough and undocumented enough to warrant its own short post.

今天下午，Arjen Lentz和我讨论了没有声明主键的InnoDB的行为，这个话题足够有趣且不够记录，值得写一篇短文来讨论。

Background on InnoDB clustered keys

InnoDB聚簇键的背景

In The physical structure of InnoDB index pages I described how “Everything is an index in InnoDB”. This means that InnoDB must always have a “cluster key” for each table, which is normally the PRIMARY KEY. The manual has this to say in Clustered and Secondary Indexes:

在InnoDB索引页的物理结构中，我描述了“在InnoDB中，一切都是索引”。这意味着InnoDB对于每个表都必须有一个“聚簇键”，通常是主键。手册在聚簇和二级索引中有这样的说明：

If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

如果表没有主键或适当的唯一索引，InnoDB会在一个包含行ID值的合成列上生成一个隐藏的聚集索引。行按照InnoDB为这样的表中的行分配的ID进行排序。行ID是一个6字节的字段，随着插入新行而单调递增。因此，按照行ID排序的行在物理上是按照插入顺序排列的。

I had previously assumed this meant that an invisible column would be used along with the same sequence generation code that is used to implement auto_increment (which itself has some scalability issues). However the reality is that they are completely different implementations.

我之前认为这意味着将使用一个不可见的列以及与实现auto_increment相同的序列生成代码（它本身也有一些可扩展性问题）。然而，事实是它们是完全不同的实现。

Implementation of implicit Row IDs

隐式行ID的实现

How this is actually implemented is, as the manual says, if a table is declared with no PRIMARY KEY and no non-nullable UNIQUE KEY, InnoDB will automatically add a 6-byte (48-bit) integer column called ROW_ID to the table, and cluster the data based on that column. The column won’t be accessible to any queries nor usable for anything internally such as row-based replication.

实际上，这是如何实现的，正如手册所说，如果一个表没有声明主键和非空唯一键，InnoDB会自动为该表添加一个名为ROW_ID的6字节（48位）整数列，并根...