MySQL innodb的B+树到底长什么样,为什么MySQL要这样设计?

数据库 MySQL
最近也许是我们公司给的活动太给力,业务数据量剧增,于是要考虑优化数据库,作为程序猿的我们都知道数据是我们的命脉,我们做的工作就是处理数据,优化数据是我们一直要面临的问题。

背景

最近也许是我们公司给的活动太给力,业务数据量剧增,于是要考虑优化数据库,作为程序猿的我们都知道数据是我们的命脉,我们做的工作就是处理数据,优化数据是我们一直要面临的问题。

[[435839]]

MySQL优化维度

一般优化数据库都需要从以下四个维度进行:

  • 硬件
  • 系统配置
  • 数据库表结构
  • SQL 及索引

对于写业务的我们的最直接就是SQL及索引优化,效果最显著性、价比最高的是索引优化。

认识索引

索引是帮助数据库(Mysql)高效获取数据的排好顺序的数据结构。

原理

通过不断的缩小想要获得数据的范围来筛选出最终想要的结果,同时把随机的事件变成顺序的事件,也就是我们总是通过同一种查找方式来锁定数据。

MySQL索引的数据结构有以下几种:

  • Hash表
  • B+树

使用Hash算法作为索引,有以下问题,所以大部分我们选择的是BTREE。

  • 存在Hash碰撞。
  • 只能精确查找,无法用于局部查找和范围查找。

MySQL的B+树

我们先来回顾下大学数据结构里面的B+树,长这个样子。 

MySQL innodb的B+树到底长什么样,为什么MySQL要这样设计?

MySQL中的B+数其实是对传统的B+树做了改进。

将叶子节点的数据单向指针变成双向指针,树高为2。

MySQL每次查1条数据都会查出1页数据(16K),然后在内存里面遍历,减少IO,大大提高查询效率。

Mysql 在插入数据后,会自动给我们排序。为什么要这样做呢?

1.先看一个例子,查询一条不存在的数据。 

MySQL innodb的B+树到底长什么样,为什么MySQL要这样设计? 

如果排序后,只用遍历到第4条记录,就可以不用查了,如果不排序,就要遍历所有的数据。

2.比较多的数据查询,还是一页数据。 

MySQL innodb的B+树到底长什么样,为什么MySQL要这样设计? 

在页模式底部存储的数据,采用了链表的结构,插入比较快,但是查询比较慢,数据量比较大的时候就需要用空间换时间,给页面加个目录,先去查页目录(通过二分法查找)。不加目录则需要查13次,加了目录只需要3次就就可以找到数据。这是排序的最主要原因。

3.随着数据量的进一步增大,会出现很多页数据,然后再对多页数据进行索引,即采用了页目录的目录项,从而管理页,而页目录管理行。 

MySQL innodb的B+树到底长什么样,为什么MySQL要这样设计?

目录页的本质也是页,存的数据是普通页的地址。所以不管是目录页还是页目录,都和数据存放在一起。这就是聚簇索引的由来(即主键索引和数据放在一起)。这样就形成了B+树。

一棵树存放的数据量

一行存放数据大小按1k算,则一页存放16行数据。高度为3的b +树,主键为BigInt(占8个字节),innodb 指针占(6个字节),就可以存放(16*1024/(8+6)*(16*1024/(8+6)*16=2千多万行数据。这就是一般一个表的数据超过2千万就不建议走索引,要分库分表的原因了。这样的结构就可以使得2千万的数据,只需要3次IO.

双向指针的原因

范围查找时,如果查找小于某个值的记录,就需根据指针要反向查找,所以需要反向指针。

回表

当有多个字段组成组合索引时,此时的索引是非聚簇索引,叶子节点不存储数据,存储的是数据行地址,因为数据量比较大。这样查出后,通过记录主键反查完整记录。这就是回表。

注意

InnoDB中一定有主键索引,主键一定是聚簇索引,如果没有则会使用一个unique索引作为主键索引,如果没有unique索引,则会使用数据库内部的一个隐藏行id来当作主键索引。有且只有一个聚簇索引。非聚簇索引都需要回表。

责任编辑:华轩 来源: 今日头条
相关推荐

2023-06-06 09:03:06

InnodbMySQL

2022-03-28 08:24:52

MySQL聚簇索引非聚簇索引

2019-09-24 09:33:53

MySQLB+树InnoDB

2019-03-14 09:51:50

MySQL存储逻辑架构

2020-02-12 19:01:22

索引B-树B+树

2020-03-19 07:53:56

Mysql引擎B+树

2022-04-16 14:20:29

MySQL数据库

2020-04-01 18:08:57

MySQL B-树B+树

2019-08-29 10:46:22

MySQL索引数据库

2015-04-21 13:09:01

B+树MySQL索引结构

2021-12-17 17:52:02

MySQL B+面试

2009-10-26 13:36:10

BSM

2022-04-05 20:24:19

元宇宙技术数字化

2013-10-29 09:35:54

Windows 9概念图

2021-02-16 16:38:41

MySQLB+树索引

2019-01-29 19:43:10

MySQL索引数据库

2019-06-25 09:15:27

MySQLInnoDBSQL

2013-08-29 11:38:53

企业App

2023-01-06 21:03:59

2010-09-01 15:27:40

DHCP工作流程
点赞
收藏

51CTO技术栈公众号