MySQL-索引数据结构B+Tree

首先我们先来看一下Mysql的体系结构

Mysql为什么要用B+Tree数据结构的索引

索引是什么？

索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构。索引是将数据与磁盘位置建立关联关系

为什么要用索引？

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

为什么选择B+Tree

首先来了解下几个常见数据结构

二叉查找树

平衡二叉查找树

为什么不选择二叉树呢？

• 太深了：数据处的深度决定着他的IO操作次数，IO操作耗时大
• 太小了：每一个磁盘块（节点/页）保存的数据量太小了，没有很好的利用操作磁盘IO的数据交换性，也没有利用好磁盘IO的预读能力（空间局部性原理），从而带来频繁的IO操作

多路平衡查找树 BTree

加强版多路平衡查找树 B+Tree

B+Tree与BTree的区别

• B+节点关键字搜索采用闭合区间
• B+非叶节点不保存数据相关信息，只保存关键字和子节点的引用
• B+关键字对应的数据保存再叶子节点中
• B+叶子节点是顺序排列的，并且相邻节点具有顺序引用的关系

选择B+Tree的理由

• B+Tree是BTree的变种多路绝对平衡查找树，拥有BTree的优势
• B+Tree扫库、表能力更强
• B+Tree的磁盘读写能力更强
• B+Tree的排序能力更强
• B+Tree的查询效率更加稳定

Mysql中B+Tree索引体现形式

Myisam引擎

数据和索引分别存储

Innodb引擎

没有主键会创建一个隐式主键，大小6byte

索引知识补充

列的离散型

count(distinct col) : count(col)比例越大，离散性越好，离散型越好，选择性就越好

最左匹配原则

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

联合索引

• 单列索引
  • 节点中关键字为一个
• 联合索引
  • 节点中关键字为多个
• 单列索引选择原则
  • 经常用的列优先：最左匹配原则，且不能跳过
  • 选择性（离散度）高的列优先：离散度高原则
  • 宽度小的列优先：最少空间原则

覆盖索引

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

相关优化方案：

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