对于后端开发者而言，MySQL 索引是提升数据库性能的核心手段，也是面试中的高频考点。很多初学者虽然知道“要建索引”，但往往陷入“索引建得越多越好”的误区，甚至建了索引却发现查询速度没提升，反而拖慢了写入性能。今天这篇文章，我们就从零深入拆解索引的核心逻辑、适用场景、优化技巧，结合实际开发中的常见问题，帮你真正吃透索引的高级运用，避开所有坑。

一、为什么要创建索引（Index）？—— 数据库的“目录”有多重要

我们先从最基础的问题入手：为什么需要索引？其实一句话就能理解：索引就是数据库表的“目录”，就像我们看书时，不用逐页翻找，通过目录就能快速定位到目标章节；数据库查询时，有了索引，就不用进行全表扫描（逐行查找数据），能直接定位到符合条件的记录，极大提升查询效率。

✅ 索引的核心优点

  • 提升检索速度：这是最核心的作用，尤其是表中数据量达到万级、十万级甚至百万级时，索引能将查询时间从“秒级”压缩到“毫秒级”。
  • 加速表连接：多表关联查询（JOIN）时，索引能快速匹配两个表中的关联字段，避免两个表都进行全表扫描，减少关联耗时。
  • 优化排序和分组：当查询中包含 ORDER BY（排序）、GROUP BY（分组）时，索引会提前对字段进行排序，避免数据库在查询后再进行“文件排序”，节省大量资源。

❌ 索引的不可忽视的缺点

索引不是“万能的”，它的优点背后也有代价，这也是很多初学者容易忽略的点：

  • 占用额外磁盘空间：索引本身也是一种数据结构（比如B+树），需要单独存储在磁盘上，一张表的索引越多，占用的磁盘空间就越大。
  • 降低写入性能：当执行插入、删除、更新操作时，数据库不仅要修改表中的数据，还要同步维护对应的索引（比如新增数据时，要在索引树中插入新的节点），这会增加写入操作的耗时，索引越多，维护成本越高。

总结：索引的核心是“trade-off（权衡）”—— 用少量的磁盘空间和写入性能损耗，换取查询性能的巨大提升。

二、什么时候需要创建索引？—— 记住这一个核心原则

很多人建索引凭“感觉”，其实只要记住一句话，就能避开80%的误区：凡是经常出现在 WHERE、JOIN、ORDER BY、GROUP BY 中的列，都值得考虑建立索引。

结合实际开发场景，以下5种情况，一定要建索引：

1. 主键和唯一键（必建索引）

MySQL 中，主键（PRIMARY KEY）会自动创建唯一索引，无需手动操作；唯一键（UNIQUE）也会自动创建唯一索引，用于保证字段值的唯一性（比如用户手机号、邮箱）。这两种索引是数据库自带的优化，必须合理使用。

示例：用户表中，id 作为主键，自动生成主键索引；email 作为唯一键，自动生成唯一索引，避免重复注册。

2. 经常用作查询条件的字段（WHERE 后出现的列）

如果一个字段经常出现在 WHERE 子句中，作为查询条件（比如根据邮箱查用户、根据商品ID查商品），那么给这个字段建索引，能直接定位到符合条件的记录，避免全表扫描。

示例：

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

分析：email 是频繁查询的条件，给 email 建立索引，查询时能直接定位到该邮箱对应的用户记录，效率提升明显。

3. 用于排序的字段（ORDER BY 后出现的列）

当查询需要对某个字段进行排序（比如按文章发布时间降序、按商品价格升序），给该字段建索引，能让数据库直接利用索引的有序性进行排序，避免查询后再进行“文件排序”（文件排序耗时极长，尤其是数据量较大时）。

示例：

1
SELECT * FROM article ORDER BY create_time DESC;

分析：create_time 是排序字段，给它建索引后，数据库会直接按照索引的顺序返回数据，无需额外排序，查询速度会大幅提升。

4. 用于分组统计的字段（GROUP BY 后出现的列）

当需要对某个字段进行分组统计（比如统计每个部门的员工数量、每个分类的商品数量），给该字段建索引，能让数据库快速分组，减少分组时的计算耗时。

示例：

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

分析：department 是分组字段，给它建索引后，数据库能快速按照部门分组，统计每个部门的员工数量，避免全表扫描后再分组。

5. 多表连接时用作连接条件的字段（JOIN 关联字段）

多表关联查询（比如学生表和成绩表、订单表和用户表）时，连接条件中的字段（通常是主键和外键）必须建索引，否则会导致两个表都进行全表扫描，关联效率极低。

示例：

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

分析：s.id（学生表主键，自动有索引）和 c.student_id（成绩表外键），需要给 c.student_id 建索引，这样关联时，能通过索引快速匹配学生和成绩，避免全表扫描。

三、什么时候不需要创建索引？—— 索引越多，性能越差

