数据库索引1

最新推荐文章于 2021-06-17 21:50:06 发布

一个诺诺前行的后端程序员

最新推荐文章于 2021-06-17 21:50:06 发布

阅读量199

点赞数

本文链接：https://blog.csdn.net/weixin_43689953/article/details/116581414

版权

在这里插入图片描述

当我们执行
select * from t where t.col2=89
此时没有建立任何索引的时候，mysql实际上是从这个表中的第一行开始查，一行一行的查找，直到找到为止,也就是说他此时会找6次,做6次Io
我们的数据在磁盘上面实际上是随机分布的，他不一定是连在一起的，所以oxo7这些就是这一行数据在磁盘中的位置
我们从磁盘中那一条数据，说白了我要和磁盘做一次交互叫做IO读取，
此时如果数据量特别大的话，此时一行一行的拿，拿完之后再去比对，这样的话效率特别低
基于此索引就诞生了

我们现在要做的就是减少我们的查找次数要拿到真正的数据。我们把次数控制在可控范围内就可以了
第一二叉树 对数据建立索引 为key, value作为磁盘地址，看右边 我们只需要经过2次就可以查到了，看的出来 二叉树会比全表效率高,但是此时如果对col1建立索引的话
select * from table where t.col1='6' , 对col1这种逐行递增的建立索引 的话

在这里插入图片描述

此时还是查找了6次跟全表扫描没多大区别，所以二叉树对于单边增长的列建立索引是没什么帮助的

有没有更好的数据结构来构建我们的索引呢
红黑树

在这里插入图片描述

我们对col1和col2建立红黑树结构，利用红黑树的重平衡机制【这个左旋右旋变色 我也没搞明白】,似乎是比二叉树强
但是红黑树是有缺点的就是他数据量越大 树的高度也越大
假设你树的高度在20的话，再查数据要经过20次遍历查找，也就是需要20次IO操作，效率低下肯定不行
所以红黑树的弊端 就是树的高度不可控

对于树而言，我们期望在树中查找，查找次数跟树的高度是有关系的，树的高度越小，查找效率越高
对红黑树做优化，让他数的高度尽量可控，即便几千万的数据量，让他的高度控制在一定范围比如说4，如果能做到的话，问题解决

在这里插入图片描述

红黑树只有一个根节点，如果我可以把这个节点一开始画大一点或者说在磁盘中划分的区域大一点点，我让里面可以放更多的更多的索引元素，一个大的节点里面放很多小的索引元素这样是不是就可以解决问题了
存储相同的500w条数据， 这样横向节点放的索引越多 [500w 高度越低],data里面放的可能是这条数据在磁盘中的位置

在这里插入图片描述

但是mysql依然没有使用B树作为索引，我们再来看下B+树,B+树对B树做了改造，将data数据全部移动到最底层了，B数中间没有指针，B+树相邻2个系节点中间有指针，他的非叶子节点相当于冗余索引

在这里插入图片描述


假设索引采用的B+树,此时我要找col=30,怎么找,从根节点找，把根节点整个节点的元素全部Load到内存里面 然后定位30在15-56中间，然后再	继续load 下一个节点，将下一个节点load到内存，在进行比对 直到比对到最底层叶子节点的话，就可以拿到我的具体数据了

此时一个节点在mysql中叫做页，就跟翻书一样的一页,页节点大概分配了16kb，比方说一张表你用bigint做主键，bigint在mysql中大概占了8个byte,空白地方只是存了下一页的地址，一个地址大概占用了6个byte，也就是说他一页就可以存储16384/（8+6）=1170个元素
然后他的子节点就是最底层的节点，可能会大点因为他携带了data,比如说15，可能放了整行的数据，通常一行数据可能就1Kb
也就是说你此时如果一个B+树装满了 可以放 1170*1170*16 的数据，可以看到2000多万的数据量放在B+树里面高度就是3，在进行查找数据的时候，只需要找3次就可以了，
B+树和B 树的区别就是将data数据全部移动到了叶子节点，这样的话 一页就能存储更多的数据，

在这里插入图片描述

 非叶子节点一个节点能放更多的元素 ,存储同样的数据 树的高度就越小

我们再来看下这些表中索引到底是怎么存储的，或者说我们的B+树到底在表中是怎么存储的
当我们执行 select * from t where t.col1='30' 的时候，他是从根节点开始遍历，遍历到叶子节点 然后叶子节点上就[能找到对应的数据的磁盘地址],然后就能找到对应的数据

我们表中数据是存储在磁盘中，索引也在磁盘中
我们看下myIsam存储引擎如果走了索引的话怎么在各个文件中找数据

在这里插入图片描述

首先我们的知道我们的索引存在的地址，通常情况下，你不改变他的目录一般会在/data文件夹下,一个数据文件就对应一个数据库实例,这些文件中就存储关于数据库的索引，数据以及表结构 相关的信息
我们接下来就结合我们的数据库表看他的 B+树在表中到底是怎么存储的
看MyIsam存储引擎建立的表
myIsam  frm 数据表结构的相关的信息存储在这了
		myd myIsam Data  数据文件
		myI 索引
