数据库基础体系 · 第 84/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

MySQL 表设计：主键、行格式、NULL、生成列、分区与归档

在 InnoDB 中，表设计不是把列类型逐个填好即可。主键会决定聚簇存储和二级索引的物理内容；行格式会影响长字段如何放入数据页；NULL 会改变比较逻辑、唯一性和统计行为；生成列可以把表达式结果纳入索引或分区设计；分区则会改变数据定位、DDL 和归档路径。

这些机制相互关联。一个看似局部的选择，例如把一个较长的业务编号设为主键，可能同时放大所有二级索引；把时间列改成允许 NULL，可能影响分区边界、查询谓词和数据完整性。

本文以 MySQL 8.4、InnoDB、单实例或常规主从部署为主要边界。分区表、归档操作还会受到外键、锁、复制和在线 DDL 条件的影响，不能脱离部署环境单独判断。

---

一、先明确 InnoDB 表的物理模型

1.1 聚簇索引不是普通索引

InnoDB 的表数据存储在聚簇索引（clustered index）中。聚簇索引的叶子节点直接保存整行记录，而不是只保存“索引键加行地址”。

通常，聚簇索引由主键构成：

CREATE TABLE account (
    account_id BIGINT UNSIGNED NOT NULL,
    email      VARCHAR(255) NOT NULL,
    created_at DATETIME(6) NOT NULL,
    PRIMARY KEY (account_id)
) ENGINE = InnoDB;

这里的组织方式可以抽象为：

聚簇索引
  ├── 非叶子节点：account_id
  └── 叶子节点：account_id + email + created_at + 其他列

查询：

SELECT email
FROM account
WHERE account_id = 1001;

定位到聚簇索引叶子页后即可取得整行，不需要再回表。

1.2 没有显式主键时会发生什么

如果没有主键，InnoDB 会依次寻找：

1. 第一个 UNIQUE 且所有列都声明为 NOT NULL 的索引；
2. 如果没有这样的索引，InnoDB 创建内部的 6 字节行 ID。

因此，下面两个表的物理语义不同：

CREATE TABLE t1 (
    code VARCHAR(32) NOT NULL,
    UNIQUE KEY uk_code (code)
) ENGINE = InnoDB;

t1 可以使用 uk_code 作为聚簇索引。

CREATE TABLE t2 (
    code VARCHAR(32) NULL,
    UNIQUE KEY uk_code (code)
) ENGINE = InnoDB;

由于 code 允许 NULL，这个唯一索引不满足“所有列非空”的条件，InnoDB 可能使用内部行 ID 作为聚簇键。

内部行 ID 对用户不可见，也无法自然地用于业务定位。更重要的是，它不能表达业务实体的稳定身份。因此，生产表通常应显式声明主键，而不是依赖 InnoDB 的隐含选择。

1.3 二级索引会携带主键

InnoDB 的二级索引叶子节点通常保存：

二级索引列 + 主键列

例如：

CREATE TABLE customer (
    customer_id CHAR(36) NOT NULL,
    email       VARCHAR(255) NOT NULL,
    name        VARCHAR(100) NOT NULL,
    PRIMARY KEY (customer_id),
    UNIQUE KEY uk_email (email)
) ENGINE = InnoDB;

uk_email 的索引记录逻辑上类似：

email -> customer_id

执行：

SELECT name
FROM customer
WHERE email = 'a@example.com';

大致过程是：

1. 在 uk_email 中查找 email；
2. 得到 customer_id；
3. 用 customer_id 回到聚簇索引；
4. 读取 name。

如果主键从 CHAR(36) 改成 BINARY(16)，每个二级索引记录携带的主键也会变小。反过来，如果主键是很长的字符串，所有二级索引都可能变大。

这不是“主键只影响主键索引”的问题，而是一个简单的空间传播关系：

每个二级索引的额外主键成本
≈ 二级索引记录数 × 主键存储长度

假设有：

• 1 亿行；
• 4 个二级索引；
• 主键从 8 字节增加到 36 字节；
• 暂不计索引记录头、页目录和填充开销。

增加的原始键数据约为：

100,000,000 × 4 × (36 - 8)
= 11,200,000,000 字节

也就是约 10.4 GiB 的十进制换算前数据量。实际磁盘和内存占用还会受到页分裂、记录头、压缩、填充率等影响，但数量级关系已经成立。

1.4 主键应满足哪些可验证条件

主键不是“随便找一个唯一列”。它至少应满足：

• 每行必须有值，即隐含 NOT NULL；
• 值必须唯一；
• 生命周期内稳定，不应频繁更新；
• 类型尽可能紧凑；
• 写入顺序和并发模式应符合访问特征；
• 不把经常变化的业务属性当作身份。

自增整数和时间有序的二进制标识通常具有较好的插入局部性，但它们不是普遍最优解：

• 自增整数便于范围扫描和顺序插入，但跨节点生成需要额外设计；
• UUID 便于分布式生成，但随机 UUID 可能造成聚簇索引页分裂；
• 时间有序 ID 能改善局部性，但必须明确其生成算法、时钟回拨行为和跨节点唯一性；
• 业务编码可读，但可能较长、会变更，且常常不适合作为物理主键。

一个常见折中是：

CREATE TABLE order_header (
    order_id       BIGINT UNSIGNED NOT NULL,
    order_no       VARCHAR(40) NOT NULL,
    customer_id    BIGINT UNSIGNED NOT NULL,
    created_at     DATETIME(6) NOT NULL,
    PRIMARY KEY (order_id),
    UNIQUE KEY uk_order_no (order_no),
    KEY idx_customer_created (customer_id, created_at)
) ENGINE = InnoDB;

order_id 负责物理身份，order_no 负责对外业务编号。这样业务编号的格式变化不会迫使所有外键和二级索引跟着变化。

1.5 主键更新为什么昂贵

聚簇索引的键就是记录在 B+Tree 中的位置。修改主键通常不是简单地覆盖一列，而是近似于：

1. 在旧主键位置删除记录；
2. 按新主键插入记录；
3. 更新引用该主键的二级索引记录；
4. 在事务提交前维护锁、回滚信息和 MVCC 版本。

