MySQL优化一：索引原理

最新推荐文章于 2023-12-04 17:22:58 发布

且行且珍惜、

最新推荐文章于 2023-12-04 17:22:58 发布

阅读量268

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/hello__ZC/article/details/103311542

版权

MySQL 专栏收录该内容

4 篇文章

订阅专栏

本文详细探讨了MySQL索引的设计原理，包括B+TREE结构的优势、页分裂问题、二级索引的工作机制，以及如何优化索引以提升查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

什么是索引

在一般的系统中，数据读一般是比数据写的比例高出许多，所以数据查询优化对于数据库来说是一个非常重要的工作，那么索引就是MySQL针对查询优化所设计的一种数据结构。MySQL官方解释是：索引是为MySQL提高获取数据效率的数据结构，为了快速查询数据。索引是满足某种特定查找算法的数据结构，而这些数据结构会以某种方式指向数据，从而实现高效查找数据。

MySQL中索引的实现

对于不同的存储引擎中索引的实现是不同的。以目前比较主流的存储引擎InnoDB和MyISAM来说，虽然两种存储引擎的索引所使用的数据结构都是相同的都是B+TREE但是其索引的存储结构略有不同。这里对这两种存储引擎索引的存储结构分别做一下介绍。

存储引擎

索引存储结构

特点

优点

缺点

InnoDB

聚簇索引

1.B+TREE叶子节点存储行数据

2.一张表中一定存在唯一的聚簇索引

3.聚簇索引一般是主键列，若主键列不存在，则使用第一个唯一索引，否则InnoDB内部生成一个聚簇索引

4.表数据是按照索引的顺序存储的

行数据存储在索引结构叶子节点，且行数据按照索引顺序存储，查询数据时可减少磁盘IO

1.更新索引代价非常大，会造成页分裂，使磁盘会存在大量的磁盘碎片，占用更多的磁盘空间

2.用二级索引查询数据需要回表到聚簇索引中查找

3.插入数据严重依赖插入顺序，若是使用UUID做主键，则会因为插入顺序不固定，造成大量的数据前插带来大量的磁盘碎片占用磁盘空间。

MyISAM

非聚簇索引

1.B+TREE叶子节点存储行数据的磁盘地址指针

2.一张表中可以存在多个非聚簇索引，MyISAM不会自动创建，只会在用户在添加索引时创建

3.表数据存储顺序与索引顺序无关

叶子节点存储行数据磁盘地址指针，所以数据存储顺序和索引顺序无关，不会造成页分裂问题

需要使用磁盘存储地址去磁盘寻址会加大磁盘IO

B+TREE的优点

对于一个多路查找树来讲，若是其树的深度过大势必会造成更多的磁盘IO,为了减少树的深度，所以B+TREE应运而生。

图例：

优点：

1. 由于中间节点不存指针，同样大小的磁盘页可以容纳更多的节点元素，树的高度就小。（数据量相同的情况下，B+树比B树更加“矮胖”），查找起来就更快。
2. B+树每次查找都必须到叶子节点才能获取数据。因此B+树查找的时间更稳定。
3. B+树的每一个叶子节点都有指向下一个叶子节点的指针，方便范围查询和全表查询：只需要从第一个叶子节点开始顺着指针一直扫描下去即可。

磁盘数据预读

我们知道磁盘IO是一种非常昂贵的操作，对于磁盘来说每次数据搜索，都是依赖转动磁盘和移动磁头这种机械运动，每次搜索数据延时都要大约10ms。为了减少磁盘的IO，计算机系统做了优化，每次搜索数据时都会对数据作出预读，不光会将当前磁盘地址的数据也会把相邻的磁盘地址的数据读取到内存缓冲区，因为磁盘的顺序读取效率较高不需要寻道时间，所以不仅减少了磁盘IO也提高了IO的效率。对于每次磁盘IO读取出来的数据我们称之为一页（page），一页大小为4k或8k，这个要看具体的操作系统。

对于使用B+TREE的索引来说，可以把一个节点的大小设计成一个磁盘页的大小。当创建一个节点时直接申请一个磁盘页的空间，那么一个节点也就存储在一个磁盘页内了。这样做的好处是，对于B+TREE每个节点的读取只需要一次磁盘IO。

页分裂问题

上面提到一个节点的存储就是一个磁盘页，当一个磁盘页被存满时就会开辟新的磁盘页存储。由于节点中存储的数据是按照索引大小顺序存储若是每次插入数据的索引大小顺序不固定，有可能比之前插入的数据的索引小那么会造成数据往前面页插入数据，这样的结果是若是往前插入的那一页已经存满了，那么就会在这页后面开辟一个新的磁盘页来存储这页后面的数据，这块新开辟的磁盘空间的剩余空间我们称之为磁盘碎片，若是每次插入数据都是这样可想而知会带来更多的磁盘碎片造成磁盘空间的浪费。

因此在创建聚簇索引时我们应该使用递增的整数作为主键这样在插入数据时他就会按照主键的大小顺序一页一页的顺序存储下去。

还有就是当我们建立的聚簇索引列修改的频率非常大时是不建议作为聚簇索引的，因为索引值的修改会造成数据的重新排序，这样也会带来页的分裂。

二级索引（辅助索引）

在InnoDB中在主键列建立的其他索引就是二级索引。二级索引可以有0个，可以有多个，就看用户有没有建立除主键索引外的其他索引。InnoDB中二级索引和聚簇索引同样都是使用的B+TREE区别在于二级索引叶子节点存储的是索引列的值和主键列的值。存储主键列值而不是存储行数据的存储地址带来的好处是不会因为主键索引的页分裂而造成的行数据地址改变而去改变二级索引，若是存储了行数据的地址那么当业数据的存储地址改变时还要同时改变二级索引中对应的地址，这样会加大二级索引维护的复杂度。

当使用二级索引查询数据时，并不是将数据集直接返回，而是先拿到主键列的值之后再回到聚簇索引根据主键值拿到当前行数据，索引使用二级索引查找数据需要两次的B+TREE查找。

二级索引图例: