【MySql学习笔记】MySql索引

2021/9/26 2:10:58

本文主要是介绍【MySql学习笔记】MySql索引,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

索引是什么

    索引:对数据库表中的一列或者多列数据进行排序的一种结构。使用索引可以快速查询数据库表中的特定信息。

存储引擎都用了哪些索引

      InnoDB:B+树索引,叶子结点保存的是完整的数据记录。

      Memory:hash索引。

      MyISAM:B+树索引,叶子结点保存的是数据记录的地址。

为什么InnoDB存储引擎底层数据结构使用B+树,而不是B树?

  • 数据结构的角度

    • 哈希表,HashMap:会出现哈希冲突的问题,如果使用这种结构,那么就需要设计很好的避免哈希冲突的算法,保证数据尽可能的更加的散列;使用这种数据结构要一次性申请很多的内存,比较浪费内存空间;在查找数据的时候,需要进行挨个的匹配,不适合进行范围查询。

    • 二叉树/二叉搜索树:在数据全部是升序或者降序的时候,二叉树就退化成了链表,查询数据比较慢。

    • 二叉平衡树:通过左旋、右旋和双旋的操作,使得树尽量保持平衡;这样一来,查询的性能就能够提升,但是如果需要频繁的插入操作,不断的通过旋转来调整树结构,那么插入的效率就会降低。

    • 使用各种类型的二叉树存储数据的时候,每个结点只能保存一个数据,如果需要插入更多的数据的话,那么只能通过增加树的深度来保存数据,但是加深数据的深度在操作数据库的时候IO次数就会增多。

    • 为什么树越深,IO次数越多?在读取数据的时候,需要磁盘预读,使用树结构的时候,每次读取数据都是4k,但是每一个结点存储的都是一条数据,这样就造成了空间的浪费。

    • 读取数据时的两个概念B树:我们考虑每一个结点多保存一些数据,二叉树变成多茶树的结构,这样一来就可以降低树的深度;每一个结点可以多存放几个结点,结点中的值保存key和value。B树存储数据多,可能树高会高一些,IO效率低。B+树IO次数少。

      • 局部性原理:数据和程序都有聚集成群的倾向,之前被访问过的数据很可能再次被访问到
      • 磁盘预读:内存与磁盘进行交互的时候有一个最小的逻辑单元:页,一般是由操作系统决定他都多大(4k/8k),数据交互的时候可以取页的整数倍进行读写。InnoDB每次读取16K。
    • B+树:在创建索引的时候,尽量给整型的数据创建索引,因为整型占用的字节数小;B+树的非叶子结点只是存储了key值,value由叶子结点保存,叶子结点保存了这些key和对应的value;千万级别的数据可能树能够达到3-4层,尽量不要多于3层,多一层就会增加更多的内存占用,多出来一点内存占用其实影响很大,多个人并发操作的时候,相当于每个人在请求的时候都多出来这么多内存;主键最好是自增的,在插入数据的时候,树的结构不容易发生很大的变化,如果不是自增的数据可能造成树的结构发生改变,增加索引维护的成本。

  • 数据库的角度

    • 存储引擎:不同的存储引擎表示了不同的文件在磁盘上面的表现形式(不同的存储引擎数据的存储形式不同)。

    • InnoDB B+树索引:非叶子结点放入的key值,叶子结点放入的key值和整行的行记录。其数据文件和索引文件是放在一起的。在存储数据的时候必然会有一个key作为聚簇索引,可能是主键,唯一键,或者rowid(rowid占6个字节)

    • MyISAM B+树索引:非叶子结点放入的key值,叶子结点放入的是存储文件的一个地址。其数据文件和存储文件是分开存放的。MyISAM中没有聚簇索引。

    • B+树是B树的变种,B+树的非叶子结点只用来保存索引,不存储数据,所有的数据都保存在叶子节点上。而B树的非叶子结点也会保存数据。B+树的查询效率更加的稳定,都是从根节点到叶子结点。

    • B+树的内部节点没有指向关键字具体信息的指针,其内部节点相对于B树来说更小,同样的空间可以读入更多的节点,B+树磁盘读写代价更低

    • 聚簇索引与非聚簇索引  

      • 聚簇索引:索引树的叶子结点存储的是整行数据,一个表只能有一个聚簇索引;数据和索引是一起存放的,聚簇索引的key值可以是主键、唯一键或者rowid。

      • 非聚簇索引:普通索引,叶子结点存储的是主键的值;在InnoDB中,非聚簇索引也叫做二级索引;如果创建了一个普通索引,那么叶子结点中存放的值是聚簇索引的key值。