因此，主键应视为不可变身份。若业务上存在“可修改的编号”，应把它建成唯一二级索引，而不是主键。

---

二、行格式：一行如何进入 InnoDB 数据页

2.1 行格式解决什么问题

行格式（row format）规定 InnoDB 如何组织行记录、变长字段、溢出字段和记录头。它影响：

• 行记录的页内布局；
• 长 VARCHAR、TEXT、BLOB 等字段是否完整放在数据页；
• 行大小限制的表现；
• 压缩和兼容性；
• 访问一行时是否需要额外读取溢出页。

InnoDB 不是把每一行当成一段完全独立的连续字节。数据按页管理，页通常为 16 KiB，但实例可以配置其他页大小。页面中还要容纳页目录、记录头和管理信息，所以“列长度总和”不能直接等于“可存储的行长度”。

2.2 常见行格式

MySQL 8.4 中常见的 InnoDB 行格式包括：

• REDUNDANT
• COMPACT
• DYNAMIC
• COMPRESSED

现代 MySQL 8.4 环境通常使用 DYNAMIC。可以检查实际表定义：

SHOW TABLE STATUS LIKE 'account'\G

或者：

SELECT
    TABLE_NAME,
    ENGINE,
    ROW_FORMAT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'account';

不要只看全局默认值，因为已有表可能在旧版本创建，或者显式指定过行格式。

2.3 COMPACT 与 DYNAMIC 的关键差异

对较长的变长字段，COMPACT 和 DYNAMIC 的处理方式不同。

在 COMPACT 行格式中，长字段通常会保留一部分前缀在聚簇索引记录内，其余部分放到溢出页。历史上常见的前缀长度是 768 字节，但具体记录还包含指向外部页的指针和其他元数据，因此不能把它简单理解为“每个字段固定占 768 字节”。

在 DYNAMIC 行格式中，长字段可以更彻底地放到页外，聚簇记录中主要保留指针。这样可以让数据页中保留更多完整的短字段，也减少长字段对聚簇索引页的挤占。

这并不表示 DYNAMIC 让 TEXT 或超长 VARCHAR 变成零成本：

• 查询长字段仍需读取溢出页；
• 频繁读取长字段可能增加随机 I/O；
• 更新长字段可能产生更多版本和页操作；
• 二级索引不能直接索引任意长度的完整长字段，通常需要索引前缀；
• 行总长度和单列长度限制仍然存在。

2.4 VARCHAR、TEXT 与行格式不能混为一谈

VARCHAR(1000) 和 TEXT 的差别不是简单的“一个在行内，一个在行外”。

实际存储取决于：

• 字段的实际值长度；
• 行中其他字段的长度；
• 行格式；
• 页面大小；
• 是否有压缩；
• 是否需要溢出存储。

例如：

CREATE TABLE article (
    article_id BIGINT UNSIGNED NOT NULL,
    title      VARCHAR(200) NOT NULL,
    body       LONGTEXT NULL,
    PRIMARY KEY (article_id)
) ENGINE = InnoDB ROW_FORMAT = DYNAMIC;

title 大多数情况下适合留在聚簇记录中。body 可能放在溢出页，聚簇记录保留必要的字段信息和外部页指针。

如果列表页只查询标题：

SELECT article_id, title
FROM article
ORDER BY article_id
LIMIT 20;

就不会因为选择了 DYNAMIC 而自动读取完整正文。相反，下面的查询明确要求读取正文：

SELECT article_id, title, body
FROM article
WHERE article_id = 100;

此时读取溢出页是正常成本。

2.5 行格式与“行太大”错误

以下表定义可能在插入数据时遇到行大小问题：

CREATE TABLE wide_row (
    id BIGINT NOT NULL PRIMARY KEY,
    a VARCHAR(2000) NOT NULL,
    b VARCHAR(2000) NOT NULL,
    c VARCHAR(2000) NOT NULL,
    d VARCHAR(2000) NOT NULL,
    e VARCHAR(2000) NOT NULL
) ENGINE = InnoDB;

原因不是单纯的字符数相加。对于 utf8mb4，一个字符最多可能占 4 个字节；变长字段还需要长度信息；记录和页面还需要管理空间。

可用以下方法验证实际边界：

INSERT INTO wide_row VALUES
(
    1,
    REPEAT('a', 2000),
    REPEAT('b', 2000),
    REPEAT('c', 2000),
    REPEAT('d', 2000),
    REPEAT('e', 2000)
);

如果失败，应查看错误文本，而不是只根据 DDL 中的字符长度推断。常见错误包括：

• Row size too large
• Data too long for column
• 字符集转换导致的长度超限

解决方式应针对原因选择：

• 缩短确实不需要那么长的字段；
• 使用更合适的字段类型；
• 把不常访问的大字段拆到扩展表；
• 使用现代行格式；
• 检查字符集造成的字节数放大；
• 不要把所有可选属性堆在一张“超宽表”中。

拆表也有代价。例如：

CREATE TABLE product (
    product_id BIGINT UNSIGNED NOT NULL,
    name       VARCHAR(200) NOT NULL,
    PRIMARY KEY (product_id)
) ENGINE = InnoDB;

CREATE TABLE product_detail (
    product_id BIGINT UNSIGNED NOT NULL,
    description TEXT NULL,
    extra_json JSON NULL,
    PRIMARY KEY (product_id)
) ENGINE = InnoDB;

这样可以让常用查询避开大字段，但读取完整对象需要一次额外的主键查找或连接。它是访问模式的取舍，不是无条件优化。

---

三、NULL：缺失状态，不是特殊字符串

3.1 NULL 的语义

NULL 表示“没有值”或“未知”，不是：

• 空字符串 ''；
• 数字 0；
• 字符串 'NULL'；
• 默认值；
• 任意一个可比较的普通值。

因此：

SELECT NULL = NULL;

结果不是 TRUE，而是 NULL。在 SQL 的三值逻辑中，逻辑结果可以是：

• TRUE
• FALSE
• UNKNOWN，通常由 NULL 表示

WHERE 只保留条件结果为 TRUE 的行。于是：

SELECT *
FROM user_profile
WHERE deleted_at = NULL;

通常返回 0 行，因为比较结果是 UNKNOWN。

判断空值必须使用：

WHERE deleted_at IS NULL

