Schema 设计最佳实践

MySQL Schema 设计指南

  基于 MySQL 8.0 / 8.4
  15 个专题
  阅读约 35 分钟

Schema 设计是数据库性能的基石。错误的数据类型选择可能浪费数十 GB 存储空间，不恰当的范式化导致查询需要数十次 JOIN，而过度反范式又会引发数据不一致。本章从底层存储原理出发，系统讲解每种数据类型的内存/磁盘开销、范式理论的实际应用、反范式的收益与代价、以及电商和社交网络两大典型场景的完整 Schema 设计案例。

1. Schema 设计原则总览

在开始讨论具体数据类型之前，需要先理解几条核心原则。这些原则贯穿整个 Schema 设计过程，违反任何一条都可能导致严重的性能或维护问题。

1.1 越小越好 (Smaller is Better)

更小的数据类型意味着：更少的磁盘 I/O、更多的行能放进 Buffer Pool 页、更快的排序和比较操作、更小的索引树。InnoDB 的一个 16KB 页面能存放的行越多，一次随机 I/O 获取的有效数据就越多。这是所有优化中回报最高的。

1.2 越简单越好 (Simpler is Better)

整数比字符串的比较操作开销低。使用 MySQL 内置的 DATE/DATETIME 类型存储日期，而不是字符串。使用整数存储 IP 地址（INET_ATON()/INET_NTOA()），而不是 VARCHAR(15)。简单类型不仅节省空间，还让优化器能做出更好的执行计划。

1.3 避免 NULL（除非确实需要）

NULL 列在 InnoDB 中需要额外的位图空间来标记哪些列为 NULL。可为 NULL 的列会让索引统计和值比较更加复杂。如果一个列在业务上总是有值，就声明为 NOT NULL 并给一个有意义的默认值。但不要为了避免 NULL 而用空字符串或魔术数字（-1、0、9999）来替代——那会导致更严重的语义混乱。

1.4 为查询模式设计，而非为存储模式设计

先明确业务的读写比例和核心查询路径，再决定如何组织表结构。一个写多读少的日志系统和一个读多写少的商品展示页面，即使底层数据模型相同，Schema 设计也应该完全不同。

核心思维方式：Schema 设计是在「存储效率」「查询性能」「开发便利」「数据一致性」四个维度之间做权衡。没有放之四海皆准的方案，只有在约束条件下的最优解。

2. 整数类型：INT vs BIGINT vs TINYINT

整数类型是 MySQL 中最常用的数据类型。选择正确的整数宽度直接影响存储空间和索引大小。

2.1 INT vs BIGINT：何时升级？

对于自增主键，INT UNSIGNED 最大值约 42.9 亿，对于大多数业务来说足够了。但在以下场景必须使用 BIGINT：

• 分布式 ID（雪花算法）：64 位 ID 超出 INT 范围
• 日志/事件表：每秒数千行写入，数年后会超过 42.9 亿
• 合并分片：多个分片各自有 42.9 亿的 ID 空间，合并后可能冲突

存储开销计算：从 INT(4B) 升级到 BIGINT(8B)，每行主键多 4 字节。如果有 5 个二级索引，每个二级索引的叶节点也要存主键值，那么每行实际多 4 x (1 + 5) = 24 字节。1 亿行就是 2.4 GB 额外存储。请在设计阶段就做好这个计算。

2.2 INT(11) 中的数字是什么？

INT(11) 中的 11 不影响存储空间，不影响值范围。它只是指定了 ZEROFILL 时的显示宽度。MySQL 8.0.17+ 已废弃此语法。在建表时直接写 INT 或 INT UNSIGNED，不需要指定宽度。

-- Bad: misleading width specifiers (deprecated in 8.0.17+)
CREATE TABLE bad_example (
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  status TINYINT(1),  -- does NOT mean boolean; still stores -128..127
  PRIMARY KEY (id)
);

-- Good: clean, modern syntax
CREATE TABLE good_example (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  status TINYINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);

2.3 UNSIGNED 的陷阱

UNSIGNED 列的减法操作可能产生溢出错误。例如两个 UNSIGNED 列做差，如果结果为负值，MySQL 会在 NO_UNSIGNED_SUBTRACTION SQL mode 关闭时报错。在 MySQL 8.0+ 中，默认 SQL mode 已经包含了这个保护，但需要注意跨版本兼容性。

-- Potential issue with UNSIGNED subtraction
SELECT col_a - col_b FROM t WHERE col_a < col_b;
-- If both are UNSIGNED and col_a < col_b → ERROR 1690 or huge number

-- Safe approach: CAST to SIGNED
SELECT CAST(col_a AS SIGNED) - CAST(col_b AS SIGNED) FROM t;

3. 字符串类型：CHAR vs VARCHAR vs TEXT

3.1 存储机制差异

3.2 CHAR 适用场景

CHAR 适合存储长度固定或非常接近固定的值：

• MD5 哈希：CHAR(32)
• ISO 国家代码：CHAR(2)
• UUID（去掉横线后）：CHAR(32) 或更好的 BINARY(16)
• Y/N 标记：CHAR(1)

3.3 VARCHAR 长度选择策略

虽然 VARCHAR(255) 和 VARCHAR(1000) 存储同一个 10 字符的字符串消耗的磁盘空间相同，但声明长度仍然重要，原因如下：

1. 内存分配：MySQL 的内存临时表（MEMORY 引擎）会按 VARCHAR 的最大声明长度分配固定空间。VARCHAR(1000) 在 utf8mb4 下的临时表中占 4000 字节/行
2. 排序缓冲区：sort_buffer 也基于声明长度分配
3. 长度前缀开销：N ≤ 255 用 1 字节前缀，N > 255 用 2 字节前缀

最佳实践：按业务实际最大长度设置 VARCHAR，并尽量控制在 255 以内以获得 1 字节长度前缀的优势。用户名 VARCHAR(50)，邮箱 VARCHAR(254)（RFC 5321），URL VARCHAR(2083)（IE 最大 URL 长度）。

3.4 TEXT vs VARCHAR：何时用 TEXT？

TEXT 和 VARCHAR 的核心区别在于溢出行为。当数据超过 InnoDB 页面的行内存储限制（DYNAMIC 行格式下约 768 字节的前缀 + 溢出页指针），两者都会溢出到外部页。但 TEXT 列不能有默认值（MySQL 8.0.13 之前），不能作为内存临时表的一部分（会强制转为磁盘临时表）。

选 VARCHAR 当...

• 需要索引整个列
• 长度在 65,535 以内且可预估
• 需要设置默认值
• 列参与排序和分组

选 TEXT 当...

• 存储文章、日志、评论等大文本
• 长度变化极大（几字节到数 KB）
• 很少参与排序
• 可以单独查询（避免 SELECT *）

3.5 ENUM vs VARCHAR

ENUM 在 InnoDB 内部以 1-2 字节整数存储，比 VARCHAR 更紧凑。但 ENUM 的修改（添加/删除成员）需要 ALTER TABLE，在大表上代价很高。

-- ENUM: compact but inflexible
CREATE TABLE orders (
  status ENUM('pending','paid','shipped','delivered','cancelled') NOT NULL DEFAULT 'pending'
);
-- Storage: 1 byte (up to 255 members) or 2 bytes (up to 65535 members)

-- Alternative: TINYINT + application-level mapping (more flexible)
CREATE TABLE orders (
  status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0=pending,1=paid,2=shipped,3=delivered,4=cancelled'
);

推荐做法：如果枚举值极少变化（如性别、大洲），ENUM 是好选择。如果可能频繁增减成员（如订单状态、支付方式），用 TINYINT + 应用层映射更灵活。永远不要用 VARCHAR 存储可枚举的值——浪费空间且容易出错。

