MySQL 底层数据结构 聚簇索引以及二级索引 Explain的使用

这篇具有很好参考价值的文章主要介绍了MySQL 底层数据结构 聚簇索引以及二级索引 Explain的使用。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

数据结构

我们知道MySQL的存储引擎Innodb默认底层是使用B+树的变种来存储数据的

下面我们来复习一下B树存储 + B树存储  + 哈希存储的区别

MySQL 底层数据结构 聚簇索引以及二级索引 Explain的使用,MySQL,mysql,数据结构,数据库

哈希存储,只能使用等值查询

B树与B+树存储

我们知道B+树实际上就是B树的变种

那么为啥使用B+树而不是使用B树呢?

我们知道效率的高低主要取决于load进内存的时候这个load操作的次数

注:数据表中的数据只是逻辑上连续的,在物理内存中其实是不连续的

因为我们知道磁盘是一圈一圈的,磁头是一直在读写的

可能两次读写之间根本不在一个磁道中

我们先看看B树的数据结构

MySQL 底层数据结构 聚簇索引以及二级索引 Explain的使用,MySQL,mysql,数据结构,数据库

假设这里我们向查找一个9号数据,我们内存中就会加载这里的根节点,以二分查找的方式开始查找9,但是这里的查找由于一层存放的节点是带有数据的,相对来说存放相同的数据的节点数就会少,对应的层数也就升高了,所以MySQL决定使用了B树的变种,B+树,B+树只在叶子结点存储数据,这样同一层能存储的数据就多了,虽然每一层都会使用一个冗余节点,但是内存开销也是很小的

一个叶节点最大16K(默认)

假设对于一个千万级的数据,对于B+树只需要三层,对于B树却需要远超3层的一个指数级节点数,而且对于范围查找也是B+树更擅长的,因为B+树在叶子节点之间之间放了一个双向指针,而且是排好序的数据,更方便查找范围数据

注:其实也可以选择使用hash结构存储,但是hash存储是不能解决范围查找的问题的,所以还是B+树更优

MySQL 底层数据结构 聚簇索引以及二级索引 Explain的使用,MySQL,mysql,数据结构,数据库

索引

索引的定义:索引是帮助mysql高效获取数据的排好序的数据结构

以上的定义对于理解索引的操作非常重要

对于根节点来说,其是常驻内存的

我们知道对于表规范来说我们应该在设计表的时候加上id  开始时间  更新时间

通常id设置为整形自增主键  

为啥是自增主键呢??

选择整形是因为其占用的内存小,相对来说查找较快  以前使用uuid占用内存就比较大

自增是为了不导致树的平衡和节点拆分操作

我们举个例子

假设我先插入7 再插入 8 可能就是对树的大节点进行拆分,还对树进行了平衡操作,效率降低了

所以这里建议使用自增主键

相对来说使用自增主键的效率更高

二级索引

我们知道对于mysql还有二级索引

那么二级索引又是怎么存储的呢?

二级索引的索引树叶子节点存储的就是索引信息和主键信息

对于二级索引索引树包含的信息使用它会更快

但是一旦超出的他的数据范围,就需要一个回表的操作了

因为二级索引树的信息不能包含所有的信息

只能根据其主键来去主键的索引树查询了

这样来说效率反而会降低,不如直接使用主键索引树的聚集查询

explain工具

我们知道explain关键字可以查询到sql语句中对应的sql执行信息,方便我们进行sql的优化

下面我们来介绍一下有关的信息

MySQL 底层数据结构 聚簇索引以及二级索引 Explain的使用,MySQL,mysql,数据结构,数据库

1.select_type   语句的复杂程度

一般有三种

simple   sqlquery子查询   derived延伸查询 

延伸查询是在from后面临时生成的临时表

2.partitions   

分区  一般不使用

对于数据多的直接使用分库分表了

3.type   

可以查看估算到sql语句执行的效率

MySQL 底层数据结构 聚簇索引以及二级索引 Explain的使用,MySQL,mysql,数据结构,数据库

下面我们来一个一个介绍一下这里的表示

1.system 

 表示这个表只有一个字段,使用唯一索引直接就查找到了

2.const 

表示查找的时候使用唯一索引   就跟查询一个常量一样快

我们可以理解为system是const 的一个特殊情况

system的数据更少

3.eq_ref

equal_reference 表示连接的时候使用主键索引

这时候因为使用了唯一性索引就出来不需要比较

MySQL 底层数据结构 聚簇索引以及二级索引 Explain的使用,MySQL,mysql,数据结构,数据库

4.ref 

在连接的时候没有使用唯一性索引

但是使用了索引可能使用了二级索引等等

查出来可能是多条数据要进行比较

5.range

范围查找  

使用主键索引来检索给定范围的行

因为是有序的,还是能使用索引的

6.index

全索引扫描   

这里使用的是二级索引进行范围查找

7.all 

效率最低的全表扫描

不使用索引

key_len

这里表示的就是使用联合索引的哪个字段

比如说使用了int类的字段就是4表示4个字节...

MySQL 底层数据结构 聚簇索引以及二级索引 Explain的使用,MySQL,mysql,数据结构,数据库

extra

额外信息

这里说几个常见的

Using index 

使用覆盖索引

这里的覆盖索引指的不是一种索引 而是一种查找索引的方式

