第08周 索引设计与数据库表综合实训

第8周 · 索引设计与数据库表综合实训

这周前半部分学"索引"——让查询变快的秘密武器；后半部分是综合实训，把前几周的建库、建表、约束、外键串起来，做一个完整的"班级成绩管理"小项目。

---

一、知识点：索引为什么快

没有索引时，MySQL 查一条数据要逐行扫描（全表扫描），数据越多越慢。索引就像书的目录：翻目录直接定位页码，不用一页页翻。

MySQL 索引底层一般是 B+ 树（一种有序多叉树），能快速"折半"定位到数据位置。不是所有索引都适合——索引本身也要占用空间、也要维护。

---

二、知识点：索引的分类

类型	说明	是否允许重复
普通索引 INDEX	最基本的加速索引	允许
唯一索引 UNIQUE INDEX	列值不重复	不可（除一个 NULL）
主键索引 PRIMARY KEY	主键自动带索引	不可
复合索引（联合索引）	多个列组合成一个索引，如 (sid, cid)	组合值唯一

主键、唯一约束会自动创建对应索引，不用再单独建。

---

三、操作教程：创建索引（CREATE INDEX）

USE school;

-- 普通索引：给 student 的 sname 加速按姓名查询
CREATE INDEX idx_name ON student(sname);

-- 复合索引：给 sc 的 (sid, cid) 组合建索引（常用于按学号+课程查成绩）
CREATE INDEX idx_sid_cid ON sc(sid, cid);

-- 唯一索引示例
CREATE UNIQUE INDEX idx_idcard ON student(idcard);

查看表的索引：

SHOW INDEX FROM student;

---

四、操作教程：删除索引（DROP INDEX）

DROP INDEX idx_name ON student;

删除索引只去掉"目录"，不影响表数据和结构。

---

五、知识点：索引的代价与建索引原则

好处：大幅加速 WHERE 过滤、JOIN 连接、ORDER BY 排序。

代价： - 占用额外存储空间； - 拖慢 INSERT / UPDATE / DELETE（每次改数据都要维护索引）。

哪些列适合建索引： - 经常出现在 WHERE、JOIN、ORDER BY 后的列； - 高选择性列（取值多、重复少），如学号、身份证号。

哪些列不适合建索引： - 取值很少的列（如性别"男/女"，建了也没用）； - 很长的文本（TEXT）； - 频繁更新的列； - 小表（数据少，全表扫描本身就快，建索引反而亏）。

常见错误：给所有列都建索引。索引不是越多越好——只给真正需要加速的列建。

补充：复合索引遵循最左前缀原则。建了 idx_sid_cid(sid, cid)，查询用 sid 或 sid+cid 能命中；只用 cid 查则命中不了，需另建 (cid) 索引。

---

六、综合实训：三表设计（成绩管理）

把 student、course、sc 三张表一次建好，包含主外键关系：

USE school;

-- 学生表
CREATE TABLE student (
  sid    INT PRIMARY KEY AUTO_INCREMENT,
  sname  VARCHAR(20) NOT NULL,
  sage   INT DEFAULT 18
);

-- 课程表
CREATE TABLE course (
  cid   INT PRIMARY KEY,
  cname VARCHAR(40) NOT NULL
);

-- 成绩表：联合主键(sid,cid)，两个外键分别引用前两表
CREATE TABLE sc (
  sid   INT,
  cid   INT,
  grade INT,
  PRIMARY KEY (sid, cid),
  FOREIGN KEY (sid) REFERENCES student(sid)
    ON DELETE CASCADE,
  FOREIGN KEY (cid) REFERENCES course(cid)
    ON DELETE CASCADE
);

联合主键 (sid, cid) 保证"一个学生一门课只有一条成绩"，不会重复录入。

---

七、综合实训流程（完整一遍）

1. CREATE DATABASE school CHARACTER SET utf8mb4;
2. USE school;
3. 按顺序建 student → course → sc（先建被引用的父表）。
4. 给常用查询列加索引（如 idx_name、idx_sid_cid）。
5. 录入样例数据验证： sql INSERT INTO student(sname) VALUES ('张三'),('李四'); INSERT INTO course(cid,cname) VALUES (1,'MySQL数据库'),(2,'计算机网络'); INSERT INTO sc VALUES (1,1,88),(1,2,92),(2,1,76);
6. 查询验证：SELECT * FROM sc WHERE sid=1;

---

八、易混点排查表

现象	解决方法
查询没变快	可能没建索引，或不符合最左前缀；用 EXPLAIN 看执行计划
索引越多越慢（写入）	多余索引拖累增删改；删掉不用到的索引
复合索引只用第二列查不到	不符合最左前缀；改查第一列或单建该列索引
建外键报类型不一致	父表/子表列类型长度必须一致
小表建一堆索引	小表全表扫描就够快，不必建
主键还要再建索引吗	不需要，主键自动带主键索引

---

九、小测验（自测）

1. 给 student(sname) 建普通索引、给 sc(sid,cid) 建复合索引的 SQL 分别怎么写？
2. 索引带来什么好处、付出什么代价？哪些列不适合建索引？
3. 什么是复合索引的"最左前缀原则"？
4. 写出 student/course/sc 三表（含主键、外键、联合主键）的建表 SQL。

---

【声明】本文由 AI 辅助生成，仅供 MySQL 数据库教学参考，如有疑误以教材为准。