MySQL索引，InnoDB索引详解

阳光穿透心脏的1/2处 2023-10-12 10:52 40阅读 0赞

**目录**

1、为什么使用索引

2、索引概述

优点

缺点

3、InnoDB中索引的推演

3.1、数据页

3.2、在一个页中的查找

3.2、在很多页中查找

3.3、设计一个索引

4、常见索引概念

4.1、聚簇索引

特点

优点

缺点

限制

4.2、二级索引（辅助索引、非聚簇索引）

回表

为什么需要回表操作？

总结

4.3、联合索引

5、InnoDB的B+树索引的注意事项

5.1、根页面位置万年不变

5.2、 内节点中目录项记录的唯一性

5.3、一个页面最少存储2条记

--------------------

### 1、为什么使用索引 ###

索引是存储引擎用于快速找到数据记录的一种数据结构，就好比一本教课书的目录部分，通过目录中找到对应文章的页码，便可快速定位到需要的文章。MySQL中也是一样的道理，进行数据查找时，首先查看查询条件是否命中某条索引，符合则通过索引查找相关数据，如果不符合则需要全表扫描，即需要一条一条地查找记录，直到找到与条件符合的记录。

![96f5be961eb04e168205bc6ed0528a6c.png][]

--------------------

建立索引的目的：为了**减少磁盘I/O的次数，加快查询速率**。

--------------------

### 2、索引概述 ###

MySQL官方对索引的定义为：**索引（Index）是帮助MySQL高效获取数据的数据结构**。

**索引的本质：**索引是数据结构。可以理解为“排好序的快速查找数据结构”，满足特定查找算法。这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法。

索引是在存储引擎中实现的，因此每种存储引擎的索引不一定完全相同，并且每种存储引擎不一定支持所有索引类型。同时，存储引擎可以定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少256字节。有些存储引擎支持更多的索引数和更大的索引长度。

#### 优点 ####

*  类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本，这也是创建索引最主要的原因。
 *  通过创建唯一索引，可以保证数据库中每一行数据的唯一性。
 *  在实现数据的参考完整性方面，可以加速表和表之间的连接。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。
 *  在使用分组和排序字句进行数据查询时，可以显著减少查询中分组和排序的时间，降低CPU的消耗。

#### 缺点 ####

增加索引也有许多不利的方面，主要表现为：

*  创建索引和维护索引要耗费时间，并且随着数据量的增加，所消耗的时间也会增加。
 *  索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，存储在磁盘上，如果有大量的索引，索引文件就可能比数据文件更举哀达到最大文件尺寸。
 *  虽然索引大大提高了查询速度，同时会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

因此，选择使用索引时，需要综合考虑索引的优点和缺点。

### 3、InnoDB中索引的推演 ###

##### 3.1、数据页 #####

*  页是mysql中磁盘和内存交换的基本单位，也是mysql管理存储空间的基本单位。
 *  同一个数据库实例的所有表空间都有相同的页大小；默认情况下，表空间中的页大小都为 16KB，当然也可以通过改变 innodb\_page\_size 选项对默认大小进行修改，需要注意的是不同的页大小最终也会导致区大小的不同。
 *  一次最少从磁盘读取16KB内容到内存中，一次最少把内存中16KB内容刷新到磁盘中，当然了单页读取代价也是蛮高的，一般都会进行预读

##### 3.2、在一个页中的查找 #####

假设目前表中的记录比较少，所有的记录都可以被存放到一个页中，在查找记录的时候可以根据搜索条件的不同分为两种情况：

*  以主键为搜索条件

可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。

*  以其他列作为搜索条件

因为在数据页中并没有对非主键列建立所谓的页目录，所以我们无法通过二分法快速定位相应的槽。这种情况下只能从最小记录开始依次遍历单链表中的每条记录，然后对比每条记录是不是符合搜索条件。很显然，这种查找的效率是非常低的。

##### 3.2、在很多页中查找 #####

大部分情况下我们表中存放的记录都是非常多的，需要好多的数据页来存储这些记录，在很多页中查找记录的话可以分为两个步骤：

