约束与索引设计

学习目标

• 能用非空、唯一、检查与外键表达数据库完整性。
• 能区分聚簇与二级索引，并从查询设计联合索引。
• 能衡量选择性、覆盖收益、写入代价和冗余索引。

核心知识点

本节固定 MySQL 8.0 / InnoDB，沿用 learning_lab.users/orders 种子模型，额外约束实验使用独立小表。约束负责拒绝不合法数据，索引负责提供访问路径；两者相关但不能互相替代。添加约束或索引前，先核对现存数据和版本；DDL 通常隐式提交，可能等待元数据锁，生产变更需迁移窗口与恢复方案。

InnoDB 索引通常用 B+Tree 组织，叶子顺序支持范围读取。聚簇索引叶子包含完整行：优先主键，无主键时选择适合的首个 UNIQUE NOT NULL 索引，否则生成隐藏行 ID。二级索引叶子保存索引键和主键值；查未覆盖字段通常需要回聚簇索引。宽主键会扩大二级索引。聚簇与二级索引。

常用用法

CREATE TABLE NOT NULL DEFAULT：定义缺失值规则

用于为业务必填字段禁止 NULL，并为省略列提供合理默认值。

USE learning_lab;
-- 初始：lab_defaults不存在，准备写入id=1。
CREATE TABLE lab_defaults(id INT PRIMARY KEY,label VARCHAR(16) NOT NULL DEFAULT '待命名') ENGINE=InnoDB;
INSERT INTO lab_defaults(id) VALUES (1);
SELECT id,label FROM lab_defaults;
-- 关键变化：省略label采用默认值。
-- 输出：1/待命名。

显式 NULL 不等于省略列；严格模式下向 NOT NULL 列写 NULL 会报错。NOT NULL 仍允许空字符串，必填文本还需业务校验。不要把默认值设计成掩盖未知业务状态。

CREATE TABLE UNIQUE：约束唯一业务键

用于阻止重复业务标识，同时观察可空唯一列的边界。

-- 初始：lab_unique不存在。
CREATE TABLE learning_lab.lab_unique(id INT PRIMARY KEY,code VARCHAR(16) NULL,UNIQUE KEY uk_code(code)) ENGINE=InnoDB;
INSERT INTO learning_lab.lab_unique VALUES (1,'A'),(2,NULL),(3,NULL);
SELECT id,code FROM learning_lab.lab_unique ORDER BY id;
-- 关键变化：唯一索引允许多个NULL。
-- 输出：1/A、2/NULL、3/NULL。

再次插入 code='A' 会报重复键错误；唯一性还受排序规则影响，大小写不敏感规则下 A 与 a 可能冲突。希望业务键始终存在时同时使用 NOT NULL。

CREATE TABLE CHECK：验证行内条件

用于限制金额非负，并明确 CHECK 不能替代非空约束。

-- 初始：lab_checks不存在；要求MySQL 8.0.16+。
CREATE TABLE learning_lab.lab_checks(id INT PRIMARY KEY,amount DECIMAL(12,2) NULL,
 CONSTRAINT ck_nonnegative CHECK(amount>=0)) ENGINE=InnoDB;
INSERT INTO learning_lab.lab_checks VALUES (1,10),(2,NULL);
SELECT id,amount FROM learning_lab.lab_checks ORDER BY id;
-- 关键变化：TRUE与UNKNOWN均通过检查。
-- 输出：1/10、2/NULL。

插入负金额会失败；8.0.16 前 CHECK 被解析但不执行，不能按早期 8.0 推断有保护。CHECK 条件 FALSE 拒绝，TRUE 或 UNKNOWN 允许，因此金额必填需 NOT NULL。CHECK 不能做跨表余额约束。CHECK 官方说明。

CREATE TABLE FOREIGN KEY：保护关联与级联

用于让独立实验订单只能关联已存在用户，并观察删除级联。

