DDL、约束与索引设计

第 5 章 · 建表、约束与索引设计

本章目标：掌握 CREATE TABLE 完整语法；PRIMARY KEY / FOREIGN KEY / UNIQUE / CHECK / NOT NULL / DEFAULT；理解 InnoDB B+Tree 索引；单列与复合索引；最左前缀原则；用 EXPLAIN 初判执行计划；为 shop_db 编写生产级 DDL（schema/shop_db.sql）；对照 spring-boot-web JPA @Entity 与 python-database ch05 平行结构。

学时建议：5～6 小时（含 DDL 重写与 EXPLAIN 实验）

前置：java-database ch01～ch04；理解 JOIN 与事务。

5.1 场景说明：从练习表到规范 shop-db Schema

ch02 的样本表无外键，便于入门。本章在 ~/learn-java/shop-db/schema/shop_db.sql 定义可对接 spring-boot-web 的正式结构，字段对齐全站约定：

虚构 API GET /api/v1/products/:slug 依赖 products(slug) UNIQUE 索引 实现 O(log n) 查找。

5.2 CREATE TABLE 基础语法

CREATE TABLE table_name (
  col_name data_type [NOT NULL | NULL] [DEFAULT expr]
    [COMMENT '注释'],
  ...
  [表级约束],
  [表选项]
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

常用数据类型（shop_db）：

5.3 shop_db 完整 DDL

schema/shop_db.sql（执行前会 DROP，仅 dev）：

USE shop_db;

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS order_lines;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS users;
SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
    ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uk_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='商城用户';

CREATE TABLE products (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  slug VARCHAR(64) NOT NULL,
  title VARCHAR(200) NOT NULL,
  category VARCHAR(50) NOT NULL,
  price INT UNSIGNED NOT NULL COMMENT '分',
  is_published TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
    ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uk_products_slug (slug),
  KEY idx_products_published_category (is_published, category),
  CONSTRAINT chk_products_price CHECK (price > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='商品';

CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  total_amount INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '分',
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
    ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  KEY idx_orders_user_created (user_id, created_at),
  KEY idx_orders_status (status),
  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT chk_orders_status CHECK (status IN ('pending','paid','cancelled','refunded'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='订单';

CREATE TABLE order_lines (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  quantity INT UNSIGNED NOT NULL DEFAULT 1,
  unit_price INT UNSIGNED NOT NULL COMMENT '下单快照，分',
  PRIMARY KEY (id),
  KEY idx_order_lines_order (order_id),
  KEY idx_order_lines_product (product_id),
  CONSTRAINT fk_ol_order
    FOREIGN KEY (order_id) REFERENCES orders(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_ol_product
    FOREIGN KEY (product_id) REFERENCES products(id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT chk_ol_qty CHECK (quantity > 0),
  CONSTRAINT chk_ol_price CHECK (unit_price > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='订单明细';

执行：

# macOS / Linux
docker exec -i shop-db-mysql mysql -ushop_user -pdev_shop_pass_change_me shop_db \
  < ~/learn-java/shop-db/schema/shop_db.sql

# Windows PowerShell
Get-Content "$env:USERPROFILE\learn-java\shop-db\schema\shop_db.sql" |
  docker exec -i shop-db-mysql mysql -ushop_user -pdev_shop_pass_change_me shop_db

5.4 PRIMARY KEY 与 AUTO_INCREMENT

为何不用 slug 作主键？ slug 可变、较长；代理键 id + UNIQUE(slug) 是电商惯例。

spring-boot-web：@Id @GeneratedValue(strategy = GenerationType.IDENTITY)；django-web：BigAutoField。

5.5 FOREIGN KEY 与引用动作

FOREIGN KEY (user_id) REFERENCES users(id)
  ON DELETE RESTRICT   -- 有订单的用户不可删
  ON UPDATE CASCADE;  -- users.id 变更时同步（极少发生）

FOREIGN KEY (order_id) REFERENCES orders(id)
  ON DELETE CASCADE;   -- 删订单级联删明细

应用层 vs 数据库层：JPA 可省略 FK，但 shop-db 教学要求 DB 层 enforce，防止脏数据。

5.6 UNIQUE、CHECK、NOT NULL

-- UNIQUE：slug、email
UNIQUE KEY uk_products_slug (slug)

-- CHECK（MySQL 8.0.16+ 强制）
CONSTRAINT chk_products_price CHECK (price > 0)

-- 插入违反 CHECK
INSERT INTO products (slug, title, category, price, is_published)
VALUES ('bad-price', 'x', 'books', 0, 1);  -- ERROR 3819

5.7 索引原理：B+Tree 简述

InnoDB 聚簇索引 = 主键 B+Tree，叶子存整行；二级索引叶子存 主键值，回表查聚簇索引。

                    [10 | 20 | 30]          ← 非叶子：键值 + 指针
                   /    |     \
          [1..9]      [10..19]    [20..29]  ← 叶子：有序，链表相连
          行数据       行数据        行数据

api.example.com 商品详情：WHERE slug = ? AND is_published = 1

• uk_products_slug (slug)：唯一索引定位一行；
• 若常带 is_published，可建 (slug, is_published) 或 (is_published, category) 列表索引。