Mysql索引--02---索引数据结构

已于 2023-12-06 18:05:53 修改
阅读量258 收藏 1
点赞数
分类专栏： Mysql高级 文章标签： 数据结构 mysql 数据库
于 2020-12-08 18:33:19 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_48052161/article/details/108944867
版权

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

文章目录

索引

  • 索引是帮助MYsql高效获取数据的排好序的数据结构
  • 索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构

索引数据结构

  • 二叉树
  • 红黑树
  • Hash
  • B+树

Data Structure Visualizations

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

二叉树-- Binary Search Tree

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

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

查找where Col2 = 22的记录
在这里插入图片描述
col1 数据是1-7 连续递增的数据
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

分析: 如果数据是从小到大 ,连续递增或递减函数,则二叉树呈线性结构,查询效率低

红黑树 (二叉平衡树)

插入数据 从1-8 依次插入
在这里插入图片描述
分析:红黑树会主动平衡树的结构,使树两边数据尽量达到平衡.始终保证左子节点数 < 父节点数 < 右子节点数的规则。

但 红黑树 在大数据场景下面,树的高度不可控,那么存在叶子节点的数据,查找起来效率不会特别高.会多次IO读取磁盘中的数据(索引一般保存在磁盘当中).

Hash

  • 优点：

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

  • 缺点：

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

不适合模糊查询（like）的场景。

B-Tree

在这里插入图片描述
在这里插入图片描述

B+Tree (多叉平衡树)

在这里插入图片描述

  • 每一个索引旁边会分配一个指针.指针指向下一节点的存储地址信息
  • 只有叶子节点的索引元素存储data
  • 根节点元素,MySQL运行时一般直接加载进内存.

B+树和B树的差异：

  1. 非叶结点仅具有索引作用，也就是说，非叶子结点只存储key，不存储value；
  2. 树的所有叶结点构成一个有序链表，可以按照key排序的次序遍历全部数据。

所以

- b+树的高度边小了,io寻址效率更高
- 存放更多的key
- 叶子结点存储了全部数据,且有序,更方便遍历,也更方便区间查找和搜索

B+ 树的优点在于：

  1. 由于B+树在非叶子结点上不包含真正的数据，只当做索引使用，因此在内存相同的情况下，能够存放更多的key。(树的高度变小了)
  2. B+树的叶子结点都是相连的，因此对整棵树的遍历只需要一次线性遍历叶子结点即可。而且由于数据顺序排列并且相连，所以便于区间查找和搜索。—更利于遍历,区间查找和搜索

B树的优点在于：

  • 由于B树的每一个节点都包含key和value，因此我们根据key查找value时，只需要找到key所在的位置，就能找到value，但B+树只有叶子结点存储数据，索引每一次查找，都必须一次一次，一直找到树的最大深度处，也就是叶子结点的深度，才能找到value。

计算一个h=3的b+树 能存多少个索引元素?

  • Mysql 默认底层给每个节点设置的容量大小是 16kb

在这里插入图片描述

  • 假设一个主键索引 类型为 bigint 8个字节,

  • Mysql 默认给指针分配的内存大小 是 6个字节

  • 每一个节点能存储 16*1024/(8+6)=1170个索引元素

  • 叶子节点的索引元素因为要存储data,所以按1kb的大小计算的话,一个叶子节点能存储16个索引元素

count(索引元素)=1170 * 1170 * 16=21902400

经过上述计算得知,一个高度h为3 的 B+树,叶子节点,大约能容纳2100万个索引元素.

所以上千万的数据表,MySQL也只要经过1到2次的io磁盘查找,就能获取到指定元素的数据.

MyISAM和InnoDB对索引和数据的存储在磁盘上是如何体现的

