精华内容
下载资源
问答
  • 深入理解 MySQL 索引底层原理 Mysql 作为互联网中非常热门的数据库,其底层的存储引擎和数据检索引擎的设计非常重要,尤其是 Mysql 数据的存储形式以及索引的设计,决定了 Mysql 整体的数据检索性能。 何为索引 我们...
  • 想进大厂,这些MySQL索引底层知识你必须掌握

    万次阅读 多人点赞 2021-09-03 14:45:12
    想进大厂,这些MySQL索引底层知识你必须掌握

    💂 个人主页: Java程序鱼
    🤟 准备精心打造一套精品MySQL课程,大家可以持续关注
    💬 如果文章对你有帮助、欢迎关注、点赞、收藏(一键三连)和订阅专栏哦
    💅 有任何问题欢迎私信,看到会及时回复!

    序号内容链接地址
    0MySQL面试题https://blog.csdn.net/qq_35620342/article/details/119930887
    1MySQL基础总结https://blog.csdn.net/qq_35620342/article/details/120081524
    2MySQL索引https://blog.csdn.net/qq_35620342/article/details/120081558
    3MySQL事务待分享
    4MySQL锁机制待分享
    5MySQL日志(redo log、undo log、binlog)待分享
    6主从复制原理待分享
    7主从延迟解决方案待分享
    8SQL语句查询原理待分享
    9MySQL海量数据优化待分享
    10SQL优化待分享
    11数据库与缓存双写一致性方案待分享
    12分布式锁待分享


    一、索引是什么?

    • 索引是存储引擎用于快速检索记录行的一种数据结构,用于提升数据库的查找速度。
    • 索引一般存储在磁盘的文件中,它是占用物理空间的。
    • 适当的索引能提高查询效率,过多的索引会影响数据库表的插入和更新性能

    为什么需要使用索引:

    ①索引能极大的减少存储引擎需要扫描的数据量

    ②索引可以把随机I/O变为顺序I/O

    ③索引可以帮助我们进行分组、排序等操作时,避免使用临时表

    索引对于良好的性能非常关键,尤其是当表中的数据量越来越大时,索引对性能的影响愈发重要,在数据量较小且负载较低时,不恰当的索引对性能的影响可能还不明显,但当数据量逐渐增大时,性能则会急剧下降。

    索引优化应该是查询性能优化最有效的手段了(可以起到立竿见影的效果),索引能够轻易将查询性能提高几个数量级,’最优’的索引有时比一个‘好的’索引性能要好两个数量级,创建一个真正’最优’的索引经常需要重写查询。

    二、索引有哪些类型?

    在这里插入图片描述

    1.数据结构维度

    (1)B+树索引:所有数据存储在叶子节点,复杂度为O(logn),适合范围查询。

    (2)哈希索引: 适合等值查询,检索效率高。

    哈希索引基于哈希表实现,只有精确匹配索引所有列的查询才有效。对于每一行数据,存储引擎都会对所有的索引列计算一个哈希码,哈希码是一个较小的值,并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中,同时在哈希表中保存指向每个数据行的指针。

    但是范围查找不适合,因为存储引擎都会为每一行计算一个hash码,hash码都是比较小的,并且不同键值行的hash码通常是不一样的,hash索引中存储的就是Hash码,hash 码彼此之间是没有规律的,且 Hash 操作并不能保证顺序性,所以值相近的两个数据,Hash值相差很远,被分到不同的桶中。这就是为什么hash索引只能进行全职匹配的查询,因为只有这样,hash码才能够匹配到数据。

    在MySQL中,只有Memory引擎显示支持哈希索引,这是Memory引擎的默认索引,Memory引擎同时也支持B-Tree索引,指得一提的是,Memory引擎是支持非唯一哈希索引的,如果多个列的哈希值相同,索引会以链表的方式存放多个记录指针到同一个哈希条目中。HASH时间复杂度O(1),链表时间复杂度是O(n)

    InnoDB支持Hash索引吗?

    • InnoDB用户无法手动创建哈希索引,这一层上说,InnoDB确实不支持哈希索引;
    • InnoDB会自调优(self-tuning),如果判定建立自适应哈希索引(Adaptive Hash Index, AHI),能够提升查询效率,InnoDB自己会建立相关哈希索引,这一层上说,InnoDB又是支持哈希索引的;

    (3)全文索引:只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时,如果创建普通索引,在进行like模糊查询时效率比较低,这时可以创建全文索引。 MyISAM和InnoDB中都可以使用全文索引。

    2.物理存储维度

    (1)聚簇索引(主键索引):每个InnoDB表都有一个聚簇索引 ,聚簇索引使用B+树构建,叶子节点存储的数据是整行记录。一般情况下,聚簇索引等同于主键索引,当一个表没有创建主键索引时,InnoDB会自动创建一个ROWID字段来构建聚簇索引。InnoDB创建索引的具体规则如下:

    • 如果表定义了PK,则PK就是聚集索引;
    • 如果表没有定义PK,则第一个非空unique列是聚集索引;
    • 否则,InnoDB会创建一个隐藏的row-id作为聚集索引;

    (2)非聚簇索引(二级索引):非聚簇索引就是以非主键创建的索引,叶子节点存储的是主键和索引列。

    3.逻辑维度

    (1)主键索引:主键索引一般都是在创建表的时候指定,「一个表只有一个主键索引」,特点是「唯一、非空」。

    (2)普通索引:普通索引唯一的作用就是加快查询。

    (3)组合索引:组合索引是创建一个「多个字段的索引」,这个概念是相对于上上面的单列索引而言,组合索引查询遵循「最左前缀原则」。

    (4)唯一索引:唯一索引具有的特点就是唯一性,可以在创建表的时候指定,也可以在创建表后创建。

    (5)空间索引:MySQL在5.7之后的版本支持了空间索引,而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。

    三、为什么选择B+树作为索引结构?

    可以从几个维度去看这个问题,查询是否够快,效率是否稳定,存储数据多少,以及查找磁盘次数,为什么不是二叉树,为什么不是平衡二叉树,为什么不是B树,而偏偏是B+树呢?

    1.为什么不使用哈希结构?

    我们知道哈希结构,类似k-v结构,也就是,key和value是一对一关系。它用于「等值查询」还可以,但是范围查询它是无能为力的哦。

    2.为什么不使用二叉树呢?

    • 当数据量大时,树的高度会比较高(树的高度决定着它的IO操作次数,IO操作耗时大),查询会比较慢。
    • 每个磁盘块(节点/页)保存的数据太小(IO本来是耗时操作,每次IO只能读取到一个关键字,显然不合适)
    • 如果二叉树特殊化为一个链表,相当于全表扫描

    没有很好的利用操作磁盘IO的数据交换特性,也没有利用好磁盘IO的预读能力(空间局部性原理),从而带来频繁的IO操作。

    在这里插入图片描述

    3.为什么不使用B树呢?

    B树的搜索:从根节点开始,对节点内的关键字(有序)序列进行二分查找,如果命中则结束,否则进入查询关键字所属范围的儿子节点;重复,直到所对应的儿子指针为空,或已经是叶子节点。 关键字集合分布在整颗树中 ,即叶子节点和非叶子节点都存放数据,搜索可能在非叶子节点结束。其搜索性能等价于在关键字全集内做一次二分查找。

    在这里插入图片描述

    假设检索26,先把磁盘块1加载到内存中,然后26与28和46比较,26比28小,然后基于P1子节点引用,P1是指向磁盘块2的一个指针地址,基于P1引用可以通过顺序IO快速加载磁盘块2,然后26与19和23比,26大于23,通过P3子节点引用,加载磁盘块7。然后命中,基于节点数据区加载数据。

    B树的特点:

    • 不再是二叉搜索,而是m叉搜索;
    • 叶子节点,非叶子节点,都存储数据;
    • 中序遍历,可以获得所有节点;

    名词解释:

    局部性原理:软件设计要尽量遵循 “数据读取集中”与“使用到一个数据,大概率会使用其附近的数据”,这样磁盘预读能充分提高磁盘IO;

    磁盘预读能力:磁盘读写并不是按需读取,而是按页预读,一次会读一页的数据,每次加载更多的数据,如果未来要读取的数据就在这一页中,可以避免未来的磁盘IO,提高效率;

    数据交换特性:操作系统去硬盘读取一次,做一次I/O交换,一次交换数据是4k(Linux默认页大小),交换单位以页为单位,1页就是4k(索引按数据页为单位读写的,在InnoDB中,每个数据页的大小默认是16KB)

    3.为什么使用B+树?

    它是B-Tree数的变体,也是一种多路搜索树B+Tree和B-Tree基本相同,区别在于B-Tree树非叶子节点和叶子节点都可以存放数据,而B+Tree树关键字存储在叶子节点上,非叶子节点不存真正的数据。(B+树中根到每一个节点的路径长度一样,因此查询速度更稳定;而B树不是这样)

    叶子之间,增加了链表,获取所有节点,不再需要中序遍历,直接遍历叶子节点就行;

    在这里插入图片描述

    比如查找28,其实图顶端的28是索引,并不是真实数据,他会继续往下找。

    B+Tree与B-Tree比较

    ①B+Tree范围查找,定位min与max之后,中间叶子节点,就是结果集,不用中序回溯;

    ②B+Tree磁盘读写能力更强(叶子节点不保存真实数据,因此一个磁盘块能保存的关键字更多,因此每次加载的关键字越多)

    ③B+Tree扫表和扫库能力更强(B-Tree树需要扫描整颗树,B+Tree树只需要扫描叶子节点)

    四、B+树索引搜索过程

    准备数据:

    CREATE TABLE `employee` (
      `id` int(11) NOT NULL,
      `name` varchar(255) DEFAULT NULL,
      `age` int(11) DEFAULT NULL,
      `date` datetime DEFAULT NULL,
      `sex` int(1) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_age` (`age`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    insert into employee values(1,'小红',43,'2021-01-20','0');
    insert into employee values(2,'铁蛋',48,'2021-01-21','0');
    insert into employee values(3,'张三',36,'2020-01-21','1');
    insert into employee values(4,'李四',32,'2020-01-21','0');
    insert into employee values(5,'王老五',37,'2020-01-21','1');
    insert into employee values(6,'赵六',49,'2021-01-21','0');
    insert into employee values(7,'小丑',28,'2021-01-21','1');
    

    索引结构图:
    在这里插入图片描述
    在这里插入图片描述
    (1)非聚簇索引(二级索引)

    select * from employee where age=32;
    

    这条 SQL 查询语句执行流程:

    • 搜索idx_age索引树,将磁盘块1加载到内存,由于32<37,搜索左路分支,到磁盘寻址磁盘块2。
    • 将磁盘块2加载到内存中,在内存继续遍历,找到age=32的记录,取得id = 4.
    • 拿到id=4后,回到id主键索引树。
    • 搜索id主键索引树,将磁盘块1加载内存,在内存遍历,找到了4,但是B+树索引非叶子节点是不保存数据的。索引会继续搜索4的右分支,到磁盘寻址磁盘块3.
    • 将磁盘块3加载内存,在内存遍历,找到id=4的记录,拿到R4这一行的数据,好的,大功告成。

    什么是回表查询呢?回表查询简单来说「通过二级索引查询数据,得不到完整的数据行,只能拿到主键ID,需要再次查询主键索引来获得数据行」。

    (2)聚簇索引(主键索引)

    select * from employee where id = 4;
    

    这条 SQL 查询语句执行流程:

    • 搜索id主键索引树,将磁盘块1加载内存,在内存遍历,找到了4,但是B+树索引非叶子节点是不保存数据的。索引会继续搜索4的右分支,到磁盘寻址磁盘块3.
    • 将磁盘块3加载内存,在内存遍历,找到id=4的记录,拿到R4这一行的数据,好的,大功告成。

    五、覆盖索引

    大家想一个问题,如果不用select *, 而是使用select id,age,执行几次树搜索操作呢?

    在这里插入图片描述

    select id,age from employee where age=32;
    

    这条 SQL 查询语句执行流程:

    • 搜索idx_age索引树,将磁盘块1加载到内存,由于32<37,搜索左路分支,到磁盘寻址磁盘块2。
    • 将磁盘块2加载到内存中,在内存继续遍历,找到age=32的记录,取得id = 4.
    • 拿到id=4后,id和age的值都有了,此时就不会去主键索引树查询了

    覆盖索引:在查询的数据列里面,不需要回表去查,直接从索引列就能取到想要的结果。换句话说,你SQL用到的索引列数据,覆盖了查询结果的列,就算上覆盖索引了。

    六、索引哪些情况会失效?

    • 查询条件包含or,会导致索引失效。
    • 隐式类型转换,会导致索引失效,例如age字段类型是int,我们where age = “1”,这样就会触发隐式类型转换。
    • like通配符会导致索引失效。注意:"ABC%“会走range索引,”%ABC"索引才会失效。
    • 联合索引,查询时的条件列不是联合索引中的第一个列,索引失效。
    • 对索引字段进行函数运算。
    • 对索引列运算(如,+、-、*、/),索引失效。
    • 索引字段上使用(!= 或者 < >,not in)时,会导致索引失效。
    • 索引字段上使用is null, is not null,可能导致索引失效。
    • 相join的两个表的字符编码不同,不能命中索引,会导致笛卡尔积的循环计算
    • mysql估计使用全表扫描要比使用索引快,则不使用索引。
    展开全文
  • 掌握Mysql索引底层原理让你轻松涨薪5K
  • 主要介绍了MySQL8新特性之降序索引底层实现详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧
  • BAT面试深入理解Mysql索引底层数据结构与算法_1
  • BAT面试_深入理解Mysql索引底层数据结构与算法_4
  • BAT面试_深入理解Mysql索引底层数据结构与算法_5
  • BAT面试_深入理解Mysql索引底层数据结构与算法_6
  • BAT面试_深入理解Mysql索引底层数据结构与算法_7
  • BAT面试_深入理解Mysql索引底层数据结构与算法_8
  • BAT面试_深入理解Mysql索引底层数据结构与算法_9
  • 本文介绍关于 Oracle 索引的结构。大概了解 Oracle 索引底层的数据结构,从而更好地理解 Oracle 索引对增、删、改、查的性能
  • 诸葛_BAT面试_深入理解Mysql索引底层数据结构与算法_2
  • 诸葛_BAT面试_深入理解Mysql索引底层数据结构与算法_3
  • MySQL索引底层数据结构索引到底是什么联合索引结构MyISAM索引文件和数据文件是分离的主键索引普通索引InnoDB索引实现主键索引普通索引 索引到底是什么 索引是帮助MySQL高效获取数据的 排好序 的 数据结构 索引存储...

    索引到底是什么

    • 索引是帮助MySQL高效获取数据的 排好序数据结构
    • 索引存储在文件,MySQL使用的数据结构为 B+Tree
      在这里插入图片描述

    数据结构教学网站:https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
    B-Tree 数据结构:https://mp.weixin.qq.com/s?__biz=MzIxMjE5MTE1Nw==&mid=2653190965&idx=1&sn=53f78fa037386f85531832cd5322d2a0&chksm=8c9909efbbee80f90512f0c36356c31cc74c388c46388dc2317d43c8f8597298f233ca9c29e9&scene=21#wechat_redirect
    B+Tree 数据结构:https://mp.weixin.qq.com/s?__biz=MzIxMjE5MTE1Nw==&mid=2653191027&idx=1&sn=4ba22e3ec8bd149f69fc0aba72e4347e&chksm=8c9909a9bbee80bfa1d8497ff0525df130414c1731b5aa5287bf16ea1cf86c8d8e6f20782184&scene=21#wechat_redirect

    联合索引结构

    在这里插入图片描述

    聚集索引和非聚集索引

    根本区别

    • 数据的物理存放顺序与索引顺序是否一致

    MyISAM和InnoDB的索引

    • MyISAM的B+Tree的叶子节点上的data,并不是数据本身,而是数据存放的地址。主索引和辅助索引没啥区别,只是主索引中的key一定得是唯一的。这里的索引都是非聚集索引。

    • InnoDB的数据文件本身就是索引文件,B+Tree的叶子节点上的data就是数据本身,key为主键,这是聚集索引。非聚集索引,叶子节点上的data是物理地址(所以聚集索引的key,不能过长)。为什么存放的主键,而不是记录所在地址呢,理由相当简单,因为记录所在地址并不能保证一定不会变,但主键可以保证。

    • 至于为什么主键通常建议使用自增id呢?

    MyISAM索引文件和数据文件是分离的(非聚集)

    主键索引

    在这里插入图片描述

    普通索引

    在这里插入图片描述

    InnoDB索引实现(聚集)

    主键索引

    在这里插入图片描述

    普通索引

    在这里插入图片描述

    1. 数据文件本身就是按B+Tree组织的一个索引结构文件
    2. 索引-叶子节点包含了完整的数据记录
    3. 为什么InnoDB表必须有主键,并且推荐使用整型的自增主键?
    4. 为什么非主键索引结构叶子节点存储的是主键值?(一致性和节省存储空间)
    展开全文
  • 深入理解MySQL索引底层数据结构与算法

    万次阅读 多人点赞 2018-10-10 11:10:58
    目录 ... 联合索引底层存储结构 一 理解索引的特性 索引是帮助MySQL高效获取数据的排好序的数据结构 索引存储在文件里 二 索引的各种存储结构及其优缺点 在开始讲这一小节之前,我们先来看一...

    目录

    一 理解索引的特性

    二 索引的各种存储结构及其优缺点

    (一) 二叉树

    (二) 红黑树

    (三) Hash

    (四) B-Tree

    (五) B+Tree(MySQL索引的真正存储结构)

    三. 联合索引底层存储结构


    一 理解索引的特性

    • 索引是帮助MySQL高效获取数据的排好序数据结构
    • 索引存储在文件里

    二 索引的各种存储结构及其优缺点

    在开始讲这一小节之前,我们先来看一下在数据库没有加索引的情况下,SQL中的where字句是如何查找目标记录的。

    我们先看下左边表格第二列Col2列的数据时如何查找的,如果我们希望查找where Col2 = 22的记录,我们在没加索引的情况下是按顺序从第一条记录查找,由此可知需要查找5次才能找到;

    如果对Col2字段加上索引后,我们假设使用最简单的二叉树作为索引存储方式,再次查找where Col2 = 22的记录这次只需要查找2次就能找到目标记录,效率提高十分明显。

    (一) 二叉树

    1. 优点:

    二叉树是一种比顺序结构更加高效地查找目标元素的结构,它可以从第一个父节点开始跟目标元素值比较,如果相等则返回当前节点,如果目标元素值小于当前节点,则移动到左侧子节点进行比较,大于的情况则移动到右侧子节点进行比较,反复进行操作最终移动到目标元素节点位置。

    2. 缺点:

    在大部分情况下,我们设计索引时都会在表中提供一个自增整形字段作为建立索引的列,在这种场景下使用二叉树的结构会导致我们的索引总是添加到右侧,在查找记录时跟没加索引的情况是一样的,如下图所示:

    (二) 红黑树

    1. 优点:

    红黑树也叫平衡二叉树,它不仅继承了二叉树的优点,而且解决了上面二叉树遇到的自增整形索引的问题,从下面的动态图中可以看出红黑树会走动对结构进行调整,始终保证左子节点数 < 父节点数 < 右子节点数的规则。

    2. 缺点:

    在数据量大的时候,深度也很大。从图中可以看出每个父节点只能存在两个子节点,如果我们有很多数据,那么树的深度依然会很大,可能就会超过十几二十层以上,对我们的磁盘寻址不利,依然会花费很多时间查找。

    (三) Hash

    1. 优点:

    对数据进行Hash(散列)运算,主流的Hash算法有MD5、SHA256等等,然后将哈希结果作为文件指针可以从索引文件中获得数据的文件指针,再到数据文件中获取到数据,按照这样的设计,我们在查找where Col2 = 22的记录时只需要对22做哈希运算得到该索引所对应那行数据的文件指针,从而在MySQL的数据文件中定位到目标记录,查询效率非常高。

    2. 缺点:

    无法解决范围查询(Range)的场景,比如 select count(id) from sus_user where id >10;因此Hash这种索引结构只能针对字段名=目标值的场景使用。

    不适合模糊查询(like)的场景。

    (四) B-Tree

    既然红黑树存在缺点,那么我们可以在红黑树的基础上构思一种新的储存结构。解决的思路也很简单,既然觉得树的深度太长,就只需要适当地增加每个树节点能存储的数据个数即可,但是数据个数也必须要设定一个合理的阈值,不然一个节点数据个数过多会产生多余的消耗。

    按照这样的思路,我们先来了解下关于B-Tree的一些知识点:

    • 度(Degree)-节点的数据存储个数,每个树节点中数据个数大于 15/16*Degree(未验证) 时会自动分裂,调整结构
    • 叶节点具有相同的深度,左子树跟右子树的深度一致
    • 叶节点的指针为空
    • 节点中的数据key从左到右递增排列

    1. 树节点结构:

    在这里需要说明下的是,BTree的结构里每个节点包含了索引值和表记录的信息,我们可以按照Map集合这样理解:key=索引,value=表记录,如下图所示:

    2. 优点:

    BTree的结构可以弥补红黑树的缺点,解决数据量过大时整棵树的深度过长的问题。相同数量的数据只需要更少的层,相同深度的树可以存储更多的数据,查找的效率自然会更高。

    3. 缺点:

    从上面得知,在查询单条数据是非常快的。但如果范围查的话,BTree结构每次都要从根节点查询一遍,效率会有所降低,因此在实际应用中采用的是另一种BTree的变种B+Tree(B+树)。

    (五) B+Tree(MySQL索引的真正存储结构)

    在介绍B+Tree之前,我们先来看下面两个问题:

    1. 为什么要对BTree继续做优化?

    要解答这个疑问需要先了解BTree每个节点结构(上面已经说明)和MySQL数据库它是如何读取索引数据的,索引和表数据在不使用的时候是存储在文件中的,也就是磁盘,当我们执行查询操作时会DBMS(数据库管理系统)首先会先从内存中查找,如果找到直接使用,如果找不到则从磁盘文件中读取;操作系统储存数据的最小单位是页(page),一页假设是4K大小(由操作系统决定),对内存和磁盘读取数据是按一页的整数倍读取的。

    这里我们假设数据库一次IO操作就读取1页4K的数据,再假设图中圈起来的元素就是一个大节点,内含多个小节点的索引和数据,其大小是10MB,那么我们要从磁盘中读取完整个大节点需要进行 10M / 4K = 2500次IO操作,这样就可以看出如果大节点数据总量越大,需要执行的IO操作越多,花费的时间也越长,因此为了提高性能,数据库会建议我们一个大节点只存储一页4K大小的数据,这里的数据包含了索引和表记录,另外我们还能计算出树的度Degree应该设置成多大才合理:

    Degree = 内存页大小(4K) / 单个索引值字节大小;

    进一步分析,索引值的大小相对于整条记录的大小是很小的,如果我们需要查找的数据刚好是在最后,那么前面遍历过的节点中存储的记录数据是不是对我们来说是没用的,它会占用比索引大得多的空间,导致我们一个大节点里能遍历的索引数量大大减少,需要向下继续遍历的几率就更大,花费更多时间查找,那么有没有办法可以优化呢?看下一个问题。

    2. 相对于BTree,B+Tree做了哪些优化?

    • B+Tree存储结构,只有叶子节点存储数据

    新的B+树结构没有在所有的节点里存储记录数据,而是只在最下层的叶子节点存储,上层的所有非叶子节点只存放索引信息,这样的结构可以让单个节点存放下更多索引值,增大度Degree的值,提高命中目标记录的几率。

    这种结构会在上层非叶子节点存储一部分冗余数据,但是这样的缺点都是可以容忍的,因为冗余的都是索引数据,不会对内存造成大的负担。

    • 每个叶子节点都指向下一个叶子节点

    这点优化有什么用呢?我们直接看下面的B+Tree结构,如果我们进行范围查找where id > 4的记录,我们只需要先找到id = 4的记录后自然就能通过叶子节点间的双向指针方便地查询出大于4的所有记录。

    三. 联合索引底层存储结构

    单列索引其实也可以看做联合索引,索引列为1的联合索引,从下图就可以看出联合索引的底层存储跟单列索引时类似的,区别在于联合索引是每个树节点中包含多个索引值,在通过索引查找记录时,会先将联合索引中第一个索引列与节点中第一个索引值进行匹配,匹配成功接着匹配第二个索引列和索引值,直到联合索引的所有索引列都匹配完;如果过程中出现某一个索引列与节点相应位置的索引值不匹配的情况,则无需再匹配节点中剩余索引列,前往下一个节点。

    感谢大家的阅读,本文如有错误的地方,希望能私信我改正,共同进步!

    下一篇文章打算介绍两种常见的数据库存储引擎InnoDB和MyISAM

    展开全文
  • MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干,就可以得到索引的本质:索引是数据结构。我们知道,数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽...
  • 深入解析了Mysql的B+Tree索引底层数据结构,以及MyISAM和InnoDB 存储引擎的索引底层原理。

    深入解析了Mysql的B+Tree索引底层数据结构,以及MyISAM和InnoDB 存储引擎的索引底层原理。

    上一篇文章中,我们介绍了索引的概念以及MySQL常见索引类型:索引的概念以及MySQL七种索引类型。下面我们来看看常见的索引结构的底层实现原理。包括B-Tree、B+Tree的数据结构,以及MyISAM和InnoDB 存储引擎对于B+Tree索引的具体实现。

    1 索引的数据结构

    索引有多种数据结构,在MySQL中,索引是在存储引擎层而不是服务器层实现的。所以,并没有统一的索引标准:不同存储引擎的索引的工作方式不一样,也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一类型的索引,其底层实现也可能不同。

    一般来说常见的索引结构是HASH和BTREE
    在这里插入图片描述

    2 B-Tree索引的介绍

    如果在谈论索引的时候没有指明类型,那么多半说的是B-Tree索引,它使用B-Tree数据结构来存储数据,大多数存储引擎都支持这种索引。

    不过,虽然大多都支持B-Tree索引,但是底层的存储引擎也可能使用不同的存储结构,例如NDB集群存储引擎内部实际上使用了T-Tree结构存储这种索引,即使其名字是BTREE;而InnoDB和MyISAM则使用的是B+Tree结构(实际上很多存储引擎使用的是B+Tree,即每个叶子节点都包含指向下一个叶子节点的指针,从而方便叶子节点的范围遍历),但其名字也还是叫BTREE

    我们通常所说聚集索引、覆盖索引、组合索引、前缀索引、普通索引、唯一索引等,没有特别说明,默认都是使用B+Tree结构的索引

    2.1 为什么选择B-Tree结构

    B-Tree是一种自平衡的多路查找树,其中B是Balanced (平衡)的意思,节点最大的孩子数目称为B-Tree的阶(order)。

    关于B-Tree和B+Tree,可以看看以前我的这篇文章:数据结构—多路查找树中的2-3树、2-3-4树、B树、B+树的原理详解,里面有更加详细的介绍,下面的内容都是总结自这篇文章。

    B-Tree节点也是天然有序的(排序),和平衡二叉树一样,B-Tree也能以O(logn)的时间复杂度运行进行查找、顺序读取、插入和删除的数据结构,并且所有的叶子节点都位于同一层,或者说根节点到每个叶子节点的长度都相同。

    不同的是,平衡二叉树是一般是用于优化内存中的数据的查找速度的数据结构,B-Tree一般用于优化外存中数据的查找数据的数据结构,普遍运用在数据库索引结构和文件系统。

    对硬盘中的数据结构的某个节点的访问就会发起一次磁盘IO请求,IO操作耗时远大于内存中操作的耗时。如果一棵B-Tree的阶为1001(即1个节点包含1000个关键字),高度为2,它可以储存超过10亿个关键字。那么在这棵树上,寻找某一个关键字至多需要两次硬盘的读取即可。如果采用二叉树来存储这10亿个关键字,那么会需要非常多次数的IO请求。虽然两种数据结构最终找到某个数据所需的比较次数不会少,但是使用B-Tree时,可以经历非常少的IO次数(将一大批数据IO到内存中进行比较),而IO操作时非常耗时的。

    由于B-Tree每个节点能包含比二叉树更多的数据,同时树的层级比原来的二叉树更少的特性,加上数据库充分利用了磁盘预读原理(磁盘数据存储是采用块的形式存储的,每个块的大小为4k或8k,每次IO进行数据读取时,同一个磁盘块的数据可以一次性读取出来(InnoDB存储引擎一次IO会读取的一页(默认一页16K))),因此采用B-Tree作为索引结构,能够减少定位记录时所经历IO次数,从而加快存取速度。可以说,B-Tree的数据结构就是为内外存的数据交互准备的。

    以上特点也是“为什么不选择红黑树或者其他二叉树作为外存中进行查找的数据结构”等问题的原因。

    2.2 B+Tree和B-Tree结构的区别

    B+Tree可以说是B-Tree的升级版,相对于B-Tree来说B+Tree更充分的利用了节点的空间,让查询速度更加稳定,其速度完全接近于二分法查找。

    目前大部分数据库系统及文件系统都采用 B-Tree或其变种B+Tree作为索引结构。

    B-Tree和B+Tree的主要区别在于:

    1. B-Tree的非叶节点会存储关键字(key)及其对应的数据(data),而B+Tree的非叶节点只存关键字索引(key),不会存具体的数据(data),因此B+Tree的一个节点相比B-Tree能存储更多的索引元素,一次性读入内存的需要查找的关键字(key)也就越多,存储同样的数量的数据,B+Tree的高度可以更小,相对IO读写次数就降低了。
    2. B-Tree的查询效率并不稳定,最好的情况只查询一次(根节点),最坏情况是查找到叶子节点,而B+Tree由于非叶节点不存数据,而只是叶子节点中存储有关键字的索引。所有查询都要查找到叶子节点才算命中,查询效率比较稳定。这对于sql语句的优化是非常有帮助的。
    3. B-Tree的叶子节点之间都是独立的,而B+Tree所有叶子节点形成有序链表(可以是双向链表),只需要去遍历叶子节点就可以实现整棵树的遍历。提高范围查找的效率,而B-Tree不支持这样的操作或者说效率太低。

    如下,是一颗典型的B-Tree,它的非叶节点中存储了key和data:
    在这里插入图片描述

    如下,是一颗mysql索引表中实现的一种B+Tree,它的非叶节点中只存储了key,叶子节点之间使用链表关联起来:
    在这里插入图片描述

    这里的data,不同的存储引擎有不同的实现,对于MyISAM来说,data域存放的是数据记录的地址,对于InnoDB来说,data域保存了完整的数据记录。另外,即使InnoDB和MyISAM的BTREE索引都是采用的B+Tree结构,但其具体的存储实现上,仍有很大的不同。

    磁盘中的数据页是按顺序一页一页存放的,读取的时候也都是以页为单位读取的,InnoDB引擎一页的大小通常为16K。而B+Tree结构的每一页的数据还会从按照从小到大的顺序进行排序。

    B+Tree索引结构中,非叶节点作为索引页,key专门存放索引值,data域存储的是指向另一页的指针,key索引值的大小就是data对应的页的数据中的最小索引值,即对应页最左边的数据。

    叶节点作为数据叶,key同样是索引,但data存放了对应的数据,这个数据可能是主键id,指向表数据的指针,或者是真实的表数据,两两相邻的数据页之间会采用双向链表的格式互相引用,而每一页中的数据之间则是通过单链表来连接的。

    3 MyISAM的BTREE索引实现

    MyISAM存储引擎的数据文件和索引文件是分开存储的,索引文件名为tablename. MYI,而数据文件名为tablename.MDB,并且表空间中可存储行记录数。

    在MyISAM引擎中,使用 B+Tree作为BTREE索引结构,叶节点的关键key是索引列的值,而data 则是保存数据记录的地址。

    假设有一个user表,具有id、age、name字段,其中id是主键。下图是 在MyISAM 引擎中user表的主键索引图:
    在这里插入图片描述

    假设我们要根据主键id精确查询值为12的数据,则需要进行4次磁盘IO,其中3次索引IO,一次数据IO。

    假设我们要根据主键id精确查询值为10~20的数据,则需要进行5次磁盘IO,其中4次索引IO,一次数据IO。多出来的一次索引IO是对叶子节点横向进行的范围查找,并且id的查询范围跨越了不同的数据块,如果范围在同一个数据块中,则不需要多一次的范围查找。

    MyISAM存储引擎的主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。所以在查询时即使是等值查询,也需要按照范围查询的方式在辅助索引树中检索数据。

    对于user表,假设age字段是辅助索引,那么下图是 MyISAM 引擎中user表的辅助索引图:
    在这里插入图片描述

    另外,加载到内存中的数据将可能被MyISAM缓存起来,因此不是每次的查找都会进行磁盘IO。

    4 InnoDB的BTREE索引实现

    InnoDB 也使用 B+Tree 作为BTREE索引结构。一个 InnoDB 表包含两部分,即:表结构定义和数据。在 MySQL 8.0 版本以前,表结构是存在以.frm 为后缀的文件里。而 MySQL 8.0 版本,则已经允许把表结构定义放在系统数据表中了。因为表结构定义占用的空间很小。

    MyISAM 索引文件和数据文件是分离的,索引文件仅保存数据记录的地址,而InnoDB的数据文件本身就是索引文件,数据和索引存储在一个文件t_tablename.ibd中。

    其表数据文件本身就是按 B+Tree 组织的一个索引结构,树的叶节点 data 域保存了完整的数据记录,InnoDB表数据文件本身就是主索引,索引的key是数据表的主键,这种索引就是主键索引,也称为聚集(聚簇)索引,相应的,MyISAM存储引擎的索引被称为非聚集(聚簇)索引。

    聚簇索引的一个叶子节点存储的是被查找的数据行所在的一个页(同理一个非叶子节点则是存放的一个索引页),通过B+Tree最终找到的也是一个数据页,然后数据库通过把数据页读入内存,再在内存中进行查找(数据是有序的),最后得到要查找的数据。数据页占用固定大小的空间,但是却不一定会被数据填满,因为各种原因,比如,对一个满页插入数据时会使用页分裂计数将一页分裂成两个页,造成每一个数据页有近50%的空闲空间,形成很多磁盘碎片,可能导致全表扫描变慢(数据页和索引页都有可能页分裂)。

    假设有一个user表,具有id、age、name字段,其中id是主键。下图是在InnoDB引擎中user表的主键索引图:
    在这里插入图片描述

    假设我们要根据主键id精确查询值为12的数据,则需要进行3次磁盘IO,其中3次索引IO。

    假设我们要根据主键id精确查询值为10~20的数据,则需要进行4次磁盘IO,其中4次索引IO。多出来的一次索引IO是对叶子节点横向进行的范围查找,并且id的查询范围跨越了不同的数据块,如果范围在同一个数据块中,则不需要多一次的范围查找。

    因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列(唯一索引且not null)作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含ROWID字段作为主键,这个字段长度为6个字节,类型为长整形。

    InnoDB存储引擎除了主键/聚簇索引之外的其余的索引都作为辅助索引,也称为二级索引,或者非聚集(聚簇)索引。需要注意的是,辅助索引的 data 域存储相应记录主键的值而不是地址,而MyISAM的辅助索引的data域同样存放的是数据的地址。

    假设user表的age字段作为辅助索引,下图是在InnoDB引擎中user表的辅助索引图:
    在这里插入图片描述

    在根据主索引(主键)搜索时,直接找到 key 所在的节点的data即可取出数据,而在根据辅助索引查找时,则需要先走辅助索引取出对应的主键值,然后再走一遍主索引。

    根据在辅助索引树中先获取的主键id,然后再到主键索引树检索数据的过程(即二次查询)称为回表查询。假设我们要根据age字段查询age值为18的数据,则需要进行6次磁盘IO,其中3次辅助索引IO,3次回表主索引的IO。我们在应用中应该尽量使用主键查询。

    因此,在设计表的时候,不建议使用过长的字段作为主键,因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。也不建议使用非单调的字段作为主键,因为InnoDB数据文件本身是一颗B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整树的平衡,导致效率降低,而使用自增或者单调字段作为主键则是一个很好的选择(这一点对于MyISAM同样有效)。

    4.1 ROWID

    InnoDB主键策略:每个InnoDB引擎的表必须有一个“聚簇索引”,通常是通过主键索引字段构建。如果没有指定主键,则会通过第一个not null的唯一索引字段构建,如果这两个都没有,那么InnoDB会给你创建一个不可见的,长度为6个字节的row_id字段来构建。(https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html)。

    InnoDB维护了一个全局的dict_sys.row_id值,所有无主键并且没有not null的唯一键的InnoDB表,每插入一行数据,都将当前的dict_sys.row_id值作为要插入数据的row_id,然后把dict_sys.row_id的值加1。

    实际上,在代码实现时row_id是一个长度为8字节的无符号长整型(bigint unsigned)。但是,InnoDB在设计时,给row_id留的只是6个字节的长度,这样写到数据表中时只放了最后6个字节,所以row_id能写到数据表中的值,就有两个特征:

    1. row_id写入表中的值范围,是从0到2^48-1;
    2. 当row_id为2^48-1时,如果再有插入数据的行为要来申请row_id,拿到以后再取最后6个字节的话就是0。

    也就是说,写入表的row_id是从0开始到2^48-1。达到上限后,下一个值就是0,然后继续循环。

    在InnoDB逻辑里,申请到row_id=N后,就将这行数据写入表中;如果表中已经存在row_id=N的行,新写入的行就会覆盖原有的行。

    实际上,InnoDB 存储引擎为每行数据添加了三个 隐藏字段:

    1. DB_TRX_ID(6字节):表示最后一次插入或更新该行的事务 id。此外,delete 操作在内部被视为更新,只不过会在记录头Record header 中的 deleted_flag 字段将其标记为已删除。
    2. DB_ROLL_PTR(7字节) 回滚指针,指向该行的 undo log 。如果该行未被更新,则为空。
    3. DB_ROW_ID(6字节):如果没有设置主键且该表没有唯一非空索引时,InnoDB 会使用该 id
      来生成聚簇索引,这个字段值在所有的表中唯一。

    5 B-Tree索引的有效查询类型

    所谓有效查询类型,就是说发生了如下情况时,可以使用索引扫描查询,而不是全表扫描。

    1. 全值匹配:全值匹配指的是和索引列中的所有列进行匹配,例如查询name为“张三”的用户,此时就是全值匹配,对于多列索引来说,就是所有的索引列都完全匹配。
    2. 匹配最左前缀:对于多列索引可以从右向左一次匹配,后面会想详细讲解最左前缀。
    3. 匹配列前缀:可以只匹配某一列的值的开头部分,例如查询所有姓“张”的用户。对于多列索引,则只能对最左列使用列前缀匹配。
    4. 匹配范围值:由于B-Tree的有序性,可以进行基于索引列的范围匹配。所以索引可以按照升序或者降序进行扫描,以满足精确符合列顺序的ORDER BY、GROUP BY 和DISTINCT等之句的查询需求。
    5. 精确匹配和范围匹配:对于多列索引,可以对左边的列进行精准匹配,对右边的列进行范围匹配。但对于范围匹配之后的列则不能走索引,后面会详细讲解。
    6. 只访问索引的查询:B-Tree通常支持“只访问索引的查询”,即查询只需要访问索引,比如姓名列就是索引列,此时查询所有姓“张”的用户的姓名,那么我们无序访问数据行,因为索引就包含所要返回的全部结果,这是一种“覆盖索引”的优化,避免回表。

    基于这些有效查询类型,后面我们讲到索引优化部分的时候会非常有用。

    参考资料:

    1. 《 MySQL 技术内幕: InnoDB 存储引擎》
    2. 《高性能 MySQL》

    如有需要交流,或者文章有误,请直接留言。另外希望点赞、收藏、关注,我将不间断更新各种Java学习博客!

    展开全文
  • 数据库之索引底层数据结构 索引时Mysql高效获取数据排好序的数据结构。 底层为二叉树:对于极端情况时间复杂度很高 底层为红黑树:当数据量特别大的时候,红黑树的高度特别大,性能会比较差 B+Tree:非叶子不存储...
  • Mysql 的索引底层原理 1.什么是索引? 索引是一种排好序的数据结构,mysql目前默认使用的是b+树。 2.为什么使用b+树? 例如表table 数据 id name 1 zs 2 ls 3 sa 4 zl 5 wmz ...
  • Mysql索引底层数据结构与算法

    千次阅读 2018-10-10 17:25:44
    2,索引底层数据结构与算法 3,索引最左前缀原理   索引到底是什么 •索引是帮助MySQL高效获取数据的排好序的数据结构 索引的本质 MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构...
  • Mysql索引底层数据结构(B树) 一.什么是B树 B - Trees是一种平衡的多叉树,称为B树(或B-树、B_树),也是数据结构中树形结构的一种。 二.什么是索引,为什么要用要索引 索引是帮助Mysql高效获取数据的排好序的数据...
  • 首先,大家要清楚mysql索引底层使用的树形数据结构是B+Tree,并不是B-Tree;为什么不是二叉树,红黑树,B-Tree呢,大家可以自行百度,这儿就不一一说明了。 先放一张B+Tree的图: 这是单值索引时底层的样子。用单值...
  • Oracle支持的全部索引MySQL支持的全部索引
  • 单列索引其实也可以看做联合索引索引列为1的联合索引,从下图就可以看出联合索引底层存储跟单列索引时类似的,区别在于联合索引是每个树节点中包含多个索引值,在通过索引查找记录时,会先将联合索引中第一个...
  • mysql索引底层原理分析

    万次阅读 多人点赞 2018-09-23 00:01:40
    大家都知道索引的重要性,基本用法在上章《最全面的mysql索引知识大盘点》已分享过,本章主要是探索索引底层实现原理。当然了,我们还是以mysql为基准进行探讨。 目录 前言:innodb和myisam的区别 1.物理磁盘...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 233,772
精华内容 93,508
关键字:

索引底层