索引知识点

最新推荐文章于 2024-08-12 16:09:28 发布

三十六煩惱風

最新推荐文章于 2024-08-12 16:09:28 发布

阅读量786

点赞数 14

文章标签：数据库

本文链接：https://blog.csdn.net/weixin_45119534/article/details/140306300

版权

文章目录

一.索引的定义
二.索引的数据结构
三.索引的优势和劣势
- 1.优势
- 2.劣势
四.索引类型
五.索引的数据结构
六.MySql索引之MyISAM索引
七.MySql索引之InnoDB索引
八.组合索引

一.索引的定义

用于提高数据存取效率的数据结构,合理的使用索引可以大大降低I/O次数,从而提高数据访问性能.
类似于现实生活中书的目录,能够加快数据库的查询速度.
索引是需要占据存储空间的,一般存储在磁盘上的文件中.

二.索引的数据结构

	B+树(多叉搜索树)

三.索引的优势和劣势

1.优势

提高数据查询效率，降低数据库的I/O成本
通过索引列对数据进行排序，降低数据排序成本,降低CPU的消耗.被索引的列会自动进行排序，包括[单列索引]和[组合索引]
利用唯一性约束，保证数据唯一性.

2.劣势

增加I/O成本(磁盘读写).
占用一定的磁盘空间.
索引不合适或者索引过多,会降低更新效率;比如每次对表进行更新操作,在更新数据的同时,还要更新对应的索引文件.

四.索引类型

1.主键索引(PRIMARY)

索引列中的值必须是唯一的,不允许有空值.当设置表中某一列为主键时,数据库会自动为该列创建主键索引.

2.普通索引(NORMAL)

最基本的索引类型,允许索引列中插入重复值和空值。

3.唯一索引(Unique)

索引列中的值必须是唯一的,但是允许为空值.

4.全文索引(FULLTEXT)-MySql

索引列的数据类型只能是CHAR,VARCHAR,TEXT等类型。

字段长度较大时,如果创建普通索引,在进行1ike模糊查询时效率比较低,这时可以创建全文索引。

MYISAM和InnoDB都可以使用全文索引

5.单列索引

针对某一列创建索引

6.组合索引

针对多列创建索引,组合索引的使用,需要遵循最左前缀匹配原则

五.索引的数据结构

1.Hash表

1.1.定义

Hash表结构-以键值对的方式存储数据.key可以存储索引列,value可以存储行记录或者行磁盘地址(rowId).

1.2.特点

Hash表在等值查询时效率很高,时间复杂度0(1).

1.3.存在的问题

不支持范围查找,范围查找时还是只能通过扫描全表方式.

2.平衡二叉树

在这里插入图片描述

2.1.定义

平衡二叉树采用二分法思维,保证每次查找都可以折半从而减少I/O次数,树的左右两个子树的层级最多相差1.

2.2.特点

在插入/删除数据时通过左旋\右旋操作保持二叉树的平衡,不会出现左子树很高、右子树很矮的情况

2.3.存在的问题

1.时间复杂度和树高相关,每个节点的读取，都对应一次磁盘I/O操作,树的高度就等于每次查询时磁盘1/O操作的次数。
2.不支持范围查询快速查找,范围查询时需要从根节点多次遍历,查找效率不高。

3.B树

3.0.原理

1.B树本质上是对二叉树的改造.

2.我们查询数据时，需要把磁盘中的数据加载到内存中,磁盘I/O操作是非常耗时的.

3.所以我们优化的重点就是尽量减少磁盘I/O操作,访问二叉树的每个节点就会发生一次I/O.

4.如果想要减少磁盘I/O操作,就要降低树的高度.

5.假如key为bigint=8字节,每个节点有两个指针，每个指针占4个字节,一个节点占用的空间为16个字节.

6.InnoDB存储引擎一次I/O操作会读取一页(16K)的数据量,而二叉树一次I/O操作有效数据量只有16字节,空间利用率极低.