先来看下面创建的两张表信息，

  • role表使用的存储引擎是MyISAM

  • 而user使用的是InnoDB：
    在这里插入图片描述
    再来看下两张表在磁盘中的索引文件和数据文件：
    在这里插入图片描述
    1.role表有三个文件，对应如下：

  • role.frm：表结构文件

  • role.MYD：数据文件（MyISAM Data）

  • role.MYI：索引文件（MyISAM Index）

2. user表有两个文件，对应如下：

  • user.frm：表结构文件

  • user.ibd：索引和数据文件（InnoDB Data）

也由于两种引擎对索引和数据的存储方式的不同，我们也称MyISAM的索引为非聚集索引，InnoDB的索引为聚集索引。

MyISAM主键索引与辅助索引的结构

我们先列举一部分数据出来分析，如下：

在这里插入图片描述
上面已经说明了MyISAM引擎的索引文件和数据文件是分离的，我们接着看一下下面两种索引结构异同。

1. 主键索引：

上一篇文章已经介绍过数据库索引是采用B+Tree存储，并且只在叶子节点存储数据，在MyISAM引擎中叶子结点存储的数据其实是索引和数据的文件指针两类。

如下图中我们以Col1列作为主键建立索引，对应的叶子结点储存形式可以看一下表格。
在这里插入图片描述
在这里插入图片描述
通过索引查找数据的流程：

  1. 先通过索引文件中查找到索引节点，
  2. 从中拿到数据的文件指针，再指针定位了具体的数据。

2. 辅助（非主键）索引：

以Col2列建立索引，得到的辅助索引结构跟上面的主键索引的结构是相同的。

在这里插入图片描述

InnoDB主键索引与辅助索引的结构

1. 主键索引：

我们已经知道InnoDB索引是聚集索引，它的索引和数据是存入同一个.idb文件中的，因此它的索引结构是在同一个树节点中同时存放索引和数据，如下图中最底层的叶子节点有三行数据，对应于数据表中的Col1、Col2、Col3数据项。

这个索引的 key 是数据表的主键,因此 InnoDB 表数据文件本身就是主索引。

在这里插入图片描述
通过索引查找数据的流程：

  1. 先通过索引文件中查找到索引节点，
  2. 所有的主键索引元素都在叶子节点,并以kv结构,存储对应数据信息

上图是 InnoDB 主索引(同时也是数据文件)的示意图,可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为 InnoDB 的数据文件本身要按主键聚集,

辅助（非主键）索引：

这次我们以数据表中的Col3列的字符串数据建立辅助索引，它的索引结构跟主键索引的结构有很大差别，我们来看下面的图：

在最底层的叶子结点有两行数据，第一行的字符串是辅助索引，按照ASCII码进行排序，第二行的整数是主键的值。

所有叶子节点,非主键索引关联的是 对应主键的值

在这里插入图片描述
通过索引查找数据的流程：

  1. 先通过索引文件中查找到索引节点，
  2. 从中拿到对应的主键索引,并再次通过B+ 树结构查找一次

InnoDB 索引 & MyISAM索引 区别

1.第一个重大区别是 InnoDB 的数据文件本身就是索引文件。

MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。在InnoDB 中,表数据文件本身就是按 B+Tree 组织的一个索引结构,这棵树的叶点data 域保存了完整的数据记录。这个索引的 key 是数据表的主键,

2.InnoDB 要求表必须有主键(MyISAM 可以没有),
如果没有显式指定,则 MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL 自动为 InnoDB 表生成一个隐含字段作为主键,类型为长整形。

3. InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。
辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。

面试题

1. 为什么不建议使用过长的字段作为主键?

因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。

2. 为什么 InnoDB 上尽量采用自增字段做表的主键?

  • 因为 InnoDB 数据文件本身是一棵B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree的特性而频繁的分裂调整,十分低效,
  • 而使用自增字段作为主键则是一个很好的选择。如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。

如下图所示:
在这里插入图片描述

  • 这样就会形成一个紧凑的索引结构,近似顺序填满。由于每次插入时也不需要移动已有数据,因此效率很高,也不会增加很多开销在维护索引上。

