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()，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，在叶子节点中已经可以找到这两个字段，因此不需要进行回表