mysql innodb索引结构的理解

从几个“经验”做法问题开始：

1、建索引的时候，建议使用一个id字段作为主键，并递增的插入，这样效率更高。

2、为什么索引查询的时候，会有前缀匹配的约束？

开始的时候抛出这两个问题，我们先了解完innodb的索引的数据结构，再回来看这两个问题是不是更清晰了。

innodb的数据与主键索引是如何组织

B树与B+树的基本原理

mysql的索引通过B+树来进行组织。为什么是B+树？这先简单回顾一下B树和B+树的基本原理和特性。

B树可以参考基本概念B树定义，简单理解为一般化的二叉查找树，B+树的定义如下B+树定义。

B树的结构如下图所示：

B+树结构如下图所示：

B+树结构

B+树相对B树而言，在建索引场景下，有几个好处：

1）数据只存在叶子节点中，对于遍历全部数据或者区间数据而言，可以顺序遍历，效率高。

2）非叶子节点，只存储关键字，不存储数据，使得每个块存储的关键字相对B树而言较多，可以减少查找过程中的IO次数。查找效率相对稳定。

B+树的插入操作

 这里引用《mysql技术内幕》中的一段，说明对于B+树索引的插入操作的算法

我们可以看到，对于正常的插入操作的话，如果leaf page和index page都是空的情况下，最简单，直接插入即可。而对于插入数据在非连续情况下，例如，上图B+树结构中，如果插入的数据一直处于(5,90)这个区间内的情况下，那当leaf page满的时候，需要不停的进行分页操作。而此时，对磁盘IO是随机的。

而如果插入的时候，如果一直是id递增的，则只需要不停的申请新的磁盘块进来，并插入数据，将索引插入，而此时的磁盘IO，是顺序的。

因此，这里可以得到问题1的结论：插入ID主键递增的时候，innodb建索引的时候，可以是顺序磁盘IO，因为效率要比插入ID是非顺序递增要高效。

联合索引情况下，索引如何存储

举个实例：

create table t(a int not null, b int not null, primary key(a), key index_a_b(a,b));

里面的联合索引，本质上也是一颗B+树，如下图所示：

我们看到，里面的数据是已经按(a,b)排好序了。

我们在查找的时候，可以使用select * from where a = xxx and b = xxx; 我们也可以使用select * from where a = xxx;这样，因为对于a来说，在页子中遍历，是有序的。 但是，我们无法使用select * from where b = xx;这样来进行查找，因为对于b数据而言，它在叶子中，是无序的。所以对于联合索引(a, b)，使用where b = xx的时候，是无法使用索引。

因此，这里也解释了问题2，为什么联合索引必须是前缀匹配的原因，因为联系索引在B+树中是按联系索引的定义先后进行排序的。