索引与查询优化入门

🎯 引言

这是 MySQL 课程的收尾篇。学完这篇文章，你能说清楚索引是什么、它为什么能加快查询，会给表创建和查看索引，能用 EXPLAIN 判断一条 SQL 有没有用上索引，还能说出哪些字段适合加索引、哪些不适合，以及几条常用的查询优化建议。

🛠 准备演示数据

老规矩，先把 user 表建好，插入几条演示数据。本文使用前面课程已经跑起来的 mysql8 容器（MySQL 8.x），通过 docker exec -it mysql8 mysql -uroot -p123456 进入命令行后执行：

CREATE DATABASE IF NOT EXISTS blog_demo;
USE blog_demo;

CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    age INT,
    city VARCHAR(50),
    created_at DATETIME
);

INSERT INTO user (username, age, city, created_at) VALUES
    ('zhangsan', 20, '北京', NOW()),
    ('lisi', 25, '上海', NOW()),
    ('wangwu', 30, '北京', NOW()),
    ('zhaoliu', 22, '广州', NOW()),
    ('sunqi', 28, '上海', NOW());

插入完成后，user 表里的数据如下：

created_at 用的是 NOW()，实际值是你执行插入时的时间，同一个 INSERT 语句里所有行的时间相同。本文后面的查询结果都以这份数据为准。

🧱 索引是什么

索引是帮助 MySQL 快速定位数据的一种数据结构，作用是避免每次查询都把整张表从头翻到尾。

可以拿查字典打比方：字典里的字按拼音排了序，还有拼音检字表。没有这个检字表，你要找某个字只能一页一页翻完整个字典；有了检字表，先查拼音找到页码，直接翻过去就行。

对应到 MySQL：

• 一页页翻完整个字典：全表扫描，MySQL 把表里每一行都读出来逐个比对，数据越多越慢。
• 通过检字表直接定位：走索引，MySQL 借助索引快速找到目标行，不用扫描全表。

MySQL 默认的 InnoDB 引擎把索引组织成一种叫 B+ 树的结构，查找效率很高。入门阶段不用深入它的原理，先记住「索引让查询不用扫描全表」就够了。

✨ 主键自带索引

建表时声明的 PRIMARY KEY 不需要额外处理，MySQL 会自动为主键创建索引。所以按 id 查询天生就是快的：

SELECT * FROM user WHERE id = 1;

执行结果如下：

这类按主键等值查询是数据库里常见的高效写法。而其他普通字段，比如 username，默认是没有索引的，需要我们手动创建。

🛠 创建和查看索引

给 user 表的 username 字段创建一个索引：

CREATE INDEX idx_username ON user (username);

• idx_username 是索引名，习惯上用 idx_ 开头加上字段名。
• ON user (username) 指定给哪张表的哪个字段建索引。

查看一张表上有哪些索引：

SHOW INDEX FROM user;

结果里能看到两条：一条是主键自带的 PRIMARY，另一条是刚创建的 idx_username。关键几列如下：

如果某个索引不再需要，可以删掉它：

DROP INDEX idx_username ON user;

🧰 用 EXPLAIN 分析查询

光说「索引用上了」不算数，MySQL 提供了 EXPLAIN 命令，可以看到一条 SQL 的实际执行方式。用法很简单，在 SELECT 语句前加上 EXPLAIN：

EXPLAIN SELECT * FROM user WHERE username = 'zhangsan';

输出的关键几列如下：

输出是一张表，列比较多，入门阶段只看两列就够：

• key：这次查询实际使用的索引。显示 idx_username 说明用上了索引；显示 NULL 说明没走索引。
• type：访问方式。ALL 表示全表扫描，需要警惕；ref、range 等表示用上了索引；按主键等值查询会显示 const，属于高效的访问方式。

对比一下没走索引的查询：

EXPLAIN SELECT * FROM user WHERE city = '北京';

输出的关键几列如下：

city 还没建索引，这条输出的 key 是 NULL、type 是 ALL，也就是全表扫描，rows 显示要检查 5 行，正好是表里的全部数据。

⚖️ 索引的代价

索引不是免费的，它有两方面的代价：

• 占用存储空间：索引本身也是一份数据，要实实在在存在磁盘上。表越大、索引越多，占的空间越多。
• 拖慢写操作：执行 INSERT、UPDATE、DELETE 时，MySQL 除了改数据，还要同步更新相关的所有索引，索引越多写入越慢。

还是用字典打比方：每新增一个字，正文要加一页，检字表也要同步加一条。检字表维护得越复杂，往后每次加字的工作量就越大。

所以结论是：索引不是越多越好，只给真正高频的查询条件加。

💡 常见优化建议

结合前面的知识，整理几条常用的查询优化建议。

1. 避免 SELECT *，只查需要的列

-- 不推荐：把所有列都查出来
SELECT * FROM user WHERE city = '北京';

-- 推荐：只查需要的列，减少数据传输量
SELECT id, username FROM user WHERE city = '北京';

第一条 SELECT * 的执行结果如下：

第二条只查 id 和 username，执行结果如下：

两条 SQL 查到的行是一样的，区别在返回的列数：不需要的列查出来只会白白增加数据传输量。

2. 常出现在 WHERE 里的列考虑加索引

