具有唯一子集的 MySQL 复合索引

Question

我有以下一对一链接表

CREATE TABLE `foo_bar` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `foo_id` int(10) unsigned NOT NULL,
  `bar_id` int(10) unsigned NOT NULL,
  …
  PRIMARY KEY (`id`),
  …
) ENGINE=InnoDB 

其中 foo_id 和 bar_id 分别是表 foo 和 bar 的外键

还有

• (foo_id, bar_id) 上的唯一索引，用于将 foo_id 映射到 foo 记录的外键，以及查询性能，和...
• bar_id 上的唯一索引，用于从bar_id 到bar 记录的外键映射

注意此设置允许非唯一的 foo_id，只要它们伴随着唯一的 bar_id

---

我现在想单独对 foo_id 强制执行唯一性，同时保留 (foo_id, bar_id) 上现有复合索引的性能优势

有没有什么方法可以创建一个唯一的复合索引，在其中对引用的列的子集强制执行唯一性，但允许将更多的列添加到索引中？

在此示例中，这看起来像 ([foo_id], bar_id)，其中方括号表示复合索引的唯一部分

我想避免在 (foo_id, bar_id) 上单独索引的开销

在我的脑海中，我想不出任何算法原因为什么这不可能 - 只要列的唯一子集位于复合索引的开头

注意，我知道我可以使用 foo_id 或 bar_id 作为表的主键，但应用程序需要一个单独的ID，可以在不泄露链接的情况下单独删除链接记录

基本上 foo_bar.id 存储在客户端 cookie 中(在被可逆加密后)，任何个人数据都可以从这个 ID 访问。这个想法是，如果客户端请求删除，我们会删除 foo_bar 记录并删除它们的数据，而不会破坏 foo 和 bar 中的匿名或功能数据>

这有一个额外的好处，即使 cookie 在任何地方持续存在，它也将不再可解析为任何数据

Answer 1

这是一个多对多映射表？

如果id 则摆脱;它很困惑并且会减慢速度。

PRIMARY KEY(foo_id, bar_id),
INDEX(bar_id, foo_id)

那些和更多提示:http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

This has the added bonus that, even if the cookie persists anywhere, it will no longer be resolvable to any data

这意味着执行以下操作之一:

• 保留所有创建的散列，但将一些散列标记为“已删除”。
• 否则请确保您永远不会两次生成相同的散列。

无论哪种情况，您都可以避免将旧 cookie“解析”为已失效或消失的数据。

这多少暗示了 UNIQUE 的存在(或 PRIMARY )在某处输入 foo_id 的键并且，分开，bar_id .

如果这两个唯一性约束在同一个表中并且您需要一个单独的 id由于其他原因，则最小索引为:

PRIMARY KEY(id),
UNIQUE(foo_id),
UNIQUE(bar_id)

或

PRIMARY KEY(foo_id),
UNIQUE(bar_id),
INDEX(id)   -- This is sufficient to keep `AUTO_INCREMENT` happy.

不需要在 UNIQUE 上有任何索引(普通或 (foo_id, bar_id) )因为第一个的唯一性约束足以保证对的唯一性和高效查找。

foo_bar.id is encrypted and stored in a client cookie ... client requests deletion, we delete the foo_bar record

什么样的加密？单向(md5、shar256 等)？还是可逆的(aes ...)？如果是单向的，那么您需要在某处映射 CONCAT(foo_id, bar_id) 的索引(或无论你在做什么)到foobar_id .