数据定义与索引

第四讲：建库建表的完整语法、五类约束、数据类型怎么选，以及"为什么加了 WHERE 条件查询还是慢"——索引。
先记住一句话：表和约束是"数据的法律"，索引是"数据的目录"——目录建在值分布最乱的列上收益最大，建在几乎人人相同的列上纯属浪费。

一、DDL 四件套

sql

CREATE DATABASE / TABLE   -- 建
ALTER DATABASE / TABLE    -- 改结构
DROP DATABASE / TABLE     -- 删（整库整表，不可恢复，生产环境高危）
TRUNCATE TABLE            -- 清空数据但保留表结构（比 DELETE 快，不可回滚）

ALTER TABLE 的常用形态：

sql

ALTER TABLE student ADD COLUMN email VARCHAR(50);        -- 加列
ALTER TABLE student DROP COLUMN email;                   -- 删列
ALTER TABLE student MODIFY COLUMN sname VARCHAR(30);     -- 改类型
ALTER TABLE student RENAME TO stu;                       -- 改表名

⚠️ DROP 与 TRUNCATE 属于 DDL，隐式提交、不能回滚；DELETE 是 DML 可回滚。三者区别是必考选择题：速度 TRUNCATE > DELETE，安全 DELETE > TRUNCATE。

二、数据类型选择：三个原则

够用且更小：TINYINT 能放就别用 INT；小类型省空间，页里塞得多，扫描就快
定长 vs 变长：CHAR(n) 定长补空格适合固定编号；VARCHAR(n) 变长适合姓名标题
精确小数用 DECIMAL：金额列禁止用 FLOAT/DOUBLE（浮点误差），DECIMAL(10,2) 才是账本

时间类型记三个：DATE 日期、DATETIME 日期时间、TIMESTAMP 带时区自动更新（常用于 updated_at）。

三、五类约束

约束	关键字	作用	违反后果
主键	`PRIMARY KEY`	唯一标识，非空不重	拒绝插入
外键	`FOREIGN KEY ... REFERENCES`	参照完整性	拒绝 / 级联 / 置空
唯一	`UNIQUE`	列值不重复（允许 NULL）	拒绝插入
检查	`CHECK (表达式)`	用户定义合法性（MySQL 8.0.16 起真正生效）	拒绝插入
非空/默认	`NOT NULL` / `DEFAULT`	必填与兜底值	拒绝 / 自动填充

第二讲已经写过含全部约束的建表语句，这里补一个"事后加约束"的写法：

sql

ALTER TABLE sc ADD CONSTRAINT fk_sno FOREIGN KEY (sno) REFERENCES student(sno) ON DELETE CASCADE;
ALTER TABLE student ADD CONSTRAINT uq_mail UNIQUE (email);

四、索引：B+ 树、聚簇与失效

索引 = 帮查询跳过全表扫描的数据结构，InnoDB 用 B+ 树。只需要记住 B+ 树的三个特征：

多叉平衡树，3~4 层就能撑住千万行——一次查询约等于 3~4 次磁盘访问
数据全部存在叶子节点，且叶子之间有序链表相连——所以范围查询（BETWEEN、ORDER BY）飞快
聚簇索引（主键）：叶子存整行数据，一张表只有一个；二级索引叶子存主键值，查完还要"回表"

主键为什么推荐自增 ID？ 追加写在最右叶子，不会把写满的页拆开；随机主键（如 UUID）会让插入落点乱跳，页分裂频繁，写性能骤降。

什么时候建索引、什么时候白建：

建了有用	建了白建
WHERE / JOIN / ORDER BY 高频列	几乎全员同值的列（如"性别"）
值分布差异大的列	表只有几百行（全扫更快）
写少读多的表	高频写入的表（索引要同步维护）

五、动手：用 EXPLAIN 亲眼看见索引的作用

💡 动手实验：造 10 万行数据，对比有无索引的查询计划：

sql

-- 造数据：借助数字辅助表批量插入（略去具体过程，任何方式灌 10 万行即可）
CREATE TABLE t (id INT PRIMARY KEY AUTO_INCREMENT, a INT, b INT, pad CHAR(10));
-- …插入 id=1..100000，a 为 1~1000 随机数，b = id % 10 …

ALTER TABLE t ADD INDEX idx_a (a);

EXPLAIN SELECT * FROM t WHERE a = 500;   -- type: ref, key: idx_a, rows: ~100
EXPLAIN SELECT * FROM t WHERE b = 3;     -- type: ALL, key: NULL, rows: ~100000

EXPLAIN 的 type 列从好到坏：system > const > eq_ref > ref > range > index > ALL——看到 ALL 就是全表扫描，数据量一大必慢。

💡 加餐——索引失效三场景（面试高频）：

sql

SELECT * FROM t WHERE YEAR(created_at) = 2026;      -- ① 对列套函数：失效
SELECT * FROM t WHERE a + 1 = 500;                  -- ② 对列做运算：失效
SELECT * FROM t WHERE phone = 13800000000;          -- ③ 类型不匹配（phone 是 VARCHAR）：失效

口诀：列要"裸着"出现在条件里，一加工就废。

小结

DROP（删结构）/ TRUNCATE（清数据保结构）/ DELETE（逐行、可回滚）
金额用 DECIMAL；主键用自增；CHAR 定长 VARCHAR 变长
B+ 树：叶子存数据 + 有序链表；聚簇索引一表一个；二级索引要回表
EXPLAIN 看到 type: ALL 就该警觉；套函数、做运算、类型不匹配是三大失效场景

下一讲解决"表应该怎么设计"：ER 图与范式——ER 图与范式设计。

数据定义与索引 ​

一、DDL 四件套 ​

二、数据类型选择：三个原则 ​

三、五类约束 ​

四、索引：B+ 树、聚簇与失效 ​

五、动手：用 EXPLAIN 亲眼看见索引的作用 ​