mysql新增BTREE索引_「Mysql索引原理（二）」Mysql高性能索引实践，索引概念、BTree索引、B+Tree索引...

1. 索引是什么

2. 索引的类型

3. BTree索引

概念

举例：以5阶数为列

4. B+Tree索引

概念

5阶B+Tree插入举例

B+树的优点

可以使用B+树索引的查询类型

B+Tree索引的限制

索引是什么

索引是存储引擎用于快速找到记录的一种数据结构。存储引擎首先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。比如，

select first_name from actor where actor.id=5;

mysql先在索引上按值进行查找，然后返回所有包含该值的数据行。

索引的类型

并没有统一的索引标准，不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现也不一样。

mysql中常用的索引类型包括BTree索引、B+Tree索引、哈希索引。在介绍索引的使用和索引的优点之前，需要先弄清楚索引抱哈的。

BTree索引

概念

B树是一种自平衡树数据结构，它维护有序数据并允许以对数时间进行搜索，顺序访问，插入和删除。B树是二叉搜索树的一般化，因为节点可以有两个以上的子节点。与其他自平衡二进制搜索树不同，B树非常适合读取和写入相对较大的数据块(如光盘)的存储系统。它通常用于数据库和文件系统。

定义：

B树是一种平衡的多分树，通常我们说m阶的B树，它必须满足如下条件：

1. 每个节点最多只有m个子节点。

2. 每个非叶子节点(除了根)具有至少⌈ m/2⌉子节点。

3. 如果根不是叶节点，则根至少有两个子节点。

4. 具有k个子节点的非叶节点包含k -1个键。

所有叶子都出现在同一水平，没有任何信息(高度一致)。

什么是阶？

image.png

节点【13,16,19】拥有的子节点数目最多，有四个子节点(粉色节点)，所以可以定义上面的图片为4阶B树。

什么是根节点？

节点【10】即为根节点。包含子节点数关系式2<= M <=m，M为子节点数量；包含的元素数量 1<= K <=m-1,K为元素数量。

什么是内部节点?

节点【13,16,19】、节点【3,6】都为内部节点，特征：内部节点是除叶子节点和根节点之外的所有节点，拥有父节点和子节点。包含子节点数关系式符合(m/2)<= M <=m关系式，包含元素数量M-1；包含的元素数量 (m/2)-1<= K <=m-1,K为元素数量。m/2向上取整。

什么是叶子节点？

节点【1,2】、节点【11,12】等最后一层都为叶子节点，叶子节点对元素的数量有相同的限制，但是没有

子节点，也没有指向子节点的指针。叶子节点的元素符合(m/2)-1<= K <=m-1。

举例：以5阶数为列

插入操作

规则：

若该节点元素个数小于m-1，直接插入；

若该节点元素个数等于m-1，引起节点分裂；以该节点中间元素为分界，取中间元素(偶数个数，中间两个随机选取)插入到父节点中；

重复上面动作，直到所有节点符合B树的规则；最坏的情况一直分裂到根节点，生成新的根节点，高度增加1；

关键点：

2<=根节点子节点个数<=5

3<=内节点子节点个数<=5

1<=根节点元素个数<=4

2<=非根节点元素个数<=4

插入8

此时根节点元素个数为5，不符合 1<=根节点元素个数<=4，进行分裂

接着插入元素【5】，【11】，【17】时，不需要任何分裂操作

插入元素【13】

节点元素超出最大数量，进行分裂，提取中间元素【13】，插入到父节点当中

接着插入元素【6】，【12】，【20】，【23】时，不需要任何分裂操作

插入【26】时

最右的叶子结点空间满了，需要进行分裂操作，中间元素【20】上移到父节点中

插入【4】时

导致最左边的叶子结点被分裂，【4】恰好也是中间元素，上移到父节点中

【16】,【18】,【24】,【25】陆续插入不需要任何分裂操作

关键，插入【19】时

分裂

继续分裂

此时树的高度增加1。

删除操作

首先查找B树中需删除的元素,如果该元素在B树中存在，则将该元素在其结点中进行删除；删除该元素后，首先判断该元素是否有左右孩子结点，如果有，则上移孩子结点中的某相近元素(“左孩子最右边的节点”或“右孩子最左边的节点”)到父节点中，然后是移动之后的情况；如果没有，直接删除。

某结点中元素数目小于(m/2)-1,(m/2)向上取整，则需要看其某相邻兄弟结点是否丰满；

如果丰满(结点中元素个数大于(m/2)-1)，则向父节点借一个元素来满足条件；

如果其相邻兄弟都不丰满，即其结点数目等于(m/2)-1，则该结点与其相邻的某一兄弟结点进行“合并”成一个结点；

删除【8】

删除【20】

因为非根节点元素个数>=2，只有一个元素【17】的节点不符合规定，因此需要将元素【23】上移动。

删除【18】

向父结点借一个元素，然后将最丰满的相邻兄弟结点中上移最后或最前一个元素到父节点中

最后一步删除【5】

合并后

image.png

再次合并

image.png

总结，增加导致分裂，删除导致合并。

注意，BTree索引每个节点不但保存索引信息，还保存了对应的数据行信息，找到一个节点相当于找到了数据表中的一行。

B+Tree索引

概念

B+Tree是BTree的一个变种，最大的区别是B+Tree内部节点不保存数据，只保存索引信息，所有数据都保存在叶子节点，具有如下特征：

每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。

所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

所有的中间节点元素都同时存在于子节点，在子节点元素中是最大(或最小)元素

不但节点之间含有重复元素，而且叶子结点还用指针连接在一起。

