最左前缀道理与相干优化
本文以MySQL数据库为研究对象，评论辩论与数据库索引相干的一些话题。特别须要解释的是，MySQL支撑诸多存储引擎，而各类存储引擎对索引的支撑也各不雷同，是以MySQL数据库支撑多种索引类型，如BTree索引，哈希索引，全文索引等等。为了避免纷乱，本文将只存眷于BTree索引，因为这是平常应用MySQL时重要打交道的索引，至于哈希索引和全文索引本文暂不评论辩论。
文┞仿重要内容分为三个部分。
第一部分重要大年夜数据构造及算法理论层面评论辩论MySQL数据库索引的数理基本。
第二部分结合MySQL数据库中MyISAM和InnoDB数据存储引擎中索引的架构实现评论辩论集合索引、非集合索引及覆盖索引等话题。
第三部分根据膳绫擎的理论基本，评论辩论MySQL中高机能应用索引的策略。
数据构造及算法基本
B-/+Tree索引的机能分析
索引的本质
MySQL官方对索引的定义为：索引(Index)是赞助MySQL高效获取数据的数据构造。提取句子骨干，就可以获得索引的本质：索引是数据构造。
我们知道，数据库萌芽是数据库的最重要功能之一。我们都欲望萌芽数据的速度能尽可能的快，是以数据库体系的设计者会大年夜萌芽算法的角度进行优化。最根本的萌芽算法当然是次序查找(linear search)，这种复杂度为O(n)的算法在数据量很大年夜时显然是糟糕的，好在计算机科学的成长供给了很多更优良的查找算法，例如二分查找(binary search)、二叉树查找(binary tree search)等。如不雅稍微分析一下会发明，每种查找算法都只能应用于特定的数据构造之上，例如二分查找请求被检索数据有序，而二叉树查找只能应用于二叉查找树上，然则数据本身的组织构造弗成能完全知足各类数据构造(例如，理论上弗成能同时将两列都按次序进行组织)，所以，在数据之外，数据库体系还保护着知足特定查找算法的数据构造，这些数据构造以某种方法引用(指向)数据，如许就可以在这些数据构造上实现高等查找算法。这种数据构造，就是索引。
看一个例子：
图1
图1展示了一种可能的索引方法。左边是数据表，一共有两列七笔记录，最左边的是数据记录的物理地址(留意逻辑上相邻的记录在磁盘上也并不是必定物理相邻的)。为了加快Col2的查找，可以保护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，如许就可以应用二叉查找在O(log2n)的复杂度内获取到响应数据。
固然这是一个货真价实的索引，然则实际的数据库体系几乎没有应用二叉查找树或其进化品种红黑树(red-black tree)实现的，原因会鄙人文介绍。
B-Tree和B+Tree
今朝大年夜部分数据库体系及文件体系都采取B-Tree或其变种B+Tree作为索引构造，在本文的下一节会结合存储器道理及计算机存取道理评论辩论为什么B-Tree和B+Tree在被如斯广泛用于索引，这一节先纯真大年夜数据构造角度描述它们。
B-Tree
为了描述B-Tree，起首定义一条数据记录为一个二元组[key, data]，key为记录的键值，对于不合数据记录，key是互不雷同的;data为数据记录除key外的数据。那么B-Tree是知足下列前提的数据构造：
1. d为大年夜于1的一个正整数，称为B-Tree的度。
2. h为一个正整数，称为B-Tree的高度。
3. 每个非叶子节点由n-1个key和n个指针构成，个中d<=n<=2d。
4. 每个叶子节点起码包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为null 。
5. 所有叶节点具有雷同的深度，等于树高h。
6. key和指针互相距离，节点两端是指针。
8. 所有节点构成树构造。
9. 每个指针要么为null，要么指向别的一个节点。
10. 如不雅某个指针在节点node最左边且不为null，则其指向节点的所有key小于v(key1)，个中v(key1)为node的第一个key的值。
11. 如不雅某个指针在节点node最右边且不为null，则其指向节点的所有key大年夜于v(keym)，个中v(keym)为node的最后一个key的值。
12. 如不雅某个指针在节点node的阁下相邻key分别是keyi和keyi+1且不为null，则其指向节点的所有key小于v(keyi+1)且大年夜于v(keyi)。
图2是一个d=2的B-Tree示意图。
图2
因为B-Tree的特点，在B-Tree中按key检索数据的算法异常直不雅：起首大年夜根节点进行二分查找，如不雅找到则返回对应节点的data，不然对响应区间的指针指向的节点递归进行查找，直到找到节点或找到null指针，前者查找成功，后者查找掉败。B-Tree上查找算法的伪代码如下：
BTree_Search(node, key)
{
if(node == null) return null;