很多初学者觉得“索引建得越多越好”，其实恰恰相反：错误的索引不仅不能提升性能，还会拖慢数据库的写入速度、浪费磁盘空间。以下5种情况，坚决不建议建索引：

1. 数据量很小的表（几百行以内）

如果表中只有几百行数据，MySQL 进行全表扫描的时间非常短（毫秒级），此时建索引反而会浪费磁盘空间，而且查询时，数据库可能会直接选择全表扫描（因为索引查询也需要消耗资源），索引相当于“无用功”。

2. 频繁更新的字段

如果一个字段经常被更新（比如用户的登录时间、商品的库存），那么给这个字段建索引，每次更新时，数据库都要同步维护索引，会大幅增加写入耗时。比如商品库存，每秒可能有多次更新，建索引会严重影响更新性能。

3. 高重复度的字段（区分度极低）

索引的效果取决于字段的“区分度”（即字段中不同值的比例），区分度越低，索引的作用越小。比如“性别”（男/女）、“是否删除”（0/1）、“状态”（启用/禁用）这类字段，重复度极高，区分度很低，即使建了索引，数据库也可能选择全表扫描，优化效果几乎为零，还会浪费磁盘空间。

小技巧：判断字段是否适合建索引，可以计算“区分度”= 不同值的数量 / 总记录数，区分度低于0.1的字段，不建议建索引。

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

如果一个字段几乎不会出现在 WHERE、JOIN、ORDER BY、GROUP BY 中，那么给它建索引完全没有意义，只是单纯浪费磁盘空间和维护成本。比如用户表中的“备注”字段，很少用于查询，就没必要建索引。

5. 临时表、一次性查询的表

如果是临时创建的表，用于一次性查询，查询完成后就删除，那么建索引完全是多余的，只会增加表的创建时间和磁盘占用。

四、索引优化方法—— 面试高频+实战必备

掌握了索引的创建和不创建场景，接下来就是核心的索引优化技巧。这些技巧不仅能提升实际开发中的数据库性能，也是面试中经常被问到的考点，建议重点掌握。

1️⃣ 前缀索引优化（Prefix Index）—— 解决长字符串索引浪费空间问题

实际开发中，我们经常会遇到长字符串字段（比如邮箱、URL、UUID），如果给整个字段建索引，会占用大量的磁盘空间（比如一个邮箱字段，平均长度20个字符，建索引后，每个索引节点都会存储这20个字符，数据量大会非常浪费）。

前缀索引的核心：只取长字符串的前N个字符建立索引，既能节省磁盘空间，又能保证一定的区分度，实现高效查询。

示例（给邮箱字段建前缀索引）：

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

说明：取 email 的前10个字符建索引，既能节省空间，又能通过前10个字符快速定位到大部分符合条件的记录，再回表验证完整邮箱即可。

前缀索引的优缺点

  • 优点：占用磁盘空间小，索引查询速度快，适合长字符串字段。
  • 缺点：可能降低字段的区分度（比如两个邮箱前10个字符相同，就需要回表验证），无法用于 ORDER BY 和 GROUP BY 操作。

实战建议

前缀长度N的选择是关键，既要节省空间，又要保证区分度。可以通过以下SQL语句测试前缀长度的区分度：

1
2
3
4
5
-- 测试不同前缀长度的区分度，接近1最好
SELECT COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) AS rate8,
       COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS rate10,
       COUNT(DISTINCT LEFT(email, 12)) / COUNT(*) AS rate12
FROM user;

一般情况下，前缀长度选择能让区分度达到0.9以上的最小N即可。

2️⃣ 覆盖索引优化（Covering Index）—— 避免回表，提升查询速度

很多人不知道，即使建了索引，查询时也可能存在“回表”操作：索引中只存储了索引列的值，而查询需要的字段（比如姓名、年龄）不在索引中，此时数据库会先通过索引找到对应的主键，再通过主键去表中查询完整数据，这个过程就是“回表”，会增加IO操作，降低查询速度。

覆盖索引的核心：查询的所有字段，都包含在索引中，无需回表，直接从索引中获取所有需要的数据，大幅提升查询效率。

示例：

1
2
3
4
-- 给 student 表建立索引 (id, name)
ALTER TABLE student ADD INDEX idx_id_name (id, name);
-- 查询的 id 和 name 都在索引中，无需回表
SELECT id, name FROM student WHERE id = 1;

说明：索引 idx_id_name 包含了 id 和 name 两个字段，查询时直接从索引中获取这两个字段的值，不需要回表查询，速度极快。

覆盖索引的实战技巧

  • 查询时，只查需要的字段，不要用 SELECT *（SELECT * 会查询所有字段，大概率需要回表）。
  • 用 EXPLAIN 分析查询语句，查看 Extra 字段，如果出现“Using index”，说明使用了覆盖索引，无需回表。

3️⃣ 主键索引自增优化（Auto Increment Primary Key）—— 避免数据页分裂

