Inedex与B Tree

什么是索引?

索引是一种提高表中操作速度的数据结构,类似于我们小时候常用的字典;
索引也是一种表,它将主键或索引字段的值以及所在行的数据块地址,以指针(16进制)的形式保存到实际表中;

使用索引的好处

能加快数据的查询,减少磁盘I/O,降低CPU负载;

mariadb中的索引类型:

primary key

主键索引:使用建表语句时指定了字段为PRIMARY KEY,同时会自动创建一个同名主键索引。主键索引必须唯一且不能为空,每张表可以没有主键,但是有主键的话,只能有一个主键;
比如下面的建表语句指定了id为自增主键

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL COMMENT '用户名',
  PRIMARY KEY (`id`) USING BTREE)
  ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='用户表';
unique index

唯一索引:使用建表语句时指定了字段为UNIQUE KEY,则指定该列为唯一索引,该列的值必须唯一,并且可以为空
比如下面的建表语句指定了name为唯一索引

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL COMMENT '用户名',
  PRIMARY KEY (`id`),
  UNIQUE KEY (`name`)
  )
  ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='用户表';
plain indexes

普通索引:使用建表语句时指定了字段为INDEX,则指定该列为普通索引,普通索引不需要唯一,并且可以为空
比如下面的建表语句指定了id为普通索引

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL COMMENT '用户名',
  INDEX (`id`) USING BTREE)
  ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='用户表';
fulltext indexes

全文索引:使用建表语句时指定了字段为fulltext,则指定该列为全文索引,用于全文搜索,full-text indexes只可以用于字段类型为VARCHARCHAR TEXT格式,并且无法分区表上创建全文索引。执行全文索引,需要有单独的语法支持,格式如下:

MATCH (col_name1,col_name2,...) AGAINST (expression (search_modifier))

其中MATCH()用于选择对应列,AGAINST()输入需要搜索的值,并且值的格式只能是字符串格式
创建一个表格

