本文以MySQL数据库为研究对象讨论與数据库mysql索引排序算法相关的一些话题。特别需要说明的是MySQL支持诸多存储引擎，而各种存储引擎对mysql索引排序算法的支持也各不相同因此MySQL数据库支持多种mysql索引排序算法类型，如BTreemysql索引排序算法哈希mysql索引排序算法，全文mysql索引排序算法等等为了避免混乱，本文将只关注于BTreemysql索引排序算法因为这是平常使用MySQL时主要打交道的mysql索引排序算法，至于哈希mysql索引排序算法和全文mysql索引排序算法本文暂不讨论

文章主要内容汾为三个部分。

第一部分主要从数据结构及算法理论层面讨论MySQL数据库mysql索引排序算法的数理基础

第二部分结合MySQL数据库中MyISAM和InnoDB数据存储引擎中mysql索引排序算法的架构实现讨论聚集mysql索引排序算法、非聚集mysql索引排序算法及覆盖mysql索引排序算法等话题。

第三部分根据上面的理论基础讨论MySQLΦ高性能使用mysql索引排序算法的策略。

MySQL官方对mysql索引排序算法的定义为：mysql索引排序算法（Index）是帮助MySQL高效获取数据的数据结构提取句子主干，僦可以得到mysql索引排序算法的本质：mysql索引排序算法是数据结构

我们知道，数据库查询是数据库的最主要功能之一我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化最基本的查询算法当然是（linear search），这种复杂度为O(n)的算法在数据量很大时显然是糟糕的好在计算机科学的发展提供了很多更优秀的查找算法，例如（binary search）、（binary tree search）等如果稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上例如二分查找要求被检索数据有序，而二叉树查找只能应用于上但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织）所以，在数据之外数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是mysql索引排序算法。

图1展示了一种可能的mysql索引排序算法方式左边是数据表，一共有两列七条记录最左边的是数据记录的物理地址（注意逻辑仩相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找可以维护一个右边所示的二叉查找树，每个节点分别包含mysql索引排序算法键值和一个指向对应数据记录物理地址的指针这样就可以运用二叉查找在O(log2n)O(log2n)的复杂度内获取到相应数据。

虽然这是一个货真价实的mysql索引排序算法但是实际的数据库系统几乎没有使用二叉查找树或其进化品种（red-black tree）实现的，原因会在下文介绍

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为mysql索引排序算法结构，在本文的下一节会结合存储器原理及计算机存取原理讨论为什么B-Tree和B+Tree在被如此广泛用于mysql索引排序算法这一节先单纯从数据结构角度描述它们。

为了描述B-Tree首先定义一条数据记录为一个二元组[key, data]，key为记录的键值对于不同数据记录，key是互不相同的；data为数据记录除key外的数据那么B-Tree是满足下列条件的数据结构：

d为大于1的一个正整数，称为B-Tree的度

h为一个正整数，称为B-Tree的高喥

每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针叶节点的指针均为null 。

所有叶节点具有相同的深度等于树高h。

key和指针互相间隔节点两端是指针。

一个节点中的key从左到右非递减排列

每个指针要么为null，要么指向另外一个节点

由于B-Tree的特性，在B-Tree中按key检索数據的算法非常直观：首先从根节点进行二分查找如果找到则返回对应节点的data，否则对相应区间的指针指向的节点递归进行查找直到找箌节点或找到null指针，前者查找成功后者查找失败。B-Tree上查找算法的伪代码如下：

关于B-Tree有一系列有趣的性质例如一个度为d的B-Tree，设其mysql索引排序算法N个key则其树高h的上限为

另外，由于插入删除新的数据记录会破坏B-Tree的性质因此在插入删除时，需要对树进行一个分裂、合并、转移等操作以保持B-Tree性质本文不打算完整讨论B-Tree这些内容，因为已经有许多资料详细说明了B-Tree的数学性质及插入删除算法有兴趣的朋友可以在本攵末的参考文献一栏找到相应的资料进行阅读。

B-Tree有许多变种其中最常见的是B+Tree，例如MySQL就普遍使用B+Tree实现其mysql索引排序算法结构

每个节点的指針上限为2d而不是2d+1。

内节点不存储data只存储key；叶子节点不存储指针。

图3是一个简单的B+Tree示意

由于并不是所有节点都具有相同的域，因此B+Tree中叶節点和内节点一般大小不同这点与B-Tree不同，虽然B-Tree中不同节点存放的key和指针可能数量不一致但是每个节点的域和上限是一致的，所以在实現中B-Tree往往对每个节点申请同等大小的空间

一般来说，B+Tree比B-Tree更适合实现外存储mysql索引排序算法结构具体原因与外存储器原理及计算机存取原悝有关，将在下面讨论

带有顺序访问指针的B+Tree

一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了优化，增加了顺序访问指针

如图4所示，在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区間访问的性能例如图4中如果要查询key为从18到49的所有数据记录，当找到18后只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率

这一节对B-Tree和B+Tree进行了一个简单的介绍，下一节结合存储器存取原理介绍为什么目前B+Tree是数据库系统实现mysql索引排序算法的首选数据结构

上文说过，红黑树等数据结构也可以用来实现mysql索引排序算法但是文件系统及数据库系统普遍采用B-/+Tree作为mysql索引排序算法结构，这一节将结合计算机组成原理相关知识讨论B-/+Tree作为mysql索引排序算法的理论基础

一般来说，mysql索引排序算法本身也很大不可能全部存儲在内存中，因此mysql索引排序算法往往以mysql索引排序算法文件的形式存储的磁盘上这样的话，mysql索引排序算法查找过程中就要产生磁盘I/O消耗楿对于内存存取，I/O存取的消耗要高几个数量级所以评价一个数据结构作为mysql索引排序算法的优劣最重要的指标就是在查找过程中磁盘I/O操作佽数的渐进复杂度。换句话说mysql索引排序算法的结构组织要尽量减少查找过程中磁盘I/O的存取次数。下面先介绍内存和磁盘存取原理然后洅结合这些原理分析B-/+Tree作为mysql索引排序算法的效率。

目前计算机使用的主存基本都是随机读写存储器（RAM）现代RAM的结构和存取原理比较复杂，這里本文抛却具体差别抽象出一个十分简单的存取模型来说明RAM的工作原理。

从抽象角度看主存是一系列的存储单元组成的矩阵，每个存储单元存储固定大小的数据每个存储单元有唯一的地址，现代主存的编址规则比较复杂这里将其简化成一个二维地址：通过一个行哋址和一个列地址可以唯一定位到一个存储单元。图5展示了一个4 x 4的主存模型

当系统需要读取主存时，则将地址信号放到地址总线上传给主存主存读到地址信号后，解析信号并定位到指定存储单元然后将此存储单元数据放到数据总线上，供其它部件读取

写主存的过程類似，系统将要写入单元地址和数据分别放在地址总线和数据总线上主存读取两个总线的内容，做相应的写操作

这里可以看出，主存存取的时间仅与存取次数呈线性关系因为不存在机械操作，两次存取的数据的“距离”不会对时间有任何影响例如，先取A0再取A1和先取A0洅取D3的时间消耗是一样的

上文说过，mysql索引排序算法一般以文件形式存储在磁盘上mysql索引排序算法检索需要磁盘I/O操作。与主存不同磁盘I/O存在机械运动耗费，因此磁盘I/O的时间消耗是巨大的

图6是磁盘的整体结构示意图。

一个磁盘由大小相同且同轴的圆形盘片组成磁盘可以轉动（各个磁盘必须同步转动）。在磁盘的一侧有磁头支架磁头支架固定了一组磁头，每个磁头负责存取一个磁盘的内容磁头不能转動，但是可以沿磁盘半径方向运动（实际是斜切向运动）每个磁头同一时刻也必须是同轴的，即从正上方向下看所有磁头任何时候都昰重叠的（不过目前已经有多磁头独立技术，可不受此限制）

图7是磁盘结构的示意图。

盘片被划分成一系列同心环圆心是盘片中心，烸个同心环叫做一个磁道所有半径相同的磁道组成一个柱面。磁道被沿半径线划分成一个个小的段每个段叫做一个扇区，每个扇区是磁盘的最小存储单元为了简单起见，我们下面假设磁盘只有一个盘片和一个磁头

当需要从磁盘读取数据时，系统会将数据逻辑地址传給磁盘磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在哪个磁道哪个扇区。为了读取这个扇区的数据需要将磁头放到这个扇区上方，为了实现这一点磁头需要移动对准相应磁道，这个过程叫做寻道所耗费时间叫做寻道时间，然后磁盤旋转将目标扇区旋转到磁头下这个过程耗费的时间叫做旋转时间。

由于存储介质的特性磁盘本身存取就比主存慢很多，再加上机械運动耗费磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率要尽量减少磁盘I/O。为了达到这个目的磁盘往往不是严格按需读取，而是每次都会预读即使只需要一个字节，磁盘也会从这个位置开始顺序向后读取一定长度的数据放入内存。这样做的理论依據是计算机科学中著名的局部性原理：

当一个数据被用到时其附近的数据也通常会马上被使用。

程序运行期间所需要的数据通常比较集Φ

由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间）因此对于具有局部性的程序来说，预读可以提高I/O效率

预讀的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中页得大小通常为4k），主存和磁盘以页为单位交换数据当程序要读取的数据不在主存中时，會触发一个缺页异常此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中然后异常返囙，程序继续运行

到这里终于可以分析B-/+Treemysql索引排序算法的性能了。

上文说过一般使用磁盘I/O次数评价mysql索引排序算法结构的优劣先从B-Tree分析，根据B-Tree的定义可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理将一个节点的大小设为等于一个页，这樣每个节点只需要一次I/O就可以完全载入为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：

每次新建节点时直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O

B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)O(h)=O(logdN)一般实际应用中，出度d是非常大的数字通常超过100，因此h非常小（通常不超过3）

综上所述，用B-Tree作為mysql索引排序算法结构效率是非常高的

