ShowDocs
搜索文章Ctrl K

外观

← 返回MySQL列表
知识专题

MySQL 建模与查询：约束、关联、分页和复合索引 ​

本轮仅全文静态审计，未安装依赖、启动服务、连接数据库或执行任何示例。下文实测、RED/GREEN及平台结果均为此前历史证据；本轮修改后的脚本未运行，命令与输出作为学习步骤和预期保留。

关系模型与数据约束 ​

前置：SQL首次连接。一行代表一个实体，列描述属性；主键唯一标识一行。库存产品与预留是一对多关系：一个sku可以被许多request_id预留。不要把多个请求ID拼成一个逗号字符串放进产品行，否则关联、约束和逐项查询都会困难。

项目完整DDL与应用共用以下文件。数量用INT而非浮点数，金额若扩展则选择DECIMAL并明确币种/舍入；NOT NULL声明必填，CHECK防止负库存及非正预留，外键保证预留指向存在的产品。

sql
CREATE TABLE IF NOT EXISTS products (
  sku VARCHAR(64) COLLATE utf8mb4_bin PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  stock INT NOT NULL CHECK (stock >= 0),
  note VARCHAR(120) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS reservations (
  request_id VARCHAR(64) COLLATE utf8mb4_bin PRIMARY KEY,
  sku VARCHAR(64) COLLATE utf8mb4_bin NOT NULL,
  quantity INT NOT NULL CHECK (quantity > 0),
  created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  FOREIGN KEY (sku) REFERENCES products(sku),
  INDEX ix_sku_created (sku, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

sku是教学自然主键；大规模系统可采用短整型主键与唯一业务键，代价是多一次映射与业务键索引。二级索引带主键值，过宽主键会放大存储及写放大。这里不为未知规模提前引入ID生成服务。外键不会自动实现业务扣减或库存总量约束，必须由事务维护。

查询、关联与聚合 ​

在verify.py完成后，book库存为3、pen为0，预留数各1。以下SQL可直接在inventory_lab执行：

sql
SELECT p.sku,p.name,COALESCE(SUM(r.quantity),0) AS reserved
FROM products AS p
LEFT JOIN reservations AS r ON r.sku=p.sku
GROUP BY p.sku,p.name ORDER BY p.sku;

SELECT p.sku FROM products AS p
WHERE NOT EXISTS (SELECT 1 FROM reservations AS r WHERE r.sku=p.sku);

SELECT sku,SUM(quantity) AS total FROM reservations
GROUP BY sku HAVING SUM(quantity)>=2 ORDER BY sku;

第一句返回book/2、pen/1；第二句零行，因为两个商品都已有预留；第三句仅book、total=2。若先运行额外预留或练习，结果会改变，先核对数据状态。LEFT JOIN保留未有预留的产品，COUNT(*)会把补NULL的那一行算进去，若统计预留个数用COUNT(r.request_id)。把r.quantity>0放在WHERE会过滤补NULL的行，改变外连接意义；要只筛关联候选应放ON。WHERE在分组前过滤行，HAVING在分组后筛聚合结果。ONLY_FULL_GROUP_BY下不要随意SELECT未分组非聚合列。

排序与游标分页 ​

LIMIT没有ORDER BY不保证顺序。分页应包含唯一的决胜键；按创建时间排序时相同时间可能多行，加入request_id后才能稳定比较。下一页携带上一页最后一行的时间与ID：

sql
SELECT request_id,sku,quantity,created_at FROM reservations
WHERE (created_at,request_id)>('2026-01-01 00:00:00.000000','last-id')
ORDER BY created_at,request_id LIMIT 20;

这是演示合法形态；实际传上页返回的值，用驱动参数绑定。该全局分页若成为主访问路径，应评估(created_at,request_id)索引，而不是假定当前(sku,created_at)适合。OFFSET适合小结果临时查阅，大偏移仍可能扫描并丢弃许多行。并发插入/删除下分页不是跨请求快照；如果要求整个报表一致，需要另行设计快照或固定范围。

复合索引与 EXPLAIN ​

索引按访问路径选择。当前常见请求是“某sku按时间取预留”，因此建(sku,created_at)。等值约束sku后可按时间有序访问；只按created_at查全库不能假定等价使用左前缀。优化器也可能选择其他计划，是否利用索引要看真实数据、选择性、代价及版本，不能靠“有索引”判断快慢。

sql
EXPLAIN SELECT * FROM reservations WHERE sku='book' ORDER BY created_at;
EXPLAIN ANALYZE SELECT * FROM reservations WHERE sku='book' ORDER BY created_at;
SHOW INDEX FROM reservations;

实际8.4.7验证key=ix_sku_created、type=ref。possible_keys表示候选，key才是选择项；rows是估算，不是实际耗时。EXPLAIN ANALYZE会执行查询并显示实际行数/循环和时间，先确认语句及数据量；不要随意对昂贵操作运行。小表全扫可能是合理选择，不能为了截图强制索引后声称生产性能优化。

覆盖索引减少取整行，代价是更宽索引、更多写放大；SELECT *可能破坏覆盖。函数作用到索引列、隐式类型转换、前导通配LIKE都可能改变可用路径。改SQL或索引前保存慢查询、数据分布与原计划，改后比较相同负载；本实验只证明功能路径，未进行容量基准。

研究依据：MySQL复合索引、EXPLAIN输出。中文实践见华为云最佳实践，语义以相应版本官方手册及本实验为准。

自检练习：题2、3、7。提示：先画无关联商品补NULL后的行，分页条件与ORDER BY用同组键；解答另列。

继续阅读