跳到主要内容

4.3 索引入门：让查询快起来的"目录"

📖 本节导读​

  • 理解什么是索引：数据库里的"书本目录"，让查询不再逐行翻找。
  • 掌握索引的增删查：CREATE INDEX、ALTER TABLE ADD INDEX、SHOW INDEX、DROP INDEX。
  • 认识唯一索引、联合索引和"最左前缀原则"。
  • 学会用 EXPLAIN 判断一条查询"有没有用上索引"，并记住哪些写法会让索引失效。

一、什么是索引？​

想象你手里有一本 1000 页的《新华字典》，要查"魏"字：

  • 没有目录：从第 1 页开始一页页翻，翻到为止——最坏要翻 1000 页；
  • 有拼音检索：先查"wei"，直接定位到第 683 页，翻一次就到。

索引（Index） 就是数据库给表建的"目录"。它是一份按某列的值排好序的额外数据结构，记录着"值 → 这行数据在哪"的对应关系。查询时先查索引、再直达数据，而不是把整张表从头翻到尾。

为什么快？​

没有索引时，MySQL 只能全表扫描（一行一行地看，行话叫 ALL）。表小无所谓，表一旦有几百万行，每次查询都全表扫描就是灾难。

有了索引，MySQL 用一种叫 B+ 树 的结构存储索引——你只需概念性地知道：它像一棵层层分叉的"查号树"，几百万行的表通常翻 3~4 层就能定位到目标，而不是翻几百万次。B+ 树内部细节新手阶段不必深究。

索引的代价​

天下没有免费的午餐，索引有两笔开销：

  1. 占磁盘空间：目录本身也要占页数，每个索引都是一份额外的数据。
  2. 拖慢写入：每次 INSERT / UPDATE / DELETE，除了改数据，还得同步维护每一个索引（目录也得跟着改）。索引越多，写入越慢。

所以索引不是越多越好——用查得多的列换取加速，别给不查的列白白建目录。

主键自带索引​

好消息：PRIMARY KEY 会自动创建索引（而且是效率最高的那种，叫聚簇索引/主键索引），UNIQUE 约束同样自动附带一个唯一索引。所以 WHERE id = 3 这种按主键查询的语句，天生就是飞快的，不需要你再做任何事。

二、索引的增删查​

以下示例都基于 school 库。假设我们经常按姓名查学生：SELECT * FROM student WHERE name = '王小红';，就值得给 student.name 建个索引。

2.1 创建索引的三种写法​

写法一：CREATE INDEX（最常用）

CREATE INDEX idx_student_name ON student(name);

idx_student_name 是索引名，建议按 idx_表名_列名 的习惯命名，一眼能看懂。

写法二：ALTER TABLE ADD INDEX（效果完全相同）

ALTER TABLE student ADD INDEX idx_student_name (name);

写法三：建表时直接定义

CREATE TABLE student (
    id       INT PRIMARY KEY AUTO_INCREMENT,
    name     VARCHAR(50) NOT NULL,
    gender   CHAR(1),
    birthday DATE,
    class_id INT,
    INDEX idx_student_name (name)
);

2.2 查看表上有哪些索引​

SHOW INDEX FROM student;
+---------+------------+------------------+--------------+-------------+-----------+-------------+
| Table   | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality |
+---------+------------+------------------+--------------+-------------+-----------+-------------+
| student |          0 | PRIMARY          |            1 | id          | A         |           5 |
| student |          1 | idx_student_name |            1 | name        | A         |           5 |
+---------+------------+------------------+--------------+-------------+-----------+-------------+
2 rows in set (0.01 sec)

（实际输出列更多，这里截取了关键几列。）能看到两条索引：主键自带的 PRIMARY，和我们刚建的 idx_student_name。Non_unique = 0 表示唯一索引，= 1 表示普通索引。

2.3 删除索引​

DROP INDEX idx_student_name ON student;
-- 或者等价写法：
ALTER TABLE student DROP INDEX idx_student_name;

2.4 唯一索引​

在索引前加 UNIQUE，就是唯一索引：既加速查询，又保证该列不重复——它和上一节的 UNIQUE 约束本质是同一个东西。

CREATE UNIQUE INDEX uk_course_name ON course(name);

建好之后再插一门重名课程试试：

INSERT INTO course (name, teacher) VALUES ('数学', '赵老师');
ERROR 1062 (23000): Duplicate entry '数学' for key 'course.uk_course_name'

三、联合索引与最左前缀原则​

索引可以建在多个列上，叫联合索引（也叫复合索引）：

CREATE INDEX idx_score_stu_course ON score(student_id, course_id);

