MySQL索引详解：查询性能优化的核心手段
上一节内容中，我们已经学习了视图、函数和过程的核心用法。本节课，我们将聚焦MySQL查询性能优化的关键——索引，深入拆解索引的原理、用法、实战案例及设计原则，帮大家彻底搞懂“为什么索引能提速”“怎么用索引才高效”，轻松解决开发中“数据量变大，查询变慢”的痛点。
温馨提示：本文所有实操案例均基于MySQL 8.0版本的InnoDB存储引擎，建议大家动手实操演练，更易掌握索引的核心逻辑，避免只记理论不落地。
一、索引是什么？
在MySQL数据库开发中，索引是提升查询性能最关键的手段，没有之一。很多同学在开发中会遇到“数据量达到几万、几十万后，查询速度越来越慢”的问题，核心原因往往就是没有合理使用索引。
我们可以用一个通俗的比喻理解索引：关系型数据库中的索引，就像一本书的目录。试想一下，如果一本厚厚的技术书没有目录，想要找到某个特定知识点，只能逐页翻阅，效率极低；而有了目录，我们可以快速定位知识点的页码，瞬间找到目标内容。
创建索引会占用一定的存储空间（就像目录会占用书籍的部分篇幅），但这种空间上的牺牲，能换来查询速度的质的飞跃——尤其是数据量越大，索引的优势越明显，性价比极高。
补充说明：MySQL数据库中，所有数据类型的列都支持创建索引。InnoDB存储引擎支持三种索引类型：B+树索引、全文索引和R树索引。其中，B+树索引是日常开发中使用最广泛、效率最高的一种，也是我们本节课重点讲解的内容。
二、为什么首选B+树索引？底层原理拆解
为什么B+树索引能成为开发中的“首选”？核心原因是它是目前基于磁盘存储海量数据、实现高效排序和查询的数据结构，完美适配MySQL的存储场景。
B+树是一棵平衡树，其高度通常只有3-4层，但却能存储百万级甚至十亿级的数据；更关键的是，从海量数据中查询一条目标数据，只需执行3-4次I/O操作，效率远超其他索引类型。
1. B+树索引的结构原理
B+树由根节点、中间节点和叶子节点三部分组成，其中叶子节点是存储数据的核心——所有数据都会按顺序保存在叶子节点中。由于数据在索引上是有序排列的，因此在单个叶子节点内查找数据时，可直接使用二分查找，查询效率极高。
当表中数据较少时，B+树只有一个根节点，所有数据都会直接保存在根节点中；随着数据不断增加，B+树会自动分裂，根节点不再存储数据，而是存储指向中间节点的指针，通过这些指针快速定位数据所在的叶子节点，进而找到目标数据。
2. 聚集索引与非聚集索引（二级索引）
在创建数据表时，我们通常会为表指定主键列——主键列上会默认自动创建索引。对于MySQL InnoDB存储引擎来说，它采用“索引组织表”的存储结构，主键上的索引不仅是索引，更是整张表的数据存储载体，这种索引被称为聚集索引（clustered index）。
很明显，一张表只能有一个聚集索引——如果有多个聚集索引，就意味着表的数据要被重复存储多次，这既浪费空间，也会导致数据不一致。我们手动为非主键列创建的索引，都属于二级索引（secondary index），也常被称为非聚集索引（non-clustered index）。
这里有一个关键知识点，一定要记牢：通过非聚集索引查询时，只能定位到目标数据的主键值，想要获取完整的行数据，还需要通过主键上的聚集索引再次查询，这个过程被称为“回表”。因此，通过非聚集索引检索数据的速度，通常比通过聚集索引检索要慢。
三、实战案例：索引对查询性能的影响（直观可见）
光说理论不够直观，下面我们通过一个实际案例，看看索引对查询性能的影响。假设我们有一张学生表（tb_student），需要根据学生姓名查询学生信息——这个场景在实际开发中十分常见，就像电商平台通过商品名称查询商品一样。
我们可以使用MySQL的explain关键字，查看SQL语句的执行计划（即数据库执行SQL的具体步骤），通过执行计划判断查询是否使用了索引、执行效率如何。
案例1：未创建索引时的查询（全表扫描，效率极低）
explain select*from tb_student where stuname='林震南'\G
执行结果如下：
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb_student
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 11
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
执行计划关键参数解读（必掌握）
执行计划中有几个核心参数，直接决定了查询性能，大家一定要熟练掌握，后续排查查询慢问题会经常用到：
- 1. select_type：查询类型，此处为SIMPLE（简单查询，无需使用UNION操作或子查询，最常见）；
- 2. table：当前查询对应的数据表（此处为tb_student）；
- 3. type：访问类型，即MySQL在表中查找满足条件的行的方式，性能从优到差依次为：NULL > const/system > eq_ref > ref > range > index > ALL；其中，ALL代表“全表扫描”，是性能最差的访问类型——需要扫描表中的每一行，才能找到匹配的数据，数据量越大，查询越慢。
- 4. possible_keys：MySQL可以选择使用的索引（此处为NULL，说明没有可选择的索引）；
- 5. key：MySQL实际使用的索引（此处为NULL，说明未使用任何索引）；
- 6. rows：执行查询需要扫描的行数（预估值），行数越少，查询效率越高（此处需扫描11行）；
- 7. Extra：查询的额外信息，此处为Using where，说明查询列未被索引覆盖，筛选条件不是索引的前导列，需扫描数据后再筛选。
从上面的执行计划可以看出：未创建索引时，查询属于全表扫描，效率极差——尤其是表中数据量达到几万、几十万时，这种查询会严重拖慢系统性能，甚至影响用户体验。
案例2：创建索引后的查询（索引查询，效率翻倍）
如果需要频繁通过学生姓名查询学生信息，我们可以在stuname列上创建索引，加速查询：
create index idx_student_name on tb_student(stuname);
再次执行相同的查询，并查看执行计划：
explain select*from tb_student where stuname='林震南'\G
执行结果如下：
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb_student
partitions: NULL
type: ref
possible_keys: idx_student_name
key: idx_student_name
key_len: 62
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
对比未创建索引的执行计划，变化十分明显，查询效率呈几何级提升：
- 1. type从ALL（全表扫描）变为ref（非唯一索引扫描），性能大幅提升；
- 2. key不再是NULL，而是实际使用了我们创建的
idx_student_name索引； - 3. rows从11变为1，只需扫描1行数据即可找到目标，无需遍历整张表。
四、前缀索引：空间与时间的平衡技巧
MySQL支持创建前缀索引，即仅对索引字段的前N个字符创建索引。这种方式可以有效减少索引占用的存储空间，但需要注意：空间节省的同时，可能会牺牲部分查询效率——时间与空间往往是不可调和的矛盾，需根据实际业务场景权衡。
创建前缀索引的语法如下（以学生姓名的前1个字符为例）：
create index idx_student_name_1 on tb_student(stuname(1));
该索引仅根据学生姓名的第一个字创建，再次查看查询执行计划：
explain select*from tb_student where stuname='林震南'\G
执行结果中，rows变为2——因为学生表中有两个姓“林”的学生，前缀索引只能定位到这两行，还需要进一步筛选，因此查询效率比完整索引略低，但索引占用的空间大幅减少。
建议：前缀索引适合字段长度较长（如varchar(255)）、前N个字符区分度较高的场景（如用户名、手机号），可在节省空间的同时，尽量减少查询效率的损耗。
五、索引的删除方法（两种方式，灵活选用）
如果某个索引不再需要（如业务场景变更、索引创建不合理），可通过以下两种方式删除，两种方式效果完全一致，可根据个人习惯选用：
方式1：通过ALTER TABLE语句删除（推荐，更规范）
alter table tb_student drop index idx_student_name;
方式2：通过DROP INDEX语句删除（简洁，适合快速操作）
drop index idx_student_name on tb_student;
温馨提示：删除索引前，建议先通过explain查看索引是否被使用，避免误删正在使用的索引，影响查询性能。
六、索引的高级用法与设计原则（避坑关键）
除了上述基础用法，MySQL还支持复合索引（多列组合索引）、函数索引（MySQL 5.7及以上版本支持）等高级用法。其中，合理使用复合索引实现“索引覆盖”，可以避免回表和不必要的排序，让查询性能成倍提升，感兴趣的同学可自行深入研究。
下面给大家总结5条索引设计核心原则，帮大家避开常见坑，让索引发挥最大作用：
- 1. 优先为WHERE子句、连接子句（JOIN）中出现的列创建索引——这些列是查询的筛选条件，索引能直接提升筛选效率，避免全表扫描；
- 2. 索引列的基数越高，索引效果越好——基数指列的取值数量（重复值越少，基数越高），比如“身份证号”“手机号”比“性别”“状态”更适合创建索引；
- 3. 合理使用前缀索引——减少索引占用空间，让内存中能缓存更多索引，平衡空间与时间成本，避免索引占用过多磁盘空间；
- 4. 索引不是越多越好——索引能加速查询（读操作），但会减慢增、删、改（写操作）的速度，因为数据变化时，索引也需要同步更新（类似书籍增删章节需更新目录）；
- 5. InnoDB存储引擎下，主键尽量选择短数据类型——普通索引会保存主键的值，主键越短，索引占用空间越小，缓存效果越好，间接提升查询效率。
七、索引生效与失效的关键注意点（必看）
很多同学会遇到“创建了索引，但查询还是很慢”的问题，核心原因是索引失效了。InnoDB使用的B+树索引，并非所有查询场景都会生效，以下是常见的生效与失效场景，一定要记牢：
- 1. 数值类型列：等值判断（=）、范围判断（>、<、>=、<=、BETWEEN...AND...、<>）时，索引均生效；
- 2. 字符串类型列：仅当模糊查询不以通配符（%）开头时，索引才生效（如
stuname like '林%'）；若以通配符开头（如stuname like '%震南'），索引会失效，大概率触发全表扫描； - 3. 其他失效场景：函数操作索引列（如
substr(stuname,1,1)='林'）、索引列使用运算（如age+1=20）、使用OR连接未索引的列，都会导致索引失效。
八、总结
本节课我们聚焦MySQL索引的核心知识点，从索引的定义、B+树底层原理，到实战案例、前缀索引、删除方法，再到设计原则和生效/失效场景，全方位拆解了索引的用法。
核心总结：索引是MySQL查询性能优化的核心，合理设计和使用索引，能让海量数据的查询效率实现质的飞跃；但索引并非“越多越好”，需结合业务场景权衡空间与时间成本，避开索引失效的坑，才能让索引发挥最大作用。
建议大家课后多动手实操，尝试创建不同类型的索引，通过explain查看执行计划，感受索引对查询性能的影响，逐步积累索引优化经验，真正将知识转化为实战能力。
