MySQL 索引底层原理详解

大家好 我是积极向上的湘锅锅💪💪💪

何为索引？有什么作用？

索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。

索引的作用就相当于书的目录。打个比方: 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了

索引特点

索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种

Hash表

哈希表是键值对的集合，通过键(key)即可快速取出对应的值(value)，因此哈希表可以快速检索数据（接近 O（1））。

为何能够通过 key 快速取出 value呢？ 原因在于 哈希算法（也叫散列算法）。通过哈希算法，我们可以快速找到 key 对应的 index，找到了 index 也就找到了对应的 value。

但是我们知道哈希算法有Hash冲突的问题，为了减少 Hash 冲突的发生，一个好的哈希函数应该“均匀地”将数据分布在整个可能的哈希值集合中。

既然哈希表这么快，为什么MySQL 没有使用其作为索引的数据结构呢？

1.Hash 冲突问题 ：我们上面也提到过Hash 冲突了，不过对于数据库来说这还不算最大的缺点。

2.Hash 索引不支持顺序和范围查询(Hash 索引不支持顺序和范围查询是它最大的缺点： 假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了。

B树 & B+树

B 树也称 B-树,全称为 多路平衡查找树 ，B+ 树是 B 树的一种变体。B 树和 B+树中的 B 是 Balanced （平衡）的意思。

目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

B 树& B+树两者有何异同呢？

• B树的所有节点即可以有key和data，而B+树只有其叶节点可以存放key和data，其他非叶子节点就只能存放key。
• 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
• B树的搜索方式是二分查找，检索时间不定，而B+树一定是要从根节点一直搜到到叶节点，所以B+树的检索效率就很稳定了。

在 MySQL 中，MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是，两者的实现方式不太一样

MyISAM中,b+数的叶节点的data域存放的是数据记录的地址，所以在搜索的时候，查找对应的key，然后根据data域去取对应的数据地址，所以数据文件和索引文件是分开的，又称为“非聚簇索引”

InnoDB中本身数据文件就是索引结构，其中叶节点的key就是主键，而其data域就保存了完整的数据记录，因此 InnoDB 表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”，而其余为二级索引，二级索引比如唯一索引，普通索引，全文索引等，其叶节点保存的是改对应记录的主键而不是其完整的数据记录，所以要再取出其data域的主键，再去走一遍主索引，这也称为回表操作

索引类型

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。

主键索引

数据表的主键列使用的就是主键索引。

一张数据表有只能有一个主键，并且主键不能为 null，不能重复。

在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在null值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键

二级索引

二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。

唯一索引，普通索引，前缀索引等索引属于二级索引。

唯一索引(Unique Key) ：唯一索引要求所在的属性列不能重复，但是一张表可以存在多个唯一索引，所以唯一索引常常用来保证改属性列的数据不重复
普通索引(Index) ：普通索引的作用就是来查找数据，一张表李可以有多个普通索引，并允许数据重复和为NULL
前缀索引(Prefix) ：前缀索引适用于比较长的属性值的时候，需要取文本的前几位建立索引
全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引

而在在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

聚集索引

聚集索引即索引结构和数据一起存放的索引，主键索引属于聚集索引

对于InnoDB引擎来说，该表的每个非叶子节点存储索引，该叶子节点存储索引和对应的行数据

聚集索引的优点

• 查询速度非常快，根据B+树为一个多叉平衡树的性质，每个节点都是经过排序的，所以根据排序的规则，就能很快找到节点，定位到数据

聚集索引的缺点

• 依赖于有序的数据：因为B+树为一个多叉平衡树，所以在插入不是有序的数据的时候，还需要排序，根据左旋右旋重新对整个树结构进行修改，那速度就比较慢了
• 更新代价大： 如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚集索引的叶子节点还存放着数据，修改代价肯定是较大的

非聚集索引

非聚集索引即索引结构和数据分开存放的索引，二级索引属于非聚集索引。

非聚集索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据

非聚集索引的优点

• 更新代价比聚集索引要小 那就跟聚集索引相比，因为非聚集索引的叶节点不存放数据，所以修改来说，就要容易的多

非聚集索引的缺点

• 跟聚集索引一样，都依赖有序的数据
• 可能会二次查询(回表) 这也是非聚集索引的最大不足之处，当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询

覆盖索引

那有没有一种情况是非聚集索引不用回表查询呢，有的，那就是覆盖索引，即覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询，所以尽量使用覆盖索引，减少select *

比如

如主键索引，如果一条 SQL 需要查询主键，那么正好根据主键索引就可以查到主键。
再如普通索引，如果一条 SQL 需要查询 name，name 字段正好有索引， 那么直接根据这个索引就可以查到数据，也无需回表

前缀索引

当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率

单列索引与联合索引

单列索引：即一个索引只包含单个列。
联合索引：即一个索引包含了多个列

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引， 而非单列索引

最左前缀法则

最左前缀匹配原则指的是，在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成
比如

有一个联合索引，这个联合索引涉及到三个字段，顺序分别为：profession， age，status。
对于最左前缀法则指的是，查询时，最左边的列，也就是profession必须存在，否则索引全部失效。 而且中间不能跳过某一列，否则该列后面的字段索引将失效，注意这跟where后面的顺序是无关的
profession ，
profession， age，
profession， age，status

索引设计原则

（1). 针对于数据量较大，且查询比较频繁的表建立索引。
（2). 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
（3). 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
（4). 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
（5). 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，
避免回表，提高查询效率。
（6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
（7). 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。