MySQL 的 InnoDB 存储引擎中，主键索引是“聚簇索引”（Clustered Index），聚簇索引的特点是：数据按照主键的顺序存储在磁盘上，索引和数据是绑定在一起的。

如果主键是随机值（比如 UUID、随机字符串），会导致以下问题：

  • 数据插入时，无法按照顺序插入，需要频繁调整数据页的顺序，导致数据页分裂。
  • 数据页分裂会产生大量碎片，占用更多磁盘空间，同时降低写入和查询性能。

实战建议：主键必须使用自增主键，格式如下：

1
id BIGINT AUTO_INCREMENT PRIMARY KEY;

自增主键的优势：数据会按照主键顺序依次插入，不会产生数据页分裂，碎片少，写入性能最优，同时查询时也能通过自增主键快速定位。

注意：不要用 INT 类型作为主键（INT 最大只能存储2147483647条数据），建议用 BIGINT 类型，避免数据量过大时主键溢出。

4️⃣ 索引列设置为 NOT NULL —— 避免索引失效

MySQL 在处理 NULL 值时，无法高效使用索引，尤其是在范围查询（比如 >、<、BETWEEN）和排序时，NULL 值会被当作特殊值处理，导致索引失效，只能进行全表扫描。

实战建议：

  • 避免在索引列中使用 NULL 值。
  • 如果字段确实可能为空，可以设置默认值（比如字符串字段默认空字符串 DEFAULT ‘’，数值字段默认 0），确保索引列不会出现 NULL。

5️⃣ 防止索引失效 —— 避开这些常见坑

最让人头疼的问题：索引建好了，但查询时却没被用到，这就是“索引失效”。以下是6种最常见的索引失效场景，一定要避开，同时记住对应的解决方法。

索引失效场景 示例 失效原因 解决方法
使用 OR 连接非索引列 WHERE a=1 OR b=2（a有索引，b无索引） OR 连接的字段中，只要有一个字段没有索引，就会导致全表扫描 给 OR 连接的所有字段都建索引；或用 UNION 替代 OR
对索引列进行函数操作 WHERE YEAR(create_time)=2025（create_time有索引） 函数操作会改变索引列的值，导致数据库无法利用索引 避免对索引列进行函数操作，改为 WHERE create_time BETWEEN ‘2025-01-01’ AND ‘2025-12-31’
模糊匹配以 % 开头 WHERE username LIKE ‘%abc’（username有索引） % 开头的模糊匹配，无法利用前缀索引，只能全表扫描 尽量用前缀匹配（LIKE ‘abc%’）；如果必须用 % 开头，可考虑全文索引
隐式类型转换 WHERE num = ‘123’（num是INT类型，有索引） 字符串和数值类型不匹配，MySQL会自动进行类型转换，导致索引失效 保持查询条件和字段类型一致，改为 WHERE num = 123
复合索引未遵守“最左前缀原则” 索引(a,b,c)，查询 WHERE b=2 AND c=3 复合索引的查询，必须从最左边的列开始，否则无法使用索引 查询时包含复合索引最左边的列；或调整索引顺序，将常用字段放在左边
使用 NOT IN / NOT EXISTS WHERE id NOT IN (1,2,3)（id有索引） NOT IN / NOT EXISTS 会导致索引失效，触发全表扫描 用 LEFT JOIN … IS NULL 替代 NOT IN；或用 EXISTS 替代 NOT EXISTS

五、总结速记表 —— 快速回顾核心要点

为了方便大家记忆和后续查阅，整理了一份核心速记表，涵盖索引的创建原则、优化技巧和避坑点，建议收藏：

类型 原则/技巧 说明
创建索引 WHERE / JOIN / ORDER / GROUP 字段 核心原则，提升查询效率
不建索引 小表 / 高频更新 / 低区分度 / 少用字段 节省磁盘空间，避免拖慢写入性能
前缀索引 email(10)（取前N个字符） 节省空间，需测试区分度（接近1最佳）
覆盖索引 查询字段全在索引中 避免回表，减少IO，查询更快
主键自增 AUTO_INCREMENT 防止数据页分裂，写入性能最优
NOT NULL 索引列不应为NULL 避免索引失效，提升查询效率
索引失效 函数、OR、%abc、类型不匹配等 避开这些场景，确保索引被正常使用

六、最后总结

索引是MySQL性能优化的核心，掌握它的关键不在于“建多少索引”，而在于“建对索引”。记住以下3点，就能轻松应对大部分开发和面试场景：

  • 权衡利弊：索引是“查询快、写入慢”的 trade-off，根据业务场景选择是否建索引。
  • 抓准场景：常出现在 WHERE、JOIN、ORDER、GROUP 中的列建索引，反之不建。
  • 避开坑点：记住常见的索引失效场景，合理使用前缀索引、覆盖索引、自增主键，最大化索引价值。

下一章，我们将结合具体的SQL案例，实战演练索引优化的全过程，教你如何用 EXPLAIN 分析索引使用情况，解决实际开发中的性能问题，记得持续关注哦！