如何为存在全量关联场景的两个超大型表建模多对多关系？

这种“大部分用户关联全部物品”的场景确实很棘手，直接用传统中间表会导致存储爆炸。我来分享几个经过实践验证的优化方案，包括通用SQL策略和数据库特定实现：

一、通用SQL优化方案 #

1. 反向存储例外数据 #

核心思路是只记录用户不拥有的物品，而非所有拥有的物品。这样当90%以上的用户拥有全部物品时，中间表的行数会骤减到仅存少数例外记录。

表结构设计：

创建user_excluded_items表，仅存储用户排除的物品对：

CREATE TABLE user_excluded_items (
    user_id INT NOT NULL,
    item_id INT NOT NULL,
    PRIMARY KEY (user_id, item_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (item_id) REFERENCES items(item_id)
);

查询逻辑：

要获取某个用户拥有的物品，只需从所有物品中排除该用户标记为“不拥有”的条目：

SELECT i.*
FROM items i
WHERE NOT EXISTS (
    SELECT 1
    FROM user_excluded_items uei
    WHERE uei.user_id = 123 -- 目标用户ID
      AND uei.item_id = i.item_id
);

这个方案的优势是存储成本极低，查询逻辑也相对清晰。但要注意：如果后续例外用户/物品的比例超过30%，这个方案的效率会下降，需要重新评估。

2. 分层分组权限策略 #

如果用户可以按权限分组（比如“默认全拥有组”、“自定义权限组”），可以通过用户-组-物品的三层关系来减少关联数：

表结构设计：

-- 用户分组表
CREATE TABLE user_groups (
    user_id INT NOT NULL,
    group_id INT NOT NULL,
    PRIMARY KEY (user_id, group_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- 组-物品关联表
CREATE TABLE group_items (
    group_id INT NOT NULL,
    item_id INT NOT NULL,
    PRIMARY KEY (group_id, item_id),
    FOREIGN KEY (item_id) REFERENCES items(item_id)
);

逻辑实现：

• 创建一个默认组（比如group_id=0），将所有物品关联到这个组，大部分用户直接加入该组。
• 少数需要自定义权限的用户，加入专属组，仅关联他们拥有的物品（或排除的物品，结合上面的反向策略）。

查询逻辑：

SELECT DISTINCT i.*
FROM items i
JOIN group_items gi ON i.item_id = gi.item_id
JOIN user_groups ug ON gi.group_id = ug.group_id
WHERE ug.user_id = 123;

这个方案适合有多种批量权限规则的场景，扩展性更强。

二、数据库引擎特定实现 #

1. PostgreSQL：数组类型+GIN索引 #

PostgreSQL对数组类型的支持非常友好，可以在users表中直接存储用户排除的物品ID数组：

表结构调整：

ALTER TABLE users ADD COLUMN excluded_item_ids INT[] DEFAULT '{}';
-- 为数组创建GIN索引，加速查询
CREATE INDEX idx_users_excluded_items ON users USING GIN (excluded_item_ids);

查询逻辑：

SELECT i.*
FROM items i
JOIN users u ON u.user_id = 123
WHERE NOT i.item_id = ANY(u.excluded_item_ids);

数组类型的存储效率很高，GIN索引也能保证查询速度，适合例外物品数量较少的场景。

2. MySQL：JSON类型存储例外 #

MySQL 5.7+支持JSON字段，可以用类似的思路存储排除的物品ID：

表结构调整：

ALTER TABLE users ADD COLUMN excluded_items JSON DEFAULT '[]';

查询逻辑：

SELECT i.*
FROM items i
WHERE NOT EXISTS (
    SELECT 1
    FROM users u
    WHERE u.user_id = 123
      AND JSON_CONTAINS(u.excluded_items, CAST(i.item_id AS JSON), '$')
);

注意：MySQL的JSON索引性能不如PostgreSQL的数组索引，更适合例外数量极少的场景。

3. Redis：位图/集合辅助查询 #

如果需要超高并发的查询性能，可以用Redis做缓存层：

• 位图方案：如果物品ID是连续整数，每个用户对应一个位图，位值1表示拥有，0表示排除。默认位图全为1，仅修改例外的位。100万物品的位图仅占125KB，存储成本极低。
• 集合方案：为每个例外用户创建一个集合，存储他们排除的物品ID。查询时先检查Redis中是否存在该集合，不存在则返回所有物品，存在则排除集合中的ID。

这个方案能极大减轻数据库的查询压力，适合高并发场景。

三、方案选择建议 #

• 如果例外比例极低（<10%）：优先选择「反向存储例外数据」或数据库特定的数组/JSON方案。
• 如果存在批量权限分组：选择「分层分组策略」，扩展性更强。
• 如果需要超高查询性能：结合Redis位图/集合做缓存。

相比你提到的is_owned_by_all_users字段，以上方案都能避免查询逻辑分支复杂、数据分散的问题，同时大幅降低存储和索引成本。

内容的提问来源于stack exchange，提问作者Kathandrax