mysql 索引原理详解

╰半橙微兮° 2023-01-10 11:53 120阅读 0赞

## 前言 ##

在mysql的性能优化中，索引优化可以算是篇幅最大的了，这是因为mysql底层的Innodb结构在对于索引的优化上面做了大量的优化和改进，如何合理的使用索引，加速查询性能，是一个值得研究的问题

## Innodb索引 ##

为什么要建立索引呢？拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字

mysql索引的目的也是在此，数据可以以一定的物理结构存储到磁盘上，但是检索的时候，如果直接查找必然会消耗大量的时间在其他方面，而有了索引，配合合理的存储结构，就可以大大加速检索效率，因此数据表索引的目的可以简单总结为：

*  索引是为数据表建立的“目录”
 *  索引的目的是为了防止全表扫描(full scan)
 *  索引的存储形式是由存储引擎决定

**数据表索引分类**

*  按照存储结构划分，BTree索引（B-Tree或B+Tree索引），Hash索引，full-index索引，R-Tree索引
 *  按照应用层次划分，可分为普通索引，唯一索引，复合索引
 *  根据表的数据的物理存储顺序与键值的逻辑（索引）顺序关系，可分为聚集索引和非聚集索引（具体由存储引擎决定）

**mysql中常用的索引**

*  B-Tree索引，适合范围查找
 *  Hash索引，适合精确查询

**B-Tree索引**

*  mysql中Innodb和myIsam采用的就是B+Tree索引，
 *  B+Tree索引采用树形链表结构建立数据“目录”

假如有这么一张数据表  
![在这里插入图片描述][20210124101458969.png]  
在没有使用索引的情况下，假如我们要查询id为6的数据，就需要依次顺序遍历到id为6的数据未知，但是有了索引之后（B+Tree），整个存储的结构就是下面这样

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY29uZ3lpNDIw_size_16_color_FFFFFF_t_70]

假如使用了B+Tree索引之后再次查找，当查找ID=7的数据时候，根据树的结构，首先定位到ID=7所在的范围为最右边的006节点，这样一下子大大缩小了查询的范围从而减少了查询的时间

如果再在其他字段上创建了索引，比如给name字段添加索引，这时候的结构图可表示如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY29uZ3lpNDIw_size_16_color_FFFFFF_t_70 1]  
即以name为索引创建一个B+Tree的索引存储结构，与主键索引进行关联，当查询name=Hanke的数据时，通过name的索引关联找到ID=6的索引，然后将数据返回，也许大家发现，这里相当于是二次索引，没错，一定程度上，非主键索引或唯一索引存在这个特点

**注意点：**

*  使用B+Tree索引时，建议使用自增长的主键作为索引，方便底层的树进行快速的变更与重构（树重新达成平衡消耗在计算上的时间最小）
 *  而使用其他字段作为B+Tree索引时，树在重新计算的时候的工作量很大（树重新达成平衡消耗在计算上的时间会大大增加）

而如果存储引擎是MyIsam，同样使用B+Tree的ID为主键的索引存储结构时，由于索引的数据节点不再直接存储值，而是一个物理指针，指向具体的与之对应的表数据的地址  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY29uZ3lpNDIw_size_16_color_FFFFFF_t_70 2]

## Hash索引 ##

*  基于hash表实现
 *  精确匹配索引所有列的查询才有效
 *  Hash索引为每一条数据生成一个hashCode(通过这个hashCode就可以快速定位数据所在位置)

假如我们使用Memory引擎创建一张表（当前mysql版本在Memory引擎时支持Hash索引）

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY29uZ3lpNDIw_size_16_color_FFFFFF_t_70 3]  
简而言之，hash索引的检索过程可以归纳如下：

*  当为数据表插入一条数据时，hash索引将会使用hash算法对name字段生成一个hashcode，这里理解为有一个中间表保存了hashcode和地址行指针的对应关系值
 *  这个hashcode执行数据表中具体的那一行数据
 *  再次检索的这条数据的时候，计算拿到hashcode之后直接通过指针定位到数据行

**hash索引特点：**

*  hash索引只包含hash值和行指针
 *  hash索引只支持精确匹配，不支持范围查询，模糊查询和排序
 *  在对数据保存时是零散的（B-Tree的数据区是一个连续有序的）
 *  hash取值速度非常快，但是索引选择很低时不建议使用（比如性别字段或者枚举字段）
 *  mysql目前只有Memory引擎支持hash索引（Innodb只在数据的精准匹配时，会自动生成hashCode并放入缓存，下次或后面反复查询同一条数据时加快检索速度）

不管是使用那种存储引擎，使用索引的目的都是为了提升数据的检索速度，因此总结使用索引的几个优点如下：

*  索引大幅提升了数据的检索效率
 *  索引把随机IO变成了顺序IO，比如Innodb的聚集索引，将数据的存储变成有序的连续的存储，在检索数据时就可以根据索引进行快速定位（无序变有序，范围查找时效果更明显，减少了计算的时间）

当然，索引也存在一些缺点：

*  **一定程度上降低了数据写入的效率**，因为索引相当于是说在数据表之外添加了新的数据存储目录来保存索引与数据的对照关系，因此在插入数据时，需要在额外的计算与索引之间的关系，在大批量数据写入的时候，这个效果会很明显
 *  **太多的索引增加了查询优化器的选择时间**，查询优化器在对一条sql语句进行分析时，会结合一系列的分析计算出一条最优的查询sql，但是添加了索引之后，相当于是在原来的基础上，添加了对索引因素的分析，如果字段比较多，而且在很多字段上创建了索引的话，这无疑会加大这个选择的时间，增加了CPU的计算资源开销
 *  **不合理的使用索引，会额外占用磁盘空间**，由于索引本身也需要维护一个索引目录，是需要占用磁盘空间的，如果创建的索引太多，随着数据量的大幅增长，甚至索引占用的空间也大幅增加，这显然会加大磁盘空间的消耗

[20210124101458969.png]: /images/20221119/4c980d1895164417bc6a4b848ff23fe9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY29uZ3lpNDIw_size_16_color_FFFFFF_t_70]: /images/20221119/e520313edf124ab8ad6e2d2a07271187.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY29uZ3lpNDIw_size_16_color_FFFFFF_t_70 1]: /images/20221119/e2c90f571aff41b383eef2e0df9b34e2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY29uZ3lpNDIw_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20210124105708954.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY29uZ3lpNDIw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY29uZ3lpNDIw_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/2021012412364134.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY29uZ3lpNDIw,size_16,color_FFFFFF,t_70