7.为了最大化利用一次IO空间，一个简单的想法是在每个节点存储多个元素，在每个节点尽可能多的存储数据。

8.每个节点可以存储1000个索引（16k/16=1000），这样就将二叉树改造成了多叉树，通过增加树的叉树，将树从高瘦变为矮胖。

3.1.定义

多叉平衡树,每个节点存储着多个元素,每个内节点有多个分叉。

3.2.特点

1.节点的元素包含键值、指针、数据;

键值:代表当前记录的主键

指针:存储子节点地址信息

数据:代表当前记录除主键之外的数据

2.父节点中的元素不会出现在子节点中,所有的节点都储存数据。

3.所有叶子节点都位于同一层,叶子结点具有相同的深度,叶节点之间没有指针连接

3.4.B树结构

在这里插入图片描述

3.5.数据查询的步骤

假如我们查询值等于10的数据。查询路径磁盘块1->磁盘块2->磁盘块6。

1.第一次磁盘IO：将磁盘块1加载到内存中，在内存中从头遍历比较，10<15，走左路，到磁盘寻址磁盘块2。

2.第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比较，7<10，到磁盘中寻址定位到磁盘块6。

3.第三次磁盘IO：将磁盘块6加载到内存中，在内存中从头遍历比较，10=10，找到10，取出data，如果data存储的行记录，取出data，查询结束。如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止。

4.相比二叉平衡查找树，在整个查找过程中，虽然数据的比较次数并没有明显减少，但是磁盘IO次数会大大减少。

5.同时，由于我们的比较是在内存中进行的，比较的耗时可以忽略不计。B树的高度一般2至3层就能满足大部分的应用场景，所以使用B树构建索引可以很好的提升查询的效率。

3.6.模拟视图

在这里插入图片描述

3.7.存在的问题

1.B树不支持范围查询的快速查找，你想想这么一个情况如果我们想要查找10和35之间的数据，查找到15之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高。

2.如果data存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变大。

4.B+树

4.1.定义

B+树是对B树的升级改造.

4.2.与B树的区别

1.B树：非叶子节点和叶子节点都会存储数据。

2.B+树：只有叶子节点才会存储数据，非叶子节点只存储键值。

叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

4.3.B+树结构

在这里插入图片描述
1.B+树的最底层叶子节点包含了所有的索引项。

2.从图上可以看到，B+树在查找数据的时候，由于数据都存放在最底层的叶子节点上，所以每次查找都需要检索到叶子节点才能查询到数据。

3.所以在需要查询数据的情况下每次的磁盘的I/O跟树高有直接的关系.

4.但是从另一方面来说，由于数据都被放到了叶子节点，相比于B树来说,每个节点所存放的索引数量是会增加的，即B+树的树高理论情况下是比B树要矮的。

4.4 Demo

1.等值查询-文字步骤

假如我们查询值等于9的数据。查询路径磁盘块1->磁盘块2->磁盘块6。

1.第一次磁盘IO：将磁盘块1加载到内存中，在内存中从头遍历比较，9<15，走左路，到磁盘寻址磁盘块2。

2.第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比较，7<9<12，到磁盘中寻址定位到磁盘块6。

3.第三次磁盘IO：将磁盘块6加载到内存中，在内存中从头遍历比较，在第三个索引中找到9，取出data。

如果data存储的行记录，取出data，查询结束。如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止。

这里需要区分的是在InnoDB中Data存储的为行数据，而MyIsam中存储的是磁盘地址。

2.等值查询-图示步骤

在这里插入图片描述

3.范围查询-文字步骤

假如我们想要查找9和26之间的数据。查找路径是磁盘块1->磁盘块2->磁盘块6->磁盘块7。

1.首先查找值等于9的数据，将值等于9的数据缓存到结果集。这一步和前面等值查询流程一样，发生了三次磁盘IO。

2.查找到9之后，底层的叶子节点是一个有序列表，我们从磁盘块6，键值9开始向后遍历筛选所有符合筛选条件的数据。

