多重互相关联

这里实际上工作当中涉及到的数据库多重关联问题, 而且这是目前所有系统系统当中大概率会接触到的问题, 假设目前后台需要设计游戏邮件表处理游戏邮件发放, 这里初版表结构如下:

CREATE TABLE `game_mail_list`
(
    `id`           BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
    `server_id`    JSON                NOT NULL COMMENT '发送的服务器id',
    `player_id`    JSON                NOT NULL COMMENT '发送的玩家id',
    `type`         TINYINT(3) UNSIGNED NOT NULL COMMENT '邮件类型',
    `title`        VARCHAR(255)        NOT NULL COMMENT '邮件标题' COLLATE 'utf8mb4_unicode_ci',
    `content`      LONGTEXT COMMENT '邮件内容' COLLATE 'utf8mb4_unicode_ci',
    `item`         LONGTEXT COMMENT '附件内容',
    `create_time`  INT(10) UNSIGNED    NOT NULL COMMENT '创建时间',
    `push_time`    INT(10) UNSIGNED    NOT NULL DEFAULT '0' COMMENT '推送时间',
    `start_time`   INT(10) UNSIGNED    NOT NULL DEFAULT '0' COMMENT '展示开始时间',
    `end_time`     INT(10) UNSIGNED    NOT NULL DEFAULT '0' COMMENT '展示结束时间',
    `delete_time`  INT(10) UNSIGNED    NOT NULL DEFAULT '0' COMMENT '软删除时间',
    `update_uid`   BIGINT(20) UNSIGNED NOT NULL DEFAULT '0' COMMENT '操作者',
    `check_status` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT '审核状态: 0 代表等待审核, 1代表审核通过,2代表审核拒绝',
    `check_uid`    BIGINT(20) UNSIGNED NOT NULL DEFAULT '0' COMMENT '审核处理人',
    `check_time`   INT(10) UNSIGNED    NOT NULL DEFAULT '0' COMMENT '审核时间',
    PRIMARY KEY (`id`) USING BTREE
) COMMENT ='玩家邮件列表'
    COLLATE = 'utf8mb4_unicode_ci'
    ENGINE = InnoDB
    ROW_FORMAT = DYNAMIC;

十分简单的表, 直接记录后台创建对应 服务器ID+玩家ID 即可, 基本上满足前期推送游戏邮件需求.

但是后续项目开始慢慢成型, 开始进一步要求: 允许多选发给玩家, 我曾经见过比较奇葩的设计就是采用逗号分隔记录, 比如:

CREATE TABLE `game_mail_list`
(
    `id`         BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
    `player_ids` VARCHAR(255)        NOT NULL COMMENT '推送的玩家们' COLLATE 'utf8mb4_unicode_ci',

    # 其他略
    PRIMARY KEY (`id`) USING BTREE
) COMMENT ='玩家邮件列表'
    COLLATE = 'utf8mb4_unicode_ci'
    ENGINE = InnoDB
    ROW_FORMAT = DYNAMIC;


# 示范插入多玩家
INSERT INTO `game_mail_list`(`player_ids`)
VALUES ('10001,10002,10003');

# 查询获取, 模糊查询
SELECT *
FROM `game_mail_list`
WHERE `player_ids` LIKE '%10001%'

这是我见过最可怕的实现, 抛弃索引检索并且会出现取值覆盖的问题.

取值覆盖: 假如查询 10001 玩家, 如果后续玩家数量增到到 100010 会一起被查询出来, 就像下面查询语句一样：

# 注意以下 players 内容, 里面完全不含 10001
SELECT *
FROM (SELECT "100010,100012,100014" AS `players`) AS t
WHERE `players` LIKE '%10001%';

如果你查询 10001 玩家, 那么不命中的数据会直接被匹配查询出来, 完全没办法检索准确数据.

分组处理

这时候你想到了分组方案, 追加关系分组表让其关联起来:

