InnoDB存储引擎索引的数据结构、算法原理和策略优化

最新推荐文章于 2022-11-29 15:31:12 发布

LiZhen798

最新推荐文章于 2022-11-29 15:31:12 发布

阅读量630

点赞数

分类专栏：数据库知识文章标签： mysql 数据库

本文链接：https://blog.csdn.net/LiZhen314/article/details/114373508

版权

数据库知识专栏收录该内容

39 篇文章 2 订阅

订阅专栏

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

什么是索引

MySQL官方定义：

Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. This is much faster than reading every row sequentially.

索引（Index）的本质是为了避免查询时全表扫描、而额外辅助构造的数据结构，使用索引可以提升查询数据的效率。数据的select是MySQL最重要核心的功能之一，我们都希望查询速度尽可能的快。如果你的库表不大，可以接受线性的顺序查找（linear search），这种复杂度为O(n)的算法在数据量很大时显然是很糟糕的。如果你可以接受，那就不需要在数据库表上面加任何索引。当然实际线上的生产环境几乎不存在这种情况，所以我们需要时间复杂度更优的查找算法，例如二分查找（binary search）、二叉树查找（binary tree search）等。每种查找算法一般都对应着一类存储的数据结构，例如二分查找在数据有序时效率最高，二叉树查找在平衡树上有更好的深度效率。

所以对于MySQL来说在数据之外，还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用或指向存储的数据，然后在这些数据结构上实现查找算法。MySQL索引本质就是这些数据结构+算法。

大结局

既然知道了索引的本质，那我们不妨做一次思维跳跃，从文章一开始就让读者看一看InnoDB索引的全貌：

存储的数据结构：

查询的算法过程：

通过对整棵B+树的“二分查找”，定位到具体的Page。
在一个Page内“二分查找”，使用Page中的Slot，定位到Record。
比较定位到的Record，确认是否是需要查询的Record。
结束

一个具体的索引例子

对于InnoDB不清楚的同学，上面的描述可能过于简单了，我们来看一个具体的例子：

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

虽然这是一个可实现的索引可以用来加速查询，但是实际的数据库系统几乎没有使用如此简单结构的索引。

索引的数据结构和算法

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构，MySQL-InnoDB也使用B+Tree作为索引的存储结构。接下来二个小节会先温习一下B-Tree和B+Tree的结构和基本操作。如果对这一块非常熟知的读者可以选择跳过。

B-Tree数据结构

在计算机科学中，B树（英语：B-tree）是一种自平衡的树，能够保持数据有序。这种数据结构能够让查找数据、顺序访问、插入数据及删除的动作，都在对数时间内完成。B树，概括来说是一个一般化的二叉查找树（binary search tree）一个节点可以拥有最少2个子节点。

B树中每一个内部节点会包含一定数量的键，键将节点的子树分开。例如，如果一个内部节点有3个子节点（子树），那么它就必须有两个键： a1 和 a2 。左边子树的所有值都必须小于 a1 ，中间子树的所有值都必须在 a1 和a2 之间，右边子树的所有值都必须大于 a2 。通常，键的数量被选定在 $d$ 和 $2d$ 之间。其中 $d$ 是键的最小数量，是树最小的度或分支因子。

一个B树的结构可以如下图所示

算法

搜索

B树的搜索和二叉搜索树类似。从根节点开始，从上到下递归的遍历树。在每一层上，搜索的范围被减小到包含了搜索值的子树中。子树值的范围被它的父节点的键确定。

插入

所有的插入都从根节点开始。要插入一个新的元素，首先搜索这棵树找到新元素应该被添加到的叶子节点。将新元素插入到这一节点中的步骤如下：

如果节点拥有的元素数量小于最大值，那么有空间容纳新的元素。将新元素插入到这一节点，且保持节点中元素有序。
否则的话这一节点已经满了，将它平均地分裂成两个节点：
1. 从叶子节点的元素和新的元素中选择出中位数
2. 小于这一中位数的元素放入左边节点，大于这一中位数的元素放入右边节点，中位数作为分隔值。
3. 分隔值被插入到父节点中，这可能会造成父节点分裂，分裂父节点时可能又会使它的父节点分裂，以此类推。如果没有父节点（这一节点是根节点），就创建一个新的根节点（增加了树的高度）。

如果分裂一直上升到根节点，那么一个新的根节点会被创建，它有一个分隔值和两个子节点。这就是根节点并不像内部节点一样有最少子节点数量限制的原因。每个节点中元素的最大数量是 U-1。当一个节点分裂时，一个元素被移动到它的父节点，但是一个新的元素增加了进来。所以最大的元素数量 U-1 必须能够被分成两个合法的节点。如果 U-1 是奇数，那么 U=2L ，总共有 2L-1 个元素，一个新的节点有 L-1 个元素，另外一个有 L 个元素，都是合法的节点。如果 U-1 是偶数，那么 U=2L-1,总共有 2L-2 个元素。一半是 L-1，正好是节点允许的最小元素数量。

