MySQL索引的数据结构、特点（Keywords：InnoDB、B+Tree、Hash）

已于 2023-05-15 16:51:32 修改
阅读量676 收藏 1
点赞数 1
分类专栏： DB及数据交互 文章标签： mysql 数据库
于 2021-07-04 21:43:51 首次发布
转载/复制请联系作者:snail.pu@foxmail.com
本文链接：https://blog.csdn.net/BigBug_500/article/details/118281046
版权

一、InnoDB下的B+Tree数据结构

   其特点就是能较好的利用B+Tree，支持范围查找数据的能力。

1.1 B+树索引

   在数据库中，B+树是高度平衡的，树的高度一般都在2 ~ 4层，所以查某一键值的行记录最多需要2 ~ 4次 IO（不同层级能存储的数据行数计算可参考）。B+ 树索引种类分为聚集索引（clustered index）和辅助索引（secondary index）。

聚集索引（clustered index）

   聚集索引按照每张表中的主键顺序构建B+树（每张表只能有一个主键，故每张表只能有一个聚集索引），其叶子节点存放完整的行数据，非叶子节点存放键值及指向数据页的偏移量。

在这里插入图片描述
   聚集索引的存储，是逻辑连续的，叶子节点间通过双向链表互相连接，存储叶子节点的页也是通过双向链表互相连接的。

辅助索引（Secondary Index）

   每张表只有一个CI，除了CI的索引，都是SI索引。

   SI 不同于 CI，其叶子节点并不是存放的一整行信息，而是存放索引具体的值和一个书签（bookmark），bookmark就是聚集索引中行数据的键。

在这里插入图片描述
   由图中 SI 与 CI 的关系我们可以知道，当使用 SI 无法获取到需要的数据时，那么将通过 SI 中的 bookmark 去 CI 中继续查找。这便是一个“回表”查询的流程。

索引的管理

可以通过 SHOW INDEX FROM employees；查看表中存在的索引
在这里插入图片描述
字段含义：

  • Non_unique:非唯一的索引，可以看到primary key是0，因为必须是唯–的

  • Seq_in_index：索引中该列的位置，如果是联合索引，那么这个值会大于1

  • Collation：列以什么方式存储在索引中。可以是A或NULL。B+树索引总是A，即排序的。如果使用了Heap存储引擎，并且建立了Hash索引，这里就会显示NULL了。因为Hash根据Hash桶存放索引数据，而不是对数据进行排序

  • Cardinality：非常关键的值，表示索引中唯一值的数目的估计值。Cardinality 如果非常小，那么需要考虑是否可以删除此索引

  • Sub_part：是否是列的部分被索引。即前缀索引的长度，如果索引整个列，则该字段为NULL。

  • Packed：关键字如何被压缩。如果没有被压缩，则为NULL

  • Null：索引的列是否含有NULL值

Fast Index Creation(快速引创建）

   MySQL 5.5 之前（不包括5.5），对索引的添加/删除之类的 DDL(Data Definition Language) 操作时，需要对旧的表和其中的数据拷贝到新的表中（临时表数据会被存放在全局参数 tmpdir 目录下，需保证有足够的空间，否则 DDL 操作将失败），如果有大量的事务访问正在被修改的表，那么此时的数据库服务将不可用。

   InnoDB 从 1.0.x 版本开始支持 FIC 的索引创建方式。对于辅助索引的创建，InnoDB 会对创建索引的表加上一个 S 锁；删除辅助索引时，删除对应的数据即可。
   由于 FIC 在索引的创建的过程中对表加上了S锁，若有需要对目标表进行写操作，那么数据库的服务同样不可用。此外，FIC方式只限定于辅助索引，对于主键的创建和删除同样需要重建一张表。

Online DDL

   虽然 FIC 提高了辅助索引的创建效率，但在索引创建期间还是会阻塞 DML（data manipulation language） 操作，MySQL 5.6 开始支持 ODDL，可完成如下功能的 在线 操作：

  • 辅助索引的创建与删除，且可同时进行 DML 操作

  • 改变自增长值

  • 添加或删除外键约束

  • 列的重命名

并不是全部的 DDL 操作都支持 ODDL 的，例如修改列数据类型、删除主键、变更表字符集等，需要线上 DDL 时，建议先进行测试是否会阻塞 DML。

在 ODDL 方式下，用户可以调整参数，选择索引的创建方式：

ALTER TABLE tbl_name
| ADD {INDEX|KEY) [index name]
[index_type] (index_col_name,...) [index_option] ...
ALGORITHM [=] (DEFAULT|INPLACE|COPY)
LOCK [=] (DEFAULT | NONE | SHARED | EXCLUSIVE)

ALGORITHM指定了创建或删除索引的算法（默认 DEFAULT，等于 INPLACE）
LOCK 部分为索引创建或删除时对表添加锁的情况（默认 DEFAULT ）

1.2 B+ 树索引的使用与注意事项

联合索引

在这里插入图片描述

   联合索引是指对表上的多个列进行索引。与单列索引不同的是，联合索引 B+ 树中键值的数量 >=2。

   联合索引会对多个键值依次进行排序处理（即在每个a下，b是有序的），所以，如果需要使用到联合索引，需要根据这个依次排序的特性去分析查询条件。常见用例如下：

  1. 联合索引(a, b) 其实是根据列a 、b 进行排序， 因此下列语句可以直接使用联合索引得到结果：
    SELECT … FROM TABLE WHERE a=xxx ORDER BY b;

  2. 对于联合索引（ a, b ， c) 来说， 下列语句也可以直接通过联合索引得到结果：
    SELECT … WHERE a=xxx ORDER BY b;
    SELECT … WHERE a=xxx AND b=xxx ORDER BY c
    但是对于下面的语句， 联合索引不能直接得到结果， 其还需要执行一次 filesort 排序操作， 因为索引(a ， c) 并未排序：
    SELECT … WHERE a=xxx ORDER BY c
    对于下面的语句，可以使用到联合索引，但还使用到了 Using index condition（使用 index 后对数据进行的条件筛查）
    SELECT … WHERE a=xxx AND b>xxx AND c=xxx;

