INT / BIGINT / TINYINT / SMALLINT 各占几字节？什么时候用哪个？

考察要点

这道题考察的是候选人对数据库基础数据类型的理解深度和设计选型能力。面试官想看你是否懂：

1. 存储与性能的权衡：是否知道不同整型占用的空间，并能将其与磁盘、内存、CPU 消耗关联起来。
2. 数据范围的预估：能否根据业务场景，合理预估数据范围，选择最“恰当”而非“最大”的类型。
3. 细节知识的掌握：是否了解 UNSIGNED 属性的价值，以及 INT(11) 这类表示宽度的含义，避免常见误区。

核心概念

MySQL 提供了多种整型来存储整数，主要区别在于存储空间和可表示的数值范围。所有整型都支持 SIGNED（有符号，默认）和 UNSIGNED（无符号）两种属性。

关键概念解释：

1. UNSIGNED 属性： 当列的值永远不会为负数时（如自增 ID、年龄、数量、状态码），应使用 UNSIGNED。它将 SIGNED 类型用于符号位的那 1 bit 用来表示数值，使得正整数的上限扩大一倍，而存储空间不变。这是一个简单高效的优化。

   sql

   ⧉⛶

    1CREATE TABLE products (
    2    product_id INT UNSIGNED NOT-NULL AUTO_INCREMENT,
    3    stock_count INT UNSIGNED DEFAULT 0,
    4    PRIMARY KEY (product_id)
    5);

2. INT(M) 中的 M： INT(11) 中的 11 不影响存储空间和数值范围，它只与显示宽度有关，通常需要配合 ZEROFILL 属性使用，用于在数字位数不足 M 时，在左侧填充 0。

   • 例如，INT(5) ZEROFILL 存储数字 123，会显示为 00123。
   • 注意：从 MySQL 8.0.17 开始，ZEROFILL 属性以及整型的显示宽度 (M) 已被废弃，未来版本会被移除。因此，在新的设计中应避免使用。

常见低分回答

1. 混淆 INT(11) 的含义：错误地认为 INT(11) 限制了只能存 11 位数字，或者认为它比 INT(4) 占用更多存储空间。这是最常见的误解。
2. 无脑使用 BIGINT：为了“保险”或“省事”，所有 ID 字段都用 BIGINT。这体现了缺乏成本意识，BIGINT 会导致更大的磁盘占用、更多的内存消耗（Buffer Pool）和更慢的索引查询（特别是二级索引）。
3. 忘记 UNSIGNED：在明确非负的场景（如主键 ID）下，没有使用 UNSIGNED，浪费了一半的数据范围。例如，用 INT 做主键，最大只能到约 21 亿，而 INT UNSIGNED 可以到约 42 亿，对于很多业务来说，这可以推迟向 BIGINT 迁移的时间。
4. 数据类型不匹配：在设计表关联时，主键（如 users.id）和外键（如 orders.user_id）使用了不同的数据类型（例如一个是 INT，一个是 BIGINT），或 SIGNED/UNSIGNED 属性不一致。这会导致无法创建外键约束，并且在 JOIN 查询时可能无法有效利用索引。

工程实践与优化

选型原则：在满足业务需求和未来增长的前提下，选择最小的类型。

1. 主键 ID 选型策略：

   • TINYINT UNSIGNED (0-255): 适用于非常小的配置表、状态表、类型表。如：订单状态（待支付、已支付、已发货、已完成...）。
   • SMALLINT UNSIGNED (0-65,535): 适用于中小型配置表。如：商品分类。
   • INT UNSIGNED (0-42.9亿): 绝大多数中小型应用的首选。用户表、商品表、订单表等，42 亿的上限对于绝大多数公司在数年内都足够。
   • BIGINT UNSIGNED (0-1.8x10^19): 大型互联网应用或未来预估数据量巨大的场景的标配。例如，社交网络的消息、帖子，电商的交易流水，物联网设备的数据点。一旦预见到单表数据可能在几年内突破 20 亿，就应直接使用 BIGINT，避免未来的在线 DDL 迁移成本。

