索引及其实现结构

最新推荐文章于 2023-06-07 22:57:00 发布

娘口三三³

最新推荐文章于 2023-06-07 22:57:00 发布

阅读量881

点赞数 2

文章标签：二叉树 mysql 数据结构

原文链接：https://www.cnblogs.com/guokaifeng/p/11272896.html#b树

版权

索引及其实现结构

索引的本质
为什么建立索引，有什么作用？
- 索引的优点
- 索引的缺点
MySQL索引结构
补充知识
mysql常用操作

索引的本质

索引（Index）是帮助MySQL高效获取数据的数据结构，
可以得到索引的本质：索引是数据结构。 可以理解为“排好序的快速查找数据结构”

为什么建立索引，有什么作用？

索引的优点

1.创建唯一性索引，保证数据库表中每一行数据的唯一性。
2. 加快数据的检索速度，这也是创建索引的最主要的原因。
3. 减少磁盘IO（向字典一样可以直接定位）。
4. 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能.
5. 加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

索引的缺点

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
索引需要占用物理空间，特别是聚集索引，需要较大的空间。
当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

MySQL索引结构

二叉树

在这里插入图片描述
二叉树是一种很经典的数据结构,二叉树是每个结点最多有两个子树的树结构。通常子树被称作“左子树”（left subtree）和“右子树”（right subtree）。
二叉树常被用于实现二叉查找树和二叉堆。二叉树有如下特性：
1、每个结点都包含一个元素以及n个子树，这里0≤n≤2。
2、左子树和右子树是有顺序的，次序不能任意颠倒。左子树的值要小于父结点，右子树的值要大于父结点。

缺点：由于每次插入是升序插入，所以每次插入都会往节点的右边插入，
最终导致二叉树左右边的长度不对等，右边远远高于左边，使得查询时io次数高查询效率慢

红黑树（平衡二叉树）

在这里插入图片描述

平衡二叉树是一种特殊的二叉树，所以他也满足前面说到的二叉树的两个特性，同时还有一个特性：
它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。

对比二叉树缩减了查询的io次数，大大提高了查询效率，但是面对海量数据查询效率慢，is次数高

Hash表

在这里插入图片描述
哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可,是无序的

优点：
等值查询，哈希索引具有绝对优势
（前提是：没有大量重复键值，如果大量重复键值时，哈希索引的效率很低，因为存在所谓的哈希碰撞问题。）

缺点：
不支持范围查询
不支持索引完成排序
不支持联合索引的最左前缀匹配规则

B± 树

B-树

在这里插入图片描述
B 树又叫平衡多路查找树。一棵m阶的B 树 (注：切勿简单的认为一棵m阶的B树是m叉树，虽然存在四叉树，八叉树，KD树，及vp/R树/R*树/R+树/X树/M树/线段树/希尔伯特R树/优先R树等空间划分树，但与B树完全不等同)的特性如下：

树中每个结点最多含有m个孩子（m>=2）；
除根结点和叶子结点外，其它每个结点至少有[ceil(m / 2)]个孩子（其中ceil(x)是一个取上限的函数）；

若根结点不是叶子结点，则至少有2个孩子（特殊情况：没有孩子的根结点，即根结点为叶子结点，整棵树只有一个根节点）；

所有叶子结点都出现在同一层，叶子结点不包含任何关键字信息(可以看做是外部接点或查询失败的接点，实际上这些结点不存在，指向这些结点的指针都为null)；

每个非终端结点中包含有n个关键字信息： (n，P0，K1，P1，K2，P2，…，Kn，Pn)。其中：
a) Ki (i=1…n)为关键字，且关键字按顺序升序排序K(i-1)< Ki。
b) Pi为指向子树根的接点，且指针P(i-1)指向子树种所有结点的关键字均小于Ki，但都大于K(i-1)。
c) 关键字的个数n必须满足： [ceil(m / 2)-1]<= n <= m-1