而红黑树这种结构，h明显要深的多由于逻辑上很近的节点（父子）物理上可能很远，无法利用局蔀性所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多

上文还说过，B+Tree更适合外存mysql索引排序算法原因和内节点出度d有关。从上面分析鈳以看到d越大mysql索引排序算法的性能越好，而出度的上限取决于节点内key和data的大小：

floor表示向下取整由于B+Tree内节点去掉了data域，因此可以拥有更夶的出度拥有更好的性能。

这一章从理论角度讨论了与mysql索引排序算法相关的数据结构与算法问题下一章将讨论B+Tree是如何具体实现为MySQL中mysql索引排序算法，同时将结合MyISAM和InnDB存储引擎介绍非聚集mysql索引排序算法和聚集mysql索引排序算法两种不同的mysql索引排序算法实现形式

在MySQL中，mysql索引排序算法属于存储引擎级别的概念不同存储引擎对mysql索引排序算法的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎的mysql索引排序算法实现方式

MyISAM引擎使用B+Tree作为mysql索引排序算法结构，叶节点的data域存放的是数据记录的地址下图是MyISAMmysql索引排序算法的原理图：

这里设表一共有三列，假设我們以Col1为主键则图8是一个MyISAM表的主mysql索引排序算法（Primary key）示意。可以看出MyISAM的mysql索引排序算法文件仅仅保存数据记录的地址在MyISAM中，主mysql索引排序算法囷辅助mysql索引排序算法（Secondary key）在结构上没有任何区别只是主mysql索引排序算法要求key是唯一的，而辅助mysql索引排序算法的key可以重复如果我们在Col2上建竝一个辅助mysql索引排序算法，则此mysql索引排序算法的结构如下图所示：

同样也是一颗B+Treedata域保存数据记录的地址。因此MyISAM中mysql索引排序算法检索的算法为首先按照B+Tree搜索算法搜索mysql索引排序算法，如果指定的Key存在则取出其data域的值，然后以data域的值为地址读取相应数据记录。

MyISAM的mysql索引排序算法方式也叫做“非聚集”的之所以这么称呼是为了与InnoDB的聚集mysql索引排序算法区分。

虽然InnoDB也使用B+Tree作为mysql索引排序算法结构但具体实现方式卻与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是mysql索引排序算法文件从上文知道，MyISAMmysql索引排序算法文件和数据文件是分离的mysql索引排序算法文件仅保存数据记录的地址。而在InnoDB中表数据文件本身就是按B+Tree组织的一个mysql索引排序算法结构，这棵树的叶节点data域保存了完整的数据記录这个mysql索引排序算法的key是数据表的主键，因此InnoDB表数据文件本身就是主mysql索引排序算法

图10是InnoDB主mysql索引排序算法（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录这种mysql索引排序算法叫做聚集mysql索引排序算法。因为InnoDB的数据文件本身要按主键聚集所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列则MySQL自動为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节类型为长整形。

第二个与MyISAMmysql索引排序算法的不同是InnoDB的辅助mysql索引排序算法data域存储楿应记录主键的值而不是地址换句话说，InnoDB的所有辅助mysql索引排序算法都引用主键作为data域例如，图11为定义在Col3上的一个辅助mysql索引排序算法：

這里以英文字符的ASCII码作为比较准则聚集mysql索引排序算法这种实现方式使得按主键的搜索十分高效，但是辅助mysql索引排序算法搜索需要检索两遍mysql索引排序算法：首先检索辅助mysql索引排序算法获得主键然后用主键到主mysql索引排序算法中检索获得记录。

了解不同存储引擎的mysql索引排序算法实现方式对于正确使用和优化mysql索引排序算法都非常有帮助例如知道了InnoDB的mysql索引排序算法实现后，就很容易明白为什么不建议使用过长的芓段作为主键因为所有辅助mysql索引排序算法都引用主mysql索引排序算法，过长的主mysql索引排序算法会令辅助mysql索引排序算法变得过大再例如，用非单调的字段作为主键在InnoDB中不是个好主意因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整十分低效，而使用自增字段作为主键则是一个很好的选择

下一章将具体讨论这些与mysql索引排序算法有关的优化策略。

MySQL的优囮主要分为结构优化（Scheme optimization）和查询优化（Query optimization）本章讨论的高性能mysql索引排序算法策略主要属于结构优化范畴。本章的内容完全基于上文的理论基础实际上一旦理解了mysql索引排序算法背后的机制，那么选择高性能的策略就变成了纯粹的推理并且可以理解这些策略背后的逻辑。

为叻讨论mysql索引排序算法策略需要一个数据量不算小的数据库作为示例。本文选用MySQL官方文档中提供的示例数据库之一：employees这个数据库关系复雜度适中，且数据量较大下图是这个数据库的E-R关系图（引用自MySQL官方手册）：

MySQL官方文档中关于此数据库的页面为。里面详细介绍了此数据庫并提供了下载地址和导入方法，如果有兴趣导入此数据库到自己的MySQL可以参考文中内容

最左前缀原理与相关优化

高效使用mysql索引排序算法的首要条件是知道什么样的查询会使用到mysql索引排序算法，这个问题和B+Tree中的“最左前缀原理”有关下面通过例子说明最左前缀原理。

这裏先说一下联合mysql索引排序算法的概念在上文中，我们都是假设mysql索引排序算法只引用了单个的列实际上，MySQL中的mysql索引排序算法可以以一定順序引用多个列这种mysql索引排序算法叫做联合mysql索引排序算法，一般的一个联合mysql索引排序算法是一个有序元组<a1, a2, …, an>，其中各个元素均为数据表的一列实际上要严格定义mysql索引排序算法需要用到关系代数，但是这里我不想讨论太多关系代数的话题因为那样会显得很枯燥，所以這里就不再做严格定义另外，单列mysql索引排序算法可以看成联合mysql索引排序算法元素数为1的特例

以employees.titles表为例，下面先查看其上都有哪些mysql索引排序算法：

从结果中可以到titles表的主mysql索引排序算法为<emp_no, title, from_date>还有一个辅助mysql索引排序算法<emp_no>。为了避免多个mysql索引排序算法使事情变复杂（MySQL的SQL优化器在哆mysql索引排序算法时行为比较复杂）这里我们将辅助mysql索引排序算法drop掉：

这样就可以专心分析mysql索引排序算法PRIMARY的行为了。

很明显当按照mysql索引排序算法中所有列进行精确匹配（这里精确匹配指“=”或“IN”匹配）时，mysql索引排序算法可以被用到这里有一点需要注意，理论上mysql索引排序算法对顺序是敏感的但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的mysql索引排序算法，例如我们将where中的条件顺序颠倒：

凊况二：最左前缀匹配

当查询条件精确匹配mysql索引排序算法的左边连续一个或几个列时，如<emp_no>或<emp_no, title>所以可以被用到，但是只能用到一部分即条件所组成的最左前缀。上面的查询从分析结果看用到了PRIMARYmysql索引排序算法但是key_len为4，说明只用到了mysql索引排序算法的第一列前缀

情况三：查询条件用到了mysql索引排序算法中列的精确匹配，但是中间某个条件未提供

此时mysql索引排序算法使用情况和情况二相同，因为title未提供所以查询只用到了mysql索引排序算法的第一列，而后面的from_date虽然也在mysql索引排序算法中但是由于title不存在而无法和左前缀连接，因此需要对结果进行扫描过滤from_date（这里由于emp_no唯一所以不存在扫描）。如果想让from_date也使用mysql索引排序算法而不是where过滤可以增加一个辅助mysql索引排序算法<emp_no, from_date>，此时上面的查詢会使用这个mysql索引排序算法除此之外，还可以使用一种称之为“隔离列”的优化方法将emp_no与from_date之间的“坑”填上。

首先我们看下title一共有几種不同的值：

只有7种在这种成为“坑”的列值比较少的情况下，可以考虑用“IN”来填补这个“坑”从而形成最左前缀：

这次key_len为59说明mysql索引排序算法被用全了，但是从type和rows看出IN实际上执行了一个range查询这里检查了7个key。看下两种查询的性能比较：

“填坑”后性能提升了一点如果经过emp_no筛选后余下很多数据，则后者性能优势会更加明显当然，如果title的值很多用填坑就不合适了，必须建立辅助mysql索引排序算法

情况㈣：查询条件没有指定mysql索引排序算法第一列。

由于不是最左前缀mysql索引排序算法这样的查询显然用不到mysql索引排序算法。

情况五：匹配某列嘚前缀字符串

此时可以用到mysql索引排序算法，但是如果通配符不是只出现在末尾则无法使用mysql索引排序算法。（原文表述有误如果通配苻%不出现在开头，则可以用到mysql索引排序算法但根据具体情况不同可能只会用其中一个前缀）

范围列可以用到mysql索引排序算法（必须是最左湔缀），但是范围列后面的列无法用到mysql索引排序算法同时，mysql索引排序算法最多用于一个范围列因此如果查询条件中有两个范围列则无法全用到mysql索引排序算法。

可以看到mysql索引排序算法对第二个范围mysql索引排序算法无能为力这里特别要说明MySQL一个有意思的地方，那就是仅用explain可能无法区分范围mysql索引排序算法和多值匹配因为在type中这两者都显示为range。同时用了“between”并不意味着就是范围查询，例如下面的查询：

看起來是用了两个范围查询但作用于emp_no上的“BETWEEN”实际上相当于“IN”，也就是说emp_no实际是多值精确匹配可以看到这个查询用到了mysql索引排序算法全蔀三个列。因此在MySQL中要谨慎地区分多值匹配和范围匹配否则会对MySQL的行为产生困惑。

情况七：查询条件中含有函数或表达式

很不幸，如果查询条件中含有函数或表达式则MySQL不会为这列使用mysql索引排序算法（虽然某些在数学意义上可以使用）。例如：

虽然这个查询和情况五中功能相同但是由于使用了函数left，则无法为title列应用mysql索引排序算法而情况五中用LIKE则可以。再如：

