企业网站服务器,惠州网络推广,html5支持最好的浏览器,抖音流量推广神器软件免费个人主页#xff1a;兜里有颗棉花糖 欢迎 点赞#x1f44d; 收藏✨ 留言✉ 加关注#x1f493;本文由 兜里有颗棉花糖 原创 收录于专栏【MySQL学习专栏】#x1f388; 本专栏旨在分享学习MySQL的一点学习心得#xff0c;欢迎大家在评论区讨论#x1f48c; 目录 一、索引… 个人主页兜里有颗棉花糖 欢迎 点赞 收藏✨ 留言✉ 加关注本文由 兜里有颗棉花糖 原创 收录于专栏【MySQL学习专栏】 本专栏旨在分享学习MySQL的一点学习心得欢迎大家在评论区讨论 目录 一、索引索引介绍MySQL中索引的使用查看索引创建索引危险危险删除索引比较危险 二、索引底层数据结构B-树B树 三、索引总结 一、索引
索引介绍
索引就相当于一本书的目录(index)比如我们如果想要快速查找到书中的某一部分内容我们就可以查看索引来找到我们指定的内容。
而在MySQL中表中存储的数据是非常庞大的最基本的方式就是把表中的数据进行一条一条的进行遍历但是这样的遍历方式的速度太慢了因为数据库中的信息量是非常庞大的。所以我们就需要给数据库中的表建立一个索引建立索引的方式是按照列的维度进行建立的来提高我们的查找速度。 我们不妨来举个例子请看下面这张表 我们首先要知道一张表中一般是存在很多列的而针对列的不同查询的方式也是会有所区别的。比如上面这张表有时候是按照id进行查询的有时候是按照name来进行查询的也有时候是按照qq_email来进行查询的等等 假设我们现在来按照id来进行查询此时在数据库中就会开辟出额外的一块空间来维护和id相关的信息这里是通过特定的数据结构来维护表示id相关索引的情况 如id 为 1 对应表中的某个数据 如id 为 2 对应表中的某个数据 如果后续再来按照id进行查询的时候此时就不需要一条一条遍历数据了。而是从索引中直接进行查询根据索引就能够初步锁定数据所在的位置。 现在有这么一种情况如果我们是按照id来建立索引的话而后面是按照name来进行查询的此时是不能提高效率的如果想要name加快查找的话我们可以给name建立索引 我们现在已经知道索引是用来提高查询速度的。 但是查询速度的提高是要付出一定的代价的即要消耗额外的空间主要是硬盘空间。如果我们想要向表中添加数据的话此时我们还要修改索引如果我们要修改或者删除的数据涉及到索引列的删除或者修改此时我们依然是需要维护索引的。 MySQL中索引的使用
查看索引 查看索引 语法格式show index from table_name; 举个栗子1 输入命令create table student(id int primary key,name varchar(20)); 查看当前表的结果如下图 我们可以看到上表中存在主键idmysql中当表中存在主键的时候系统就会自动地给这个列来创建索引同时由于主键是不允许重复的因此要想插入或者修改数据的话就需要先进行查询来看看插入或者删除后的结果是否存在。 举个栗子2 输入命令create table student(id int unique,name varchar(20));和show index from student; 查询结果如下 可以看到上述中如果使用unique约束的话也会自动生成索引。 举个栗子3测试数据的sql语句:
create table classes(classes_Id int primary key,name varchar(20));
create table student(id int primary key,name varchar(20),classes_Id int,foreign key (classes_Id) references classes(classes_Id));// 查看索引
show index from classes;
show index from student;举个栗子3 查询结果如下 其中classes表和student表中各有一个主键约束自动创建的索引同时student表中存在一个外键约束自动创建的索引。 如上图外键约束也是会自动生成索引的当然外键约束也是会涉及到自动查询的。这里我们来举两个栗子在这之前我们再来回顾一下下面需要用到的student表和classes表请看 现在来进行外键约束也是会涉及到自动查询的的举例。 举个栗子1当我们需要在学生表中插入数据的时候就需要查询classes_Id在classes表中是否存在此处的查询就需要用到classes表的classes_Id(即classes表中主键自动申城的索引)。 举个栗子2当我们想要删除classes表中的一条记录我们就需要需要查询classes表中的classes_Id是否在student表中存在。如果存在的话此时就是删除失败删除失败原因就是子表对父表的反向约束作用。而此处的查询就用到了student表中的classes这一列对应的索引此索引就是外键约束自动生成的。 上述三种情况主键、unique、外键都会使表自动创建出索引。所以此时我们就不需要再对单独的这一列创建单独的索引了。
创建索引危险危险
手动给指定的列创建索引语法格式create index 索引名 on 表名(字段名); 这里依然使用上述的两张表student表和classes表请看 下面我们来简单的创建索引 输入命令create index idx_student_name on student(name); 创建索引结果如下 我们来查看一下我们刚刚创建的索引输入命令show index from student; 如上图我们可以观察到我们刚刚新创建出来的索引在上图中的最后一行。 然而这里我们不得不提到创建索引的操作是非常危险的。真是的怎么我写个sql语句就这么困难呢到处都是危险操作。 如果我们创建的索引所在的表的数据是空的或者表中的数据本身就不多此时我们创建索引的操作就不是危险操作 但是如果表是非空的并且表中的数据量是非常庞大的那么此时如果我们创建索引的话就会引起非常庞大的硬盘IO操作。甚至会消耗大量的cpu资源从而进一步导致数据库被卡死。 所以我们创建表的时候一定要想好到底要不要创建索引提前考虑好表的结构中的哪一列需要创建索引因为后期一旦我们再想创建索引的话就有点不切实际了所以凡事要趁早提前做好规划。 删除索引比较危险
删除索引只能删除手动创建的索引。 删除索引语法格式指定要删除的索引名和表名即可drop index 索引名 on 表名; 还是来举个栗子现在我们要删除刚刚给student表中的name创建的索引如果你忘记了刚刚student表结构那么请看下图来回忆以下 输入命令 drop index idx_student_name on student; 结果如下检验是否删除索引成功请看下图 可以看到我们刚刚创建的idx_student_name索引删除成功了。 上面我们提到过只能删除手动创建的索引我们试试能不能删除student表自动创建的索引classesId输入命令drop index classes_Id on student;结果如下 报错原因外键约束需要索引classes_Id。 由此我们可以看出我们只能删除手动创建的索引而自动创建的索引是不能被删除的。 删除索引这个操作也是比较危险的不能轻易删除索引。
假设有这样一个场景现在我们有一个有很多很多数据的表同时这个数据库此时是处于生产环境的一个数据库。但是我们现在必须要创建删除索引。此时怎么办呢我们要知道数据库服务器往往不仅仅是单台服务器为了整个系统的稳定性通常会搞出多个mysql服务节点这些节点的数据往往都是一样的能够提出相同的服务。以防万一某个mysql服务器节点挂了而其它服务器依然可以很正常运行
此时我们可以这样做先准备好一个新的mysql服务器把表和索引都创建好然后把数据都导过来再把要替换的mysql服务器关闭掉然后再把新的mysql服务器替换掉就好了。
二、索引底层数据结构
MySQL底层的数据结构并不是一个定式而是取决于MySQL使用哪个存储引擎。 那什么又是存储引擎呢 MySQL这个程序中包含了许多模块有的模块是负责解析sql的有的模块是负责网络通信的而有的模块则是用来存储数据的。我们用来存储数据的相关的模块称为存储引擎本质上就是代码中的一个模块这个模块中包含了若干个代码文件以及一堆具体的代码。 而具体如何来进行数据的存储MySQL支持多种存储方案。Innodb是当下最主流的一种存储方案。 数据库中组织数据所使用的数据结构是在硬盘上的。这里就与内存中的数据结构就有所区别了 内存上的数据结构比如我们平常实现的链表、二叉树、顺序表等都是存储在代码内存中的对于访问操作来是是非常不敏感的。其实内存中的数据结构进行访问所占用的时间并不多真正占用时间的而是找数据的过程 硬盘上的数据结构对于访问操作来说是比较敏感的读写一次硬盘的开销是远远大于内存的此时我们就不得不考虑访问所带来的开销了且读写一次硬盘相当于读写约10000次内存。 好了我们现在来看看索引到底是使用哪种数据结构比较合适吧在这之前我们首先要明白索引是用来干什么的嗯对索引的目的其实就是方便我们进行快速的查找。回顾我们以往学到的数据结果中能够做到快速查找的一个是二叉搜索树带有平衡机制的二叉搜索树另外一个就是哈希表了。 但是哈希表并不适合作为索引的底层结构因为哈希表哈希表基本执行过程就是把给定的key通过hash函数映射出一个具体的下标才能定位到具体位置无法做到范围查询或者说模糊查询哈希表只能完成精确查询的任务。 那红黑树其实也不适合作为索引的数据结构虽然红黑数种的元素是有序的可以进行范围查询但是之所以红黑树不能作为索引的数据结构最大的问题就是红黑树的高度当元素比较多的时候树就会变的非常高、或者非常深而树的深度越深比较的次数就越多。而索引这样的数据结构是存储在硬盘上的每次比较都意味着硬盘IO操作读写一次硬盘就相当于约10000次内存读写。所以红黑树不适合作为索引的底层数据结构 在MySQL中的Innodb存储引擎中索引的底层数据结构是B树而在其它存储引擎中也可能会用到hash索引此时就只能应对精准匹配查询的这种情况所以hash并不是不能作为索引只是丧失了部分功能而已。
想要清楚什么是B树我们就需要先知道什么是B - 树。
B-树
B - 树本质是N叉搜索树B 树的一个节点上可以保存多个keyN个key就可以延伸出N1个分支。查找元素的流程就是拿着要查找的元素从根节点出发判断要查找的元素是否在根节点上如果不存在的话我们就需要看看这个元素最终落到哪个区间内然后沿着这个区间的路线继续往后找如果最终的叶子节点依然不存在我们要查找的元素那么这个要查找的元素就真的不存在了。 与二叉搜索树相比B - 树的每个节点可以存储多个元素在元素总个数相同的情况下B - 树的节点数和高度都会大大降低同时B - 树在同一个层次中会存在多个分叉这也会降低节点数和树的高度。
对于数据库来说每个节点上的数据都需要从数据库中读取出来才能进行比较每个节点访问的时候都会进行硬盘的IO操作。随着B树的节点减少的同时节点的访问次数就会减少硬盘IO操作也当然会随之减少。还有一点我们需要知道一个节点中有一个key和一个节点中有多个key的节点访问时的硬盘IO操作的开销是差不多的。 B树的插入元素和删除元素会涉及到拆分和合并的操作解释如下 B树中的一个节点的确可以保存多个key但是也不可以无限的进行存储当key的数量到达一定程度的时候此时就需要把这个节点进行拆分即把这个节点中的一部分key以树的子节点的方式进行重新组织这样就可以保证每个节点中的key的数量不会太多当然也会衍生出新的叶子几点。 B树的合并操作B树的合并操作一般是在节点删除后执行的因为当删除一个节点时会导致 B 树的高度降低可能会破坏 B 树的平衡性需要对 B 树进行合并操作以保持平衡。 关于B树的拆分和合并操作具体什么时候进行拆拆分怎么拆分什么时候合并怎么合并需要看具体的实现同时不同场景下会有不同的应对策略。 B树
好了现在我们来了解下数据库中索引的主角B树N叉搜索树。B树在B树的基础上又做出了一些改进。
B树特点
N个key分出了N个区间每个节点上的最后一个key就是最大值也可能是第一个key即最小值。父节点中的key会在子节点中重复出现以最大值或者最小值的身份这样就会出现一个效果即叶子节点这一层的的数据包含了整个数据的全集。把叶子节点进行链表方式的首尾相连此时叶子节点这样的一个连接方式就可以快速找到上一个或者下一个节点方便进行范围查询只要能确定开头和结尾开头和结尾中间的子链表就是我们查询出来的结果。 好了以上就是本文的全部内容就到这里吧再见啦友友们~~~
三、索引总结
好了现在再来重新回顾一下索引吧
索引简单点来说就相当于一本书中的目录可以加快我们的查找速度。并且索引是按照列来进行查询的。
索引的优劣
优点提高查询速度。缺点占据额外的内存空间。一定程度上会拖慢插入修改删除数据的速度。
索引具体的使用方式
创建索引会自动创建的索引主键、外键、unique。手动创建索引语法格式create index 索引名称 on 表名(列名);查看索引show index from 表名;删除索引drop index 索引名 on 表名;
索引的数据结构
hash不适合作为索引的数据结构原因是因为hash不能够进行范围查询不能够进行模糊匹配查询。红黑树也不适合作为索引的数据结构虽然红黑树可以进行范围查询但是需要引入更多的硬盘IO操作。B树就非常适合索引的数据结构在MySQL中的Innodb存储引擎中的索引就是以B树作为底层数据结构的。
B树的优势
B树非常擅长范围查询即只要找到开头和结尾那么中间那一部分的链表就是我们要查找的数据。所以的查询操作的查询时间非常稳定因为所有的查询操作最终都会落在叶子节点上。而B树的话有点数据可能在根节点找到有的数据可能在叶子节点找到这样的话查询速度就很不稳定。某些时候稳定比快速更加重要由于叶子节点上是完整的数据全集因此表的每一行数据的其它列都可以保存在叶子节点上我们要知道物理结构中并不存在表格这样的数据结构所以我们可以使用B树来存储表中的数据表格只是看起来像一个表格而已而非叶子节点只存储构建索引的key即可ID。此时非叶子节点的存储空间是非常小的以至于我们可以在内存中缓存一份这样的话在进行数据查询的时候就可以通过内存来直接进行数据的比较从而更快速的找到叶子节点上的记录这里又进一步的减少了硬盘IO操作的次数。而B树的话由于B树的整个数据的全集并非只有叶子节点非叶子节点也有可能是数据的全集。B树如果也要把元素存储到每个节点上非叶子节点所占用的空间就会变大从而无法在内存中进行缓存了。当然对应的硬盘IO操作也会增多。 嗯本文就到这里吧再见啦友友们 记得一键三连哦
