【MySQL】90% 的 MySQL 性能问题都和它有关！索引的正确打开方式，看完少走 3 年弯路
2025-11-17 17:10:57
文章摘要
本文聚焦MySQL索引这一性能优化核心，90%的MySQL性能瓶颈源于索引使用不当。索引是提升检索效率的数据结构，MySQL默认采用B+树，结合InnoDB 16KB默认页减少磁盘IO。索引分主键、普通等类型，支持自动或手动创建删除。它是“双刃剑”，需控制数量（5个内）、避冗余，遵循最左前缀原则，用EXPLAIN检查有效性，定期优化，兼顾查询效率与写操作性能，解决数据增长后的查询卡顿。



引言：在后端开发的日常工作中，你是否遇到过这样的场景：本地测试时 MySQL 查询秒级响应，一到生产环境就突然“卡壳”，页面加载转圈半分钟才出结果；或者随着业务数据量从几万条涨到几百万条，原本流畅的列表查询，慢慢变成了“龟速”操作？



> 如果你曾为这些问题头疼，那大概率没绕开 MySQL 性能优化的核心——索引。有数据统计显示，90% 的 MySQL 性能瓶颈，根源都在于索引使用不当：要么没建索引让查询“全表扫描”，要么建了冗余索引浪费资源，要么索引设计不合理导致优化器“弃用”。今天这篇文章，我们就从“为什么索引重要”讲到“怎么用对索引”，帮你避开新手常踩的坑，少走 3 年弯路。


一:什么是索引

MySQL的索引是⼀种数据结构，它可以帮助数据库⾼效地查询、更新数据表中的数据。索引通过⼀定的规则排列数据表中的记录，使得对表的查询可以通过对索引的搜索来加快速度。


MySQL 索引类似于书籍的目录，通过指向数据行的位置，可以快速定位和访问表中的数据，比如汉语字典的⽬录（索引）页，我们可以按笔画、偏旁部首、拼音等排序的目录（索引）快速查找到需要的字。


例如：笔画索引


拼音索引


可以发现使用索引的目的只有⼀个，就是提升数据检索的效率，在应用程序的运行过程中，查询操作的频率远远高于增删改的频率。




二:相信到这里我们很期待的是索引该使用何种的数据结构

2.1 HASH

时间复杂度是 O(1)，查询**速度非常快，但是MySQL并没有选择HASH做为索引的默认数据结构，主要原因是HASH不⽀持范围查找。

2.2 二叉搜索树

二叉搜索树的中序遍历是⼀个有序数组，但有⼏个问题导致它不适合⽤作索引的数据结构