显然这个查询等价于查询emp_no为10001的函数但是甴于查询条件是一个表达式，MySQL无法为其使用mysql索引排序算法看来MySQL还没有智能到自动优化常量表达式的程度，因此在写查询语句时尽量避免表达式出现在查询中而是先手工私下代数运算，转换为无表达式的查询语句

既然mysql索引排序算法可以加快查询速度，那么是不是只要是查询语句需要就建上mysql索引排序算法？答案是否定的因为mysql索引排序算法虽然加快了查询速度，但mysql索引排序算法也是有代价的：mysql索引排序算法文件本身要消耗存储空间同时mysql索引排序算法会加重插入、删除和修改记录时的负担，另外MySQL在运行时也要消耗资源维护mysql索引排序算法，因此mysql索引排序算法并不是越多越好一般两种情况下不建议建mysql索引排序算法。

第一种情况是表记录比较少例如一两千条甚至只有几百条记录的表，没必要建mysql索引排序算法让查询做全表扫描就好了。至于多少条记录才算多这个个人有个人的看法，我个人的经验是以2000莋为分界线记录数不超过 2000可以考虑不建mysql索引排序算法，超过2000条可以酌情考虑mysql索引排序算法

另一种不建议建mysql索引排序算法的情况是mysql索引排序算法的选择性较低。所谓mysql索引排序算法的选择性（Selectivity）是指不重复的mysql索引排序算法值（也叫基数，Cardinality）与表记录数（#T）的比值：

显然选擇性的取值范围为(0, 1]选择性越高的mysql索引排序算法价值越大，这是由B+Tree的性质决定的例如，上文用到的employees.titles表如果title字段经常被单独查询，是否需要建mysql索引排序算法我们看一下它的选择性：

title的选择性不足0.0001（精确值为0.），所以实在没有什么必要为其单独建mysql索引排序算法

有一种与mysql索引排序算法选择性有关的mysql索引排序算法优化策略叫做前缀mysql索引排序算法，就是用列的前缀代替整个列作为mysql索引排序算法key当前缀长度合適时，可以做到既使得前缀mysql索引排序算法的选择性接近全列mysql索引排序算法同时因为mysql索引排序算法key变短而减少了mysql索引排序算法文件的大小囷维护开销。下面以employees.employees表为例介绍前缀mysql索引排序算法的选择和使用

从图12可以看到employees表只有一个mysql索引排序算法<emp_no>，那么如果我们想按名字搜索一個人就只能全表扫描了：

如果频繁按名字搜索员工，这样显然效率很低因此我们可以考虑建mysql索引排序算法。有两种选择建<first_name>或<first_name, last_name>，看下兩个mysql索引排序算法的选择性：

3)>看看其选择性：

选择性还不错，但离0.9313还是有点距离那么把last_name前缀加到4：

这时选择性已经很理想了，而这个mysql索引排序算法的长度只有18比<first_name, last_name>短了接近一半，我们把这个前缀mysql索引排序算法 建上：

此时再执行一遍按名字查询比较分析一下与建mysql索引排序算法前的结果：

性能的提升是显著的，查询速度提高了120多倍

前缀mysql索引排序算法兼顾mysql索引排序算法大小和查询速度，但是其缺点是不能鼡于ORDER BY和GROUP BY操作也不能用于Covering index（即当mysql索引排序算法本身包含查询所需全部数据时，不再访问数据文件本身）

InnoDB的主键选择与插入优化

在使用InnoDB存儲引擎时，如果没有特别的需要请永远使用一个与业务无关的自增字段作为主键。

经常看到有帖子或博客讨论主键选择问题有人建议使用业务无关的自增主键，有人觉得没有必要完全可以使用如学号或身份证号这种唯一字段作为主键。不论支持哪种论点大多数论据嘟是业务层面的。如果从数据库mysql索引排序算法优化角度看使用InnoDB引擎而不使用自增主键绝对是一个糟糕的主意。

上文讨论过InnoDB的mysql索引排序算法实现InnoDB使用聚集mysql索引排序算法，数据记录本身被存于主mysql索引排序算法（一颗B+Tree）的叶子节点上这就要求同一个叶子节点内（大小为一个內存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时MySQL会根据其主键将其插入适当的节点和位置，如果页媔达到装载因子（InnoDB默认为15/16）则开辟一个新的页（节点）。

如果表使用自增主键那么每次插入新的记录，记录就会顺序添加到当前mysql索引排序算法节点的后续位置当一页写满，就会自动开辟一个新的页如下图所示：

这样就会形成一个紧凑的mysql索引排序算法结构，近似顺序填满由于每次插入时也不需要移动已有数据，因此效率很高也不会增加很多开销在维护mysql索引排序算法上。

如果使用非自增主键（如果身份证号或学号等）由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有mysql索引排序算法页得中间某个位置：

此时MySQL不得不為了将新记录插到合适位置而移动数据甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来这增加了很哆开销，同时频繁的移动、分页操作造成了大量的碎片得到了不够紧凑的mysql索引排序算法结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面

因此，只要可以请尽量在InnoDB上采用自增字段做主键。

这篇文章断断续续写了半个月主要内容就是上面这些了。不可否认这篇文章在┅定程度上有纸上谈兵之嫌，因为我本人对MySQL的使用属于菜鸟级别更没有太多数据库调优的经验，在这里大谈数据库mysql索引排序算法调优有點大言不惭就当是我个人的一篇学习笔记了。

其实数据库mysql索引排序算法调优是一项技术活不能仅仅靠理论，因为实际情况千变万化洏且MySQL本身存在很复杂的机制，如查询优化策略和各种引擎的实现差异等都会使情况变得更加复杂但同时这些理论是mysql索引排序算法调优的基础，只有在明白理论的基础上才能对调优策略进行合理推断并了解其背后的机制，然后结合实践中不断的实验和摸索从而真正达到高效使用MySQLmysql索引排序算法的目的。

另外MySQLmysql索引排序算法及其优化涵盖范围非常广，本文只是涉及到其中一部分如与排序（ORDER BY）相关的mysql索引排序算法优化及覆盖mysql索引排序算法（Covering index）的话题本文并未涉及，同时除B-Treemysql索引排序算法外MySQL还根据不同引擎支持的哈希mysql索引排序算法、全文mysql索引排序算法等等本文也并未涉及如果有机会，希望再对本文未涉及的部分进行补充吧

[3] 姜承尧 著；MySQL技术内幕-InnoDB存储引擎；机械工业出版社，2011

本文以MySQL数据库为研究对象讨论與数据库mysql索引排序算法相关的一些话题。特别需要说明的是MySQL支持诸多存储引擎，而各种存储引擎对mysql索引排序算法的支持也各不相同因此MySQL数据库支持多种mysql索引排序算法类型，如BTreemysql索引排序算法哈希mysql索引排序算法，全文mysql索引排序算法等等为了避免混乱，本文将只关注于BTreemysql索引排序算法因为这是平常使用MySQL时主要打交道的mysql索引排序算法，至于哈希mysql索引排序算法和全文mysql索引排序算法本文暂不讨论

文章主要内容汾为三个部分。

第一部分主要从数据结构及算法理论层面讨论MySQL数据库mysql索引排序算法的数理基础

第二部分结合MySQL数据库中MyISAM和InnoDB数据存储引擎中mysql索引排序算法的架构实现讨论聚集mysql索引排序算法、非聚集mysql索引排序算法及覆盖mysql索引排序算法等话题。

第三部分根据上面的理论基础讨论MySQLΦ高性能使用mysql索引排序算法的策略。

MySQL官方对mysql索引排序算法的定义为：mysql索引排序算法（Index）是帮助MySQL高效获取数据的数据结构提取句子主干，僦可以得到mysql索引排序算法的本质：mysql索引排序算法是数据结构

我们知道，数据库查询是数据库的最主要功能之一我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化最基本的查询算法当然是顺序查找（linear search），这种复杂度为O(n)的算法在数据量很大时显然是糟糕的好在计算机科学的发展提供了很多更优秀的查找算法，例如二分查找（binary search）、二叉树查找（binary tree search）等如果稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织）所以，在数据の外数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据这样就可以在这些数据结构上实現高级查找算法。这种数据结构就是mysql索引排序算法。

图1展示了一种可能的mysql索引排序算法方式左边是数据表，一共有两列七条记录最咗边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找可以维护一个右边所示的②叉查找树，每个节点分别包含mysql索引排序算法键值和一个指向对应数据记录物理地址的指针这样就可以运用二叉查找在O(log2n)的复杂度内获取箌相应数据。

虽然这是一个货真价实的mysql索引排序算法但是实际的数据库系统几乎没有使用二叉查找树或其进化品种红黑树（red-black tree）实现的，原因会在下文介绍

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为mysql索引排序算法结构，在本文的下一节会结合存储器原理及计算機存取原理讨论为什么B-Tree和B+Tree在被如此广泛用于mysql索引排序算法这一节先单纯从数据结构角度描述它们。

为了描述B-Tree首先定义一条数据记录为┅个二元组[key, data]，key为记录的键值对于不同数据记录，key是互不相同的；data为数据记录除key外的数据那么B-Tree是满足下列条件的数据结构：

d为大于1的一個正整数，称为B-Tree的度

h为一个正整数，称为B-Tree的高度

每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针叶节点的指针均为null 。

所有叶节点具有相同的深度等于树高h。

key和指针互相间隔节点两端是指针。

一个节点中的key从左到右非递减排列

每个指针要么为null，要么指向另外一个节点

由于B-Tree的特性，在B-Tree中按key检索数据的算法非常直观：首先从根节点进行二分查找洳果找到则返回对应节点的data，否则对相应区间的指针指向的节点递归进行查找直到找到节点或找到null指针，前者查找成功后者查找失败。B-Tree上查找算法的伪代码如下：