如果你这个表是以myIsam作为存储引擎建立的表，他是怎么取数据的
假设此时sql 是 select * from t where col1='30'
假如把col1 设置为索引,呢么这个表对应的myi文件内部会把col1这一列数据以这个B+树组织好，呢么这张表的数据记录在myd文件中， 我们要查找一条记录 比方说col1=30,呢我该怎么去找，同样从根节点开始遍历，最终遍历到叶子节点，他的data元素中放的是索引所在行的磁盘文件地址， 我拿到这个文件地址就可以到我们的myd文件中去定位具体的数据行了
---------------------------------------->
也就是说 我首先在myi文件中去定位索引元素，然后再根据索引元素对应的磁盘文件地址再到myd文件中定位具体的这一行数据，这是myIsam存储引擎，他执行一个sql 如果走了索引怎么在各个文件中找数据的

在这里插入图片描述

innerdb底层索引又是怎么存储的， 或者说对于innerDb他的索引是怎么存储的
innerdb表的磁盘有2个文件 一个 frm 结构 
						  idb 索引以及数据
						idb文件里面把整张表的数据以及索引都放进去了，但是里面的数据也是按照B+树组织的，叶子节点的data中存储的是这一列数据

myIsam存储引擎的中B+树的data 放的是你索引所在行的磁盘文件地址
但是innerDb存储引擎中B+树的data 放的是你索引所在行的其他列数据，这是innerDb和MyIsam他的索引存储的区别，还是这张表下面就是innerDb存储的数据结构

在这里插入图片描述

现在说下什么是聚集索引
聚集索引就是叶子节点会包含一整行的完整数据记录，innerDb的主键索引实际上就是聚集索引,非聚集索引 MyISAM存储引擎他的主键索引就是一个非聚集索引，怎么说呢 叶子节点只包含了索引，但是他没有包含我们的数据，数据在另外一个文件中分开存储，myd文件，索引在myi文件
对于innerDb来说，索引和数据在同一个idb文件，对于MyIsam来说 索引和数据分开存储的我们叫做非聚集索引/非聚簇索引

现在用innerDb建立的表一般会建立一个主键，并且是整形自增的
第一 为什么innerDb需要建立一个主键，idb文件在存储这些数据的时候，这些数据必须要用一个B+树来构建，B+树从哪里来啊 如果你这个表中有主键索引的话 主键自带主键索引，就可以用这个主键索引来构建这个B+树,
如果不建立主键的话，他会从第一列开始 选择所有元素都不想等的一列，用这一列建立索引来构建B＋树

你建立一个innerDb的表尽量设置一个主键，因为主键索引可以构建这个B+树
第二使用整形并且自增， 用uuid可行么
当我们找一个索引元素，我们从根节点开始查找，在整个找索引定位过程中，实际上要经历过很多次的数据比较大小，整形的比如说1<2 比 字符串 比较的快， 用1<2 这种效率高点，还有整形的话 比uuid占的空间小，你占的空间越小，这样你的一个节点就可以存储更多的数据

聚簇索引和非聚簇索引那个更快，单纯从索引角度来说聚簇索引马上就能拿到这条记录，非聚簇索引需要我跨文件去拿

在这里插入图片描述

innerDb默认是B+树索引,还有一种是hash索引，假设我把col3作为hash索引存储，col3大致是怎么存储的呢,我在进行存储索引的时候先对这个索引字段进行一个hash运算，还是这些数据，我先把这个值去计算一个hash值，比方说hash(Alice),然后我把这个值放到到hash桶中，如果发生碰撞，就放在链表上 当我们再次查找name，比方说alice,首先hash(Alice) ，就可以找到对应的索引元素， 他下面的就是索引元素磁盘所在行的文件地址

在这里插入图片描述

hash索引都是在磁盘上面，有可能我只需要经过一次磁盘io 就能定位到我们这个节点了。会相对比较快，
但是hash索引这种结构不支持范围查询，比如说name>zhangsan
此时他的进行全表扫描，这个结构是没有比较大小的概念

在这里插入图片描述

B+树就支持范围查找么？
可以B+树在叶子节点有指针，因为B+树底层是从小到大排序的，当col>20的时候，先定位到col=20 然后就可以拿到之后的数据了
B树就没有[范围查找]，B树没有指针连接访问下一个节点
B树和B+树的区别
第一就是 B+树叶子节点有指针相连， B 树叶子节点没有指针项链
第二就是 B+树中把data数据都移到叶子节点了， 上面相当于冗余数据


```bash
索引有主键索引和 非主键索引，
比方说对于col3是怎么存储的  依然是B+shu 
叶子节点放的是主键的id
二级索引这么设计B+树，不把整张表中数据都放在这里么，这样会节约空间 因为我此时只放主键索引的id
每加一个索引在我们的索引文件中就会多加一个树结构

工作中用的最多的就是联合索引[符合索引]/ 多个字段共同组织成一个索引，一般建立联合索引，而不是做单值索引
联合索引的底层数据结构
对于innerDb来说，我如果通过二级索引来查找.他实际上
首先定位到我们的主键，再用主键，去主键索引中找到真正的呢一行记录
二级索引也是一个非聚集索引，因为他此时要做一次回表操作，
还要到主键索引/聚集索引中再去找一次，找到我们真正的数据，