覆盖索引(covering index)

   covering index 是指从辅助索引中就可以得到查询的数据，而不需要回表查询对应的行数据。

   CI 不包含整行数据的所有信息， 故其大小要远小于聚集索引， 数据读取时可以减少磁盘 IO。若查询的SQL内容在CI中均存在，那么将不需要回表查询，也可以减少磁盘IO。

   使用 CI 需要注意，CI 需要额外的空间来存储建立的索引。

优化器不使用索引及解决方案

   在某些情况下，优化器放弃了可以使用的辅助索引，而是去扫描聚集索引（也就是直接进行全表扫描）来得到数据。这种情况多发生于范围查找、JOIN 链接操作等情况下。

为什么不使用索引？而是去全表扫描？

   在某些 SQL 条件中，通过辅助索引不能覆盖到我们要查询的字段信息，因此还需要使用辅助索引中的 bookmark 再做一次回表查询（即覆盖索引失效）。

   但借助聚集索引回表查找的 数据是无序的， 因此变为了磁盘上的离散读操作。如果要求访问的数据量很小， 则优化器还是会选择辅助索引， 但是当访问的数据占比较高时（ >=20 ％ 左右), 优化器会 放弃对应的辅助索引，选择聚集索引的顺序读来查找数据（顺序读要远远快于离散读）。

如何解决？

  1. 优化器之所以放弃辅助索引，使用聚集索引，是根据传统的机械硬盘性质决定的，所以你可以听从优化器的方案

  2. 如果 DB 的磁盘是固态硬盘，那么离散读操作效率是比较高的，且同时能够确定 使用辅助索引能够提高效率的，可以在 SQL 中使用 force index(index_name) 来指定索引

EXPLAIN SELECT * FROM party_answer force INDEX(idx_abc) WHERE exam_id<72

二、 Memory下的Hash结构

   基于哈希表实现，只有精确匹配所有列的查询才有效。MySQL中只有Memory引擎显式的支持哈希索引。

备注：更多的哈希索引内容，可以参考《高性能MySQL》 5.1.1节 146页 的内容。

自适应哈希索引（adaptive hash index）

   当InnoDB的某些索引值被经常使用时，该引擎会在内存中基于B+tree索引之上，再建设一个Hash索引，实现快速的哈希查找。不过 AHI 能力是 InnoDB 内部封装的一个能力，使用者不感知。如果业务场景有必要，可以按官网介绍关闭该功能。MySQL AHI参数介绍

专题

如何计算B+树中的行数与高度？

需要提前知道的知识点：

  1. 在 InnoDB 存储引擎里面，最小的存储单元是页（page），一个页的大小为 16KB，即一个B+树节点。页结构可参考《MySQL技术内幕》的4.4。
  2. 叶子节点存放行数据，非叶子节点存放 索引键值 和 指向数据页的指针

假设：

  • 非叶子节点内指向其他页的数量为 x
  • B+ 数的层数为 z
  • 叶子节点内，能容纳的数据行数为 y
    那么，totalRowNum = x^(z-1) * y

非叶子节点内指向其他页的数量X = ？
页的结构如下图所示：
在这里插入图片描述
   节点都会有 File Header (38 byte)、Page Header (56 byte)、Infimum + Supermum（26 byte）、File Trailer（8 byte）, 再加上页目录，大概 1k 左右。整个页的大小是 16K, 剩下 15k 用于存数据，在索引页中主要记录的是主键与页号，主键我们假设是 Bigint (8 byte), 而页号也是固定的（4 byte）, 那么索引页中的一条数据也就是 12 byte。所以 x=（15*1024）byte / 12 = 1280 行。

