索引与约束：速度和正确性如何同时守住

上一章把多条写操作放进了事务。订单、明细、付款和库存变动要么一起提交，要么一起回滚，这解决了“事情只做了一半”的问题。但事务还有两件事管不了：它不会自动让一条查询变快，也不会替你判断写入的数据合不合理。

想象订单表已经积累了几百万行。客服只想找某位客户最近三个月的订单，却等了好几秒；另一边，一个程序把不存在的商品编号写进了订单明细，还把数量写成了负数。前者需要索引缩小查找范围，后者需要约束在数据进入表时就拒绝错误。

这两类机制解决的问题不同：索引负责提供更短的访问路径，约束负责划定数据允许存在的边界。 它们经常一起出现，却不能互相替代。本章沿用课程里的电商数据，从一条客户订单查询出发，逐步看清 B+ 树、InnoDB 的聚簇索引与二级索引、复合索引、覆盖索引、执行计划，再把主键、唯一、非空、检查和外键约束接到同一套表结构上。

先分清两个目标：查得快与写得对

先看一条很普通的客服查询：

SELECT order_id, order_date, status, total_amount
FROM orders
WHERE customer_id = 1
  AND order_date >= '2025-01-01'
  AND order_date <  '2027-01-01'
ORDER BY order_date DESC;

查询结果是苏小满在这个时间范围内的三张订单：

没有合适索引时，数据库只能查看大量订单，逐行判断 customer_id 和 order_date 是否符合条件。课程数据只返回 3 行；当订单增长到几百万行，索引要做的，是让数据库从“把整张表翻一遍”变成“直接走到客户 1 对应的日期区间”。

再看一条写入：

INSERT INTO order_items
    (order_item_id, order_id, product_id, quantity, unit_price, discount_rate)
VALUES
    (90001, 101, 999999, -2, 299.00, 1.20);

这条记录同时有三个问题：商品 999999 可能不存在，数量是负数，折扣率大于 1。事务可以保证这条语句失败时不留下半条记录，却不知道哪些值符合业务规则。约束可以把规则放进数据库：product_id 必须引用存在的商品，quantity 必须大于 0，discount_rate 必须落在 0 到 1 之间。

索引和约束也会互相影响。主键与唯一约束通常需要唯一索引来高效检查重复值，外键检查也依赖相关列上的索引；反过来，普通索引即使名字里带有 status 或 email，也不会自动保证这些值有效或唯一。后面我们会专门拆开这层关系。

B+ 树的直觉：一次排除一大片

关系型数据库常用树形索引，不是因为“树”这个概念高级，而是因为磁盘和内存按页读取。一次读取一页时，如果一个节点能放下很多键和指针，数据库每走一层就能排除一大片不可能的范围。

节点里存的不是一整棵二叉树

初学者常把 B+ 树想成每个节点只有左右两个分支的二叉树。数据库里的 B+ 树通常是多路的：一个索引页能存许多排序后的键和许多子页指针。假设根节点把订单日期分成若干范围，查找 2026-05-18 时，数据库只跟随覆盖这个日期的指针，不必读取其他月份所在的分支。

用简化后的结构表示，大致是这样：

根节点
├── 2025-01-01 ～ 2025-12-31
├── 2026-01-01 ～ 2026-04-30
└── 2026-05-01 ～ 2026-08-31
                         ↓
                    分支节点
              ├── 05-01 ～ 05-10
              ├── 05-11 ～ 05-20
              └── 05-21 ～ 05-31
                         ↓
                    叶子节点
              05-17 → 05-18 → 05-19

实际索引页里还有页头、记录指针和其他管理信息。我们不需要先记住页结构，只要抓住三个事实：键按顺序组织，树会保持大体平衡，从根走到任意叶子的层数差不多。因此，数据量增长很多时，树高通常不会同样倍数增长。

为什么等值和范围查询都适合 B+ 树

等值查询先沿树向下定位目标键：

SELECT product_id, product_name, unit_price
FROM products
WHERE product_id = 1;

范围查询先找到边界，再沿相邻叶子页向后读取：

SELECT product_id, product_name, unit_price
FROM products
WHERE unit_price BETWEEN 100.00 AND 300.00
ORDER BY unit_price;

B+ 树的叶子页按键顺序连接，找到 100.00 后，可以沿叶子页读取到 300.00 为止。这也是索引能同时帮助 BETWEEN、>、>= 和匹配排序方向的 ORDER BY 的原因。

但“有 B+ 树”不等于“任何写法都能用上”。如果查询要求先对每个索引值计算函数，或从字符串中间开始匹配，数据库可能无法从一个连续边界开始走。索引能不能发挥作用，最终要看查询条件能否转换成树上的可定位范围。

MySQL 的 InnoDB 视角：聚簇索引与二级索引

本章的 SQL 以 MySQL 8.4 和 InnoDB 为主。InnoDB 中，每张表都有一个特殊的聚簇索引，叶子记录直接保存整行数据。通常，显式声明的主键就是聚簇索引的键。

通过主键找到的是整行

假设 orders 的主键是 order_id：

