某高性能のB树索引

近期在做项目的时候遇到了千万级别的表关联查询，发现加索引和没加索引的区别真的很大。于是今天我们就来研究一下数据库的索引吧。

 

众所周知使用索引可以将存储在数据库中的数据以某种数据结构的形式存储，使用索引会增加一定的内存，但对于查询则会有极大的提升，其次正确的使用索引也是对SQL优化起着关键性的作用。

 

数据库的索引有很多种，以MYSQL为例

按照逻辑分类索引有：主键索引、唯一索引、普通索引、全文索引、组合索引

按照物理分类索引有：聚簇索引、非聚簇索引

按照数据结构分类有：B树索引（B树、B+树），Hash索引

 

 

而今天主要想讲的是B树索引，本篇文章将会从下面两点开始讲解

1.索引的使用和优化

2.B+树的数据结构

（阶级m=3的b+树）

 

 

 

 

 

索引的使用和优化

 

存储引擎与B树索引

 

索引是在存储引擎层而不是服务器层实现。所以，不同的存储引擎索引工作方式并不一样。

 

 

连接层：连接与线程处理，这一层并不是MYSQL独有，一般的基于C/S架构的都有类似组件，比如连接处理、授权认证、安全等。

服务层：包括缓存查询、解析器、优化器，这一部分是MYSQL核心功能，包括解析、优化SQL语句，查询缓存目录，内置函数（日期、时间、加密等函数）的实现。

引擎层：负责数据存储，存储引擎的不同，存储方式、数据格式、提取方式等都不相同，这一部分也是很大影响数据存储与提取的性能的；对存储层的抽象。

存储层：存储数据，文件系统。

 

在INNODB中b树索引（B+树）索引根据主键引用被索引的行，而在MYISAM索引通过数据的物理位置引用被索引的行。在MYSQL数据库中，数据库每页只存16kb，用B+树可以存储更多的键值对，使B+树的阶级更高。

 

 

 

 

 

 

 

 

聚簇索引与非聚簇索引

 

聚集索引：当表有聚簇索引时，它的数据实际上存放在索引的叶子页中，页节点不存数据（一般存唯一的主键）。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。

非聚集索引：该索引中索引的逻辑顺序与磁盘上的物理存储顺序不同（非主键的那一列），一个表中可以拥有多个非聚集索引。

 

 

索引适用的场景（多列索引）

 

全值匹配：和索引中所有列进行匹配

最左前缀：只使用索引第一列

范围匹配：range，利用b树叶子节点数据连续的特性使用范围匹配。范围匹配后，后面的索引列会失效

覆盖索引：取查询列和主键（非常的快）

 

索引失效的场景

 

模糊查询：例如‘%J’，在首字符前使用模糊查询

单列索引：使用多个单列索引时，一般只有第一列的索引会被使用到。

复合索引：忽略第一个/中间某个查询列时会失效

函数运算：在索引列上使用函数或运算，或者使用<>, is not null

 

 

 

 

 

 

 

 

 

 

高性能的索引优化

 

正确地使用索引才是实现高性能查询的基础，这里只谈常用的索引优化策略，比较偏门的优化方法如前缀截取索引、前缀压缩索引这种适用场景比较少的索引就不多谈了，有兴趣的朋友可以读一下《高性能MYSQL》。

 

多列索引

 

很多人对索引理解不够，一个常见的错误就是，为每个列创建独立索引，期待着MYSQL会在查询的时候把建立的索引都用上。

正确的做法是，对多查询条件的SQL建立正确顺序的复合索引，避免建立重复索引

 

在建索引中，有一个经验法则是考虑表的【全局基数低】和【选择性高】作为索引优先列。

全局基数：以某列查询为基准，数据量少的列放前面。

选择性：重复数据越少，选择性越大；重复数据越多，选择性越小。

但经验法则绝不是放任四海皆准的法则，只是提供参考

 

 

覆盖索引

 

如果一个索引包含所有需要查询的字段的值，我们称为覆盖索引，能够极大地提供性能，因为查询只需要扫描索引即可，不需要进行数据访问。

 

P.S. 小技巧&小提示

1.在使用索引中范围查询应放在最后。

2.如果想跳过中间某个索引列时，可以采用全等值跳过索引列e.g.比如用in枚举所有选择

3.Explain中的Extra可以看查询的索引情况，Type可以看排序的索引使用情况

4.对于大分页可以用覆盖索引做延迟关联，否则会花费大量时间在数据丢弃中，而用延迟关联则只保存主键，提高查询效率。

 

 

 

 

 

 

 

 

 

 

 

B+树详解

B树定义

B树是一颗多路平衡查找树，当我们描述一颗B树时我们需要给定它一个阶级m，指定它有多少个孩子结点，当m=2时，就是二叉树。

一颗m阶B树

1）每个结点最多有m-1个关键字。

2）根结点最少可以只有1个关键字。

3）非根结点至少有Math.ceil(m/2)-1个关键字。

4）每个结点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它。

5）所有叶子结点都位于同一层，或者说根结点到每个叶子结点的长度都相同。

 

 

（3阶B树）

 

 

B+树定义

1）B+树包含2种类型的节点：内部结点（也称索引结点）和叶子结点。根结点本身即可以是内部结点，也可以是叶子结点。根结点的关键字个数最少可以只有1个。

2）B+树与B树最大的不同是内部结点不保存数据，只用于索引，所有数据（或者说记录）都保存在叶子结点中。

3） m阶B+树表示了内部结点最多有m-1个关键字（或者说内部结点最多有m个子树），阶数m同时限制了叶子结点最多存储m-1个记录。

4）内部结点中的key都按照从小到大的顺序排列，对于内部结点中的一个key，左树中的所有key都小于它，右子树中的key都大于等于它。叶子结点中的记录也按照key的大小排列。

5）每个叶子结点都存有相邻叶子结点的指针，叶子结点本身依关键字的大小自小而大顺序链接。

 

 

 

（3阶B+树）

详细的B树/B+树的插入删除过程可以参考：

https://blog.csdn.net/Fmuma/article/details/80287924

 

 

 

 

 

 

 

 

 

 

 

 

 

参考文献

1.B树和B+树的操作详解

https://blog.csdn.net/Fmuma/article/details/80287924

2.高性能MySQL