深入解析MySQL索引原理

快来打我* 2023-02-18 04:28 4阅读 0赞

**一. 什么是索引、为什么要建立索引？**

关于索引的理解，个人更加喜欢将其比喻为字典里面的目录，根据字典来进行查询的速度远大于每一页逐个逐个字排查的速度。
    	
    	索引主要用于快速找出在某个列中有特定值的行，倘若不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多。如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据，而不必查找所有数据，那么将会节省很大一部分时间。

*  ***索引分类***
    
        索引一般可以分为四类
         
         单列索引：单列索引指的是只包含一列的索引，又可分为三种：
         		-普通索引：普通索引是最基本的索引类型，没有什么限制，且允许空值和重复值。
         		-唯一索引：唯一索引列中的值必须是唯一的，允许存在一个空值。多个空值仍然会视为重复
         		-主键索引：主键索引是特殊的唯一索引，不允许存在空值。
         组合索引：组合索引也就是多列索引，由多列组合创建的索引，使用的时候遵循最左前缀原则。
         全文索引：全文索引只有MyISAM引擎支持，且只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，主要用于做文章的关键字搜索的
         空间索引：空间索引是对空间数据类型的字段建立的索引。

mysql索引的数据结构是树，常用的存储引擎innodb采用的是B+Tree。这里对B+Tree及其相关的

*  ***索引的存储文件是如何的？***
    
        MYISAM　存储文件结构
        .frm 表结构文件　　　
        .myd 数据文件
        .myi 索引文件

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

INNODB存储文件结构
    	.frm 表结构文件
    	.ibd 数据文件，ibd既包括数据也包括索引
    	表数据文件本身就是按B+Tree组织的一个索引结构文件，主键索引叶节点包含了完整的数据记录

*  ***主键索引三问***
    
        为什么非主键索引结构叶子节点存储的是主键值？
             一是保证一致性，更新数据的时候只需要更新主键索引树，
             二是节省存储空间。
         为什么推荐InnoDB表必须有主键？
             保证会有主键索引树的存在（因为数据存放在主键索引树上面），如果没有mysql会自己生成一个rowid作为自增的主键主键索引
         为什么推荐使用整型的自增主键？
         	一是方便查找比较，
         	二是新增数据的时候只需要在最后加入，不会大规模调整树结构，如果是UUID的话，大小不好比较，新增的时候也极有可能在中间插入数据，会导致树结构大规调整，造成插入数据变慢。

*聚集索引一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为NULL的唯一索引，如果还是没有的话，就采用Innodb存储引擎为每行数据内置的6字节ROWID作为聚集索引。*

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

**MySQL索引实现**  
在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。

MyISAM索引实现  
MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：

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

这里设表一共有三列，假设我们以Col1为主键，则图8是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ptZW1vcnlz_size_16_color_FFFFFF_t_70 1]  
图9

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB索引实现  
虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ptZW1vcnlz_size_16_color_FFFFFF_t_70 2]  
图10

图10是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，图11为定义在Col3上的一个辅助索引：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ptZW1vcnlz_size_16_color_FFFFFF_t_70 3]图11

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

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

B+Tree中一个节点就可以存放很多个索引列数据，一次IO就可以拿到很多数据，mysql默认的一个节点16K的大小，可以通过show global status like “Innodb\_page\_size” 看到该值是16384，每次IO读取16K大小的数据，以索引列是bigInt类型为例，大小8字节，每一条数据还有一个指向下一层的指针6字节，16384/（8+6）=1170，一个节点就大约可以存1170条数据。

以一个层高为3的树为例，叶子节点存放数据之后大小1KB，那么这个树可以存放 1170 \*1170 \*16 =21,902,400,大约2200万条数据。所以在这种千万级的表中通过主键索引查找一条数据，最多3次IO就可以找到一条数据。而很多时候树的根节点基本都是在内存中，所以多数时候只需要2次IO。

叶子节点之间也有双向指针连接，提高区间范围性能，范围查找。

**创建索引的时候，可以选择索引数据类型，一个是btree一个是hash，hash查找当然也快，但是当遇到范围查找的时候hash就尴尬了，所以根据实际业务需求来看是用btree还是hash。**

*  ***回表查询***
    
        这先要从InnoDB的索引实现说起，InnoDB有两大类索引
         	聚集索引(clustered index)----- 聚集索引，叶子节点存储行记录
         	普通索引(secondary index)-----普通索引，叶子节点存储主键值，
         那普通索引的查询过程是怎么样的呢？
         	通常情况下，需要扫码两遍索引树。
         	先定位主键值，再定位行记录，它的性能较扫一遍索引树更低，这就是所谓的回表查询。

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

*  ***什么是索引覆盖(Covering index)？***
    
        只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。
         比如：select id,name from user where name='zhangsan'
         表id为主键，name为普通索引。则此条sql满足了索引覆盖 因为 ‘name’的索引树的叶子节点存储的是id的值。不需要再次查询id主键的索引树，只查了一次，这就叫索引覆盖。
         比如select id,name,sex from user where name='zhangsan'
         表id为主键 name为普通索引。这条sql不满足索引覆盖，因为‘name’的叶子节点并没有存储‘sex’的值 ，所以要回表查询。
         但是如果这里将sex与name作为联合索引则可以实现索引覆盖，不用回表查询。

如何实现索引覆盖？

*  ***如何实现索引覆盖？***
    
        常见的方法是：将被查询的字段，建立到联合索引里去。
 *  ***最左前缀原则***
    
        前面讲到联合索引，这里讲下什么是最左前缀原则？
         https://blog.csdn.net/zgjdzwhy/article/details/84062105//本文写的不错不过有个地方有点错误
         https://www.jianshu.com/p/9b3406bcb199

参考：*https://www.cnblogs.com/williamjie/p/11187470.html*  
参考：*https://blog.csdn.net/u013314679/article/details/105665080*  
参考：*https://blog.51cto.com/14257001/2413768*

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ptZW1vcnlz_size_16_color_FFFFFF_t_70_pic_center]: https://img-blog.csdnimg.cn/20200616093022578.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ptZW1vcnlz,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ptZW1vcnlz_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200616105242425.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ptZW1vcnlz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ptZW1vcnlz_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200616105334879.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ptZW1vcnlz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ptZW1vcnlz_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200616105346269.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ptZW1vcnlz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ptZW1vcnlz_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200616105359893.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ptZW1vcnlz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ptZW1vcnlz_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200616160842582.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ptZW1vcnlz,size_16,color_FFFFFF,t_70