# 创建单向关联玩家邮件
CREATE TABLE `game_mail_relationship`
(
    `mail_id`   BIGINT(20) UNSIGNED NOT NULL COMMENT '邮件主键',
    `player_id` BIGINT(20) UNSIGNED NOT NULL COMMENT '推送的玩家ID',

    # 其他略
    PRIMARY KEY (`mail_id`, `player_id`) USING BTREE
) COMMENT ='玩家邮件关联'
    COLLATE = 'utf8mb4_unicode_ci'
    ENGINE = InnoDB
    ROW_FORMAT = DYNAMIC;


# 那么检索指定玩家只需要连表JOIN或者先查询一次获取 `mail_id` 处理即可:
SELECT `mail_id`
FROM `game_mail_relationship`
WHERE `player_id` = 1001;

# 之后查询出对应的关联邮件id
SELECT *
FROM `game_mail_list`
WHERE `id` IN (1, 2, 3, 4)

而且查询过程触发主键索引直接让查询速度飞快, 这样看起来 好像还不错? 但这里有个最大的 雷区 直接搞垮后续数据检索.

这里如果数据样本很少的情况, 比如只有比较少玩家邮件只需要查追加 IN(1,3,4) 就行了, 但是需要注意游戏邮件功能是平台利用频率最高的情况, 后续经年累月不断发送给玩家的情况, mail_id 是指数性保障的情况.

比如后台运营给玩家发送 上万条邮件, 那么 game_mail_relationship(玩家邮件关联表) 会追加上万条如下数据:

# 建立关联1
INSERT INTO `game_mail_relationship`
VALUES (1, 10001);

# 建立关联2
INSERT INTO `game_mail_relationship`
VALUES (2, 10001);

# 以此类推增长

这样看起来不能直接利用先取 mail_id 之后 IN(1,2,3) 处理了, 那么采用改用 JOIN 直接能实现, 虽然抛弃索引但是至少功能完备.

多JOIN连表

当你兴高采烈利用单个 JOIN 关联查询数据表的情况, 新的需求又来: 关联后台角色分组, 查询的玩家角色绑定指定批次玩家;

这种请求有的是合理的, 很多时候推广游戏的时候会填写推广码, 然后该推广码关联到后台管理员某个比如 客服 角色, 这个客服角色登录该后台只能查询到自己绑定的玩家数据, 避免让其接触到不属于自己的玩家数据.

这种时候又多出一张玩家后台推广员关联玩家id表:

# 创建单向关联玩家邮件
CREATE TABLE `game_admin_relationship`
(
    `admin_id`  BIGINT(20) UNSIGNED NOT NULL COMMENT '管理员主键',
    `player_id` BIGINT(20) UNSIGNED NOT NULL COMMENT '推送的玩家ID',

    # 其他略
    PRIMARY KEY (`admin_id`, `player_id`) USING BTREE
) COMMENT ='玩家客服关联'
    COLLATE = 'utf8mb4_unicode_ci'
    ENGINE = InnoDB
    ROW_FORMAT = DYNAMIC;

那么恐怖的就来了, 客服查询关联自己的邮件需要更长的 JOIN:

# 连表两表JOIN, 这里语法不一定对, 但是中心思想就是需要双连表
SELECT *
FROM `game_admin_relationship` as `admin`
         LEFT JOIN `game_mail_relationship` as `mail`
                   ON `admin`.`player_id` = `mail`.`player_id`
         LEFT JOIN `game_mail_list` as `m`
                   ON `admin`.`player_id` = `m`.`player_id`

看起来勉强能用, 但是实际上邮件查询是十分复杂, 涉及到 审核状态|创建时间|审核时间|推送时间 等多字段复杂混合查询.

后续又追加新需求, 要求追加 渠道id 筛选, 该渠道是指定批次来源玩家做分组, 然后又得去这个数据子集分组查询指定玩家.

这时候连表抛弃索引全表检索的情况, 配合上复杂条件筛选和分页情况, 直接把数据检索效率拉到最低; 后续如果数据庞大的情况效率更加差; 这种情况下的数据检索等于直接对数据大量内存临时筛选匹配.

目前工作这么多年的情况, 最复杂的情况就是对于玩家数据集需要横跨 八张表+七个条件 做分页查询, 那时候处理这种情况的恶心程度至今历历在目, 明明知道很低效的查询和代码为了业务统统让开.

MeteorCat / 多重互相关联

多重互相关联

分组处理

多JOIN连表