ALTER TABLE orders
ADD CONSTRAINT pk_orders PRIMARY KEY (order_id);

执行下面的查询时，沿主键树走到叶子页，就能读到该订单的整行：

SELECT order_id, customer_id, order_date, status, total_amount
FROM orders
WHERE order_id = 101;

这和“普通索引里存地址，地址再指向另一个无序数据文件”的直觉不完全相同。对 InnoDB 来说，聚簇索引的叶子就是行数据所在的位置。

如果表没有主键，InnoDB 会尝试选择第一个所有键列都非空的唯一索引；仍然找不到时，会生成内部行标识来组织聚簇索引。数据库能替你兜底，不代表表设计可以省略主键。显式主键能稳定标识一行，也让外键、更新和排错更清楚。

二级索引叶子保存主键值

除了聚簇索引之外的 InnoDB 索引称为二级索引。例如：

CREATE INDEX idx_orders_customer
ON orders (customer_id);

二级索引叶子至少包含二级索引键和对应行的主键值。用 customer_id = 1 查询所有列时，典型路径是：

1. 在 idx_orders_customer 中定位 customer_id = 1。
2. 从匹配的二级索引记录中取得 order_id。
3. 用 order_id 回到聚簇索引读取订单其余列。

第三步常被称为回表。如果匹配 12 条记录，回表成本通常可控；如果匹配 40 万条记录，大量分散的主键查找可能比顺序扫描更贵，优化器就可能放弃该索引。

主键为什么宜短且稳定

因为每个二级索引记录都携带主键值，主键越宽，所有二级索引越大。把长邮箱或经常变化的业务字符串设为主键，会把成本扩散到整张表的每个二级索引。课程表使用整数型 customer_id、order_id、product_id 作为主键，正好适合这种存储方式。

主键还应尽量稳定。修改聚簇键意味着行在聚簇索引中的位置需要变化，二级索引里保存的主键值也受到影响。业务上会改名、会更换的字段，更适合普通列或唯一键，不适合承担主键职责。

主键、唯一、普通与复合索引各自做什么

索引可以按“是否允许重复”和“由几列组成”来理解。下面四个名称不是完全互斥的分类：主键或唯一索引也可以由多列组成，普通索引也可以是复合索引。

主键只有一组，唯一键可以有多组

customers.customer_id 是主键，而邮箱和手机号可以各自承担业务唯一规则：

ALTER TABLE customers
    ADD CONSTRAINT pk_customers PRIMARY KEY (customer_id),
    ADD CONSTRAINT uq_customers_email UNIQUE (email);

主键不能为 NULL。MySQL 的唯一约束允许多个 NULL，因为未知值之间不按相等处理。如果业务规定每位客户必须提供邮箱，应把 email 同时设为 NOT NULL；仅有 UNIQUE 不会补上“必须填写”的规则。小满商店允许手机号为空，也没有把手机号声明成唯一，因为家庭成员共用联系电话在业务上可能合法。

普通索引只提供路径，不承诺业务规则

CREATE INDEX idx_products_category
ON products (category_id);

这个索引让数据库更快找到某分类下的商品，但允许多个商品属于同一分类。这正是普通索引应有的行为。不要因为希望“查询快”就误加 UNIQUE，否则第二个同分类商品会被当成非法数据。

创建、查看与删除

MySQL 可以用 CREATE INDEX 创建普通索引；唯一规则则可以用前面的 UNIQUE 约束清楚表达：

CREATE INDEX idx_orders_order_date
ON orders (order_date);

查看表上已有索引：

SHOW INDEX FROM orders;

结果中可以重点看 Key_name、Non_unique、Seq_in_index、Column_name 和 Cardinality。同一个复合索引会显示多行，Seq_in_index 表示列在索引中的顺序。

删除不再需要的索引：

DROP INDEX idx_orders_order_date ON orders;

主键不是用 DROP INDEX PRIMARY 删除，而是：

ALTER TABLE orders DROP PRIMARY KEY;

这类结构变更会影响并发、锁和执行计划。生产环境中应先确认依赖关系、表大小和数据库支持的在线 DDL 能力，不要把演示语句当成可以随时执行的清理脚本。

复合索引：最左前缀不是背口诀

现在回到本章开头的客户订单查询。一个更贴合查询的索引是：

CREATE INDEX idx_orders_customer_date_status
ON orders (customer_id, order_date, status);

可以把索引条目想成按下面的规则排队：先比较 customer_id；客户相同时，再比较 order_date；客户和日期都相同时，最后比较 status。

(1, 2025-06-18, '已完成')
(1, 2025-10-12, '已退款')
(1, 2026-04-01, '已完成')
(2, 2025-07-02, '已完成')
(2, 2026-02-14, '已完成')

所谓最左前缀，就是数据库能从这条排序规则的开头连续定位。索引 (customer_id, order_date, status) 自带以下可定位前缀：

• (customer_id)
• (customer_id, order_date)
• (customer_id, order_date, status)

能直接缩小范围的写法

只用第一列：

SELECT order_id, order_date, status
FROM orders
WHERE customer_id = 1;

使用前两列：

