MySQL---索引特性

最新推荐文章于 2026-08-28 15:51:53 发布

原创最新推荐文章于 2026-08-28 15:51:53 发布 · 371 阅读

12 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

索引的必要性

在一张海量表进行查询的时候，如果没有索引，查询时间会很长，特别是同时有多个用户进行查询时，很可能导致mysqld进程崩溃；而有了索引那么查询的时间会非常快，主要原因是因为内存和磁盘的 IO 次数减少了。

磁盘的认识

在理解索引究竟什么东西之前，我们要先了解磁盘。
（1）MySQL 给用户提供存储服务，而存储的都是数据，数据是在磁盘这个外设当中的，相比较其他外设，磁盘的数据交互效率算是比较低下的，所以如何提高MySQL 的效率，是MySQL 非常重要的话题。
（2）磁盘的基本单位是扇区（512字节），在学习文件系统时我们也了解到，为了让操作系统和磁盘解耦合，OS和磁盘进行交互的基本单位是数据块（4KB）。

如果操作系统直接使用硬件提供的数据大小进行交互，那么系统的IO代码，就和硬件强相关，换言之，如果硬件发生变化，系统必须跟着变化
单次 IO 512字节，还是太小了。IO单位小，意味着读取同样的数据内容，需要进行多次磁盘访问，会带来效率的降低。

MySQL与磁盘的交互

数据库文件，本质其实就是保存在磁盘的盘片当中，就是一个一个的文件。那数据库在内存是以 mysqld 进程的形式存在的。而 MySQL 作为一款应用软件，可以想象成一种特殊的文件系统。它有着更高的IO场景，所以，为了提高基本的IO效率， MySQL 进行IO的基本单位是 16KB

磁盘这个硬件设备的基本单位是 512 字节，而 MySQL InnoDB引擎使用 16KB 进行IO交互。即， MySQL 和磁盘进行数据交互的基本单位是 16KB 。这个基本数据单元，在 MySQL 这里叫做page（注意这里的page和系统的page不一样）

认识基础

（1）MySQL 中的数据文件，是以page为单位保存在磁盘当中的。

（2）MySQL 的 CURD 操作，都需要通过计算，而只要涉及计算，就需要CPU参与，而为了便于CPU参与，一定要能够先将数据移动到内存当中。所以在特定时间内，数据一定是磁盘中有，内存中也有。（我们总是说MySQL 和磁盘进行数据交互的基本单位是16KB，这只是为了理解索引需要有这个认知，但其实数据一般都是要先经过操作系统的，即实际上是 MySQL 和操作系统进行数据交互的基本单位是16KB）

（3）后续操作完内存数据之后，以特定的刷新策略，刷新到磁盘。而这时，就涉及到磁盘和内存的数据交互，也就是IO了。而此时IO的基本单位就是Page。

（4）为了更好的进行上面的操作， MySQL 服务器在内存中运行的时候，在服务器内部，就申请了被称为 Buffer Pool 的大内存空间，来进行各种缓存。本质就是很大的内存空间，来和磁盘数据进行IO交互。为了更高的效率，一定要尽可能的减少系统和磁盘IO的次数。

索引的本质

MySQL要管理很多数据表文件，需要先描述再组织，这里可以把一个个独立的表文件理解为由一个或多个page构成的。

Page的管理（MySQL InnoDB 引擎）

在内存中对page 的管理采用了 B+ 树和双向链表，我们先要理解为什么要用这个结构？

（1）当我们给表手动创建主键时，发现查询的数据是有序的，数据库会在我们插入数据的时候自动排序。页内部存放数据的模块，实质上也是一个链表的结构，链表的特点也就是增删快，查询修改慢，所以优化查询的效率是必须的。正式因为有序，在查找的时候，从头到后都是有效查找，没有任何一个查找是浪费的。

（2）按照MySQL与磁盘交互是page 单位的情况，在查询某条数据的时候直接将一整页的数据加载到内存中，以减少硬盘IO次数，是可以提高性能的；

（3）如果没有页目录，那么有1千万条数据，一定需要多个Page来保存1千万条数据，多个Page彼此使用双链表链接起来，而且每个Page内部的数据也是基于链表的。那么，查找特定一条记录，也一定是线性查找，效率就会很低。