1.  定位到记录所在的页。
2.  从所在的页内中查找相应的记录。

在没有索引的情况下，不论是根据主键列或者其他列的值进行查找，由于我们并不能快速的定位到记录所在的页，所以只能从第一个页沿着双向链表一直往下找，在每一个页中根据我们上面的查找方式去查找指定的记录。因为要遍历所有的数据页，所以这种方式显然是超级耗时的。如果一个表有一亿条记录呢？此时**索引**应运而生。

##### 3.3、设计一个索引 #####

![819cf90916f542c68817aca7fa5a8298.png][]

![ae281b24f71548c09810f1439c594b1a.png][]

![9eb8b33c081a452e84a89b919f1e0e2e.png][]

![efbb98e4ac21457592b87f412fabb74c.png][]

![cb92db2f40cd429fa344ef9252068efe.png][]

![2fbc9b53233243528f14834bc6f35b32.png][]

![4f267fda70e048fa9fe30843b395707b.png][]

![5b0bc92392e54eb19815000c8909823c.png][]

**问题：当数据页越来越多，意味着目录项也就越来越多，如果数据页之间插入了新的数据，那么目录项也需要插入新的目录与之对应，若按上面这种（目录项存储的值是连续的），是不可能实现的（与数据页一样，其实目录项就是数据页）。因此，我们可以把目录项按数据页进行处理，一层一层往上套：主要行格式中的record\_type**![ac7ebfb954f840258fc682f076d33a46.png][]

![e2c8ed8c76634f53a9270f3f41fb867e.png][]

![e0ee8f7fad3d4dfe80a934c2b6876f84.png][]

![b9de6ee1ce774aff9bbce6b7a4d15806.png][]

*  每一个蓝色框框都是数据页、
 *  最末尾的节点称为叶子节点，存储真正的数据
 *  叶子节点中的数据是通过单向链表的形式存储的，而叶子节点之间是通过双向链表存储的（逻辑上是连续）
 *  非叶子节点称为目录页

![73e1bc2526b94c5881ba8f0febee6292.png][]

注意：

*  树的层次越多，IO次数也就越多

### 4、常见索引概念 ###

索引按照物理实现方式，可以分为2种：聚簇索引（聚集）和非聚簇索引，我们也把非聚簇索引称为二级索引或者辅助索引。

##### 4.1、聚簇索引 #####

**聚簇索引**并不是一种单独的索引类型，而是**一种数据存储方式**（所有的用户记录都存储在了叶节点），也就是所谓的**索引即数据，数据即索引**

*  术语“聚簇”表示数据行和相邻的键值聚簇的存储在一起

###### 特点 ######

1、使用记录主键值的大小进行记录和页的排序，这包括三个方面

*  **页内**（**就是树中的节点**）的记录是按照主键的大小顺序排成一个**单项链表**
 *  叶子与叶子之间按照主键值大小顺序排成一个**双向链表**
 *  存放目录项记录的页（非叶子节点，不是存储真实数据的）分为不同层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个**双向链表**

2、B+树的叶子节点存储的是完整的用户记录（数据）

*  所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）

我们把具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在MySQL语句中显式的使用INDEX语句去创建，InnoDB存储引擎会自动为我们创建聚簇索引。

###### 优点 ######

*  **数据访问更快**，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
 *  聚簇索引对主键的**排序查找**和**范围查找**速度更快（因为是排序状态的，如查找id大于12的数据，直接获取12后面的所有数据即可）
 *  按照聚簇索引排序顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，索引节省了大量的IO操作（就是构建索引的优点）

###### 缺点 ######

*  **插入速度严重依赖插入顺序**，按照主键的顺序插入是最快的，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义**一个自增的ID列为主键**
 *  **更新主键的代价更高**，因为将会导致更新的行移动，因此，对于InnoDB表，我们一**般定义主键为不可更新**
 *  **二级索引访问需要两次索引查找**，第一次找到主键值，第二次根据主键值找到行数据