如果接口经常按 city 筛选用户，就值得给它建索引；一年查不了几次的字段，加了反而是负担。

3. LIKE 以 % 开头走不了索引

-- 能用上索引：按开头匹配
SELECT * FROM user WHERE username LIKE 'zhang%';

-- 走不了索引：以 % 开头，MySQL 无法按顺序定位
SELECT * FROM user WHERE username LIKE '%san';

第一条按开头匹配，执行结果如下：

第二条以 % 开头，执行结果如下：

两条 SQL 查到的数据相同，区别只在执行方式：第一条能用上 idx_username 索引，第二条只能全表扫描。

原因用字典类比很好理解：检字表是按拼音开头组织的，你知道开头就能快速定位；但「找出所有拼音里包含 san 的字」没法利用这个顺序，只能全翻一遍。

4. 联合索引遵循最左前缀原则

一个索引可以包含多个字段，这叫联合索引：

CREATE INDEX idx_city_age ON user (city, age);

它像一本先按城市、再按年龄排序的电话簿，查询要从左侧的字段开始连续匹配才能用上：

-- 能用上：匹配了左侧的 city
SELECT * FROM user WHERE city = '北京';

-- 能用上：city 和 age 连续匹配
SELECT * FROM user WHERE city = '北京' AND age = 20;

-- 走不了索引：跳过了 city，只查 age
SELECT * FROM user WHERE age = 20;

三条 SQL 的执行结果依次如下。第一条按 city 查询：

第二条按 city 和 age 一起查询：

第三条只按 age 查询：

可以看到三条 SQL 都能查到正确的数据，走不走索引影响的是查找方式和速度，不影响结果的对错。

⚡ 哪些字段适合建索引

适合建索引的字段：

• 高频查询条件：经常出现在 WHERE 里的字段，比如用户名、手机号。
• 常用于排序、关联的字段：比如 ORDER BY 的字段、多表关联用的外键字段。

不适合建索引的字段：

• 区分度低的字段：比如性别，只有两三个取值，索引过滤不掉多少数据，帮助有限。
• 频繁更新的字段：每次更新都要同步维护索引，字段更新越频繁，索引的维护成本越高。

🧾 小节总结

• 索引是帮助 MySQL 快速定位数据的数据结构，作用类似字典的检字表，能避免全表扫描。
• 主键自动带索引，其他字段用 CREATE INDEX 索引名 ON 表名 (字段名) 创建，SHOW INDEX FROM 表名 查看。
• 用 EXPLAIN 分析 SQL，入门阶段重点看 key（用到的索引）和 type（ALL 是全表扫描）两列。
• 索引有代价：占存储空间、拖慢写入，只给高频查询条件加。
• LIKE 以 % 开头走不了索引；联合索引要从左侧字段开始连续匹配（最左前缀原则）。
• 区分度低、频繁更新的字段不适合建索引；查询时避免 SELECT *，只取需要的列。

❓ 知识问答

Q1：表里只有几条数据，还需要关心索引吗？

数据少的时候全表扫描也很快，索引的优势要在大量数据下才明显。学习阶段先掌握「创建索引、用 EXPLAIN 验证」的方法，数据量大了自然用得上。

Q2：索引是不是加得越多越好？

不是。每个索引都占存储空间，并且 INSERT、UPDATE、DELETE 时都要同步维护。索引加得太多，查询快了一点，写入却明显变慢，需要权衡。

Q3：主键索引和自己创建的索引有什么区别？

主键索引是声明 PRIMARY KEY 时自动创建的，一张表只能有一个主键；普通索引用 CREATE INDEX 手动创建，一张表可以有多个，用于加速主键以外的查询条件。

Q4：为什么 LIKE '%san' 走不了索引？

索引是按字段值的顺序组织的，'zhang%' 这种知道开头的匹配可以按顺序定位；'%san' 开头不确定，任何位置都可能命中，MySQL 无法利用顺序，只能全表扫描。

Q5：EXPLAIN 的 type 列出现 ALL 怎么办？

说明这条查询在做全表扫描。先检查 WHERE 里的字段有没有索引，没有的话评估这个查询的使用频率，高频就给它建一个索引，建完再 EXPLAIN 验证一次。

🧪 小练习

练习 1：给 user 表的 city 字段创建索引，然后用 EXPLAIN 验证下面这条 SQL 用上了索引（观察 key 列），再对比一条按 age 查询的 SQL，看看没建索引时 key 和 type 是什么。

-- 请在这里编写 SQL：创建 city 索引

-- 请在这里编写 SQL：EXPLAIN 按 city 查询

-- 请在这里编写 SQL：EXPLAIN 按 age 查询

练习 2：创建 (city, age) 联合索引，用 EXPLAIN 分别验证三条 SQL：只按 city 查、按 city 和 age 一起查、只按 age 查，观察哪一条的 key 列是 NULL，想想为什么。

-- 请在这里编写 SQL

🎉 恭喜你已经掌握索引与查询优化的入门技能啦！到这里，整个 MySQL 课程也学完了：从安装 MySQL、数据库与表的增删改查，到条件查询、数据类型与约束、多表关联，再到在 Node.js 中用 mysql2 操作数据库，以及今天的索引与优化。你已经具备了前端开发者日常用数据库的完整知识，接下来就在自己的项目里多用多练吧！