物联网平台MySQL：消息推送系统如何用分表方案解决百万用户广播难题

erDiagram
    device_users ||--o{ user_notification_relation : "接收"
    notification_content ||--o{ user_notification_relation : "关联"
    notification_types ||--o{ notification_content : "分类"

INSERT INTO notification_content (sender_id, type_id, title, content, send_time)
VALUES (1, 1, '设备告警', '车间3号温度传感器温度过高', NOW());

INSERT INTO user_notification_relation (notification_id, user_id, create_time)
VALUES (LAST_INSERT_ID(), 2, NOW());

INSERT INTO notification_content (sender_id, type_id, title, content, send_time)
VALUES (0, 3, '系统维护通知', '今晚22:00-24:00系统维护', NOW());

INSERT INTO user_notification_relation (notification_id, user_id, create_time)
SELECT LAST_INSERT_ID(), id, NOW() FROM device_users;

SELECT 
  nc.id AS notification_id,
  nc.title,
  nc.content,
  nc.send_time,
  unr.is_read,
  unr.read_time
FROM notification_content nc
JOIN user_notification_relation unr 
  ON nc.id = unr.notification_id
WHERE unr.user_id = 2
  AND nc.is_delete = false
  AND unr.is_delete = false
ORDER BY nc.send_time DESC;

-- 创建设备用户表
-- 存储物联网平台的用户基础信息
CREATE TABLE device_users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '用户编号，系统内唯一标识',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '登录账号，用户登录时使用',
    nickname VARCHAR(50) COMMENT '用户昵称，界面显示用',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间'
);

-- 创建通知类型表
-- 管理物联网平台中的各种消息类型
CREATE TABLE notification_types (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '类型编号',
    type_name VARCHAR(50) NOT NULL UNIQUE COMMENT '类型名称，如设备告警、系统通知等'
);

-- 创建消息内容表
-- 存储所有消息的具体内容，不区分接收者
CREATE TABLE notification_content (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '消息编号，全局唯一',
    sender_id BIGINT NOT NULL COMMENT '发送者编号，关联用户表',
    type_id INT NOT NULL COMMENT '消息类型编号',
    title VARCHAR(255) NOT NULL COMMENT '消息标题',
    content TEXT NOT NULL COMMENT '消息具体内容',
    send_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '发送时间',
    is_delete BOOLEAN DEFAULT false COMMENT '是否删除标记',
    INDEX idx_sender_id (sender_id) COMMENT '发送者索引，提高按发送者查询效率',
    INDEX idx_type_id (type_id) COMMENT '消息类型索引',
    INDEX idx_send_time (send_time) COMMENT '发送时间索引，用于时间排序',
    FOREIGN KEY (type_id) REFERENCES notification_types(id)
);

-- 创建用户消息关系表
-- 记录每个用户收到的消息及其状态
CREATE TABLE user_notification_relation (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '关系记录编号',
    notification_id BIGINT NOT NULL COMMENT '消息编号',
    user_id BIGINT NOT NULL COMMENT '接收用户编号',
    is_read BOOLEAN DEFAULT false COMMENT '是否已读，默认未读',
    read_time DATETIME COMMENT '阅读时间，用户点击查看时记录',
    is_delete BOOLEAN DEFAULT false COMMENT '用户是否删除此消息',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '关系创建时间',
    INDEX idx_notification_user (notification_id, user_id) COMMENT '消息用户联合索引',
    INDEX idx_is_read (is_read) COMMENT '已读状态索引，快速查询未读消息',
    INDEX idx_create_time (create_time) COMMENT '创建时间索引',
    FOREIGN KEY (notification_id) REFERENCES notification_content(id),
    FOREIGN KEY (user_id) REFERENCES device_users(id)
);