怎么理解它？想象一本按"姓氏拼音、名字拼音"排序的电话簿：先按姓排，同姓的再按名排。

最左前缀原则：联合索引只有从最左边的列开始连续使用，才能生效。用电话簿类比：

  • 查"姓张的人" —— 能用上（按姓排序，直接翻到张）；✅
  • 查"姓张、名三的人" —— 能用上（先定位张，再在张里找三）；✅
  • 只查"名叫'三'的人" —— 用不上！名字的顺序是打散在各个姓氏里的，只能整本翻。❌

对应到 idx_score_stu_course (student_id, course_id)：

查询条件能否用上这个联合索引
WHERE student_id = 3✅ 能（用了最左列）
WHERE student_id = 3 AND course_id = 2✅ 能（从左到右连续）
WHERE course_id = 2❌ 不能（跳过了最左列）

所以建联合索引时，把最常单独查询的列放在最左边。另外，有了 (student_id, course_id) 联合索引，就不必再单独给 student_id 建索引了——联合索引的"最左部分"已经覆盖了它。

四、EXPLAIN 初体验：查询到底用没用索引？​

在 SELECT 前面加上 EXPLAIN，MySQL 不会真正执行查询，而是告诉你它打算怎么执行——这是判断索引是否生效的照妖镜。

先看一个没有索引的查询（假设 name 上的索引已删除）：

EXPLAIN SELECT * FROM student WHERE name = '王小红';
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | student | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

重点看 type 列：ALL 表示全表扫描——没用上任何索引，把 5 行全看了一遍。表小的时候感觉不到，表大了就是性能杀手。

现在建上索引再看：

CREATE INDEX idx_student_name ON student(name);
EXPLAIN SELECT * FROM student WHERE name = '王小红';
+----+-------------+---------+------+------------------+------------------+---------+-------+------+-------+
| id | select_type | table   | type | possible_keys    | key              | key_len | ref   | rows | Extra |
+----+-------------+---------+------+------------------+------------------+---------+-------+------+-------+
|  1 | SIMPLE      | student | ref  | idx_student_name | idx_student_name | 202     | const |    1 | NULL  |
+----+-------------+---------+------+------------------+------------------+---------+-------+------+-------+

变化很明显：

  • type 从 ALL 变成了 ref——通过索引定位，只扫了 1 行（rows 列从 5 变 1）；
  • key 列显示实际用上的索引是 idx_student_name。

新手阶段掌握到这个程度就够了：看 type 列，ALL = 全表扫描（没用上索引），ref / const / range 等 = 用上了索引。至于每种 type 的细微差别，以后进阶再学。

五、什么时候该建索引？（经验清单）​

适合建索引的列：

  1. 经常出现在 WHERE 条件里的列（如 student.name）；
  2. 经常用来 JOIN 连表的列（如 score.student_id）；
  3. 经常用来 ORDER BY 排序的列；
  4. 值的区分度高的列（比如姓名、手机号，几乎每行都不同）。

不适合建索引的情况：

  1. 表很小（几十几百行，全表扫描本来就快，目录反而多余）；
  2. 列的区分度太低（如 gender 只有"男/女"两种值，用索引也要扫一半表，意义不大）；
  3. 频繁被 UPDATE 的列（每次改值都得维护索引，写入变慢）；
  4. 几乎从不出现在查询条件里的列（纯浪费空间）。

一句话总结：读多、区分度高的列建索引；写多、区分度低、用不上的列不建。

六、常见索引失效场景​

建了索引不等于一定用得上。以下写法会让 MySQL 放弃索引、退回全表扫描（type 变回 ALL）：

1. 对索引列使用函数或运算

-- ❌ 失效：对 birthday 套了函数，索引里存的是原始日期，不是"年份"
EXPLAIN SELECT * FROM student WHERE YEAR(birthday) = 2005;

-- ✅ 改写成范围查询，索引可用
EXPLAIN SELECT * FROM student
WHERE birthday BETWEEN '2005-01-01' AND '2005-12-31';

类比：目录按"日期"排序，你却要按"年份计算结果"找，目录帮不上忙。

2. LIKE 以 % 开头

-- ❌ 失效：开头是通配符，相当于"查所有名字以'红'结尾的人"——拼音检索没法用
EXPLAIN SELECT * FROM student WHERE name LIKE '%红';

-- ✅ 前缀匹配可以用上索引："以'王'开头"能直接在目录里定位
EXPLAIN SELECT * FROM student WHERE name LIKE '王%';

3. 类型不匹配（隐式转换）

-- 假设 phone 列是 VARCHAR 类型且建了索引
-- ❌ 失效：拿数字比字符串，MySQL 被迫对每行的 phone 做类型转换，等于套了函数
SELECT * FROM member WHERE phone = 13800000001;