索引分类

  数据库:哈希索引,全文索引,空间索引,B+树索引

    InnoDB存储引擎:单列索引,主键索引(聚簇索引),唯一索引,联合索引

    经常进行查询的数据,并且很少进行修改的数据适合创建索引。在插入数据的时候会重新创建索引树,对性能会造成一定的影响。

相关名词

回表

    主键查询方式:搜索主键索引树,其叶子结点保存了整行数据记录,直接获取即可。

    普通查询方式:首先搜索普通索引树,得到对应的主键值,然后根据主键值去主键索引树中在搜索数据,即可获得相应的数据,这个过程就是回表,普通索引树上没有相应的数据,需要回表到主键索引树中在查询一次。回表操作增加了IO次数。

索引覆盖

    查询结果中的字段是索引字段的子集,这个就是覆盖索引。如果顺序不一样的时候,底层优化器会进行优化。

    如果在普通索引树上的查询已经直接提供了结果,不需要进行回表的操作,这样的普通索引就叫做覆盖索引。覆盖索引可以有效的提高查询的效率,是常见的MySql性能优化的手段。

最左匹配

    在联合索引的情况下,不需要定义全部的索引,只要索引满足最左前缀,索引就可以生效,可以利用索引提高查询效率。

索引下推

      MySql5.6之后引入了索引下推优化,在索引遍历的过程中,对索引中包含的字段优先做判断,直接过滤掉不满足条件的记录,减少回表的次数,提高查询的效率。

select * from table where name=? and age=?

    如果没有索引下推:数据存储在存储引擎中,server层负责数据的筛选,先根据name的值从存储引擎中把符合条件的过滤出来,然后在server层对age进行筛选。

    有索引下推:可以直接根据name和age的值从存储引擎中进行筛选数据,不需要在server层中做任何的处理。减少了server层和存储引擎之间的IO次数,回表的次数也减少了。

MySql逻辑架构

  • server层

    • 连接器:验证客户端权限,建立和断开MySql连接
    • 分析器:进行Sql语句的语法分析
    • 优化器:选择索引,生成具体的Sql语句执行计划
    • 执行器:操作存储引擎,执行Sql,返回执行结果
  • 存储引擎层:各个不同的存储引擎都提供了一套读写接口来操作数据库

Sql执行计划

    explain分析执行计划

    show profile分析Sql

索引优化

  • explain分析执行计划

  • 慢查询日志

    • 数据库层面可以看日志
    • MyBatis-Plus插件
  • 避免索引失效

    • 全值匹配:对索引中的具体的列都指定具体的值;进行查询的时候联合索引中的每一个索引都有值;
    • 最左索引法则
    • 尽量使用覆盖索引,避免使用select *
    • 回表查询
    • 通过覆盖索引解决like模糊查询索引失效
    • in走索引,not in索引失效
  • 索引失效

    • 违背最左索引法则
    • 范围查询
    • 索引列有运算操作
    • 字符串不加单引号
    • 使用or分割条件,or前面有索引列,后面没有
    • %开头的模糊查询
    • is null,is not null可能会失效,全表扫描块全表扫描,否则就走索引

 



这篇关于【MySql学习笔记】MySql索引的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程