B树插入的例子。节点最多有3个孩子

删除

有两种常用的删除策略

定位并删除元素，然后调整树使它满足约束条件；或者
从上到下处理这棵树，在进入一个节点之前，调整树使得之后一旦遇到了要删除的键，它可以被直接删除而不需要再进行调整

具体的删除过程有兴趣的同学可以参考B树删除算法

B+ Tree数据结构

B+树是B树的一种变体。狭义上，一个Ｂ树在它内部节点中存储键值，但不需在叶子节点上存储这些键值的记录。在Ｂ+树，这些键值的拷贝被存储在内部节点；键值和记录存储在叶子节点；另外，一个叶子节点可以包含一个指针，指向另一个叶子节点以加速顺序存取。

一个B+树的结构可以如图所示（把键1-7连接到值 d1-d7 的B+树。链表用于快速顺序遍历叶子节点。树的分叉因子为4。）

算法

查找

查找以典型的方式进行，类似于二叉查找树。起始于根节点，自顶向下遍历树，选择其分离值在要查找值的任意一边的子指针。在节点内部典型的使用是二分查找来确定这个位置。

插入

首先，查找要插入其中的节点的位置。接着把值插入这个节点中。
如果没有节点处于违规状态则处理结束。
如果某个节点有过多元素，则把它分裂为两个节点，每个都有最小数目的元素。在树上递归向上继续这个处理直到到达根节点，如果根节点被分裂，则创建一个新根节点。为了使它工作，元素的最小和最大数目典型的必须选择为使最小数不小于最大数的一半。

删除

首先，查找要删除的值。接着从包含它的节点中删除这个值。
如果没有节点处于违规状态则处理结束。
如果节点处于违规状态则有两种可能情况：
1. 它的兄弟节点，就是同一个父节点的子节点，可以把一个或多个它的子节点转移到当前节点，而把它返回为合法状态。如果是这样，在更改父节点和两个兄弟节点的分离值之后处理结束。
2. 它的兄弟节点由于处在低边界上而没有额外的子节点。在这种情况下把两个兄弟节点合并到一个单一的节点中，而且我们递归到父节点上，因为它被删除了一个子节点。持续这个处理直到当前节点是合法状态或者到达根节点，在其上根节点的子节点被合并而且合并后的节点成为新的根节点。

MySQL索引的数据结构是B+树

在实际线上生产环境中，MySQL存储的数据量和索引是很大的，总体积经常可以达到TB级别，即使是金融分片式数据库单分片也常常能达到1-2TB大小。所以按照目前常见的服务器架构和配置来说，把这些索引和数据全部存在内存中是不现实的，因此索引和数据都以文件形式存储在磁盘上。

因此索引查询的过程中就伴随着磁盘I/O的过程，查询的数据量越大消耗的I/O操作就越多。相对于内存读写，磁盘I/O的消耗要高好几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。索引优化的目标是结构组织要：

1）尽量减少查找过程中磁盘I/O的读写次数

2）尽量使用顺序读写的方式提升磁盘I/O的读写效率

局部性原理与磁盘预读

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

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

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

即使是目前广泛使用的SSD硬盘，因为顺序读写操作减少通道冲突，以及碎片回收的复杂度，相较于随机的少量数据读写，速度也是要快上不少的。如果是机械硬盘，由于随机读写要不断机械旋转、寻道，速度更是要差上一个量级。因为顺序的预读可以有效提高I/O效率。

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

数据库系统巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B- Tree还需要使用如下技巧：

每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。
B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O（h）=O（logmN）。一般实际应用中，m是非常大的数字，通常超过100，因此h非常小（通常不超过3）。

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

HASH索引

B树的查询时间复杂度和树高n有关为O(log n)，如果使用hash结构索引查询时间复杂度可以达到O(1)，效率远超B树。但为什么MySQL索引不采用hash方式呢？这和业务场景有关，如果只查找一个值的话hash是一个很好的选择，例如redis的key、value就采用了hash存储。

但MySQL数据库更多应用于关系型数据，存储和查询的结构往往是范围性的条数较多，这时候由于B+树索引有序，并且又有链表相连，在顺序性的范围查找时询效率比hash就快很多了。而且数据库中的索引一般是在磁盘上，数据量大的情况可能无法一次装入内存，B+树的设计可以允许数据分批加载，同时树的高度较低提高查找效率。

