设想
假设我们有一个table
用户和一个item
用户。这两个表有一个关联表,用于user_item
定义many to many
关系。
我们开始100条
item
记录我们有 5 亿条
user
记录。因此我们必须生成 50_000_000_000
user_item
(500 亿)我们可能有更多
分片或分区都不容易,因为这样会减慢任何其他操作(否则我们需要扫描所有内容)
假设查询模式 (
INSERT
,SELECT
,UPDATE
) 是基本/典型的 m2m 模式(可以在任何教程或示例中找到)
问题
无论模式如何,处理数据库中数十亿个多对多关系的最佳设计或已知解决方案是什么?
架构
想象一下这个简单的模式
CREATE DATABASE IF NOT EXISTS `playground` CHARACTER SET = latin1;
USE playground;
CREATE TABLE IF NOT EXISTS `user`
(
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`),
INDEX `user__name_fk` (`name`)
) ENGINE = InnoDB
DEFAULT CHARSET = latin1
ROW_FORMAT = DYNAMIC;
CREATE TABLE IF NOT EXISTS `item`
(
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`),
INDEX `user__name` (`name`)
) ENGINE = InnoDB
DEFAULT CHARSET = latin1
ROW_FORMAT = DYNAMIC;
CREATE TABLE IF NOT EXISTS `user_item`
(
`user_id` BIGINT UNSIGNED NOT NULL,
`item_id` BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (`user_id`, `item_id`),
INDEX `user_item__item` (`item_id`),
FOREIGN KEY `user_id_fk` (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE,
FOREIGN KEY `item_id_fk` (`item_id`) REFERENCES `item` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB
DEFAULT CHARSET = latin1
ROW_FORMAT = DYNAMIC;
-- create some default items
INSERT INTO `item` (`name`) VALUES ('item_1'), ('item_2'), ('item_3'), ('item_4'), ('item_5'), ('item_6'), ('item_7'), ('item_8'), ('item_9'), ('item_10');
-- create some users
INSERT INTO `user` (`name`) VALUES ('user_1'), ('user_2'), ('user_3'), ('user_4'), ('user_5'), ('user_6'), ('user_7'), ('user_8'), ('user_9'), ('user_10');
INSERT INTO `user_item` (`user_id`, `item_id`) VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5), (1, 6), (1, 7), (1, 8), (1, 9), (1, 10);
更多信息
我并不是在问如何在 MySQL 中使用多对多关系,我知道这一点。我是在问当相关记录的数量呈指数增长到如此大的规模时,针对扩展问题最广为人知的解决方案是什么。
另外,我故意没有添加任何查询模式(INSERT
、、),因为SELECT
这是不相关的。假设最/典型的 M2M 模式。我不想失去对有关扩展和大量数据的真正问题的关注。UPDATE
一定有一些技巧或一些已知的解决方法,对吗?我也在考虑 NoSQL 数据库,因此答案可能包括与 MySQL(或任何 SQL 数据库)无关的任何内容,
我觉得这应该是许多大公司都会面临的一个常见问题,因此应该有一个(或几个)通用的解决方案。这个问题的根本原因是,虽然 MySQL 非常适合创建关系,但它会成倍地增加关联的 m2m 表。
5 亿 x 100 == 500 亿只是一个例子。但理论上可能会发生。
澄清
- 我故意省略了查询,因为您可以假设最简单的查询。
- 我确信如果我举了几个例子,就会开始对特定查询进行流行优化,这不是问题
- 我问的是一个非常高层次的问题,如果没有真正已知的解决方案,那么解释为什么就足够了(假设是正确的)
这是一个简单的多对多查询的示例。
SELECT user.*, item.* FROM user
LEFT JOIN user_item ON user.id = user_item.user_id
LEFT JOIN item ON item.id = user_item.item_id
WHERE user.name = 'user_1';
类似但不一样的问题