MySQL-索引的数据结构-优快云博客

本文链接：https://blog.youkuaiyun.com/zl979899/article/details/122781666

索引介绍

1. 使用索引的目的

进行数据查找时，首先查看查询条件是否命中某条索引，符合则通过索引查找相关数据，如果不符合则需要全表扫描，即需要一条一条地查找记录，直到找到与条件符合的记录
使用索引可以减少磁盘的I/O次数

2. 索引的含义

索引是存储引擎用于快速找到数据记录的一种数据结构

3. 索引的优点

提高数据检索的效率，降低数据库的IO成本
通过创建唯一索引，可以保证数据库表中每一行数据的唯一性
可以加速表和表之间的连接。对于有依赖关系的子表和父表联合查询时，可以提高查询速度
在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗

4. 索引的缺点

创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加
索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，存储在磁盘上，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸
会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护

5. 索引按照物理实现方式分类：

聚簇（聚集）索引
非聚簇（非聚集）索引：也叫二级索引或者辅助索引

聚簇（聚集）索引

1. 特点

使用记录主键值的大小进行记录和页的排序
- 页内的记录是按照主键的大小顺序排成一个单向链表
- 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表
- 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表
B+树的叶子节点存储的是完整的用户记录，完整的用户记录指这个记录中存储了所有列的值（包括隐藏列）

2. 优点

数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
聚簇索引对于主键的排序查找和范围查找速度非常快
由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作

3. 缺点

插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，一般都会定义一个自增的ID列为主键
更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，一般定义主键为不可更新
二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据

4. 限制

对于MySQL数据库目前只有InnoDB数据引擎支持聚簇索引，而MyISAM并不支持聚簇索引
由于数据物理存储排序方式只能有一种，所以每个MySQL的表只能有一个聚簇索引。一般情况下就是该表的主键
如果没有定义主键，Innodb会选择非空的唯一索引代替。如果没有这样的索引，Innodb会隐式的定义一个主键来作为聚簇索引。
为了充分利用聚簇索引的聚簇的特性，所以innodb表的主键列尽量选用有序的顺序id，而不建议用无序的id，比如UUID、MD5、HASH、字符串列作为主键无法保证数据的顺序增长

非聚簇（非聚集）索引

1. 回表的概念

以非主键列c2列大小排序的B+树只能确定我们要查找记录的主键值
如果想根据c2列的值查找到完整的用户记录的话，仍然需要到聚簇索引中再查一遍
这个过程称为回表

2. 非聚簇索引的使用

聚簇索引只能在主键搜索时才能发挥作用，非主键搜索时就用到非聚簇索引
聚簇索引的叶子节点存储的就是我们的数据记录，非聚簇索引的叶子节点存储的是数据位置。非聚簇索引不会影响数据表的物理存储顺序
一个表只能有一个聚簇索引，因为只能有一种排序存储的方式，但可以有多个非聚簇索引，也就是多个索引目录提供数据检索
使用聚簇索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚簇索引低

3. 非聚簇索引特点：

页内的记录是按照某一非主键列的大小顺序排成一个单向链表
各个存放用户记录的页也是根据页中记录的某一非主键列的列大小顺序排成一个双向链表
存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的某一非主键列列大小顺序排成一个双向链表
B+树的叶子节点存储的并不是完整的用户记录，而只是按照某一非主键列+主键这两个列的值
目录项记录中不再是主键+页号的搭配，而变成了按照某一非主键列+页号的搭配

联合索引

联合索引：也属于二级索引
同时以多个列的大小作为排序规则，也就是同时为多个列建立索引
先以列1进行排序，列1相同时再以列2排序
此种排序规则建立的B+树称为联合索引

InnoDB中B+树注意事项

1. 根结点位置不变