或者：

WHERE deleted_at IS NOT NULL

3.2 三值逻辑的实际推导

设：

p = (x = 10)
q = (y = 20)

如果 x 是 NULL，则 p = UNKNOWN。

对于 AND：

p	q	p AND q
UNKNOWN	FALSE	FALSE
UNKNOWN	TRUE	UNKNOWN
UNKNOWN	UNKNOWN	UNKNOWN

对于 OR：

p	q	p OR q
UNKNOWN	TRUE	TRUE
UNKNOWN	FALSE	UNKNOWN
UNKNOWN	UNKNOWN	UNKNOWN

例如：

WHERE status = 'PAID'
  AND cancelled_at = NULL

第二个条件不是“未取消”，而是 UNKNOWN，所以无法得到预期结果。正确写法是：

WHERE status = 'PAID'
  AND cancelled_at IS NULL

NOT 也不会把未知变成真：

NOT (NULL = 1)

结果仍是 UNKNOWN，而不是 TRUE。

3.3 NULL 与索引

允许 NULL 的列可以建立索引：

CREATE INDEX idx_deleted_at ON user_profile (deleted_at);

查询：

SELECT *
FROM user_profile
WHERE deleted_at IS NULL;

优化器在适合的统计信息和数据分布下可以使用该索引。NULL 并不意味着该列完全不能被索引。

但 NULL 会影响唯一约束。对于：

CREATE TABLE contact (
    id    BIGINT UNSIGNED NOT NULL PRIMARY KEY,
    phone VARCHAR(30) NULL,
    UNIQUE KEY uk_phone (phone)
) ENGINE = InnoDB;

通常可以插入多行 phone IS NULL：

INSERT INTO contact (id, phone) VALUES
(1, NULL),
(2, NULL);

这是因为唯一索引约束的是已知值之间不能重复，而多个未知值不被视为同一个普通值。

如果业务规则是“每行都必须有电话，并且电话不能重复”，应写成：

phone VARCHAR(30) NOT NULL,
UNIQUE KEY uk_phone (phone)

如果业务规则是“电话可选，但只要填写就不能重复”，允许多个 NULL 正好表达该规则。

3.4 COUNT、聚合与 NULL

COUNT(*)

统计行数，不忽略 NULL 行。

COUNT(phone)

只统计 phone IS NOT NULL 的行。

示例：

CREATE TABLE score (
    id    INT PRIMARY KEY,
    value INT NULL
) ENGINE = InnoDB;

INSERT INTO score VALUES
(1, 10),
(2, NULL),
(3, 20);

结果：

SELECT COUNT(*) AS rows_total,
       COUNT(value) AS values_present,
       AVG(value) AS average_value
FROM score;

逻辑上为：

rows_total     = 3
values_present = 2
average_value  = 15

AVG 计算时忽略 NULL，但如果所有值都是 NULL，结果也是 NULL，不是 0。

3.5 默认值不等于非空约束

created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP

表达两个不同规则：

1. 应用不提供值时，服务器可生成当前时间；
2. 显式写入的最终值不能为 NULL。

而：

created_at DATETIME NULL DEFAULT NULL

表示该列可以没有值。默认值不是数据完整性的替代品。

对状态列而言，以下设计通常比大量 NULL 更容易推理：

status ENUM('PENDING', 'PAID', 'CANCELLED') NOT NULL
       DEFAULT 'PENDING'

如果“尚未计算”与“计算结果为 0”在业务上不同，才应保留 NULL：

discount_amount DECIMAL(12,2) NULL

其含义应明确为：

NULL = 尚未计算或不适用
0.00 = 已计算，结果为零

否则，NULL 会在查询、报表、唯一约束和应用序列化中扩散出额外分支。

---

四、生成列：把确定性表达式变成表的一部分

4.1 生成列的定义

生成列（generated column）不是由应用直接写入的列，而是由其他列通过表达式计算得到。MySQL 支持两种主要形式：

• VIRTUAL：通常不把计算结果作为普通数据长期存储，读取时计算；
• STORED：把结果存储下来，写入或相关列变化时计算。

示例：

CREATE TABLE event (
    event_id    BIGINT UNSIGNED NOT NULL,
    payload     JSON NOT NULL,
    event_type  VARCHAR(40)
        GENERATED ALWAYS AS (JSON_UNQUOTE(payload->'$.type')) STORED,
    PRIMARY KEY (event_id),
    KEY idx_event_type (event_type)
) ENGINE = InnoDB;

插入时只提供基础列：

INSERT INTO event (event_id, payload)
VALUES
(1, JSON_OBJECT('type', 'login',  'user_id', 10)),
(2, JSON_OBJECT('type', 'logout', 'user_id', 10));

查询生成列：

SELECT event_id, event_type
FROM event
ORDER BY event_id;

逻辑结果：

1 | login
2 | logout

应用不能把生成列当成普通输入列随意赋值：

INSERT INTO event (event_id, payload, event_type)
VALUES (3, JSON_OBJECT('type', 'login'), 'logout');

该语句会因试图显式写入生成列而失败。这样可以防止基础数据与派生数据不一致。

4.2 VIRTUAL 与 STORED 的取舍

假设：

CREATE TABLE document (
    id       BIGINT UNSIGNED NOT NULL,
    content  JSON NOT NULL,
    lang     VARCHAR(10)
        GENERATED ALWAYS AS (JSON_UNQUOTE(content->'$.lang')) VIRTUAL,
    PRIMARY KEY (id)
) ENGINE = InnoDB;

VIRTUAL 的特点：

• 基础列存储变化时不需要额外保存结果；
• 读取生成列时需要计算；
• 可以在满足条件时建立索引；
• 计算复杂、读取频繁时，读取 CPU 成本可能明显。

改为：

lang VARCHAR(10)
    GENERATED ALWAYS AS (JSON_UNQUOTE(content->'$.lang')) STORED

STORED 的特点：

• 结果占用行存储空间；
• 插入或基础列更新时计算；
• 读取时可直接使用存储结果；
• 更适合计算成本高、读取频繁，或需要把值稳定用于某些结构设计的场景。

核心取舍可以写成：

VIRTUAL：
    节省存储，增加读取计算

