05 表设计与数据类型选择——细节决定性能上限
摘要： 一张表设计得好不好，在业务上线的第一天就决定了它的性能天花板。数据类型选错了，索引无法充分发挥作用；字段设计不合理，每一次查询都在做额外的工作；范式与反范式的取舍，直接影响 JOIN 的代价与数据冗余的程度。本文从”为什么数据类型选择会影响性能”这个问题出发，深入分析整数类型、字符串类型、时间类型、浮点与精确数类型的选型陷阱，然后讲解三范式在实际业务中的应用边界与反范式的工程价值，最后给出字段设计、主键设计、分区表设计的实践准则。
第 1 章 为什么数据类型选择会影响性能
1.1 数据类型影响的三个层面
很多开发者认为数据类型只是”存储格式”的问题，实际上它影响的范围远不止于此：
第一层：存储空间。更小的数据类型意味着每行占用的空间更小，同样大小的 B+Tree 页（16KB）可以容纳更多的行。页可以容纳的行越多，InnoDB 读取同样数量的数据所需的 I/O 次数就越少，Buffer Pool 的缓存效率也越高。
第二层：索引效率。索引的叶子节点存储的是被索引列的值。如果索引列的数据类型很大（比如用 VARCHAR(256) 做索引），每个叶子节点能存储的索引条目就少，B+Tree 的层高就更高，每次索引查找需要更多的 I/O。此外，联合索引的 key_len 直接由索引列的数据类型决定，类型越大，索引越”重”。
第三层：运算效率。比较操作（WHERE 条件中的 =、>、<）在整数类型上几乎是一条 CPU 指令，在 VARCHAR 上需要逐字节比较，在 TEXT 上可能需要更多开销。对于高频执行的查询，这种差异在千万级别的扫描量下会累积成可观的性能差距。
设计哲学
选择数据类型的核心原则：在满足业务需求的前提下，选择占用空间最小、语义最精确的类型。“最小”意味着更好的缓存效率和更快的比较速度；“最精确”意味着数据库可以帮你做更多的约束检查，避免脏数据。
第 2 章 整数类型：从 TINYINT 到 BIGINT 的精确选型
2.1 整数类型速查
| 类型 | 存储空间 | 有符号范围 | 无符号范围 | 适用场景 |
|---|---|---|---|---|
TINYINT | 1 字节 | -128 ~ 127 | 0 ~ 255 | 状态码、布尔值、枚举（有限个值） |
SMALLINT | 2 字节 | -32768 ~ 32767 | 0 ~ 65535 | 年份、小范围计数 |
MEDIUMINT | 3 字节 | -8388608 ~ 8388607 | 0 ~ 16777215 | 中等规模 ID |
INT | 4 字节 | -2147483648 ~ 2147483647 | 0 ~ 4294967295 | 常规 ID（约 21 亿） |
BIGINT | 8 字节 | -9.2×10¹⁸ ~ 9.2×10¹⁸ | 0 ~ 1.8×10¹⁹ | 分布式 ID、大规模计数 |
2.2 常见选型错误
错误一：对所有 ID 字段无脑用 BIGINT
很多团队有一条”所有 ID 都用 BIGINT”的规范。这在分布式场景下（使用雪花算法等）是正确的，但对于一些明确有界的小表（如字典表、配置表），INT 甚至 SMALLINT 就已经够用。BIGINT 比 INT 多 4 字节，如果一张表有 10 个 BIGINT 字段，每行就多消耗 40 字节——对于亿级别的大表，这是不可忽视的差异。
错误二：用 INT(10) 当作”最大 10 位”的约束
INT(10) 中的 10 不是存储长度，不是最大值约束，而是”显示宽度（Display Width）“——这是一个历史遗留特性，在 MySQL 8.0.17 之后已经被废弃。INT 永远是 4 字节，无论你写 INT(1) 还是 INT(10)，存储能力完全一样。如果你想限制最大值，需要用 CHECK 约束。
错误三：布尔值用 INT 而不是 TINYINT(1)
BOOLEAN 在 MySQL 中是 TINYINT(1) 的别名，占 1 字节。用 INT 存布尔值会浪费 3 字节，在大量 WHERE 条件过滤和索引中累积起来会有影响。
2.3 UNSIGNED 的使用时机
对于 ID 类字段，应该使用 UNSIGNED（无符号整数），因为 ID 不会是负数，使用 UNSIGNED 可以把正数范围扩大一倍（INT UNSIGNED 最大约 42 亿，INT 有符号最大约 21 亿）。
生产避坑
对自增主键使用
INT而不是INT UNSIGNED是一个常见的设计失误。一个每天产生 100 万条记录的业务表，INT有符号在约 5.9 年后就会溢出；INT UNSIGNED可以用约 11.7 年；BIGINT UNSIGNED几乎永远不会溢出。对于设计生命周期超过 5 年的核心业务表，主键至少用INT UNSIGNED，如果使用分布式 ID 必须用BIGINT UNSIGNED。
第 3 章 字符串类型：VARCHAR、CHAR、TEXT 的选择逻辑
3.1 CHAR vs VARCHAR：固定长度 vs 可变长度
CHAR(n)：固定长度字符串，总是占用 n 个字符的空间（不足的用空格填充，读取时自动去除尾部空格）。
VARCHAR(n)：可变长度字符串，实际占用”真实内容长度 + 1-2 字节的长度前缀”（长度 ≤ 255 用 1 字节前缀，> 255 用 2 字节前缀）。
什么时候用 CHAR？
CHAR 的优势在于：长度固定意味着 InnoDB 可以精确知道每行的偏移量，在某些操作（如行内更新）中性能更好，也不需要存储长度前缀。适用于内容长度固定或几乎固定的场景：
- 固定长度的哈希值（如 MD5 = 32 字符，用
CHAR(32)） - 定长编码（如国家代码
CHAR(2)、手机号CHAR(11)） - 邮政编码、固定格式的密码存储
什么时候用 VARCHAR？
几乎所有长度不固定的字符串都应该用 VARCHAR：用户名、邮箱、地址、描述等。VARCHAR 不浪费存储空间，是绝大多数场景的首选。
VARCHAR(n) 中 n 应该设多大？
n 应该是业务上合理的最大值，不要”预留很大的 n 以防不够用”。原因是：
- 在某些 MySQL 版本中，内存中临时表（
Using temporary）的字符串列按 n 分配空间，设太大会浪费内存 - InnoDB 对于 VARCHAR 列长度的判断会影响行格式的选择（是否启用行外存储）
- VARCHAR(255) 和 VARCHAR(256) 的长度前缀从 1 字节变成 2 字节，有微小的空间差异
3.2 TEXT 与 BLOB：大对象存储的代价
TEXT（文本）和 BLOB（二进制大对象）用于存储较大的内容：TINYTEXT（255字节）、TEXT（64KB）、MEDIUMTEXT（16MB）、LONGTEXT（4GB）。
TEXT/BLOB 的主要问题：
无法完整地作为索引列（只能前缀索引），且前缀索引无法覆盖索引，会导致回表。
当列内容超过阈值时，会触发行外存储（Off-Page Storage）：内容存储在单独的溢出页中，行记录中只保留一个 20 字节的指针。访问这些列时需要额外的 I/O 去读取溢出页。
排序和 GROUP BY 操作需要使用磁盘临时表，因为内存临时表无法存放 BLOB/TEXT 类型（MySQL 8.0 的 TempTable 引擎在某些版本支持，但有限制）。
生产避坑
不要在数据库中存储文件内容（图片、视频、文档）。文件应存储在对象存储（OSS、S3）中，数据库只保存 URL。数据库的强项是结构化数据的检索和事务，用它存大二进制文件会撑大表空间、拖慢备份、降低缓存命中率。
3.3 字符集选择：utf8 vs utf8mb4
MySQL 中的 utf8 字符集是一个不完整的 UTF-8 实现——它只支持最多 3 字节的 UTF-8 字符，无法存储 4 字节的 Unicode 字符（包括 emoji、部分生僻汉字）。
utf8mb4 是真正完整的 UTF-8 实现，支持所有 Unicode 字符（最多 4 字节）。
结论：MySQL 中永远使用 utf8mb4，不要使用 utf8。utf8 在 MySQL 中是历史错误设计的产物，其存在只是为了向后兼容。
字符集对存储空间的影响：utf8mb4 的 VARCHAR(n) 中，n 表示最多 n 个字符，每个字符最多占 4 字节。所以 VARCHAR(100) CHARSET utf8mb4 最多占 100×4 + 2 = 402 字节。这影响 key_len 的计算和内存临时表的空间分配。
第 4 章 时间类型：DATETIME vs TIMESTAMP 的深度对比
这是表设计中最常见的纠结点之一。
| 特性 | DATETIME | TIMESTAMP |
|---|---|---|
| 存储空间 | 8 字节（5字节+小数秒） | 4 字节（+小数秒） |
| 时区处理 | 不做时区转换，存什么读什么 | 自动转换：写入时转 UTC 存储，读取时转服务器时区 |
| 范围 | 1000-01-01 到 9999-12-31 | 1970-01-01 到 2038-01-19 |
| NULL 值 | 支持 | 支持，但默认行为与 DATETIME 不同 |
| 自动更新 | 需要显式 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | 第一个 TIMESTAMP 列默认自动更新（旧版本行为，8.0 已改变） |
4.1 TIMESTAMP 的时区陷阱
TIMESTAMP 的时区自动转换功能，在多数情况下是坑而不是福：
- 迁移风险：如果数据库服务器的时区配置（
time_zone参数）发生变化，同样的 TIMESTAMP 值读出来的结果会不同——相当于数据”变了” - 数据同步问题：在主从复制或数据迁移时，如果源和目标的时区配置不一致，TIMESTAMP 数据会被错误地解释
- 2038 年问题：TIMESTAMP 是 32 位 Unix 时间戳，最大值是 2038-01-19 03:14:07 UTC。虽然 2038 年现在看起来还远，但很多系统都有几十年的生命周期
实践建议：
- 对于创建时间、更新时间等业务时间字段，使用
DATETIME，由应用层统一处理时区（始终存储 UTC 时间） - 如果系统只在单一时区运行且不考虑迁移，TIMESTAMP 的较小存储空间（节省 4 字节/行）有一定优势
- MySQL 8.0 支持
DATETIME的默认值表达式DEFAULT CURRENT_TIMESTAMP，弥补了 DATETIME 早期不能自动设置默认值的缺陷
4.2 时间精度：小数秒的存储代价
MySQL 支持对时间类型存储小数秒（微秒级精度），语法是 DATETIME(n) 或 TIMESTAMP(n)，n 为小数位数（0-6）：
| 小数位数 | 额外存储字节 |
|---|---|
| 0（默认） | 0 字节 |
| 1, 2 | 1 字节 |
| 3, 4 | 2 字节 |
| 5, 6 | 3 字节 |
如果不需要毫秒/微秒精度，不要加小数位——这不只是存储的问题，也影响索引的大小。
第 5 章 数值精度：FLOAT/DOUBLE vs DECIMAL
5.1 浮点数的精度陷阱
FLOAT（4 字节）和 DOUBLE（8 字节）是 IEEE 754 浮点数，无法精确表示所有十进制小数。经典例子：
-- 在大多数系统上，0.1 + 0.2 ≠ 0.3
SELECT 0.1 + 0.2;
-- 结果可能是 0.30000000000000004这对金融、支付、税收等需要精确计算的场景是不可接受的。
5.2 DECIMAL：精确的十进制数
DECIMAL(M, D) 以字符串形式存储十进制数，M 是总位数，D 是小数位数。它的每 9 位十进制数占 4 字节（通过打包编码实现）。
DECIMAL(10, 2) 可以存储 -99999999.99 到 99999999.99 的数，占 5 字节，精确无误。
金额字段必须使用 DECIMAL，绝不使用 FLOAT 或 DOUBLE。
-- ❌ 错误：用浮点数存金额
amount FLOAT(10, 2)
-- ✅ 正确：用 DECIMAL 存金额
amount DECIMAL(12, 2) -- 支持到 999999999999.99核心概念
关于金额存储，还有一种方案是以分（整数）为单位存储，用
BIGINT存储”100 元”为”10000 分”。这种方案完全避免了小数，运算更快，且 BIGINT 的比较效率高于 DECIMAL。缺点是应用层需要统一做分/元的转换。两种方案各有支持者，关键是团队内部统一规范。
第 6 章 范式与反范式的工程取舍
6.1 三大范式的业务价值
第一范式（1NF）：表中每一列的值必须是不可再分的原子值（不能是数组、JSON 等复合结构）。
违反 1NF 的典型例子：将多个手机号存在一个字段中，用逗号分隔（phone: '13800138000,13900139000'）。这会导致查询某个手机号时必须使用 LIKE '%13800138000%'，无法使用索引。
第二范式（2NF）：在 1NF 基础上，非主键列必须完全依赖于主键（不能只依赖于联合主键的一部分）。
第三范式（3NF）：在 2NF 基础上，非主键列之间不能有传递依赖（A→B→C 这种关系，C 应该单独建表）。
范式的核心价值是减少数据冗余，从而减少数据更新异常。如果用户的城市信息在 100 张订单里都冗余存储，当用户搬家时就需要更新 100 条记录——范式化设计将城市信息单独存在用户表，只需更新一处。
6.2 反范式的工程理由
在实际业务中，严格遵守三范式有时会导致过多的 JOIN 操作，降低查询性能。
典型场景：订单详情页
范式化设计：orders 表只存 user_id，order_items 表只存 product_id，商品名称和价格存在 products 表。查询一个订单的完整信息需要 3 张表 JOIN。
反范式设计：在 order_items 中冗余存储下单时的 product_name 和 snapshot_price（快照价格）。
为什么这里反范式是正确的？
- 快照语义：订单一旦创建，就应该记录下单时的商品名称和价格——即使商品后来改名或调价，历史订单不应该受影响。这里的冗余本身就有业务意义，不只是性能考量。
- 减少 JOIN：订单详情是高频访问的接口，JOIN 操作的代价不可忽视（特别是在大表上）。
反范式的适用条件：
| 条件 | 说明 |
|---|---|
| 冗余字段读多写少 | 冗余字段频繁查询但很少更新，收益 > 成本 |
| 更新可以接受最终一致 | 冗余字段的更新可以异步完成，不要求实时同步 |
| 冗余有明确的业务语义 | 如快照价格，冗余本身是业务需求，不只是优化手段 |
6.3 JSON 字段：灵活性 vs 可查询性的权衡
MySQL 5.7.8+ 支持原生 JSON 类型，允许存储结构不固定的数据。
JSON 字段的优势：
- 字段结构可以随时扩展，不需要 ALTER TABLE（ALTER 大表在生产环境是高风险操作）
- 适合存储附加属性（用户的扩展信息、商品的 SKU 属性等）
JSON 字段的局限：
- JSON 字段上不能直接建普通索引，只能通过生成列（Generated Column）间接创建索引
- JSON 内部字段的查询需要使用
->或->>操作符，语法复杂 - 数据库无法对 JSON 内部字段做约束检查（类型约束、非空约束、外键等）
-- 为 JSON 字段内的属性建索引（通过虚拟生成列）
ALTER TABLE products
ADD COLUMN brand VARCHAR(64) AS (attributes->>'$.brand') VIRTUAL,
ADD INDEX idx_brand (brand);设计哲学
JSON 字段应该是结构化列的补充，而不是替代。核心的业务字段（需要 WHERE 过滤、JOIN、排序、聚合的字段）必须设计为结构化列；真正不固定、不需要检索的附加属性（如用户偏好设置、商品详情的非关键属性）才适合用 JSON 存储。“把所有东西都塞进 JSON”是设计偷懒，后果是系统无法有效地利用数据库的查询优化能力。
第 7 章 主键设计的工程规范
7.1 自增主键 vs 业务主键
自增主键（AUTO_INCREMENT）是 InnoDB 表的最推荐主键选择，原因在于 B+Tree 的物理特性：
InnoDB 的聚簇索引（主键索引）将数据行按主键顺序存储在 B+Tree 的叶子节点中。如果主键是单调递增的（自增），新行总是追加到 B+Tree 的最右端，写入是顺序的，几乎不会触发页分裂。
如果主键是随机的（如 UUID 或雪花 ID 的字符串形式），新行会随机插入到 B+Tree 的各个位置，频繁触发页分裂，并导致索引碎片化——久而久之，B+Tree 的页填充率下降，同样大小的数据需要更多的页，缓存效率降低，查询变慢。
业务主键（如订单号、身份证号）的问题：
- 可能不是单调递增的，参见上面的页分裂问题
- 业务规则可能变化，导致主键需要修改（主键修改会级联修改所有二级索引中的主键值，代价巨大）
- 字符串主键的比较效率低于整数主键
建议：始终使用代理主键（surrogate key，即无业务含义的自增整数或自增雪花 ID），业务主键（订单号、编号等）作为唯一索引列。
7.2 分布式场景的主键选择
在分布式系统中，多个 MySQL 实例同时写入，自增主键会产生冲突。常见解决方案：
| 方案 | 优点 | 缺点 |
|---|---|---|
| 雪花算法（Snowflake） | 趋势递增，插入性能好 | 依赖时钟（时钟回拨问题），需要机器 ID 分配 |
| UUID v4 | 完全随机，无依赖 | 完全随机，页分裂严重，字符串比较慢 |
| UUID v7（有序 UUID） | 趋势递增，无时钟依赖 | 较新（MySQL 8.0.28+ 支持 UUID_TO_BIN） |
| 数据库号段模式 | 简单，高可用 | 需要额外的号段分配服务 |
| 步长自增（如奇/偶分库） | 简单 | 扩缩容复杂 |
对于大多数互联网公司，雪花算法是最常见的选择——既保证了全局唯一性，又保持了趋势递增（对 B+Tree 友好），以 BIGINT 存储，比较效率高。
第 8 章 小结
本文构建的知识链：
- 数据类型影响三个层面：存储空间（缓存效率）、索引效率（B+Tree 紧凑度）、运算效率（比较速度）
- 整数选型：按业务范围选最小的类型；ID 字段用
UNSIGNED；INT(n)的 n 是显示宽度不是存储约束 - 字符串选型：固定长度用
CHAR，可变用VARCHAR；n 设合理值不要随意放大；永远用utf8mb4不用utf8；大文件不存数据库 - 时间类型：跨时区或需要长期维护的系统用
DATETIME+ 应用层统一 UTC；TIMESTAMP 有 2038 年限制和时区陷阱 - 金额必须用 DECIMAL：浮点数无法精确表示十进制小数，金融计算绝不用
FLOAT/DOUBLE - 范式 vs 反范式：范式减少冗余和更新异常；反范式减少 JOIN 代价。快照数据、高频读低频写的派生字段适合反范式；JSON 字段是结构化列的补充而非替代
- 主键设计：代理主键（自增整数/趋势递增 ID）优于业务主键；分布式场景优选雪花算法；随机 UUID 会导致严重的 B+Tree 页分裂
思考题
- 批量 INSERT（
INSERT INTO t VALUES (1,a),(2,b),(3,c)...）比逐条 INSERT 快 10-100 倍——因为减少了网络往返和事务提交次数。最优的批量大小是多少（通常 1000-10000 行/批）？过大的批量可能导致什么问题（如事务过大、锁持有时间过长、Binlog 单事件过大）？LOAD DATA INFILE是 MySQL 最快的数据导入方式——直接从文件加载数据到表中，跳过了 SQL 解析。LOAD DATA比批量 INSERT 快多少？在什么场景下你会选择LOAD DATA而非批量 INSERT（如初始数据导入、ETL）？- 在高并发写入场景中，自增 ID 的 AUTO-INC Lock 可能成为瓶颈。
innodb_autoinc_lock_mode=2（交叉模式）使用轻量级 Mutex 替代表级锁——提升并发但可能产生不连续的 ID。在使用 Statement 格式的 Binlog 时，lock_mode=2可能导致主从不一致——为什么？Row 格式的 Binlog 是否解决了这个问题？