3.第四次磁盘IO：根据磁盘6后继指针到磁盘中寻址定位到磁盘块7，将磁盘7加载到内存中，在内存中从头遍历比较，9<25<26，9<26<=26，将data缓存到结果集。

主键具备唯一性（后面不会有<=26的数据），不需再向后查找，查询终止。将结果集返回给用户。

4.范围查询-图示步骤

在这里插入图片描述

B+树可以保证等值和范围查询的快速查找，MySQL的索引就采用了B+树的数据结构。

六.MySql索引之MyISAM索引

1.使用场景

以一个简单的user表为例。user表存在两个索引，id列为主键索引，age列为普通索引

CREATE TABLE `user`
(
  `id`       int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `age`      int(11)     DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_age` (`age`) USING BTREE
) ENGINE = MyISAM
  AUTO_INCREMENT = 1
  DEFAULT CHARSET = utf8;

在这里插入图片描述
MyISAM的数据文件和索引文件是分开存储的。

MyISAM使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。

2.主键索引

在这里插入图片描述
表user的索引存储在索引文件user.MYI中，数据文件存储在数据文件 user.MYD中。

1.根据主键等值查询数据

select * from user where id = 28;

1.先在主键树中从根节点开始检索，将根节点加载到内存，比较28<75，走左路。（1次磁盘IO）

2.将左子树节点加载到内存中，比较16<28<47，向下检索。（1次磁盘IO）

3.检索到叶节点，将节点加载到内存中遍历，比较16<28，18<28，28=28。查找到值等于28的索引项。（1次磁盘IO）

4.从索引项中获取磁盘地址，然后到数据文件user.MYD中获取对应整行记录。（1次磁盘IO）

5.将记录返给客户端。

磁盘IO次数：3次索引检索+1次记录数据检索。

在这里插入图片描述

2.根据主键范围查询数据

select * from user where id between 28 and 47;

1.先在主键树中从根节点开始检索，将根节点加载到内存，比较28<75，走左路。（1次磁盘IO）

2.将左子树节点加载到内存中，比较16<28<47，向下检索。（1次磁盘IO）

3.检索到叶节点，将节点加载到内存中遍历比较16<28，18<28，28=28<47。查找到值等于28的索引项。(1次磁盘IO)

4.向后遍历底层叶子链表，将下一个节点加载到内存中，遍历比较，28<47=47，找到范围查询最大值,拿出所有符合条件的数据磁盘地址。（1次磁盘IO）

5.根据索引查询得到的符合条件的数据指针,按序从磁盘中读取数据并返回结果（1次磁盘IO）

磁盘IO次数：4次索引检索+1次记录数据检索。

在这里插入图片描述

3.辅助索引

1.在 MyISAM 中,辅助索引和主键索引的结构是一样的，没有任何区别,叶子节点的数据存储的都是行记录的磁盘地址。只是主键索引的键值是唯一的，而辅助索引的键值可以重复。

2.查询数据时，由于辅助索引的键值不唯一，可能存在多个拥有相同的记录，所以即使是等值查询，也需要按照范围查询的方式在辅助索引树中检索数据。

七.MySql索引之InnoDB索引

1.主键索引（聚簇索引）

1.每个InnoDB表都有一个聚簇索引，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。

2.一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。

InnoDB创建索引的具体规则如下：

1.在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。

2.如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。

3.如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。

在这里插入图片描述

等值查询

select * from user_innodb where id = 28;

等值查询-文字步骤

1.先在主键树中从根节点开始检索，将根节点加载到内存，比较28<75，走左路。（1次磁盘IO）

2.将左子树节点加载到内存中，比较16<28<47，向下检索。（1次磁盘IO）

3.检索到叶节点，将节点加载到内存中遍历，比较16<28，18<28，28=28。查找到值等于28的索引项，直接可以获取整行数据。将改记录返回给客户端。（1次磁盘IO）

磁盘IO数量：3次。

等值查询-图示步骤

在这里插入图片描述

2.辅助索引

1.除聚簇索引之外的所有索引都称为辅助索引。

2.在InnoDB中，辅助索引中的叶子节点存储的数据是该行的主键值。在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录。

在这里插入图片描述

1.底层叶子节点的按照（age，id）的顺序排序，先按照age列从小到大排序，age列相同时按照id列从小到大排序。

2.使用辅助索引需要检索两遍索引：首先检索辅助索引获得主键，然后使用主键到主索引中检索获得记录。

辅助索引-等值查询

select * from t_user_innodb where age=19;

在这里插入图片描述

3.回表查询

根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询。

磁盘IO数：辅助索引3次+获取记录回表3次

八.组合索引

以自己创建的一个表为例：表 abc_innodb，id为主键索引，创建了一个联合索引idx_abc(a,b,c)。

CREATE TABLE `abc_innodb`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a`  int(11)     DEFAULT NULL,
  `b`  int(11)     DEFAULT NULL,
  `c`  varchar(10) DEFAULT NULL,
  `d`  varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_abc` (`a`, `b`, `c`)
) ENGINE = InnoDB;

select * from abc_innodb order by a, b, c, id;

在这里插入图片描述

1.组合索引的数据结构

在这里插入图片描述

2.组合索引的查询过程

select * from abc_innodb where a = 13 and b = 16 and c = 4;

在这里插入图片描述

3.最左匹配原则

1.最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的。

2.在组合索引树中，最底层的叶子节点按照第一列a列从左到右递增排列，但是b列和c列是无序的，b列只有在a列值相等的情况下小范围内递增有序，而c列只能在a，b两列相等的情况下小范围内递增有序。

3.就像上面的查询，B+树会先比较a列来确定下一步应该搜索的方向，往左还是往右。如果a列相同再比较b列。但是如果查询条件没有a列，B+树就不知道第一步应该从哪个节点查起。

4.可以说创建的idx_abc(a,b,c)索引，相当于创建了(a)、（a,b）（a,b,c）三个索引。

5.组合索引的最左前缀匹配原则：使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配。

4.创建原则

1.在创建联合索引的时候应该把频繁使用的列、区分度高的列放在前面。

2.频繁使用代表索引利用率高，区分度高代表筛选粒度大，这些都是在索引创建的需要考虑到的优化场景，也可以在常需要作为查询返回的字段上增加到联合索引中。

3.如果在联合索引上增加一个字段而使用到了覆盖索引，那我建议这种情况下使用联合索引。

5.使用

1.考虑当前是否已经存在多个可以合并的单列索引，如果有，那么将当前多个单列索引创建为一个联合索引。

2.当前索引存在频繁使用作为返回字段的列，这个时候就可以考虑当前列是否可以加入到当前已经存在索引上，使其查询语句可以使用到覆盖索引。

6.覆盖索引

1.覆盖索引是一种很常用的优化手段。因为在使用辅助索引的时候，我们只可以拿到主键值，相当于获取数据还需要再根据主键查询主键索引再获取到数据。

2.但是试想下这么一种情况，在上面abc_innodb表中的组合索引查询时，如果我只需要abc字段的，那是不是意味着我们查询到组合索引的叶子节点就可以直接返回了，而不需要回表。这种情况就是覆盖索引。

三十六煩惱風

关注

14
点赞
踩
30

收藏

觉得还不错? 一键收藏
0
评论
索引知识点

用于提高数据存取效率的数据结构,合理的使用索引可以大大降低I/O次数,从而提高数据访问性能.类似于现实生活中书的目录,能够加快数据库的查询速度.索引是需要占据存储空间的,一般存储在磁盘上的文件中.Hash表结构-以键值对的方式存储数据.key可以存储索引列,value可以存储行记录或者行磁盘地址(rowId).平衡二叉树采用二分法思维,保证每次查找都可以折半从而减少I/O次数,树的左右两个子树的层级最多相差1.多叉平衡树,每个节点存储着多个元素,每个内节点有多个分叉。B+树是对B树的升级改造.
复制链接

扫一扫