>1.最坏情况下时间复杂度为O(N）


>2.节点个数过多⽆法保证树⾼


AVL和红黑树，虽然是平衡或者近似平衡，但是毕竟是⼆叉结构在检索数据时，每次访问某个节点的子节点时都会发生一次磁盘IO，而在整个数据库系统中，IO是性能的瓶颈，减少IO次数可以有效的提升性能




2.3 N叉树


> 为了解决树高的问题，可以使用N叉树

通过观察，相同数据量的情况下，N叉树的树高可以得到有效的控制，也就意味着在相同数据量的情况下可以减少IO的次数，从而提升效率。但是MySQL认为N叉树做为索引的数据结构还不够好。




2.4 最终使用我们的B+树

B+树是⼀种经常用于数据库和文件系统等场合的平衡查找树，MySQL索引采⽤的数据结构，以4阶B+树为例，如下图所示：


> B+树的特点


- 能够保持数据稳定有序，插入与修改有较稳定的时间复杂度

- 非叶子结点仅具有索引作用，不存储数据，所有叶⼦节点保真实数据

- 所有叶子节点构成⼀个有序链表，可以按照key排序的次序依次遍历全部数据


> B+树与B树的对比

- 叶子节点中的数据是连续的，且相互链接，便于区间查找和搜索。

- 非叶子节点的值都包含在叶子节点中

- 对于B+树而言，在相同树高的情况下，查找任⼀元素的时间复杂度都⼀样，性能均衡。

三:Mysql中的页

> 在 MySQL （尤其是 InnoDB 存储引擎）中，“页”是磁盘存储与内存交互的最小单位——就像文件系统中“字节”是数据的最小单位，但实际读写会以“块”为单位一样，MySQL 不会逐行读写数据，而是以“页”为基本单元处理。理解“页”的概念，是搞懂索引原理、数据存储、甚至性能优化的关键前提。

3.1 页的核心定义：为什么需要“页”？

简单来说，MySQL 页是 InnoDB 用于组织和管理数据的固定大小的“数据块”，默认大小为 16KB（可通过配置 `innodb_page_size` 修改，常见可选值为 4KB、8KB、16KB、32KB 等，但 16KB 是主流默认值）。


之所以需要“页”，本质是为了解决 “磁盘 IO 效率低”的问题：

- 磁盘的物理特性决定了“随机读写单条数据”的成本极高（磁头寻道、盘片旋转耗时远大于内存操作）；

- 若一次性读取“连续的、固定大小的一批数据”（即一页），就能大幅减少磁盘 IO 次数——比如读取 16KB 的页，若每行数据占 1KB，可一次性加载 16 行数据，后续访问这 16 行数据时，直接从内存读取即可，无需再次操作磁盘。


可以类比：逛超市时，不会逐个拿商品（对应“逐行读数据”），而是用购物篮（对应“页”）一次性装一批商品，减少往返货架的次数（对应“减少磁盘 IO”）。



3.2 页的关键属性：固定大小与类型划分

 


> 固定大小：16KB 为何是默认值？


nnoDB 选择 16KB 作为默认页大小，是综合“磁盘效率”与“内存利用率”的平衡结果：

- 若页太小（如 4KB）：单次加载数据量少，可能需要频繁触发磁盘 IO（比如查询 16 行数据需 4 次 IO）；

- 若页太大（如 64KB）：单次加载数据量过多，若实际只需要其中少量数据，会浪费内存空间（内存中缓存了大量无用数据），且加载时间更长；

- 16KB 既能保证单次加载足够多的数据（减少 IO），又能避免内存浪费，适配绝大多数业务场景（可通过 `SHOW VARIABLES LIKE 'innodb_page_size';` 查看当前实例的页大小）。


> 页的类型：不同页承载不同职责



MySQL 中的页并非只有“存储数据”这一种，而是按功能划分为多种类型，核心类型包括：


其中，数据页和索引页是与“业务查询、性能优化”关联最紧密的两种页——我们平时说的“数据存储”“索引结构”，本质都是围绕这两种页展开的。


3.3 数据页的内部结构：如何组织一行行数据？



数据页是存储实际业务数据的载体，其内部结构复杂但逻辑清晰，核心可分为 7 个部分（从页的开头到结尾）：



举个通俗的例子：数据页就像一本“小册子”——

- 文件头/文件尾是小册子的“封面”和“封底”，记录册子的编号、关联的其他册子；

- 页头是“目录摘要”，记录册子有多少页内容、空闲页数；

- 页目录是“详细目录”，标注每 8 行内容的位置；

- 行数据是册子的“正文内容”，空闲空间是册子的“空白页”。


3.4 页与索引的关系：B+ 树如何用页构建？

我们常说 InnoDB 索引是 B+ 树结构，而 B+ 树的每一个节点，本质就是一个“索引页”或“数据页”——这是理解索引的核心关联点：

- B+ 树的“非叶子节点”：都是索引页，存储“索引键 + 子节点页的编号”（比如主键索引的非叶子节点，存储的是某个主键范围 + 对应的子页编号）；

- B+ 树的“叶子节点”：若为**主键索引（聚簇索引）**，叶子节点是数据页，直接存储完整的行数据；若为**普通索引（非聚簇索引）**，叶子节点是索引页，存储“索引键 + 主键值”（需要通过主键值回表查数据页获取完整行）。


比如一棵高度为 3 的 B+ 树（主键索引）：

1. 根节点（1 个索引页）：存储主键范围与子页编号（如主键 1-1000 对应子页 A，1001-2000 对应子页 B）；

2. 中间节点（多个索引页，如子页 A、B）：进一步细分范围（如子页 A 存储 1-100 对应子页 A1，101-200 对应子页 A2）；

3. 叶子节点（多个数据页，如子页 A1、A2）：存储主键 1-100 对应的完整行数据，且所有叶子节点通过“文件头”的“上一页/下一页”编号连成链表（支持范围查询）。


查询时，MySQL 从根节点开始，通过页编号定位到目标页（如查主键 50，先找根节点→子页 A→子页 A1），再从子页 A1（数据页）中找到行数据——整个过程只需 3 次磁盘 IO（对应 B+ 树的高度），效率极高。


3.5 页的核心作用：对 MySQL 性能的影响

 理解“页”，最终是为了优化性能，其核心影响体现在 3 个方面：

1. 减少磁盘 IO：通过“批量加载一页数据”，将多次单条数据的 IO 合并为一次页 IO，这是 MySQL 高性能的基础；

2. 内存缓存（Buffer Pool）依赖：InnoDB 的 Buffer Pool（缓冲池）本质就是“内存中的页缓存区”——磁盘上的页被读取后，会缓存到 Buffer Pool 中，后续访问同一页时直接从内存读取，无需再读磁盘；

3. 页分裂与性能损耗：当数据页满了（16KB 用尽），插入新数据时会触发“页分裂”——将原页拆分为两个页，重新分配数据和索引，这个过程会产生额外的磁盘 IO 和索引维护成本，也是“主键无序（如用 UUID）会导致性能下降”的核心原因（无序插入更容易触发页分裂）。

四:索引分类

4.1 主键索引

- 当在⼀个表上定义⼀个主键 PRIMARY KEY 时，InnoDB使⽤它作为聚集索引。

- 推荐为每个表定义⼀个主键。如果没有逻辑上唯⼀且非空的列或列集可以使用主键，则添加⼀个自增列。


4.2 普通索引

- 最基本的索引类型，没有唯⼀性的限制。

- 可能为多列创建组合索引，称为复合索引或组全索引


4.3 唯⼀索引

- 当在⼀个表上定义⼀个唯⼀键 UNQUE 时，自动创建唯⼀索引。

- 与普通索引类似，但区别在于唯⼀索引的列不允许有重复值。

4.4 全文索引

- 基于文本列(CHAR、VARCHAR或TEXT列)上创建，以加快对这些列中包含的数据查询和DML操作

- 用于全文搜索，仅MyISAM和InnoDB引擎支持。


4.5 聚集索引

- 与主键索引是同义词

- 如果没有为表定义 PRIMARY KEY, InnoDB使⽤第⼀个 UNIQUE 和 NOT NULL 的列作为聚集索引。

- 如果表中没有 PRIMARY KEY 或合适的 UNIQUE 索引，InnoDB会为新插入的行⽣成⼀个行号并用6字节的 ROW_ID 字段记录， ROW_ID 单调递增，并使用 ROW_ID 做为索引。


4.6 非聚集索引

- 聚集索引以外的索引称为非聚集索引或⼆级索引

- ⼆级索引中的每条记录都包含该行的主键列，以及二级索引指定的列。

- InnoDB使用这个主键值来搜索聚集索引中的行，这个过程称为回表查询


4.7 索引覆盖

- 当⼀个select语句使用了普通索引且查询列表中的列刚好是创建普通索引时的所有或部分列，这时就可以直接返回数据，而不⽤回表查询，这样的现象称为索引覆盖



五:使用索引

5.1 自动创建

- 当我们为⼀张表加主键约束(Primary key)，外键约束(Foreign Key)，唯一约束(Unique)时，MySQL会为对应的的列自动创建⼀个索引

- 如果表不指定任何约束时，MySQL会自动为每⼀列生成⼀个索引并⽤ ROW_ID 进行标识

5.2 手动创建

5.2.1 主键索引


> 方式⼀，创建表时创建主键


```sql
create table t_test_pk (
 id bigint primary key auto_increment,
 name varchar(20)
);
```





> 方式二，创建表时单独指定主键列


```sql
create table t_test_pk1 (
 id bigint auto_increment,
 name varchar(20),
 primary key (id)
);
```





> 方式三，修改表中的列为主键索引


```sql
create table t_test_pk2 (
 id bigint,
 name varchar(20)
 );
alter table t_test_pk2 add primary key (id) ;
alter table t_test_pk2 modify id bigint auto_increment;
```




5.2.2 唯⼀索引



> 方式⼀，创建表时创建唯⼀键


```sql
create table t_test_uk (
 id bigint primary key auto_increment,
 name varchar(20) unique
);
```



> 方式二，创建表时单独指定唯⼀列


```sql
create table t_test_uk1 (
 id bigint primary key auto_increment,
 name varchar(20),
 unique (name)
);
```





> 方式三，修改表中的列为唯⼀索引


```sql
create table t_test_uk2 (
 id bigint primary key auto_increment,
 name varchar(20)
);
alter table t_test_uk2 add unique (name);
```






5.2.3 普通索引


> 方式⼀，创建表时指定索引列


```sql
create table t_test_index (
 id bigint primary key auto_increment,
 name varchar(20) unique
 sno varchar(10),
 index(sno)
);
```




> 方式二，修改表中的列为普通索引


```sql
create table t_test_index1 (
 id bigint primary key auto_increment,
 name varchar(20),
 sno varchar(10)
 );
 alter table t_test_index1 add index (sno) ;
```






> 方式三，单独创建索引并指定索引名


```sql
create table t_test_index2 (
 id bigint primary key auto_increment,
 name varchar(20),
 sno varchar(10)
);
create index index_name on t_test_index2(sno);
```



5.2.4 创建复合索引


> 方式⼀，创建表时指定索引列


```sql
create table t_test_index4 (
 id bigint primary key auto_increment,
 name varchar(20),
 sno varchar(10),
 class_id bigint,
 index (sno, class_id)
);






> 方式二，修改表中的列为复合索引


```sql
create table t_test_index5 (
 id bigint primary key auto_increment,
 name varchar(20),
 sno varchar(10),
 class_id bigint
);
alter table t_test_index5 add index (sno, class_id);
```





> 方式三，单独创建索引并指定索引名


```sql
create table t_test_index6 (
 id bigint primary key auto_increment,
 name varchar(20),
 sno varchar(10),
 class_id bigint
);
create index index_name on t_test_index6 (sno, class_id);

```






5.2.5查看索引


> 方式⼀：show keys from 表名






> 方式二：show index from 表名;



> 方式三： desc 表名;


5.3 删除索引

主键索引：alter table 表名 drop primary key；

这里发现删除出现异常，那么就需要先删除自增属性


alter table 表名 modify id bigint;

然后在重新删除主键

最后查看成功删除


> 其他索引


```sql
alter table 表名 drop index 索引名;
```

六:创建索引的注意事项


- 1.索引应该创建在高频查询的列上

- 2.索引需要占用额外的存储空间

- 3.对表进行插入、更新和删除操作时，同时也会修索引，可能会影响性能

 - 4.创建过多或不合理的索引会导致性能下降，需要谨慎选择和规划索引


有人觉得“多建索引，总有一个能用上”，但实际上，索引是“双刃剑”：

- 查询时受益：能快速定位数据；

- 写操作受损：每次执行 `INSERT`/`UPDATE`/`DELETE` 时，MySQL 不仅要修改数据，还要同步更新所有相关的索引树（B+树调整结构）；

- 占用磁盘空间：一个索引可能占用几十 MB 甚至 GB 的空间，冗余索引会浪费大量存储。

建议：一张表的索引数量控制在 **5 个以内**，且每个索引都要“有用”——比如高频查询的条件字段建索引，低频查询的字段坚决不建；能复用联合索引的，就不建单列索引（比如建了 `(a,b,c)` 联合索引，就不用再建 `a` 的单列索引，因为 `a` 是最左前缀，能被复用）。



七:总结：记住这 4 句话，索引用对不踩坑

1. 索引不是越多越好：控制在 5 个以内，避免冗余，减少写操作负担；

2. 联合索引要讲“顺序”：等值字段放前面，范围/排序字段放后面，遵循最左前缀原则；

3. 写完 SQL 用 EXPLAIN 检查：避免函数操作、隐式转换等导致索引失效的场景；

4. 定期优化索引：清理冗余和未使用的索引，让数据库“轻装上阵”。


其实 MySQL 索引不难，难的是“避坑”和“结合业务设计”。很多新手走弯路，就是因为一开始盲目建索引，没理解底层逻辑；而一旦掌握了“原理+实战技巧”，你会发现：原来优化 MySQL 性能，并没有想象中那么复杂——有时候一个“正确的索引”，比升级服务器配置更管用。


希望这篇文章能帮你学到很多东西，下次遇到 MySQL 性能问题时，能第一时间想到“是不是索引出了问题”，并快速定位解决～


声明：该内容由作者自行发布，观点内容仅供参考，不代表平台立场；如有侵权，请联系平台删除。