CREATE TABLE `err_log` (
  `log_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `add_time` datetime DEFAULT NULL COMMENT '添加时间',
  `log_info` text(255) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`log_id`) ,
  fulltext `log_info` (`log_info`) 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='错误日志表';

单列索引、多列索引(复合索引)、聚簇索引

单列索引

在一个字段上创建的索引,叫做单列索引;创建语法如下

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
[ USING BTREE | HASH ]
ON table_name
(column_1 [(length)] [DESC | ASC])

示例:创建一个唯一索引

CREATE UNIQUE INDEX `name` ON users (`name`);
多列索引(复合索引)

在多个字段上创建的索引称为多列索引,又称为复合索引,创建语法如下

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
[ USING BTREE | HASH ]
ON table_name
(column_1 [(length)] [DESC | ASC]
column_2 [(length)] [DESC | ASC]
........)

创建一个3列的复合索引

CREATE INDEX id ON users(id,`name`,birthday)

在现有表上增加索引,请注意:在线更改表结构,会造成锁表,导致无法写入数据,请在业务不繁忙的时候进行操作;

ALTER TABLE tbl_name ADD PRIMARY KEY index_name(column_list)
聚簇索引

在innodb里必须有聚簇索引
聚簇索引怎么创建?
无法创建,由innodb引擎指定

  1. 在创建表时 建表语句中定义了PRIMARY KEY ,mariadb会默认创建一个和PRIMARY KEY同名的聚簇索引;
  2. 如果没有PRIMARY KEY,mariadb会继续搜索,搜索是否有unique key,如果有并且不为空,则将其用作聚簇索引;
  3. 假如表里既没有PRIMARY KEY也没有UNIQUE KEY,那么mariadb会在内部创建包含行id值的隐藏聚集索引GEN_CLUST_INDEX
  4. 所以InnoDB表中只能有一个聚集索引

另外非聚簇索引以外的索引叫做二级索引或者非聚簇索引(又或者叫做辅助索引。。。), 在InnoDB 表中非聚集索引的每条记录都有行和列的主键列。 mariadb使用此主键值在聚集索引或二级索引中搜索行。

如何查询索引
SHOW INDEX FROM table_name;

Btree、INDEX

innodb架构图解

在mariadb中有不同的引擎,最常用的存储引擎为innodb,其使用的索引为B树索引,其他存储引擎使用的索引树分别如下:

存储引擎支持的索引树
AriaBTREE, RTREE
MyISAMBTREE, RTREE
InnoDBBTREE
MEMORY/HEAPHASH, BTREE

hash索引用在内存表,R树索引主要用来做空间计算

首先看张图,来看一下innodb在内部是如何工作的
InnoDB_int.png
查询

  1. 假如有query过来,INNODB接收到请求后,会从内存中innodb_buffer_pool里缓存的数据的页(包含表数据和index)检索是否有要查找的数据,如果有则直接返还给客户端;(逻辑读)
  2. 如果检索不到,INNODB就会从硬盘读取数据,查询的语句中使用了INDEX,那么数据会很快被查到,并且把查询到的数据缓存到innodb_buffer_pool里(方便下次使用)并返还给客户端;(物理读)
  3. 如果查询的语句中没有使用INDEX,那么INNODB便会从磁盘数据文件中开始查询数据(扫描全表),如果你的表有100W行数据,并且要查询的数据在90W行,那么磁盘I/O使用会非常大。考虑到磁盘IO是非常高昂的操作,计算机操作系统做了一些优化,当一次IO时,不光把当前磁盘地址的数据,而是把相邻的数据也都读取到内存缓冲区内,因为局部预读性原理告诉我们,当计算机访问一个地址的数据的时候,与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关,一般为4k或8k,mysql默认是16k,也就是我们读取一页内的数据时候,实际上才发生了一次IO,这个理论对于索引的数据结构设计非常有帮助。

下面这张图显示了mariadb数据存储结构
引用一张经典的mysql数据结构图,我们可以看到数据库中从具体的字段(col)到数据行(row)、页(page)、段(extent)、区(segement)、表空间(tablespece)之间的关系

image.png

B树

既然设计了这么优秀的存储结构,那么就应该有优秀的方法去检索这些数据,于是便出现了自平衡搜索树方法对数据进行检索。常用的搜索树为:二叉搜索树、AVL 树、红黑树等搜索树,以上的搜索树每个节点仅包含一个值(键)和最多两个子节点,但是有一种特殊类型的搜索树,其中一个节点包含多个值(键)和两个以上的子节点,这种搜索算法 由 Bayer 和 McCreight 于 1972 年开发,名称为 Height Balanced m-way Search Tree(高平衡m向搜索树),后来它被命名为B-Tree。

B-Tree 被称为自平衡树,因为它的节点在遍历中排序。在 B-tree 中,一个节点可以有两个以上的子节点。 B-tree 的高度为 logM N(其中“M”是树的顺序,N 是节点数)。每次更新都会自动调整高度。在 B-tree 中,数据按特定顺序排序,最小值在左侧,最大值在右侧。在 B-tree 中插入数据或键比二叉树更复杂。
B树图
image.png
第一层Root为根节点,第二层Branch为分支节点(非叶点),第三层Leaf为叶子节点

关于分支和叶子点无需多说,我们说一下root/根页面:
由于根页是在第一次创建索引时分配的,并且该页码存储在数据字典中,因此根页永远不能重定位或删除。一旦根页面填满,就需要拆分,形成一个根页面加上两个叶子页面的小树。根中的记录被移到新的页面里(根被“提升”到一个级别),并且新页被一分为二;直到新的根再次被下面的页面指针填满,然后继续重复这一过程
image.png
B树的缺点是:分支节点存储包含键值和该键值对应的数据指针(指向包含键值的磁盘文件块的指针),这样存储就大大的减少了可以存储在该节点的数据,等于变相的增加了B树的层级,从而增加了查询时间;并且在对进行表更新(INSERT/UPDATE/DELETE)时造成的迭代,会让B树逐渐失去平衡,其性能会下降。

B树总结:

  • B树的所有叶子节点必须在同一层级
  • B树的叶子节点不仅存键储数据也存储数据链表
  • B树的叶子节点之上,不应该有空的数据
  • B树的高度应该尽可能低
  • 一个节点中的所有键都是按升序排列的
B+树

B+树属于B树的扩展,B+树在分支节点仅包含索引值,而不包含数据。只有叶子点存储密钥和数据,因此使得数据指针的遍历更容易和更有效。并且叶子之间点通过链接列表连接。 ** **

叶子点不仅存放数据,而且还存放相邻的链表连接,这样查询效率更高
image.png
B+树总结:

  • B+树属于B树的扩展;
  • B+树中索引和记录存储在叶子点中。分支节点仅包含指向其他叶节点和分支节点的指针;
  • 叶子点包含指向前一个和下一个叶节点的指针(列表链接)用于索引范围遍历。

B树和B+树的主要区别:

  1. B+树只有叶子节点才会存数据,而B树中间节点也会存数据,树的查询复杂度是由树的层级决定的,从上面磁盘读写原理的介绍我们知道一个page的大小是固定的,而一个固定大小的page能够存放的数据是有限的,B+树中间节点不存数据它就可以存放更多的索引信息,这个对于查询效率的提升是非常巨大的。
    2.B+树每个叶子节点之间链接关系,而B树没有,当我们需要进行全表扫描的时候,B+树能提供更好的查询效率。
INNODB中的B+树

INNODB中 index使用的树为B+树,作为B树的延申,层级同样也分和B树一样分为:rootbranchleaf。下面我们来看一下INNODB中的B+树结构:

叶子点:
1.png
叶子点,级别为LEVEL0
对于叶子点,每条记录(包括 infimum下确界和supremum上确界系统记录)都包含一个“下一条记录”指针,该指针存储到下一条记录的偏移量(在页面内)
链表从 infimum 开始,按 key 升序链接所有记录,到 supremum 终止。
记录在页面内没有物理排序(它们占用插入时可用的任何空间);他们唯一的顺序来自他们在链表中的位置。除了链表数据,叶子点还包含除了非键值以外的行数据;

非叶子点:
2.png
非叶页子点具有相同的结构,但它们保存的data不是非键字段,而是子页面的页码指向的其他子页面上所需要的的最小页数(类似于导航)

同一级别的叶子点
大多数索引包含多个叶子点页面,因此多个页面以升序和降序链接在一起:
3.png
每一页都包含用于“上一页”和“下一页”的指针,其对于索引页被用来形成相同级别的页面的双向链接列表(INNODB中为双向列表)

单个叶子点

4.png
完整架构(简化)
5.png

  • 在叶子节点上,级别从0开始编号,向上递增
  • 同一级别的节点的页面使用双链接记录表,按照key的升序进行双向链接(左右)
  • 层与层之间的页面使用单链接记录表,按照key的升序进行单向链接(向下)
  • Infimum低于页面上任何键的值,并且始终是单链接记录列表中的第一条记录。
  • SuPreum表示高于页面上任何键的值,并且始终是单链接记录列表中的最后一条记录
  • 非叶子点包含子页的最小键和,为指向的其他子页面上所需要的的最小页数

从此可见INNODB的B+树要比普通的B+树繁琐的多,也要效率的多,普通B+树同层之间、上下层之间都是通过单向链接表进行连接,而INNODB里B+树则是同层之间使用双向链表连接进行双向连接,这样查询和更新起来效率更加高;

B树也好,B+树也好,主要目的就是提高遍历节点的速度,从而减少查询/更新数据所需要的时间

如何设计高效的INDEX

  1. 经常与其他表进行连接的表,在连接字段上应该建立索引;
  2. 经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;
  3. 索引应该建在高区分度高的字段上;
  4. 索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;
  5. 复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替:
    • 正确选择复合索引中的主列字段,一般是选择区分度高的字段;
    • 复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否极少甚至没有?如果是,则可以建立复合索引;否则考虑单字段索引;
    • 如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引;
    • 如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引;
    • 使用复合索引时,记住最左匹配原则,也就是匹配度高的字段放在最左;
  6. 频繁进行数据操作的表,不要建立太多的索引;
  7. 删除无用的索引,避免对执行计划造成负面影响;
  8. 索引不会包含有NULL值的列
  9. 可以使用索引的关联条件:
    • =、 左like 、> 、 < 、 between、少量数据的in
  10. 不可以使用索引的关联条件:
    • <>、!=、or 、大量数据的in 、左右like
    • NOT IN不会使用索引,会进行全表扫描。NOT IN可以NOT EXISTS代替
使用优化工具SQLAdvisor

如果不知道怎么入手,或者吃不准的话可以使用美团的SQL优化工具:SQLAdvisor
SQLAdvisor 是由美团点评公司北京DBA团队开发维护的 SQL 优化工具:输入SQL,输出索引优化建议。 它基于 MySQL 原生词法解析,再结合 SQL 中的 where 条件以及字段选择度、聚合条件、多表 Join 关系等最终输出最优的索引优化建议。目前 SQLAdvisor 在公司内部大量使用,较为成熟、稳定。
https://tech.meituan.com/2017/03/09/sqladvisor-pr.html

视频学习

如果实在看不懂的,这里有2个关于INDEX的视频,比喻通俗易懂,全部是干货,虽然是关于Oracle的,但是原理基本都相同的
如何创建索引:
https://mp.weixin.qq.com/s?__biz=MzI3OTM3MDkyNg==&mid=2247493965&idx=1&sn=764f3313e217b3688e73fd559b2589ed&chksm=eb4a648cdc3ded9aa603d816d3aee27ae0178d256f66be578c848433fbbdb33d0629483daeab&scene=178&cur_album_id=1975597331630604290#rd

为什么我的查询没有索引:
https://mp.weixin.qq.com/s?__biz=MzI3OTM3MDkyNg==&mid=2247493985&idx=1&sn=87c00c4af53068b220b2d754756d62e9&chksm=eb4a64a0dc3dedb6da2773da9bad89727acbf8790c498e6f218e42e744cc9689d0ed84f12e52&scene=178&cur_album_id=1975597331630604290#rd

总结

总体来说,索引属于比较抽象的东西,通过此篇文章对索引应该有个比较详细的认识了。尾部的参考连接里有大量的文档,想加深学习的同学可以继续看一下。

参考链接

https://mariadb.com/kb/en/getting-started-with-indexes/
https://mariadb.com/kb/en/full-text-index-overview/
https://mariadbtips.com/mariadb-index/
https://zhuanlan.zhihu.com/p/66553466
https://www.cnblogs.com/abcdwxc/p/9855474.html
https://www.javatpoint.com/mysql-clustered-index#:~:text=The%20essential%20characteristics%20of%20a%20clustered%20index%20are,one%20or%20more%20column%20for%20creating%20an%20index.
http://www.btechsmartclass.com/data_structures/b-trees.html
https://www.studytonight.com/advanced-data-structures/b-trees-mway-trees-data-structure
https://www.studytonight.com/advanced-data-structures/b-plus-trees-data-structure
https://www.geeksforgeeks.org/difference-between-b-tree-and-b-tree/
http://wonderdb.org/?p=157

  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值