-
+树索引的管理
-
- 索引管理
-
- Fast Index Creation
-
Online DDL
InnoDB存储引擎支持以下几种常见的索引
-
B+树索引
-
全文索引
-
自适应哈希索引
前面MySQL(八)提到过,InnoDB支持哈希索引(只不过使用哈希表去进行存储数据而已,不像B+树索引使用B+树去存储),不过是自适应的,也就是InnoDB存储引擎根据表的使用情况自动生成哈希索引,不能认为进行干预是否在一张表中生成哈希索引
B+树索引就是传统意义上的索引,最为常用也是最为有效的索引
注意:B+树索引并不能找到一个给定键值的具体行,能找到的只是被查找数据行所在的页,然后数据库通过把页读入内存,再在内存中进行查找,最后找到想要的数据
二分查找法
回看前面的数据页结构文章,叶子节点只是存储了页的为止,真正找到数据还需要根据数据页里面的Page Directory去寻找,Page Directory存放了所有行记录的在页中的相对位置,而且是用槽去存储的,而且行记录在槽中的存放是有顺序的,根据索引键值顺序进行存放,因为行记录在槽中的存放是根据索引键值顺序存放的,所以对于某一条具体记录的查询可以通过对Page Directory进行二分查找进行得到。
B+树索引
B+树数据结构就不做赘述了。
这里要注意的是:B+树索引在数据库中有一个特点是高扇出性,即指B+树的高度一般都在2~4层,这也就是说查找某一键值的行记录时最多只需要2到4次IO
现在我们就来讨论一下这个高扇出性
这个就要配合到B树去进行讲了,B树的与B+树的区别就是,B树的非叶子节点是存放索引和数据的,但B+树的非叶子节点只存放索引,数据都放在了叶子节点,然后叶子节点去形成一个双向链表,连接起来
要知道一个前提,无论是B树还是B+树,任何的节点都是一个页(无论是叶子节点还是非叶子节点),页是存储在磁盘块里面的,需要进行IO读取才可以看到。
根据树的结构,我们找到行记录存储的数据页的时间复杂度都为 O ( l o g N ) O(logN) O(logN),所以只要限制高度,就可以减少IO次数了,B+树通过牺牲非叶子节点里面存放数据的空间,拿来存放更多的索引键,让树的高度降低,从而减少了IO次数。
数据库中的B+树索引可以分为聚集索引和辅助索引(非聚集索引),两者的实现底层都是B+树,不过两者不同的是,叶子节点存放的是否是完整的行信息。
聚集索引
InnoDB存储引擎表是索引组织表,表中的行记录都是按照主键顺序进行存放的(如果没有明确定义主键,会自己生成一个16位的run_id充当主键),而聚集索引就是按照每张表的主键去构造一棵B+树,同时所有叶子节点中存放的所有数据是整张表的所有完整行记录数据(页里面不止一条行记录数据),也将聚集索引的叶子节点称为数据页,也是由于这个特性,索引组织表中的数据也是索引的一部分,每个数据页都通过一个双向链表来进行连接。
由于实际的数据页只能按照一棵B+树进行排序,因此每张表中只能拥有一个聚集索引,在多数情况下,查询优化器倾向于采用聚集索引,这时因为聚集索引能够在B+树上找到完整的行数据,此外,由于根据主键值定义了数据的逻辑顺序,聚集索引可以很快地访问针对范围值的查询
数据页上存放的是完整的每行的记录,而在非数据页(即内部节点)的索引页中,存放的仅仅是键值及指向数据页的偏移量(用于找到叶子节点的数据页),而不是一个完整的行记录
大体的结构如下
辅助索引
对于辅助索引(非聚集索引,也就是使用非主键充当索引),其实跟聚集索引的区别就是,叶子节点并不包含行记录的全部数据,相反叶子节点除了包含键值之外(用于当辅助索引的键),每个叶子节点的索引行中还包含了一个书签,书签是用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据(回表查询),也就是找到相应行数据的聚集索引键,其实就是找到聚集索引的B+树中的聚集索引键,通过聚集索引的B+树,根据聚集索引键,去找到底层叶子节点的行数据。
不过这里也并不是一定要进行回表查询,主要看查的数据,假如查的数据在辅助索引中就有(比如辅助索引的索引键值),那么就不必进行回表查询,如果没有,才需要进行回表查询,通过聚集索引,对比辅助索引这种的书签,去找到叶子节点的行数据
辅助索引的存在并不影响数据在聚集索引中的组织,因此在每一张表上都可以有多个辅助索引
当通过辅助索去寻找数据时,InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键(其实这里遍历指的是树的遍历)
假如在一棵高度为3的辅助索引树中去查找数据,那至少需要进行3次查找,才可以找到书签,如果聚集索引树的也为3,那么也至少需要3次查找,才可以找到行数据所在页,也就是说,至少要进行6次IO访问才可以找到数据。
大体结构如下(bookmark对应的就是聚集索引里面的key)
B+树索引的决裂
索引的决裂是指,不断往页中放入行数据,但页的存储容量是有限的,当行数据达到一定数目的时候,就要进行分离,使用多个页进行存储。
但分裂不是简单的B+树形成新的节点即可,B+树索引的决裂还要涉及到页的使用效率还有并发的情况。
举个栗子
比如页里面有记录如下
1、2、3、4、5、6、7、8、9
现在要新增一条记录10,超出了限制,如果按照B+树的从中间开始拆分,就会变成如下情况
数据页1:1、2、3、4、5
数据页2:6、7、8、9、10
但如果插入是按顺序进行插入的,也就是插入的数据在10往后,这就会导致数据页1大概有一半的页空间都不会再利用的,从而导致了空间的浪费,久而久之,产生数据页3,数据页2又有空间浪费,逐渐就会浪费的越来越多,所以决裂会有向左向右进行,也会中间拆分,这是有条件判断的
所以,InnoDB存储引擎会根据页里面的Page Header里面的几个部分用来保存插入信息(Page Header回看InnoDB数据页的格式)
-
PAGE_LAST_INSERT(指向往堆中最后插入的位置)
-
PAGE_DIRECTION(指向最后往堆中插入的方向)
-
PAGE_N_DIRECTION(一个方向连续插入的数量)
通过这些信息,InnoDB存储引擎会决定是向左还是向右分离,同时决定将分裂点记为哪一个,但若插入是随机的,则会取页的中间记录作为分裂点记录,从中间进行分裂
向左向右分离的规则如下
若往同一方向插入的记录数量为5(通过PAGE_N_DIRECTION判断),并且已经定位到的记录(InnoDB存储引擎在插入时,首先需要进行定位,定位到的记录为待插入记录的前一条插入记录,也就是PAGE_LAST_INSERT属性)之后还有3条行记录(插入不是按序插入的才可能会有这种情况),则分裂点的记录为定位到PAGE_LAST_INSERT后面的第三条记录,如果之后不足3条记录,那么分裂点记录就是待插入的记录
举个栗子
![在这里插入图片描述](https://img-blog.csdnimg.cn/2
【一线大厂Java面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义】
浏览器打开:qq.cn.hn/FTf 免费领取
0210512141624274.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0dEVVRfVHJpbQ==,size_16,color_FFFFFF,t_70#pic_center)
这里的话就会向右(根据PAGE_DIRECTION判断)进行决裂,最后产生的结果如下图所示
当然向左向右分裂还有另一种情况就是,PAGE_LAST_INSERT后面的记录不满足3个(很容易想象到会怎样了吧)。
裂开后
+树索引的管理
索引管理
索引的创建和删除可以通过两种方式去进行
-
一种是ALTER TABLE
-
另一种是CREATE/DROP INDEX
具体格式为
ALTER TABLE tbl_name
ADD {INDEX|KEY} [index_name]
[index_type] (index_col_name,…) [index_option] …
ALTER TABLE tbl_name
DROP PRIMARY KEY | DROP {INDEX|KEY} index_name
CREATE [UNIQUE] INDEX index_name [INDEX_TYPE]
ON tal_name (index_col_name,…)
DROP INDEX index_name on tbl_name
同时,用于也可以设置索引的部分是整个列,还是列的一部分,通过下面SQL语句实现