3.为什么推荐使用整型自增主键而不是选择UUID？

  1. UUID是字符串，比整型消耗更多的存储空间；
  2. 在B+树中进行查找时需要跟经过的节点值比较大小，整型数据的比较运算比字符串更快速；
  3. 自增的整型索引在磁盘中会连续存储，在读取一页数据时也是连续；UUID是随机产生的，读取的上下两行数据存储是分散的，不适合执行where id > 5 && id < 20的条件查询语句。
  4. 在插入或删除数据时，整型自增主键会在叶子结点的末尾建立新的叶子节点，不会破坏左侧子树的结构；UUID主键很容易出现这样的情况，B+树为了维持自身的特性，有可能会进行结构的重构，消耗更多的时间。

4. 为什么InnoDB 非主键索引结构叶子节点存储的是主键值？

  • 保证数据一致性和节省存储空间，可以这么理解：商城系统订单表会存储一个用户ID作为关联外键，而不推荐存储完整的用户信息，因为当我们用户表中的信息（真实名称、手机号、收货地址···）修改后，不需要再次维护订单表的用户数据，同时也节省了存储空间。
知行合一。。。
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql联合索引详解
2301_77822280的博客
06-10 4591
这样的数据来检索的时候，b+树会优先比较a列来确定下一步的所搜方向，如果a列相同再依次比较b列和c列，最后得到检索的数据；)这样的数据来检索时，b+树可以用a列来指定搜索方向，但下一个字段b列的缺失，所以只能把a列的数据找到，然后再匹配c列的数据了， 这个是非常重要的性质，例如索引是key index (a,b,c). 可以支持a a,b a,b,c 3种组合进行查找，但不支持 b,c进行查找。order by c；a、b列使用索引查找，因b列是范围查询，因此c列不能使用索引，会出现file sort。
MySQL索引合并Index Merge 一条SQL使用多个索引情况分析，以及索引合并失效情况
xzh_blog
04-23 3532
MySQL在5.0版本加入了索引合并优化（Index Merge），索引合并可以同时查询多个索引的范围扫描，并将其结果合并为一个。索引合并只可以合并来自单个表的索引扫描，而不支持跨多个表的扫描。合并可以生成其基础扫描的并集（unions）、交集（intersections）或交集的并集（unions-of-intersections）。
初识MySQL索引
Mr.Cui的Java学习之路
10-01 887
初识MySQL索引
高性能的Mysql读书笔记系列之五（创建高性能的索引）
jdk_wangtaida的博客
09-21 1880
前言： 正文： 总结：
8、索引类型详解
you can you up up up的博客
10-16 2583
索引的类型和存储引擎有关，每种存储引擎所支持的索引类型不一定完全相同。MySQL 索引可以从存储方式、逻辑角度和实际使用的角度来进行分类。 存储方式区分 根据存储方式的不同，MySQL 中常用的索引在物理上分为 B-树索引和 HASH 索引两类，两种不同类型的索引各有其不同的适用范围。 1) B-树索引 B-树索引又称为 BTREE 索引，目前大部分的索引都是采用 B-树索引来存储的。 B-树索引是一个典型的数据结构，其包含的组件主要有以下几个： 叶子节点：包含的条目直接指向表里的数据行。叶子节点之间彼
MySQL高级知识-查询与索引优化分析
02-20
下面是替代方法leftjoin+union(可去除重复数据)+rightjoin实现如下面代码7、A的独有+B的独有MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。你可以简单理解为“排好序的快速查找结构”...
第B章-MySQL索引与数据完整性约束优秀文档.ppt
12-01
MySQL索引是 MySQL 数据库中的一种数据结构，用于快速查找和检索数据。索引可以提高查询速度、减少磁盘I/O和提高数据库性能。MySQL索引可以分为几种类型，包括普通索引（INDEX）、唯一性索引（UNIQUE）、主键...
MySQL-数据库-索引详解
08-30
MySQL 数据库索引是一种特殊的数据库结构，可以快速查询数据库表中特定记录。索引是提高数据库性能的重要方式。 索引的概念 ---------------- 索引是表中数据的目录。如果使用索引，则扫描索引文件，根据索引项，...
浅析MysQL B-Tree 索引
01-19
为什么mysql索引要使用B+树，而不是B树，红黑树 看完上面的文章就可以理解为何B-Tree索引能够快速访问数据了。因为存储引擎不再需要进行全表扫描获取需要的数据，叶子节点包含了所有元素信息，每一个叶子节点指针都...
mysql 联合索引温故
robert198837的专栏
05-29 1277
联合索引又叫复合索引。对于复合索引：Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index （a,b,c）。 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。     两个或更多个列上的索引被称作复合索引。     利用索引中的附加列，您可以缩
组合索引
qq_46661297的博客
05-20 7875
首先我们看看组合索引的与单列索引的区别： 一.为什么要使用联合索引 减少开销 建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！ 覆盖索引 对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2
索引合并和组合索引的比较
热门推荐
Lity丶Le 的专栏
03-12 1万+
在教务系统补考子系统的开发过程中，安排考场的算法运算时间非常慢，需要32秒才能完成考场的分配。查找原因，由于需要在学生补考科目表里面进行查找数据——该表的数据目前已达200万条，所以算法的大部分时间都花在查找数据方面。为了解决以上问题，我们决定对数据表进行分析，创建合适的索引。 在创建索引过程中，我们发现查询语句已经为查询列创建了索引，但是是为每个列单独创建的——SELECT  *  FROM 
mysql索引
qq_45610772的博客
09-19 678
索引 1：什么是索引？ 索引其实就是一种为了加速对数据表中的数据行的检索从而创建的分散存储的数据结构(也可以打个比喻：就是书中的目录) 注意： 1： 一般关系型数据库的中的索引是 硬盘级索引，就是说索引是存放在硬盘中的 2：如果程序访问我们数据库中的数据行数如果是几万的数据的规模的话，其实我们无需在意索引，但是当数据规模达到几千万上亿的情况下我们一般就会使用到索引 2：索引的作用： 索引在数据库中的作用其实是类似于目录在书本中的作用，用来提高查找信息的速度，用索引查找信息的时候，无需对整张表进行扫描，可
Oracle中组合索引的使用详解
abc123098098的博客
10-19 893
在Oracle中可以创建组合索引，即同时包含两个或两个以上列的索引。在组合索引的使用方面，Oracle有以下特点： 1、 当使用基于规则的优化器（RBO）时，只有当组合索引的前导列出现在SQL语句的where子句中时，才会使用到该索引； 2、 在使用Oracle9i之前的基于成本的优化器（CBO）时， 只有当组合索引的前导列出现在SQL语句的where子句中时，才可能...
MySQL 索引类型及数据结构
lmfxiaohuo的博客
11-02 847
1 索引是什么 数据库索引，是数据库管理系统（DBMS）中一个排序的数据结构，以协助快速查询，更新数据库表中的数据。 2 MySQL索引类型 普通索引 Normal 普通索引（由关键字KEY或INDEX定义的索引），目的是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件字段或排序字段创建索引。选择一个数据最整齐、最紧凑的数据列来创建索引。 唯一索引 Unique 与普通索引类似，不同的是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合也必须唯一。在对该列进
Mysql索引底层数据结构与算法
王林冲的博客
10-10 1万+
1，索引到底是什么 2，索引底层数据结构与算法 3，索引最左前缀原理   索引到底是什么 •索引是帮助MySQL高效获取数据的排好序的数据结构 索引的本质 MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。 我们知道，数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，因...
图解 MySQL 索引，清晰易懂，写得太好了！
wadfdhsajd的博客
06-22 316
索引是辅助存储引擎高效获取数据的一种数据结构。很多人形象的说索引就是数据的目录，便于存储引擎快速的定位数据。我们经常从以下几个方面对索引进行分类从 「数据结构的角度」 对索引进行分类从 「物理存储的角度」 对索引进行分类从 「索引字段特性角度」 分类从 「组成索引的字段个数角度」 分类下表是 MySQL 常见的存储引擎 InnoDB，MyISAM 和 Memory 分别支持的索引类型在实际使用中，InnoDB 作为 MySQL 建表时默认的存储引擎对上表进行横向查看可以了解到，B+tree 是 MySQL
Mysql-索引使用规则
qq_47142993的博客
05-11 843
使用规则 最左前缀法则 如果索引关联了多列（联合索引），要遵守最左前缀法则，最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。 如果跳跃某一列，索引将部分失效（后面的字段索引失效）。 范围查询 联合索引中，出现范围查询（<, >），范围查询右侧的列索引失效。可以用>=或者<=来规避索引失效问题。 索引失效情况 在索引列上进行运算操作，索引将失效。如：explain select * from tb_user where substring(phone, 10, 2)
mysql数据库 - 索引详细介绍
最新发布
05-30
索引是一种数据结构，它可以帮助加快数据库表中数据的检索速度。在 MySQL 数据库中，索引通常被称为 B-tree 索引，它可以加速 SELECT、UPDATE 和 DELETE 操作的速度。在本文中，我们将介绍 MySQL 中的索引，包括索引的类型、如何创建索引、如何使用索引以及索引的优化。 ## 索引的类型 MySQL 支持多种类型的索引，包括以下几种常见的类型： - PRIMARY KEY 索引：用于唯一标识数据库表中的每一行记录。 - UNIQUE 索引：用于确保表中某一列的值是唯一的。 - INDEX 索引：用于加速表中的数据检索操作。 - FULLTEXT 索引：用于全文搜索操作。 ## 如何创建索引 在 MySQL 中，可以使用 CREATE INDEX 语句来创建索引。例如，下面的语句创建一个名为 idx_last_name 的索引，用于加速对 employees 表中 last_name 列的检索： ``` CREATE INDEX idx_last_name ON employees (last_name); ``` 需要注意的是，创建索引可能会增加数据库表的插入、更新和删除操作的时间开销。因此，应该谨慎地考虑是否需要创建索引，以及应该创建哪些索引。 ## 如何使用索引 在 MySQL 中，可以使用 EXPLAIN 语句来查看查询语句的执行计划。如果查询语句使用了索引，则在执行计划中会显示使用的索引名称。例如，下面的语句使用 EXPLAIN 来查看对 employees 表进行 last_name 列检索的执行计划： ``` EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith'; ``` 如果查询语句没有使用索引，则可以使用 FORCE INDEX 语句来强制使用指定的索引。例如，下面的语句强制使用 idx_last_name 索引来对 employees 表进行 last_name 列检索： ``` SELECT * FROM employees FORCE INDEX (idx_last_name) WHERE last_name = 'Smith'; ``` 需要注意的是，强制使用索引可能会导致性能下降。因此，应该仅在必要时使用强制索引。 ## 索引的优化 在 MySQL 中，可以使用 OPTIMIZE TABLE 语句来优化数据库表。优化表可以帮助减少表中的碎片，提高表的查询性能。例如，下面的语句优化 employees 表： ``` OPTIMIZE TABLE employees; ``` 此外，还可以使用 ANALYZE TABLE 语句来分析表中的数据分布情况，以便优化索引。例如，下面的语句分析 employees 表的数据分布情况： ``` ANALYZE TABLE employees; ``` 需要注意的是，索引的性能可能会受到数据分布的影响。如果表中的数据分布不均匀，则可能需要重新设计索引或优化查询语句以提高性能。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值