Mysql索引

·  阅读 1151

索引是什么

索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构 解释:索引和数据都是存在磁盘的,需要把数据拿到内存中进行比较查找到对应的数据,如果没有用索引,就会从磁盘全盘扫描,如果走索引,他会通过索引找到对应的数据位置,从而直接去取数据

为什么要用索引

  1. 索引能极大的减少存储引擎需要扫描的数据量
  2. 索引可以把随机IO变成顺序IO
  3. 索引可以帮助我们在进行分组、排序等操作时,避免使用临时表

为什么是B+Tree

二叉树

平衡二叉树

缺点:

  • 层级太深 层级的深度决定了他的IO操作次数,IO操作耗时大
  • 每一个节点数据太少 没有很好的利用操作磁盘IO的数据交换特性,也没有利用好磁盘IO的预读能力(空间局部性原理),从而带来频繁的IO操作

解释:查找从根节点开始,一次比较就需要把一个节点数据加载到内存中进行判断(一次IO),所以层级越深,比较和IO次数就越多。 预读:每一次IO时,不仅仅把当前磁盘地址的数据加载到内存,同时也把相邻数据也加载到内存缓冲区中。当访问一个地址数据的时候,与其相邻的数据很快也会被访问到。每次磁盘IO读取的数据我们称之为一页(page)。一页的大小与操作系统有关,一般为4k或者8k。这也就意味着读取一页内数据的时候,实际上发生了一次磁盘IO,mysql为16K

多路平衡查找树 B-Tree

解释:
多路:节点的子节点最多的个数
关键字个数=路数-1
好处:随着路数的增多,可以存储更多的数据,层级也会变得更短,这样就减少了IO操作

Mysql的B+Tree

B+Tree与B-Tree的区别

  • B+节点关键字搜索采用闭合区间
  • B+非叶子节点不保存数据相关信息,只保存关键字和子节点的引用
  • B+关键字对应的数据保存在叶子节点中
  • B+叶子节点是顺序排列的,并且相邻节点具有顺序引用的关系

为什么选用B+Tree

  • B+树是B-树的变种,他包含B-树的优势
  • B+树扫库、表能力更强(覆盖索引如果要扫库或者扫表的话(什么是覆盖索引,后面会说)B-树要扫描树所有节点,B+树只要扫描叶子节点)
  • B+树的磁盘读写能力更强(因为非叶子节点不存数据,所以每页存储的关键字会更多,路数会更多)
  • B+树的排序能力更强
  • B+树的查询效率更加稳定(B+树永远要找到叶子节点,因为数据是存在叶子节点,所以IO次数是一定的,B-树找到节点就可以返回,同一个树不同数据IO次数是不一样的)

Mysql B+Tree索引体现形式

Mysql有很多存储引擎,创建表的时候指定对应的存储引擎,常用的是Myisam和Innodb,默认为Innodb

Myisam索引

Myisam创建表的时候会有三个文件:.frm文件(表定义文件)、.MYD文件(数据)、.MYI文件(索引)

叶子节点存储的是数据对应的地址

InnoDB索引

InnoDB创建表的时候只有两个文件:.frm文件(表定义文件)、.idb文件(数据) 索引和数据放在一起

解释:

InnoDB都会以主键为索引来组织数据的存储,如果没有指定主键,它会定义一个隐藏的默认的主键索引

聚集索引:指索引项的排序方式和表中数据记录排序方式一致的索引

聚簇索引:聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。术语“聚族”表示数据行和相邻的键值紧凑的存储在一起。因为无法同时把数据行放在两个不同的地方,所以一个表只能有一个聚族索引

所以InnoDB主键索引既是聚集索引也是聚簇索引

辅助索引:除了主键索引之外的索引

从图可以看出,辅助索引叶子节点保存的是主键索引的值

Innodb vs Myisam

总结:Innodb辅助索引叶子节点为什么不直接保存数据地址 而是保存主键值呢,这样还要通过主键再去查一遍索引,保存主键的原因是因为他们首先认为主键索引是最常用的索引,其次就是一般主键是不会变的,改变其他值,辅助索引就不需要改动了

索引的其他补充

  1. 列的离散性:离散性越高选择性就越好
  2. 最左匹配原则:对索引中关键字进行对比,一定是从左往右依次进行,且不可跳过
  3. 联合索引:节点中关键字是多个,单列索引是特殊的联合索引。举例a,b,c三列上创建一个联合索引,create index abc on users(a,b,c) 一般会问哪些会走索引,走什么索引,a、a,b、a,b,c、a,c(走a列,c列是不走索引的)会走索引,b,b,c,c是不走索引的,根据最左匹配原则,一定是从左开始的,abc列值会在节点中从左到右顺序排列的
  4. 覆盖索引:如果查询列可通过索引节点中的关键字直接返回,则该索引称为覆盖索引,覆盖索引可减少数据库IO,可提高查询性能,因为查索引就可以直接返回了,所以一般查询最少列
  5. 索引列的数据长度能少则少

主键为什么建议用int 自增长 而不用uuid原因:1.uuid长度比int长,所以用int,节点存储的关键字会更多,路数会比uuid多,层级会更短。2.主键一般是不变的,顺序自增长,uuid是不定的,当增加数据时,自增长树的改动会远远比uuid的改动下

  1. 索引不是越多越好,越全越好,因为索引是需要维护的,多了也会影响性能
分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改