USE learning_lab;
-- 初始：lab_fk_users/orders不存在，模拟用户7及订单701。
CREATE TABLE lab_fk_users(id BIGINT UNSIGNED PRIMARY KEY) ENGINE=InnoDB;
-- 风险：ON DELETE CASCADE让父表删除传播到子表行，仅在实验表核对主键关联并回滚演练。
CREATE TABLE lab_fk_orders(id BIGINT UNSIGNED PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,
 CONSTRAINT fk_lab_user FOREIGN KEY(user_id) REFERENCES lab_fk_users(id) ON DELETE CASCADE) ENGINE=InnoDB;
INSERT INTO lab_fk_users VALUES (7);
INSERT INTO lab_fk_orders VALUES (701,7);
START TRANSACTION;
-- 风险：DELETE CASCADE会传播到子表；仅在独立实验表按已核对主键演练并回滚。
DELETE FROM lab_fk_users WHERE id=7;
SELECT COUNT(*) AS child_count FROM lab_fk_orders;
ROLLBACK;
SELECT id,user_id FROM lab_fk_orders;
-- 关键变化：删除父7后子表为空，回滚后恢复701/7。
-- 输出：child_count=0；最终701/7。

无级联时 InnoDB 的 NO ACTION 相当于立即 RESTRICT；没有延迟到事务提交才检查。SET NULL 要求子列可空；父子键类型（含整数符号）需兼容，并有合适索引。外键不能代替跨服务业务校验。级联外键动作不会激活触发器。外键。

SHOW INDEX：检查实际索引

用于检查种子订单的主键、二级键、索引列顺序与估算基数。

-- 初始：orders有PRIMARY(id)与idx_orders_user(user_id)。
SHOW INDEX FROM learning_lab.orders;
-- 关键变化：读取索引元数据，返回PRIMARY与idx_orders_user。
-- 输出：PRIMARY唯一，idx_orders_user非唯一；Cardinality为估算值而非精确计数。

选择性描述键值区分行的能力；低基数状态列通常难以单独大幅缩小扫描，但结合查询、排序、覆盖仍可能有价值。不能只用“区分度高”决定列顺序。

CREATE INDEX composite：建立联合索引

用于按用户筛选后按创建时间与主键稳定排序。

-- 初始：orders三条种子订单；本索引尚不存在。
CREATE INDEX idx_orders_user_created_id ON learning_lab.orders(user_id,created_at,id);
EXPLAIN SELECT id,created_at FROM learning_lab.orders
WHERE user_id=1 ORDER BY created_at,id LIMIT 2;
-- 关键变化：新增联合B+Tree。
-- 输出：计划候选键可含idx_orders_user_created_id，实际选择以计划为准。

最左前缀意味着 (a,b,c) 可以按 (a)、(a,b)、(a,b,c) 定位；仅 b 的普通查询不能按常规前缀直接定位，但优化器某些场景可选择 skip scan 或全索引扫描。范围条件后面的列不一定继续缩小同一连续扫描区间，仍可能用于 Index Condition Pushdown、覆盖或其他优化，不能说“全部失效”。联合索引。

EXPLAIN covering：观察覆盖索引

用于判断结果列是否可以仅从某个索引中获取。

-- 前置条件：已创建(user_id,created_at,id)索引。
EXPLAIN SELECT id,created_at FROM learning_lab.orders WHERE user_id=1;
-- 关键变化：读取user_id=1仅返回id和created_at的覆盖查询计划。
-- 输出：若采用覆盖二级索引，Extra可见Using index。

覆盖是一条查询与某个索引的关系，不是专门的索引类型。二级索引自带主键；Using index condition 表示 ICP，不等于 Using index。小表也可能走其他更便宜路径，不保证每次都出现该标志。

CREATE INDEX prefix：只索引文本前缀

用于限制长字符串索引长度，并比较前缀的区分能力。

-- 初始：users.id=1、2姓名林同学、陈同学，索引尚不存在。
CREATE INDEX idx_users_name_prefix ON learning_lab.users(username(2));
SELECT COUNT(DISTINCT LEFT(username,2)) AS prefixes,COUNT(*) AS n
FROM learning_lab.users WHERE id IN (1,2);
-- 关键变化：新增username的2字符前缀索引，姓名前缀仍区分种子两行。
-- 输出：prefixes=2,n=2，种子数据不能代表真实分布。

