表结构设计

上一节我选定了 MySQL + Redis 的方案。接下来要做的第一件事，就是设计数据库表结构。

说来惭愧，这是我第一次用关系型数据库做正式项目。之前写 SQLite 的时候，表都是拍脑袋建的——想到什么字段就加什么，完全没有什么”设计”可言。这次我想认真一点。

从最简单的表开始

我的第一版表结构，现在回头看，简直惨不忍睹：

CREATE TABLE url_mapping (
    short_code VARCHAR(10) PRIMARY KEY,
    long_url VARCHAR(255) NOT NULL
);

就两列。当时我觉得这就够了——短链接服务不就是 short_code → long_url 的映射吗？

我把这个表建好，跑了几个测试用例，觉得挺完美。直到第二天有人给我发了一条消息：“你那个短链接服务，好像处理不了带中文参数的 URL？”

我一看日志，果然——有个用户提交了一个包含中文查询参数的 URL，long_url 的值长这样：

https://www.example.com/search?q=%E4%BD%A0%E5%A5%BD%E4%B8%96%E7%95%8C&category=%E7%A7%91%E6%8A%80&page=1&sort=relevance&filter=last_week&lang=zh-CN&utm_source=wechat&utm_medium=social&utm_campaign=2026_spring_promotion&ref=shared_link_token_xyz_abc123

URL 编码后的中文特别长。这条 URL 已经快 300 字符了，而我设的 VARCHAR(255) 直接截断，存进去的数据是残缺的。

这是我踩的第一个坑。

第一个修正：long_url 的长度

我赶紧去查了一下，URL 到底能有多长。

实际上，HTTP 规范本身没有限制 URL 长度，但不同的浏览器和服务器有自己的限制：IE 限制 2083 字符，Chrome 理论上支持超过 2MB，但大多数服务器（Nginx 默认 4KB-8KB）会截断过长的请求。

综合考虑，我把 long_url 改成了 VARCHAR(2048)：

CREATE TABLE url_mapping (
    short_code VARCHAR(10) PRIMARY KEY,
    long_url VARCHAR(2048) NOT NULL
);

2048 这个数字不是拍脑袋的——它覆盖了绝大多数合法 URL，同时不会像 TEXT 类型那样产生溢出页存储，影响查询性能。

加入更多字段

光有 short_code 和 long_url，服务跑起来没问题，但我很快就发现管理起来很痛苦。

比如用户反馈说”我的链接打不开了”，我无从查起——不知道是哪天创建的，不知道是谁创建的，也不知道这个链接被访问了多少次。

于是我加了一堆字段：

CREATE TABLE url_mapping (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    short_code VARCHAR(10) NOT NULL COMMENT '短链接码，6位Base62',
    long_url VARCHAR(2048) NOT NULL COMMENT '原始长URL',
    user_id BIGINT UNSIGNED DEFAULT NULL COMMENT '用户ID，未登录用户为NULL',
    click_count INT UNSIGNED DEFAULT 0 COMMENT '累计点击次数',
    today_clicks INT UNSIGNED DEFAULT 0 COMMENT '今日点击数',
    status TINYINT UNSIGNED DEFAULT 1 COMMENT '状态：1-正常，0-禁用，2-过期',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    expires_at TIMESTAMP DEFAULT NULL COMMENT '过期时间，NULL表示永不过期',
    UNIQUE KEY uk_short_code (short_code)
);

每个字段都有它被加入的理由。

id 是自增主键。short_code 虽然也可以当主键，但 BIGINT 自增主键对 InnoDB 的 B+ 树索引更友好——主键长度越短，每个数据页能存更多的行，磁盘 I/O 更少。而且用 short_code 做唯一索引已经保证了它的唯一性，不需要再让主键承担这个责任。

user_id 设为可空（DEFAULT NULL），因为我的服务允许未登录用户创建短链接。这一开始就有争议——有人说不登录的用户凭什么享受免费服务？但我坚持保留，因为降低使用门槛是获取用户的关键。

click_count 和 today_clicks 是两个统计字段。click_count 是总点击数，today_clicks 是今日点击数。其实把今日点击数放在主表里，现在想想有点多余，后来我用 Redis 的计数器替代了它。但第一版先这样，能跑就行。

