关系型存储（MySQL）：元数据表设计、ER建模、索引策略

聊到关系型存储，MySQL 肯定是绕不过去的坎。

我自己维护过一个图片元数据系统，每天写入量在千万级别。刚开始表结构设计得随意，结果查询越来越慢，一个简单的标签搜索跑了十几秒。没办法，回头重新搞 ER 建模、加索引，才把查询压到 50 毫秒以内。

说白了，元数据存储的成败，就看表结构和索引怎么设计。

ER 模型：从需求到表结构的第一步

ER 模型，说人话就是“实体-关系图”。你想想看，元数据本质上就是描述“谁拥有什么资源”这件事。设计 ER 模型的核心就三步：

• 找实体：哪些东西独立存在？例如用户、文件、标签、资源池。
• 定属性：实体需要哪些特征？例如文件的大小、创建时间、MD5。
• 画关系：实体之间怎么连接？例如一个文件可以挂多个标签，一个用户拥有多个文件。

举个例子，一个简单的图片元数据系统：

实体：image（图片）、tag（标签）、user（用户）
关系：image 与 tag 是 n:n，image 与 user 是 n:1

ER 图画清楚之后，物理表的结构基本就定型了。我个人习惯先画 ER 图再建表，而不是一边写 SQL 一边想关系。

表结构设计的实践经验

先把上面那个例子转成 MySQL 表定义。

CREATE TABLE user (
    user_id    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username   VARCHAR(64) NOT NULL,
    email      VARCHAR(256),
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE image (
    image_id   BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    INT UNSIGNED NOT NULL,
    file_name  VARCHAR(255) NOT NULL,
    file_size  BIGINT UNSIGNED NOT NULL,
    md5        CHAR(32) NOT NULL,
    width      INT UNSIGNED,
    height     INT UNSIGNED,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES user(user_id),
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE tag (
    tag_id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tag_name VARCHAR(64) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE image_tag (
    image_id BIGINT UNSIGNED NOT NULL,
    tag_id   INT UNSIGNED NOT NULL,
    PRIMARY KEY (image_id, tag_id),
    FOREIGN KEY (image_id) REFERENCES image(image_id),
    FOREIGN KEY (tag_id) REFERENCES tag(tag_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

嗯，这里要注意几点：

• 主键类型：image 表用 BIGINT 而不是 INT，因为图片数量一旦过千万，INT 很快就不够用。我有一次就是吃了这个亏，上线半年后不得不停机改表结构。
• 字符集：统一用 utf8mb4，因为元数据里可能携带表情符号或特殊字符。否则插入时直接报错。
• 外键与索引：外键列必须加独立索引，否则关联查询性能会崩。你想想看，外键本身是用于约束，不建索引 MySQL 做 JOIN 时得全表扫描。

索引策略的黄金法则

索引不是越多越好。多了写慢，少了读慢。

我常用的索引策略可以总结成下面这张表：

场景	推荐索引类型	注意事项
等值查询（WHERE user_id = ?）	B-tree 单列索引	选择率高时才有效
范围查询（WHERE created_at > ?）	B-tree 单列索引	索引顺序要与查询条件一致
多条件组合（WHERE user_id AND created_at）	联合索引 (user_id, created_at)	最左前缀原则
模糊查询（WHERE tag_name LIKE '前缀%'）	B-tree 单列索引	前缀搜索有效，后缀搜索无效
排序（ORDER BY created_at DESC）	索引排序 / 覆盖索引	加索引列排序可以避免 filesort

针对上面的 image 表，我们看看常见的查询怎么写索引：

-- 按用户查图片，按时间排序
CREATE INDEX idx_user_time ON image (user_id, created_at);

-- 按 MD5 查图片（等值查询）
CREATE UNIQUE INDEX idx_md5 ON image (md5);

为什么联合索引顺序是 user_id 在前，created_at 在后？因为查询条件里 user_id 是等值，created_at 是范围。按照最左前缀的原则，把等值的列放前面，范围列放后面，这样索引才能生效。

我当初在设计 image_tag 表时踩过一个坑：

-- 错误示例
CREATE TABLE image_tag (
    image_id BIGINT UNSIGNED NOT NULL,
    tag_id   INT UNSIGNED NOT NULL,
    PRIMARY KEY (image_id, tag_id)
) ENGINE=InnoDB;

-- 查询：找出某个标签下的所有图片
SELECT * FROM image_tag WHERE tag_id = 123;

这条 SQL 走不了主键索引，因为 tag_id 不在索引的最左列，结果就是全表扫描。解决方法很简单：加一个反向索引。

ALTER TABLE image_tag ADD INDEX idx_tag_image (tag_id, image_id);

记住，多对多关联表的索引要建两条路线：一条为主键路线，一条为反向查询路线。

元数据表设计的核心逻辑

把上面讲的内容串起来，元数据存储的核心逻辑可以用下面这张图来总结。

流程很清楚：从需求分析开始，画 ER 图，转成物理表，再根据查询场景加索引，最后持续优化。每一步踩过的坑，都是下一轮少踩的雷。