前缀长度对非二进制字符串按字符、二进制字符串按字节计；前缀索引通常不能覆盖完整字符串读取或完整字符串排序。现有完整 UNIQUE(username) 已可支持很多姓名查询，本索引仅演示，生产不应随意重复添加。

CREATE INDEX functional：建立表达式索引

用于让确定的表达式具备索引访问路径而不在应用保存重复值。

-- 前置条件：MySQL 8.0.13+，users包含林同学；索引尚不存在。
CREATE INDEX idx_users_lower_name ON learning_lab.users((LOWER(username)));
EXPLAIN SELECT id FROM learning_lab.users WHERE LOWER(username)='林同学';
-- 关键变化：保存LOWER表达式的索引键。
-- 输出：检查候选键与实际key，不承诺小表使用。

函数键用双括号，受生成列表达式规则限制，不能包含任意子查询或非确定函数。JSON 路径文本可 CAST 到有界 CHAR 再索引，查询表达式、类型与排序规则必须匹配；另一选择是显式生成列并查询该列。JSON 本身不能直接作为普通索引键。CREATE INDEX 函数键。

CREATE INDEX descending：支持降序键组合

用于为用户订单的降序浏览建立方向匹配的联合索引。

-- 初始：orders索引idx_orders_feed尚不存在。
CREATE INDEX idx_orders_feed ON learning_lab.orders(user_id ASC,created_at DESC,id DESC);
EXPLAIN SELECT id,created_at FROM learning_lab.orders
WHERE user_id=1 ORDER BY created_at DESC,id DESC LIMIT 2;
-- 关键变化：新增idx_orders_feed的3列降序组合键。
-- 输出：检查是否利用索引顺序，实际计划由代价决定。

MySQL 8.0 InnoDB 支持真正的降序索引；全反向排序也可能反向扫描已有升序索引，混合方向才更需审查。降序二级索引有变更缓冲限制，需衡量写入成本。

ALTER TABLE invisible：暂时让优化器忽略索引

用于在保留索引维护的情况下评估删除前的计划影响。

-- 前置条件：idx_orders_feed存在，测试环境无并发业务。
-- 风险：隐藏索引会改变计划并可能扩大扫描；仅在实验表评估，完成后立即恢复。
ALTER TABLE learning_lab.orders ALTER INDEX idx_orders_feed INVISIBLE;
EXPLAIN SELECT id FROM learning_lab.orders WHERE user_id=1 ORDER BY created_at DESC,id DESC;
ALTER TABLE learning_lab.orders ALTER INDEX idx_orders_feed VISIBLE;
SHOW INDEX FROM learning_lab.orders WHERE Key_name='idx_orders_feed';
-- 关键变化：默认优化器先忽略再恢复idx_orders_feed的3个键列。
-- 输出：最终Visible=YES。

不可见索引仍维护并继续唯一约束，不能隐藏主键。use_invisible_indexes 可让优化器使用隐藏索引，评估时先核对该开关。联合键可能使原单列键冗余，但外键需求、唯一性、键宽与其他查询都会影响判断；先观测工作负载再删除。不可见索引。

易混点

约束是正确性规则，索引是访问路径；UNIQUE 的 NULL 语义不等于 NOT NULL。建立索引增加写放大、空间与维护负担，“字段常查就建一个”容易形成冗余。索引存在不表示每条查询都会用它。

课后小问

1. 为什么二级索引中有主键？

   答：二级键通过主键定位聚簇记录；这也是主键宽度影响二级索引大小的原因。

2. CHECK(amount>=0) 是否拒绝 NULL？

   答：不拒绝 UNKNOWN；需要 NOT NULL 同时限制缺失。

本节小结

先用约束固定数据规则，再按过滤、排序和结果列设计少量有效索引；所有收益都应由实际工作负载与执行计划验证。

快速回顾

• CHECK 执行需要8.0.16+；函数键需要8.0.13+。
• 最左前缀用于定位，覆盖用于减少回表。
• 前缀、函数、降序、不可见索引都有边界；评估代价后再保留。