MySQL 索引及B+树

MySQL 索引及B+树

什么是索引?

索引是为了加速对表中数据行的检索而创建的一种分散存储的 数据结构

为什么用索引

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

为什么用B+树

  • 普通的树,当数据的id 一直是增量的时候,树的什么就一直增加,从而失去了索引的意义
  • 使用平衡二叉树,可以避免普通树的弊端,但是平衡二叉树只有两路。
  • 数据量大的时候,树太深了,IO操作次数增加,IO操作耗时大。
  • 每一个磁盘块(节点/页)保存的数据量太小了.没有很好的利用操作磁盘IO的数据交换特性, 也没有利用好磁盘IO的预读能力(空间局部性原理),从而带来频繁的IO操作
  • B树:可以解决,平衡二叉树 只有两路,树太深 节点太小的问题。因为B树 是多路的树。MySQl中默认一个索引节点的磁盘大小为16K。
  • B+树:B树的升级版。
  • B+节点关键字搜索采用闭合区间
  • B+非叶节点不保存数据相关信息,只保存关键字和子节点的引用
  • B+关键字对应的数据保存在叶子节点中
  • B+叶子节点是顺序排列的,并且相邻节点具有顺序引用的关系

综上所述:

  • B+树是B-树的变种(PLUS版)多路绝对平衡查找树,他拥有B-树的优势
  • B+树扫库、表能力更强
  • B+树的磁盘读写能力更强
  • B+树的排序能力更强
  • B+树的查询效率更加稳定(因为每次查询的时间都差不多)

索引在MySQL中,怎么落地的–引擎

innodb

  • innodb引擎在磁盘中是1个文件。
  • innodb默认为主键id建一个聚集索引。如果表没有设置id,innodb默认创建一个隐藏id。
  • 聚集索引:数据库表行中数据的物理顺序与键值的逻辑(索引) 顺序相同
  • innodb 数据存储在主键索引的叶子节点中。辅助索引叶子节点中,存储的是 主键id。
  • 为什么 辅助索引 叶子节点存储的是 id,而不是像myisam一样的数据地址。因为这样 当数据中其他字段 改变而id不变的时候,只维护 id索引就可以。
  • innodb支持事务

myisam

  • myisam引擎在磁盘中是2个文件。
  • 主键索引和辅助索引的叶子节点 都存储 数据的地址
  • myisam不支持事务

列的离散型

越大离散型越好。离散性越高 选择性就越好。

例如:性别字段。只有男 女两个属性。使用索引 还不如全表搜索。

最左匹配原则

对索引中关键字进行计算(对比),一定是从左往右依次进行, 且不可跳过

联合索引

将多个字段组合在一起,建立成一个索引。

  • 经常用的列优先 【最左匹配原则】
  • 选择性(离散度)高的列优先【离散度高原则】
  • 宽度小的列优先【最少空间原则】

覆盖索引

  • 如果查询列可通过索引节点中的关键字直接返回,则该索引称之为 覆盖索引
  • 覆盖索引可减少数据库IO,将随机IO变为顺序IO,可提高查询性能

总结:

  • 索引列的数据长度能少则少。
  • 索引一定不是越多越好,越全越好,一定是建合适的。
  • 匹配列前缀可用到索引 like 9999%,like %9999%、like %9999用不到索引;
  • Where 条件中 not in 和 <>操作无法使用索引;
  • 匹配范围值,order by 也可用到索引;
  • 多用指定列查询,只返回自己想到的数据列,少用select *;
  • 联合索引中如果不是按照索引最左列开始查找,无法使用索引;
  • 联合索引中精确匹配最左前列并范围匹配另外一列可以用到索引;
  • 联合索引中如果查询中有某个列的范围查询,则其右边的所有列都无法使用索引

你可能感兴趣的