SELECT order_id, order_date, status
FROM orders
WHERE customer_id = 1
  AND order_date >= '2025-01-01';

三列都有条件：

SELECT order_id, order_date, status
FROM orders
WHERE customer_id = 1
  AND order_date = '2026-04-01 08:30:00'
  AND status = '已完成';

SQL 条件在 WHERE 中书写的先后不会改变索引列顺序。下面这条仍然能使用同一个复合索引，因为优化器会分析表达式，不是按文本从左到右执行：

SELECT order_id, order_date, status
FROM orders
WHERE status = '已完成'
  AND customer_id = 1
  AND order_date >= '2025-01-01';

跳过左列为什么难以直接定位

只按 order_date 查询时，同一天的订单分散在每个客户自己的区间中：

SELECT order_id, customer_id, order_date
FROM orders
WHERE order_date >= '2025-01-01';

索引首先按客户分组，数据库没有一个单独连续的“所有客户五月订单”区间可以直接走。因此，(customer_id, order_date, status) 通常不能像以 order_date 开头的索引那样高效完成这条查询。MySQL 可能选择全索引扫描、其他索引或全表扫描，具体要由执行计划确认。

只按第三列也有类似问题：

SELECT order_id, customer_id, status
FROM orders
WHERE status = '已完成';

不能把“列出现在索引里”理解为“就一定能快速定位”。是否从最左侧连续使用，比是否出现过更重要。

范围条件之后会发生什么

下面的条件中，customer_id 是等值，order_date 是范围，status 位于范围列之后：

SELECT order_id, order_date, status
FROM orders
WHERE customer_id = 1
  AND order_date >= '2025-01-01'
  AND status = '已完成';

数据库可以先定位客户 1，再从日期下界开始扫描。由于 order_date 已经打开了一个范围，后面的 status 通常不能继续把 B+ 树缩成一个更窄的连续区间。不过 status 仍可能在索引层被检查，减少不必要的回表。这里要区分两件事：用于确定扫描边界和在扫描过程中提前过滤并不是同一回事。

列顺序从查询模式出发

“选择性最高的列永远放最前”不是可靠规则。复合索引要同时考虑等值条件、范围条件、排序、分组和真实查询频率。客服最常见的请求是“某客户按时间看订单”，把 customer_id 放在第一列很自然；运营最常见的请求如果是“全站按日期看订单”，则可能还需要单独的 (order_date) 或以日期开头的复合索引。

也不要机械地把每个条件都塞进一个超宽索引。宽索引占空间、写入成本高，还可能和现有索引重复。先列出高频且重要的查询，再让一条复合索引尽可能服务一组相近的查询。

覆盖索引与选择性：少回表还不够

如果查询需要的筛选列和返回列都能从一个索引中取得，数据库可能只读取索引，不再访问表行。这类访问常称为覆盖索引查询。

例如我们创建：

CREATE INDEX idx_orders_customer_date_cover
ON orders (customer_id, order_date, status, total_amount);

然后查询：

SELECT order_id, order_date, status, total_amount
FROM orders
WHERE customer_id = 1
  AND order_date >= '2025-01-01';

在 InnoDB 二级索引中，主键 order_id 随二级索引记录保存；其他所需列也在索引定义里。这条查询具备由索引直接提供全部列的条件，执行计划的 Extra 可能出现 Using index。

如果改成 SELECT *：

SELECT *
FROM orders
WHERE customer_id = 1
  AND order_date >= '2025-01-01';

查询还需要索引中没有的列，覆盖条件就被破坏了。SELECT * 不只会传输更多数据，还可能让原本无需回表的查询重新回表。明确列出真正需要的字段，是可读性和性能都更稳的写法。

覆盖不是免费午餐

为了覆盖查询而把许多列追加到索引，会让索引页变大、树中每页能放的记录变少，缓存效率下降，写入时还要维护更多数据。宽字符串和频繁更新的列尤其要谨慎。覆盖索引应该服务稳定、频繁且值得优化的查询，不是把整行复制进另一个结构。

选择性决定索引能排除多少数据

可以用一个简单比例理解单列选择性：

选择性 ≈ 不同值数量 ÷ 总行数

orders.order_id 每行不同，选择性接近 1；orders.status 可能只有 pending、paid、shipped、cancelled 几种值，选择性很低。

查看近似统计：

SELECT
    COUNT(*) AS total_rows,
    COUNT(DISTINCT customer_id) AS customer_values,
    COUNT(DISTINCT status) AS status_values
FROM orders;

结果可以这样阅读：

这 13 张订单里有 8 张是“已完成”。单独按 status = '已完成' 已经会返回大半张表；数据增长后如果分布相近，二级索引即使能找到这些索引项，随后进行大量回表也未必划算。所以优化器选择全表扫描并不代表索引“坏了”。把状态放在客户和日期之后，往往更适合“某位客户某段时间内的已完成订单”。

为什么有索引，查询还是没有走

看到 possible_keys 为空或 key 为 NULL 时，先别立刻强制使用索引。通常可以从查询写法、数据类型、选择性和统计信息四个方向检查。

对索引列计算函数