-- ✅ 类型对上，索引可用
SELECT * FROM member WHERE phone = '13800000001';

遇到"明明建了索引却很慢"的情况，第一反应就是 EXPLAIN 一下，看 type 是不是 ALL，再对照上面三条排查。

⚠️ 新手常见坑​

  1. 以为索引越多越好，给每一列都建一个。索引占空间、拖慢写入，只给真正需要的列建。
  2. 建了索引就以为万事大吉，从不用 EXPLAIN 验证。函数、LIKE '%xx'、类型不匹配都会让索引悄悄失效——它不报错，只是默默变慢。
  3. 联合索引忽略最左前缀。建了 (student_id, course_id) 却只用 WHERE course_id = 2 查询，索引用不上。
  4. 给 gender 这种低区分度的列建索引，收益几乎为零。
  5. 在小表上纠结索引性能。school 库每张表只有几行，EXPLAIN 有时会直接选择全表扫描（因为真的更快），这不代表你的索引写错了。索引的威力要在大表上才明显。
  6. 忘了主键已经自带索引，又给 id 手动建一个，纯属重复浪费。

📝 小结​

操作语句
创建普通索引CREATE INDEX idx_名 ON 表(列); 或 ALTER TABLE 表 ADD INDEX idx_名 (列);
创建唯一索引CREATE UNIQUE INDEX uk_名 ON 表(列);
创建联合索引CREATE INDEX idx_名 ON 表(列1, 列2);
查看索引SHOW INDEX FROM 表;
删除索引DROP INDEX idx_名 ON 表;
验证是否用上索引EXPLAIN SELECT ...; 看 type 列（ALL = 没用上）
  • 索引是"目录"：以空间和写入速度为代价，换取查询速度，底层是 B+ 树。
  • 主键和 UNIQUE 约束自动带索引，不用重复建。
  • 联合索引遵循最左前缀原则：从最左列开始连续使用才生效。
  • 三大失效场景：对列用函数/运算、LIKE '%xx' 开头、类型不匹配。

✍️ 练习题​

1. 我们经常执行 SELECT * FROM score WHERE course_id = 3; 来查某门课的所有成绩。请写出给相应列创建索引的语句（两种写法各写一遍），再写出查看和删除该索引的语句。

参考答案
-- 写法一
CREATE INDEX idx_score_course ON score(course_id);
-- 写法二（与写法一等价，二选一即可）
ALTER TABLE score ADD INDEX idx_score_course (course_id);

-- 查看
SHOW INDEX FROM score;

-- 删除
DROP INDEX idx_score_course ON score;

2. 判断题：student 表的 id 是主键，为了加快 WHERE id = 3 的查询，应该再执行 CREATE INDEX idx_id ON student(id);。

参考答案

错。主键自动创建索引，WHERE id = 3 天生就走索引，再建一个 idx_id 属于重复索引，只会白占空间、拖慢写入。

3. score 表上有联合索引 idx_score_stu_course (student_id, course_id)。下面哪些查询能用上它？

  • A. SELECT * FROM score WHERE student_id = 5;
  • B. SELECT * FROM score WHERE course_id = 3;
  • C. SELECT * FROM score WHERE student_id = 5 AND course_id = 3;
参考答案

A 和 C 能用上，B 不能。根据最左前缀原则，联合索引必须从最左列 student_id 开始使用：A 用了最左列，C 从左到右连续用了两列，都生效；B 跳过了 student_id 只查 course_id，就像在按姓氏排序的电话簿里只按名字找人，索引帮不上忙。

4. student.name 上已有索引 idx_student_name。执行 EXPLAIN SELECT * FROM student WHERE name LIKE '%小红';，type 列大概率会显示什么？为什么？怎样改写能让索引生效（需求变为：查姓王的学生）？

参考答案

type 大概率显示 ALL（全表扫描）。因为 LIKE '%小红' 以 % 开头，索引是按 name 从头排序的，"以某某结尾"没法利用这个顺序。若需求是查姓王的学生，改成前缀匹配即可用上索引：

EXPLAIN SELECT * FROM student WHERE name LIKE '王%';

5. 思考题：为什么不建议给 student.gender 列建索引？请用"区分度"的概念解释。

参考答案

gender 只有"男/女"两种值，区分度极低。student 表 5 个学生里男生 3 人、女生 2 人，按 gender = '男' 走索引也要取出约一半的行，几乎等于全表扫描，还多了一次"先查目录再回表"的开销。索引适合建在姓名、手机号这类几乎每行取值都不同（区分度高）的列上，MySQL 遇到低区分度的列时甚至会主动放弃使用索引。