Mysql-索引原理

mysql的索引也称为键，是存储引擎用于快速找到数据的数据结构。 良好的索引是大数据量的查询一个关键所在。数据量小且负载小时，不好的索引对性能影响可能看不太出来，但是当大量数据与负载时，性能将会急剧下降。
这里介绍InnoDB的索引原理。
1.为什么不用hash，平衡树等数据结构。
一般avl树的查找平均时间为logN，但是时间复杂度模型是基于每次相同的操作成本来考虑的。但是数据库数据是持久化到硬盘而不是内存，所以不像其他数据结构可以一次性把所有数据都加到内存中。他需要cpu与硬盘相互工作，而cpu与硬盘io速度相差几十万倍。所以希望付出cpu多次计算的代价来减少硬盘io。mysql的硬盘访问单位是页 一般是16kb（数据可调），即一次可以读出多个数据。所以我们可以依赖这个特点来减少硬盘io次数，也就是减少树的高度。
2.B-tree

如图是btree的一个简要结构，他是由叶子节点和非叶子节点构成。每个非叶子节点都包括多个key，指针与data。key就是主键，指针指向其他节点，data则是行数据。每个叶子节点则是包含多个key与data。
由于内存中的key是一个有序表结构，可以利用二分法查找提高效率。而磁盘I/O的次数是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree树提高了节点的子节点数缩减的高度，每次磁盘I/O都能更加精确地定位到具体的磁盘块，从而提高了查询效率。

B+tree
innodb的索引实现是B+tree。b+tree相当于b-tree的一个优化。由于页的大小是固定的，所以在b-tree中每个非叶子节点的data越大则子节点越少，从而会提高树的高度，增加磁盘io次数。而B+tree中非叶子节点不存行数据，所有的行数据都存于叶子节点，进而提高每个非叶子节点的子节点，减少树的高度，减少磁盘io次数。

B+Tree相对于B-Tree有几点不同：
非叶子节点只存储键值信息。
所有叶子节点之间都有一个链指针。
数据记录都存放在叶子节点中。

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为1000）。也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿 条记录。

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

在数据库中，B+Tree的高度一般都在2-4层。mysql的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。