2. 性能影响：

   • 空间：更小的数据类型意味着更小的数据行，一张表能存更多行，磁盘 I/O 更少。
   • 内存：InnoDB 以页（Page，默认 16KB）为单位管理数据，更小的数据行意味着一个页能缓存更多行，提高了内存（Buffer Pool）的利用率。
   • 索引：
     • 主键索引（聚簇索引）：主键大小直接决定 B+ 树的节点大小。
     • 二级索引：二级索引的叶子节点存储的是主键的值。如果主键从 INT (4字节) 换成 BIGINT (8字节)，那么所有的二级索引大小都会显著增加，导致索引查询性能下降。

3. 执行计划分析： 当 JOIN 的两张表的关联键类型不一致时，EXPLAIN 的结果可能会显示无法使用索引。例如，t1.id 是 INT，t2.t1_id 是 VARCHAR，JOIN ON t1.id = t2.t1_id 会导致 t1.id 发生隐式类型转换，t1 上的索引会失效。即使都是整型，SIGNED 和 UNSIGNED 不匹配也可能影响性能。

常见追问与延伸题目

1. 追问：如果一个已经用了 INT UNSIGNED 的主键快用完了（比如接近 40 亿），该如何平滑升级到 BIGINT？

   • 回答要点 1 (禁止的做法)：不能直接在生产高峰期执行 ALTER TABLE t_name MODIFY COLUMN id BIGINT UNSIGNED。对于大表，这会锁表非常长的时间（从几小时到几天），导致服务不可用。
   • 回答要点 2 (推荐的方案)：使用在线 DDL 工具，如 Percona 的 pt-online-schema-change 或 GitHub 的 gh-ost。它们的基本原理是：
     1. 创建一个与原表结构相同但主键为 BIGINT 的“影子表”。
     2. 在原表上创建触发器（INSERT/UPDATE/DELETE），将数据变更同步到影子表。
     3. 以小批量（chunk）的方式，将原表数据拷贝到影子表。
     4. 拷贝完成后，在低峰期原子地 RENAME TABLE，将原表和影子表的名字对调，完成切换。
   • 回答要点 3 (风险)：这个过程会增加数据库负载，可能导致主从延迟。需要监控并选择业务低峰期操作。

2. 追问：为什么不直接用 UUID 作为主键？它和自增整数主键相比有什么优缺点？

   • 回答要点 1 (UUID 优点)：
     • 全局唯一：适合分布式系统、微服务架构，可以在应用层生成，避免了对数据库的单点依赖。
     • 安全性：不会暴露业务规模，例如 .../users/10 暴露了你是第 10 个用户，而 UUID 则无法猜测。
   • 回答要点 2 (UUID 缺点，尤其对 InnoDB)：
     • 存储成本高：通常用 CHAR(36) (36字节) 或 BINARY(16) (16字节) 存储，远大于 INT(4字节) 或 BIGINT(8字节)。
     • 索引性能差：传统 UUID (v4) 是随机无序的，作为主键（聚簇索引）会导致 B+ 树节点频繁分裂（Page Split）和产生大量索引碎片，插入性能下降，数据物理存储不连续，范围查询效率低。
   • 回答要点 3 (优化方案)：MySQL 8.0 提供了有序 UUID 的函数 UUID_TO_BIN() 和 BIN_TO_UUID()，结合时间戳和 MAC 地址，可以生成趋势递增的 UUID，大大缓解了随机插入带来的性能问题。或者使用社区提出的 UUID v7/v8 规范。

3. 追问：在设计 API 时，如果数据库用的是 BIGINT，返回给前端（比如 JavaScript）时需要注意什么？

   • 回答要点 1 (问题根源)：JavaScript 中的 Number 类型是基于 IEEE 754 标准的双精度浮点数，其能安全表示的最大整数是 Number.MAX_SAFE_INTEGER，即 $2^{53} - 1$。
   • 回答要点 2 (风险)：MySQL 的 BIGINT 最大值（有符号）或 BIGINT UNSIGNED 远超这个范围。如果后端直接将一个大的 BIGINT 值作为 JSON Number 类型返回给前端，JavaScript 在解析时会发生精度丢失。
   • 回答要点 3 (解决方案)：最佳实践是在后端将 BIGINT 类型的字段序列化为字符串（String）再返回给前端。这样可以保证数据在传输和解析过程中不会丢失精度。前端接收到字符串后，可以原样展示，或者在需要进行计算时使用 BigInt 类型（ES2020 标准）或第三方库来处理。