一、为什么要创建索引（Index）？

索引的作用：
索引就像一本书的“目录”，让数据库在查询数据时可以“快速定位”记录，而不是全表扫描。

✅ 优点：

• 大大提高数据检索速度

• 加快表之间的连接（join）

• 在分组（GROUP BY）、排序（ORDER BY）时也能提高效率

❌ 缺点：

• 索引需要占用额外的磁盘空间

• 插入、删除、更新数据时，索引也要维护，可能会变慢

---

二、什么时候需要创建索引

可以简单理解为一句话：

“凡是经常出现在 WHERE、JOIN、ORDER BY、GROUP BY 的列，都值得考虑建立索引。”

 具体场景：

1. 主键和唯一键

   • 主键会自动创建唯一索引。

   • 唯一键（UNIQUE）可以保证数据唯一性。

2. 经常用作查询条件的字段

   SELECT * FROM user WHERE email = 'test@xupt.edu.cn';
   

    应给 email 创建索引。

3. 用于排序的字段

   SELECT * FROM article ORDER BY create_time DESC;
   

    给 create_time 建索引可避免文件排序。

4. 用于分组统计的字段

   SELECT department, COUNT(*) FROM employee GROUP BY department;
   

    department 列可建索引。

5. 多表连接时用作连接条件的字段

   SELECT * FROM student s JOIN score c ON s.id = c.student_id;
   

    student.id 与 score.student_id 都应建索引。

---

三、什么时候不需要创建索引

索引不是越多越好，错误的索引会拖慢性能。

 不建议建索引的情况：

1. 数据量很小的表

   • 比如只有几百行，MySQL 直接全表扫描更快。

2. 频繁更新的字段

   • 每次更新都要维护索引，会拖慢性能。

3. 高重复度的字段

   • 如“性别”、“是否删除（0/1）”，区分度太低。

   • 索引选择性低，优化效果几乎没有。

4. 查询中很少用到的字段

   • 从不出现在 WHERE、ORDER BY、GROUP BY 中的列没必要建索引。

---

四、索引优化方法

接下来讲几个常见的索引优化技巧，在面试中也经常被问到

---

1️⃣ 前缀索引优化（Prefix Index）

当字符串字段太长（如邮箱、URL、UUID）时，索引整个字段会浪费空间。
可以只取前几个字符建立索引。

ALTER TABLE user ADD INDEX idx_email (email(10));

 优点：

• 占用空间小，比较速度快。

 缺点：

• 可能降低“区分度”，MySQL 仍可能要回表验证。

 实战建议：
用 SELECT COUNT(DISTINCT LEFT(email, N)) / COUNT(*) 来测试前缀长度的区分度。

---

2️⃣ 覆盖索引优化（Covering Index）

“覆盖索引”指的是查询的字段都在索引中，不用回表读取数据。

-- 索引包含 (id, name)
SELECT id, name FROM student WHERE id = 1;

 优点：

• 避免回表（减少IO），查询更快。

 优化手段：
只查需要的字段；用 EXPLAIN 查看 Extra 字段中是否有 Using index。

---

3️⃣ 主键索引自增优化（Auto Increment Primary Key）

InnoDB 的主键索引是聚簇索引（Clustered Index），数据按照主键顺序存储。
如果主键是随机的（比如 UUID），容易导致数据页分裂、碎片多、写入慢。

 建议：

id BIGINT AUTO_INCREMENT PRIMARY KEY

 自增主键能保证数据顺序插入，性能最优。

---

4️⃣ 索引列设置为 NOT NULL

MySQL 在处理 NULL 时无法高效使用索引（特别是范围查询、排序时）。

 建议：

• 避免在索引列中使用 NULL。

• 可以设置默认值，比如 DEFAULT '' 或 DEFAULT 0。

---

5️⃣ 防止索引失效的情况

索引建立了 ≠ 一定会被用到。
常见的“索引失效”场景如下

场景	例子	原因
使用 OR	WHERE a=1 OR b=2	可能导致全表扫描
函数操作	WHERE YEAR(create_time)=2025	函数操作让索引失效
模糊匹配 % 开头	LIKE '%abc'	无法利用前缀索引
隐式类型转换	WHERE num = '123'	类型不同会触发转换
复合索引未遵守“最左前缀原则”	索引(a,b,c)，只用 b 查询	无法使用索引

 解决方法：

• 保持字段类型一致

• 避免对索引列进行计算或函数操作

• 对字符串匹配尽量使用前缀匹配 LIKE 'abc%'

• 使用复合索引时遵守“最左前缀原则”

---

五、总结速记表

类型	原则/技巧	说明
创建索引	WHERE / JOIN / ORDER / GROUP字段	提高查询效率
不建索引	小表 / 高频更新 / 低区分度字段	节省资源
前缀索引	email(10)	节省空间，需测试区分度
覆盖索引	查询字段全在索引中	避免回表
主键自增	AUTO_INCREMENT	防止页分裂
NOT NULL	索引列不应为NULL	提高效率
索引失效	函数、OR、%abc、类型不匹配	避免这些写法