按照上图，在每个页内部构建页目录，我们在查找时，就直接拿着主键遍历每一个page的页目录就行，但是这样还是要遍历所有的 page 啊，每进行一次 page 的遍历，都需要把这个 Page 加载进来，那还不是要进行大量的IO啊？那我引入 Page内部的目录也没什么用啊？

我们要明确我们的目的是查询的时候减少 Page 的IO，如果我此时给 Page 也带上目录，什么意思呢？就是新开一个 Page，里面专门记录下一层的 Page 中存放的最小数据的键值。

和页内目录不同的地方在于，这种页外目录管理的级别是页，而页内目录管理的级别是行。其中，每个目录项的构成是：键值（例如主键）+指针（执向下一层页）。这样一来，当我的 key 值比较大时，我在这个专门进行管理page最小键值的页先进行筛选，是不是就可以跳过很多底层 Page 页，找到匹配的页目录，进而通过指针，找到底层的Page，这样我只需要从磁盘加载一个Page，IO效率是不是就提高了。
那肯定有人说，每次检索数据的时候，该从哪里开始呢？虽然顶层的目录页少了，但是还是有可能遍历顶层的所有Page啊？那我再加目录页，让顶层最后只有一个页不就好了吗，那这样以后我查数据，带着那个 key 值，只要比较几次就可以找到 key 值数据所在页咯。

那其实我们可以发现，目录页的本质也是页，只是普通页（最底层）中存的数据是用户数据，而目录页中存的数据是普通页的地址。

所以这个目录页的思想是“用空间换时间”，我只要开辟一些页来专门存放：底层页里面最小的key值和对应指针，就可以起到减少非常多次IO的效果，是不是非常划算。

走到这里，你会发现这个非常优秀、可以减少 IO 次数，提高查找速率的管理page的结构，不就是B+ 树吗。

Page结构小总结

Page分为目录页和数据页。
目录页只放各个下级Page的最小键值。查找的时候，自顶向下找，只需要加载部分目录页到内存，即可找到目标数据页，完成算法的整个查找过程，大大减少了IO次数
上面我们讲的都是 InnoDB 下的索引结构，一般我们建表插入数据的时候，就是在该结构下进行 CURD。

InnoDB 下的B+树

在 MySQL InnoDB 中，表数据按照主键 B+ 树组织，因此可以认为表就是主键索引的 B+ 树，存放在buffer pool。
叶子节点保存表数据，非叶子节点没有数据，只有目录项
这个数是矮胖型的树：由于非叶子节点能存放很多的目录项（下级Page 的最小键值key），所以最底层其实是堆积了很多的叶子节点的，所以我们找到目标数据只需要很少的page，IO次数很少
叶子节点全部用链表级联起来：有利于范围查找。
所以一张表可能有多个索引，即多个B+树，取决给多少个列进行索引
用户没有手动设置主键，表内部默认也有B+树

B树和B+树

B树节点，既有数据，又有Page指针；而B+树，只有叶子节点有数据，其他目录页（非叶子），只有键值和Page指针（矮胖，效率查找更高）
B+叶子节点，全部相连，而B没有相连

聚簇索引 VS 非聚簇索引

上图为 MyISAM 表的主索引， Col1 为主键

MyISAM 最大的特点是，将索引Page和数据Page分离，也就是叶子节点没有数据，只有对应数据的地址。相较于 InnoDB 索引， InnoDB 是将索引和数据放在一起的

MyISAM 这种用户数据与索引数据分离的索引方案，叫做非聚簇索引。
InnoDB 这种用户数据与索引数据在一起索引方案，叫做聚簇索引。

上图表test1的存储引擎是InnoDB，test2的存储引擎是MyISAM。

普通索引

MySQL 除了默认会建立主键索引外，也可以按照其他列信息建立索引，这种索引可以叫做辅助（普通）索引。

（1）对于 MyISAM，建立辅助（普通）索引和主键索引没有差别，无非就是主键不能重复，而非主键可重复。

（2）InnoDB 的非主键索引中叶子节点并没有数据，而只有对应记录的key值。所以通过辅助索引，要找到目标记录，需要两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。这种过程，就叫做回表查询。
为何 InnoDB 针对这种辅助索引的场景，不给叶子节点也附上数据呢？原因就是太浪费空间了。

复合索引