MySQL: MySQL索引基础与创建管理
最后更新：2026-08-26
索引是数据库性能优化的关键——没有索引的查询就像翻字典不看目录。
本课讲解索引的原理、创建和管理。
1. 你将学到
- 什么是索引及为什么需要索引
- B+Tree 索引结构原理
- CREATE INDEX / ALTER TABLE 创建索引
- 查看和删除索引
- 索引的优缺点
2. 真实场景
(1) 痛点：查询太慢
100 万条用户数据，按邮箱查询：
SQL
SELECT * FROM users WHERE email = 'alice@email.com';
-- 执行时间：2.5 秒（全表扫描）
(2) 索引的解法
SQL
CREATE INDEX idx_email ON users(email);
-- 再次查询：0.003 秒（索引查找）
| 维度 | 无索引 | 有索引 |
|---|---|---|
| 查询方式 | 全表扫描 | B+Tree 查找 |
| 100 万行查询 | 2-5 秒 | < 0.01 秒 |
| 写入性能 | 无额外开销 | 略慢（需维护索引） |
3. 索引原理
(1) B+Tree 结构
graph TB
R[Root Node<br/>10 | 30 | 50] --> L1[1-10]
R --> L2[11-30]
R --> L3[31-50]
R --> L4[51+]
L1 --> D1[Data: 1,3,5,7,9]
L1 --> D2[Data: 2,4,6,8,10]
L2 --> D3[Data: 11,15,20]
L2 --> D4[Data: 25,28,30]
L3 --> D5[Data: 31,35,40]
L3 --> D6[Data: 45,48,50]
(2) 索引类型
| 类型 | 说明 | 适用场景 |
|---|---|---|
| B+Tree 索引 | 默认索引类型 | 等值/范围/排序 |
| Hash 索引 | 哈希表 | 等值查询（Memory 引擎） |
| 全文索引 | 文本搜索 | 文章内容搜索 |
| 空间索引 | GIS 数据 | 地理位置查询 |
4. 创建索引
▶ 示例：创建索引
SQL
-- 方法1：CREATE INDEX
CREATE INDEX idx_email ON users(email);
-- 唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);
-- 方法2：ALTER TABLE
ALTER TABLE users ADD INDEX idx_username (username);
-- 组合索引（联合索引）
CREATE INDEX idx_name_email ON users(username, email);
-- 前缀索引
CREATE INDEX idx_email_prefix ON users(email(10));
5. 查看索引
▶ 示例：查看索引信息
SQL
-- 查看表的所有索引
SHOW INDEX FROM users;
-- 查看索引信息
SHOW INDEX FROM users\G
输出：
TEXT
📖 仅展示
+-------+------------+----------+--------------+-------------+
| Table | Key_name | Seq_in_index | Column_name | Index_type |
+-------+------------+--------------+-------------+------------+
| users | PRIMARY | 1 | id | BTREE |
| users | idx_email | 1 | email | BTREE |
+-------+------------+--------------+-------------+------------+
6. 删除索引
▶ 示例：删除索引
SQL
-- 方法1：DROP INDEX
DROP INDEX idx_email ON users;
-- 方法2：ALTER TABLE
ALTER TABLE users DROP INDEX idx_email;
-- 删除主键
ALTER TABLE users DROP PRIMARY KEY;
7. 索引适用场景
| 场景 | 是否建索引 | 原因 |
|---|---|---|
| WHERE 条件字段 | ✅ | 加速查询 |
| JOIN 连接字段 | ✅ | 加速连接 |
| ORDER BY 字段 | ✅ | 避免排序 |
| GROUP BY 字段 | ✅ | 加速分组 |
| 高选择性字段 | ✅ | 区分度高 |
| 频繁更新字段 | ❌ | 维护成本高 |
| 低选择性字段 | ❌ | 如性别（M/F） |
| 数据量小的表 | ❌ | 全表扫描更快 |
❓ 常见问题
Q 索引越多越好？
A 不是。索引占用空间，降低写入性能。只在常用查询字段上建索引。
Q 主键自动有索引？
A 是的。PRIMARY KEY 自动创建聚簇索引，UNIQUE 自动创建唯一索引。
Q 如何判断是否需要索引？
A 用 EXPLAIN 分析查询计划，看是否使用了索引。
Q 索引越多越好吗？
A 不是。索引拖慢写入性能，建议单表不超 5-6 个。
Q 主键一定是聚簇索引吗？
A InnoDB 中是的，主键就是聚簇索引。MyISAM 中主键是非聚簇索引。
📖 小节
- 索引加速查询，但会降低写入性能
- B+Tree 是默认索引结构，支持等值/范围/排序
- CREATE INDEX 创建，DROP INDEX 删除
- 适用：WHERE/JOIN/ORDER BY/GROUP BY 字段
- 不适用：频繁更新、低选择性、小表
📝 作业
-
基础题（难度⭐）：给
users表的email字段创建唯一索引。 -
进阶题（难度⭐⭐）：创建组合索引
(department, salary)，并验证最左前缀原则。 -
挑战题（难度⭐⭐⭐）：用 EXPLAIN 对比有索引和无索引的查询性能差异。