###### 限制 ######

*  对于MySQL数据库目前只有InnoDB数据库支持聚簇索引，而MyISAM并不支持聚簇索引
 *  由于数据物理存储排序方式只有一种，所以每个mysql的**表只能有一个聚簇索引**，一般情况下就是该表的主键
 *  如果没有定义主键，InnoDB会选择**非空的唯一索引代替**，如果没有这样的索引，InnoDB会隐式的定义一个主键来作为聚簇索引。
 *  为了充分利用聚簇索引的聚簇的特性，索引InnoDB表的主键列尽量**选用有序的顺序id**，而不建议用无序的id，比如UUID、MD5、HASH、字符串作为主键，无法保证数据的顺序增长。

##### 4.2、二级索引（辅助索引、非聚簇索引） #####

上述的聚簇索引只能对主键查找才生效（B+树中的数据都是按照主键进行排序的），如果我们想以其他列作为查找条件该怎么办呢？（肯定不是从头开始找）

答案：

*  多建立几棵B+树，不同的B+树中的数据采用不同的排序规则，比方说我们用c2列的大小作为数据页，页中记录的排序规则，再建一颗B+树
 *  **这颗树包含了主键和c2列的值，不包括其他列**
 *  select \* from xxx where c2 = 4时，会通过**这颗**B+树找到c2=4的数据
 *  然后根据c2=4的数据，**得到对应的主键值**，通过**回表**的操作，再对主键（聚簇索引）进行查找，最终得到对应的数据（聚簇索引得到的是所有数据）
 *  总共需要查找2次B+树

###### 回表 ######

我们根据这个以c2列大小排序的B+树只能确定要查找的主键值，所有如果我们根据c2列的值查找到完整的用户记录的话，仍然需要到聚簇索引中再查一遍，这个过程称为回表。

###### 为什么需要回表操作？ ######

*  如果把完整的数据都放到叶子节点，是不需要回表操作的，但是太占地方了，相当于每建立一颗B+树都需要把所有的数据再拷贝一份，这太浪费存储空间了。

因为这种按照非主键建立的B+树需要一次回表操作才可以得到完整的用户记录，所有这种B+树也被称为二级索引（英文名secondary index），或者辅助索引。由于我们使用的是c2列的大小作为B+树排序规则，所以我们也称这个B+树是为了c2列建立的索引。

![8fe42ca77a234073bd0c4c36cdb1731e.png][]

###### 总结 ######

*  聚簇索引的叶子节点存储的就是我们的数据记录，非聚簇索引的叶子节点存储的是数据位置（这里的数据位置指的是该数据的主键在哪，即主键是谁）。非聚簇索引不会影响数据表的物理存储顺序。
 *  一个表只能有一个聚簇索引，因为只能有一种排序存储的方式，但可以有多个非聚簇索引，也就是多个索引目录提供数据检索。
 *  使用聚簇索引的时候，数据的查询效率高，但如果对数据进行插入、删除、更新等操作，效率会比非聚簇索引低。（原因：以上面案例为由，聚簇索引存储了所有数据，c1/c2/c3共三列，而c2的非聚簇索引只有c1/c2，数据更新时，索引变动成本低）

##### 4.3、联合索引 #####

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按照c2和c3列的大小进行排序，这个包含两层含义：

*  先把各个记录和页按照c2列进行排序。
 *  在记录的c2列相同的情况，采用c3列进行排序。

为c2和c3列建立的索引的示意图如下：

![464c00d3867a4776966da325d427c3fd.png][]这里牵扯到最左匹配原则

*  本质上就是一个二级索引

### 5、InnoDB的B+树索引的注意事项 ###

##### 5.1、根页面位置万年不变 #####

![9979371aab9e46cf9491411f0b19866f.png][]

##### 5.2、 内节点中目录项记录的唯一性 #####

我们知道B+树索引的内节点中目录项记录的内容是索引项+页号的搭配，但是这个搭配对于二级索引来说优点不严谨。

假设表中的数据如下：

