玩转Mysql 五（MySQL索引）

一路走来，所有遇到的人，帮助过我的、伤害过我的都是朋友，没有一个是敌人。如有侵权，请留言，我及时删除！

一、索引的数据结构

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

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

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

3、索引的缺点
增加索引也有许多不利的方面，主要表现在如下几个方面：
?（1）创建索引和维护索引要耗费时间 ，并且随着数据量的增加，所耗费的时间也会增加。
?（2）索引需要占磁盘空间 ，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，存储在磁盘上 ，如果有大量的索? ? ? ? ? ? ? ? 引，索引文件就可能比数据文件更快达到最大文件尺寸。
?（3）虽然索引大大提高了查询速度，同时却会降低更新表的速度 。当对表中的数据进行增加、删除和修改的时候，索引也要动态? ? ? ? ? ? 地维护，这样就降低了数据的维护速度。因此，选择使用索引时，需要综合考虑索引的优点和缺点。

二、B+tree索引图

一个B+树的节点其实可以分成好多层，规定最下边的那层，也就是存放我们用户记录的那层为第 0 层，之后依次往上加。之前我们做了一个非常极端的假设：存放用户记录的页 最多存放3条记录 ，存放目录项记录的页 最多存放4条记录 。其实真实环境中一个页存放的记录数量是非常大的，假设所有存放用户记录
的叶子节点代表的数据页可以存放 100条用户记录 ，所有存放目录项记录的内节点代表的数据页可以存放 1000条目录项记录 ，那么：
如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放 100 条记录。
如果B+树有2层，最多能存放 1000×100=10,0000 条记录。
如果B+树有3层，最多能存放 1000×1000×100=1,0000,0000 条记录。
如果B+树有4层，最多能存放 1000×1000×1000×100=1000,0000,0000 条记录。相当多的记
录！！！你的表里能存放 100000000000 条记录吗？所以一般情况下，我们 用到的B+树都不会超过4层 ，那我们通过主键值去查找某条记录最多只需要做4个页面内的查找（查找3个目录项页和一个用户记录页），又因为在每个页面内有所谓的 Page Directory （页目录），所以在页面内也可以通过 二分法 实现快速定位记录。

?B-Tree 和 B+Tree 的区别：

• B+Tree中只有叶子节点会带有指向记录的指针，而BTree则所有节点都带有，在内部节点出现的索引项不会再出现在叶子节点中。

• B+Tree中所有叶子节点都是通过指针连接在一起，而BTree不会。

三、常见索引概念

1、索引按照物理实现方式，索引可以分为 2 种：聚簇（聚集）和非聚簇（非聚集）索引。我们也把非聚集索引称为二级索引或者辅助索引。MyISAM的索引方式都是“非聚簇”的，InnoDB包含1个聚簇索引是不同的。
聚簇类型B+TREE索引图

聚簇索引特点：
使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：页内 的记录是按照主键的大小顺序排成一个 单向链表 。各个存放 用户记录的页 也是根据页中用户记录的主键大小顺序排成一个 双向链表 。存放 目录项记录的页 分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个 双向链表 。B+树的 叶子节点 存储的是完整的用户记录。所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。
优点：
数据访问更快 ，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快聚簇索引对于主键的 排序查找 和 范围查找 速度非常快按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以 节省了大量的io操作 。
缺点：
插入速度严重依赖于插入顺序 ，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键更新主键的代价很高 ，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。

2. 二级索引（辅助索引、非聚簇索引、其他索引）

3、回表的概念

在MySQL数据库中，回表（也称为回源）是一种查询执行计划的操作，通常与索引相关。回表发生在使用索引查找数据行后，MySQL需要进一步检索其他数据列的情况下，例如查询一条完整的用户记录需要使用到 2 棵B+树。典型的数据库表包含多个列，而不仅仅是主键或索引列。当你执行一个SQL查询时，如果查询条件可以由索引满足，MySQL可能会首先使用索引找到满足条件的行的主键或行标识符。然后，MySQL需要”回表”来检索其他未包含在索引中的列的数据。这通常涉及到对磁盘上的实际数据行进行额外的I/O操作。
4、联合索引
我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按照 c2和c3列 的大小进行排序，这个包含两层含义：先把各个记录和页按照c2列进行排序。
在记录的c2列相同的情况下，采用c3列进行排序注意一点，以c2和c3列的大小为排序规则建立的B+树称为 联合索引 ，本质上也是一个二级索引。它的意思与分别为c2和c3列分别建立索引的表述是不同的，不同点如下：建立 联合索引 只会建立如上图一样的1棵B+树。为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树。

四、MyISAM 与 InnoDB索引对比

生产中常用InnoDB引擎所以对MyISAM引擎索引没有多介绍

MyISAM的索引方式都是“非聚簇”的，与InnoDB包含1个聚簇索引是不同的。小结两种引擎中索引的区别：

① 在InnoDB存储引擎中，只需要根据主键值对聚簇索引 进行一次查找就能找到对应的记录，而在MyISAM中却需要进行一次 回表操作，意味着MyISAM中建立的索引相当于全部都是 二级索引 。
② InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是分离的 ，索引文件仅保存数据记录的地址。
③ InnoDB的非聚簇索引data域存储相应记录 主键的值 ，而MyISAM索引记录的是 地址 。换句话说，InnoDB的所有非聚簇索引都引用主键作为data域。
④ MyISAM的回表操作是十分 快速 的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。
⑤ InnoDB要求表 必须有主键 （ MyISAM可以没有 ）。如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

推荐大佬博文

面试篇：MySQL_mysql面试-CSDN博客文章浏览阅读3.3k次，点赞6次，收藏24次。索引(index)是帮助MySQL高效获取数据的数据结构(有序)提高数据检索的效率，降低数据库的IO成本(不需要全表扫描)通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗嗯，索引在项目中还是比较常见的，它是帮助MySOL高效获取数据的数据结构，主要是用来提高数据检索的效率，降低数据库的10成本，同时通过索引列对数据进行排序，降低数据排序的成本，也能降低了CPU的消耗如果存在主键，主键索引就是聚集索引。如果不存在主键，将使用第一个唯一 (UNIQUE) 索作为聚集索引。_mysql面试https://blog.csdn.net/weixin_55127182/article/details/130291561+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

?