4. 日期与时间类型：DATETIME vs TIMESTAMP

4.1 2038 问题（Y2038）

TIMESTAMP 使用 32 位 Unix 时间戳，最大值为 2038-01-19 03:14:07 UTC。当业务数据可能涉及 2038 年以后的日期（如合同到期日、保修期、生日），必须使用 DATETIME。MySQL 官方已经在讨论 64 位 TIMESTAMP 的扩展方案，但目前尚未正式实现。

4.2 时区处理最佳实践

-- Verify current time zone settings
SELECT @@global.time_zone, @@session.time_zone;

-- Best practice: store all times in UTC
SET GLOBAL time_zone = '+00:00';

-- TIMESTAMP auto-converts; DATETIME does not
CREATE TABLE events (
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  -- ↑ Stored as UTC, displayed in session time zone
  event_date  DATETIME NOT NULL,
  -- ↑ Stored exactly as inserted, no conversion
  updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

4.3 亚秒精度 (FSP)

MySQL 5.6.4+ 支持亚秒精度，范围 0-6（微秒）。每增加精度需要额外的存储空间：

-- Millisecond precision for API logs
CREATE TABLE api_logs (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  endpoint    VARCHAR(200) NOT NULL,
  latency_ms  SMALLINT UNSIGNED NOT NULL,
  created_at  TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY idx_created (created_at)
);

存储日期/时间的替代方案：有些团队用 BIGINT 存 Unix 毫秒时间戳。这样做的好处是避免时区转换问题、比较运算更快，缺点是可读性差、无法直接使用 DATE_FORMAT 等函数。推荐仅在需要跨语言/跨系统一致性的场景使用。

5. 精确与浮点数值：DECIMAL vs FLOAT vs DOUBLE

5.1 为什么金额不能用 FLOAT？

IEEE 754 浮点数的二进制表示无法精确存储十进制小数。经典示例：0.1 + 0.2 = 0.30000000000000004。在金融场景中，这种误差在大量累加后会变得不可忽略。

-- Demonstration: FLOAT precision loss
CREATE TABLE float_test (amount FLOAT);
INSERT INTO float_test VALUES (0.1), (0.2);
SELECT SUM(amount) FROM float_test;
-- Result: 0.30000001192092896  (not exactly 0.3)

-- Correct approach for money: DECIMAL
CREATE TABLE decimal_test (amount DECIMAL(10,2));
INSERT INTO decimal_test VALUES (0.10), (0.20);
SELECT SUM(amount) FROM decimal_test;
-- Result: 0.30  (exact)

5.2 DECIMAL 存储空间计算

DECIMAL(M,D) 的存储规则：整数部分 (M-D) 位和小数部分 D 位分别按「每 9 位数字 = 4 字节」计算，不足 9 位按下表：

例如 DECIMAL(19,4)：整数部分 15 位 = 4+4+3 = 11 字节 (9位 + 6位)，不对——准确计算：15 位 = 9 位 (4B) + 6 位 (3B) = 7 字节；小数部分 4 位 = 2 字节。总计 7 + 2 = 9 字节。

5.3 金额存储的替代方案：整数分

-- Store money as integer cents (avoids DECIMAL overhead)
CREATE TABLE payments (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  amount_cents INT UNSIGNED NOT NULL COMMENT 'Amount in cents, e.g. 1999 = $19.99',
  currency     CHAR(3) NOT NULL DEFAULT 'USD',
  PRIMARY KEY (id)
);

-- Display: divide by 100 in application layer
-- Advantage: INT is 4 bytes, DECIMAL(10,2) is 5 bytes
-- Advantage: integer arithmetic is faster, no precision issues
-- Limitation: max $42,949,672.95 with INT UNSIGNED

6. JSON 列：灵活性与性能的权衡

MySQL 5.7.8 引入原生 JSON 类型，8.0 大幅增强。JSON 列以二进制格式存储（不是文本），支持路径查询和部分更新。

6.1 JSON 的优势

• Schema 灵活性：不同行可以有不同的 JSON 结构，无需 ALTER TABLE
• 数据校验：插入时自动验证 JSON 格式，比 TEXT 存储 JSON 字符串更安全
• 路径查询：-> 和 ->> 操作符可以高效提取嵌套字段
• 部分更新：MySQL 8.0 的 JSON_SET()、JSON_REPLACE() 支持原地修改，不需要重写整个 JSON 文档

6.2 JSON 的性能代价

• 不能直接索引 JSON 列：需要通过虚拟生成列 (Virtual Generated Column) 创建索引
• 存储开销：二进制 JSON 通常比等效的关系型列占用更多空间（存储了键名、类型标记、偏移量数组）
• 查询优化器限制：JSON 路径查询无法充分利用优化器的统计信息

-- JSON column with generated column index
CREATE TABLE products (
  id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name      VARCHAR(200) NOT NULL,
  attrs     JSON NOT NULL,
  -- Virtual generated column: extracts 'color' from JSON
  color     VARCHAR(30) GENERATED ALWAYS AS (attrs->>'$.color') VIRTUAL,
  -- Virtual generated column: extracts 'weight' from JSON
  weight_kg DECIMAL(8,2) GENERATED ALWAYS AS (attrs->>'$.weight_kg') VIRTUAL,
  PRIMARY KEY (id),
  KEY idx_color (color),
  KEY idx_weight (weight_kg)
);

-- Insert: only fill 'attrs', generated columns auto-populate
INSERT INTO products (name, attrs) VALUES
  ('Widget A', '{"color":"red","weight_kg":1.5,"material":"steel"}'),
  ('Widget B', '{"color":"blue","weight_kg":0.8,"sizes":[10,20,30]}');

-- Query: use generated column (indexed!)
SELECT * FROM products WHERE color = 'red';

-- Query: JSON path query (no index, full scan)
SELECT * FROM products WHERE attrs->>'$.material' = 'steel';

6.3 Multi-Valued Index (MySQL 8.0.17+)

MySQL 8.0.17 引入多值索引，可以为 JSON 数组中的每个元素建立索引条目，支持 MEMBER OF()、JSON_CONTAINS()、JSON_OVERLAPS() 查询。

-- Multi-valued index on JSON array
CREATE TABLE articles (
  id    INT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(200) NOT NULL,
  tags  JSON NOT NULL,
  PRIMARY KEY (id),
  KEY idx_tags ((CAST(tags AS UNSIGNED ARRAY)))
);

INSERT INTO articles (title, tags) VALUES
  ('Intro to MySQL', '[1, 3, 5]'),
  ('Advanced SQL',   '[2, 3, 7]');

-- Uses multi-valued index:
SELECT * FROM articles WHERE 3 MEMBER OF(tags);
SELECT * FROM articles WHERE JSON_CONTAINS(tags, '[1, 3]');
SELECT * FROM articles WHERE JSON_OVERLAPS(tags, '[5, 7]');

JSON 列使用准则：(1) 只在确实需要灵活 Schema 时使用 JSON；(2) 高频查询的字段提取为生成列并建索引；(3) JSON 文档大小控制在几 KB 以内，避免频繁的溢出页访问；(4) 不要把 JSON 当作「万能口袋」——能拆成关系列的字段就拆出来。

7. 范式化：1NF 到 BCNF 详解

范式化的目的是消除数据冗余和更新异常。每个范式都在前一个范式的基础上增加额外约束。理解每个范式解决了什么问题，比死记定义更重要。

7.1 第一范式 (1NF)：原子性

要求每个列的值都是不可再分的原子值。违反 1NF 的典型例子：

-- Violates 1NF: multiple values in one column
CREATE TABLE students_bad (
  id    INT PRIMARY KEY,
  name  VARCHAR(50),
  phone VARCHAR(200)  -- '13800001111,13900002222,15000003333'
);

-- Correct: separate table for multi-valued attribute
CREATE TABLE students (
  id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE student_phones (
  student_id INT UNSIGNED NOT NULL,
  phone      VARCHAR(20) NOT NULL,
  is_primary TINYINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (student_id, phone),
  FOREIGN KEY (student_id) REFERENCES students(id)
);

7.2 第二范式 (2NF)：消除部分依赖

在满足 1NF 的基础上，所有非键列必须完全依赖于整个主键，不能只依赖于主键的一部分。2NF 只对复合主键的表有意义。

-- Violates 2NF: student_name depends only on student_id, not on (student_id, course_id)
CREATE TABLE enrollments_bad (
  student_id   INT NOT NULL,
  course_id    INT NOT NULL,
  student_name VARCHAR(50),  -- ← partial dependency on student_id only
  course_name  VARCHAR(100), -- ← partial dependency on course_id only
  grade        CHAR(2),
  PRIMARY KEY (student_id, course_id)
);
-- Problem: if a student changes name, must update ALL rows for that student

-- Correct 2NF: split into three tables
CREATE TABLE students     (id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE courses      (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE enrollments  (student_id INT, course_id INT, grade CHAR(2),
                           PRIMARY KEY (student_id, course_id));

7.3 第三范式 (3NF)：消除传递依赖

在满足 2NF 的基础上，非键列不能依赖于其他非键列。即所有非键列必须直接依赖于主键，不存在 A → B → C 这样的传递链。

-- Violates 3NF: city depends on zip_code, not directly on id
CREATE TABLE customers_bad (
  id       INT PRIMARY KEY,
  name     VARCHAR(50),
  zip_code CHAR(6),
  city     VARCHAR(50),    -- ← transitive: id → zip_code → city
  province VARCHAR(30)     -- ← transitive: id → zip_code → province
);
-- Problem: if a zip code's city name changes, must update all matching rows

-- Correct 3NF: separate zip code lookup
CREATE TABLE customers (
  id       INT PRIMARY KEY,
  name     VARCHAR(50),
  zip_code CHAR(6),
  FOREIGN KEY (zip_code) REFERENCES zip_codes(code)
);
CREATE TABLE zip_codes (
  code     CHAR(6) PRIMARY KEY,
  city     VARCHAR(50) NOT NULL,
  province VARCHAR(30) NOT NULL
);

7.4 BC 范式 (BCNF)：消除主属性对非候选键的依赖

BCNF 是 3NF 的加强版。在 3NF 中，只要求非主属性不传递依赖于候选键；而 BCNF 要求表中的每个非平凡函数依赖 X → Y 中，X 必须是超键。BCNF 能处理 3NF 无法解决的某些异常情况，但在实践中 3NF 已经能满足绝大多数需求。

-- Example where 3NF is satisfied but BCNF is violated
-- Scenario: Students choose courses taught by specific professors
-- FDs: {student_id, course} → professor
--      {professor} → course  (each professor teaches only one course)

CREATE TABLE schedule_bad (
  student_id INT,
  course     VARCHAR(50),
  professor  VARCHAR(50),
  PRIMARY KEY (student_id, course)
);
-- professor → course means professor is a determinant but NOT a superkey
-- This violates BCNF

-- BCNF decomposition:
CREATE TABLE prof_courses (
  professor VARCHAR(50) PRIMARY KEY,
  course    VARCHAR(50) NOT NULL
);
CREATE TABLE student_profs (
  student_id INT,
  professor  VARCHAR(50),
  PRIMARY KEY (student_id, professor)
);

实践建议：在 OLTP 系统中，设计到 3NF 通常就足够了。BCNF 分解可能导致更多的表连接和更复杂的查询。在分析系统 (OLAP) 中，通常会有意打破范式以换取查询性能（见下一节的反范式策略）。

8. 反范式策略

反范式是在「查询性能」和「数据一致性维护成本」之间做有意识的权衡。不是不懂范式而乱设计，而是在充分理解范式之后，基于具体的查询模式和性能需求做出的理性选择。

8.1 冗余列（最常见的反范式手段）

-- Normalized: need JOIN to get user name with every order query
SELECT o.id, o.amount, u.name
FROM orders o JOIN users u ON o.user_id = u.id
WHERE o.created_at >= '2025-01-01';

-- Denormalized: add redundant user_name to orders table
ALTER TABLE orders ADD COLUMN user_name VARCHAR(50) NOT NULL DEFAULT '';
-- Trade-off: no JOIN needed, but must update orders.user_name when user changes name

-- Maintenance approach: trigger or application-layer sync
CREATE TRIGGER trg_sync_user_name
AFTER UPDATE ON users FOR EACH ROW
BEGIN
  IF OLD.name != NEW.name THEN
    UPDATE orders SET user_name = NEW.name WHERE user_id = NEW.id;
  END IF;
END;

8.2 汇总表 / 统计表

将 COUNT、SUM、AVG 等聚合结果预计算并存储，避免每次查询都扫描大量原始数据。

-- Summary table: daily sales statistics
CREATE TABLE daily_sales_summary (
  summary_date DATE NOT NULL,
  category_id  INT UNSIGNED NOT NULL,
  order_count  INT UNSIGNED NOT NULL DEFAULT 0,
  total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  avg_amount   DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  updated_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (summary_date, category_id)
);

-- Refresh: scheduled job or triggered after each order
INSERT INTO daily_sales_summary (summary_date, category_id, order_count, total_amount, avg_amount)
SELECT DATE(created_at), category_id,
       COUNT(*), SUM(amount), AVG(amount)
FROM orders
WHERE DATE(created_at) = CURDATE()
GROUP BY DATE(created_at), category_id
ON DUPLICATE KEY UPDATE
  order_count = VALUES(order_count),
  total_amount = VALUES(total_amount),
  avg_amount = VALUES(avg_amount);

8.3 缓存表（物化中间结果）

对于复杂多表 JOIN 的查询结果，可以物化为一张宽表，定期或事件驱动地刷新。

-- Cache table: product listing with pre-joined data
CREATE TABLE product_listing_cache (
  product_id    INT UNSIGNED PRIMARY KEY,
  product_name  VARCHAR(200) NOT NULL,
  brand_name    VARCHAR(100) NOT NULL,
  category_path VARCHAR(300) NOT NULL,  -- 'Electronics > Phones > Smartphones'
  avg_rating    DECIMAL(2,1) NOT NULL DEFAULT 0.0,
  review_count  INT UNSIGNED NOT NULL DEFAULT 0,
  lowest_price  DECIMAL(10,2),
  in_stock      TINYINT UNSIGNED NOT NULL DEFAULT 1,
  updated_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_category (category_path(50)),
  KEY idx_rating (avg_rating DESC),
  KEY idx_price (lowest_price)
);

-- Rebuild: nightly or triggered by product/review/inventory changes

8.4 计数器表模式

高并发场景下，直接在主表上 UPDATE SET count = count + 1 会导致行锁竞争。计数器表通过多行分散更新压力。

-- Counter table: distribute writes across N slots
CREATE TABLE post_counters (
  post_id    BIGINT UNSIGNED NOT NULL,
  slot       TINYINT UNSIGNED NOT NULL COMMENT '0..99',
  like_count INT UNSIGNED NOT NULL DEFAULT 0,
  view_count INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (post_id, slot)
) ENGINE=InnoDB;

-- Initialize 100 slots per post:
-- INSERT INTO post_counters (post_id, slot) VALUES (123, 0), (123, 1), ... (123, 99);

-- Increment: pick a random slot to reduce contention
UPDATE post_counters
SET like_count = like_count + 1
WHERE post_id = 123 AND slot = FLOOR(RAND() * 100);

-- Read: sum all slots
SELECT SUM(like_count) AS total_likes, SUM(view_count) AS total_views
FROM post_counters
WHERE post_id = 123;

-- Periodically consolidate: merge all slots into slot 0, reset others
-- This keeps the read query efficient (fewer rows to sum)

何时使用计数器表：仅当单行更新出现明显的锁等待时才需要。大多数系统中，直接在主表上 count = count + 1 配合短事务就足够了。过度设计计数器表反而增加复杂度。典型需要计数器表的场景：微博点赞（每秒数千次更新同一行）、实时浏览量统计。

8.5 何时反范式、何时不反范式？

适合反范式

• 读多写少，查询频率远高于更新频率
• JOIN 代价高：表很大、JOIN 列无法有效索引
• 冗余数据的一致性可以通过异步/最终一致性保证
• 聚合查询频繁且原始数据量大

不适合反范式

• 写多读少，冗余字段更新成本高于 JOIN
• 数据一致性要求极高（金融、医疗）
• JOIN 可以通过覆盖索引消除 I/O 开销
• 团队没有完善的数据同步机制

9. NULL 深入分析

9.1 NULL 的存储开销

InnoDB 的行格式中，每个允许 NULL 的列会在行头的 NULL 位图中占 1 bit。如果一张表有 8 个 nullable 列，行头增加 1 字节；16 个 nullable 列增加 2 字节。虽然开销不大，但在数十亿行的大表上也会累积。

9.2 NULL 与索引

InnoDB 的 B+Tree 索引可以包含 NULL 值（与 Oracle 不同，Oracle 对全 NULL 的索引键不建条目）。但 NULL 在索引中有特殊处理：

• NULL 值被认为是「小于所有非 NULL 值」并排在索引的最前面
• WHERE col IS NULL 可以使用索引
• 但 COUNT(col) 不计算 NULL 行，COUNT(*) 才计算所有行
• 唯一索引允许多个 NULL（NULL != NULL）

-- NULL surprises in queries
SELECT * FROM t WHERE col != 'A';
-- ↑ Does NOT return rows where col IS NULL!
-- NULL != 'A' evaluates to NULL (not TRUE), so those rows are excluded.

-- To include NULLs:
SELECT * FROM t WHERE col != 'A' OR col IS NULL;

-- NULL in aggregations
SELECT COUNT(col)  FROM t;  -- counts non-NULL values only
SELECT COUNT(*)    FROM t;  -- counts all rows
SELECT AVG(col)    FROM t;  -- ignores NULLs in both SUM and COUNT

-- NULL in UNIQUE constraints
CREATE TABLE t (email VARCHAR(254) UNIQUE);
INSERT INTO t VALUES (NULL), (NULL);  -- Both succeed! NULL != NULL

9.3 NULL 的正确与错误使用

合理使用 NULL

• 可选字段：用户的中间名、第二电话号码
• 尚未发生的事件时间：deleted_at、completed_at
• 外键关系可选时：manager_id（CEO 没有 manager）

不应使用 NULL

• 必填字段用 NULL 表示「未填」——应该在应用层校验
• 用 NULL 表示 0 或空字符串——语义不同
• 布尔列——用 TINYINT NOT NULL DEFAULT 0
• 需要频繁排序/分组/聚合的列

10. 字符集与排序规则：utf8mb4 全解

10.1 为什么必须用 utf8mb4，而不是 utf8？

MySQL 的 utf8（又叫 utf8mb3）是一个残缺的实现，只支持最多 3 字节的 UTF-8 字符（BMP 平面），无法存储 4 字节字符如 emoji、部分 CJK 扩展区汉字、数学符号等。utf8mb4 才是完整的 UTF-8 实现。MySQL 8.0 已经将默认字符集改为 utf8mb4，默认排序规则改为 utf8mb4_0900_ai_ci。

迁移注意：从 utf8 迁移到 utf8mb4 后，VARCHAR(255) 的索引键长度从 765 字节增长到 1020 字节。InnoDB 的索引键最大长度为 3072 字节（innodb_large_prefix=ON），但组合索引的总长度仍然受限。迁移前需要检查所有索引的长度是否超限。

10.2 排序规则 (Collation) 选择

-- Recommended server-level configuration (my.cnf)
[mysqld]
character-set-server = utf8mb4
collation-server     = utf8mb4_0900_ai_ci

[client]
default-character-set = utf8mb4

-- Per-table override for case-sensitive column
CREATE TABLE users (
  id       INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs NOT NULL,
  email    VARCHAR(254) NOT NULL,
  UNIQUE KEY uk_username (username)
);

10.3 字符集对存储空间的影响

utf8mb4 的每个字符最多占 4 字节，但实际存储取决于字符本身：ASCII 字符 1 字节，大部分中文 3 字节，emoji 4 字节。VARCHAR(N) 中的 N 指的是字符数而非字节数。因此 VARCHAR(100) 在 utf8mb4 下最多需要 400 字节的行内空间。

10.4 连接级字符集设置

即使服务器和表都设为 utf8mb4，如果客户端连接使用了错误的字符集，仍会出现乱码或数据截断。确保连接的每个环节都使用 utf8mb4：

-- Check current connection charset settings
SHOW VARIABLES LIKE 'character_set%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| character_set_client             | utf8mb4|  ← client sends in this charset
| character_set_connection         | utf8mb4|  ← server converts to this for processing
| character_set_results            | utf8mb4|  ← server converts results to this
| character_set_database           | utf8mb4|  ← default for new tables in current DB
| character_set_server             | utf8mb4|  ← default for new databases
+----------------------------------+--------+

-- JDBC connection string (Java)
-- jdbc:mysql://host:3306/db?useUnicode=true&characterEncoding=utf8mb4

-- Go (go-sql-driver/mysql)
-- user:pass@tcp(host:3306)/db?charset=utf8mb4&collation=utf8mb4_0900_ai_ci

-- PHP (PDO)
-- new PDO('mysql:host=host;dbname=db;charset=utf8mb4', $user, $pass);

10.5 utf8 到 utf8mb4 迁移步骤

1. 检查所有索引长度：SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH FROM information_schema.COLUMNS WHERE CHARACTER_SET_NAME = 'utf8' AND TABLE_SCHEMA = 'mydb'
2. 缩短超长索引列的 VARCHAR 长度（如 VARCHAR(255) → VARCHAR(191) 以保持在 767 字节限制内）或启用 innodb_large_prefix
3. 修改数据库默认字符集：ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci
4. 逐表修改：ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci
5. 更新连接字符串，确保客户端使用 utf8mb4
6. 验证：插入 emoji 字符测试

11. Schema 设计模式

11.1 软删除模式 (Soft Delete)

-- Approach A: deleted_at timestamp (NULL = active, non-NULL = deleted)
CREATE TABLE posts (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title      VARCHAR(200) NOT NULL,
  content    TEXT,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_active (deleted_at, id)  -- filter active rows efficiently
);

-- Query active posts: WHERE deleted_at IS NULL
-- Query deleted posts: WHERE deleted_at IS NOT NULL
-- Undelete: UPDATE posts SET deleted_at = NULL WHERE id = ?

-- Approach B: is_deleted flag (better for partitioning)
CREATE TABLE posts (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title      VARCHAR(200) NOT NULL,
  is_deleted TINYINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY idx_active (is_deleted, id)
);

11.2 多态关联模式 (Polymorphic Association)

当多个不同类型的实体（文章、商品、视频）都可以有评论时：

-- Anti-pattern: polymorphic FK (no referential integrity)
CREATE TABLE comments_bad (
  id            BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  commentable_type VARCHAR(20) NOT NULL, -- 'article', 'product', 'video'
  commentable_id   BIGINT UNSIGNED NOT NULL,
  body             TEXT NOT NULL
  -- Cannot create a proper FOREIGN KEY here!
);

-- Better pattern: shared parent table (exclusive arc)
CREATE TABLE commentables (
  id   BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  type ENUM('article','product','video') NOT NULL
);
CREATE TABLE articles (
  commentable_id BIGINT UNSIGNED PRIMARY KEY,
  title          VARCHAR(200) NOT NULL,
  FOREIGN KEY (commentable_id) REFERENCES commentables(id)
);
CREATE TABLE products (
  commentable_id BIGINT UNSIGNED PRIMARY KEY,
  name           VARCHAR(200) NOT NULL,
  FOREIGN KEY (commentable_id) REFERENCES commentables(id)
);
CREATE TABLE comments (
  id              BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  commentable_id  BIGINT UNSIGNED NOT NULL,
  body            TEXT NOT NULL,
  FOREIGN KEY (commentable_id) REFERENCES commentables(id)
);

11.3 树形结构模式

存储层级数据（分类树、组织架构、评论嵌套）的四种方案：

-- Adjacency List + Recursive CTE (MySQL 8.0+ recommended)
CREATE TABLE categories (
  id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  parent_id INT UNSIGNED,
  name      VARCHAR(100) NOT NULL,
  sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY idx_parent (parent_id),
  FOREIGN KEY (parent_id) REFERENCES categories(id)
);

-- Query: all descendants of category 1
WITH RECURSIVE cte AS (
  SELECT id, parent_id, name, 0 AS depth
  FROM categories WHERE id = 1
  UNION ALL
  SELECT c.id, c.parent_id, c.name, cte.depth + 1
  FROM categories c JOIN cte ON c.parent_id = cte.id
)
SELECT * FROM cte ORDER BY depth, name;

-- Path Enumeration pattern
CREATE TABLE categories_path (
  id   INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  path VARCHAR(500) NOT NULL COMMENT 'e.g. /1/5/23/',
  name VARCHAR(100) NOT NULL,
  KEY idx_path (path)
);
-- All descendants of node 5:
SELECT * FROM categories_path WHERE path LIKE '/1/5/%';

11.4 审计日志模式 (Audit Trail)

记录数据变更历史，用于合规审计或数据恢复。两种常见实现：

-- Approach A: separate audit log table (generic)
CREATE TABLE audit_log (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  table_name   VARCHAR(64) NOT NULL,
  record_id    BIGINT UNSIGNED NOT NULL,
  action       ENUM('INSERT','UPDATE','DELETE') NOT NULL,
  old_values   JSON DEFAULT NULL,
  new_values   JSON DEFAULT NULL,
  changed_by   INT UNSIGNED NOT NULL COMMENT 'user id who made the change',
  changed_at   TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY idx_table_record (table_name, record_id),
  KEY idx_changed_at (changed_at),
  KEY idx_changed_by (changed_by)
) ENGINE=InnoDB;

-- Approach B: history table per entity (typed, queryable)
CREATE TABLE users_history (
  history_id  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id     INT UNSIGNED NOT NULL,
  username    VARCHAR(50) NOT NULL,
  email       VARCHAR(254) NOT NULL,
  status      TINYINT UNSIGNED NOT NULL,
  valid_from  TIMESTAMP(3) NOT NULL,
  valid_to    TIMESTAMP(3) NOT NULL DEFAULT '9999-12-31 23:59:59.999',
  changed_by  INT UNSIGNED NOT NULL,
  PRIMARY KEY (history_id),
  KEY idx_user_valid (user_id, valid_from),
  KEY idx_valid_range (valid_from, valid_to)
) ENGINE=InnoDB;

-- "What was user 42's email on 2025-06-15?"
SELECT email FROM users_history
WHERE user_id = 42
  AND valid_from   '2025-06-15';

选择建议：通用审计日志（Approach A）适合合规需求——记录谁在何时改了什么；类型化历史表（Approach B）适合需要「时间旅行查询」的场景——查询某个时间点的数据快照。两者可以并存。

11.5 EAV 模式 (Entity-Attribute-Value)

EAV 用一张通用表存储任意属性，在电商 SKU 属性、CMS 自定义字段等场景常见。但它有严重的性能和类型安全问题，通常应优先考虑 JSON 列作为替代。

-- EAV anti-pattern (still seen in legacy systems)
CREATE TABLE product_attrs (
  product_id INT UNSIGNED NOT NULL,
  attr_name  VARCHAR(50) NOT NULL,
  attr_value VARCHAR(500),
  PRIMARY KEY (product_id, attr_name)
);
-- Finding products with color='red' AND size='L' requires self-join!
-- No type safety: price '19.99' stored as string

-- Modern alternative: JSON column with generated column indexes
CREATE TABLE products (
  id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name  VARCHAR(200) NOT NULL,
  attrs JSON NOT NULL DEFAULT ('{}'),
  color VARCHAR(30) GENERATED ALWAYS AS (attrs->>'$.color') VIRTUAL,
  KEY idx_color (color)
);

12. Schema 设计反模式

12.1 万能表 (God Table)

将所有信息塞进一张「超级宽表」，导致大量列为 NULL、行过宽影响 Buffer Pool 效率、ALTER TABLE 极其耗时。

-- Anti-pattern: God Table
CREATE TABLE everything (
  id INT PRIMARY KEY,
  user_name VARCHAR(50), user_email VARCHAR(254), user_phone VARCHAR(20),
  order_id INT, order_date DATE, order_amount DECIMAL(10,2),
  product_name VARCHAR(200), product_price DECIMAL(10,2),
  shipping_address TEXT, shipping_city VARCHAR(50),
  payment_method VARCHAR(20), payment_status VARCHAR(20),
  -- ... 50+ more columns, most NULL for any given row
);
-- Problems: rows are huge, Buffer Pool holds fewer rows per page,
-- no clear entity boundaries, impossible to maintain.

12.2 多列属性 (Multi-Column Attribute)

-- Anti-pattern: phone1, phone2, phone3 columns
CREATE TABLE contacts_bad (
  id     INT PRIMARY KEY,
  name   VARCHAR(50),
  phone1 VARCHAR(20),
  phone2 VARCHAR(20),
  phone3 VARCHAR(20)  -- What if someone has 4 phones?
);
-- Fix: separate phone table (1NF)

12.3 隐式类型转换

当 JOIN 或 WHERE 条件中的列类型不匹配时，MySQL 会做隐式类型转换，导致索引失效。这是最常见的"Schema 设计引发的性能 bug"之一。

-- Table A: user_id is INT
CREATE TABLE orders (user_id INT UNSIGNED, ...);

-- Table B: user_id is VARCHAR (design mistake!)
CREATE TABLE user_logs (user_id VARCHAR(20), ...);

-- This JOIN silently converts VARCHAR to INT, index on user_logs.user_id is IGNORED:
SELECT * FROM orders o
JOIN user_logs l ON o.user_id = l.user_id;  -- FULL TABLE SCAN on user_logs!

-- Fix: ensure matching types across all related tables

12.4 过度使用外键

InnoDB 外键在大规模高并发系统中会引入额外的锁开销（每次 INSERT/UPDATE/DELETE 子表时需要检查父表的共享锁）。在微服务架构中，跨服务的数据完整性更适合在应用层保证。

外键使用建议：(1) 单体应用、数据一致性关键的场景（金融）——使用外键。(2) 高并发 OLTP、微服务架构——应用层校验 + 索引保证查询性能，不用外键。(3) 无论是否使用外键，JOIN 列的索引都必须建好。

12.5 过度索引

每个索引都需要额外的磁盘空间，并且每次写操作（INSERT/UPDATE/DELETE）都需要维护所有相关索引。索引过多会显著降低写入性能。

-- Over-indexed table (real example from production audit)
CREATE TABLE orders_overindexed (
  id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id     INT UNSIGNED NOT NULL,
  status      TINYINT UNSIGNED NOT NULL,
  amount      INT UNSIGNED NOT NULL,
  created_at  TIMESTAMP NOT NULL,
  KEY idx_user (user_id),
  KEY idx_status (status),
  KEY idx_amount (amount),
  KEY idx_created (created_at),
  KEY idx_user_status (user_id, status),        -- ← covers idx_user
  KEY idx_user_created (user_id, created_at),    -- ← redundant if idx_user_status_created exists
  KEY idx_status_created (status, created_at),
  KEY idx_user_status_created (user_id, status, created_at),  -- ← covers idx_user_status
  KEY idx_user_amount (user_id, amount)
  -- 9 secondary indexes! Each INSERT updates 9 B+Trees
);

-- After analysis: only 3 indexes needed
CREATE TABLE orders_optimized (
  id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id     INT UNSIGNED NOT NULL,
  status      TINYINT UNSIGNED NOT NULL,
  amount      INT UNSIGNED NOT NULL,
  created_at  TIMESTAMP NOT NULL,
  KEY idx_user_status_created (user_id, status, created_at),
  KEY idx_status_created (status, created_at),
  KEY idx_user_amount (user_id, amount)
  -- 3 indexes: covers all actual query patterns
);

索引审查方法：使用 sys.schema_unused_indexes 找出从未使用的索引，使用 sys.schema_redundant_indexes 找出冗余索引（一个索引是另一个的前缀）。定期审查并删除无用索引可以将写入性能提升 20-50%。

12.6 VARCHAR 存储定长数据

MD5 哈希 (32 字符)、UUID (36 字符含横线)、ISO 国家码 (2 字符) 等定长数据应使用 CHAR 或 BINARY，避免 VARCHAR 的长度前缀开销。UUID 存储更应使用 BINARY(16) 配合 UUID_TO_BIN() / BIN_TO_UUID()。

-- UUID storage: BINARY(16) vs CHAR(36)
CREATE TABLE sessions (
  id BINARY(16) PRIMARY KEY,  -- 16 bytes
  user_id INT UNSIGNED NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Insert with UUID_TO_BIN (MySQL 8.0+, swap flag for time-ordered UUID)
INSERT INTO sessions (id, user_id) VALUES
  (UUID_TO_BIN(UUID(), 1), 42);

-- Query: convert back for display
SELECT BIN_TO_UUID(id, 1) AS uuid, user_id FROM sessions;
-- Savings: 16 bytes (BINARY) vs 36 bytes (CHAR) = 56% smaller indexes

13. 实战案例：电商系统 Schema 设计

以一个中型电商系统为例，展示从需求分析到 Schema 设计的完整过程。核心实体：用户、商品、订单、支付。

13.1 用户表设计

CREATE TABLE users (
  id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  username     VARCHAR(50) NOT NULL,
  email        VARCHAR(254) NOT NULL,
  password_hash CHAR(60) NOT NULL COMMENT 'bcrypt hash, always 60 chars',
  phone        VARCHAR(20) DEFAULT NULL,
  avatar_url   VARCHAR(500) DEFAULT NULL,
  status       TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '0=disabled,1=active,2=suspended',
  created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_username (username),
  UNIQUE KEY uk_email (email),
  KEY idx_phone (phone),
  KEY idx_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Design decisions:
-- 1. INT (not BIGINT): a single DB unlikely to have >4B users
-- 2. password_hash CHAR(60): bcrypt is always exactly 60 chars
-- 3. email VARCHAR(254): RFC 5321 max local+domain length
-- 4. phone nullable: some users register via email only
-- 5. status TINYINT, not ENUM: easier to add new states

13.2 商品与 SKU 表设计

CREATE TABLE products (
  id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name         VARCHAR(200) NOT NULL,
  slug         VARCHAR(200) NOT NULL COMMENT 'URL-friendly identifier',
  brand_id     INT UNSIGNED NOT NULL,
  category_id  INT UNSIGNED NOT NULL,
  description  TEXT,
  attrs        JSON NOT NULL DEFAULT ('{}') COMMENT 'flexible product attributes',
  status       TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0=draft,1=active,2=archived',
  created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_slug (slug),
  KEY idx_brand (brand_id),
  KEY idx_category_status (category_id, status),
  KEY idx_created (created_at)
) ENGINE=InnoDB;

CREATE TABLE product_skus (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  product_id   INT UNSIGNED NOT NULL,
  sku_code     VARCHAR(50) NOT NULL,
  price        INT UNSIGNED NOT NULL COMMENT 'price in cents',
  cost         INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'cost in cents',
  stock        INT UNSIGNED NOT NULL DEFAULT 0,
  attrs        JSON NOT NULL DEFAULT ('{}') COMMENT '{"color":"red","size":"L"}',
  weight_g     MEDIUMINT UNSIGNED COMMENT 'weight in grams',
  is_active    TINYINT UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  UNIQUE KEY uk_sku (sku_code),
  KEY idx_product_active (product_id, is_active),
  KEY idx_price (price)
) ENGINE=InnoDB;

-- Why BIGINT for SKU id: a product may have hundreds of SKU variants,
-- and with millions of products, INT could be reached.
-- Why INT for price (cents): avoids DECIMAL overhead, max $42.9M per item

13.3 订单表设计

CREATE TABLE orders (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_no      VARCHAR(32) NOT NULL COMMENT 'business order number',
  user_id       INT UNSIGNED NOT NULL,
  status        TINYINT UNSIGNED NOT NULL DEFAULT 0
                COMMENT '0=created,1=paid,2=shipped,3=delivered,4=cancelled,5=refunded',
  total_cents   INT UNSIGNED NOT NULL,
  discount_cents INT UNSIGNED NOT NULL DEFAULT 0,
  shipping_cents INT UNSIGNED NOT NULL DEFAULT 0,
  pay_amount    INT UNSIGNED NOT NULL COMMENT 'total - discount + shipping',
  currency      CHAR(3) NOT NULL DEFAULT 'USD',
  -- Denormalized: avoids JOIN to get address on every order page
  ship_name     VARCHAR(100) NOT NULL,
  ship_address  VARCHAR(500) NOT NULL,
  ship_city     VARCHAR(100) NOT NULL,
  ship_country  CHAR(2) NOT NULL,
  ship_zip      VARCHAR(20) NOT NULL,
  paid_at       TIMESTAMP NULL DEFAULT NULL,
  shipped_at    TIMESTAMP NULL DEFAULT NULL,
  delivered_at  TIMESTAMP NULL DEFAULT NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_no (order_no),
  KEY idx_user_status (user_id, status),
  KEY idx_created (created_at),
  KEY idx_status_created (status, created_at)
) ENGINE=InnoDB;

CREATE TABLE order_items (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id    BIGINT UNSIGNED NOT NULL,
  sku_id      BIGINT UNSIGNED NOT NULL,
  -- Snapshot: price at time of order (not current price)
  sku_name    VARCHAR(200) NOT NULL,
  unit_price  INT UNSIGNED NOT NULL COMMENT 'cents, snapshot at order time',
  quantity    SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  KEY idx_order (order_id),
  KEY idx_sku (sku_id)
) ENGINE=InnoDB;

设计要点 - shipping address 反范式存入 orders 表：订单创建后地址不应跟随用户地址更新而变化，这里的冗余既是性能优化也是业务正确性要求 - order_items.unit_price 是下单时的快照，不是商品当前价格 - order_no 与 auto_increment id 分离：对外暴露 order_no，对内使用整数 id 做 JOIN

14. 实战案例：社交网络 Schema 设计

14.1 关注/粉丝关系表

CREATE TABLE user_follows (
  follower_id  INT UNSIGNED NOT NULL,
  followee_id  INT UNSIGNED NOT NULL,
  created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (follower_id, followee_id),
  KEY idx_followee (followee_id, follower_id)
) ENGINE=InnoDB;

-- "Who does user 42 follow?"  → PRIMARY KEY scan on follower_id=42
-- "Who follows user 42?"      → idx_followee scan on followee_id=42
-- "Does user 42 follow user 99?" → PRIMARY KEY point lookup

-- Denormalized counters (avoid COUNT(*) on millions of rows)
ALTER TABLE users
  ADD COLUMN follower_count  INT UNSIGNED NOT NULL DEFAULT 0,
  ADD COLUMN following_count INT UNSIGNED NOT NULL DEFAULT 0;

-- Maintain via application layer:
-- On follow:  UPDATE users SET following_count = following_count + 1 WHERE id = ?
--             UPDATE users SET follower_count  = follower_count  + 1 WHERE id = ?
-- On unfollow: decrement both

14.2 Feed / 时间线表

社交网络的 Feed 有两种经典架构模式：推模式 (fanout on write) 和拉模式 (fanout on read)。Schema 设计直接决定了使用哪种模式。

-- Pull model: query followers' posts at read time
CREATE TABLE posts (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id    INT UNSIGNED NOT NULL,
  content    TEXT NOT NULL,
  media_urls JSON DEFAULT NULL,
  like_count INT UNSIGNED NOT NULL DEFAULT 0,
  reply_count INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY idx_user_created (user_id, created_at DESC)
) ENGINE=InnoDB;

-- Pull: get feed for user 42 (query all followees' posts)
SELECT p.* FROM posts p
JOIN user_follows f ON p.user_id = f.followee_id
WHERE f.follower_id = 42
ORDER BY p.created_at DESC
LIMIT 20;
-- Problem: if user 42 follows 1000 people, this becomes expensive

-- Push model: pre-materialize feed for each user
CREATE TABLE user_feed (
  user_id    INT UNSIGNED NOT NULL,
  post_id    BIGINT UNSIGNED NOT NULL,
  author_id  INT UNSIGNED NOT NULL,
  created_at TIMESTAMP(3) NOT NULL,
  PRIMARY KEY (user_id, post_id),
  KEY idx_user_time (user_id, created_at DESC)
) ENGINE=InnoDB;

-- On new post: fan out to all followers' feeds
-- Fast read: SELECT * FROM user_feed WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20
-- Problem: celebrities with millions of followers → huge write amplification

-- Hybrid: push for normal users, pull for celebrities (>100K followers)

14.3 点赞表设计

CREATE TABLE post_likes (
  user_id    INT UNSIGNED NOT NULL,
  post_id    BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, post_id),
  KEY idx_post (post_id, created_at DESC)
) ENGINE=InnoDB;

-- "Has user 42 liked post 999?"  → PRIMARY KEY point lookup (very fast)
-- "Who liked post 999?"          → idx_post scan
-- Like count: maintained in posts.like_count (denormalized)

-- Like action (idempotent)
INSERT IGNORE INTO post_likes (user_id, post_id) VALUES (42, 999);
UPDATE posts SET like_count = like_count + ROW_COUNT() WHERE id = 999;
-- ROW_COUNT() = 1 if inserted, 0 if duplicate (already liked)

14.4 消息表设计

CREATE TABLE conversations (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  type         TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0=direct,1=group',
  last_msg_at  TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY idx_last_msg (last_msg_at DESC)
) ENGINE=InnoDB;

CREATE TABLE conversation_members (
  conversation_id BIGINT UNSIGNED NOT NULL,
  user_id         INT UNSIGNED NOT NULL,
  last_read_msg   BIGINT UNSIGNED DEFAULT NULL COMMENT 'last read message id',
  joined_at       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (conversation_id, user_id),
  KEY idx_user (user_id, conversation_id)
) ENGINE=InnoDB;

CREATE TABLE messages (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  conversation_id BIGINT UNSIGNED NOT NULL,
  sender_id       INT UNSIGNED NOT NULL,
  content         TEXT NOT NULL,
  msg_type        TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0=text,1=image,2=file',
  created_at      TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY idx_conv_created (conversation_id, created_at DESC)
) ENGINE=InnoDB;

-- Unread count: compare last_read_msg with max message id per conversation
SELECT m.conversation_id,
       COUNT(*) AS unread
FROM messages m
JOIN conversation_members cm
  ON m.conversation_id = cm.conversation_id
WHERE cm.user_id = 42
  AND m.id > COALESCE(cm.last_read_msg, 0)
GROUP BY m.conversation_id;

15. Schema 迁移策略

15.1 在线 DDL (Online DDL)

MySQL 8.0 大幅改进了在线 DDL 能力。理解三种 DDL 算法对于选择正确的迁移方式至关重要：

-- INSTANT: add column at the end (MySQL 8.0.12+)
ALTER TABLE users ADD COLUMN bio TEXT DEFAULT NULL, ALGORITHM=INSTANT;
-- Completes in milliseconds regardless of table size!

-- INPLACE: add index without blocking writes
ALTER TABLE orders ADD INDEX idx_amount (pay_amount), ALGORITHM=INPLACE, LOCK=NONE;

-- Verify which algorithm MySQL will use:
ALTER TABLE orders ADD COLUMN note TEXT, ALGORITHM=INSTANT;
-- If INSTANT is not possible, MySQL returns an error instead of falling back

15.2 pt-online-schema-change / gh-ost

对于原生 Online DDL 无法处理的大表变更（如修改列类型、修改主键），使用第三方工具：

-- gh-ost example: change column type on a 500M row table
gh-ost \
  --host=db-primary.example.com \
  --database=mydb \
  --table=orders \
  --alter="MODIFY COLUMN order_no VARCHAR(64) NOT NULL" \
  --chunk-size=1000 \
  --max-load="Threads_running=25" \
  --critical-load="Threads_running=100" \
  --throttle-control-replicas=db-replica.example.com \
  --postpone-cut-over-flag-file=/tmp/gh-ost-cutover.flag \
  --execute

15.3 大表迁移清单

1. 评估影响：表大小、写入 QPS、从库延迟容忍度
2. 选择方案：INSTANT > INPLACE > gh-ost/pt-osc > COPY
3. 测试环境验证：在与生产等规模的测试环境上跑一遍，记录耗时
4. 备份：迁移前做一次完整备份
5. 低峰期执行：选择业务低峰时段
6. 监控：关注复制延迟、Threads_running、I/O 利用率
7. 回滚计划：明确失败时的回滚步骤

15.4 数据类型变更的安全路径

并非所有数据类型变更都需要重建表。以下变更可以通过 INPLACE 完成：

• 扩展 VARCHAR：从 VARCHAR(50) 到 VARCHAR(200)（只要原始和目标长度都在 255 以内，或都超过 255）
• 扩展 ENUM：在末尾添加新成员
• 修改默认值：完全是元数据操作

以下变更需要表重建（COPY 或 gh-ost）：

• INT → BIGINT
• VARCHAR → TEXT（或反向）
• 修改字符集（如 utf8 → utf8mb4）
• 跨越 VARCHAR 255 边界（如 VARCHAR(200) → VARCHAR(500)）

16. 常见问题 (FAQ)

Q1: 主键应该用自增 INT 还是 UUID？

性能角度：自增 INT/BIGINT 是最优选择。InnoDB 使用聚簇索引（数据按主键排序），自增主键保证了顺序插入，新行总是追加在 B+Tree 的末尾，避免了页分裂。UUID v4 是随机的，导致大量随机插入和页分裂，在大表上写入性能可能下降 2-5 倍。 分布式场景：如果需要多节点独立生成不冲突的 ID，自增 INT 无法满足。此时有三种方案： - UUID v7 / ULID：时间戳前缀保证了大致有序，用 BINARY(16) 存储，性能接近自增 - 雪花算法 (Snowflake)：64 位时间有序 ID，适合 BIGINT 列 - MySQL UUID_TO_BIN(uuid, 1)：交换时间戳的高位和低位，改善有序性 推荐：单体应用用自增 INT/BIGINT；分布式系统用雪花算法（BIGINT）或 UUID v7（BINARY(16)）。

Q2: VARCHAR(255) 为什么这么流行？

因为 VARCHAR 的长度前缀在 N ≤ 255 时只需 1 字节，N > 255 时需要 2 字节。255 是「用最少存储开销获得最大灵活性」的分界点。 但这不意味着应该默认使用 VARCHAR(255)。正确做法是根据业务最大长度来设置：用户名 VARCHAR(50)、邮箱 VARCHAR(254)、商品名 VARCHAR(200)。盲目使用 255 会导致：(1) 内存临时表浪费空间；(2) 代码审查时无法判断字段的实际含义和限制。

Q3: 什么时候应该拆表（垂直分表）？

当一张表的列可以明确分为「热数据」和「冷数据」时，应该考虑垂直拆分： - 用户表拆分：users (id, name, email, status) + user_profiles (user_id, bio, avatar_url, preferences JSON) — 列表页只查 users，详情页才 JOIN user_profiles - 商品表拆分：products (id, name, price, status) + product_details (product_id, description TEXT, spec JSON) — 列表页不需要 description 和 spec 拆分标准：(1) 核心查询（列表、搜索）不需要的大列（TEXT, JSON, BLOB）应该拆出去；(2) 更新频率不同的列组可以分离（频繁更新的统计字段 vs 很少变化的基础信息）。

Q4: ENUM 能在线添加新成员吗？

在 MySQL 8.0 中，在 ENUM 末尾添加新成员可以通过 INPLACE 算法完成，不需要重建表： ALTER TABLE orders MODIFY status ENUM('pending','paid','shipped','delivered','cancelled','returned'), ALGORITHM=INPLACE; 但是：(1) 在中间插入成员会改变内部数值映射，需要 COPY 算法；(2) 删除成员不被支持（只能添加）；(3) 重排序同样需要 COPY。这就是为什么对于经常变化的枚举值，TINYINT + 应用层映射更灵活。

Q5: JSON 列和 TEXT 存 JSON 字符串有什么区别？

主要区别有四点： 1. 格式验证：JSON 类型在写入时自动验证格式，TEXT 不会——你可以往 TEXT 里存任意字符串 2. 存储格式：JSON 类型以优化的二进制格式存储，支持直接访问嵌套元素而无需解析整个文档；TEXT 存储原始文本，每次查询都要完整解析 3. 索引支持：JSON 类型可以通过虚拟生成列和多值索引建立索引；TEXT 只能用前缀索引（且无法索引内部结构） 4. 部分更新：MySQL 8.0 的 JSON 类型支持 JSON_SET() 原地部分更新（在特定条件下不需要重写整个文档）；TEXT 必须重写整个值 建议：如果存储的内容是 JSON 格式，永远用 JSON 类型而非 TEXT。

Q6: 如何选择合适的主键策略？

主键选择的核心考量： | 场景 | 推荐主键 | 原因 | | --- | --- | --- | | 场景 | 推荐主键 | 原因 | | 单库单表 | INT UNSIGNED AUTO_INCREMENT | 最紧凑、最快、顺序插入 | | 可能超 42 亿行 | BIGINT UNSIGNED AUTO_INCREMENT | 空间换容量 | | 分布式 / 多写入点 | BIGINT (雪花算法) | 时间有序 + 全局唯一 | | 需要客户端生成 ID | BINARY(16) (UUID v7) | 时间有序 + 不依赖中心服务 | | 有天然唯一业务键 | 业务键 + 代理键 | 代理键做 PK，业务键做 UNIQUE |

Q7: 数据库范式化到什么程度最合适？

实践中的经验法则： - OLTP（在线事务处理）：设计到 3NF，然后根据性能测试结果有选择地反范式。大多数生产系统最终是「3NF + 有限的战略性反范式」 - OLAP（在线分析处理）：通常使用星型/雪花型 Schema，有意大量反范式以减少 JOIN - 不要一开始就过度范式化：如果拆分后 90% 的查询都需要 JOIN 回来，说明拆得太碎了 - 也不要一开始就反范式：先建立正确的范式化结构，在性能测试中发现瓶颈后再有针对性地反范式

Q8: 如何处理多币种金额存储？

多币种金额存储需要注意以下几点： - 始终存储币种代码：使用 CHAR(3) 存储 ISO 4217 货币代码（USD, CNY, EUR） - 按最小单位存储：美元用分（cents），日元没有小数，部分中东货币有 3 位小数（dinar = 1000 fils）。使用 BIGINT 存储最小单位的整数值 - 汇率表：独立的汇率表，记录时间点汇率，用于历史追溯 - 不要在数据库中做汇率换算：在应用层完成，数据库只存储原始金额和币种 CREATE TABLE payments ( id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, amount_minor BIGINT NOT NULL COMMENT 'amount in smallest currency unit', currency CHAR(3) NOT NULL, exponent TINYINT UNSIGNED NOT NULL DEFAULT 2 COMMENT 'decimal places: USD=2, JPY=0, BHD=3', exchange_rate DECIMAL(16,8) DEFAULT NULL COMMENT 'rate to base currency at payment time' );

Q9: 软删除和硬删除哪个更好？

两者适用于不同场景： 软删除 (deleted_at / is_deleted) 适合：需要数据恢复（误删找回）、审计要求（金融法规要求保留历史记录）、关联数据较多（删除一个用户可能影响订单、评论等数十张表）。缺点：所有查询都需要加 WHERE deleted_at IS NULL，久而久之表会变大。 硬删除适合：日志/临时数据、GDPR 等法规要求彻底删除用户数据、表增长极快需要控制大小。 混合方案：软删除 + 归档。数据软删除后，定期将已删除数据迁移到归档表（或归档库），保持主表精简。

Q10: 为什么不推荐在 MySQL 中使用外键约束？

严格来说，「不推荐」是针对大规模高并发系统的建议。外键在 InnoDB 中的代价包括： - 额外的锁：子表 INSERT 时需要对父表的对应行加共享锁（S-lock），高并发下增加锁等待 - 级联操作风险：ON DELETE CASCADE 可能在删除一行父记录时导致数千行子记录被删除，造成长事务和性能抖动 - 跨库/分表困难：外键不支持跨数据库、跨分片，当系统需要水平拆分时外键成为障碍 - DDL 复杂度：有外键的表在执行 DDL 时有额外的依赖关系需要处理 但在小型系统、开发环境、或数据一致性极为关键的场景中，外键仍然是有价值的工具。关键是理解代价后做出选择，而不是教条式地「永远不用」或「永远必须用」。

[← 上一章：InnoDB 内核](/books/high-performance-mysql/innodb-internals)
[返回目录](/books/high-performance-mysql)
[下一章：索引优化 →](/books/high-performance-mysql/index-guide)