3、数据模型设计：创建测试用表、定义主键与索引、插入初始测试数据

缓存一致性校验的核心在于模拟真实业务场景下的数据读写与状态变更。本章将构建两张基础业务表——用户表与订单表，并为其设计合理的主键、索引及初始测试数据。这两张表将作为后续集成测试中缓存与数据库比对的基础载体。

3.1 表结构设计原则

测试用表的设计需兼顾业务代表性与测试可观测性。用户表与订单表之间存在典型的一对多关系，且订单表包含时间戳、状态枚举等易变字段，非常适合触发缓存失效与一致性校验场景。

设计维度 用户表 (users) 订单表 (orders)
业务角色 主实体，缓存热点 从实体，高频写入
主键策略 自增整数 (SERIAL) UUID 字符串
索引需求 主键索引 + 邮箱唯一索引 主键索引 + 用户ID外键索引 + 状态索引
缓存一致性关注点 更新后缓存淘汰 新增/状态变更后缓存同步

3.2 创建用户表 (users)

用户表存储核心账户信息，其中 email 字段作为业务唯一标识，常用于缓存键的拼接。主键采用 SERIAL 自增类型，确保插入性能与有序性。

CREATE TABLE users (
    user_id      SERIAL PRIMARY KEY,
    username     VARCHAR(64)  NOT NULL,
    email        VARCHAR(128) NOT NULL,
    age          INT          NOT NULL DEFAULT 0,
    created_at   TIMESTAMP    NOT NULL DEFAULT NOW(),
    updated_at   TIMESTAMP    NOT NULL DEFAULT NOW()
);

-- 为 email 创建唯一索引，加速缓存键查询并保证业务唯一性
CREATE UNIQUE INDEX idx_users_email ON users (email);

-- 为 created_at 创建普通索引，便于测试中按时间范围筛选
CREATE INDEX idx_users_created_at ON users (created_at);
测试设计提示： updated_at 字段在后续触发器或应用层更新时自动刷新，是校验缓存是否随数据库更新而失效的关键字段。

3.3 创建订单表 (orders)

订单表采用 UUID 主键，避免自增主键在分布式测试环境中产生冲突。同时引入 status 枚举字段，模拟订单状态的流转（待支付、已支付、已取消），这是缓存一致性校验中最容易产生脏数据的场景。

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE orders (
    order_id     UUID        PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id      INT         NOT NULL,
    product_name VARCHAR(128) NOT NULL,
    amount       NUMERIC(10,2) NOT NULL,
    status       VARCHAR(16) NOT NULL DEFAULT 'pending'
                 CHECK (status IN ('pending', 'paid', 'cancelled')),
    created_at   TIMESTAMP   NOT NULL DEFAULT NOW(),
    updated_at   TIMESTAMP   NOT NULL DEFAULT NOW()
);

-- 外键索引：加速按用户查询订单，也是缓存按用户维度失效的依据
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- 状态索引：用于测试中按状态批量筛选，模拟缓存批量失效场景
CREATE INDEX idx_orders_status ON orders (status);

-- 组合索引：覆盖按用户+状态查询的常见测试路径
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
注意： 外键约束在测试环境中建议不启用，以避免级联操作干扰缓存一致性校验的原子性。索引仅用于加速查询，不强制引用完整性。

3.4 主键与索引策略对比

索引名称 类型 所属表 缓存一致性测试用途
users_pkey 主键索引 (B-tree) users 单行缓存键的基础
idx_users_email 唯一索引 (B-tree) users 按邮箱查询的缓存穿透防护
orders_pkey 主键索引 (B-tree) orders 单订单缓存键的基础
idx_orders_user_id 普通索引 (B-tree) orders 用户维度缓存批量失效
idx_orders_status 普通索引 (B-tree) orders 状态维度缓存扫描

3.5 插入初始测试数据

初始数据需覆盖正常状态与边界状态。用户表插入 5 条记录，订单表插入 8 条记录，包含不同用户、不同状态及不同时间分布，为后续缓存一致性校验提供多样化的数据快照。

-- 插入用户初始数据
INSERT INTO users (username, email, age) VALUES
    ('Alice',   'alice@example.com',   28),
    ('Bob',     'bob@example.com',     35),
    ('Charlie', 'charlie@example.com', 22),
    ('Diana',   'diana@example.com',   30),
    ('Eve',     'eve@example.com',     27);

-- 插入订单初始数据
INSERT INTO orders (user_id, product_name, amount, status, created_at) VALUES
    (1, 'Laptop',       1200.00, 'paid',     '2025-01-15 10:00:00'),
    (1, 'Mouse',          25.50, 'pending',  '2025-01-16 11:30:00'),
    (2, 'Keyboard',       85.00, 'paid',     '2025-01-17 09:15:00'),
    (2, 'Monitor',       350.00, 'cancelled','2025-01-18 14:20:00'),
    (3, 'Headphones',     60.00, 'pending',  '2025-01-19 16:45:00'),
    (3, 'Webcam',         45.00, 'paid',     '2025-01-20 08:00:00'),
    (4, 'Desk Lamp',      30.00, 'pending',  '2025-01-21 12:10:00'),
    (5, 'USB Hub',        22.00, 'paid',     '2025-01-22 18:30:00');
数据分布要点： 用户 Alice 有 2 笔订单（1 笔已支付、1 笔待支付），用户 Bob 有 2 笔订单（1 笔已支付、1 笔已取消），用户 Charlie 有 2 笔订单（1 笔待支付、1 笔已支付），用户 Diana 和 Eve 各有 1 笔订单。这种分布可覆盖单用户多订单、多状态混合、单订单用户等边界。

3.6 数据模型关系图

下图展示了用户表与订单表之间的实体关系、主键外键关联以及索引覆盖情况，帮助理解缓存键的粒度与失效范围。

用户表与订单表实体关系及索引覆盖 users 用户表 PK user_id (SERIAL) username VARCHAR(64) UK email VARCHAR(128) created_at TIMESTAMP updated_at TIMESTAMP orders 订单表 PK order_id (UUID) FK user_id INT product_name VARCHAR(128) amount NUMERIC(10,2) status VARCHAR(16) created_at TIMESTAMP updated_at TIMESTAMP 1 : N 索引覆盖策略 • users: PK (user_id) | UK (email) | idx (created_at) • orders: PK (order_id) | idx (user_id) | idx (status) | idx (user_id, status) • 缓存键粒度: 单行(user_id/order_id) → 用户维度(user_id) → 状态维度(status)

3.7 数据校验要点

插入完成后，建议立即执行以下校验，确保数据模型与测试基线一致：

  • 行数校验： SELECT count(*) FROM users; 应返回 5；SELECT count(*) FROM orders; 应返回 8。
  • 外键关联校验： SELECT u.username, o.order_id FROM users u JOIN orders o ON u.user_id = o.user_id; 应返回 8 条关联记录，无缺失。
  • 状态分布校验： SELECT status, count(*) FROM orders GROUP BY status; 应包含 pending(3)、paid(4)、cancelled(1)。
  • 索引生效校验： 使用 EXPLAIN ANALYZE 验证查询是否走索引扫描而非全表扫描。
← 上一章 📖 返回目录 下一章 →