叶子节点内，能容纳的数据行数Y=？

   叶子节点和非叶子节点的结构是一样的，同理，能放数据的空间也是 15k；但是叶子节点中存放的是真正的行数据，这个影响的因素就会多很多，比如，字段的类型，字段的数量；每行数据占用空间越大，页中所放的行数量就会越少；这边我们暂时按一条行数据 1k 来算，那一页就能存下 15 条，Y≈15。

结果

根据上述的公式，totalRowNum = x^(z-1) y，已知 x=1280 行，y = 15 行

  • 假设 B+ 树是两层，那就是 Z =2，
    • totalRowNum = （1280 ^1 ）* 15 = 19200 行
    • maxIndexSizeOfNonLeafNode（索引最大内存）= 16 K * 1= 16K
  • 假设 B+ 树是三层，那就是 Z =3，
    • totalRowNum = （1280 ^2） *15 = 24576000 （约 2.45 KW）
    • maxIndexSizeOfNonLeafNode（索引最大内存）= 16K *(1+1280行) = 20496 K ≈ 20MB
  • 假设 B+ 树是四层，那就是 Z =4，
    • totalRowNum = （1280 ^3） * 15 = 300亿+ 行
    • maxIndexSizeOfNonLeafNode（索引最大内存）= 16K * (1+1280 + 1280 * 1280行) ≈ 25 GB

B+Tree 高度和行数的计算

