索引 与 MySQL 索引管理

① 索引的作用：以空间换时间

为什么数据库需要索引？

在大多数业务场景中，数据库 "读"操作的次数远多于"写"操作（一个网站，浏览页面可能上千次，但下单可能就几次）。所以 "如何让读得更快" 成了数据库优化的核心命题。

字典类比图

索引也不是免费的

② 索引的类型：四个分类维度

索引可以从 4 个不同的角度 进行分类。这 4 个维度是 独立的 —— 同一个索引可以同时被打上多个标签。比如：一个索引可能既是"非聚集索引"，又是"复合索引"，还是"唯一索引"，使用"B-Tree 存储方式"。

重点理解 1：聚集索引 vs 非聚集索引

重点理解 2：单列索引 vs 复合索引

重点理解 3：按特征的 5 种索引

重点理解 4：B-Tree 索引 vs Hash 索引

③ 索引的设置原则：5 条铁律

索引不是越多越好，要"会用"。下面是 5 条核心原则：

1. 1
   严格限制同一个表上的索引数量。索引会拖慢 INSERT/UPDATE/DELETE 的执行性能。对于使用频度低或不再使用的索引，要及时删除。
2. 2
   对"重复值较多"的列，不建议建索引。比如"性别"字段只有"男/女"两个值 —— 索引扫一半才能锁定，效果还不如直接全表扫。
3. 3
   对"排序、分组、表连接"涉及的字段建索引，可显著提升检索效率。比如经常在 ORDER BY、GROUP BY、JOIN ON 中出现的字段。
4. 4
   对视图建立索引，将提升视图的检索效率（这是上节课视图的延伸）。
5. 5
   注意唯一索引和全文索引对 NULL 的处理方式。唯一索引允许多个 NULL（NULL 不参与"唯一性"判断），全文索引会忽略 NULL 值，使用前要清楚这些差异。

反例对比：什么时候 不应该 建索引？

📋 第一部分小结

🎯 概念小测

📦 准备工作：复用上次课的环境

本节继续使用上次课建好的 school 数据库（s 学生、c 课程、sc 选课 三张表）。如果你已经按上次的脚本建好了，这一步可以跳过。如果忘了或者数据被改乱了，下面是简化版的重建脚本，再加一张教师表 t（本节有例子要用）：

USE school;

-- 新增：教师表 t（如果不存在）
CREATE TABLE IF NOT EXISTS t (
  tno VARCHAR(10) PRIMARY KEY,    -- 教师工号
  tn  VARCHAR(20) NOT NULL,       -- 教师姓名
  title VARCHAR(10),                -- 职称
  dept  VARCHAR(20)                 -- 所在学院
);

INSERT IGNORE INTO t VALUES
('t01', '王老师', '教授',   '信息学院'),
('t02', '李老师', '副教授', '信息学院'),
('t03', '张老师', '讲师',   '理学院');

① 创建索引：三种方式

MySQL 中创建索引有 3 种语法路径，加上 Workbench 图形化操作共 4 种。我们重点掌握前 3 种。

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名称
  ON 表名(字段名 [(索引字符长度) [ASC | DESC]] [, ...]);

CREATE INDEX s_name_index
  ON s(sn(6) DESC);

SHOW INDEX FROM s;

CREATE UNIQUE INDEX c_cn_ct_index
  ON c(cn, ct);

CREATE TABLE 表名(
  属性名1 数据类型 [列约束],
  ...
  属性名n 数据类型 [列约束],
  [表约束],
  [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY 索引名1 (字段名 [(长度) [ASC|DESC]]),
  ...
  [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY 索引名n (字段名 [(长度) [ASC|DESC]])
);

CREATE TABLE classroom (
  cid  INT AUTO_INCREMENT,           -- 自增主键
  crno VARCHAR(10) NOT NULL,         -- 教室编号
  cbn  VARCHAR(10) NOT NULL,         -- 教学楼
  PRIMARY KEY(cid),
  UNIQUE INDEX cn_cb_index(crno, cbn)   -- 在这里附加索引
);

-- 验证
SHOW INDEX FROM classroom;

ALTER TABLE 表名
  ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY 索引名1 (字段名 [(长度) [ASC|DESC]]),
  ...
  ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY 索引名n (字段名 [(长度) [ASC|DESC]]);

ALTER TABLE t
  ADD INDEX tn_index(tn(6) DESC);

-- 验证
SHOW INDEX FROM t;

ALTER TABLE t
  ADD INDEX idx_title(title),
  ADD INDEX idx_dept(dept);

② 查看索引：SHOW INDEX

语法格式

SHOW INDEX FROM 表名 [FROM 数据库名];

例子：查看学生表 s 的索引

SHOW INDEX FROM s;
-- 等价于：SHOW INDEX FROM s FROM school;

③ 删除索引：两种语法

MySQL 提供了两种删除索引的语法，效果一样，按习惯选用：

方式 1：ALTER TABLE ... DROP INDEX

ALTER TABLE 表名
  DROP INDEX 索引名;

方式 2：DROP INDEX ... ON 表名

DROP INDEX 索引名 ON 表名;

例子：删除 s 表上的 s_name_index

📋 第二部分小结

✏️ 综合练习

以下题目综合了 索引概念 和 MySQL 操作。先想答案，再点选项验证。

🛠️ 实操任务（课后练习）

以下任务请在自己的 MySQL 环境中实际执行，并用 SHOW INDEX FROM 表名 验证。

CREATE INDEX idx_age
  ON s(age ASC);

-- 验证
SHOW INDEX FROM s;

ALTER TABLE c
  ADD UNIQUE INDEX uk_cn(cn),
  ADD INDEX idx_ct(ct);

SHOW INDEX FROM c;

-- ① 创建复合索引
CREATE INDEX idx_sno_cno ON sc(sno, cno);

-- ② 查看
SHOW INDEX FROM sc;

-- ④a 用 DROP INDEX 语法删除
DROP INDEX idx_sno_cno ON sc;

-- 重新创建一次
CREATE INDEX idx_sno_cno ON sc(sno, cno);

-- ④b 用 ALTER TABLE 语法删除
ALTER TABLE sc
  DROP INDEX idx_sno_cno;