每当为某个表创建一个B+树索引（聚簇索引不是人为创建的，默认就有）的时候，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个B+树索引对应的根节点中既没有用户记录，也没有目录项记录。
随后向表中插入用户记录时，先把用户记录存储到这个根节点中。
当根节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如页a中，然后对这个新页进行页分裂的操作，得到另一个新页，比如页b。这时新插入的记录根据键值（也就是聚簇索引中的主键值，二级索引中对应的索引列的值）的大小就会被分配到页a或者页b中，而根节点便升级为存储目录项记录的页

2. 内节点中目录项记录的唯一性

为了让新插入的记录找到自己的索引位置，需要保证在B+树的同一层内结点的目录项记录除页号这个字段以外是唯一的
因此目录项的内容可以由：索引列的值，主键值和页号构成

3. 一个页面最少存储2条记录

MyISAM中的索引

1. B树索引适用存储引擎

多个存储引擎支持同一种类型的索引，但是他们的实现原理不同
Innodb和MyISAM默认的索引是B+tree索引；而Memory默认的索引是Hash索引
MyISAM引擎使用 B+Tree 作为索引结构，叶子节点的data域存放的是数据记录的地址

索引 / 存储引擎	MyISAM	InnoDB	Memory
B+ Tree索引	支持	支持	支持

2. MyISAM索引原理

MyISAM的索引方案虽然也使用树形结构，但是却将索引和数据分开存储
将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件。这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录就成了。
由于在插入数据的时候并没有刻意按照主键大小排序，所以并不能在这些数据上使用二分法进行查找。
使用MyISAM 存储引擎的表会把索引信息另外存储到一个称为索引文件的文件中。MyISAM会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值+数据记录地址的组合。

3. MyISAM 与 InnoDB对比

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

索引的代价

1. 空间代价

每建立一个索引都要为它建立一棵B+树
每一棵B+树的每一个节点都是一个数据页，一个页默认会占用 16KB 的存储空间

2. 时间代价

每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引
B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表
不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表
增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收等操作来维护好节点和记录的排序

数据结构的选择

Hash结构
二叉搜索树
AVL平衡二叉树
B 树
B+树
R树

1. 为了减少IO，索引树会一次性加载吗？

数据库索引是存储在磁盘上的，如果数据量很大，必然导致索引的大小也会很大，超过几个G
利用索引查询的时候，是不可能将全部几个G的索引都加载进内存的，只能是：逐一加载每一个磁盘页，因为磁盘页对应着索引树的节点

2. B+树的存储能力如何？为何说一般查找行记录，最多只需1~3次磁盘IO

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/（8B+8B）=1K个键值
也就是说一个深度为3的B+Tree 索引可以维护1000*1000*1000=10亿条记录。（这里假定一个数据页存储1000条行记录数据）实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2～4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

3. 为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引？

B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，需要查找的关键字也就越多。相对来说B+树的IO读写次数也就降低了。
由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致B+树中每一个数据的查询效率相当。

4. Hash 索引与 B+ 树索引的区别

Hash索引不能进行范围查询，而B+树可以。这是因为Hash索引指向的数据是无序的，而B+树的叶子节点是个有序的链表。
Hash索引不支持联合索引的最左侧原则（即联合索引的部分索引无法使用），而B+树可以。对于联合索引来说，Hash 索引在计算Hash值的时候是将索引键合并后再一起计算Hash值，所以不会针对每个索引单独计算Hash值。因此如果用到联合索引的一个或者几个索引时，联合索引无法被利用。|
Hash索引不支持ORDERBY排序，因为Hash索引指向的数据是无序的，因此无法起到排序优化的作用，而B+树索引数据是有序的，可以起到对该字段ORDERBY排序优化的作用。同理，也无法用Hash索引进行模糊查询，而B+树使用LIKE进行模糊查询的时候，前缀模糊查询（比如%结尾）的话就可以起到优化作用。
InnoDB不支持哈希索引

5. Hash 索引与 B+ 树索引是在建索引的时候手动指定的吗？