mysql - MySQL多列PRIMARY key数据透视表设计

Question

任何人都可以立即看出下面架构中的问题或瓶颈吗？读取占操作的 90%，但我想知道我是否在写入的任何地方搬起石头砸自己的脚。

命题

每个对象(另一个表中的行)都可以与其他对象相关联。每个关系对只能有一个记录(对是方向敏感的，因此 X 到 Y 可以与 共存Y 到 X)。

+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| a_id  | bigint(20) unsigned | NO   | PRI | NULL    |                |
| b_id  | bigint(20) unsigned | NO   | PRI | NULL    |                | 
+-------+---------------------+------+-----+---------+----------------+

典型的请求是获取所有相关对象(对于给定对象 A):

SELECT * FROM objects INNER JOIN relations ON id = b_id WHERE a_id = A

关系是在 UI 中使用一个简单的复选框数组来管理的。为了保存关系，我会计算当前集合中选中/未选中的差异(对象将在 UI 中分页)，然后相应地插入/删除；

DELETE FROM relations WHERE a_id = A AND b_id IN(B,C)

# if these relations already exist, will fail silently
INSERT IGNORE INTO relations (a_id, b_id) VALUES (A,D), (A,E)

我 可能 还需要查询反向关系，只使用 b_id 进行选择 - 因为它不在索引的左侧，所以它会被使用吗？如果不是，在其上添加单独的索引是否会导致写入的大量开销？

Answer 1

在 b_id 上添加第二个索引的优势将超过写入的任何开销，因为没有索引，它将需要进行全表扫描以按 b_id 进行过滤>.

至于只在 b_id 或 b_id, a_id 上建立索引取决于表引擎，因为 InnoDB 将主键存储在二级索引，但是 MyISAM 没有。