status 用 TINYINT 存状态。一开始我只设了 0 和 1 两个值，后来加了 2（过期），再后来还考虑过加 3（审核中）。用数字而不是 ENUM，是因为 ENUM 每加一个值都要 ALTER TABLE，对线上服务不友好。

expires_at 是过期时间，NULL 表示永不过期。这个字段加上之后，我就可以写一个定时任务，定期扫描过期的链接并更新状态。

索引的设计

表结构定下来之后，我开始加索引。

short_code 上的唯一索引没有悬念——每个短链接必须唯一，查询也要靠它：

UNIQUE KEY uk_short_code (short_code)

long_url 上的索引费了一些心思。这个索引用于创建短链接时检查”这个长 URL 是不是已经存在了”。但 VARCHAR(2048) 太长了，直接建完整索引，索引体积会爆炸。

我的做法是前缀索引：

KEY idx_long_url (long_url(255))

只索引前 255 个字符。你可能会问：如果两个 URL 的前 255 个字符一样，后面不一样怎么办？确实，前缀索引在这种情况下会返回多个匹配行，需要在应用层做二次比对。但实际上，绝大多数 URL 的差异都出现在前 255 个字符以内（毕竟域名 + 路径部分通常不会太长），所以前缀索引的区分度已经足够高。

然后是 user_id 和 created_at 的索引：

KEY idx_user_id (user_id)
KEY idx_expires_at (expires_at)

user_id 索引用于”用户查看自己创建的所有短链接”这个查询场景。expires_at 索引用于定时任务扫描过期链接。

status 的索引我当时也加了，但后来发现大部分链接都是 status = 1，这个索引的区分度极低，查询优化器基本不会用。属于建了个寂寞，后来被我删掉了。这个教训让我学到了一点：不是所有查询条件都需要索引，要看区分度。

字符集：一个差点踩的坑

建表的时候，我在字符集上犹豫了一下。

MySQL 默认的字符集是 latin1，如果你不显式指定，所有的中文、Emoji 都会变成乱码。我一开始没注意这个，直到有一天测试的时候发现，带 Emoji 的 URL 注释全都变成了问号。

吓得我赶紧改成了 utf8mb4：

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

这里又有一个坑：MySQL 的 utf8 不是真正的 UTF-8！它只支持最多 3 字节的字符，而 Emoji 和部分生僻字需要 4 字节。必须用 utf8mb4 才是完整的 UTF-8。utf8mb4_unicode_ci 是排序规则，ci 表示 case-insensitive（大小写不敏感），对 URL 查询来说更友好。

还好我在项目早期就发现了这个问题。如果等到数据量大了再改字符集，那才是真正的噩梦——需要导出、转换、重新导入，还要处理索引长度变化。

点击日志表

短链接服务只有一张映射表当然不够。我很快发现用户想看详细统计数据：“这个链接今天有多少人点？从哪里点的？用什么设备的？”

这些需求超出了主表 click_count 字段的能力范围。我需要一个点击日志表：

CREATE TABLE url_click_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    short_code VARCHAR(10) NOT NULL,
    click_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(45) NOT NULL COMMENT 'IPv4/IPv6',
    user_agent VARCHAR(500) COMMENT '用户代理',
    referer VARCHAR(1000) COMMENT '来源页面',
    country VARCHAR(50) COMMENT '国家',
    province VARCHAR(50) COMMENT '省份',
    city VARCHAR(50) COMMENT '城市',
    device_type VARCHAR(20) COMMENT '设备类型：mobile/pc/tablet',
    browser VARCHAR(50) COMMENT '浏览器',
    os VARCHAR(50) COMMENT '操作系统',

    KEY idx_short_code_time (short_code, click_time),
    KEY idx_click_time (click_time),
    KEY idx_country (country)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COMMENT='短链接点击日志表';

ip_address 用 VARCHAR(45) 而不是 VARCHAR(15)，因为要同时支持 IPv4 和 IPv6。IPv6 最长 45 个字符，IPv4 最长 15 个字符。一开始我用的 VARCHAR(15)，结果测试环境用 IPv6 访问直接报错。

user_agent、referer 这些字段是从 HTTP 请求头里提取的，长度不太确定，给得比较宽裕。device_type、browser、os 这些是通过解析 user_agent 得到的，长度有限，VARCHAR(50) 绑绑有余。