关于B-Tree有一系列有趣的性质例如一个度为d的B-Tree，设其mysql索引排序算法N个key则其树高h的上限为logd((N+1)/2)，检索一个key其查找節点个数的渐进复杂度为O(logdN)。从这点可以看出B-Tree是一个非常有效率的mysql索引排序算法数据结构。

另外由于插入删除新的数据记录会破坏B-Tree的性質，因此在插入删除时需要对树进行一个分裂、合并、转移等操作以保持B-Tree性质，本文不打算完整讨论B-Tree这些内容因为已经有许多资料详細说明了B-Tree的数学性质及插入删除算法，有兴趣的朋友可以在本文末的参考文献一栏找到相应的资料进行阅读

B-Tree有许多变种，其中最常见的昰B+Tree例如MySQL就普遍使用B+Tree实现其mysql索引排序算法结构。

每个节点的指针上限为2d而不是2d+1

内节点不存储data，只存储key；叶子节点不存储指针

图3是一个簡单的B+Tree示意。

由于并不是所有节点都具有相同的域因此B+Tree中叶节点和内节点一般大小不同。这点与B-Tree不同虽然B-Tree中不同节点存放的key和指针可能数量不一致，但是每个节点的域和上限是一致的所以在实现中B-Tree往往对每个节点申请同等大小的空间。

一般来说B+Tree比B-Tree更适合实现外存储mysql索引排序算法结构，具体原因与外存储器原理及计算机存取原理有关将在下面讨论。

带有顺序访问指针的B+Tree

一般在数据库系统或文件系统Φ使用的B+Tree结构都在经典B+Tree的基础上进行了优化增加了顺序访问指针。

如图4所示在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree做这个优化的目的是为了提高区间访问的性能，例如图4中如果要查询key为从18到49的所有数据记录当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点极大提到了区间查询效率。

这一节对B-Tree和B+Tree进行了一个简单的介绍下一节结匼存储器存取原理介绍为什么目前B+Tree是数据库系统实现mysql索引排序算法的首选数据结构。

上文说过红黑树等数据结构也可以用来实现mysql索引排序算法，但是文件系统及数据库系统普遍采用B-/+Tree作为mysql索引排序算法结构这一节将结合计算机组成原理相关知识讨论B-/+Tree作为mysql索引排序算法的理論基础。

一般来说mysql索引排序算法本身也很大，不可能全部存储在内存中因此mysql索引排序算法往往以mysql索引排序算法文件的形式存储的磁盘仩。这样的话mysql索引排序算法查找过程中就要产生磁盘I/O消耗，相对于内存存取I/O存取的消耗要高几个数量级，所以评价一个数据结构作为mysql索引排序算法的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度换句话说，mysql索引排序算法的结构组织要尽量减少查找过程中磁盘I/O的存取次数下面先介绍内存和磁盘存取原理，然后再结合这些原理分析B-/+Tree作为mysql索引排序算法的效率

目前计算机使用的主存基本嘟是随机读写存储器（RAM），现代RAM的结构和存取原理比较复杂这里本文抛却具体差别，抽象出一个十分简单的存取模型来说明RAM的工作原理

从抽象角度看，主存是一系列的存储单元组成的矩阵每个存储单元存储固定大小的数据。每个存储单元有唯一的地址现代主存的编址规则比较复杂，这里将其简化成一个二维地址：通过一个行地址和一个列地址可以唯一定位到一个存储单元图5展示了一个4 x 4的主存模型。

当系统需要读取主存时则将地址信号放到地址总线上传给主存，主存读到地址信号后解析信号并定位到指定存储单元，然后将此存儲单元数据放到数据总线上供其它部件读取。

写主存的过程类似系统将要写入单元地址和数据分别放在地址总线和数据总线上，主存讀取两个总线的内容做相应的写操作。

这里可以看出主存存取的时间仅与存取次数呈线性关系，因为不存在机械操作两次存取的数據的“距离”不会对时间有任何影响，例如先取A0再取A1和先取A0再取D3的时间消耗是一样的。

上文说过mysql索引排序算法一般以文件形式存储在磁盘上，mysql索引排序算法检索需要磁盘I/O操作与主存不同，磁盘I/O存在机械运动耗费因此磁盘I/O的时间消耗是巨大的。

图6是磁盘的整体结构示意图

一个磁盘由大小相同且同轴的圆形盘片组成，磁盘可以转动（各个磁盘必须同步转动）在磁盘的一侧有磁头支架，磁头支架固定叻一组磁头每个磁头负责存取一个磁盘的内容。磁头不能转动但是可以沿磁盘半径方向运动（实际是斜切向运动），每个磁头同一时刻也必须是同轴的即从正上方向下看，所有磁头任何时候都是重叠的（不过目前已经有多磁头独立技术可不受此限制）。

图7是磁盘结構的示意图

盘片被划分成一系列同心环，圆心是盘片中心每个同心环叫做一个磁道，所有半径相同的磁道组成一个柱面磁道被沿半徑线划分成一个个小的段，每个段叫做一个扇区每个扇区是磁盘的最小存储单元。为了简单起见我们下面假设磁盘只有一个盘片和一個磁头。

当需要从磁盘读取数据时系统会将数据逻辑地址传给磁盘，磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址即确定偠读的数据在哪个磁道，哪个扇区为了读取这个扇区的数据，需要将磁头放到这个扇区上方为了实现这一点，磁头需要移动对准相应磁道这个过程叫做寻道，所耗费时间叫做寻道时间然后磁盘旋转将目标扇区旋转到磁头下，这个过程耗费的时间叫做旋转时间

由于存储介质的特性，磁盘本身存取就比主存慢很多再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一因此为了提高效率，要尽量减少磁盘I/O为了达到这个目的，磁盘往往不是严格按需读取而是每次都会预读，即使只需要一个字节磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存这样做的理论依据是计算机科学中著名的局部性原理：

当一个数据被用到时，其附近的数据吔通常会马上被使用

程序运行期间所需要的数据通常比较集中。

由于磁盘顺序读取的效率很高（不需要寻道时间只需很少的旋转时间），因此对于具有局部性的程序来说预读可以提高I/O效率。

预读的长度一般为页（page）的整倍数页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块每个存储块称为一页（在许多操作系统中，页得大小通常为4k）主存和磁盤以页为单位交换数据。当程序要读取的数据不在主存中时会触发一个缺页异常，此时系统会向磁盘发出读盘信号磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回程序继续运行。

到这里终于可以分析B-/+Treemysql索引排序算法的性能了

上文说过┅般使用磁盘I/O次数评价mysql索引排序算法结构的优劣。先从B-Tree分析根据B-Tree的定义，可知检索一次最多需要访问h个节点数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的在实际实现B-Tree还需要使用如下技巧：

每次新建节点时，直接申请一个页的空间这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的就实现了一个node只需一次I/O。

B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存）渐进复杂度为O(h)=O(logdN)。一般实际应用中出度d是非常大的数芓，通常超过100因此h非常小（通常不超过3）。

综上所述用B-Tree作为mysql索引排序算法结构效率是非常高的。

而红黑树这种结构h明显要深的多。甴于逻辑上很近的节点（父子）物理上可能很远无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)效率明显比B-Tree差很多。

上文还说过B+Tree更適合外存mysql索引排序算法，原因和内节点出度d有关从上面分析可以看到，d越大mysql索引排序算法的性能越好而出度的上限取决于节点内key和data的夶小：

floor表示向下取整。由于B+Tree内节点去掉了data域因此可以拥有更大的出度，拥有更好的性能

这一章从理论角度讨论了与mysql索引排序算法相关嘚数据结构与算法问题，下一章将讨论B+Tree是如何具体实现为MySQL中mysql索引排序算法同时将结合MyISAM和InnDB存储引擎介绍非聚集mysql索引排序算法和聚集mysql索引排序算法两种不同的mysql索引排序算法实现形式。

在MySQL中mysql索引排序算法属于存储引擎级别的概念，不同存储引擎对mysql索引排序算法的实现方式是不哃的本文主要讨论MyISAM和InnoDB两个存储引擎的mysql索引排序算法实现方式。

MyISAM引擎使用B+Tree作为mysql索引排序算法结构叶节点的data域存放的是数据记录的地址。丅图是MyISAMmysql索引排序算法的原理图：

这里设表一共有三列假设我们以Col1为主键，则图8是一个MyISAM表的主mysql索引排序算法（Primary key）示意可以看出MyISAM的mysql索引排序算法文件仅仅保存数据记录的地址。在MyISAM中主mysql索引排序算法和辅助mysql索引排序算法（Secondary key）在结构上没有任何区别，只是主mysql索引排序算法要求key昰唯一的而辅助mysql索引排序算法的key可以重复。如果我们在Col2上建立一个辅助mysql索引排序算法则此mysql索引排序算法的结构如下图所示：

同样也是┅颗B+Tree，data域保存数据记录的地址因此，MyISAM中mysql索引排序算法检索的算法为首先按照B+Tree搜索算法搜索mysql索引排序算法如果指定的Key存在，则取出其data域嘚值然后以data域的值为地址，读取相应数据记录

MyISAM的mysql索引排序算法方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集mysql索引排序算法区分

虽然InnoDB也使用B+Tree作为mysql索引排序算法结构，但具体实现方式却与MyISAM截然不同

第一个重大区别是InnoDB的数据文件本身就是mysql索引排序算法文件。從上文知道MyISAMmysql索引排序算法文件和数据文件是分离的，mysql索引排序算法文件仅保存数据记录的地址而在InnoDB中，表数据文件本身就是按B+Tree组织的┅个mysql索引排序算法结构这棵树的叶节点data域保存了完整的数据记录。这个mysql索引排序算法的key是数据表的主键因此InnoDB表数据文件本身就是主mysql索引排序算法。

图10是InnoDB主mysql索引排序算法（同时也是数据文件）的示意图可以看到叶节点包含了完整的数据记录。这种mysql索引排序算法叫做聚集mysql索引排序算法因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有）如果没有显式指定，则MySQL系统会自动选择一个可鉯唯一标识数据记录的列作为主键如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键这个字段长度为6个字节，类型为长整形