B树的高度
根据上面的例子我们可以看出，对于辅存做IO读的次数取决于B树的高度。而B树的高度由什么决定的呢？
若B树某一非叶子节点包含N个关键字，则此非叶子节点含有N+1个孩子结点，而所有的叶子结点都在第I层，我们可以得出：

因为根至少有两个孩子，因此第二层至少有两个节点。
除根和叶子外，其它结点至少有┌m/2┐个孩子。
在第3层至少有2*（┌m/2┐）个结点。
在第4层至少有2*（┌m/2┐^2）个结点。
在第l层至少有2*（┌m/2┐（l-2））个结点，于是有：N+1 >= 2*（┌m/2┐（l-2））。
考虑第l层的结点个数为N+1，那么2*(┌m/2┐^(l-2)）≤N+1，也就是l层的最少结点数刚好达到N+1个，即： l≤ log┌m/2┐((N+1)/2 )+2。

所以当B树包含N个关键字时，B树的最大高度为l-1（因为在计算B树高度时，叶结点所在层不计算在内），即：l-1 = log┌m/2┐((N+1)/2 )+1。
这个B树的高度公式从侧面显示了B树的查找效率是相当高的。

B+树

在这里插入图片描述

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构。B+Tree与B-Tree的结构很像，但是也有几个自己的特性：

1、所有的非叶子节点只存储关键字信息。
2、所有具体数据都存在叶子结点中。
3、所有的叶子结点中包含了全部元素的信息。
4、所有叶子节点之间都有一个链指针。

b+树性质

1.索引字段要尽量的小：通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，
每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，
磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，
即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，
一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。

2.索引的最左匹配特性：当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，
比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，
最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，
必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，
所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了，这个是非常重要的性质，即索引的最左匹配特性。

补充知识

为什么说B±tree比B 树更适合实际应用中操作系统的文件索引和数据库索引？
1）B+树的磁盘读写代价更低
B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对于B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。
举个例子，假设磁盘中的一个盘块容纳16bytes，而一个关键字2bytes，一个关键字具体信息指针2bytes。一棵9阶B-tree(一个结点最多8个关键字)的内部结点需要2个盘快。而B+ 树内部结点只需要1个盘快。当需要把内部结点读入内存中的时候，B 树就比B+ 树多一次盘块查找时间(在磁盘中就是盘片旋转的时间)。
2) B±tree的查询效率更加稳定
由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

B+树索引和哈希索引的区别
B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接，是有序的，如下图：
在这里插入图片描述

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可,是无序的，如下图所示：
在这里插入图片描述

B树和B+树的区别
1、B树，每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为null，叶子结点不包含任何关键字信息。
在这里插入图片描述

2、B+树，所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接
所有的非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）关键字。 (而B 树的非终节点也包含需要查找的有效信息)
在这里插入图片描述

mysql常用操作

索引功能

索引的功能就是加速查找
mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能

常用的索引

普通索引INDEX：加速查找

唯一索引：
-主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）
-唯一索引UNIQUE:加速查找+约束（不能重复）

联合索引：
-PRIMARY KEY(id,name):联合主键索引
-UNIQUE(id,name):联合唯一索引
-INDEX(id,name):联合普通索引

举个例子来说，比如你在为某商场做一个会员卡的系统。

这个系统有一个会员表
有下列字段：
会员编号 INT
会员姓名 VARCHAR(10)
会员身份证号码 VARCHAR(18)
会员电话 VARCHAR(10)
会员住址 VARCHAR(50)
会员备注信息 TEXT

那么这个会员编号，作为主键，使用 PRIMARY
会员姓名如果要建索引的话，那么就是普通的 INDEX
会员身份证号码如果要建索引的话，那么可以选择 UNIQUE （唯一的，不允许重复）

#除此之外还有全文索引，即FULLTEXT
会员备注信息，如果需要建索引的话，可以选择全文搜索。
用于搜索很长一篇文章的时候，效果最好。
用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。
但其实对于全文搜索，我们并不会使用MySQL自带的该索引，而是会选择第三方软件如Sphinx，专门来做全文搜索。

