mysql - MySQL 如何确定 INSERT 是否唯一？

Question

我想知道在对具有定义为 UNIQUE 的任何列的表执行 INSERT 之前是否运行了隐式 SELECT。我在 INSERT 的文档中找不到任何关于此的信息。

我问了一些似乎没有人能够回答的其他问题——也许是因为我没有正确解释自己——与上述问题有关。

如果我理解正确，那么我认为以下内容是正确的:

案例 1:
您有一个包含 10 亿行的表。每行都有一个唯一的 UUID 列。如果您执行插入，服务器必须执行某种隐式 SELECT COUNT(*) FROM table WHERE UUID = [new uuid]并确定计数是 0 还是 1。正确吗？

案例 2:
您有一个包含 10 亿行的表。每行都有一个由 DATE 和 UUID 组成的复合唯一键。如果您执行插入，服务器必须执行某种隐式 SELECT COUNT(*) FROM table WHERE DATE = [date] AND UUID = [new uuid]并检查计数是 0 还是 1。是吗？

我使用隐式这个词是因为在某个时刻，在过程中的某个地方，服务器必须检查该值。如果不是，它将要求物理定律规定不能存在两个相同的行 - 据我所知，当涉及到以二进制形式写下的数字的唯一性时，物理不会发挥重要作用计算机中的磁盘。

让我们假设您的 10 亿行在 2,000 个不同的日期中按顺序均匀分布。这是否意味着案例 2 会更快地执行插入，因为它可以查找分割成日期的 UUID？如果不是，那么将案例 1 用于插入速度会更好 - 在这种情况下，为什么？

这个问题是理论上的，所以在这种情况下不要考虑常规 SELECT 性能。主键不会是 UUID+DATE 索引。

作为对评论的回应:在我的案例中，UUID 仅用于避免由于连接不良而导致重复条目的目的。由于您不能为不同的日期创建两次相同的条目(在逻辑上它没有成为新条目)，因此 UUID 不需要全局唯一 - 它只需要每个日期唯一。这就是为什么我可以允许它成为组合键的一部分。

Answer 1

前面的回答有一些缺陷和误解；我不会指出它们，而是从头开始。

仅指 InnoDB...

一个 INDEX(包括 UNIQUE 和 PRIMARY KEY)是一个 BTree。 BTrees 非常有效，可以根据 BTree 排序的键定位一行。 (按键顺序扫描也很有效。)MySQL 中典型 BTree 的“扇出”大约为 100。因此，对于一百万行，BTree 大约有 3 级深(log100(million) );对于一万亿行，它只有两倍深(大约)。因此，即使没有缓存任何内容，只需点击 3 次磁盘即可在百万行索引中找到一个特定行。

我在这里对“索引”与“表”感到松散，因为它们本质上是相同的(至少在 InnoDB 中)。两者都是 BTrees。不同之处在于叶节点中的内容:表 BTree 的叶节点具有所有列。 (我忽略了 InnoDB 中 TEXT/BLOB 的块外存储。)一个 INDEX(除了 PRIMARY KEY)在叶节点中有一个 PRIMARY KEY 的副本。这就是辅助键可以从 INDEX BTree 获取到行的其余列的方式，以及 InnoDB 不必存储所有列的多个副本的方式。

PRIMARY KEY 与数据“聚集”在一起。即一个 BTree 包含所有行的所有列，并且它根据 PRIMARY KEY 规范进行排序。

通过 PRIMARY KEY 定位记录是一种 BTree 搜索。通过 SECONDARY KEY 定位记录是两次 BTree 搜索，一次在次要 INDEX 的 BTree 中为您提供 PRIMARY KEY；然后第二个钻取数据/PK BTree。

PRIMARY KEY(UUID)... 由于 UUID 非常随机，您插入的“下”行将位于“随机”位置。如果表比buffer_pool中缓存的大很多，那么新行需要进入的块很可能不会被缓存。这导致磁盘命中将块拉入缓存(缓冲池)，并最终导致另一个磁盘命中将其写回磁盘。

由于 PRIMARY KEY 是 UNIQUE KEY，因此同时发生了其他事情(无 SELECT COUNT(*) 等)。在获取块之后和决定是否给出“重复键”错误或存储行之前，检查 UNIQUEness。另外，如果块是“满的”，那么块将需要“拆分”以便为新行腾出空间。

INDEX(UUID) 或 UNIQUE(UUID)... 该索引有一个 BTree。在 INSERT 上，一些随机定位的块需要被获取、修改、可能拆分并写回磁盘，非常类似于上面的 PK 讨论。如果您有 UNIQUE(UUID)，还会检查 UNIQUEness 和可能的错误消息。在任何一种情况下，现在和/或以后都有磁盘 I/O。

AUTO_INCREMENT PK... 如果 PRIMARY KEY 是 auto_increment，则新记录将添加到数据 BTree 中的“最后一个”块。当它变满时(每 100 条左右的记录)，(逻辑上)会进行块拆分并将旧块刷新到磁盘。 (实际上，I/O 可能会延迟并在后台完成。)

PRIMARY KEY(id) + UNIQUE(UUID) ... 两个 BTree。在 INSERT 上，两者都有事件。这可能比简单的 PRIMARY KEY(UUID) 更糟糕。把上面的磁盘命中加起来看看我的意思。

“磁盘命中”是大表中的杀手，尤其是 UUID。 “计算磁盘命中数”以了解性能，尤其是在比较两种可能的技术时。

现在为您的秘方... PRIMARY KEY(date, UUID)... 您允许相同的 UUID 在两个不同的日子出现。这可以帮助!回到 PK 的工作原理和检查 UNIQUEness ... 在插入记录时检查“复合”索引(日期，UUID)的唯一性。记录按日期+UUID 排序，因此今天的所有记录都聚集在一起。如果(这可能是一个很大的 IF)一天的数据适合缓冲池(但整个表不适合)，那么这就是每天早上发生的事情......插入突然将新记录添加到“结束”由于新的“日期”表。这些插入是在新日期内随机发生的。 buffer_pool 中的块被推送到磁盘，为新块腾出空间。但是，很好，您看到的是流畅、快速的 INSERT。这与您在 PRIMARY KEY(UUID) 中看到的不同，在检查 UNIQUEness 之前，许多行必须等待磁盘读取。今天的所有块都保持缓存状态，您不必等待 I/O。

但是，如果您变得太大以至于无法在缓冲池中容纳一天的数据，事情就会开始放缓，首先是在一天结束时，然后随着 INSERT 频率的增加，它会越来越早地蔓延。

顺便说一下，PARTITION BY RANGE(date) 和 PRIMARY KEY(uuid, date) 有一些相似的特性。 (是的，我故意翻转了 PK 列。)