架构与系统设计 / 系统课时

索引与数据约束

索引与数据约束

支付订单表既要承受高频写入,又要支持商户查单、回调幂等和运营筛选。本课从真实访问路径出发,为订单主表设计主键、唯一约束和联合索引,并用 EXPLAIN 验证查询是否命中预期索引。

学习目标

  • 根据查询条件和排序方式确定联合索引顺序
  • 使用唯一约束阻止重复订单与重复请求
  • 识别冗余索引、低选择性索引和范围查询边界

先列出关键访问路径

索引设计应从“系统如何查数据”开始,而不是为每个字段单独建索引。支付订单通常有三类高频路径:商户按商户订单号查单、接口按请求号做幂等校验、运营按状态和时间倒序分页。

商户订单号只在某个商户范围内唯一,因此约束应是 (merchant_no, merchant_order_no)。平台请求号若由接入层保证全局唯一,可以单独建立唯一索引;若只在商户内唯一,也应把商户号放在前面。

运营检索经常带状态和创建时间范围,并按创建时间倒序。联合索引应优先放等值条件,再放范围和排序字段,典型顺序是 (status, created_at, id)。末尾加入 id 可以稳定同一时间点的分页顺序。

创建约束与联合索引

下面给出一张最小订单表。金额使用最小货币单位;id 作为聚簇主键;两个唯一索引分别保护商户订单和接口请求幂等;运营索引用于状态加时间范围查询。

sql
CREATE TABLE payment_order (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    merchant_no VARCHAR(32) NOT NULL,
    merchant_order_no VARCHAR(64) NOT NULL,
    request_id VARCHAR(64) NOT NULL,
    status VARCHAR(16) NOT NULL,
    amount BIGINT UNSIGNED NOT NULL,
    currency CHAR(3) NOT NULL,
    created_at DATETIME(3) NOT NULL,
    updated_at DATETIME(3) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uk_merchant_order (
        merchant_no, merchant_order_no
    ),
    UNIQUE KEY uk_request_id (request_id),
    KEY idx_status_created_id (
        status, created_at, id
    ),
    KEY idx_merchant_created_id (
        merchant_no, created_at, id
    ),
    CONSTRAINT ck_order_amount CHECK (amount > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO payment_order (
    merchant_no, merchant_order_no, request_id,
    status, amount, currency, created_at, updated_at
) VALUES (
    'M10001', 'ORDER-20260803-001', 'REQ-000001',
    'SUCCESS', 12800, 'HKD',
    '2026-08-03 10:00:00.000',
    '2026-08-03 10:00:01.000'
);

INSERT INTO payment_order (
    merchant_no, merchant_order_no, request_id,
    status, amount, currency, created_at, updated_at
) VALUES (
    'M10001', 'ORDER-20260803-001', 'REQ-000002',
    'PENDING', 12800, 'HKD',
    '2026-08-03 10:01:00.000',
    '2026-08-03 10:01:00.000'
);

预期结果:第一条订单插入成功,第二条因商户订单组合重复而失败。错误文本会因 MySQL 版本和客户端不同而变化,但约束名应指向 uk_merchant_order。

text
Query OK, 1 row affected
ERROR 1062 (23000): Duplicate entry '<VALUE>' for key 'payment_order.uk_merchant_order'

验证查询是否命中索引

对运营查询使用半开时间区间,避免在索引列上套 DATE()、TO_DAYS() 等函数。查询包含状态等值条件、创建时间范围和稳定排序,与 idx_status_created_id 的列顺序一致。

sql
EXPLAIN
SELECT id, merchant_no, merchant_order_no, amount, currency
FROM payment_order
WHERE status = 'SUCCESS'
  AND created_at >= '2026-08-03 00:00:00.000'
  AND created_at <  '2026-08-04 00:00:00.000'
ORDER BY created_at DESC, id DESC
LIMIT 20;

预期结果:key 使用 idx_status_created_id,访问类型通常为 range,扫描行数会随数据量和统计信息变化;不应出现对整张表的全量扫描。

text
type   key                    rows   Extra
range  idx_status_created_id  <N>    Using index condition

EXPLAIN 只是计划估算。上线前还应使用接近生产分布的数据执行 EXPLAIN ANALYZE,观察实际扫描行数、返回行数和耗时,避免因状态分布极不均衡而误判索引收益。

联合索引的边界

联合索引遵循最左前缀原则。idx_status_created_id 可以支持只按 status 查询,也可以支持 status 加 created_at;但仅按 created_at 查询时,通常不能有效利用该索引的首列定位。

范围条件之后的列通常不能继续用于缩小索引扫描范围,但仍可能用于排序或覆盖。若查询先对 created_at 做范围过滤,再按 id 翻页,应使用与排序一致的游标条件,而不是不断增大的 OFFSET。

不要同时保留 (status)、(status, created_at) 和 (status, created_at, id) 三个高度重叠的索引。较长联合索引已能覆盖左侧前缀,重复索引只会增加写放大、存储占用和维护成本。

约束不能替代业务校验

唯一约束是并发下的最后防线,但业务层仍应先查询或捕获重复键异常,并把它转换为稳定的幂等响应。不能先查后插却不处理异常,因为两个并发请求可能同时通过查询。

订单状态迁移也不能只依赖字段类型。数据库可以限制金额大于零、币种长度和唯一性,但“只有待支付订单才能变为成功”属于跨状态业务规则,应通过带旧状态条件的更新语句或事务控制。

sql
UPDATE payment_order
SET status = 'SUCCESS',
    updated_at = '2026-08-03 10:05:00.000'
WHERE merchant_no = 'M10001'
  AND merchant_order_no = 'ORDER-20260803-001'
  AND status = 'PENDING';

预期结果:前面成功插入的订单已经是 SUCCESS,因此本次条件更新影响 0 行;若目标订单仍为 PENDING,则会影响 1 行。示例中的时间应替换为实际 <TIME>。

text
Query OK, 0 rows affected
Rows matched: 0  Changed: 0  Warnings: 0
按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

索引与数据约束

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通