架构与系统设计 / 系统课时
索引与数据约束
索引与数据约束
支付订单表既要承受高频写入,又要支持商户查单、回调幂等和运营筛选。本课从真实访问路径出发,为订单主表设计主键、唯一约束和联合索引,并用 EXPLAIN 验证查询是否命中预期索引。
学习目标
- 根据查询条件和排序方式确定联合索引顺序
- 使用唯一约束阻止重复订单与重复请求
- 识别冗余索引、低选择性索引和范围查询边界
先列出关键访问路径
索引设计应从“系统如何查数据”开始,而不是为每个字段单独建索引。支付订单通常有三类高频路径:商户按商户订单号查单、接口按请求号做幂等校验、运营按状态和时间倒序分页。
商户订单号只在某个商户范围内唯一,因此约束应是 (merchant_no, merchant_order_no)。平台请求号若由接入层保证全局唯一,可以单独建立唯一索引;若只在商户内唯一,也应把商户号放在前面。
运营检索经常带状态和创建时间范围,并按创建时间倒序。联合索引应优先放等值条件,再放范围和排序字段,典型顺序是 (status, created_at, id)。末尾加入 id 可以稳定同一时间点的分页顺序。
创建约束与联合索引
下面给出一张最小订单表。金额使用最小货币单位;id 作为聚簇主键;两个唯一索引分别保护商户订单和接口请求幂等;运营索引用于状态加时间范围查询。
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。
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 的列顺序一致。
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,扫描行数会随数据量和统计信息变化;不应出现对整张表的全量扫描。
type key rows Extra
range idx_status_created_id <N> Using index conditionEXPLAIN 只是计划估算。上线前还应使用接近生产分布的数据执行 EXPLAIN ANALYZE,观察实际扫描行数、返回行数和耗时,避免因状态分布极不均衡而误判索引收益。
联合索引的边界
联合索引遵循最左前缀原则。idx_status_created_id 可以支持只按 status 查询,也可以支持 status 加 created_at;但仅按 created_at 查询时,通常不能有效利用该索引的首列定位。
范围条件之后的列通常不能继续用于缩小索引扫描范围,但仍可能用于排序或覆盖。若查询先对 created_at 做范围过滤,再按 id 翻页,应使用与排序一致的游标条件,而不是不断增大的 OFFSET。
不要同时保留 (status)、(status, created_at) 和 (status, created_at, id) 三个高度重叠的索引。较长联合索引已能覆盖左侧前缀,重复索引只会增加写放大、存储占用和维护成本。
约束不能替代业务校验
唯一约束是并发下的最后防线,但业务层仍应先查询或捕获重复键异常,并把它转换为稳定的幂等响应。不能先查后插却不处理异常,因为两个并发请求可能同时通过查询。
订单状态迁移也不能只依赖字段类型。数据库可以限制金额大于零、币种长度和唯一性,但“只有待支付订单才能变为成功”属于跨状态业务规则,应通过带旧状态条件的更新语句或事务控制。
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>。
Query OK, 0 rows affected
Rows matched: 0 Changed: 0 Warnings: 0