这张表的数据量增长很快——每有一次点击就多一行。按日均 100 万次点击计算，一年就是 3.65 亿行。所以我在 (short_code, click_time) 上建了组合索引，支持”查询某个短链接在某个时间段内的点击记录”这个最常见的场景。

后来数据量实在太大，我改成了按月分区：

-- 按月分表
url_click_log_202601
url_click_log_202602
url_click_log_202603

或者使用 MySQL 的分区表功能：

CREATE TABLE url_click_log (
    ...
) PARTITION BY RANGE (UNIX_TIMESTAMP(click_time)) (
    PARTITION p202601 VALUES LESS THAN (UNIX_TIMESTAMP('2026-02-01')),
    PARTITION p202602 VALUES LESS THAN (UNIX_TIMESTAMP('2026-03-01')),
    PARTITION p202603 VALUES LESS THAN (UNIX_TIMESTAMP('2026-04-01')),
    ...
);

按月分区的好处是，查询历史数据的时候可以直接跳过不相关的分区，而且删除过期数据也方便——直接 DROP PARTITION 就行，不用 DELETE，不会产生大量碎片。

最终表结构

经过反复修改，最终的表结构如下：

CREATE TABLE url_mapping (
    -- 主键
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    -- 核心字段
    short_code VARCHAR(10) NOT NULL COMMENT '短链接码，6位Base62',
    long_url VARCHAR(2048) NOT NULL COMMENT '原始长URL',

    -- 用户相关
    user_id BIGINT UNSIGNED DEFAULT NULL COMMENT '用户ID，未登录用户为NULL',

    -- 统计字段
    click_count INT UNSIGNED DEFAULT 0 COMMENT '累计点击次数',
    today_clicks INT UNSIGNED DEFAULT 0 COMMENT '今日点击数',

    -- 状态控制
    status TINYINT UNSIGNED DEFAULT 1 COMMENT '状态：1-正常，0-禁用，2-过期',

    -- 时间字段
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    expires_at TIMESTAMP DEFAULT NULL COMMENT '过期时间，NULL表示永不过期',

    -- 索引
    UNIQUE KEY uk_short_code (short_code),
    KEY idx_long_url (long_url(255)),
    KEY idx_user_id (user_id),
    KEY idx_expires_at (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='短链接映射表';

回头看这张表，其实每一行背后都是一次踩坑。VARCHAR(2048) 是因为被长 URL 教训过，user_id DEFAULT NULL 是因为纠结过要不要强制登录，utf8mb4 是差点用错 utf8……

数据量估算

表结构定好后，我估算了一下存储开销。一行数据大约 250 字节：

1000 万条记录大约 2.5 GB 数据，加上索引大约 2.7 GB，总共约 5 GB。对于一台普通的 MySQL 服务器来说，完全撑得住。

表结构设计好了，我用测试数据跑了几个查询，觉得应该没问题了。

但我高兴得太早了。上线第一周，有个用户反馈说他创建的短链接，打开后等了将近两秒才跳转。我查了一下慢查询日志，发现问题不在表结构，而在索引上——我虽然建了索引，但有好几个查询根本没用上。

问题出在哪里？下一节，我来聊聊索引优化的那些事。

练习题

练习 1

为什么 long_url 的索引用前缀索引 idx_long_url(long_url(255)) 而不是全字段索引？

SELECT 
    COUNT(DISTINCT LEFT(long_url, 255)) / COUNT(*) AS selectivity_255,
    COUNT(DISTINCT long_url) / COUNT(*) AS selectivity_full
FROM url_mapping;

练习 2

如果需要支持 1 亿条短链接记录，单表会有什么问题？你会怎么解决？

url_mapping_00, url_mapping_01, ..., url_mapping_15

-- 路由逻辑
table_index = CRC32(short_code) % 16

url_mapping_u{user_id % 100}

CREATE TABLE url_mapping (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    short_code VARCHAR(10) NOT NULL,
    long_url VARCHAR(2048) NOT NULL,
    ...
    UNIQUE KEY uk_short_code (short_code)
) SHARD_ROW_ID_BITS=4 PRE_SPLIT_REGIONS=4;