MySql索引分析

一、MySql索引的介绍

1、定义

索引是帮助MySQL高效获取数据的数据结构,其本质是数据结构。

简单的可理解为一句话:排好序的快速查找数据结构,用于排序和快速查找。

一般来索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上。

2、常见疑问

为啥都说,索引不能建立在频繁更新的字段上?

原因是:因为你在平凡更新数据的时候,索引也同时会去维护操作数据的指向,这样我们在频繁更新数据的时候就会慢。

3、索引的优势和劣势

优势:

  • 提高数据检索的效率,降低数据库的IO成本。
  • 通过索引列对是数据进行排序,降低数据排序的成本,降低了CPU的消耗。 

劣势:

  • 实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的 。
  • 虽然大大提高了查询的速度,同时也会降低更新表的速度。

二、索引的分类

单值索引:即一个索引只包含一个单列,一个表可以有多个单列索引(一般不能建立5个索引)。

唯一索引:索引列的值必须唯一,但允许为空值。

复合索引:即一个索引包含多列。

三、索引的数据结构

  • 二叉树
  • 红黑树
  • Hash表
  • B-Tree

1、二叉树数据结构的索引

二叉树算法索引数据结构普分布图

如果我们要查select * from t where col2=89这行数据情况:

对应图的右边就有个二叉树的算法,结构就是一个key,value的存储结构,key就是我们的索引,value就是索引字段所在行的那个磁盘地址文件指针。

根据二叉树的特点,右边的子节点是大于父节点的,左边小于父节点,分析:

如果col2数据是依次递增的情况,那按照我们的二叉树算法的特性,就变成了单边增长的情况了,最终就会变成一个链表的形式。

出现链表的形式就会像上图, 这样我如果要查col2=0006,会查6次,很显然不符合我的要求。

2、红黑树数据结构的索引

测验分析图:

根据图上的编号得出:

其实红黑树也是一种二叉树,它叫做二叉平衡树,如果单边增长的太多,就会自动去平衡,随着数据量的增大,会不停的去移动平衡。

对第一种二叉算法的优化:

在使用红黑树数据结构后相比第一种的二叉树查找要优一些,很明显,查col2=0006只用了3次。

劣势:

假如在我们实际的项目中,有百万级的数据,通过测验分析图和二叉平衡树的特点,会出现这棵树的高度越来越大,这样我们查最底下的数据,这样从上往下查,又会查很多次,随着数据量的增大,红黑树数据结构算法不符合实际的情况。

2、B-Tree和B+Tree数据结构的索引

问题?

在红黑树的基础上再次优化,优化的点很显然就是控制我们树的高度问题,让这棵树只有3-5层的高度就能存放千万级的数据问题?

方案设想:

让每个节点存放更多的索引元素,让树横向发展,控制树的高度,这样其实就是我们B-Tree结构。

B-Tree分布图:

规则:

mysql给我们的一个节点设置成16kb,在运行的时候先将一个节点放入到我们的内存当中,

疑问?

那千万级的数据完全可以放在一个节点呀,这样树的高度是1,这样查不是很快吗?问题是如果是千万级的数据都放在一个节点,这样一进来就将这千万级的数据打入到内存,很明显这样会消耗我们的很大内存,显然是不合适的。

B+Tree分布图:

B+Tree特点:

  • B+Tree是一颗多叉平衡树,其叶子节点也是一个二叉树。
  • 与B-Tree的区别在于,B+Tree多了非叶子节点,从上图可以看出,第一个和第二个节点存放都是索引元素,并没有存放data值,叶子节点存放了所有的索引元素,而且每个节点还多了个指向箭头。

为啥要这样分布呢?为啥会有非叶子阶段?

原因是:一个节点存放的索引是有大小限制的,如mysql默认设置一个节点为16kb,如果非叶子节点不存放我们的data数据指针,这样我们一个节点就可以存放更多的索引元素。

四、根据不同存储引擎来分析B+Tree的索引

1、MyISAM存储引擎索引实现

解析图

查看数据库的data文件可以看出MyISAM表在我们的磁盘上存放了三个文件分别是:

.frm文件:存放表结构定义的数据。

.MYD文件:存放数据的文件。

