mysql索引详解,了解mysql索引看这一篇就够了

一、什么是索引

索引是一种数据结构,在数据库中该数据结构,能够辅助存储引擎高效获取数据

二、索引在MySql架构中的位置

三、索引的分类

  • 按数据结构分类
    • B+tree、Hash、Full-text索引
  • 按物理存储分类
    • 聚簇索引、二级索引(也叫辅助索引)
  • 按字段特性分类
    • 主键索引、唯一索引、普通索引、前缀索引
  • 按字段个数分类
    • 单列索引、联合索引(也叫复合索引)

四、常用存储引擎中支持的索引结构

 

在MySql中Innodb是默认的存储引擎,且B+tree索引是最常用的索引结构

五、B+tree、B-tree、红黑树、Hash表的区别对比

B+tree的存储示意图

B-tree的存储示意图

红黑树的存储示意图

和B-tree的不同点

  • B+tree只在叶子节点存储数据、而B-tree的非叶子节点也存储数据,所以B+tree单个节点的数据量更小,在相同的磁盘IO下能查询更多的节点
  • B+tree的叶子节点采用单链表链接,适合mysql中基于范围的查询场景,而B-tree无法做到

和红黑树的比较

  • 对于有n个叶子节点,度为d的B+tree,查询的时间复杂度为O(log(dN));度的定义是:
  • 度(degree)为节点所允许的最大子节点的个数,一般在实际的应用中,degree都是大于100,因此数据达到千万级别时,B+tree的高度依然可以维持在3-4左右,保证了只需要3-4次的磁盘IO就能查询到目标数据
  • 而红黑树是二叉树,节点的子节点个数最大为两个,也就是degree为2,搜索复杂度为O(logdN),log以d为底N的对数
  • 红黑树要检索到目标数据所需要的磁盘IO次数多

和Hash表比较

  • Hash适合的等值查询,且存在hash碰撞等问题
  • B+tree适合范围查询,而且这种业务也比较多,使用场景更广泛

六、从物理存储结构了解B+tree索引

从物理存储的角度,索引类型可以分为聚簇索引、二级索引

这里假设有个学生表,有三个表字段:id->自增主键;name->二级索引;

聚簇索引存储示意图

二级索引存储示意图

  • 聚簇索引
    • 聚簇索引的每个叶子节点都存储一行完整的数据
    • 叶子节点间采用单项链表链接
    • innodb引擎,要求必须要有聚簇索引,默认在主键字段上建立聚簇索引,如果没有指定主键,则在第一个Not Null唯一键索引上建立聚簇索引,如果两者都没有,则会自动生成一个隐式自增id列,并在此列上建立聚簇索引
  • 二级索引
    • 聚簇索引以外的其它索引,都是二级索引
    • 二级索引的叶子节点,并不是存储一行完整的表数据,而是只存储了主键的值
    • 由于二级索引的叶子节点只是存储了主键的值,因此通过二级索引查询数据,需要根据主键的值,回表查询完整的表数据。

二级索引的回表查询

     

我们通过二级索引name进行查询时,因为叶子节点只是存储了聚簇索引的id值,因此为了拿到该行的其他字段信息,还需要进行回到聚簇索引根据id拿到数据,回表过程还有走一次聚簇索引的查找过程,因此会比较耗时;但是如果叶子节点包含了查询的所有字段时,就不需要进行回表了,这时候就是触发了索引覆盖。比如select id,name from xx where name = '';这个sql中只需要查询id和name,在叶子节点中已经可以找到这两个字段,因此不需要进行回表

 

 

 

 

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值