二叉排序树

同一高度下左边跟节点小，右边跟节点大，并且左右子树都是二叉排序的。如果使用二叉排序树作为索引结构，在极端情况下插入的是有序的序列就会变成链表，导致查询变成线性效率降低。所以我们需要对二叉树进行平衡，让插入的时候节点能均匀分布，降低树高。

红黑树

红黑树也是一种非常优秀的平衡二叉树结构，在内存中不考虑到多次寻址的情况它比B树的搜索效率更加优秀。关于红黑树的结构特性和操作在此不再赘述，有兴趣的读者可自行查找资料。红黑树随着数据量的变大树高也会随之增加，这一点对于存储在文件内的索引就带来了读写效率问题。

假设一张库表有100万+的数据，根据2^N计算红黑树的高度h会在20左右。如果将索引数据存储在叶子节点，那么每读取一条索引数据都需要经过20次的磁盘IO操作，这样的IO效率显然是不能接受的。

B-Tree和B+Tree的性能优势

上文已详细介绍了这二种存储结构，这里我们直接分析：

B+树只有叶节点存放数据，其余节点用来索引，而B-树是每个索引节点都会有Data域。
B+树所有的Data域在叶子节点，并且所有叶子结点之间都有指针，这样遍历叶子结点就能获得全部数据。这样就极大的提升了排序区间操作，在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作。
B树可以有多个子节点，从几十到上千，可以降低树的高度，极大减少IO次数提升读写效率。

举个例子：我们假设一行的数据是1K，按一页16KB大小计算，那一页能存储16行数据，也就是一个叶节点可以存储16条记录。再来看非叶节点，假设ID是bigint类型，那么长度为8B，指针大小在InnoDB源码中为64（6B），一共就是14B，那么一页里面就可以存储16K/14=1170个（主键+指针）

那么一颗高度为2的B+树能存储的数据为：1170*16=18720条，一颗高度为3的B+树可以存储1170*1170*16=21902400（千万条）。只要3层的树高就可以存储千万级别的表记录了。

B+树的查找效率比B-树高，原因是因为B+树把数据放在叶子节点，所以就可以增加更多的非叶子节点数。而B-树节点和数据是保存在一起的，所以非叶节点的节点数要比B+树少，树的高度就比B+树高从而导致查询效率不如B+树。

聚簇索引和非聚簇索引

聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据

非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

注：某些文献中也将族簇索引称之为聚集索引，含义一致

聚簇索引

一个表中只能拥有一个聚簇索引，聚簇索引决定数据在物理磁盘上的物理排序，如果定义了主键，那么InnoDB会通过主键来聚集数据，如果没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有唯一的非空索引，InnoDB会隐式定义一个主键来作为聚簇索引。聚簇索引可以很大程度的提高访问速度，因为聚簇索引将索引和行数据保存在了同一个B+Tree中，所以找到了索引也就相应的找到了对应的行数据。但在使用聚簇索引的时候需注意避免随机的聚簇索引（一般指主键值不连续，且分布范围不均匀），如使用UUID来作为聚簇索引性能会很差，因为UUID值的不连续会导致增加很多的索引碎片和随机I/O，最终导致查询的性能急剧下降。在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

下图是InnoDB主索引（同时也是数据文件）的示意图：

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

注意：聚簇索引并不是在物理存储上是连续的，其只是在逻辑上连续，这其中：

数据页是按照主键的顺序并通过双向链表链接的，因此物理存储上可以不按主键顺序存储。
数据页中的记录也是通过双向链表进行维护的，物理存储上同样可以不按主键顺序存储。

聚簇索引的好处：

对于主键的排序查找非常的快，因为其叶子节点是用双向链表链接的。
对于主键的范围查找非常的快，因为通过叶子节点的上层中间节点，就可以得到叶结点的范围值。

非聚簇索引

在innodb中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找。

一个表中可以拥有多个非聚簇索引。非聚簇索引并不决定数据在磁盘上的物理排序，且在B-Tree中包含索引但不包含行数据，行数据只是通过保存在B-Tree中的索引对应的指针来指向行数据，如：在普通字段（user_name，city, age）上建立的索引就是非聚簇索引。

下面的表总结了何时使用聚簇索引或非聚簇索引：

动作描述	使用聚簇索引	使用非聚簇索引
列经常被分组排序	应	应
返回某范围内的数据	应	不应
一个或极少不同值	不应	不应
小数目的不同值	应	不应
大数目的不同值	不应	应
频繁更新的列	不应	应
外键列	应	应
主键列	应	应
频繁修改索引列	不应	应

