长沙哪里可以做网站,官方网站面膜做代理,吉林网站建设企业,福州建设项目管理公司目录#xff1a; 一. 什么是索引 二. 索引应该选择哪种数据结构 三. MySQL中的页 四. 索引分类及使用 一. 什么是索引#xff1a; 1. MySQL的索引是⼀种数据结构#xff0c;它可以帮助数据库高效地查询、更新数据表中的数据。 索引通过 ⼀定的规则排列数据表中的记录#x… 目录 一. 什么是索引 二. 索引应该选择哪种数据结构 三. MySQL中的页 四. 索引分类及使用 一. 什么是索引 1. MySQL的索引是⼀种数据结构它可以帮助数据库高效地查询、更新数据表中的数据。 索引通过 ⼀定的规则排列数据表中的记录使得对表的查询可以通过对索引的搜索来加快速度 2.MySQL 索引类似于书籍的目录通过指向数据行的位置可以快速定位和访问表中的数据如汉语字典的目录索引页我们可以按笔画、偏旁部首、拼音等排序的目录索引快速查找到需要的字。 3. 使⽤索引的⽬的只有⼀个就是提升数据检索的效率在应⽤程序的运⾏过程中查 询操作的频率远远⾼于增删改的频率。 二. 索引应该选择哪种数据结构 下面让我们来逐个分析 1.哈希 如果我们使用第一种哈希哈希确实是一种很优秀的数据结构时间复杂度是 O(1) 查询速度非常快但是但是MySQL并没有选择哈希主要原因是哈希不支持持范围查找哈希是通过哈希函数构建的由数组链表或红黑树组成是通过哈希函数来定位因此哈希不支持持范围查找。 2.二叉搜索树 如果我们使用二叉搜索树的中序遍历是⼀个有序数组 但有几个问题导致它不适合用作索引的数据结构 2.1.最坏情况下时间复杂度为O(N) 2.2.相关搜索树AVL和红⿊树 节点个数过多无法保证树高 包括AVL和红⿊树虽然是平衡或者近似平衡但是毕竟是⼆叉结构 在检索数据时每次访问某个节点的⼦节点时都会发⽣⼀次磁盘IO⽽在整个数据库系统中IO是性能的瓶颈减少IO次数可以有效的提升性能所以树高导致磁盘IO次数多是个硬伤。所以MySQL并也没有选择搜索树 3.N叉树 通过观察相同数据量的情况下N叉树的树⾼可以得到有效的控制也就意味着在相同数据量的情况下可以减少IO的次数从而提升效率。但是MySQL认为N叉树做为索引的数据结构还不够好 4.B树 B树是⼀种经常用于 数据库和文件系统 等场合的平衡查找树 MySQL索引采用的数据结构 该数据结构解决了树高 导致磁盘IO次数多问题 解决了范围查询问题由于 叶子节点保存着所有数据性能也比较均衡。 5. B树 特点 5.1.能够保持数据稳定有序插入与修改有较稳定的时间复杂度 5.2.非叶子节点仅具有索引作用不存储数据所有叶子节点保存着所有数据 5.3.所有叶子节点构成⼀个有序链表可以按照主键值排序的次序依次遍历全部数据 6. B树与B树的对比 6.1.叶子节点中的数据是连续的且相互链接便于区间查找和搜索。 6.2.⾮叶⼦节点的值都包含在叶⼦节点中 6.3.对于B树⽽⾔在相同树⾼的情况下查找任⼀元素的时间复杂度都⼀样性能均衡。 三. MySQL中的页每一个节点就是一页 1.为什么要使用页 在 .ibd ⽂件中最重要的结构体就是Page(页)页是内存与磁盘交互的最⼩单元默认⼤⼩为 16KB每次内存与磁盘的交互⾄少读取一页所以在磁盘中每个页内部的地址都是连续的之所以这样做是因为在使⽤数据的过程中根据局部性原理将来要使用的数据大概率与当前访问的数据在空间上是临近的 所以⼀次从磁盘中读取一页的数据放⼊内存中当下次查询的数据还在这个页中时就可以从内存中直接读取从而减少磁盘I/O提高性能。 解释上面的.ibd ⽂件是innodb储存引擎生成的文件。 注意每⼀个页中即使没有数据也会使用 16KB 的存储空间同时与索引的B树中的节点对应这里可以查看页大小使用命令 show variables like innodb_page_size ; 2. 局部性原理 是指程序在执⾏时呈现出局部性规律在⼀段时间内整个程序的执⾏仅限于程序中的某⼀部分。相应地执⾏所访问的存储空间也局限于某个内存区域局部性通常有两种形式时间局部性和空间局部性。 时间局部性 Temporal Locality 如果⼀个信息项正在被访问那么在近期它很可能还会被再次访问。 空间局部性 Spatial Locality 将来要⽤到的信息⼤概率与正在使⽤的信息在空间地址上是临近的。 3.页文件头和页文件尾 在MySQL中有多种不同类型的页最常⽤的就是⽤来存储数据和索引的索引⻚也叫做数据⻚但不论哪种类型的⻚都会包含⻚头和⻚尾页的主体信息使用数 据⾏进⾏填充数据⻚的基本结构如下 页文件头和页文件尾如图所示 这里上一页页号和下一页页号通过这两个属性可以把页与页之间链接起来形成⼀个 双向链表。 4.页主体 页主体部分是保存真实数据的主要区域每当创建⼀个新页都会⾃动分配两个行⼀个是页内最⼩行Infimun 另⼀个是页内最⼤⾏ Supremun 这两个行并不存储任何真实信息⽽是做为数据⾏链表的头和尾第⼀个数据⾏有⼀个记录下⼀⾏的地址偏移量的区域 next_record 将⻚内所有数据行组成了⼀个单向链表 当向一个新页插⼊数据时将 Infimun 连接第⼀个数据行最后⼀⾏真实数据行连接 Supremun 这样数据行就构建成了⼀个单向链表更多的行数据插⼊后会按照主键从⼩到⼤的顺序进行链接 此时新 页 的结构如下所示 5.页目录 1.说明⼀个⻚有16KB通常会存在数百⾏数据每次都要遍历数百⾏⽆法满⾜⾼效查 询为了提⾼查询效率InnoDB采⽤⼆分查找来解决查询效率问题 2. 因此加入 页目录 这个结构 将页内包括头行、尾⾏在内的所有⾏进⾏分组约定头行单独为⼀组其他每个组最多8条数据同时把每个组最后⼀行在页中的地址按主键从⼩到⼤的顺序记录在页⽬录中在页⽬录中的每⼀个位置称为⼀个槽每个槽都对应了⼀个分组⼀旦分组中的数据行超过分组的上限8个时就会分裂出⼀个新的分组后续在查询某⾏时就可以通过⼆分查找先找到对应的槽然后在槽内最多8个数据行中进行遍历即可从⽽⼤幅提高了查询效率这时⼀个页的核⼼结构就完成了 总结分组时会在页目录中创建一个个的槽最小行单独为一组⼀旦分组中的数据行超过分组的上限8个时就会分裂出⼀个新的分组槽指向对应分组的最后一条记录并且储存该组的主键值方便来 ⼆分查找。 6. B在MySQL索引中的应用 注意 (1).非叶子节点存的是索引页的信息 (2).索引页保存的是主键和子节点的引用信息。 (3).叶子节点保存的是真实数据的信息 (4).叶子节点形成一个双向链表 1.以查找id为5的记录完整的检索过程如下 步骤一首先找到这条记录所对页 步骤二再找到对应的槽 步骤三根据槽的主键值通过二分查找找到对应的记录。 具体如下 首先判断B树的根节点中的索引记录此时 5 7 应访问左孩⼦节点找到索引页2 然后在索引页2中判断主键id的大小找到对应的槽的主键值与之不对 最后槽的主键值通过二分查找找到对应的记录 找到与5相等的记录命中加载对应的数据页。 注意这里每进行一次查找之前都要进行一次IO加载到内存遍历几个节点IO几次。 2.三层B树数据存放数略 索引⻚⼀条数据的⼤⼩为主键⽤BIGINT类型占8Byte下⼀⻚地址6Byte⼀共14Byte⼀个 索引页可以保存 16*1024/14 1170 条索引记录 综合只保存索引的根节点和⼆级节点的索引⻚以及保存真实数据的数据页那么⼀共可以保存 1170*1170*16 21,902,400 条记录也就是说在两千多万条数据的表中可以通过三次IO就完成数据的检索 四. 索引分类及使用: 注意创建多少索引就会生成多少索引树 1.主键索引: 当在⼀个表上定义⼀个主键 PRIMARY KEY 时InnoDB使⽤它作为聚集索引 代码创建主键两种方式 方式三修改表中的列为主键索引修改表中的id列为主键索引 语法添加ALTER TABLE 表名 add PRIMARY KEY ... 修改ALTER TABLE 表名 modify ... 2. 唯⼀索引 当在⼀个表上定义⼀个唯⼀键 UNQUE 时自动创建唯⼀索引 与普通索引类似但区别在于唯⼀索引的列不允许有重复值 下图是创建索引的三种方式 3.普通索引 最基本的索引类型没有唯⼀性的限制 可能为多列创建组合索引称为复合索引或组和索引 方式一创建表的时候创建普通索引 -- 创建表的时候创建普通索引
CREATE TABLE t_index1 (id bigint PRIMARY KEY auto_increment,name varchar(20) UNIQUE,sno varchar(20),index(sno)
) 方式二修改表中的列为普通索引 修改表中的列为普通索引
CREATE TABLE t_index2 (id BIGINT PRIMARY KEY auto_increment,name VARCHAR(20),sno VARCHAR(20));ALTER TABLE t_index2 ADD INDEX (sno); 方式三单独创建索引并指定索引名 -- 单独创建索引并指定索引名
CREATE TABLE t_index3 (id bigint PRIMARY KEY auto_increment,name varchar(20),sno varchar(20)
);-- 索引名推荐使用 idx_表名_列名[_列名]
CREATE INDEX idx_t_index3 on t_index3(sno); 创建普通索引三种方式 -- 创建复合索引-- 创建表时指定索引列
create table t_index_4 (id bigint PRIMARY key auto_increment,name varchar(20),sno varchar(20),class_id bigint,INDEX(sno,name)
)-- 单独创建复合索引并指定索引名
create table t_index_6 (id bigint PRIMARY key auto_increment,name varchar(20),sno varchar(20),class_id bigint
);CREATE INDEX idx__t_index_6_sno_name ON t_index_6 (sno,name);4.全⽂索引 4.1。基于⽂本列(CHAR、VARCHAR或TEXT列)上创建以加快对这些列中包含的数据查询和4.2.DML操作 4.3.用于全文搜索仅MyISAM和InnoDB引擎支持 5.聚集索引 与主键索引是同义词 如果没有为表定义 PRIMARY KEY, InnoDB使⽤第⼀个 UNIQUE 和 NOT NULL 的列作为聚集索引 注意 如果表中没有 PRIMARY KEY 或合适的 UNIQUE 索引InnoDB会为新插⼊的行生成⼀个⾏号并用6字节的 ROW_ID 字段记录 ROW_ID 单调递增并使⽤ ROW_ID 做为索引 6 非聚集索引 6.1.聚集索引以外的索引称为非聚集索引或⼆级索引 6.2.⼆级索引中的每条记录都包含该⾏的主键列以及⼆级索引指定的列。 6.3.InnoDB使⽤这个主键值来搜索聚集索引中的⾏这个过程称为回表查询 7.非聚集索引的查询-回表查询解释 我们知道创建多少索引就会生成多少索引树. 我的理解是通过索引查询到叶子节点的索引记录根据这个索引记录去整棵包含全部数据的索引树中查找数据的过程因为总共查询了两张表索引叫回表查询。 例子 -- 回表查询
select * from student where student_id 1 and name 韩立; 8.索引覆盖 通过索引查询的列 不需要 根据索引记录 去整棵 包含全部数据的 索引树中查找 数据 例子 select sn from student where name 厉飞羽;