![9a9b6965aaa542fe8bac267dd17501fc.png][]如果二级索引中目录项记录的内容只是索引列+页号的搭配，那么为c2建立索引后的B+树应为：

![3f4f6216925e445abd9573e0334e9b8e.png][]![698ddbe190b9407c9ca18d3faa617a41.png][]

![d24972695ff343989f0737d49c1b6114.png][]

##### 5.3、一个页面最少存储2条记录 #####

树分叉，这里的页面指的是非叶子节点，如果没有至少2条记录，那么就不是树了，没有意义

[96f5be961eb04e168205bc6ed0528a6c.png]: https://img-blog.csdnimg.cn/96f5be961eb04e168205bc6ed0528a6c.png
[819cf90916f542c68817aca7fa5a8298.png]: https://img-blog.csdnimg.cn/819cf90916f542c68817aca7fa5a8298.png
[ae281b24f71548c09810f1439c594b1a.png]: https://img-blog.csdnimg.cn/ae281b24f71548c09810f1439c594b1a.png
[9eb8b33c081a452e84a89b919f1e0e2e.png]: https://img-blog.csdnimg.cn/9eb8b33c081a452e84a89b919f1e0e2e.png
[efbb98e4ac21457592b87f412fabb74c.png]: https://img-blog.csdnimg.cn/efbb98e4ac21457592b87f412fabb74c.png
[cb92db2f40cd429fa344ef9252068efe.png]: https://img-blog.csdnimg.cn/cb92db2f40cd429fa344ef9252068efe.png
[2fbc9b53233243528f14834bc6f35b32.png]: https://img-blog.csdnimg.cn/2fbc9b53233243528f14834bc6f35b32.png
[4f267fda70e048fa9fe30843b395707b.png]: https://img-blog.csdnimg.cn/4f267fda70e048fa9fe30843b395707b.png
[5b0bc92392e54eb19815000c8909823c.png]: https://img-blog.csdnimg.cn/5b0bc92392e54eb19815000c8909823c.png
[ac7ebfb954f840258fc682f076d33a46.png]: https://img-blog.csdnimg.cn/ac7ebfb954f840258fc682f076d33a46.png
[e2c8ed8c76634f53a9270f3f41fb867e.png]: https://img-blog.csdnimg.cn/e2c8ed8c76634f53a9270f3f41fb867e.png
[e0ee8f7fad3d4dfe80a934c2b6876f84.png]: https://img-blog.csdnimg.cn/e0ee8f7fad3d4dfe80a934c2b6876f84.png
[b9de6ee1ce774aff9bbce6b7a4d15806.png]: https://img-blog.csdnimg.cn/b9de6ee1ce774aff9bbce6b7a4d15806.png
[73e1bc2526b94c5881ba8f0febee6292.png]: https://img-blog.csdnimg.cn/73e1bc2526b94c5881ba8f0febee6292.png
[8fe42ca77a234073bd0c4c36cdb1731e.png]: https://img-blog.csdnimg.cn/8fe42ca77a234073bd0c4c36cdb1731e.png
[464c00d3867a4776966da325d427c3fd.png]: https://img-blog.csdnimg.cn/464c00d3867a4776966da325d427c3fd.png
[9979371aab9e46cf9491411f0b19866f.png]: https://img-blog.csdnimg.cn/9979371aab9e46cf9491411f0b19866f.png
[9a9b6965aaa542fe8bac267dd17501fc.png]: https://img-blog.csdnimg.cn/9a9b6965aaa542fe8bac267dd17501fc.png
[3f4f6216925e445abd9573e0334e9b8e.png]: https://img-blog.csdnimg.cn/3f4f6216925e445abd9573e0334e9b8e.png
[698ddbe190b9407c9ca18d3faa617a41.png]: https://img-blog.csdnimg.cn/698ddbe190b9407c9ca18d3faa617a41.png
[d24972695ff343989f0737d49c1b6114.png]: https://img-blog.csdnimg.cn/d24972695ff343989f0737d49c1b6114.png