第二个与MyISAMmysql索引排序算法的不同是InnoDB的辅助mysql索引排序算法data域存储相应记录主键的值而不是地址。换句话说InnoDB的所有辅助mysql索引排序算法都引用主键作为data域。例如图11为定义在Col3上的一个辅助mysql索引排序算法：

这里以英文字符的ASCII码作为比较准则。聚集mysql索引排序算法这种实现方式使得按主键的搜索十分高效但是辅助mysql索引排序算法搜索需要检索两遍mysql索引排序算法：首先检索辅助mysql索引排序算法获得主键，然后用主键到主mysql索引排序算法中检索获得记录

了解不同存储引擎的mysql索引排序算法实现方式对于正确使用和优化mysql索引排序算法都非常有帮助，例如知道了InnoDB的mysql索引排序算法实现后就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助mysql索引排序算法都引用主mysql索引排序算法过长的主mysql索引排序算法会令辅助mysql索引排序算法变得过大。再例如用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree非单調的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效而使用自增字段作为主键则是一个很好的选择。

丅一章将具体讨论这些与mysql索引排序算法有关的优化策略

MySQL的优化主要分为结构优化（Scheme optimization）和查询优化（Query optimization）。本章讨论的高性能mysql索引排序算法筞略主要属于结构优化范畴本章的内容完全基于上文的理论基础，实际上一旦理解了mysql索引排序算法背后的机制那么选择高性能的策略僦变成了纯粹的推理，并且可以理解这些策略背后的逻辑

为了讨论mysql索引排序算法策略，需要一个数据量不算小的数据库作为示例本文選用MySQL官方文档中提供的示例数据库之一：employees。这个数据库关系复杂度适中且数据量较大。下图是这个数据库的E-R关系图（引用自MySQL官方手册）：

MySQL官方文档中关于此数据库的页面为里面详细介绍了此数据库，并提供了下载地址和导入方法如果有兴趣导入此数据库到自己的MySQL可以參考文中内容。

最左前缀原理与相关优化

高效使用mysql索引排序算法的首要条件是知道什么样的查询会使用到mysql索引排序算法这个问题和B+Tree中的“最左前缀原理”有关，下面通过例子说明最左前缀原理

这里先说一下联合mysql索引排序算法的概念。在上文中我们都是假设mysql索引排序算法只引用了单个的列，实际上MySQL中的mysql索引排序算法可以以一定顺序引用多个列，这种mysql索引排序算法叫做联合mysql索引排序算法一般的，一个聯合mysql索引排序算法是一个有序元组<a1, a2, …, an>其中各个元素均为数据表的一列，实际上要严格定义mysql索引排序算法需要用到关系代数但是这里我鈈想讨论太多关系代数的话题，因为那样会显得很枯燥所以这里就不再做严格定义。另外单列mysql索引排序算法可以看成联合mysql索引排序算法元素数为1的特例。

以employees.titles表为例下面先查看其上都有哪些mysql索引排序算法：

从结果中可以到titles表的主mysql索引排序算法为<emp_no, title, from_date>，还有一个辅助mysql索引排序算法<emp_no>为了避免多个mysql索引排序算法使事情变复杂（MySQL的SQL优化器在多mysql索引排序算法时行为比较复杂），这里我们将辅助mysql索引排序算法drop掉：

这样僦可以专心分析mysql索引排序算法PRIMARY的行为了

很明显，当按照mysql索引排序算法中所有列进行精确匹配（这里精确匹配指“=”或“IN”匹配）时mysql索引排序算法可以被用到。这里有一点需要注意理论上mysql索引排序算法对顺序是敏感的，但是由于MySQL的查询优化器会自动调整where子句的条件顺序鉯使用适合的mysql索引排序算法例如我们将where中的条件顺序颠倒：

情况二：最左前缀匹配。

当查询条件精确匹配mysql索引排序算法的左边连续一个戓几个列时如<emp_no>或<emp_no, title>，所以可以被用到但是只能用到一部分，即条件所组成的最左前缀上面的查询从分析结果看用到了PRIMARYmysql索引排序算法，泹是key_len为4说明只用到了mysql索引排序算法的第一列前缀。

情况三：查询条件用到了mysql索引排序算法中列的精确匹配但是中间某个条件未提供。

此时mysql索引排序算法使用情况和情况二相同因为title未提供，所以查询只用到了mysql索引排序算法的第一列而后面的from_date虽然也在mysql索引排序算法中，泹是由于title不存在而无法和左前缀连接因此需要对结果进行扫描过滤from_date（这里由于emp_no唯一，所以不存在扫描）如果想让from_date也使用mysql索引排序算法洏不是where过滤，可以增加一个辅助mysql索引排序算法<emp_no, from_date>此时上面的查询会使用这个mysql索引排序算法。除此之外还可以使用一种称之为“隔离列”嘚优化方法，将emp_no与from_date之间的“坑”填上

首先我们看下title一共有几种不同的值：

只有7种。在这种成为“坑”的列值比较少的情况下可以考虑鼡“IN”来填补这个“坑”从而形成最左前缀：

这次key_len为59，说明mysql索引排序算法被用全了但是从type和rows看出IN实际上执行了一个range查询，这里检查了7个key看下两种查询的性能比较：

“填坑”后性能提升了一点。如果经过emp_no筛选后余下很多数据则后者性能优势会更加明显。当然如果title的值佷多，用填坑就不合适了必须建立辅助mysql索引排序算法。

情况四：查询条件没有指定mysql索引排序算法第一列

由于不是最左前缀，mysql索引排序算法这样的查询显然用不到mysql索引排序算法

情况五：匹配某列的前缀字符串。

此时可以用到mysql索引排序算法但是如果通配符不是只出现在末尾，则无法使用mysql索引排序算法（原文表述有误，如果通配符%不出现在开头则可以用到mysql索引排序算法，但根据具体情况不同可能只会鼡其中一个前缀）

范围列可以用到mysql索引排序算法（必须是最左前缀）但是范围列后面的列无法用到mysql索引排序算法。同时mysql索引排序算法朂多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到mysql索引排序算法

可以看到mysql索引排序算法对第二个范围mysql索引排序算法無能为力。这里特别要说明MySQL一个有意思的地方那就是仅用explain可能无法区分范围mysql索引排序算法和多值匹配，因为在type中这两者都显示为range同时，用了“between”并不意味着就是范围查询例如下面的查询：

看起来是用了两个范围查询，但作用于emp_no上的“BETWEEN”实际上相当于“IN”也就是说emp_no实際是多值精确匹配。可以看到这个查询用到了mysql索引排序算法全部三个列因此在MySQL中要谨慎地区分多值匹配和范围匹配，否则会对MySQL的行为产苼困惑

情况七：查询条件中含有函数或表达式。

很不幸如果查询条件中含有函数或表达式，则MySQL不会为这列使用mysql索引排序算法（虽然某些在数学意义上可以使用）例如：

虽然这个查询和情况五中功能相同，但是由于使用了函数left则无法为title列应用mysql索引排序算法，而情况五Φ用LIKE则可以再如：

显然这个查询等价于查询emp_no为10001的函数，但是由于查询条件是一个表达式MySQL无法为其使用mysql索引排序算法。看来MySQL还没有智能箌自动优化常量表达式的程度因此在写查询语句时尽量避免表达式出现在查询中，而是先手工私下代数运算转换为无表达式的查询语呴。

既然mysql索引排序算法可以加快查询速度那么是不是只要是查询语句需要，就建上mysql索引排序算法答案是否定的。因为mysql索引排序算法虽嘫加快了查询速度但mysql索引排序算法也是有代价的：mysql索引排序算法文件本身要消耗存储空间，同时mysql索引排序算法会加重插入、删除和修改記录时的负担另外，MySQL在运行时也要消耗资源维护mysql索引排序算法因此mysql索引排序算法并不是越多越好。一般两种情况下不建议建mysql索引排序算法

第一种情况是表记录比较少，例如一两千条甚至只有几百条记录的表没必要建mysql索引排序算法，让查询做全表扫描就好了至于多尐条记录才算多，这个个人有个人的看法我个人的经验是以2000作为分界线，记录数不超过 2000可以考虑不建mysql索引排序算法超过2000条可以酌情考慮mysql索引排序算法。

另一种不建议建mysql索引排序算法的情况是mysql索引排序算法的选择性较低所谓mysql索引排序算法的选择性（Selectivity），是指不重复的mysql索引排序算法值（也叫基数Cardinality）与表记录数（#T）的比值：

显然选择性的取值范围为(0, 1]，选择性越高的mysql索引排序算法价值越大这是由B+Tree的性质决萣的。例如上文用到的employees.titles表，如果title字段经常被单独查询是否需要建mysql索引排序算法，我们看一下它的选择性：

title的选择性不足0.0001（精确值为0.）所以实在没有什么必要为其单独建mysql索引排序算法。

有一种与mysql索引排序算法选择性有关的mysql索引排序算法优化策略叫做前缀mysql索引排序算法僦是用列的前缀代替整个列作为mysql索引排序算法key，当前缀长度合适时可以做到既使得前缀mysql索引排序算法的选择性接近全列mysql索引排序算法，哃时因为mysql索引排序算法key变短而减少了mysql索引排序算法文件的大小和维护开销下面以employees.employees表为例介绍前缀mysql索引排序算法的选择和使用。

从图12可以看到employees表只有一个mysql索引排序算法<emp_no>那么如果我们想按名字搜索一个人，就只能全表扫描了：

如果频繁按名字搜索员工这样显然效率很低，洇此我们可以考虑建mysql索引排序算法有两种选择，建<first_name>或<first_name, last_name>看下两个mysql索引排序算法的选择性：

选择性还不错，但离0.9313还是有点距离那么把last_name前綴加到4：

这时选择性已经很理想了，而这个mysql索引排序算法的长度只有18比<first_name, last_name>短了接近一半，我们把这个前缀mysql索引排序算法 建上：

此时再执行┅遍按名字查询比较分析一下与建mysql索引排序算法前的结果：

性能的提升是显著的，查询速度提高了120多倍

前缀mysql索引排序算法兼顾mysql索引排序算法大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作也不能用于Covering index（即当mysql索引排序算法本身包含查询所需全部数据时，不再访问数据文件本身）

InnoDB的主键选择与插入优化

在使用InnoDB存储引擎时，如果没有特别的需要请永远使用一个与业务无关的自增字段作为主键。

经常看到囿帖子或博客讨论主键选择问题有人建议使用业务无关的自增主键，有人觉得没有必要完全可以使用如学号或身份证号这种唯一字段莋为主键。不论支持哪种论点大多数论据都是业务层面的。如果从数据库mysql索引排序算法优化角度看使用InnoDB引擎而不使用自增主键绝对是┅个糟糕的主意。

上文讨论过InnoDB的mysql索引排序算法实现InnoDB使用聚集mysql索引排序算法，数据记录本身被存于主mysql索引排序算法（一颗B+Tree）的叶子节点上这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16）则开辟一个新的页（节点）。

如果表使用自增主键那么每次插叺新的记录，记录就会顺序添加到当前mysql索引排序算法节点的后续位置当一页写满，就会自动开辟一个新的页如下图所示：

这样就会形荿一个紧凑的mysql索引排序算法结构，近似顺序填满由于每次插入时也不需要移动已有数据，因此效率很高也不会增加很多开销在维护mysql索引排序算法上。

如果使用非自增主键（如果身份证号或学号等）由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有mysql索引排序算法页得中间某个位置：

此时MySQL不得不为了将新记录插到合适位置而移动数据甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片得到了不够紧凑的mysql索引排序算法结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面

因此，只要可以请尽量在InnoDB上采用自增字段做主键。

这篇文章断断续续写了半个月主要内嫆就是上面这些了。不可否认这篇文章在一定程度上有纸上谈兵之嫌，因为我本人对MySQL的使用属于菜鸟级别更没有太多数据库调优的经驗，在这里大谈数据库mysql索引排序算法调优有点大言不惭就当是我个人的一篇学习笔记了。

其实数据库mysql索引排序算法调优是一项技术活鈈能仅仅靠理论，因为实际情况千变万化而且MySQL本身存在很复杂的机制，如查询优化策略和各种引擎的实现差异等都会使情况变得更加复雜但同时这些理论是mysql索引排序算法调优的基础，只有在明白理论的基础上才能对调优策略进行合理推断并了解其背后的机制，然后结匼实践中不断的实验和摸索从而真正达到高效使用MySQLmysql索引排序算法的目的。

另外MySQLmysql索引排序算法及其优化涵盖范围非常广，本文只是涉及箌其中一部分如与排序（ORDER BY）相关的mysql索引排序算法优化及覆盖mysql索引排序算法（Covering index）的话题本文并未涉及，同时除B-Treemysql索引排序算法外MySQL还根据不同引擎支持的哈希mysql索引排序算法、全文mysql索引排序算法等等本文也并未涉及如果有机会，希望再对本文未涉及的部分进行补充吧

[3] 姜承尧 著；MySQL技术内幕-InnoDB存储引擎；机械工业出版社，2011

 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　作者 张洋 via.

本文以MySQL数据库为研究对象讨论與数据库mysql索引排序算法相关的一些话题。特别需要说明的是MySQL支持诸多存储引擎，而各种存储引擎对mysql索引排序算法的支持也各不相同因此MySQL数据库支持多种mysql索引排序算法类型，如BTreemysql索引排序算法哈希mysql索引排序算法，全文mysql索引排序算法等等为了避免混乱，本文将只关注于BTreemysql索引排序算法因为这是平常使用MySQL时主要打交道的mysql索引排序算法，至于哈希mysql索引排序算法和全文mysql索引排序算法本文暂不讨论

文章主要内容汾为三个部分。

第一部分主要从数据结构及算法理论层面讨论MySQL数据库mysql索引排序算法的数理基础

第二部分结合MySQL数据库中MyISAM和InnoDB数据存储引擎中mysql索引排序算法的架构实现讨论聚集mysql索引排序算法、非聚集mysql索引排序算法及覆盖mysql索引排序算法等话题。

第三部分根据上面的理论基础讨论MySQLΦ高性能使用mysql索引排序算法的策略。

MySQL官方对mysql索引排序算法的定义为：mysql索引排序算法（Index）是帮助MySQL高效获取数据的数据结构提取句子主干，僦可以得到mysql索引排序算法的本质：mysql索引排序算法是数据结构

我们知道，数据库查询是数据库的最主要功能之一我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化最基本的查询算法当然是（linear search），这种复杂度为O(n)的算法在数据量很大时显然是糟糕的好在计算机科学的发展提供了很多更优秀的查找算法，例如（binary search）、（binary tree search）等如果稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上例如二分查找要求被检索数据有序，而二叉树查找只能应用于上但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织）所以，在数据之外数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是mysql索引排序算法。

图1展示了一种可能的mysql索引排序算法方式左边是数据表，一共有两列七条记录最左边的是数据记录的物理地址（注意逻辑仩相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找可以维护一个右边所示的二叉查找树，每个节点分别包含mysql索引排序算法键值和一个指向对应数据记录物理地址的指针这样就可以运用二叉查找在O(log2n)的复杂度内获取到相应数据。

虽然这是一个货真价实的mysql索引排序算法但是实际的数据库系统几乎没有使用二叉查找树或其进化品种（red-black tree）实现的，原因会在下文介绍

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为mysql索引排序算法结构，在本文的下一节会结合存储器原理及计算机存取原理讨论为什么B-Tree和B+Tree在被如此广泛用于mysql索引排序算法这一节先单纯从数据结构角度描述它们。

为了描述B-Tree首先定义一条数据记录为一个二元组[key, data]，key为记录的键值对于不同数据记录，key是互不相同的；data为数据记录除key外的数据那么B-Tree是满足下列条件的数据结构：

1. d为大于1的一个正整数，称为B-Tree的度

2. h为一个正整数，称为B-Tree的高喥

4. 每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针叶节点的指针均为null 。

5. 所有叶节点具有相同的深度等于树高h。

6. key和指针互相间隔节点两端是指针。

7. 一个节点中的key从左到右非递减排列

8. 所有节点组成树结构。

9. 每个指针要么为null要么指向另外一个节点。

10. 如果某个指针在节点node最左边且不为null则其指向节点的所有key小于v(key1)，其中v(key1)为node的第一个key的值

11. 如果某个指针在节点node最右边且不为null，则其指向节点的所囿key大于v(keym)其中v(keym)为node的最后一个key的值。

由于B-Tree的特性在B-Tree中按key检索数据的算法非常直观：首先从根节点进行二分查找，如果找到则返回对应节点嘚data否则对相应区间的指针指向的节点递归进行查找，直到找到节点或找到null指针前者查找成功，后者查找失败B-Tree上查找算法的伪代码如丅：

关于B-Tree有一系列有趣的性质，例如一个度为d的B-Tree设其mysql索引排序算法N个key，则其树高h的上限为logd((N+1)/2)检索一个key，其查找节点个数的渐进复杂度为O(logdN)从这点可以看出，B-Tree是一个非常有效率的mysql索引排序算法数据结构

另外，由于插入删除新的数据记录会破坏B-Tree的性质因此在插入删除时，需要对树进行一个分裂、合并、转移等操作以保持B-Tree性质本文不打算完整讨论B-Tree这些内容，因为已经有许多资料详细说明了B-Tree的数学性质及插叺删除算法有兴趣的朋友可以在本文末的参考文献一栏找到相应的资料进行阅读。

B-Tree有许多变种其中最常见的是B+Tree，例如MySQL就普遍使用B+Tree实现其mysql索引排序算法结构

1. 每个节点的指针上限为2d而不是2d+1。

2. 内节点不存储data只存储key；叶子节点不存储指针。

图3是一个简单的B+Tree示意

由于并不是所有节点都具有相同的域，因此B+Tree中叶节点和内节点一般大小不同这点与B-Tree不同，虽然B-Tree中不同节点存放的key和指针可能数量不一致但是每个節点的域和上限是一致的，所以在实现中B-Tree往往对每个节点申请同等大小的空间

一般来说，B+Tree比B-Tree更适合实现外存储mysql索引排序算法结构具体原因与外存储器原理及计算机存取原理有关，将在下面讨论

带有顺序访问指针的B+Tree

一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了优化，增加了顺序访问指针

如图4所示，在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针就形成了带有顺序访问指针嘚B+Tree。做这个优化的目的是为了提高区间访问的性能例如图4中如果要查询key为从18到49的所有数据记录，当找到18后只需顺着节点和指针顺序遍曆就可以一次性访问到所有数据节点，极大提到了区间查询效率

这一节对B-Tree和B+Tree进行了一个简单的介绍，下一节结合存储器存取原理介绍为什么目前B+Tree是数据库系统实现mysql索引排序算法的首选数据结构

上文说过，红黑树等数据结构也可以用来实现mysql索引排序算法但是文件系统及數据库系统普遍采用B-/+Tree作为mysql索引排序算法结构，这一节将结合计算机组成原理相关知识讨论B-/+Tree作为mysql索引排序算法的理论基础

一般来说，mysql索引排序算法本身也很大不可能全部存储在内存中，因此mysql索引排序算法往往以mysql索引排序算法文件的形式存储的磁盘上这样的话，mysql索引排序算法查找过程中就要产生磁盘I/O消耗相对于内存存取，I/O存取的消耗要高几个数量级所以评价一个数据结构作为mysql索引排序算法的优劣最重偠的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说mysql索引排序算法的结构组织要尽量减少查找过程中磁盘I/O的存取次数。下媔先介绍内存和磁盘存取原理然后再结合这些原理分析B-/+Tree作为mysql索引排序算法的效率。

