博达网站建设,建筑企业资质加盟,网站建设模式化的体现,买房咨询平台在线一、为什么使用索引
1.索引是存储引擎用于快速找到记录的一种数据结构。相当于一本书的目录。在进行数据查找时#xff0c;首先查看查询条件是否命中某条索引#xff0c;符合则通过索引查找相关数据。如果不符合则需要全表扫描#xff0c;一条一条查找记录#xff0c;直到…一、为什么使用索引
1.索引是存储引擎用于快速找到记录的一种数据结构。相当于一本书的目录。在进行数据查找时首先查看查询条件是否命中某条索引符合则通过索引查找相关数据。如果不符合则需要全表扫描一条一条查找记录直到找到与条件符合的记录。
2.创建索引减少磁盘的I/O的次数加快查询速度。
二、索引的优缺点
1.索引概述
①索引是MySQL高效获取数据的数据结构
②索引的本质索引是数据结构排好序的快速查找的数据结构
③索引是在存储引擎实现的每种存储引擎不一定支持所有的索引类型。存储引擎定义表的最大索引数和最大索引长度。
2.索引优点
①索引提高数据检索的效率降低数据库的IO成本
②通过创建唯一索引保证数据库表每一行的数据唯一性
③加速表和表之间的连接对于有依赖关系的子表和父表联合查询时可以提高查询速度
④使用分组和排序子句进行查询数据时显著减少查询分组和排序的时间降低CPU消耗
3.索引缺点
①创建索引和维护索引耗费时间随着数据量增加所耗费的时间增加
②索引占磁盘空间存储在磁盘上
③索引提高查询速度同时降低更新表的速度在表中的数据增加删除修改索引动态维护降低数据的维护速度。
4.索引的提示
索引提高查询的速度会影响插入的速度。这种情况下最好的办法是先删除表中的索引然后插入数据插入完成再创建索引 三、InnoDB索引的推演
3.1没有索引情况下查找
3.1.1.在一个页查找
①主键查找
select *
from employees
where employee_id101;在页目录使用二分法快速定位到对应的槽然后再遍历槽对应分组的记录快速找到指定的记录
②其他列作为条件非主键的列
select *
from employees
where last_nameAbel;只能从最小记录开始依次遍历单链表中的每条记录判断是否符合条件效率低。
3.1.2.在很多页查找
①定义到记录所在的页
②从所在的页查找对应的记录
没有索引情况下不论根据主键还是其他列只能从第一页沿着双向链表一直往下找在每个页根据上面的查找方法查找指定的记录。非常耗时所以索引来了。
3.2设计索引
建一个表 ①index_demo表的行格式示意图 record_type:0是普通记录2是最小记录3是最大记录
next_record记录下一条数据的地址
②把一些记录存放到表里的示意图 3.2.1简单的索引设计方案
问题想快速定位到需要查找的数据在哪些数据页可以为数据页建立一个目录
①所有数据页里面的记录的主键是递增的 ②为数据页建立目录当前页的主键最小值key,当前页号page_no 此时把目录项在物理存储器连续存储实现主键快速查找某条记录的功能。
比如查找主键20
①先从目录项根据二分法快速确定主键值为20在目录3中(1220209)
②在页中查找记录方法根据主键值二分法查找具体的记录
3.2.2InnoDB中的索引方案
①迭代1次目录项记录的页采用单向链表实现便于增加和删除 record_type1表示存放的是目录项
record_type0 表示存放的是普通记录
目录项记录和普通用户记录的相同点都会为主键值生成Page Directory(页目录)按照主键值进行查找时使用二分法加快查询速度。
比如查找主键20
先从页30中通过二分法快速定位到对应目录项因为(1220209),定位到页9
从页9使用二分法定位到主键值为20的用户记录
②迭代2次多个目录项记录的页双向链表
假设目录项记录的页最大记录是4现在新添加的记录所以要生成新的目录项的页 比如查找主键20
确定目录项记录页现在目录项页30和页32页30的主键值范围是[1320,页32是不小于320所以在页30
通过目录项记录页根据二分法查找出对应的目录项定位页9
在页9通过二分法定位到主键20的用户记录
③迭代3次目录项记录页的目录页大目录嵌套小目录 生成存储高级目录项的页33这个页中的两条记录代表页30和页32。
④这个结构就是B树 B树都不会超过4层通过主键值查找某条记录最多需要4个页面内的查找。3个目录项页1个用户记录页每个页面有page Directory页目录可以通过二分法快速定位记录。
假设普通数据页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 条记录
3.3常见索引的概念 3.3.1聚簇索引主键构建的B树
概念
聚簇索引是一种数据存储方式所有的用户记录都在叶子节点索引即数据数据即索引。
聚簇表示数据行和相邻的键值聚簇的存储在一起。InnoDB存储引擎会自动创建聚簇索引
特点
①页内记录按照主键的大小排序成一个单向链表有序的
②存放记录的页按照主键值大小顺序排成一个双向链表
③存放目录项记录的页按照主键值顺序排成一个双向链表
④B树的叶子节点存储的是完整的用户记录
优点
①数据访问快聚簇索引将索引和数据保存在同一个B树中。B树的叶子节点就是完整的用户记录
②因为按照主键大小排序成一个单链表排序查找和范围查找速度快
③查找范围的数据时数据紧密相连节省了大量IO操作
缺点
①插入速度严重依赖插入顺序否则会出现页的分裂影响性能。因此InnoDB表定义一个自增的ID列作为主键
②更新主键的代价很高。会导致更新行的移动。对于InnoDB表定义主键不可更新
③二级索引访问需要两次索引查找第一次找到主键值第二次根据主键值找到行数据。
限制
①InnoDB支持聚簇索引MyISAM不支持聚簇索引
②每个表只能有一个聚簇索引该表的主键
③没有定义主键InnoDB选择非空的唯一索引代替。没有的话InnoDB隐式定义主键
④InnoDB的键尽量选择有序的id不建议UUID,MD5,HASH,字符串作为主键
3.3.2非聚簇索引二级索引辅助索引非主键构建的B树
概念
如果使用非主键字段进行查找可以多建几棵B树每个树采用不同的排序规则。比如c2的列,进行查找。按照非主键列建立的B树需要一次回表操作能定位到完整的用户记录这种B树是二级索引。 叶子节点数据页里面的数据按照c2的大小排序成单链表。用户记录不完整存c2和主键项。
回表回聚簇索引
根据c2的列非主键列大小排序的B树只能找到查找记录对应的主键值二级索引没有完整的用户记录所以需要到聚簇索引根据查找出的主键值再查一遍。这个过程就是回表。所以从非主键列条件查找一条记录需要2棵B树
为什么要回表操作把完整的用户记录存放到二级索引的叶子节点不行吗
太占内存空间。冗余大
一张表有1个聚簇索引和多个非聚簇索引。 3.3.3联合索引多个列作为排序规则c2,c3组合 概念
①把各个记录和页按照c2进行排序
②在c2列相同情况下采用c3进行排序。
③本质上是二级索引需要回表操作
④叶子节点用户记录是c2,c3和主键c1构成 3.4InnoDB的B树索引的注意事项
3.4.1根页面内存位置万年不动
B树的形成过程
①当创建一个表的时候底层为这个索引创建一个根节点此时没有目录项和用户记录
②向表中插入数据时先存储在根节点 ③当根节点空间用满时(假设3条)此时根节点成为目录页复制一个新页(页a)
④再插入一个数据时通过页分裂操作得到页b。 ⑤随着插入操作叶子节点变多目录项空间满了。此时根节点复制一个新目录页a
⑥当进行插入的时候会把目录项存放到目录页b 当InnoDB用到这个索引的时候都会从固定的地址找到这个根节点的页号从而访问这个索引。
3.4.2内节点中目录项记录的唯一性除了非叶子节点
①B树索引的内节点目录项是索引列页号。假设一个表的数据是 构建的二级索引B树如过插入一条数据 (8,1,’c’)就不知道是插入页4还是页5了。造成了目录项记录不唯一 ②解决方法是目录项加入主键索引列主键值页号 3.4.3一个页面最少存储2条记录 四、MyISAM中的索引方案B-Tree索引
1.介绍
MyISAM引擎使用BTree作为索引结构但是叶子节点的data域存放的是数据记录的地址
2.原理
InnoDB中索引就是数据。但在MyISAM中索引和数据分开存储非聚簇索引 ①数据文件:用户记录的插入顺序单独储存的一个文件。不分页。不按照主键大小排序。不能使用二分法查找
②索引文件:存放索引信息的为表中的主键创建一个索引。叶子节点存放的是主键值数据记录的地址
3.MyISAM和InnoDB的对比
MyISAM的索引方式是“非聚簇的”InnoDB包含1个聚簇索引
①在InnoDB中根据主键值对聚簇索引进行一次查找就能找到对应的记录。在MyISAM非聚簇二级索引需要一次回表操作。
②InnoDB数据文件就是索引文件索引即数据。但是MyISAM的数据文件和索引文件是分离的索引文件保存的是数据的地址
③InnoDB的非聚簇索引的data域存放的是主键的值而MyISAM存放的是记录的地址。
④MyISAM的回表十分迅速拿着地址直接找数据。但InnoDB通过获取主键后在聚簇索引查找记录。
⑤InnoDB要求表必须有主键MyISAM可以没有。在InnoDB如果没有指定MySQL自动选择一个非空且唯一做主键。如果不存在这种类型MySQL自动为InnoDB隐含字段做主键。 五、索引的代价
空间上代价
每次建立索引都是建立B树每一棵B树的节点都是数据页。一个数据页默认16KB许多数据页组成就是一大片存储空间
时间上代价
每次对表中的数据增、删、改需要修改B树的索引。B树的节点按照索引列的值从小到大顺序排序双向链表。存储引擎需要额外的时间进行一些记录移位页面分裂、页面回收等操作来维护好节点和记录的排序。
六、MySQL为什么选择B树
选择标准
能让索引的数据结构尽量减少磁盘的I/O操作次数。索引是存储在外部磁盘上的只能逐一加载到内存。MySQL衡量查询效率标准就是磁盘IO次数
1.Hash结构数组链表 问题1为什么Hash效率高索引结构使用的是树形呢
①Hash索引仅能满足等于不等于in的等值查询。如果是范围查询时间复杂度是O(n)树形是有序的保持高效率
②Hash索引的缺陷是没有顺序的order by 排序时要重新排序
③对于联合索引Hash值是一起计算的无法对单独一个键或几个索引键查询
④Hash索引不适合在重复值多的列上比如性别年龄。因为遇到Hash冲突时需要遍历桶中的行指针进行比较找到查询关键字非常耗时。
问题2什么是InnoDB自适应的Hash索引
如果某个数据经常被访问当满足一定条件当前的数据页地址就会放到Hash表中当下次查询时直接到找个页的位置。B树也有了Hash的优点 2.二叉搜索树 磁盘的IO次数跟索引树的高度是相关的。
为了提高查询效率就需要 减少磁盘IO数 。为了减少磁盘IO的次数就需要尽量 降低树的高度 需要把原来“瘦高”的树结构变的“矮胖”树的每层的分叉越多越好。
3.AVL树平衡二叉树 针对同样的数据如果我们把二叉树改成 M 叉树 M2呢当 M3 时同样的 31 个节点可以由下面的三叉树来进行存储 4.B树非叶子节点存放的也是数据 查找的关键字是 9步骤
①与根节点的关键字 (1735进行比较9 小于 17 那么得到指针 P1
②按照指针 P1 找到磁盘块 2关键字为812因为 9 在 8 和 12 之间所以我们得到指针 P2
③按照指针 P2 找到磁盘块 6关键字为910然后我们找到了关键字 9。
5.B树 问题1B树和B树的区别
①B树有K个关键字就有K个孩子B树有K个关键字孩子是K1
②B树中只有叶子节点存数据B树叶子节点和非叶子节点都存放数据
③B树所有的关键字都在叶子节点出现构成一个有序链表按照关键字从小到大排序。
问题2为什么B树非叶子节点不存放数据好处是什么
①B树查询效率更稳定B树访问到叶子节点才有数据但是B树非叶子节点和叶子节点都有数据查询不稳定
②B树查询效率更高B树比B树更矮胖。查询IO次数少B树存储更多的节点关键字
③在查询范围上B树效率比B树高。B树的数据都在叶子节点上而且数据递增的。B树需要遍历才能完成查找效率低。
问题3为了减少IO索引树会一次性加载吗
①数据库索引是存储在磁盘上的如果数据量很大。索引会很大超过几个G
②当利用索引时不能把几个G的索引加载到内存。逐一加载到每一个磁盘页磁盘页对应着索引树的节点。
问题4B树的存储能力如何为何说一般查找行记录最多只需1~3次磁盘IO
InnoDB的存储引擎中页的大小为16KB一个页大概存储1000个键值。深度为3的B可以维护10亿条记录
实际情况每个节点不可能填充满因此数据库中2-4层包含根节点因为根节点就在内存所以只需1-3次磁盘的IO操作。
问题5为什么说B树比B-树更适合实际应用中操作系统的文件索引和数据库索引
回答B树的优点
①B树查询效率更稳定B树访问到叶子节点才有数据但是B树非叶子节点和叶子节点都有数据查询不稳定
②B树查询效率更高B树比B树更矮胖。查询IO次数少B树存储更多的节点关键字
③在查询范围上B树效率比B树高。B树的数据都在叶子节点上而且数据递增的。B树需要遍历才能完成查找效率低。
问题6Hash 索引与 B 树索引的区别
①Hash索引不能范围查找B树可以。Hash数据是无序B叶子节点是有序链表
②Hash索引的缺陷是没有顺序的order by 排序时要重新排序。B叶子节点是有序链表
③对于联合索引Hash值是一起计算的无法对单独一个键或几个索引键查询B树可以
④InnoDB不支持哈希索引
问题7Hash索引与 B 树索引是在建索引的时候手动指定的吗
根据MySQL的存储引擎决定的。
InnoDB和MyISAM存储引擎是B树索引但InnoDB提供自适应的Hash
Memory/Heap和NDB存储引擎可以选择Hash索引