.MYI文件:存放索引的文件。

索引查找过程:

比如我们现在要查找上图中的col2=89的元素,那么执行过程就是:

通过B+Tree依次比对查找,查找到一个节点就放到我们内存当中,最终查找了索引89这行索引元素也就是key,就可以找到这个key对应value,这个vlue就是当前索引所在那一行的磁盘文件地址指针,然后这个指针就会去我们的.MYD文件快速的定位到需要查找的这一行。

2、InnoDB存储引擎索引实现

解析图

查看数据库的data文件可以看出InnoDB表在我们的磁盘上存放了二个文件分别是:

.frm文件:存放表结构定义的数据。

.ibd文件:存放数据和索引数据文件,将其数据文件和索引数据文件合并到一个文件了。

                 本身就利用了b+tree的结构存储模式。

特点:

  • 与MyISAM分析图对比明显看出叶子节点存储实施有所不同的,b+tree叶子节点不仅存放了索引的元素还存储了具体的数据。
  • 它是个聚集索引,而MyISAM是一个非聚集索引,这两者的定义的就是来源于我们的存储方式的不同,下面我们来分析下:

          InnoDB存储引擎叶子节点把索引和数据都存放在一起了,也就是全部存放在.ibd文件了,不需要像MyISAM存储引擎那            样,数据和索引分开单独文件存储,也InnoDB存储引擎不需要去找myi文件了, 很显然查找的效率比MyISAM存储引              擎要快。

InnoDB表为啥必须要有主键?并且推荐使用整形的自增主键?

mysql设计如此必须要有主键?

如果你没有建立主键,mysql后台会给你默认选择一个字段加一个主键,因为它必须要主键,才能组织数据。

为啥整形的主键?

如果用字符型的uuid的方式去设置主键的话,那比较起来就麻烦了,字符串要转成我们的ACSII码然后在进行比较很显然比我们的整形直接拿来比较效率要低,占用的空间上整形也比字符串要少,节约空间。

为啥要自增?

b+tree索引的特性是从左到右依次递增的过程,在我们叶子节点有个指针,这样顺序指定会加快我们的查找效率。

如果不是递增去存放的,实例情况:1,2,3,4,5,6,7,9,10。这里缺少了一个8,如果我在10后面再去插入8,这时候我们的树就会分裂,树做了自动平衡,这些都是有性能开销的,会做大量的运算。

五、哪些情况下适合建立索引?哪些情况下不适合建立索引

哪些情况下适合建立索引?

  1. 主键自动建立唯一索引。
  2. 频繁作为查询条件的字段应该创建索引。
  3. 查询中与其他表关联的字段,外键关系建立索引。
  4. 频繁更新的字段不适合建立索引。
  5. where条件里面用不到的字段不创建索引。
  6. 单键/组合索引选择问题(在高并发下倾向选择组合索引)。
  7. 查询中排序的字段,排序字段若通过索引去访问将大大提高排序的速度。
  8. 查询中统计或者分组的字段需要创建索引,(因为分组会设计到排序的问题)。

哪些情况下不适合建立索引?

  1. 表记录太少。
  2. 经常增删改的表:提高了查询的速度,同时却降低了更新表的速度,因为MySQL在更新表数据的同时还要更新保存下索引文件。
  3. 数据重复且分布平均的表字段,应该只为最经常查询和最经常排序的数据列建立索引。
  4. 注意如果某个数据列包含许多重复的内容,为他建立索引就没有太大的实际效果。(如:性别字段)

导致索引优化的点

mysql事物相关问题:https://blog.csdn.net/madongyu1259892936/article/details/98977500?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522159054489119724811800708%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=159054489119724811800708&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~first_rank_ecpm_v1~pc_rank_v3-2-98977500.first_rank_ecpm_v1_pc_rank_v3&utm_term=mysql%E4%BA%8B%E7%89%A9%E9%9D%A2%E8%AF%95%E9%A2%98

  https://www.bilibili.com/video/BV1aE41117sk?p=7

https://www.bilibili.com/video/BV1KW411u7vy?p=18

©️2020 CSDN 皮肤主题: 游动-白 设计师:上身试试 返回首页