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 *；
• 联合索引中如果不是按照索引最左列开始查找，无法使用索引；
• 联合索引中精确匹配最左前列并范围匹配另外一列可以用到索引；
• 联合索引中如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引