📚 课程目录第3章数据库设计

数据库设计：设备信息表、注册记录表、密钥表

数据库设计这事儿，说白了就是给设备身份找个靠谱的「家」。我见过不少项目，前期图省事把设备信息、密钥、注册日志全塞一张表里，结果上线三个月就开始卡，查个记录要扫全表。嗯，今天咱们就把这三张表拆明白。

  核心原则：设备信息表管「你是谁」，注册记录表管「你什么时候来过」，密钥表管「你用什么证明自己」。三表各司其职，互不干扰。

3.1 设备信息表（device_info）

这张表是设备身份的「户口本」。每台设备只对应一条记录，从出厂到报废都不变。我个人习惯把设备唯一标识（比如设备序列号或 MAC）设为主键，而不是用自增 ID——为什么？因为业务查询时永远是通过设备标识来找，用自增 ID 反而多一次索引查找。

CREATE TABLE device_info (
    device_id       VARCHAR(64)     NOT NULL COMMENT '设备唯一标识（SN/MAC）',
    product_key     VARCHAR(32)     NOT NULL COMMENT '产品型号标识',
    device_name     VARCHAR(128)    DEFAULT NULL COMMENT '设备别名（用户自定义）',
    device_status   TINYINT         NOT NULL DEFAULT 0 COMMENT '状态：0-未激活 1-在线 2-离线 3-已注销',
    firmware_ver    VARCHAR(32)     DEFAULT NULL COMMENT '当前固件版本',
    create_time     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '首次注册时间',
    update_time     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
    PRIMARY KEY (device_id),
    INDEX idx_product_key (product_key),
    INDEX idx_device_status (device_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备信息表';

我的经验：device_id 字段长度建议留够 64 位。之前有个客户用的设备 SN 是 48 位，加上前缀刚好超了 32 位，改表结构时差点把线上服务搞挂。你想想看，这种低级错误能避免就避免。

这里有个细节——device_status 我用的是 TINYINT 而不是 ENUM。为什么？因为 ENUM 在 MySQL 里加新值要 ALTER TABLE，生产环境锁表很要命。用 TINYINT 配合代码里的常量定义，扩展性会好很多。

3.2 注册记录表（device_register_log）

这张表记录设备每次注册的「案底」。注意，是每次。设备第一次上线、断网重连、更换证书后重新注册，都会在这里留一条记录。我曾经遇到一个坑：设备频繁重连导致注册日志暴涨，一个月写了 2 亿条。后来加了按天分表策略才解决。

CREATE TABLE device_register_log (
    id              BIGINT          NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    device_id       VARCHAR(64)     NOT NULL COMMENT '设备标识',
    register_type   TINYINT         NOT NULL COMMENT '注册类型：1-首次注册 2-重连注册 3-证书更新注册',
    source_ip       VARCHAR(45)     DEFAULT NULL COMMENT '设备注册时的IP地址（支持IPv6）',
    auth_method     VARCHAR(16)     DEFAULT 'PSK' COMMENT '认证方式：PSK/CERT/OAUTH',
    result          TINYINT         NOT NULL COMMENT '结果：0-失败 1-成功',
    fail_reason     VARCHAR(255)    DEFAULT NULL COMMENT '失败原因描述',
    create_time     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录生成时间',
    PRIMARY KEY (id),
    INDEX idx_device_id (device_id),
    INDEX idx_create_time (create_time),
    INDEX idx_result (result)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备注册记录表';

注意：这张表不要设唯一索引！同一台设备同一秒注册两次是完全可能的（比如网络抖动导致重试）。我见过有人给 device_id + create_time 加了唯一索引，结果线上频繁报 Duplicate Entry，排查了半天才发现是业务逻辑本身允许重复注册。

register_type 字段我建议保留。很多场景下，你需要区分「设备第一次来」和「设备又来了」。比如首次注册要触发初始化流程，重连注册只需要更新在线状态。没有这个字段，你就得去查历史记录，性能差很多。

3.3 密钥表（device_secret）

这张表存的是设备的「身份证密码」。注意，我从来不在设备信息表里直接放密钥字段。为什么？因为密钥有生命周期，要轮换、要过期、要吊销。单独一张表，管理起来清晰得多。

CREATE TABLE device_secret (
    id              BIGINT          NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    device_id       VARCHAR(64)     NOT NULL COMMENT '设备标识',
    secret_type     TINYINT         NOT NULL COMMENT '密钥类型：1-预共享密钥 2-证书指纹 3-临时令牌',
    secret_value    VARCHAR(512)    NOT NULL COMMENT '密钥值（加密存储）',
    secret_status   TINYINT         NOT NULL DEFAULT 1 COMMENT '状态：0-已吊销 1-有效 2-已过期',
    effective_time  DATETIME        NOT NULL COMMENT '生效时间',
    expire_time     DATETIME        DEFAULT NULL COMMENT '过期时间（NULL表示永不过期）',
    create_time     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_device_id (device_id),
    INDEX idx_secret_status (secret_status),
    INDEX idx_expire_time (expire_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备密钥表';

  安全提醒：secret_value 字段存的一定是加密后的密文，不是明文！我习惯用 AES-256-GCM 加密，密钥由独立的密钥管理服务（KMS）保管。数据库 DBA 能看到密文，但解不开，这才是最小权限原则。

你可能会问：为什么要有 effective_time 和 expire_time？举个例子，设备预置密钥的有效期是 5 年，到期后自动失效，设备必须通过 OTA 获取新密钥。没有时间字段，你就得写定时任务去扫表，麻烦不说，还容易漏。

3.4 三表关系与核心流程

这三张表怎么配合？我画了张图，你看一眼就明白了。

3.5 设计原则总结

说了这么多，我归纳几条硬性原则，你设计时照着来就行：

职责单一：一张表只干一件事。设备信息不存密钥，注册日志不存设备详情。
主键选择：业务主键（device_id）做主表主键，日志表用自增 ID 避免热点写。
索引策略：查询频繁的字段（device_id、status、create_time）一定要加索引，但不要滥用——索引不是越多越好，写性能会下降。
时间字段：每张表都保留 create_time 和 update_time，排查问题时你会感谢这个习惯。
预留扩展：VARCHAR 长度留余量，TINYINT 代替 ENUM，字段允许 NULL 时要有业务含义。

避坑指南：我曾经接手过一个项目，设备信息表里有个字段叫 extra_info，JSON 类型，里面塞了密钥、配置、标签、甚至日志。查询时要用 JSON_EXTRACT，性能惨不忍睹。后来花了两个通宵拆成三张表。你想想看，这种「万能字段」看着方便，其实是给自己挖坑。

好了，数据库设计这块就聊到这儿。这三张表搭好了，设备身份注册系统的地基就算打牢了。后面章节咱们会基于这个表结构，一步步把注册接口、密钥分发、状态同步这些逻辑串起来。