STORED：
    增加存储和写入计算，降低读取计算

“生成列一定比应用计算可靠”也不完全正确。它能保证数据库内的派生规则一致，但表达式本身仍需考虑字符集、排序规则、时区和函数确定性。

4.3 生成列、函数索引与查询写法

生成列常用于把 JSON 路径或表达式结果索引化：

CREATE TABLE message (
    id       BIGINT UNSIGNED NOT NULL,
    body     JSON NOT NULL,
    user_id  BIGINT UNSIGNED
        GENERATED ALWAYS AS (
            CAST(JSON_UNQUOTE(body->'$.user_id') AS UNSIGNED)
        ) STORED,
    PRIMARY KEY (id),
    KEY idx_user_id (user_id)
) ENGINE = InnoDB;

查询：

SELECT id, body
FROM message
WHERE user_id = 42;

该查询直接使用了生成列上的索引。

如果写成：

WHERE JSON_UNQUOTE(body->'$.user_id') = '42'

优化器是否能够把它等价改写为生成列索引访问，取决于表达式是否严格匹配、类型转换是否一致以及优化器能力。为了让执行计划稳定，通常应直接查询生成列，或者使用 MySQL 支持的函数索引语法并验证执行计划。

检查计划：

EXPLAIN
SELECT id, body
FROM message
WHERE user_id = 42;

重点观察：

• key 是否为 idx_user_id；
• type 是否从全表扫描改善为索引访问；
• rows 估算是否合理；
• 实际数据分布是否与统计信息相符。

4.4 生成列的限制

生成列表达式不是任意程序。一般需要满足：

• 只能依赖同一行中的列；
• 不能依赖另一行或另一张表；
• 不能依赖每次执行都可能不同的非确定性结果；
• 不能用当前时间这类随执行时刻变化的值作为可靠派生结果；
• 生成列不能再被普通方式直接覆盖；
• 表达式的数据类型、字符集和排序规则必须明确。

例如，不应把：

CURRENT_TIMESTAMP

作为“由其他业务列生成”的持久派生结果。创建时间应使用普通列的默认值：

created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)

而不是把当前时间伪装成生成列。

另外，生成列结果为 NULL 的可能性必须单独考虑。例如：

CREATE TABLE profile (
    id       BIGINT PRIMARY KEY,
    data     JSON NULL,
    country  CHAR(2)
        GENERATED ALWAYS AS (
            JSON_UNQUOTE(data->'$.country')
        ) VIRTUAL
) ENGINE = InnoDB;

当 data 为 NULL，或路径不存在时，country 可能为 NULL。如果业务要求所有记录都有国家代码，应在基础数据上建立约束或在应用和数据库层共同验证，而不是只依赖生成列。

---

五、分区：一个逻辑表中的多个物理分区

5.1 分区是什么

分区（partitioning）把一个逻辑表拆成多个分区。应用仍通过同一个表名访问：

SELECT *
FROM access_log
WHERE occurred_at >= '2025-01-01'
  AND occurred_at <  '2025-02-01';

优化器可以根据分区键判断只访问必要分区，这称为分区裁剪（partition pruning）。

分区不是：

• 自动分库分表；
• 自动把数据放到不同服务器；
• 自动给每个分区建立独立的业务索引；
• 自动解决所有大表查询问题。

在 InnoDB 中，每个分区可以理解为该逻辑表下的一组独立索引树和数据结构，但对 SQL 层仍表现为一张表。

5.2 RANGE 分区：按有序区间划分

时间序列最常见的是 RANGE COLUMNS：

CREATE TABLE access_log (
    id          BIGINT UNSIGNED NOT NULL,
    occurred_at DATETIME(6) NOT NULL,
    user_id     BIGINT UNSIGNED NOT NULL,
    action      VARCHAR(40) NOT NULL,
    PRIMARY KEY (id, occurred_at),
    KEY idx_user_time (user_id, occurred_at)
) ENGINE = InnoDB
PARTITION BY RANGE COLUMNS (occurred_at) (
    PARTITION p202501 VALUES LESS THAN ('2025-02-01'),
    PARTITION p202502 VALUES LESS THAN ('2025-03-01'),
    PARTITION pmax    VALUES LESS THAN (MAXVALUE)
);

这里的含义是：

p202501: occurred_at < 2025-02-01
p202502: 2025-02-01 <= occurred_at < 2025-03-01
pmax:    occurred_at >= 2025-03-01

插入：

INSERT INTO access_log
    (id, occurred_at, user_id, action)
VALUES
    (1, '2025-01-15 10:00:00', 10, 'login'),
    (2, '2025-02-03 11:00:00', 10, 'logout');

第 1 行进入 p202501，第 2 行进入 p202502。

如果没有 pmax，插入超出最后边界的数据会失败：

Table has no partition for value

pmax 可以作为接收未来数据的兜底分区，但它也可能掩盖“忘记增加新分区”的运维问题。使用它时应配合监控，而不是认为分区会自动创建。

RANGE 与 RANGE COLUMNS 不完全相同：

• RANGE 使用表达式结果划分，例如 TO_DAYS(occurred_at)；
• RANGE COLUMNS 直接按列值划分，类型语义通常更直观；
• 多列 RANGE COLUMNS 使用元组的字典序比较，需要严格理解边界。

5.3 LIST 分区：按离散集合划分

CREATE TABLE tenant_data (
    id         BIGINT UNSIGNED NOT NULL,
    tenant_id  INT NOT NULL,
    payload    JSON NOT NULL,
    PRIMARY KEY (id, tenant_id)
) ENGINE = InnoDB
PARTITION BY LIST COLUMNS (tenant_id) (
    PARTITION p_a VALUES IN (1, 2, 3),
    PARTITION p_b VALUES IN (4, 5, 6),
    PARTITION p_other VALUES IN (DEFAULT)
);

LIST 适合有限且稳定的类别集合，例如区域、租户组或业务状态。但租户集合不断增长时，频繁修改分区定义会增加 DDL 和运维复杂度。

它不适合直接替代权限隔离。分区裁剪是访问优化和数据管理机制，不是安全边界。应用仍必须执行租户条件校验，数据库账号权限也不能仅靠分区实现。

5.4 HASH 与 KEY 分区：均匀分散，而不是时间管理