假设有索引：

CREATE INDEX idx_orders_order_date
ON orders (order_date);

下面的写法直观，却要求数据库对每一行的 order_date 计算 DATE()：

SELECT order_id, customer_id, order_date
FROM orders
WHERE DATE(order_date) = '2026-04-01';

把目标日期改写成半开区间，数据库就能在原始列上定位连续范围：

SELECT order_id, customer_id, order_date
FROM orders
WHERE order_date >= '2026-04-01 00:00:00'
  AND order_date <  '2026-04-02 00:00:00';

半开区间还能避免拼接 23:59:59 时遗漏更高精度时间值。

如果业务确实长期按表达式查询，MySQL 8.4 支持函数索引键，例如：

CREATE INDEX idx_orders_order_day
ON orders ((DATE(order_date)));

函数索引也会占空间并增加写入维护，且查询表达式要与索引表达式匹配。通常先判断能否改写为原始列范围，再决定是否创建表达式索引。

隐式类型转换

customers.phone 是字符串。下面把它和数字比较：

SELECT customer_id, customer_name, phone
FROM customers
WHERE phone = 13800001001;

MySQL 会进行类型转换，结果可能反直觉，也可能让索引访问变差。与列的数据类型保持一致：

SELECT customer_id, customer_name, phone
FROM customers
WHERE phone = '13800001001';

电话号码不是拿来做算术的数值。使用字符串还能保留前导零和国家区号符号。

前导通配符

有 customer_name 索引时，固定前缀有机会形成范围：

SELECT customer_id, customer_name
FROM customers
WHERE customer_name LIKE '张%';

但下面的条件从任意位置开始匹配，B+ 树无法直接知道从哪个键开始：

SELECT customer_id, customer_name
FROM customers
WHERE customer_name LIKE '%科技%';

这类包含匹配要根据需求考虑全文索引、专门搜索服务或其他检索结构，不能靠普通 B+ 树索引硬撑。

返回行太多

SELECT order_id, customer_id, status
FROM orders
WHERE status = '已完成';

如果大多数订单都是 paid，索引并没有排除多少行。优化器可能认为顺序扫描表比“读大量二级索引项再回表”更便宜。此时不应把 FORCE INDEX 当成默认修复；应重新检查查询是否真的需要这么多行，能否加客户、日期等限制，或能否做成覆盖查询。

小表、统计过旧与数据分布偏斜

小表只有几页时，全表扫描本来就便宜。数据大量导入后，统计信息若没有及时反映新分布，估算也可能偏差。可以先检查：

SHOW INDEX FROM orders;
 
ANALYZE TABLE orders;

ANALYZE TABLE 会更新用于优化器估算的统计信息，但它不是每次慢查询都该执行的万能按钮。先用执行计划确认估算问题，再安排合适的维护时机。

用 EXPLAIN 读懂数据库准备怎么走

建索引之后不能凭感觉宣布优化完成。EXPLAIN 展示优化器选择的访问路径，EXPLAIN ANALYZE 还会执行查询并报告估算与执行过程。读计划时，我们关心的不是“有没有出现索引名”这一项，而是数据库预计读取多少数据、怎样过滤、是否回表、是否额外排序。

EXPLAIN
SELECT order_id, order_date, status, total_amount
FROM orders
WHERE customer_id = 1
  AND order_date >= '2025-01-01'
  AND order_date <  '2027-01-01'
ORDER BY order_date DESC;

传统表格格式中，先看这几列：

常见访问类型的直觉

在单表查询中，经常见到这些 type：

• ALL：扫描整张表。
• index：扫描整棵索引，不等于精准定位；它仍可能读取很多记录。
• range：读取索引的一个或多个范围。
• ref：通过非唯一索引的等值条件查找，可能返回多行。
• eq_ref：连接时通过主键或唯一非空键为前表每行匹配至多一行。
• const：通过主键或唯一键的常量条件定位至多一行，查询开始时即可当成常量处理。

这些类型不能脱离 rows、返回列和 Extra 排名。一个返回大量数据的 range 可能仍然昂贵，一个只有几十行的小表用 ALL 也可能完全合理。

先核对这条课程查询的访问路径

课程表已经有下面这条索引：

CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date);

对固定查询执行计划分析后，把不同展示格式收敛成“访问动作 + 索引 + 边界”，核心路径是：

SEARCH orders USING INDEX idx_orders_customer_date
  (customer_id=? AND order_date>? AND order_date<?)

这行信息说明数据库没有在订单表中漫无目的地逐行判断，而是进入 idx_orders_customer_date，先定位客户，再在该客户内部读取开始时间与结束时间之间的索引范围。课程数据中，这个范围返回订单 101、105 和 111。

对比数据增长前后的目标计划

订单增长到大量行后，如果没有合适索引，MySQL 传统格式的计划可能呈现为：

创建刚才的索引后，目标访问路径可以读成：

这里真正的变化是扫描范围从大量行缩到客户对应的日期区间。索引前两列与 WHERE 条件一致，顺序还能服务同一客户内部的日期排序。status 与 total_amount 不在这条索引中，所以它不是覆盖索引；如果它们没有被其他机制取得，仍要读取表行。rows 是估算值，数据分布和统计信息变化后会改变。