联合(组合)索引

组合索引的本质也是一棵B+树，和普通的非聚簇索引一样在叶子节点存储数据行的主键。例如联合索引(col1, col2,col3)，是一棵以最左边的列col1为关键字建立的B+索引树。其非叶子节点存储的是第一个关键字col1的索引，而叶节点存储的则是三个关键字col1、col2、col3三个关键字的数据，且按照col1、col2、col3的顺序进行排序。如下图标示：

所以联合索引的最左生效原理就非常好理解了，因为联合索引只按照最左列建立索引树，所以只有最左列的索引才会在查询中生效。当然其它列也是有加速意义的，在确定检索范围后，对于排好序的组合数据可以快速的批量拉取。

Key和Index

相信有部分读者对于MySQL的Key和Index区别会有些疑惑，当纯粹用作索引时它们并没有本质的区别。Key是数据库的物理结构，它包含两层意义，一是约束（偏重于约束和规范数据库的结构完整性），二是索引（辅助查询用的）。常见的key如 primary key, unique key, foreign key 等。

primary key ：有两个作用，一是约束作用（constraint），用来规范一个存储主键和唯一性，但同时也在此key上建立了一个index。
unique key ：也有两个作用，一是约束作用（constraint），规范数据的唯一性，但同时也在这个key上建立了一个index。
foreign key：也有两个作用，一是约束作用（constraint），规范数据的引用完整性，但同时也在这个key上建立了一个index。

MySQL中的key是同时具有constraint和index的意义。
MySQL requires every Key also be indexed, that's an implementation detail specific to MySQL to improve performance.

Index也是数据库的物理结构，但它只是辅助查询的，它创建时会在另外的表空间（MySQL中的innodb表空间）以一个类似目录的结构存储。因此索引只是索引，它不会去约束索引的字段的行为。总结起来说：

约束有主键约束、唯一约束、外键约束、非空约束、检查约束（在MySQL中压根无法使用检查约束，但可以正常建立）等
索引有普通索引、主键索引、唯一索引、联合索引、全文索引等
两者的实现都可以通过建表时实现，建表后的话通过alter语句来实现建立与删除
在理论上是不能将MySQL的key和index划等号的，他们不是一回事，但在实际使用中，他们基本没有区别。

索引使用策略及优化

说明：以下具体的索引策略说明和优化，均摘抄自和翻译自MySQL5.6标准手册。

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

本章节我们会使用具体的SQL来进行说明，先以MySQL官方示例库的employees.titles表为例，下面先查看其上都有哪些索引：

SHOW INDEX FROM employees.titles;

+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+

+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+

| titles | 0 | PRIMARY | 1 | emp_no | A | NULL | | BTREE |

| titles | 0 | PRIMARY | 2 | title | A | NULL | | BTREE |

| titles | 0 | PRIMARY | 3 | from_date | A | 443308 | | BTREE |

| titles | 1 | emp_no | 1 | emp_no | A | 443308 | | BTREE |

+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+

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

ALTER TABLE employees.titles DROP INDEX emp_no;

情况一：全列匹配

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26';

+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+

| 1 | SIMPLE | titles | const | PRIMARY | PRIMARY | 59 | const,const,const | 1 | |

+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+

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

EXPLAIN SELECT * FROM employees.titles WHERE from_date='1986-06-26' AND emp_no='10001' AND title='Senior Engineer';

+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+

| 1 | SIMPLE | titles | const | PRIMARY | PRIMARY | 59 | const,const,const | 1 | |

+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+

情况二：最左前缀匹配

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

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001';

+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+

| 1 | SIMPLE | titles | ref | PRIMARY | PRIMARY | 4 | const | 1 | |

+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+

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

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

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-06-26';

+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+

| 1 | SIMPLE | titles | ref | PRIMARY | PRIMARY | 4 | const | 1 | Using where |

+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+

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

SELECT DISTINCT(title) FROM employees.titles;

+--------------------+

| title |

+--------------------+

| Senior Engineer |

| Staff |

| Engineer |

| Senior Staff |

| Assistant Engineer |

| Technique Leader |

| Manager |

+--------------------+

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

EXPLAIN SELECT * FROM employees.titles

WHERE emp_no='10001'

AND title IN ('Senior Engineer', 'Staff', 'Engineer', 'Senior Staff', 'Assistant Engineer', 'Technique Leader', 'Manager')

AND from_date='1986-06-26';

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

| 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 59 | NULL | 7 | Using where |

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

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

SHOW PROFILES;

+----------+------------+-------------------------------------------------------------------------------+

| Query_ID | Duration | Query |

+----------+------------+-------------------------------------------------------------------------------+