HASH 使用表达式结果分散数据：

CREATE TABLE session_data (
    id         BIGINT UNSIGNED NOT NULL,
    user_id    BIGINT UNSIGNED NOT NULL,
    expires_at DATETIME NOT NULL,
    PRIMARY KEY (id, user_id)
) ENGINE = InnoDB
PARTITION BY HASH (user_id)
PARTITIONS 8;

KEY 由 MySQL 选择哈希方式，语法更适合直接按一个或多个键分散：

CREATE TABLE session_data_key (
    id         BIGINT UNSIGNED NOT NULL,
    user_id    BIGINT UNSIGNED NOT NULL,
    expires_at DATETIME NOT NULL,
    PRIMARY KEY (id, user_id)
) ENGINE = InnoDB
PARTITION BY KEY (user_id)
PARTITIONS 8;

HASH/KEY 的目标通常是把数据均匀分散，降低单个索引树的规模或缓解局部热点；它们不天然提供“删除某个月数据”的能力。若核心需求是按时间快速删除和归档，时间 RANGE 分区更匹配。

增加 HASH 分区数也不是无成本操作。重新组织分区可能需要移动大量数据，不能假设“把 8 改成 16”只是修改一个数字。

---

六、分区键、主键和唯一键的硬约束

6.1 唯一键必须包含分区键

对于分区表，分区键必须包含在每一个唯一键中，包括主键。原因是唯一性检查需要在分区范围内完成，而 MySQL 不允许依赖跨分区的全局唯一索引。

下面的定义在按 occurred_at 分区时存在问题：