possible_keys 有值，key 却为空

这表示索引在语法上可用，但优化器估算后选择了别的路径。常见原因包括返回比例太高、回表太贵、小表扫描更便宜，或统计信息让成本估算偏向扫描。先看 rows 与 filtered，再比较覆盖程度和数据分布，不要只盯着索引名。

用树形计划与分析计划继续确认

EXPLAIN FORMAT=TREE
SELECT order_id, order_date, status, total_amount
FROM orders
WHERE customer_id = 1
  AND order_date >= '2025-01-01'
  AND order_date <  '2027-01-01'
ORDER BY order_date DESC;

树形格式会把扫描、过滤、连接、排序等操作按父子关系展示。需要比较估算和执行过程时，可以对安全的 SELECT 使用：

EXPLAIN ANALYZE
SELECT order_id, order_date, status, total_amount
FROM orders
WHERE customer_id = 1
  AND order_date >= '2025-01-01'
  AND order_date <  '2027-01-01'
ORDER BY order_date DESC;

EXPLAIN ANALYZE 会执行查询，不只是预估。对昂贵查询要先限制环境和影响范围；对修改语句更不能把“分析”误解成绝对不会改数据。本章练习只用它分析 SELECT。

索引会把读取成本换到写入端

每次插入订单，InnoDB 不只写聚簇索引。表上每个相关二级索引也要插入新记录；更新索引列时，要移除旧键并加入新键；删除行时，相关索引记录也需要处理。

假设 orders 同时有这些索引：

CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_order_date ON orders (order_date);
CREATE INDEX idx_orders_status ON orders (status);
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);
CREATE INDEX idx_orders_customer_date_status
ON orders (customer_id, order_date, status);

看起来每种查询都有照顾，实际上存在明显重叠。后两个复合索引都能以 customer_id 为左前缀，单列 idx_orders_customer 很可能变得冗余；idx_orders_customer_date_status 也覆盖了前两列的查找能力，idx_orders_customer_date 是否保留要比较索引宽度、查询覆盖和真实使用情况。

写入为什么会变慢

新增索引会带来这些成本：

• 每次 INSERT 都要维护更多树。
• 更新索引列会改变索引记录的位置。
• 删除也要标记或移除各索引中的记录。
• 索引占用磁盘与缓冲池，挤压其他热数据。
• 随机键插入可能造成页分裂和更多页管理工作。
• 优化器还要在更多候选访问路径之间估算成本。

索引不是越少越好。没有索引时，大量查询、外键检查和锁定范围都可能变得更重。正确做法是让每条索引有清楚的服务对象，并定期识别重复、未使用或收益很低的索引。

一份更稳的索引设计清单

约束：把数据规则放到最后一道入口

应用程序当然要校验输入，但数据库可能同时被多个服务、批处理任务和运维脚本写入。如果规则只存在于某一个页面的表单代码里，其他入口就可能绕过它。约束放在表上，所有写入路径都要遵守同一条底线。

非空约束解决“必须有值”

订单必须属于某位客户，必须有下单时间、状态和金额：

ALTER TABLE orders
    MODIFY customer_id INTEGER NOT NULL,
    MODIFY order_date DATETIME NOT NULL,
    MODIFY status VARCHAR(12) NOT NULL,
    MODIFY total_amount DECIMAL(10, 2) NOT NULL;

NOT NULL 只保证不是空值，不保证字符串不是 ''，也不保证金额大于等于 0。规则需要按含义组合。

检查约束解决“值必须满足条件”

商品价格与库存不能为负，状态只能来自允许集合：

ALTER TABLE products
    ADD CONSTRAINT ck_products_unit_price
        CHECK (unit_price > 0),
    ADD CONSTRAINT ck_products_stock
        CHECK (stock >= 0),
    ADD CONSTRAINT ck_products_status
        CHECK (status IN ('在售', '缺货', '下架'));

订单明细的数量、成交单价和折扣率也可以直接表达：

ALTER TABLE order_items
    ADD CONSTRAINT ck_order_items_quantity
        CHECK (quantity > 0),
    ADD CONSTRAINT ck_order_items_unit_price
        CHECK (unit_price > 0),
    ADD CONSTRAINT ck_order_items_discount_rate
        CHECK (discount_rate >= 0 AND discount_rate <= 1);

库存流水要特别小心：inventory_movements.quantity 用正数表示入库，用负数表示出库或盘亏，所以不能照抄库存表的“必须大于等于 0”。它真正需要阻止的是没有变化的零值：

ALTER TABLE inventory_movements
    ADD CONSTRAINT ck_inventory_movements_type
        CHECK (movement_type IN ('采购入库', '销售出库', '顾客退货', '盘点调整')),
    ADD CONSTRAINT ck_inventory_movements_quantity
        CHECK (quantity <> 0);

MySQL 8.4 会执行这些 CHECK。检查表达式为 FALSE 时拒绝写入；如果表达式因 NULL 得到未知，检查本身不会拒绝，所以必须填写的列还要配合 NOT NULL。

