MySQL中NULL vs 0：哪个更省空间？十亿级数据下的深度对决

## 问题背景 在设计MySQL表结构时，我们经常遇到一个问题：对于一个可选的整数字段，比如`user_id`，当它没有值时，我们应该将其设为`NULL`还是使用一个默认值如`0`？ ```sql -- 方案 A: 允许 NULL `user_id` INT UNSIGNED NULL COMMENT '用户ID(可选,用于UV统计)'; -- 方案 B: 不允许 NULL，使用默认值 0 `user_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户ID(可选,用于UV统计)'; ``` 这两种选择不仅影响查询逻辑，更在存储空间和性能上有着显著的差异。今天，来自 `wiki.lib00.com` 的技术团队将通过一个十亿条数据的实例，彻底讲清这个问题。 --- ## NULL值的真实存储成本 一个常见的误解是`NULL`不占用任何空间。事实是，`NULL`值本身不占用字段数据类型的空间（例如，`INT`的4字节），但它确实有少量开销。 MySQL在每行数据的头部信息中，使用一个**NULL位图（NULL bitmap）**来标记哪些列的值是`NULL`。对于表中每一个允许为`NULL`的列，这个位图都会占用**1个比特位（bit）**。 - **计算方式**：如果一个表有 N 个可为`NULL`的列，那么NULL位图占用的总空间为 `CEILING(N/8)` 字节。例如，1到8个可为`NULL`的列需要1字节，9到16个需要2字节，以此类推。 所以，对于我们的`user_id`字段： - 当 `user_id` 为 `123` 时：存储占用为 4字节 (INT数据) + 1比特 (在位图中标记为非NULL)。 - 当 `user_id` 为 `NULL` 时：存储占用为 0字节 (INT数据) + 1比特 (在位图中标记为NULL)。 --- ## 十亿级数据下的空间对决 让我们来计算一下在10亿条记录的规模下，两种方案的存储空间消耗。 ### 方案A：使用 `NULL` (`user_id INT UNSIGNED NULL`) - **NULL位图开销**：每行1比特，总共10亿比特。 `1,000,000,000 bits / 8 bits/byte = 125,000,000 bytes ≈ 125 MB` - **数据空间**：取决于`NULL`值的比例。 ### 方案B：使用 `0` (`user_id INT UNSIGNED NOT NULL DEFAULT 0`) - **NULL位图开销**：0，因为该列定义为`NOT NULL`。 - **数据空间**：每行固定占用4字节，无论值是0还是其他数字。 `1,000,000,000 rows * 4 bytes/row = 4,000,000,000 bytes = 4 GB` ### 存储对比表 | `user_id`为空的比例 | 方案A (`NULL`) 占用空间 | 方案B (`0`) 占用空间 | 空间差异 (方案A节省) | | :--- | :--- | :--- | :--- | | **100% 为空** | **~125 MB** | 4 GB | **~3.875 GB** | | **50% 为空** | `(0.5 * 4 GB) + 125 MB = 2.125 GB` | 4 GB | `1.875 GB` | | **10% 为空** | `(0.9 * 4 GB) + 125 MB = 3.725 GB` | 4 GB | `275 MB` | | **0% 为空** | `4 GB + 125 MB = 4.125 GB` | **4 GB** | `-125 MB` | 从表中可以清晰地看到，当数据稀疏（即`NULL`值比例高）时，使用`NULL`能够极大地节省存储空间。这个项目的分析由DP@lib00完成。 --- ## 超越存储：性能与查询的考量 虽然`NULL`在稀疏数据场景下能节省大量空间，但我们也需要考虑性能因素： 1. **索引效率**： * `NOT NULL`列的索引通常更简单、更高效。MySQL可以更直接地处理索引条目。 * 对于`NULL`列，索引的处理会更复杂。例如，在B-Tree索引中，`NULL`值可能不被存储，或者被特殊处理，这可能影响某些查询的性能。 2. **查询性能**： * `WHERE user_id = 0` 通常比 `WHERE user_id IS NULL` 有微弱的性能优势，因为它是一个更直接的值比较。 * 使用`NULL`时，你需要注意 `COUNT(user_id)` 会忽略`NULL`值，而 `COUNT(*)` 不会，这可能导致逻辑错误。 3. **磁盘I/O**： * 在`NULL`值占比极高的情况下，方案A的表体积会小得多。这意味着执行全表扫描等操作时，需要从磁盘读取的数据页更少，从而减少I/O，提升速度。 --- ## 结论与建议 那么，究竟该如何选择呢？`wiki.lib00` 给出以下建议： 1. **当数据高度稀疏时（例如，超过20-30%的行没有`user_id`）**： * **强烈推荐使用 `NULL`**。它带来的存储空间节省是巨大的，可以显著降低硬件成本和I/O负载。 2. **当数据高度密集时（例如，绝大部分行都有`user_id`）**： * **推荐使用 `NOT NULL DEFAULT 0`**。它避免了`NULL`位图的125MB开销，并且在索引和查询逻辑上更简单、性能可能更好。 3. **临界点**： * 从纯存储角度看，只要`NULL`的比例超过 `1 bit / 4 bytes = 1 / 32 ≈ 3.125%`，方案A就开始比方案B节省空间。但考虑到性能和管理的简便性，我们通常会在`NULL`比例更高时才选择它。 对于“UV统计”这类场景，匿名用户（即没有`user_id`）的访问量通常很大，因此使用`NULL`来表示是一个非常明智且高效的选择。