CREATE TABLE bad_log (
    id          BIGINT UNSIGNED NOT NULL,
    occurred_at DATETIME NOT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB
PARTITION BY RANGE COLUMNS (occurred_at) (
    PARTITION p0 VALUES LESS THAN ('2025-02-01'),
    PARTITION p1 VALUES LESS THAN (MAXVALUE)
);

PRIMARY KEY (id) 没有包含分区列 occurred_at。正确方向之一是：

PRIMARY KEY (id, occurred_at)

如果业务上需要全局唯一的 id，则还需理解一个事实：PRIMARY KEY (id, occurred_at) 只保证这个组合唯一，不单独保证所有分区中的 id 全局唯一。通常应通过 ID 生成策略保证 id 本身全局不重复，或者重新评估分区键和唯一性需求。

二级非唯一索引通常不要求包含分区键，但查询性能仍要结合分区裁剪和索引顺序判断。

6.2 分区表通常不适合外键关系

MySQL 对 InnoDB 分区表的外键支持存在限制。实际设计中，不应假设分区表可以像普通 InnoDB 表一样自由地：

• 声明引用其他表的外键；
• 被其他表通过外键引用；
• 与外键级联删除一起使用。

如果核心实体关系强依赖数据库外键，而归档又要求按时间分区，常见做法是：

• 保持核心事务表为普通 InnoDB 表；
• 将日志、事件、历史快照等低耦合数据单独设计为分区表；
• 在应用、作业或数据管道中维护归档关系；
• 在引入分区前检查现有外键和迁移工具的能力。

不能为了使用分区而无条件删除外键约束。那会改变数据完整性的保证边界。

6.3 分区键的查询写法决定裁剪效果

以下查询明确给出了分区键范围：

SELECT COUNT(*)
FROM access_log
WHERE occurred_at >= '2025-01-01'
  AND occurred_at <  '2025-02-01';

优化器有机会只访问 p202501。

而以下查询没有提供可用于裁剪的时间范围：

SELECT COUNT(*)
FROM access_log
WHERE user_id = 10;

即使有 idx_user_time (user_id, occurred_at)，也可能需要在多个分区中查找。分区不会让 user_id 查询自动只访问一个分区。

验证方式：

EXPLAIN
SELECT COUNT(*)
FROM access_log
WHERE occurred_at >= '2025-01-01'
  AND occurred_at <  '2025-02-01';

在适用版本和执行计划输出中，观察 partitions 是否只列出目标分区。还应使用真实数据分布验证，因为表达式形式、隐式类型转换、函数包裹和参数类型都可能影响裁剪。

例如：

WHERE DATE(occurred_at) = '2025-01-15'

比直接的半开区间更难优化。通常应写成：

WHERE occurred_at >= '2025-01-15 00:00:00'
  AND occurred_at <  '2025-01-16 00:00:00'

半开区间还避免了时间精度边界问题：

[开始时间, 结束时间)

它包含开始时间，不包含结束时间。

---

七、生成列与分区的组合

生成列有时用于把分区表达式变成显式列，或者把复杂输入归一化后再参与索引设计。但它会增加约束和排障成本。

例如，用日期列直接分区通常最清晰：

CREATE TABLE metric (
    id          BIGINT UNSIGNED NOT NULL,
    recorded_at DATETIME(6) NOT NULL,
    value       DECIMAL(20,6) NOT NULL,
    PRIMARY KEY (id, recorded_at)
) ENGINE = InnoDB
PARTITION BY RANGE COLUMNS (recorded_at) (
    PARTITION p202501 VALUES LESS THAN ('2025-02-01'),
    PARTITION pmax    VALUES LESS THAN (MAXVALUE)
);

如果使用生成列：

CREATE TABLE metric_by_day (
    id          BIGINT UNSIGNED NOT NULL,
    recorded_at DATETIME(6) NOT NULL,
    record_day  DATE
        GENERATED ALWAYS AS (DATE(recorded_at)) STORED,
    value       DECIMAL(20,6) NOT NULL,
    PRIMARY KEY (id, record_day)
) ENGINE = InnoDB
PARTITION BY RANGE COLUMNS (record_day) (
    PARTITION p202501 VALUES LESS THAN ('2025-02-01'),
    PARTITION pmax    VALUES LESS THAN (MAXVALUE)
);

则需要同时确认：

1. 当前 MySQL 版本和目标部署确实支持该分区定义；
2. 生成列表达式满足分区表达式的确定性和类型限制；
3. 主键、唯一键都包含实际分区列；
4. recorded_at 不会因为时区转换产生跨日歧义；
5. 插入和更新时，分区键变化会导致记录从一个分区移动到另一个分区。

最后一点很重要：

UPDATE metric_by_day
SET recorded_at = '2025-02-01 00:00:00'
WHERE id = 1;

如果生成的 record_day 从 2025-01-31 变成 2025-02-01，这不只是修改一个时间字段，还可能是：

1. 重新计算生成列；
2. 判断新分区；
3. 从旧分区删除记录；
4. 把记录插入新分区；
5. 更新相关索引和事务版本。

因此，时间分区表中的时间字段通常应尽量不可变。若事件时间可被业务修正，应评估该更新在高并发下的成本和锁影响。

---

八、时间分区的边界、时区与 NULL

8.1 使用半开区间定义业务时间

表定义：

PARTITION p202501 VALUES LESS THAN ('2025-02-01')

表达的是：

occurred_at < 2025-02-01

不是“截至 2025-01-31 23:59:59”。因此它可以正确覆盖 DATETIME(6) 中的所有微秒值：

2025-01-31 23:59:59.999999

查询也应使用同样的形式：

WHERE occurred_at >= '2025-01-01'
  AND occurred_at <  '2025-02-01'

不要用：

WHERE occurred_at <= '2025-01-31 23:59:59'

因为这会漏掉带有微秒的记录。

8.2 TIMESTAMP 与 DATETIME 的边界

TIMESTAMP 会按照 MySQL 会话时区与 UTC 之间进行转换；DATETIME 主要保存字面上的日期时间值，不做同样的时区转换。

如果分区边界代表 UTC 月份，而连接会话使用本地时区，直接用 TIMESTAMP 做边界判断可能造成“业务日”和“存储日”不一致。设计时必须明确：

分区边界使用哪一个时区？
写入时是否统一转换？
查询参数是否已经转换到同一时区？

一种常见方案是：

• 事件发生时间统一转为 UTC；
• 使用 DATETIME(6) 保存 UTC 字面值；
• 应用展示时再转换为用户时区；
• 分区边界也全部使用 UTC。

另一种方案是使用 TIMESTAMP，但必须把服务器、连接和应用的时区策略固定并验证。不要把时区假设留给默认配置。

8.3 分区键为 NULL 的风险

如果 RANGE 分区键允许 NULL，必须明确 NULL 的归属规则。对于基于有序值的 RANGE 分区，NULL 会被视为小于非空值，通常进入最小边界所在的分区。这个行为容易把“时间未知”的记录混入最早分区。

因此，时间分区表通常声明：

occurred_at DATETIME(6) NOT NULL

如果业务必须允许未知时间，可以选择：

• 单独设置一个明确的“未知”分区或状态；
• 先把记录放入非分区暂存表；
• 使用独立的归档规则；
• 允许其进入专门定义的分区，并对数量设置监控。

不能把 NULL 当成普通的“未归档时间”而不处理。

---

九、分区维护：新增、删除和锁风险

9.1 预先增加未来分区

假设当前定义有：

PARTITION p202501 VALUES LESS THAN ('2025-02-01'),
PARTITION pmax    VALUES LESS THAN (MAXVALUE)

要把 2025-02 从 pmax 拆出来，可以执行：

ALTER TABLE access_log
REORGANIZE PARTITION pmax INTO (
    PARTITION p202502 VALUES LESS THAN ('2025-03-01'),
    PARTITION pmax    VALUES LESS THAN (MAXVALUE)
);

该操作的逻辑是：

1. 读取原 pmax 中的记录；
2. 按新边界把小于 2025-03-01 的记录移动到 p202502；
3. 保留更晚记录在新的 pmax；
4. 更新分区元数据和相关索引。

如果 pmax 已经积累了大量数据，这可能不是一个轻量操作。更安全的维护方式是提前定期增加未来分区，让 pmax 始终很小，或者在设计时避免长期依赖大型兜底分区。

9.2 删除历史分区

删除整个历史分区：

ALTER TABLE access_log
DROP PARTITION p202401;

这会删除该分区中的数据。它通常比逐行执行：

DELETE FROM access_log
WHERE occurred_at >= '2024-01-01'
  AND occurred_at <  '2024-02-01';

更适合批量淘汰整段历史，因为不需要对每一行执行独立的删除逻辑，也不产生同规模的逐行删除事务过程。

但 DROP PARTITION 不是可逆的普通更新。执行前应确认：

• 备份或归档副本已验证；
• 目标分区边界正确；
• 没有仍需访问这些数据的报表或审计任务；
• 复制链路、备份工具和监控可以正确处理该 DDL；
• 元数据锁等待不会阻塞核心业务。

TRUNCATE PARTITION 也会快速清空指定分区，但它是破坏性操作，不等于归档。

9.3 DROP PARTITION 与逐行 DELETE 的差异

逐行删除：

DELETE FROM access_log
WHERE occurred_at < '2024-01-01';

可能涉及：

• 大量行锁和 undo；
• 长事务；
• 二级索引逐行维护；
• 复制延迟；
• purge 压力；
• 大量 redo/undo；
• 删除后空间不一定立即归还给操作系统。

删除分区：

ALTER TABLE access_log
DROP PARTITION p202312;

更接近“移除一组物理数据结构”。但它：

• 不能只删除分区中的部分行；
• 需要正确的分区边界；
• 可能持有元数据锁；
• 删除后数据无法从事务中恢复；
• 仍要考虑备份、复制和审计要求。

如果只需要删除部分历史数据，应使用分批 DELETE，并根据执行计划、锁等待和复制延迟控制批次。

---

十、归档：把数据生命周期变成可验证的状态机

分区和归档不是同一个概念：

• 分区解决逻辑表内部的数据组织、裁剪和批量维护；
• 归档解决数据从热存储迁移到低成本或只读存储；
• DROP PARTITION 只负责删除，不自动生成归档副本。

一个可靠归档流程至少应有这些状态：

在线
  -> 已冻结
  -> 已复制到归档表或归档介质
  -> 已校验
  -> 已从在线表删除
  -> 可恢复验证

10.1 方案一：复制到归档表，再删除分区

可以先建立结构相同的非分区归档表：

CREATE TABLE access_log_archive LIKE access_log;

如果原表是分区表，LIKE 得到的定义需要进一步检查。归档表通常不应继续复制原来的分区结构，实际操作前应通过：

SHOW CREATE TABLE access_log\G
SHOW CREATE TABLE access_log_archive\G

核对字段、索引、字符集、排序规则和行格式。必要时显式创建归档表，而不是盲目依赖 LIKE 的结果。

按时间复制：

INSERT INTO access_log_archive
    (id, occurred_at, user_id, action)
SELECT
    id, occurred_at, user_id, action
FROM access_log
WHERE occurred_at >= '2024-01-01'
  AND occurred_at <  '2024-02-01';

然后校验：

SELECT COUNT(*), MIN(id), MAX(id)
FROM access_log
WHERE occurred_at >= '2024-01-01'
  AND occurred_at <  '2024-02-01';

SELECT COUNT(*), MIN(id), MAX(id)
FROM access_log_archive
WHERE occurred_at >= '2024-01-01'
  AND occurred_at <  '2024-02-01';

数量相同并不足以证明完全一致。还应根据业务选择：

• 主键范围；
• 按日期和租户分组计数；
• 校验和；
• 随机抽样比对；
• 归档表是否存在重复主键。

确认归档副本可读后，再执行：

ALTER TABLE access_log
DROP PARTITION p202401;

这里的“复制”和“删除”不一定能构成一个跨在线表与外部归档介质的原子事务。进程可能在复制成功、校验完成、删除前崩溃，也可能在删除后归档索引尚未更新。因此归档任务必须可重试、可审计，且删除动作要依赖明确的成功标记。

10.2 方案二：交换分区

EXCHANGE PARTITION 可以把一个分区与一个非分区表交换，目标是避免把整批数据逐行复制到归档表。

典型方向是：

ALTER TABLE access_log
EXCHANGE PARTITION p202401
WITH TABLE access_log_archive
WITHOUT VALIDATION;

执行前必须满足严格的结构条件，例如：

• 两张表的列数量和定义匹配；
• 索引定义及相关属性符合要求；
• 存储引擎和行格式等表属性满足交换要求；
• 目标表是非分区表；
• 外键、临时表等限制不构成冲突；
• 使用 WITHOUT VALIDATION 时，必须由调用方保证归档表中的所有行确实属于该分区边界。

交换后的状态近似为：

交换前：
    access_log.p202401       = 2024-01 数据
    access_log_archive       = 空表或旧数据

交换后：
    access_log.p202401       = access_log_archive 原有数据
    access_log_archive       = 原 p202401 的数据

这不是复制，而是元数据层面的归属交换；因此它可能显著减少数据搬运。但 WITHOUT VALIDATION 具有明确风险：如果归档表中混入了不属于 p202401 的记录，交换后在线表的分区语义就被破坏。

若使用默认校验，MySQL 需要验证交换表中的行是否满足分区条件；数据量较大时，校验本身也有成本。应先在测试环境验证目标版本的 DDL 行为、锁等待和复制表现，再用于生产。

交换成功后，归档表保存原分区数据，在线表中的 p202401 则保存交换前归档表的数据。若原归档表为空，可以再对在线表的空分区执行：

ALTER TABLE access_log TRUNCATE PARTITION p202401;

或者按实际生命周期重新组织分区。不要把“交换成功”误认为“历史数据已经删除”；必须检查在线分区和归档表中的行数。

10.3 归档任务的并发一致性

如果在线表仍在写入，归档条件必须与分区边界和写入规则一致。常见问题包括：

• 归档查询执行期间，迟到数据写入旧时间范围；
• 业务允许修改 occurred_at，导致记录跨分区移动；
• 时区转换让应用认为是某天，数据库却按另一天分区；
• 复制延迟导致从库或归档读取到的视图不同；
• 归档表已有重复主键，INSERT 中途失败；
• 归档成功后任务在删除前崩溃，重试时重复插入。

可行的控制方法包括：

1. 只归档已经封存的时间窗口；
2. 设置“数据冻结时间”，例如只处理早于当前时间若干天的数据；
3. 禁止或严格审计历史时间列更新；
4. 采用幂等写入或记录归档批次；
5. 归档前后保存行数和校验摘要；
6. 删除前确认归档副本已被独立读取验证；
7. 将 DDL、复制延迟和锁等待纳入任务状态。

归档的核心不是一条 SQL，而是“数据是否可以安全从热表移除”的证明过程。

---

十一、完整示例：按月分区、生成列索引与归档边界

下面设计一张事件表：

CREATE TABLE app_event (
    event_id     BIGINT UNSIGNED NOT NULL,
    occurred_at  DATETIME(6) NOT NULL,
    tenant_id    BIGINT UNSIGNED NOT NULL,
    payload      JSON NOT NULL,
    event_type   VARCHAR(40)
        GENERATED ALWAYS AS (
            JSON_UNQUOTE(payload->'$.type')
        ) STORED,

    PRIMARY KEY (event_id, occurred_at),
    KEY idx_tenant_time (tenant_id, occurred_at),
    KEY idx_event_type (event_type)
) ENGINE = InnoDB
  ROW_FORMAT = DYNAMIC
PARTITION BY RANGE COLUMNS (occurred_at) (
    PARTITION p202501 VALUES LESS THAN ('2025-02-01'),
    PARTITION p202502 VALUES LESS THAN ('2025-03-01'),
    PARTITION pmax    VALUES LESS THAN (MAXVALUE)
);

逐项解释：

主键

PRIMARY KEY (event_id, occurred_at)

包含分区键 occurred_at，满足分区唯一键约束。event_id 仍应由系统保证全局唯一，否则组合主键只能保证组合不重复。

时间列

occurred_at DATETIME(6) NOT NULL

• 不允许未知时间进入普通时间分区；
• 保留微秒；
• 查询使用半开区间；
• 设计上应明确它是否保存 UTC。

生成列

event_type VARCHAR(40)
    GENERATED ALWAYS AS (
        JSON_UNQUOTE(payload->'$.type')
    ) STORED

从 JSON 中提取事件类型，并建立普通索引。基础数据是 payload，派生数据是 event_type，应用不能绕过表达式直接写入后者。

行格式

ROW_FORMAT = DYNAMIC

适合可能含有较长 JSON 的现代 InnoDB 表，但长 JSON 仍可能使用溢出页，不能据此认为读取 payload 没有额外成本。

分区查询

EXPLAIN
SELECT COUNT(*)
FROM app_event
WHERE occurred_at >= '2025-01-01'
  AND occurred_at <  '2025-02-01'
  AND tenant_id = 100;

优化器可以先通过时间条件裁剪到 p202501，再在该分区的 idx_tenant_time 上按租户和时间查找。

如果查询只有：

SELECT COUNT(*)
FROM app_event
WHERE tenant_id = 100;

则不能根据分区键裁剪到单个月份，可能需要访问多个分区。此时分区没有替代按租户组织数据的作用。

边界归档

当 p202501 已经封存后，可以：

1. 将 p202501 交换到归档表，或复制并校验；
2. 验证归档副本；
3. 从在线表删除该分区；
4. 保留归档表的检索和恢复记录。

如果采用交换方式，必须先通过 SHOW CREATE TABLE 核验表定义，不能只因为“列看起来一样”就执行 WITHOUT VALIDATION。

---

十二、诊断表设计问题的工具链

12.1 检查主键和索引

SHOW CREATE TABLE app_event\G
SHOW INDEX FROM app_event;

重点确认：

• 是否存在显式主键；
• 主键列顺序是否符合主要访问路径；
• 唯一索引是否包含分区键；
• 二级索引是否因主键过长而膨胀；
• 索引列的字符集、排序规则和前缀长度是否符合预期。

12.2 检查行格式和表空间

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    ENGINE,
    ROW_FORMAT,
    TABLE_ROWS,
    DATA_LENGTH,
    INDEX_LENGTH
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'app_event';

TABLE_ROWS 对 InnoDB 可能是估算值，不能把它当成精确行数。空间字段也应结合表统计刷新、压缩和实际文件观察。

12.3 检查分区边界

SELECT
    PARTITION_NAME,
    PARTITION_METHOD,
    PARTITION_EXPRESSION,
    PARTITION_DESCRIPTION,
    TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'app_event'
ORDER BY PARTITION_ORDINAL_POSITION;

注意：

• PARTITION_DESCRIPTION 对 RANGE 分区表示边界描述；
• TABLE_ROWS 同样可能是估算值；
• 分区名存在不代表数据一定在预期范围，边界需要结合 DDL 核对；
• 维护前后应记录分区列表变化。

12.4 检查执行计划

EXPLAIN
SELECT event_id
FROM app_event
WHERE occurred_at >= '2025-02-01'
  AND occurred_at <  '2025-03-01'
  AND tenant_id = 100;

检查两个独立问题：

1. partitions 是否体现了分区裁剪；
2. key 是否选择了合适的分区内索引。

只看到 key = idx_tenant_time，不能证明分区裁剪发生；只看到访问了一个分区，也不能证明分区内索引有效。

---

十三、常见误解与失败表现

误解一：有分区就不需要索引

错误。分区先缩小可能访问的物理范围，分区内仍然需要索引定位记录。没有合适索引时，查询可能在每个候选分区内扫描大量数据。

误解二：NULL 等于空字符串

错误。以下三个值不同：

NULL
''
'NULL'

应用序列化、JSON 输出、报表聚合和唯一约束都会受到影响。

误解三：唯一索引中的 NULL 只能出现一次

通常错误。允许 NULL 的唯一索引通常允许多行 NULL。如果业务要求唯一，必须声明 NOT NULL，或设计明确的规范化规则。

误解四：STORED 生成列可以像缓存一样手工修正

错误。它仍由数据库表达式维护。若需要人工修正，应修改基础列或显式设计普通列，并承担数据一致性责任。

误解五：删除分区就是归档

错误。删除分区是销毁在线副本。没有经过复制、校验和恢复验证，就不能称为安全归档。

误解六：pmax 会自动生成下个月分区

错误。MAXVALUE 只是边界兜底，不会自动创建 p202503。如果长期不维护，所有新数据都会堆进 pmax，后续重组成本可能变大。

误解七：分区能替代分库分表

错误。分区仍属于同一个 MySQL 实例和逻辑表。它不自动解决跨节点容量、租户隔离、跨库事务或跨实例故障域问题。

---

十四、设计决策应围绕数据生命周期展开

可以用以下因果链检查一张表：

业务身份
  -> 主键类型和稳定性
  -> 聚簇索引顺序
  -> 二级索引携带的主键大小
  -> 页占用、缓存和写入局部性

字段语义
  -> 是否允许 NULL
  -> 比较和聚合结果
  -> 唯一约束和查询谓词
  -> 应用中的缺失状态处理

字段长度与访问模式
  -> 行格式和溢出页
  -> 常用查询是否读取大字段
  -> 宽行风险和拆表边界

派生查询条件
  -> 生成列或函数索引
  -> 计算成本和确定性
  -> 索引可用性与类型一致性

数据生命周期
  -> 分区键和边界
  -> 分区裁剪
  -> 新分区维护
  -> 封存、交换、校验和删除

一个可靠的表定义，不只是“能插入数据”，还应能回答：

• 一行如何被唯一定位；
• 二级索引需要携带多少主键数据；
• 长字段是否会挤压常用行；
• NULL 究竟表示什么业务状态；
• 派生值由谁负责计算；
• 查询是否能利用分区裁剪；
• 历史数据如何在不误删的情况下归档；
• 失败发生在复制、校验、删除还是恢复阶段；
• 每个阶段如何验证和重试。

当这些问题都能通过表定义、执行计划、分区元数据和归档记录得到验证时，主键、行格式、NULL、生成列、分区与归档才真正形成了一套可维护的 InnoDB 数据模型，而不是彼此孤立的语法选项。

---

系列导航与关联阅读

• 系列入口：数据库完整学习路线：从关系模型、事务索引到分布式与向量检索
• 上一篇：MySQL 数据类型、字符集与排序规则：精度、编码和索引影响
• 下一篇：MySQL Redo、Undo 与 Binlog：提交链路、崩溃恢复和一致性
• 延伸：MySQL 分区表：Range、List、Hash、裁剪、维护和适用边界

官方资料

• MySQL 8.4 Reference Manual
• MySQL InnoDB Storage Engine

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。