Mysql InnoDB引擎中索引理解

电玩女神 2022-12-28 06:12 191阅读 0赞

前言：面试的时候经常问到索引，前面也只是拿面经背了背，今天看了看书查了查资料，重新理解了一下索引，文章中说的mysql没有特别说明指的都是InnoDB引擎下得mysql，适合有一定基础的。

参考：

《Mysql技术内幕：InnoDB存储引擎》

[https://blog.csdn.net/qq\_41999455/article/details/104946754][https_blog.csdn.net_qq_41999455_article_details_104946754]

[https://blog.csdn.net/qq\_29235677/article/details/106950270][https_blog.csdn.net_qq_29235677_article_details_106950270]

[https://www.cnblogs.com/dbf-/p/11891530.html][https_www.cnblogs.com_dbf-_p_11891530.html]

[https://www.cnblogs.com/bdsir/p/8745553.html][https_www.cnblogs.com_bdsir_p_8745553.html]

[https://blog.csdn.net/u011196295/article/details/88030451][https_blog.csdn.net_u011196295_article_details_88030451]

[https://blog.csdn.net/qq\_33521184/article/details/109991531][https_blog.csdn.net_qq_33521184_article_details_109991531]

[https://www.cnblogs.com/wdss/p/11186411.html][https_www.cnblogs.com_wdss_p_11186411.html]

**目录**

第一章 InnoDB逻辑存储结构

1.1 表空间

1.2 段

1.3 区

1.4 页

1.4.1 User Records

第二章 索引的构成

第三章 索引的查询过程

第四章 面试时遇到的问题

4.1 索引树一般有几层吗，为什么不用B树

4.2 联合索引

4.3 索引失效的情况

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

# 第一章 InnoDB逻辑存储结构 #

InnoDB中所有数据都被逻辑地存放在一个空间中，称之为表空间，表空间又由段、区、页组成。

![img][]

## 1.1 表空间 ##

默认情况下，InnoDB存储引擎有一个共享的表空间ibdata1，即所有数据都存放在这个表空间中，如果用户启用了innodb\_file\_der\_table参数，则每张表的数据可以单独放到一个表空间中，如果启动了此参数，每张表的表空间内存放的只是数据、索引和插入缓冲Bitmap页，其它的数据如回滚信息，插入缓冲页，系统事务信息，二次写缓冲还是存放在原来的共享表空间。

## 1.2 段 ##

常见的段有数据段、索引段、回滚段等等，数据段即为B+树的叶子节点，索引段即为B+树的非索引节点，对段的管理都是由引擎自身完成，DBA不能也没有必要对其进行控制。

## 1.3 区 ##

区是由连续页组成的空间，在任何情况下每个区的大小都为1MB，为了保证区中页的连续性，InnoDB存储引擎一次从磁盘中申请4~5个区，在默认情况下，InnoDB存储引擎页的大小为16kb，即一个区中一共有64个连续的页。

## 1.4 页 ##

InnoDB存储引擎中，表都是按主键顺序组织存放的，这种存储方式得表称为索引组织表，不只是数据会组成页，还有索引页、undo页、系统页等等。

*  File Header：表示页的一些通用信息，占固定的38字节
 *  Page Header：表索引页专有的一些信息，占固定的56个字节
 *  Infimum & Supremum：两个虚拟的伪记录，分别表示页中的最小和最大记录，占固定的26个字节
 *  User Records：真实存储我们插入的记录的部分，大小不固定（）
 *  Free Space：页中尚未使用的部分，大小不固定
 *  Page Directory：页中的某些记录相对位置，也就是各个槽在页面中的地址偏移量，大小不固定，插入的记录越多，这个部分占用的空间越多
 *  File Trailer：用于检验页是否完整的部分，占用固定的8个字节

![7ec4adfede22f48cb96c879f7e51905a.png][]

### 1.4.1 User Records ###

用户记录是按照相应行格式存储数据的地方，行有不同的存储格式。

InnoDB存储引擎有两种文件格式

Antelope:compact与redundant两种行记录格式

Barracuda:compress与dynamic两种行记录格式

以 Compact 为例：

![20201222223442866.png][]

借助 `next_record`，记录之间像链表一样连接起来，顺序为主键从小到大排序，第一个为最小记录，最后一条为最大纪录  
`next_record` 指向的是下一纪录真数据开始的地方，也就是下一纪录 `next_record` 之后

删除记录

当其中的一条记录被删除后，上一条记录的 `next_record` 指向下一条纪录，此记录标记为删除，`next_record` 指向 0，当前组最后一条记录

# 第二章 索引的构成 #

索引分为聚簇索引和辅助索引，之前也说过表都是按主键顺序组织存放的，Mysql会自动按照主键建立聚簇索引，这也是数据库数据的存放形式，如果没有主键则会：

**1 首先判断表中是否有非空的整形唯一索引,如果有,则该列为主键(这时候可以使用  select \_rowid from table 查询到主键列).**

**2 如果没有符合条件的则会自动创建一个6字节的主键(该主键是查不到的).**

组成的聚簇索引结构大概如下，可以看到：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3OTQxNDgz_size_16_color_FFFFFF_t_70_pic_center][]

辅助索引：

辅助索引同样也是b+树，非聚簇索引的叶子节点存储的是数据的主键值，没有存储全部的行数据，当进行查找时，要先找到对应的主键，然后再拿着主键去聚簇索引中查找数据。

![aHR0cHM6Ly9jZG4ubmxhcmsuY29tL3l1cXVlLzAvMjAxOS9wbmcvMTcxNTkzLzE1NjAwNjY2OTczNjMtYzAwYzcyMmYtNWVmZi00ZDFlLWFlYjQtZTUxMTc4YmVkMDgyLnBuZw][]

# 第三章 索引的查询过程 #

聚簇索引：

每个数据页都会为存储在它里边儿的记录生成一个页目录（Page Directory），在通过主键查找某条记录的时候可以在页目录使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。

1.  InnoDb通过B+Tree聚集索引搜索时，只能找到该记录所在的索引页(index page)，而不能到具体的行记录。
2.  找到该索引页(index page)后将该页加载入内存。
3.  通过key在索引页(index page)的directory slots中进行二分查找（binary search），找到key对应的slot。
4.  因为slot是管理多条记录，普通的slot最少管辖4条,最多管辖8条,所以会再根据KEY在对应的slot管理的记录中顺序（linear search）查找，找到最终结果。

这里提个问题：进行二分查找是只发生在数据页还是也会在索引页中进行二分查找？凭上面的分析我感觉应该是在索引页中也是二分查找的。

辅助索引：

当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

没有索引：

只能从最小记录开始依次遍历单链表中的每条记录  
所以说，如果我们写select \* from user where indexname = 'xxx’这样没有进行任何优化的sql语句，默认会这样做：  
1.**定位到记录所在的页:需要遍历双向链表，找到所在的页**  
2.**从所在的页内中查找相应的记录:由于不是根据主键查询，只能遍历所在页的单链表了**  
很明显，在数据量很大的情况下这样查找会很慢！这样的时间复杂度为O（n）。

# 第四章 面试时遇到的问题 #

## 4.1 索引树一般有几层吗，为什么不用B树 ##

一般有几层：

这里我们先假设 B+ 树高为 2，即存在一个根节点和若干个叶子节点，那么这棵 B+ 树的存放总记录数为：**根节点指针数 \* 单个叶子节点记录行数。**

上文我们已经说明单个叶子节点（页）中的记录数 =16K/1K=16。（这里假设一行记录的数据大小为 1k，实际上现在很多互联网业务数据记录大小通常就是 1K 左右）。

那么现在我们需要计算出非叶子节点能存放多少指针？

其实这也很好算，我们假设主键 ID 为 bigint 类型，长度为 8 字节，而指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即 16384/14=1170。

那么可以算出一棵高度为 2 的 B+ 树，能存放 1170\*16=18720 条这样的数据记录。

根据同样的原理我们可以算出一个高度为 3 的 B+ 树可以存放： 1170*1170*16=21902400 条这样的记录。

**所以在 InnoDB 中 B+ 树高度一般为 1-3 层，它就能满足千万级的数据存储。**

在查找数据时一次页的查找代表一次 IO，所以通过主键索引查询通常只需要 1-3 次 IO 操作即可查找到数据 。

为什么不用B树：

因为B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致IO 操作变多，查询性能变低。

## 4.2 联合索引 ##

MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

查看mysql的排序规则：

mysql> show collation;

mysql> show variables like 'collation\_%';

\+----------------------+-------------------+

| Variable\_name        | Value             |

\+----------------------+-------------------+

| collation\_connection | utf8\_general\_ci   |

| collation\_database   | latin1\_swedish\_ci |

| collation\_server     | latin1\_swedish\_ci |

**具体原因为:**

MySQL使用索引时需要索引有序，假设现在建立了"name，age，school"的联合索引，那么索引的排序为: 先按照name排序，如果name相同，则按照age排序，如果age的值也相等，则按照school进行排序。

当进行查询时，此时索引仅仅按照name严格有序，因此必须首先使用name字段进行等值查询，之后对于匹配到的列而言，其按照age字段严格有序，此时可以使用age字段用做索引查找，以此类推。因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。

## 4.3 索引失效的情况 ##

这个限于时间的关系，就只是大概列举一下索引失效的情况（其实也是别人博客上复制的，自己还在学校没什么场景去用），至于更详细的索引失效原因，后面再去分析。

1、like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。

2、or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效

3、组合索引，不是使用第一列索引，索引失效

4、数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。

5、在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。

6、对索引字段进行计算操作、字段上使用函数。（索引为 emp(ename,empno,sal)）

7、当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。

另外网上说在索引列上使用 IS NULL 或 IS NOT NULL操作会失效，这个有问题，具体可参考[https://mp.weixin.qq.com/s/CEJFsDBizdl0SvugGX7UmQ][https_mp.weixin.qq.com_s_CEJFsDBizdl0SvugGX7UmQ]

另外附一个最左匹配原则的图：

<table> 
 <thead> 
  <tr> 
   <th>where语句</th> 
   <th>索引是否被使用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>where a = 3</td> 
   <td>Y,使用到a</td> 
  </tr> 
  <tr> 
   <td>where a = 3 and b = 5</td> 
   <td>Y,使用到a，b</td> 
  </tr> 
  <tr> 
   <td>where a = 3 and b = 5 and c = 4</td> 
   <td>Y,使用到a,b,c</td> 
  </tr> 
  <tr> 
   <td>where b = 3 或 where b = 3 and c = 4 或 where c = 4</td> 
   <td>N，索引必须从头开始</td> 
  </tr> 
  <tr> 
   <td>where a = 3 and c = 5</td> 
   <td>使用到a， 但是C不可以，中间断了</td> 
  </tr> 
  <tr> 
   <td>where a = 3 and b &gt; 4 and c = 7</td> 
   <td>使用到a和b， c在范围之后，断了</td> 
  </tr> 
  <tr> 
   <td>where a = 3 and b like ‘kk%’ and c = 4</td> 
   <td>使用到a和b， c在模糊查询的通配符之后，断了</td> 
  </tr> 
 </tbody> 
</table>

[https_blog.csdn.net_qq_41999455_article_details_104946754]: https://blog.csdn.net/qq_41999455/article/details/104946754
[https_blog.csdn.net_qq_29235677_article_details_106950270]: https://blog.csdn.net/qq_29235677/article/details/106950270
[https_www.cnblogs.com_dbf-_p_11891530.html]: https://www.cnblogs.com/dbf-/p/11891530.html
[https_www.cnblogs.com_bdsir_p_8745553.html]: https://www.cnblogs.com/bdsir/p/8745553.html
[https_blog.csdn.net_u011196295_article_details_88030451]: https://blog.csdn.net/u011196295/article/details/88030451
[https_blog.csdn.net_qq_33521184_article_details_109991531]: https://blog.csdn.net/qq_33521184/article/details/109991531
[https_www.cnblogs.com_wdss_p_11186411.html]: https://www.cnblogs.com/wdss/p/11186411.html
[img]: /images/20221120/0d1a9cb7bab94a4a91d951812f88dccb.png
[7ec4adfede22f48cb96c879f7e51905a.png]: https://img-blog.csdnimg.cn/img_convert/7ec4adfede22f48cb96c879f7e51905a.png
[20201222223442866.png]: https://img-blog.csdnimg.cn/20201222223442866.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3OTQxNDgz_size_16_color_FFFFFF_t_70_pic_center]: https://img-blog.csdnimg.cn/20190407101611384.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3OTQxNDgz,size_16,color_FFFFFF,t_70#pic_center
[aHR0cHM6Ly9jZG4ubmxhcmsuY29tL3l1cXVlLzAvMjAxOS9wbmcvMTcxNTkzLzE1NjAwNjY2OTczNjMtYzAwYzcyMmYtNWVmZi00ZDFlLWFlYjQtZTUxMTc4YmVkMDgyLnBuZw]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9jZG4ubmxhcmsuY29tL3l1cXVlLzAvMjAxOS9wbmcvMTcxNTkzLzE1NjAwNjY2OTczNjMtYzAwYzcyMmYtNWVmZi00ZDFlLWFlYjQtZTUxMTc4YmVkMDgyLnBuZw
[https_mp.weixin.qq.com_s_CEJFsDBizdl0SvugGX7UmQ]: https://mp.weixin.qq.com/s/CEJFsDBizdl0SvugGX7UmQ