这里就是表示二级索引的索引树叶子节点已经包含了全部信息

这里就无需再进行回表使用主键索引树继续查找了

Using where 

查询的列没被索引覆盖

Using index condition

用到了临时表  比如使用了distinct进行去重 ,这里如果用到索引树就直接去拿

没用到索引树就得创建一个临时表

Using filesort

使用外部排序   在orderby的时候会出现   

如果没使用索引就会出现外部排序

这里使用临时表和外部排序的都需要被优化掉,使用索引去覆盖即可

MySQL 底层数据结构 聚簇索引以及二级索引 Explain的使用,MySQL,mysql,数据结构,数据库

MySQL 底层数据结构 聚簇索引以及二级索引 Explain的使用,MySQL,mysql,数据结构,数据库

使用全值索引更快

顺序换了一下也会走索引,但是最好不要,因为mysql底层会进行一定程度的运算,会降低效率

注:不要在索引上做一些运算操作,因为这样会导致索引树无法定位文章来源地址https://www.toymoban.com/news/detail-861257.html

到了这里,关于MySQL 底层数据结构 聚簇索引以及二级索引 Explain的使用的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • Map,List,Set 等集合以及底层数据结构

    集合类存放于java.util包中。集合类存放的都是对象的引用,而非对象本身。常见的集合主要有三种——Set(集)、List(列表)和Map(映射)。其中,List和Set 都 实现 了 Collection 接口,并且List和Set也是接口,而 Map 为独立接口 。常见的实现类如下: List 的实现类有:ArrayList、

    2024年02月09日
    浏览(13)
  • (一)深入理解Mysql底层数据结构和算法

    索引是帮助MySQL高效获取数据的排好序的数据结构 数据结构模拟网站:Data Structure Visualization 二叉树 不适合做自增ID的数据结构。如下示意图,假设采用二叉树作为表自增主键ID的数据存储结果如下:当查询id为5的数据时,其查询次数为5次 红黑树 不适合做mysql的索引,因为当

    2024年01月25日
    浏览(16)
  • 数据结构MySQL —— 索引

    目录 一、索引概述 二、索引结构 三、索引分类 四、索引语法  五、SQL性能分析 1.  查看执行频次 2.  慢查询日志 3.  show profiles指令  4.  explain执行计划 六、索引使用规则 1.  验证索引效率 2.  最左前缀法则  3.  范围查询 4.  索引失效情况 5.  SQL提示  6.  覆盖索引 7. 

    2024年01月24日
    浏览(16)
  • MySQL索引的数据结构

    MySQL官方对索引的定义为: 索引(Index)是帮助MySQL高效获取数据的数据结构。 索引的本质:索引是数据结构。你可以简单理解为“排好序的快速查找数据结构”,满足特定查找算法。这些数据结构以某种方式指向数据, 这样就可以在这些数据结构的基础上实现 高级查找算法

    2024年02月14日
    浏览(16)
  • MySQL数据库索引的数据结构

    数据库索引的功能就是让查找更加的高效,所以索引的数据结构应该是能够加速查找的数据结构。 MySQL的innoDB存储引擎的索引的数据结构就是多叉搜索树中的b+树,这可以说是为索引量身定做的一个数据结构。 首先,索引可以通过主键,unique修饰创建,也可以直接使用sql语句

    2024年02月10日
    浏览(16)
  • 索引的数据结构(MySql高级)

    索引是存储引擎用于快速找到数据记录的一种数据结构,就好比一本教科书的目录部分,通过目录中找到对应文章的页码,便可快速定位到需要的文章. MySQL中也是一样的道理,进行数据查找时,首先查看查询条件是否命中某条索引,符合则通过索引查找相关数据,如果不符合

    2024年01月18日
    浏览(12)
  • MySQL-06.索引的数据结构

    索引是存储引擎用于快速找到数据记录的一种数据结构,就好比一本书的目录部分,通过目录中找到对应文章的页码,便可快速定位到需要的文章。MySQL中的索引也是一样的道理,进行数据查找时,首先查看查询条件是否命中某条索引,符合则 通过索引查找 相关数据,如果不

    2024年04月22日
    浏览(6)
  • Mysql——索引相关的数据结构

    我们知道,数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快,因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找(linear search),这种复杂度为O(n)的算法在数据量很大时显然是糟糕的,好在计算机科学

    2024年01月16日
    浏览(11)
  • mysql索引的数据结构(Innodb)

    首选要注意,这里的数据结构是存储在硬盘上的数据结构,不是内存中的数据结构,要重点考虑io次数. 一.不适合的数据结构: 1.Hash:不适合进行范围查询和模糊匹配查询.(有些数据库索引会使用Hash,但是只能精准匹配) 2.红黑树:可以范围查询和模糊匹配,但是和硬盘io次数比较多. 二

    2024年02月10日
    浏览(12)
  • 数据结构:Mysql索引原理(通俗易懂)

    在工作中如果经常写业务代码,说白了就是CURD,比如做一个查询功能,我们会将前端参数传递到后端后拼接SQL查询数据,那为了提高用户体验,查询速度肯定是越快越好,这就要求我们能够对SQL调优,让查询速度更快。 由于数据需要持久化,且数据量庞大,所以只能将数据

    2024年02月11日
    浏览(14)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包