BigBug_500
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
第38讲：MySQL索引结构之Hash索引数据结构
江晓龙的博客
11-16 7802
如下图所示，我们想对user_name这一列添加hash索引，首先会将user_name这一列的字段内容转换成新的hash值，然后通过hash函数将数据写入到对应的槽位上，例如小红这条的数据，通过hash函数计算出要分配的槽位，然后会将索引字段的值和这一条数据的hash值都写入到槽位中，在查询小红这条数据时，通过槽位中记录的hash值就可以找到对应的这条数据。哈希索引就是采用了一定的HASH算法，将key元素换成新的hash值，然后映射到对应的槽位上，最终存储在hash表。无法利用索引完成排序操作。
MySQL哈希索引以及InnoDB自适应哈希索引
长期更新Java自学笔记
03-13 3757
一、哈希索引 二、InnoDB自适应哈希索引
MySQL中使用B+树的优点、特点及其数据查找流程
最新发布
qq_46130027的博客
07-08 656
在B+树中，节点内部通常存储关键字和对应的指针，指针可以指向子节点或叶子节点。叶子节点中存储了关键字和对应的数据。B+树是一种自平衡的树结构，通过节点的分裂和合并来保持树的平衡，使得所有叶子节点的深度相同，提高了查询的效率。B+树的高度相对较低，通过几次磁盘IO就能快速定位到目标数据所在的叶子节点，从而实现高效的查找操作。叶子节点存储了所有关键字和对应的数据，且通过指针链表连接在一起，形成有序的叶子节点序列。B+树的节点可以存储更多的关键字和数据，减少了磁盘IO次数，提高了数据访问的效率。
Sql语句正确却报BadSqlGrammarException（MySQL数据库）
热门推荐
Odinpeng的博客
04-22 1万+
MySQL数据库sql语句正常，却报BadSqlGrammarException（sql语法错误异常） Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException
MySQL(一):B+ Tree,索引以及其优点, 索引实战, 聚簇索引和非聚簇索引, 最左匹配,索引失效
qq_43398345的博客
02-17 1687
在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。由于构建一棵B+树只能根据一个值来确定索引关系，所以数据库依赖联合索引最左的字段来构建。举例：创建一个（a,b）的联合索引，那么它的索引树就是下图的样子。可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。但是我们又可发现a在等值的情况下，b值又是按顺序排列的，但是这种顺序是相对的。
Mysql中索引的结构和类型大盘点
Limelimelimes的博客
12-10 293
大家好, 这里是Yve菌, 今天给大家带来一些Mysql索引相关的基础知识.
Mysql数据库索引数据结构(HashMap,BTree,B+Tree)
F道人
04-25 966
目录 概述 HashMap结构的索引 二叉树数据结构 B Tree数据结构 B+Tree数据结构 一：概述 1. 索引的定义 MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是一种数据结构。 数据库查询是数据库的主要功能之一，最基本的查询算法是顺序查找（linear search）时间复杂度为O(n)，显然在数据...
获取 MySQL innodb B+tree 的高度的方法
12-14
MySQL 的 innodb 引擎之所以使用 B+tree 来存储索引，就是想尽量减少数据查询时磁盘 IO 次数。树的高度直接影响了查询的性能。一般树的高度在 3~4 层较为适宜。数据库分表的目的也是为了控制树的高度。那么如何获取...
MySQL中InnoDB数据结构和索引介绍
12-14
MySQL中的InnoDB存储引擎使用B+Tree作为主要的数据结构来构建索引，这是因为它具备诸多优点，使得查询和插入操作高效稳定。B+Tree是一种多路平衡查找树，相较于数组、链表和哈希表，它更适合大规模数据存储。数组...
b+tree在myisam和innodb中的表现形式
01-07
b+tree在 innodb和mysiam中的提现形式 左边的是innodb的，辅助索引保存是主健值，所以放用辅助索引搜索数据时，要先走辅助索引，再走主健索引。 右边的是mysaim的，索引之间是独立的。 作者：我们一直
B+Tree索引的背后
12-14
InnoDB的索引采用B-Tree数据结构，索引页大小默认为16KB，可调整。在插入新记录时，InnoDB会预留一定空间以应对未来的增长。页是InnoDB存储的基本单位，页内有FIL Header/Trailer和User Records等部分。FIL Header/...
MySQL8关键词与保留字
Zze0
05-18 2493
关键字是在SQL中具有重要意义的单词。某些关键字，如SELECT， DELETE或 BIGINT，被保留，需要用作标识符，例如表和列名特殊待遇。内置函数的名称也可能如此。 允许使用非保留关键字作为标识符而无需引用。如果您引用保留字，则可以将它们用作标识符： mysql> CREATE TABLE interval (begin INT, end INT); ERROR 1064 (42000): You have an error in your SQL syntax ... near 'interv
【mysql】mysql索引存储结构和特点
weixin_44458365的博客
08-04 599
原创作者地址： https://blog.csdn.net/u010922732/article/details/82992920 MySQL索引存储结构和特点 一 理解索引的特性 二 索引的各种存储结构及其优缺点 （一） 二叉树 ​（二） 红黑树 （三） Hash （四
mysql基础-InnoDB 中的索引
d495435207的博客
11-22 447
1. InnoDB 中的索引 InnoDB 中的索引自然也是按照 B+树来组织的，前面我们说过 B+树的叶子节 点用来放数据的，但是放什么数据呢?索引自然是要放的，因为 B+树的作用本 来就是就是为了快速检索数据而 出的一种数据结构，不放索引放什么呢?但是 数据库中的表，数据才是我们真正需要的数据，索引只是辅助数据，甚至于一个 表可以没有自定义索引。InnoDB 中的数据到底是如何组织的? 1. 聚集索引/聚簇索引 InnoDB 中使用了聚集索引，就是将表的主键用来构造一棵 B+树，并且将整 张表的行记录数
MySQL索引特性
裙下的霸气的博客
06-18 634
索引的概念索引的价值上述SQL中创建一个名为index_demon的数据库，在该数据库中创建了一个名为index_demon的数据库，在该数据库中创建了一个名为EMP的员工表，并向表中插入了八百万条记录。将上述SQL保存到文件中，然后在MySQL中使用source命令，依次执行文件中的SQL即可。通过desc命令可以看到，目前EMP员工表中没有建立任何索引。如下：查询emp表中指定工号的员工信息，可以看到每次查询过程中都需要花费4-5s左右。
mysql指引（十三）：innodb中的锁
oatlmy
05-26 1020
讲解锁的来源和梳理 innodb 中的锁
Hash索引和B+树相关知识
qq_52563729的博客
07-23 1880
学习笔记
mysql b+tree介绍
wang951654的博客
03-31 390
MySQL中的索引可分为以下几类：主键索引：每个表只能有一个主键，主键必须是唯一的且不为空。MySQL会自动在主键上创建B+树索引。唯一索引：唯一索引要求被索引列的值唯一，可以为空。MySQL会在唯一索引上创建B+树索引。普通索引：普通索引没有唯一性或主键约束，可以包含重复值和NULL值。MySQL会在普通索引上创建B+树索引。全文检索：全文检索可以用来进行关键字搜索，需要通过特定的语法来定义关键字。MySQL支持在MyISAM存储引擎下使用全文检索。
MySQL 简单了解B+树
weixin_43844718的博客
12-07 1664
父文章锁知识点:大家都知道mysql中数据是存储在物理磁盘上的，而真正的数据处理又是在中执行的。由于磁盘的读写速度非常慢，如果每次操作都对磁盘进行频繁读写的话，那么性能一定非常差。为了上述问题，InnoDB将数据划分为若干页，以页作为磁盘与内存交互的基本单位，一般页的大小为16KB。这样的话，一次性至少读取1页数据到内存中或者将1页数据写入磁盘。通过减少内存与磁盘的交互次数，从而提升性能。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值