在上面这课树中，根节点元素8是子节点2，5，8的最大元素，也是叶子节点6，8的最大元素。需要注意的是根节点的最大元素(这里是15)，也就等同于整个B+树的最大元素。以后无论插入删除多少元素，始终保持最大元素在根节点当中。

至于叶子节点，由于父节点的元素都出现在子节点，因此叶子结点包含了全部元素的信息。并且每个叶子节点都带有指向下一个节点的指针，形成了一个有序链表。

对于B+树，只需记住叶子节点是个有序列表且包含全部元素数据信息即可，影响到后续索引的使用。

5阶B+Tree插入举例

空树插入【5】

一次插入【8】、【10】、【15】

插入【16】

元素个数超过限制，进行分裂，分裂规则同BTree，但是注意，分裂的元素保留在原节点中，同时叶子节点通过指针连接。

插入【17】、【18】

元素个数超过限制，进行分裂，分裂的元素保留在原节点中。

image.png

B+树的优点

单元素查询

在单元素查询的时候，B+树会自顶向下逐层查找节点，最终找到匹配的叶子节点。

比如我们查找元素4

第一次磁盘IO

第二次磁盘IO

第三次磁盘IO

B+树的中间节点只保存索引信息，不保存元素其他相关信息，所以同样大小的磁盘页可以容纳更多的节点元素，这就意味着在数据量相同的情况下，B+树更加的矮胖，因此IO的次数也就较少。B+树查询必须查找到叶子节点，每一次查找都是稳定的；

B树的范围查找及过程与B+树对比

比如，查找范围3~11

B树，首先自顶向下,查找到范围的下限(3)

中序遍历到元素6

中序遍历到元素8

中序遍历到元素9

中序遍历到元素11

B+树,自顶向下,查找到范围的下限(3)

通过链表指针遍历

总结，B+树比B树更适合做数据库索引：

1)B+树的磁盘读写代价更低

B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了；

2)B+树查询效率更加稳定

由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当；

3)B+树便于范围查询(最重要的原因，范围查找是数据库的常态)

B树在提高了IO性能的同时并没有解决元素遍历的我效率低下的问题，正是为了解决这个问题，B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低；

Mysql数据库中，大多数存储引擎都使用这种索引，存储引擎以不同的方式使用B+Tree索引，性能也各不相同，各有优劣，如，MyISAM使用前缀压缩技术使得索引更小，但InnoDB按照原数据格式进行存储。再如MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB通过主键引用被索引的行。

后面这句话什么意思呢？MyISAM索引文件和数据文件是分离的，索引文件仅保存记录所在页的指针，通过这些指针指向的物理地址来读取页，进而读取索引的行。

InnoDB存储引擎采用“聚集“索引的数据存储方式实现，所谓聚集，就是指数据行和相邻的键值紧凑的存储在一起。在InnoDB中，表数据本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域完整的保存了数据记录。

可以使用B+树索引的查询类型

B+树索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。B+树对索引列hi顺序组织数据的，所以很适合查找范围数据，其实工作中大部分查询语句都是范围查找。

例如，创建表:

CREATE TABLE `people` (

`last_name` varchar(50) CHARACTER SET utf8 NOT NULL,

`first_name` varchar(50) CHARACTER SET utf8 NOT NULL,

`dob` date NOT NULL,

`gender` enum('m','f') CHARACTER SET utf8 NOT NULL,

KEY `myindex` (`last_name`,`first_name`,`dob`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;

数据的B+Tree排列方式：

索引排列顺序依据索引创建时列的顺序。

并不是所有的查询都能使用到B+树索引，B+树索引适用于全键值、键值范围或键前缀查找等，其中键前缀查找只适合用于根据最左前缀的查找。

全值匹配

和索引中所有的列进行匹配，例如查询姓名是Cuba Allen、出生于1960-01-01的人。

SELECT * FROM `people` where last_name='Allen' and first_name="Cuba" and dob="1960-01-01"

2.匹配最左前缀

查找所有姓为Allen的人，即只使用索引的第一列。

SELECT * FROM `people` where last_name='Allen'

3.匹配列前缀

查找所有姓以A开头的人。即只使用索引的第一列

SELECT * FROM `people` where last_name like 'B%'

4.匹配范围值

查找姓在Allen和Barrymore之间的人。

SELECT * FROM `people` where last_name >= 'Allen' and last_name <='Barrymore'

5.精确匹配某一列并范围匹配另一列

查找姓为Allen，并且名字是字母K开头的人。即第一列last_name全匹配，第二列first_name范围匹配

select * from people where last_name='Allen' and first_name like 'K%'

6.只访问索引的查询

查询只需要访问索引，而无需访问数据行，后面会单独讨论这种覆盖索引的优化。

另，索引节点是有序链表，索引除了按值查找外，还可以用于查询中的order by 操作，即按顺序查找，前提是Order by 满足上述几种查询类型。

如:

select * from people where last_name='Allen' order by last_name

B+Tree索引的限制

如果不是按照索引的最左列开始查找，则无法使用索引。

例如上述例子，索引无法用于查找名字为Bill的人，也无法用于查找某个特定生日的人。

如果查询中有某个列的范围查询，则右边所有列都无法使用索引优化查询。

select * from people where last_name='Allen' and first_name like 'J%' and dob='1976-12-23'

这个查询只能使用索引的前两列，原因是后面列的排序是建立在前列完全一致的基础上的。

不能跳过索引中的列

如，上述索引无法用于查找姓为Allen且出生日期是1960-01-01的人。如果不指出第二列first_name，那么mysql只能会用索引的第一列。

select * from people where last_name = 'Allen' and dob='1960-01-01'

总结：索引列的顺序太重要了，牢记B+树的核心：“有序链表且按列顺序排列”。