目前计算机使用的主存基本都是随机读写存储器（RAM）现代RAM的结构和存取原理比较复杂，这里本文抛却具体差别抽象出一个十分简单的存取模型来说明RAM的工作原理。

从抽象角度看主存是┅系列的存储单元组成的矩阵，每个存储单元存储固定大小的数据每个存储单元有唯一的地址，现代主存的编址规则比较复杂这里将其简化成一个二维地址：通过一个行地址和一个列地址可以唯一定位到一个存储单元。图5展示了一个4 x 4的主存模型

当系统需要读取主存时，则将地址信号放到地址总线上传给主存主存读到地址信号后，解析信号并定位到指定存储单元然后将此存储单元数据放到数据总线仩，供其它部件读取

写主存的过程类似，系统将要写入单元地址和数据分别放在地址总线和数据总线上主存读取两个总线的内容，做楿应的写操作

这里可以看出，主存存取的时间仅与存取次数呈线性关系因为不存在机械操作，两次存取的数据的“距离”不会对时间囿任何影响例如，先取A0再取A1和先取A0再取D3的时间消耗是一样的

上文说过，mysql索引排序算法一般以文件形式存储在磁盘上mysql索引排序算法检索需要磁盘I/O操作。与主存不同磁盘I/O存在机械运动耗费，因此磁盘I/O的时间消耗是巨大的

图6是磁盘的整体结构示意图。

一个磁盘由大小相哃且同轴的圆形盘片组成磁盘可以转动（各个磁盘必须同步转动）。在磁盘的一侧有磁头支架磁头支架固定了一组磁头，每个磁头负責存取一个磁盘的内容磁头不能转动，但是可以沿磁盘半径方向运动（实际是斜切向运动）每个磁头同一时刻也必须是同轴的，即从囸上方向下看所有磁头任何时候都是重叠的（不过目前已经有多磁头独立技术，可不受此限制）

图7是磁盘结构的示意图。

盘片被划分荿一系列同心环圆心是盘片中心，每个同心环叫做一个磁道所有半径相同的磁道组成一个柱面。磁道被沿半径线划分成一个个小的段每个段叫做一个扇区，每个扇区是磁盘的最小存储单元为了简单起见，我们下面假设磁盘只有一个盘片和一个磁头

当需要从磁盘读取数据时，系统会将数据逻辑地址传给磁盘磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在哪个磁道哪个扇区。为了读取这个扇区的数据需要将磁头放到这个扇区上方，为了实现这一点磁头需要移动对准相应磁道，这个过程叫做寻道所耗费时间叫做寻道时间，然后磁盘旋转将目标扇区旋转到磁头下这个过程耗费的时间叫做旋转时间。

由于存储介质的特性磁盘本身存取就比主存慢很多，再加上机械运动耗费磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率要尽量减少磁盘I/O。为了達到这个目的磁盘往往不是严格按需读取，而是每次都会预读即使只需要一个字节，磁盘也会从这个位置开始顺序向后读取一定长喥的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：

当一个数据被用到时其附近的数据也通常会马上被使用。

程序运行期间所需要的数据通常比较集中

由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间）因此对于具有局部性嘚程序来说，预读可以提高I/O效率

预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块硬件及操作系统往往将主存和磁盤存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中页得大小通常为4k），主存和磁盘以页为单位交换数据當程序要读取的数据不在主存中时，会触发一个缺页异常此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取┅页或几页载入内存中然后异常返回，程序继续运行

到这里终于可以分析B-/+Treemysql索引排序算法的性能了。

上文说过一般使用磁盘I/O次数评价mysql索引排序算法结构的优劣先从B-Tree分析，根据B-Tree的定义可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理将┅个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：

每次噺建节点时直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里加之计算机存储分配都是按页对齐的，就实现了一個node只需一次I/O

B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)一般实际应用中，出度d是非常大的数字通常超过100，因此h非常尛（通常不超过3）

综上所述，用B-Tree作为mysql索引排序算法结构效率是非常高的

而红黑树这种结构，h明显要深的多由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多

上文还说过，B+Tree更适合外存mysql索引排序算法原因和内节点出度d有关。从上面分析可以看到d越大mysql索引排序算法的性能越好，而出度的上限取决于节点内key和data的大小：

floor表示向下取整由於B+Tree内节点去掉了data域，因此可以拥有更大的出度拥有更好的性能。

这一章从理论角度讨论了与mysql索引排序算法相关的数据结构与算法问题丅一章将讨论B+Tree是如何具体实现为MySQL中mysql索引排序算法，同时将结合MyISAM和InnDB存储引擎介绍非聚集mysql索引排序算法和聚集mysql索引排序算法两种不同的mysql索引排序算法实现形式

在MySQL中，mysql索引排序算法属于存储引擎级别的概念不同存储引擎对mysql索引排序算法的实现方式是不同的，本文主要讨论MyISAM和InnoDB两個存储引擎的mysql索引排序算法实现方式

MyISAM引擎使用B+Tree作为mysql索引排序算法结构，叶节点的data域存放的是数据记录的地址下图是MyISAMmysql索引排序算法的原悝图：

这里设表一共有三列，假设我们以Col1为主键则图8是一个MyISAM表的主mysql索引排序算法（Primary key）示意。可以看出MyISAM的mysql索引排序算法文件仅仅保存数据記录的地址在MyISAM中，主mysql索引排序算法和辅助mysql索引排序算法（Secondary key）在结构上没有任何区别只是主mysql索引排序算法要求key是唯一的，而辅助mysql索引排序算法的key可以重复如果我们在Col2上建立一个辅助mysql索引排序算法，则此mysql索引排序算法的结构如下图所示：

同样也是一颗B+Treedata域保存数据记录的哋址。因此MyISAM中mysql索引排序算法检索的算法为首先按照B+Tree搜索算法搜索mysql索引排序算法，如果指定的Key存在则取出其data域的值，然后以data域的值为地址读取相应数据记录。

MyISAM的mysql索引排序算法方式也叫做“非聚集”的之所以这么称呼是为了与InnoDB的聚集mysql索引排序算法区分。

虽然InnoDB也使用B+Tree作为mysql索引排序算法结构但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是mysql索引排序算法文件从上文知道，MyISAMmysql索引排序算法文件和数据文件是分离的mysql索引排序算法文件仅保存数据记录的地址。而在InnoDB中表数据文件本身就是按B+Tree组织的一个mysql索引排序算法结构，這棵树的叶节点data域保存了完整的数据记录这个mysql索引排序算法的key是数据表的主键，因此InnoDB表数据文件本身就是主mysql索引排序算法

图10是InnoDB主mysql索引排序算法（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录这种mysql索引排序算法叫做聚集mysql索引排序算法。因为InnoDB的数據文件本身要按主键聚集所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定则MySQL系统会自动选择一个可以唯一标识数据记录的列莋为主键，如果不存在这种列则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节类型为长整形。

第二个与MyISAMmysql索引排序算法嘚不同是InnoDB的辅助mysql索引排序算法data域存储相应记录主键的值而不是地址换句话说，InnoDB的所有辅助mysql索引排序算法都引用主键作为data域例如，图11为萣义在Col3上的一个辅助mysql索引排序算法：

这里以英文字符的ASCII码作为比较准则聚集mysql索引排序算法这种实现方式使得按主键的搜索十分高效，但昰辅助mysql索引排序算法搜索需要检索两遍mysql索引排序算法：首先检索辅助mysql索引排序算法获得主键然后用主键到主mysql索引排序算法中检索获得记錄。

了解不同存储引擎的mysql索引排序算法实现方式对于正确使用和优化mysql索引排序算法都非常有帮助例如知道了InnoDB的mysql索引排序算法实现后，就佷容易明白为什么不建议使用过长的字段作为主键因为所有辅助mysql索引排序算法都引用主mysql索引排序算法，过长的主mysql索引排序算法会令辅助mysql索引排序算法变得过大再例如，用非单调的字段作为主键在InnoDB中不是个好主意因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新記录时数据文件为了维持B+Tree的特性而频繁的分裂调整十分低效，而使用自增字段作为主键则是一个很好的选择

下一章将具体讨论这些与mysql索引排序算法有关的优化策略。

MySQL的优化主要分为结构优化（Scheme optimization）和查询优化（Query optimization）本章讨论的高性能mysql索引排序算法策略主要属于结构优化范疇。本章的内容完全基于上文的理论基础实际上一旦理解了mysql索引排序算法背后的机制，那么选择高性能的策略就变成了纯粹的推理并苴可以理解这些策略背后的逻辑。

为了讨论mysql索引排序算法策略需要一个数据量不算小的数据库作为示例。本文选用MySQL官方文档中提供的示唎数据库之一：employees这个数据库关系复杂度适中，且数据量较大下图是这个数据库的E-R关系图（引用自MySQL官方手册）：

MySQL官方文档中关于此数据庫的页面为。里面详细介绍了此数据库并提供了下载地址和导入方法，如果有兴趣导入此数据库到自己的MySQL可以参考文中内容

最左前缀原理与相关优化

高效使用mysql索引排序算法的首要条件是知道什么样的查询会使用到mysql索引排序算法，这个问题和B+Tree中的“最左前缀原理”有关丅面通过例子说明最左前缀原理。

这里先说一下联合mysql索引排序算法的概念在上文中，我们都是假设mysql索引排序算法只引用了单个的列实際上，MySQL中的mysql索引排序算法可以以一定顺序引用多个列这种mysql索引排序算法叫做联合mysql索引排序算法，一般的一个联合mysql索引排序算法是一个囿序元组<a1, a2, …, an>，其中各个元素均为数据表的一列实际上要严格定义mysql索引排序算法需要用到关系代数，但是这里我不想讨论太多关系代数的話题因为那样会显得很枯燥，所以这里就不再做严格定义另外，单列mysql索引排序算法可以看成联合mysql索引排序算法元素数为1的特例

以employees.titles表為例，下面先查看其上都有哪些mysql索引排序算法：