#其他的如空间索引SPATIAL，了解即可，几乎不用

索引的两大类型hash与btree

我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

创建/删除索引的语法

#方法一：创建表时
　　CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC])
);

#方法二：CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]) ;

#方法三：ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名 (字段名[(长度)] [ASC |DESC]) ;

#删除索引：DROP INDEX 索引名 ON 表名字;
#方式一
create table t1(
id int,
name char,
age int,
sex enum(‘male’,‘female’),
unique key uni_id(id),
index ix_name(name) #index没有key
);
create table t1(
id int,
name char,
age int,
sex enum(‘male’,‘female’),
unique key uni_id(id),
index(name) #index没有key
);

#方式二
create index ix_age on t1(age);

#方式三
alter table t1 add index ix_sex(sex);
alter table t1 add index(sex);

#查看
mysql> show create table t1;
| t1 | CREATE TABLE t1 (
id int(11) DEFAULT NULL,
name char(1) DEFAULT NULL,
age int(11) DEFAULT NULL,
sex enum(‘male’,‘female’) DEFAULT NULL,
UNIQUE KEY uni_id (id),
KEY ix_name (name),
KEY ix_age (age),
KEY ix_sex (sex)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

测试索引

#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

#2. 创建存储过程，实现批量插入记录
delimiter $KaTeX parse error: Expected 'EOF', got '#' at position 2: #̲声明存储过程的结束符号为$
create procedure auto_insert1()
BEGIN
declare i int default 1;
while(i<3000000)do
insert into s1 values(i,‘eva’,‘female’,concat(‘eva’,i,’@oldboy’));
set i=i+1;
end while;
END $KaTeX parse error: Expected 'EOF', got '#' at position 2: #̲$ 结束
delimiter ; #重新声明分号为结束符号

#3. 查看存储过程
show create procedure auto_insert1\G

#4. 调用存储过程
call auto_insert1();

在没有索引的前提下测试查询速度
#无索引：mysql根本就不知道到底是否存在id等于333333333的记录，只能把数据表从头到尾扫描一遍，
此时有多少个磁盘块就需要进行多少IO操作，所以查询速度很慢
mysql> select * from s1 where id=333333333;
Empty set (0.33 sec)
在这里插入图片描述

总结

#1. 一定是为搜索条件的字段创建索引，比如select * from s1 where id = 333;就需要为id加上索引

#2. 在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快
比如create index idx on s1(id);会扫描表中所有的数据，然后以id为数据项，创建索引结构，存放于硬盘的表中。
建完以后，再查询就会很快了。

#3. 需要注意的是：innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文件table1.MYI

MySAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在innodb中，表数据文件本身就是按照
B+Tree（BTree即Balance True）组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，
因此innodb表数据文件本身就是主索引。
因为inndob的数据文件要按照主键聚集，所以innodb要求表必须要有主键（Myisam可以没有），如果没有显式定义，
则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则mysql会自动为innodb表生成一个隐含字段作为主键，
这字段的长度为6个字节，类型为长整型.

娘口三三³

关注

2
点赞
踩
7

收藏

觉得还不错? 一键收藏
1
评论
索引及其实现结构

索引及其实现结构索引的本质为什么建立索引，有什么作用？索引的优点索引的缺点MySQL索引结构二叉树红黑树（平衡二叉树）Hash表B+- 树B-树B+树补充知识mysql常用操作索引功能常用的索引索引的两大类型hash与btree创建/删除索引的语法测试索引总结索引的本质索引（Index）是帮助MySQL高效获取数据的数据结构，可以得到索引的本质：索引是数据结构。可以理解为“排好序的快速查找数据结构”为什么建立索引，有什么作用？索引的优点1.创建唯一性索引，保证数据库表中每一行数据的唯一性。
复制链接

扫一扫