唯一约束解决“不能出现第二个相同值”

ALTER TABLE customers
    ADD CONSTRAINT uq_customers_email UNIQUE (email);
 
ALTER TABLE departments
    ADD CONSTRAINT uq_departments_name UNIQUE (department_name);

唯一约束适合稳定的候选键。不要随意把 customer_name 或 phone 设为唯一：同名客户是合法情况，电话也可能由家庭成员共用。department_name 是否全局唯一则取决于组织规则。如果不同公司可以各有一个“销售部”，表里还需要公司维度，唯一规则可能应是一个复合唯一约束，而不是单列唯一。

主键解决“怎样明确指向这一行”

课程中的每张核心表都有一个 ID 字段，适合声明为主键：

ALTER TABLE products
    ADD CONSTRAINT pk_products PRIMARY KEY (product_id);
 
ALTER TABLE payments
    ADD CONSTRAINT pk_payments PRIMARY KEY (payment_id);

一张表只能有一组主键，但这组主键可以由多列构成。是否使用复合主键要看行的身份定义。本课程的 order_items 已有独立的 order_item_id，因此继续用它做主键；如果没有独立 ID，才可能考虑由 order_id 和产品维度组成的复合身份，同时还要判断同一订单是否允许同一商品出现多行。

外键与级联：关系存在之后，删除意味着什么

外键约束检查子表中的引用值是否能在父表的候选键中找到。比如 orders.customer_id 指向 customers.customer_id：

ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers (customer_id)
    ON UPDATE RESTRICT
    ON DELETE RESTRICT;

这会拒绝两类操作：给订单写入不存在的客户，以及在仍有订单引用时删除客户。外键不是自动连接，也不会让查询结果自动带上客户名；查询仍然要明确写 JOIN。

课程表之间的主要关系

把本课程字段连起来，可以得到这些自然外键：

ALTER TABLE categories
    ADD CONSTRAINT fk_categories_parent
    FOREIGN KEY (parent_id)
    REFERENCES categories (category_id)
    ON UPDATE RESTRICT
    ON DELETE SET NULL;
 
ALTER TABLE products
    ADD CONSTRAINT fk_products_category
    FOREIGN KEY (category_id)
    REFERENCES categories (category_id)
    ON UPDATE RESTRICT
    ON DELETE RESTRICT;
 
ALTER TABLE order_items
    ADD CONSTRAINT fk_order_items_order

这些策略体现的是一套可能的业务选择，不是所有系统的唯一答案。order_items 被视为订单的组成部分，所以删除订单时可以一起删除明细；付款与库存流水常承担审计职责，因此不让删除订单或商品带走这些记录。分类的 parent_id、员工的 manager_id 和库存流水的 employee_id 是可选关系，相应父记录删除后可以设为 NULL，保留子记录。

RESTRICT / NO ACTION：先阻止，再显式处理

当父子对象能独立存在，或历史数据不能静默消失时，优先考虑限制删除。例如商品已被订单明细引用，直接级联删除明细会破坏历史订单，所以商品关系使用 RESTRICT。

在 MySQL 的 InnoDB 中，NO ACTION 与 RESTRICT 的效果都是立即拒绝破坏引用完整性的父表修改。其他数据库可能支持延迟检查，因此跨数据库设计时不能只看关键词相似就认定行为完全相同。

CASCADE：子记录确实没有独立生命

订单明细离开订单本身就没有意义，因此：

FOREIGN KEY (order_id)
REFERENCES orders (order_id)
ON DELETE CASCADE

删除订单会自动删除对应明细。便利的同时也放大了误删范围。级联链条如果继续向下延伸，一次父表删除可能影响许多表。启用之前要画清依赖方向，估算受影响行数，并确定审计与恢复方案。

SET NULL：关系可选，记录仍应保留

员工的上级离职后，员工仍然存在：

FOREIGN KEY (manager_id)
REFERENCES employees (employee_id)
ON DELETE SET NULL

使用 SET NULL 的前提是子表外键列允许 NULL。如果 manager_id 声明成 NOT NULL，删除策略和列约束会互相冲突。

ON UPDATE CASCADE 通常不是替代稳定主键的办法

父键更新时，ON UPDATE CASCADE 可以把新值传播给子表。但课程中的 ID 主键本来就应稳定，频繁更新主键通常说明身份设计有问题。级联更新是可用工具，不应成为“主键随业务字段变化”的补丁。

约束与索引的关系：实现手段不等于业务含义

主键和唯一约束需要快速判断“是否已存在相同键”，数据库通常通过唯一索引实现。于是你会在 SHOW INDEX 中看到相应索引。但不能因此说“约束就是索引”。

外键列的索引存在数据库差异

MySQL/InnoDB 要求外键列有合适索引；子表缺少时会自动创建能够以前缀支持外键列的索引。PostgreSQL 会保证被引用的主键或唯一键有索引，但不会自动为子表的引用列创建索引，因为不同查询可能需要不同的列组合。

即使 MySQL 自动创建了单列外键索引，也要检查它是否与业务查询的复合索引重叠。例如有：

CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date);

它以 customer_id 开头，可以支持 orders.customer_id 的外键访问需求。表上如果还保留完全重复的单列索引，就要确认是否有独立价值。

不要用唯一索引偷偷表达不清楚的规则

下面两条语句在 MySQL 中都能阻止非空邮箱重复：

ALTER TABLE customers
ADD CONSTRAINT uq_customers_email UNIQUE (email);
 
CREATE UNIQUE INDEX uq_customers_email_idx
ON customers (email);

对纯粹的数据规则，优先用约束命名和表达意图更清楚；对部分行唯一、表达式唯一等特殊能力，才考虑数据库提供的专门索引形式，并记录方言差异。别让后来的维护者只能通过一个含糊索引名猜业务规则。

把规则落进现有电商表

下面给出一组围绕课程字段的核心定义。它重点展示约束和索引，不重复添加课程之外的业务列。

CREATE TABLE customers (
    customer_id INTEGER NOT NULL,
    customer_name VARCHAR(40) NOT NULL,
    phone VARCHAR(20),
    email VARCHAR(100),
    city VARCHAR(30) NOT NULL,
    registered_at DATETIME NOT NULL,
    referrer_id INTEGER,
    CONSTRAINT pk_customers 

订单和订单明细继续连接：

CREATE TABLE orders (
    order_id INTEGER NOT NULL,
    customer_id INTEGER NOT NULL,
    order_date DATETIME NOT NULL,
    status VARCHAR(12) NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    CONSTRAINT pk_orders PRIMARY KEY (order_id),
    INDEX idx_orders_customer_date (customer_id, order_date),
    INDEX idx_orders_status_date (

为什么订单上同时保留 (customer_id, order_date) 和 (status, order_date)？前者服务客户订单历史，后者服务“某状态在一段日期中的订单”。它们的左列不同，不能互相替代。是否都长期保留，仍要由真实查询和计划决定。

一条查询怎样同时受益于两类机制

SELECT
    o.order_id,
    o.order_date,
    o.status,
    oi.product_id,
    oi.quantity,
    oi.unit_price,
    oi.discount_rate
FROM orders AS o
JOIN order_items AS oi
  ON oi.order_id = o

idx_orders_customer_date 帮助定位客户的日期区间；在 MySQL/InnoDB 中，order_items.order_id 的外键需要相应索引，缺少时会自动建立，帮助从订单找到明细。外键确保每条明细一定指向存在的订单，检查约束确保数量和折扣率处于允许范围。索引缩短寻找过程，约束让被找到的数据关系可信。

约束上线前，先处理历史脏数据

向已有表添加约束时，数据库会检查现有数据。旧数据不合规，ALTER TABLE 就会失败。不要通过临时关闭约束后直接宣布完成，应该先查清脏数据如何产生、如何修复。

查重复邮箱

SELECT email, COUNT(*) AS duplicate_count
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;

结果如果是：

不能直接随便删两行。应先确认邮箱是否被错误复用、客户是否重复建档，以及订单该归属哪位客户。清洗完成后再添加唯一约束。

查孤儿订单明细

SELECT oi.order_item_id, oi.order_id
FROM order_items AS oi
LEFT JOIN orders AS o
  ON o.order_id = oi.order_id
WHERE o.order_id IS NULL;

出现结果说明明细引用了不存在的订单。根据业务证据补回订单、迁移明细或删除无效记录，不能只为让外键创建成功而盲目删除。

查不合法范围

SELECT order_item_id, quantity, unit_price, discount_rate
FROM order_items
WHERE quantity <= 0
   OR unit_price < 0
   OR discount_rate < 0
   OR discount_rate > 1
   OR quantity IS NULL
   OR unit_price IS NULL
   OR discount_rate IS NULL;

先把查询结果按成因分类：导入映射错误、单位理解错误、默认值缺失，还是应用校验漏掉了路径。约束能阻止新的脏数据继续进入，但不会替你决定旧数据的正确修复值。

常见报错怎样顺着规则排查

约束报错不是数据库在“找麻烦”，它是在指出哪条数据规则被破坏。排错时从约束名称、目标值和相关父记录开始，比反复重试更有效。

重复邮箱：先确认业务上是否真重复

INSERT INTO customers
    (customer_id, customer_name, phone, email, city, registered_at, referrer_id)
VALUES
    (11, '苏小满', '13800001999', 'xiaoman@example.com', '杭州', NOW(), NULL);

课程数据中 xiaoman@example.com 已属于客户 1，MySQL 会报告重复键，并指出邮箱唯一键。排查顺序：

1. 用报错值查询已有客户。
2. 判断这是同一客户重复注册，还是业务允许多人共用联系方式。
3. 如果规则正确，修复写入流程；如果业务确实允许重复，重新讨论约束，而不是在代码里吞掉错误。

子表写入失败：父记录不存在

INSERT INTO order_items
    (order_item_id, order_id, product_id, quantity, unit_price, discount_rate)
VALUES
    (90001, 999999, 1, 2, 39.90, 0.10);

order_id = 999999 不存在时，外键拒绝插入。先查父表：

SELECT order_id
FROM orders
WHERE order_id = 999999;

如果订单本该由同一业务动作创建，要检查事务中的执行顺序和主键传递；如果订单编号本身错了，应修正输入。不要把 FOREIGN_KEY_CHECKS 关闭当成正常写入方案。

父表删除失败：仍有子记录引用

DELETE FROM products
WHERE product_id = 1;

如果订单明细或库存流水仍引用商品，RESTRICT 会阻止删除。可以先统计影响范围：

SELECT COUNT(*) AS order_item_count
FROM order_items
WHERE product_id = 1;
 
SELECT COUNT(*) AS movement_count
FROM inventory_movements
WHERE product_id = 1;

对已经进入历史订单的商品，更常见的做法是把 products.status 更新为 discontinued，保留身份与历史关系，而不是物理删除。

检查约束失败：不要只改到“刚好能过”

UPDATE products
SET stock = -1
WHERE product_id = 1;

ck_products_stock 会拒绝负库存。排查时应继续问：这是并发扣减造成的，还是库存业务允许欠货却用了错误的数据模型？如果不允许负库存，上一章的事务和本章的检查约束要同时使用：事务保证扣减与流水一致，检查约束兜住最终值。

综合练习

练习：为客户订单历史设计索引

查询如下：

SELECT order_id, order_date, status, total_amount
FROM orders
WHERE customer_id = ?
  AND order_date >= ?
  AND order_date < ?
ORDER BY order_date DESC;

请设计一条索引，并说明列顺序、范围条件和覆盖查询之间的关系。

CREATE INDEX idx_orders_customer_date_cover
ON orders (customer_id, order_date, status, total_amount);

练习：解释为什么第二列单独查询不理想

已有索引：

CREATE INDEX idx_products_category_status_price
ON products (category_id, status, unit_price);

判断下面三条查询中，哪一条最符合这个索引的排序方式：

-- A
SELECT product_id, product_name
FROM products
WHERE category_id = 1;
 
-- B
SELECT product_id, product_name
FROM products
WHERE status = '在售';
 
-- C
SELECT product_id, product_name, unit_price
FROM products
WHERE category_id = 1
  AND status = '在售'
  AND unit_price BETWEEN 100 AND

练习：把函数条件改成可定位范围

下面的查询用于统计 2026 年 6 月订单：

SELECT COUNT(*)
FROM orders
WHERE DATE_FORMAT(order_date, '%Y-%m') = '2026-06';

在已有 orders(order_date) 索引的前提下，改写查询。

SELECT COUNT(*)
FROM orders
WHERE order_date >= '2026-06-01 00:00:00'
  AND order_date <  '2026-07-01 00:00:00';

练习：从 EXPLAIN 找出真正的问题

某条查询的计划如下：

查询为：

SELECT *
FROM orders
WHERE status = '已完成';

为什么有候选索引却仍全表扫描？你会先改什么？

练习：为库存流水设计复合索引

常见查询是查看某商品一段时间内的库存变动：

SELECT movement_id, movement_type, quantity, moved_at, note
FROM inventory_movements
WHERE product_id = ?
  AND moved_at >= ?
  AND moved_at < ?
ORDER BY moved_at DESC;

请给出一条基础索引，并说明为什么不先把 movement_type 放在第一列。

CREATE INDEX idx_movements_product_time
ON inventory_movements (product_id, moved_at);

练习：为订单明细选择删除策略

请判断下面两个外键更适合 CASCADE 还是 RESTRICT，并解释原因：

1. order_items.order_id 引用 orders.order_id。
2. order_items.product_id 引用 products.product_id。

练习：同时补齐非空与检查约束

payments 的字段为 payment_id, order_id, paid_at, amount, payment_method, status。要求金额必须大于 0，付款方式与状态必须填写，请添加约束。

ALTER TABLE payments
    MODIFY order_id INTEGER NOT NULL,
    MODIFY paid_at DATETIME NOT NULL,
    MODIFY amount DECIMAL(10, 2) NOT NULL,
    MODIFY payment_method VARCHAR(12) NOT NULL,
    MODIFY status VARCHAR(12) NOT NULL,
    ADD CONSTRAINT ck_payments_amount 

从表的内部规则走向稳定的查询入口

学到这里，我们已经给数据库补上两层能力：索引让数据库用更短的路径找到行，约束让每一行以及表之间的关系保持在允许范围内。它们都工作在基础表内部，一个关心访问成本，一个关心数据边界。

接下来会出现另一个问题：表结构虽然正确，使用者却不一定需要看到全部列，也不应该每次都重写复杂连接。客服只想看到脱敏后的客户信息，报表只想面对整理好的订单汇总，应用还希望底层表调整时查询接口尽量稳定。下一章的视图，会把已经验证过的查询定义成可复用的数据库对象，在表与使用者之间再加一层清晰入口。

NOT NULL 阻止缺值，CHECK 约束金额范围和状态集合，外键保证付款属于存在的订单。添加前要先查询历史数据是否为空、金额是否非正、状态是否超出集合，以及是否有孤儿付款记录。