什么是电子商务网站开发,wordpress主题 演员,免费seo课程,龙岩网站建设论坛文章目录 常用索引概念聚簇索引 #x1f389;非聚簇索引#xff08;二级索引#xff09; 数据结构选择Hash结构 ⭐️有序数组二叉搜索树AVL树#xff08;平衡二叉搜索树#xff09;B-Tree#xff08;多路平衡查找树#xff09;BTree ⭐️ MySQL中索引的实现InnoDB 索引实… 文章目录 常用索引概念聚簇索引 非聚簇索引二级索引 数据结构选择Hash结构 ⭐️有序数组二叉搜索树AVL树平衡二叉搜索树B-Tree多路平衡查找树BTree ⭐️ MySQL中索引的实现InnoDB 索引实现首先一个简单的索引迭代两次多个目录项纪录的页迭代三次目录项记录页的目录页 MyISAM 索引实现InnoDB 和 MyISAM 对比 MySQL官方对索引的定义为索引是帮助MySQL高效获取数据的数据结构。 即索引是数据结构可以简单的理解为“排好序的快速查找数据结构”满足特定查找算法这些数据结构以某种方式指向数据。索引的作用相当于图书的目录可以根据目录中的页码快速查找到所需的内容。
在MySQL中索引是在存储引擎中实现的先在索引中找到对应值然后根据匹配的索引记录找到对应的行。首先我们需要花5分钟搞懂MySQL存储引擎
常用索引概念
聚簇索引 和 非聚簇索引二级索引是数据库中的两种索引类型他们在组织和存储数据时有不同的方式。
聚簇索引 聚簇索引简单点理解就是将数据与索引放到了一起找到索引也就是找到了数据。对于聚簇索引来说它的非叶子节点上存储的是索引字段的值而它的叶子节点上存储的是这条记录的数据。 在InnoDB中聚簇索引指的是按照每张表的主键构建的一种索引方式它是将表数据按照主键的顺序存储在磁盘上的一种方式。这种索引方式保证了行的物理存储顺序与主键的逻辑顺序相同因此查找聚簇索引的速度非常快。
如果我们在表结构中没有定义主键那怎么办呢
其实数据库中的每行记录中除了保存了我们自己定义的一些字段以外还有一些重要的 db_row_id字段其实他就是一个数据库帮我添加的隐藏主键如果我们没有给这个表创建主键会选择一个不为空的唯一索引来作为聚簇索引但是如果没有合适的唯一索引那么会以这个隐藏主键来创建聚簇索引。 非聚簇索引二级索引 非聚簇索引就是将数据与索引分开存储叶子节点存储着指向数据页数据行的逻辑指针。 在Innodb中非聚簇索引Non-clustered Index是指根据非主键字段创建的索引也就是通常所说的二级索引。它不影响表中数据的物理存储顺序而是单独创建一张索引表用于存储索引列和对应行的指针。
在InnoDB中主键索引就是聚簇索引而非主键索引就是非聚簇索引所以在InnoDB中
对于聚簇索引来说他的非叶子节点上存储的是索引值而它的叶子节点上存储的是整行记录。对于非聚簇索引来说他的非叶子节点上存储的都是索引值而它的叶子节点上存储的是主键的值索引值。
所以通过非聚簇索引的查询需要进行一次回表就是先查到主键ID在通过ID查询所需字段。
数据结构选择
从MySQL的角度讲不得不考虑一个现实问题那就是 磁盘IO也就是数据页从磁盘加载至内存。如果我们能让索引的数据结构尽少的减少IO次数那对于查询的时间消耗也是最小的。 首先说明下MySQL的索引主要是基于Hash表或者B树
Hash结构 ⭐️
Hash 本身是一个算法相同的输入永远可以得到相同的输出。
数据结构定义为给定表M存在函数f(key)对任意给定的关键字值key代入函数后若能得到包含该关键字的记录在表中的地址则称表M为哈希(Hash表函数f(key)为哈希(Hash) 函数。具有以下特点
Key-Value存储结构查询的复杂度为O(1)无法用于范围查询、模糊、排序情景当数据量过大时容易发生Hash冲突
Hash索引适用存储引擎如表所示
索引/存储引擎MyISAMInnoDBMemoryHash索引不支持不支持支持 Hash结构效率高那为什么索引结构要设计成树型呢 首先Hash 索引仅能满足等值查找、!、in。如果进行范围查询时间复杂度会退化至O(n)而树型的“有序”特性依然能够保持O(log2N)的高效率。其次hash表中数据是无序的在进行 order by 时需要对数据重新排序。最后当数据量过大时Hash冲突较多需要遍历桶中行指针来进行一一比较消耗了大量性能。 上图中hash函数有可能将两个不同的关键字映射到相同的位置这叫做 hash冲突hash碰撞 在数据库中一般采用 链接法 来解决。在链接法中将散列到同一槽位的元素放在一个链表中。 InnoDB支持 自适应Hash索引 另外InnoDB本身虽然不支持Hash索引但是提供 自适应Hash索引。在InnoDB中如果某个数据经常被访问当满足一定条件的时候就会将这个数据页的地址存放到Hash表中。在下次同样查询条件的时候就可以直接找到这个页所在位置这样B树也具备了Hash索引的优点。 我们可以通过 innodb_adaptive_hash_index 变量来查看是否开启了自适应 Hash比如
mysql show variables like %innodb_adaptive_hash_index;
-----------------------------------
| Variable_name | Value |
-----------------------------------
| innodb_adaptive_hash_index | ON |
-----------------------------------
1 row in set (0.06 sec)有序数组
数组中的数据是按关键字 升序或降序排列的具有以下特征
更新代价大插入和删除操作需要移动大量数据。当数据个数不确定时难以确定存储空间的容量无须为表示线性表中数据之间的逻辑关系而增加额外的存储空间。通过二分法时间复杂度为O(logN)查找适用于等值范围查找
class Solution {public int search(int[] nums, int target) {int l 0;int r nums.length;while(l r) {int i (r-l)/2;if(nums[i] target) {return i;} else if (nums[i] target) {r i;} else if (nums[i] target) {l i ;}}return -1;}
}二叉搜索树
如果我们利用二叉树作为索引结构那么磁盘的IO次数和索引树的高度是相关的。为什么这样说呢我们先来看看二叉树的特征
一个节点只能有两个子节点并且子节点也为二叉树若它的左子树不空则左子树上所有节点的值均小于它根节点的值若它的右子树不空则右子树上所有节点的值均大于它根节点的值。
搜索某个节点和插入节点的规则一样我们假设插入的数值为key
如果 key 大于 当前节点则在右子树中进行查找如果 key 小于 当前节点则在左子树中进行查找如果 key 等于 当前节点返回当前节点即可。
举个例子我们以数列34, 22, 89, 5, 23, 77, 91构造一棵二叉搜索树如下图此时查询某个值我们仅需io 3次即可
但是在深度比较大时比如我们给出的数据顺序是5, 22, 23, 34, 77, 89, 91,如下图。此时性能已经退化成链表一致时间复杂度为O(n) 为了提高查询效率就需要 减少磁盘IO数 。为了减少磁盘IO的次数就需要尽量 降低树的高度 需要把原来“瘦高”的树结构变的“矮胖”树的每层的分叉越多越好。
AVL树平衡二叉搜索树
为了防止二叉树像一个「链表」一样存在于是在 1962 年一个姓 AV 的大佬G. M. Adelson-Velsky 和一个姓 L 的大佬 Evgenii Landis提出「平衡二叉树」AVL 。奇特性为任何一个节点的左子树或者右子树都是「平衡二叉树」左右高度差小于等于 1 我们发现如上图五层仅能存储31节点如果使用平衡三叉树仅需四层即可那M叉呢我们需要把树再变的矮胖些 B-Tree多路平衡查找树
B树中所有节点的孩子个数的最大值称为B树的阶用m表示。一颗m阶B树是一颗空树或者是符合一系列条件的m叉树。具有以下特征
m阶B树每个节点最多有m棵子树那么该节点最多有m-1个关键字(元素)m阶是代表每个节点最多有m个分支/子树根节点要么没有子树要么至少有两个子树除根节点外其它非叶子节点至少有m/2(向上取整)棵子树也就是至少有 【m/2(向上取整-1 】个关键字每个节点中的关键字都按照从小到大的顺序排列每个关键字的左子树中的所有关键字都小于它而右子树中的所有关键字都大于它所有叶子节点都位于同一层或者说根节点到每个叶子节点的长度都相同在 B 树中节点存储的是键值和对应的数据 内部节点包含一组键值和指向子节点的指针叶子节点包含键值和对应的数据 如图是一棵 3 阶的 B 树。我们可以看下磁盘块 2里面的关键字为812它有 3 个孩子 (35)(910) 和 (1315)。我们发现(35) 小于 8(910) 在 8 和 12 之间而 (1315) 大于 12完全符合刚才我们给出的特征。
然后我们来看下如何用 B 树进行查找。假设我们想要 查找的关键字是 9 那么步骤可以分为以下几步 我们与根节点的关键字 (2635进行比较9 小于 26 那么得到指针 P1 按照指针 P1 找到磁盘块 2关键字为812。因为 9 在 8 和 12 之间所以我们得到指针 P2 按照指针 P2 找到磁盘块 6关键字为910然后我们找到了关键字 9。
B 树相比于平衡二叉树来说磁盘 I/O 操作要少 在数据查询中比平衡二叉树效率要高。所以只要树的高度足够低IO次数足够少就可以提高查询性能 。
但是我们发现B 树的非叶子节点和叶子节点都存储数据导致每个磁盘块能够容纳的键值对数量较少。除此之外由于数据分布在内部节点和叶子节点上执行范围查询时需要在多个层级上进行搜索和遍历。在执行顺序访问操作时需要进行多次随机磁盘访问。
BTree ⭐️
B树是B树的一些变形它具有以下特点
m阶B树每个分支节点最多有m叉(与B树相同)根节点要么没有子树要么至少有两个子树(与B树相同)除根节点外其它每个分支节点至少有m/2(向上取整)棵子树有n棵子树的节点恰好有n个关键字(B树是有n-1个关键字才有n个节点)所有内部节点中仅包含它的各个子节点中最大关键字和指向子节点的指针。所有叶子节点包含全部关键字和记录数据而且叶子节点按关键字大小顺序排列。并将所有叶子节点链接起来⭐️ MySQL中索引的实现
InnoDB 索引实现
接下来通过推演的方式讲解 InnoDB中的索引实现 页和记录的关系 CREATE TABLE index_demo(c1 INT,c2 INT,c3 CHAR(1),PRIMARY KEY(c1)
) ROW_FORMAT C我们新建了 index_demo 表表中有2个INT类型的列1个CHAR(1)类型的列并且设定c1列为主键。指定 compact 行格式来实际存储记录的。为了便于理解简化了index_demo表的行格式示意图 record_type 记录头信息的一项属性表示记录的类型。 0表示普通记录1表示B树非叶节点记录2表示最小记录3表示最大记录。 next_record它表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量。下面示意图中我们用 箭头来表明下一条记录是谁 各个列的值 这里只记录在 index_demo 表中的三个列分别是 c1 、 c2 和 c3 。 其他信息 除了上述3种信息以外的所有信息包括其他隐藏列的值以及记录的额外信息
那将记录格式示意图的其他信息项暂时去掉并把它竖起来的效果就是这样 它们在页中是这样为了便于理解我们示意图中每页仅3条记录 我们知道的数据页是InnoDB存储引擎中用于存储数据的基本单位。它是磁盘上的一个连续区域通常大小为16KB可以通过配置修改。意味着每次读写都是以16KB为单位的一次磁盘到内存的读取是16KB一次从内存到磁盘的持久化也是16KB。 首先一个简单的索引
在不设置索引之前根据某个搜索条件查找一些记录时为什么要遍历所有的数据页呢因为各个页中的记录并没有规律我们并不知道我们的搜索条件匹配哪些页中的记录所以不得不依次遍历所有的数据页。
现在我们建立一个目录用于快速定位数据页如同下图 以 页28 为例它对应 目录项2 这个目录项中包含着该页的页号 28 以及该页中用户记录的最小主键值 5 。我们只需要把几个目录项在物理存储器上连续存储比如数组就可以实现根据主键值快速查找某条记录的功能了。比如查找主键值为 20 的记录具体查找过程分两步 先从目录项中根据 二分法 快速确定出主键值为 20 的记录在 目录项3 中因为 12 20 209 它对应的页是 页9 。 再根据前边说的在页中查找记录的方式去 页9 中定位具体的记录。
至此针对数据页做的简易目录就搞定了。这个目录有一个别名称为 索引 。
那我们把前边使用到的目录项放到数据页中的样子就是这样假设只能存储4条目录项记录 我们新分配了一个编号为30的页来专门存储目录项记录。目录项记录 和 普通的用户记录 是有些不同的
两者的record_type 不同目录项记录的 record_type 值是1而 普通用户记录 的 record_type 值是0。目录项记录只有 主键值和页的编号 两个列而普通的用户记录的列是用户自己定义的可能包含很多列 另外还有InnoDB自己添加的隐藏列。
除此之外记录头信息里还有一个叫 min_rec_mask 的属性当前记录是目录项页中主键值最小时值是为1其他记录该值都是 0 。
迭代两次多个目录项纪录的页 如上图我们插入了一条主键值为320的新记录此时前三个用于存储记录的页容量已满前边假设只能存储3条数据记录需要新生成页31用于存储该用户记录。此时原先存储目录项记录的 页30的容量已满 前边假设只能存储4条目录项记录所以不得不需要一个新的 页32 来存放 页31 对应的目录项。
此时我们再查找主键值为20的用户记录大致需要3个步骤
首先需要确定目录项记录页根据 二分法 快速确定出在 主键值为20的记录页的目录项页30因为 1 20 320 再通过目录项页 确定用户记录真实所在的页 9因为 12 20 209 在真实存储用户记录的页中定位到具体的记录
迭代三次目录项记录页的目录页 我们生成了一个存储更高级目录项的 页33 这个页中的两条记录分别代表页30和页32如果用户记录的主键值在 [1, 320) 之间则到页30中查找更详细的目录项记录如果主键值 不小于320 的话就到页32中查找更详细的目录项记录。
嗯哼有没有人发现类似于某种数据结构了嘛
没错那就是 B 树。B树的每个节点都对应着一个数据页包括根节点、非叶子节点和叶子节点。B树通过节点之间的指针连接了不同层级的数据页从而构建了一个有序的索引结构。 B树的非叶子节点对应着数据页其中存储着主键指向子节点即其他数据页的指针。 B树的叶子节点包含实际的数据行每个数据行存储在一个数据页中。
通过B树的搜索过程可以从根节点开始逐层遍历最终到达叶子节点找到所需的数据行所在的数据页。而数据页是存储数据行的实际物理空间以页为单位进行磁盘读写操作。
一般情况下我们用到的B树都不会超过4层 因为以上为了便于理解我设定了非常极端的假设存放用户记录的页 最多存放3条记录 存放目录项记录的页 最多存放4条记录 。其实真实环境中一个页存放的记录数量是非常大的假设所有存放用户记录的叶子节点代表的数据页可以存放 100条用户记录 所有存放目录项记录的内节点代表的数据页可以存放 1000条目录项记录 那么
如果B树只有1层也就是只有1个用于存放用户记录的节点最多能存放 100 条记录如果B树有2层最多能存放 1000×10010,0000 条记录如果B树有3层最多能存放 1000×1000×1001,0000,0000 条记录。如果B树有4层最多能存放 1000×1000×1000×1001000,0000,0000 条记录。请问咱们的表里能存放10亿条记录嘛
通过这种方式InnoDB利用B树和数据页的组合实现了高效的数据存储和检索。B树提供了快速的索引查找能力而数据页提供了实际存储和管理数据行的机制。它们相互配合使得InnoDB能够处理大规模数据的高效访问。
MyISAM 索引实现
MyISAM是采用了一种索引和数据分离的存储方式也就是说MyISAM中索引文件和数据文件是独立的。 因为文件独立所以在MyISAM的索引树中叶子节点上存储的并不是数据而是数据所在的地址。所以MyISAM 存储引擎实际上不支持聚簇索引的概念。在 MyISAM 中所有索引都是非聚簇索引。
也就是说在MyISAM中根据索引查询的过程中必然需要先查到数据所在的地址然后再查询真正的数据那么就需要有两次查询的过程。而在InnoDB中如果基于聚簇索引查询则不需要回表因为叶子节点上就已经包含数据的内容了。
因为MyISAM是先出的正式因为存在这个问题所以后来的InnoDB 引入了聚簇索引的概念提高了数据检索的效率特别是对于主键检索。
InnoDB 和 MyISAM 对比
首先MyISAM的索引方式都是非聚簇的与InnoDB包含1个主键聚簇索引。
InnoDB存储引擎中我们只需要根据主键值对 聚簇索引 进行一次查找就能找到对应的记录而在MyISAM 中却需要进行一次 回表 操作 。InnoDB的数据文件本身就是索引文件而MyISAM索引文件和数据文件是 分离的 索引文件仅保存数据记录的地址。InnoDB的非聚簇索引data域存储相应记录 主键的值 而MyISAM索引记录的是 地址 。换句话说InnoDB的所有非聚簇索引都引用主键作为data域。MyISAM的回表操作是十分 快速 的因为是拿着地址偏移量直接到文件中取数据的反观InnoDB是通过获取主键之后再去聚簇索引里找记录虽然说也不慢但还是比不上直接用地址去访问。InnoDB要求表 必须有主键 MyISAM可以没有 。如果没有显式指定则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列则MySQL自动为InnoDB表生成一个隐含字段作为主键这个字段长度为6个字节类型为长整型。
文章转载自： http://www.morning.hjssh.cn.gov.cn.hjssh.cn http://www.morning.nhrkc.cn.gov.cn.nhrkc.cn http://www.morning.lrjtx.cn.gov.cn.lrjtx.cn http://www.morning.wjlbb.cn.gov.cn.wjlbb.cn http://www.morning.btmwd.cn.gov.cn.btmwd.cn http://www.morning.lgwjh.cn.gov.cn.lgwjh.cn http://www.morning.hjwkq.cn.gov.cn.hjwkq.cn http://www.morning.wbqt.cn.gov.cn.wbqt.cn http://www.morning.xbckm.cn.gov.cn.xbckm.cn http://www.morning.yydeq.cn.gov.cn.yydeq.cn http://www.morning.ndmh.cn.gov.cn.ndmh.cn http://www.morning.dyzbt.cn.gov.cn.dyzbt.cn http://www.morning.mztyh.cn.gov.cn.mztyh.cn http://www.morning.dwtdn.cn.gov.cn.dwtdn.cn http://www.morning.ymwrs.cn.gov.cn.ymwrs.cn http://www.morning.qqtzn.cn.gov.cn.qqtzn.cn http://www.morning.qyglt.cn.gov.cn.qyglt.cn http://www.morning.hhrpy.cn.gov.cn.hhrpy.cn http://www.morning.ftzll.cn.gov.cn.ftzll.cn http://www.morning.mbhdl.cn.gov.cn.mbhdl.cn http://www.morning.hnmbq.cn.gov.cn.hnmbq.cn http://www.morning.ntzfj.cn.gov.cn.ntzfj.cn http://www.morning.tqygx.cn.gov.cn.tqygx.cn http://www.morning.fllx.cn.gov.cn.fllx.cn http://www.morning.rkfgx.cn.gov.cn.rkfgx.cn http://www.morning.swkzr.cn.gov.cn.swkzr.cn http://www.morning.kbntl.cn.gov.cn.kbntl.cn http://www.morning.ybgt.cn.gov.cn.ybgt.cn http://www.morning.guofenmai.cn.gov.cn.guofenmai.cn http://www.morning.srjbs.cn.gov.cn.srjbs.cn http://www.morning.tgyzk.cn.gov.cn.tgyzk.cn http://www.morning.xkhxl.cn.gov.cn.xkhxl.cn http://www.morning.rfmzs.cn.gov.cn.rfmzs.cn http://www.morning.rttkl.cn.gov.cn.rttkl.cn http://www.morning.ssxlt.cn.gov.cn.ssxlt.cn http://www.morning.syhwc.cn.gov.cn.syhwc.cn http://www.morning.wwjft.cn.gov.cn.wwjft.cn http://www.morning.srltq.cn.gov.cn.srltq.cn http://www.morning.ktfbl.cn.gov.cn.ktfbl.cn http://www.morning.ktrzt.cn.gov.cn.ktrzt.cn http://www.morning.tlrxt.cn.gov.cn.tlrxt.cn http://www.morning.hypng.cn.gov.cn.hypng.cn http://www.morning.drfcj.cn.gov.cn.drfcj.cn http://www.morning.mydgr.cn.gov.cn.mydgr.cn http://www.morning.xzrbd.cn.gov.cn.xzrbd.cn http://www.morning.trfrl.cn.gov.cn.trfrl.cn http://www.morning.tymnr.cn.gov.cn.tymnr.cn http://www.morning.rhjhy.cn.gov.cn.rhjhy.cn http://www.morning.kbkcl.cn.gov.cn.kbkcl.cn http://www.morning.duckgpt.cn.gov.cn.duckgpt.cn http://www.morning.wjlrw.cn.gov.cn.wjlrw.cn http://www.morning.hytr.cn.gov.cn.hytr.cn http://www.morning.tplht.cn.gov.cn.tplht.cn http://www.morning.lfttb.cn.gov.cn.lfttb.cn http://www.morning.cgbgc.cn.gov.cn.cgbgc.cn http://www.morning.lffrh.cn.gov.cn.lffrh.cn http://www.morning.wnhgb.cn.gov.cn.wnhgb.cn http://www.morning.etsaf.com.gov.cn.etsaf.com http://www.morning.jqpyq.cn.gov.cn.jqpyq.cn http://www.morning.kjsft.cn.gov.cn.kjsft.cn http://www.morning.zkrzb.cn.gov.cn.zkrzb.cn http://www.morning.mbzlg.cn.gov.cn.mbzlg.cn http://www.morning.kgnrh.cn.gov.cn.kgnrh.cn http://www.morning.bmsqq.cn.gov.cn.bmsqq.cn http://www.morning.sfqtf.cn.gov.cn.sfqtf.cn http://www.morning.kbqbx.cn.gov.cn.kbqbx.cn http://www.morning.wmglg.cn.gov.cn.wmglg.cn http://www.morning.nzcys.cn.gov.cn.nzcys.cn http://www.morning.bzlgb.cn.gov.cn.bzlgb.cn http://www.morning.mkkcr.cn.gov.cn.mkkcr.cn http://www.morning.srkwf.cn.gov.cn.srkwf.cn http://www.morning.mpnff.cn.gov.cn.mpnff.cn http://www.morning.xjnjb.cn.gov.cn.xjnjb.cn http://www.morning.pfbx.cn.gov.cn.pfbx.cn http://www.morning.mxptg.cn.gov.cn.mxptg.cn http://www.morning.zbtfz.cn.gov.cn.zbtfz.cn http://www.morning.wdykx.cn.gov.cn.wdykx.cn http://www.morning.kjxgc.cn.gov.cn.kjxgc.cn http://www.morning.sgqw.cn.gov.cn.sgqw.cn http://www.morning.lgtcg.cn.gov.cn.lgtcg.cn