| 10 | 0.00058000 | SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-06-26'|

| 11 | 0.00052500 | SELECT * FROM employees.titles WHERE emp_no='10001' AND title IN ... |

+----------+------------+-------------------------------------------------------------------------------+

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

情况四：查询条件没有指定索引第一列

EXPLAIN SELECT * FROM employees.titles WHERE from_date='1986-06-26';

+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

| 1 | SIMPLE | titles | ALL | NULL | NULL | NULL | NULL | 443308 | Using where |

+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

由于不是最左前缀，索引这样的查询显然用不到索引。

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

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title LIKE 'Senior%';

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

| 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 56 | NULL | 1 | Using where |

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

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

情况六：范围查询

EXPLAIN SELECT * FROM employees.titles WHERE emp_no < '10010' and title='Senior Engineer';

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

| 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 4 | NULL | 16 | Using where |

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

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

EXPLAIN SELECT * FROM employees.titles

WHERE emp_no < '10010'

AND title='Senior Engineer'

AND from_date BETWEEN '1986-01-01' AND '1986-12-31';

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

| 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 4 | NULL | 16 | Using where |

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

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

EXPLAIN SELECT * FROM employees.titles

WHERE emp_no BETWEEN '10001' AND '10010'

AND title='Senior Engineer'

AND from_date BETWEEN '1986-01-01' AND '1986-12-31';

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

| 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 59 | NULL | 16 | Using where |

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

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

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

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

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND left(title, 6)='Senior';

+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+

| 1 | SIMPLE | titles | ref | PRIMARY | PRIMARY | 4 | const | 1 | Using where |

+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+

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

EXPLAIN SELECT * FROM employees.titles WHERE emp_no - 1='10000';

+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

| 1 | SIMPLE | titles | ALL | NULL | NULL | NULL | NULL | 443308 | Using where |

+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

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

索引选择性与前缀索引

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

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

所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：Index Selectivity = Cardinality / #T

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

SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;

+-------------+

| Selectivity |

+-------------+

| 0.0000 |

+-------------+

title的选择性不足0.0001（精确值为0.00001579），所以实在没有什么必要为其单独建索引。有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。下面以employees.employees表为例介绍前缀索引的选择和使用。

EXPLAIN SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';

+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

| 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 300024 | Using where |

+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

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

SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees;

+-------------+

| Selectivity |

+-------------+

| 0.0042 |

+-------------+

SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees;

+-------------+

| Selectivity |

+-------------+

| 0.9313 |

+-------------+

<first_name>显然选择性太低，<first_name, last_name>选择性很好，但是first_name和last_name加起来长度为30，有没有兼顾长度和选择性的办法？可以考虑用first_name和last_name的前几个字符建立索引，例如<first_name, left(last_name, 3)>，看看其选择性：

SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees;

+-------------+

| Selectivity |

+-------------+

| 0.7879 |

+-------------+

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

SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;

+-------------+

| Selectivity |

+-------------+

| 0.9007 |

+-------------+

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

ALTER TABLE employees.employees

ADD INDEX `first_name_last_name4` (first_name, last_name(4));

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

SHOW PROFILES;

+----------+------------+---------------------------------------------------------------------------------+

| Query_ID | Duration | Query |

+----------+------------+---------------------------------------------------------------------------------+

| 87 | 0.11941700 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |

| 90 | 0.00092400 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |

+----------+------------+---------------------------------------------------------------------------------+

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

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

参考文献

[1] Baron Scbwartz等著，王小东等译；高性能MySQL（High Performance MySQL）；电子工业出版社，2010

[2] Michael Kofler 著，杨晓云等译；MySQL5权威指南（The Definitive Guide to MySQL5）；人民邮电出版社，2006

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

[4] D Comer, Ubiquitous B-tree; ACM Computing Surveys (CSUR), 1979

[5] Codd, E. F. (1970). "A relational model of data for large shared data banks". Communications of the ACM, , Vol. 13, No. 6, pp. 377-387

[6] MySQL5.6参考手册 - https://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html

LiZhen798

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
2
评论
InnoDB存储引擎索引的数据结构、算法原理和策略优化

什么是索引大结局一个具体的索引例子索引的数据结构和算法 B-Tree数据结构算法 B+ Tree数据结构算法 MySQL索引的数据结构是B+树局部性原理与磁盘预读 HASH索引二叉排序树红黑树 B-Tree和B+Tree的性能优势聚簇索引和非聚簇索引聚簇索引非聚簇索引联合(组合)索引 Key和Index 索引使用策略及优化情况一：全列匹配情况二：最左前缀
复制链接

扫一扫