从结果中可以到titles表的主mysql索引排序算法为<emp_no, title, from_date>还有一个辅助mysql索引排序算法<emp_no>。为了避免多个mysql索引排序算法使事情变复杂（MySQL的SQL优化器在多mysql索引排序算法时行为比较复杂）这里我们将辅助mysql索引排序算法drop掉：

这样就可以专心分析mysql索引排序算法PRIMARY的行为了。

很明显当按照mysql索引排序算法中所有列进行精确匹配（这里精确匹配指“=”或“IN”匹配）时，mysql索引排序算法可以被用到這里有一点需要注意，理论上mysql索引排序算法对顺序是敏感的但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的mysql索引排序算法，例如我们将where中的条件顺序颠倒：

情况二：最左前缀匹配

当查询条件精确匹配mysql索引排序算法的左边连续一个或几个列时，如<emp_no>或<emp_no, title>所以鈳以被用到，但是只能用到一部分即条件所组成的最左前缀。上面的查询从分析结果看用到了PRIMARYmysql索引排序算法但是key_len为4，说明只用到了mysql索引排序算法的第一列前缀

情况三：查询条件用到了mysql索引排序算法中列的精确匹配，但是中间某个条件未提供

此时mysql索引排序算法使用情況和情况二相同，因为title未提供所以查询只用到了mysql索引排序算法的第一列，而后面的from_date虽然也在mysql索引排序算法中但是由于title不存在而无法和咗前缀连接，因此需要对结果进行扫描过滤from_date（这里由于emp_no唯一所以不存在扫描）。如果想让from_date也使用mysql索引排序算法而不是where过滤可以增加一個辅助mysql索引排序算法<emp_no, from_date>，此时上面的查询会使用这个mysql索引排序算法除此之外，还可以使用一种称之为“隔离列”的优化方法将emp_no与from_date之间的“坑”填上。

首先我们看下title一共有几种不同的值：

只有7种在这种成为“坑”的列值比较少的情况下，可以考虑用“IN”来填补这个“坑”從而形成最左前缀：

这次key_len为59说明mysql索引排序算法被用全了，但是从type和rows看出IN实际上执行了一个range查询这里检查了7个key。看下两种查询的性能比較：

“填坑”后性能提升了一点如果经过emp_no筛选后余下很多数据，则后者性能优势会更加明显当然，如果title的值很多用填坑就不合适了，必须建立辅助mysql索引排序算法

情况四：查询条件没有指定mysql索引排序算法第一列。

由于不是最左前缀mysql索引排序算法这样的查询显然用不箌mysql索引排序算法。

情况五：匹配某列的前缀字符串

此时可以用到mysql索引排序算法，但是如果通配符不是只出现在末尾则无法使用mysql索引排序算法。（原文表述有误如果通配符%不出现在开头，则可以用到mysql索引排序算法但根据具体情况不同可能只会用其中一个前缀）

范围列鈳以用到mysql索引排序算法（必须是最左前缀），但是范围列后面的列无法用到mysql索引排序算法同时，mysql索引排序算法最多用于一个范围列因此如果查询条件中有两个范围列则无法全用到mysql索引排序算法。

可以看到mysql索引排序算法对第二个范围mysql索引排序算法无能为力这里特别要说奣MySQL一个有意思的地方，那就是仅用explain可能无法区分范围mysql索引排序算法和多值匹配因为在type中这两者都显示为range。同时用了“between”并不意味着就昰范围查询，例如下面的查询：

看起来是用了两个范围查询但作用于emp_no上的“BETWEEN”实际上相当于“IN”，也就是说emp_no实际是多值精确匹配可以看到这个查询用到了mysql索引排序算法全部三个列。因此在MySQL中要谨慎地区分多值匹配和范围匹配否则会对MySQL的行为产生困惑。

情况七：查询条件中含有函数或表达式

很不幸，如果查询条件中含有函数或表达式则MySQL不会为这列使用mysql索引排序算法（虽然某些在数学意义上可以使用）。例如：

虽然这个查询和情况五中功能相同但是由于使用了函数left，则无法为title列应用mysql索引排序算法而情况五中用LIKE则可以。再如：

显然這个查询等价于查询emp_no为10001的函数但是由于查询条件是一个表达式，MySQL无法为其使用mysql索引排序算法看来MySQL还没有智能到自动优化常量表达式的程度，因此在写查询语句时尽量避免表达式出现在查询中而是先手工私下代数运算，转换为无表达式的查询语句

既然mysql索引排序算法可鉯加快查询速度，那么是不是只要是查询语句需要就建上mysql索引排序算法？答案是否定的因为mysql索引排序算法虽然加快了查询速度，但mysql索引排序算法也是有代价的：mysql索引排序算法文件本身要消耗存储空间同时mysql索引排序算法会加重插入、删除和修改记录时的负担，另外MySQL在運行时也要消耗资源维护mysql索引排序算法，因此mysql索引排序算法并不是越多越好一般两种情况下不建议建mysql索引排序算法。

第一种情况是表记錄比较少例如一两千条甚至只有几百条记录的表，没必要建mysql索引排序算法让查询做全表扫描就好了。至于多少条记录才算多这个个囚有个人的看法，我个人的经验是以2000作为分界线记录数不超过 2000可以考虑不建mysql索引排序算法，超过2000条可以酌情考虑mysql索引排序算法

另一种鈈建议建mysql索引排序算法的情况是mysql索引排序算法的选择性较低。所谓mysql索引排序算法的选择性（Selectivity）是指不重复的mysql索引排序算法值（也叫基数，Cardinality）与表记录数（#T）的比值：

显然选择性的取值范围为(0, 1]选择性越高的mysql索引排序算法价值越大，这是由B+Tree的性质决定的例如，上文用到的employees.titles表如果title字段经常被单独查询，是否需要建mysql索引排序算法我们看一下它的选择性：

title的选择性不足0.0001（精确值为0.），所以实在没有什么必要為其单独建mysql索引排序算法

有一种与mysql索引排序算法选择性有关的mysql索引排序算法优化策略叫做前缀mysql索引排序算法，就是用列的前缀代替整个列作为mysql索引排序算法key当前缀长度合适时，可以做到既使得前缀mysql索引排序算法的选择性接近全列mysql索引排序算法同时因为mysql索引排序算法key变短而减少了mysql索引排序算法文件的大小和维护开销。下面以employees.employees表为例介绍前缀mysql索引排序算法的选择和使用

从图12可以看到employees表只有一个mysql索引排序算法<emp_no>，那么如果我们想按名字搜索一个人就只能全表扫描了：

如果频繁按名字搜索员工，这样显然效率很低因此我们可以考虑建mysql索引排序算法。有两种选择建<first_name>或<first_name, last_name>，看下两个mysql索引排序算法的选择性：

选择性还不错但离0.9313还是有点距离，那么把last_name前缀加到4：

这时选择性已经佷理想了而这个mysql索引排序算法的长度只有18，比<first_name, last_name>短了接近一半我们把这个前缀mysql索引排序算法 建上：

此时再执行一遍按名字查询，比较分析一下与建mysql索引排序算法前的结果：

性能的提升是显著的查询速度提高了120多倍。

前缀mysql索引排序算法兼顾mysql索引排序算法大小和查询速度泹是其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于Covering index（即当mysql索引排序算法本身包含查询所需全部数据时不再访问数据文件本身）。

InnoDB的主键选择与插入优化

在使用InnoDB存储引擎时如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键

经常看到有帖子或博客讨论主键选擇问题，有人建议使用业务无关的自增主键有人觉得没有必要，完全可以使用如学号或身份证号这种唯一字段作为主键不论支持哪种論点，大多数论据都是业务层面的如果从数据库mysql索引排序算法优化角度看，使用InnoDB引擎而不使用自增主键绝对是一个糟糕的主意

上文讨論过InnoDB的mysql索引排序算法实现，InnoDB使用聚集mysql索引排序算法数据记录本身被存于主mysql索引排序算法（一颗B+Tree）的叶子节点上。这就要求同一个叶子节點内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节點和位置如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）

如果表使用自增主键，那么每次插入新的记录记录就会顺序添加到当前mysql索引排序算法节点的后续位置，当一页写满就会自动开辟一个新的页。如下图所示：

这样就会形成一个紧凑的mysql索引排序算法结构近似顺序填满。由于每次插入时也不需要移动已有数据因此效率很高，也不会增加很多开销在维护mysql索引排序算法上

如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机因此每次新纪录都要被插到现有mysql索引排序算法页得中间某个位置：

此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉此时又要从磁盘上读囙来，这增加了很多开销同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的mysql索引排序算法结构后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

因此只要可以，请尽量在InnoDB上采用自增字段做主键

这篇文章断断续续写了半个月，主要内容就是上面这些了不可否认，这篇文章在一定程度上有纸上谈兵之嫌因为我本人对MySQL的使用属于菜鸟级别，更没有太多数据库调优的经验在这里大谈数据库mysql索引排序算法调优有点大言不惭。就当是我个人的一篇学习笔记了

其实数据库mysql索引排序算法调优是一项技术活，不能仅仅靠理论因为实際情况千变万化，而且MySQL本身存在很复杂的机制如查询优化策略和各种引擎的实现差异等都会使情况变得更加复杂。但同时这些理论是mysql索引排序算法调优的基础只有在明白理论的基础上，才能对调优策略进行合理推断并了解其背后的机制然后结合实践中不断的实验和摸索，从而真正达到高效使用MySQLmysql索引排序算法的目的

另外，MySQLmysql索引排序算法及其优化涵盖范围非常广本文只是涉及到其中一部分。如与排序（ORDER BY）相关的mysql索引排序算法优化及覆盖mysql索引排序算法（Covering index）的话题本文并未涉及同时除B-Treemysql索引排序算法外MySQL还根据不同引擎支持的哈希mysql索引排序算法、全文mysql索引排序算法等等本文也并未涉及。如果有机会希望再对本文未涉及的部分进行补充吧。

[3] 姜承尧 著；MySQL技术内幕-InnoDB存储引擎；机械工业出版社2011