关于mysql:MySQL索引原理及实战

摘要

本文以 MySQL 数据库为钻研对象，探讨与数据库索引相干的一些话题。特地须要阐明的是，MySQL 反对诸多存储引擎，而各种存储引擎对索引的反对也各不相同，因而 MySQL 数据库反对多种索引类型，如 BTree 索引，哈希索引，全文索引等等。为了防止凌乱，本文将只关注于 BTree 索引，因为这是平时应用 MySQL 时次要打交道的索引，至于哈希索引和全文索引本文暂不探讨。

第一局部次要从数据结构及算法实践层面探讨 MySQL 数据库索引的数理根底。

第二局部联合 MySQL 数据库中 MyISAM 和 InnoDB 数据存储引擎中索引的架构实现探讨汇集索引、非汇集索引及笼罩索引等话题。

第三局部解说 Explain 命令的应用标准

第四局部探讨 MySQL 中高性能应用索引的策略。

数据结构与算法根底

索引的实质

MySQL 官网对索引的定义为：索引（Index）是帮忙 MySQL 高效获取数据的数据结构。提取句子骨干，就能够失去索引的实质：索引是数据结构。

咱们晓得，数据库查问是数据库的最次要性能之一。咱们都心愿查问数据的速度能尽可能的快，因而数据库系统的设计者会从查问算法的角度进行优化。最根本的查问算法当然是程序查找（linear search），这种复杂度为 O(n)的算法在数据量很大时显然是蹩脚的，好在计算机科学的倒退提供了很多更优良的查找算法，例如二分查找（binary search）、二叉树查找（binary tree search）等。如果略微剖析一下会发现，每种查找算法都只能利用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能利用于二叉查找树上，然而数据自身的组织构造不可能齐全满足各种数据结构（例如，实践上不可能同时将两列都按程序进行组织），所以，在数据之外，数据库系统还保护着满足特定查找算法的数据结构，这些数据结构以某种形式援用（指向）数据，这样就能够在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

看一个例子：

图 1 展现了一种可能的索引形式。右边是数据表，一共有两列七条记录，最右边的是数据记录的物理地址（留神逻辑上相邻的记录在磁盘上也并不是肯定物理相邻的）。为了放慢 Col2 的查找，能够保护一个左边所示的二叉查找树，每个节点别离蕴含索引键值和一个指向对应数据记录物理地址的指针，这样就能够使用二叉查找在 O(log2n)的复杂度内获取到相应数据。

尽管这是一个货真价实的索引，然而理论的数据库系统简直没有应用二叉查找树或其进化种类红黑树（red-black tree）实现的，起因会在下文介绍。

B-Tree 和 B +Tree

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

B-Tree

B 树的定义(维基百科)

依据 Knuth 的定义，一个 m 阶的 B 树是一个有以下属性的树：

1. 每一个节点最多有 m 个子节点
2. 每一个非叶子节点（除根节点）起码有 ⌈m/2⌉ 个子节点
3. 如果根节点不是叶子节点，那么它至多有两个子节点
4. 有 k 个子节点的非叶子节点领有 k − 1 个键
5. 所有的叶子节点都在同一层

每一个外部节点的键将节点的子树离开。例如，如果一个外部节点有 3 个子节点（子树），那么它就必须有两个键：a1 和 a2。右边子树的所有值都必须小于 a1，两头子树的所有值都必须在 a1 和 a2 之间，左边子树的所有值都必须大于 a2。

外部节点

外部节点是除叶子节点和根节点之外的所有节点。它们通常被示意为一组有序的元素和指向子节点的指针。每一个外部节点领有最多 U 个，起码 L 个子节点。元素的数量总是比子节点指针的数量少一（元素的数量在 L-1 和 U-1 之间）。U 必须等于 2L 或者 2L-1; 因而，每一个外部节点都至多是半满的。U 和 L 之间的关系意味着两个半满的节点能够合并成一个非法的节点，一个全满的节点能够被决裂成两个非法的节点（如果父节点有空间包容移来的一个元素）。这些个性使得在 B 树中删除或插入新的值时能够调整树来放弃 B 树的性质。

根节点

根节点领有的子节点数量的下限和外部节点雷同，然而没有上限。例如，当整个树中的元素数量小于 L-1 时，根节点是惟一的节点并且没有任何子节点。

叶子节点

叶子节点对元素的数量有雷同的限度，然而没有子节点，也没有指向子节点的指针。
一个深度为 n +1 的 B 树能够包容的元素数量大概是深度为 n 的 B 树的 U 倍，然而搜寻、插入和删除操作的开销也会减少。和其余的均衡树一样，这一开销减少的速度远远慢于元素数量的减少。

一些均衡树只在叶子节点中存储值，而且叶子节点和外部节点应用不同的构造。B 树在每一个节点中都存储值，所有的节点有着雷同的构造。然而，因为叶子节点没有子节点，所以能够通过应用专门的构造来进步 B 树的性能。

插入示例：

动态效果参考：
https://www.cs.usfca.edu/~galles/visualization/BTree.html

B+ 树

B-Tree 有许多变种，其中最常见的是 B +Tree，例如 MySQL 就广泛应用 B +Tree 实现其索引构造。

与 B -Tree 相比，B+Tree 有以下不同点：

1. B+ 跟 B 树不同 B + 树的非叶子节点不保留关键字记录的指针，只进行数据索引，这样使得 B + 树每个非叶子节点所能保留的关键字大大增加
2. B+ 树叶子节点保留了父节点的所有关键字记录的指针，所有数据地址必须要到叶子节点能力获取到。所以每次数据查问的次数都一样；
3. B+ 树叶子节点的关键字从小到大有序排列，右边结尾数据都会保留左边节点开始数据的指针。
4. 非叶子节点的子节点数 = 要害字数（起源百度百科）（依据各种材料 这里有两种算法的实现形式，另一种为非叶节点的要害字数 = 子节点数 -1（起源维基百科)，尽管他们数据排列构造不一样，但其原理还是一样的 Mysql 的 B + 树是用第一种形式实现）

B 树绝对于 B + 树的长处是，如果常常拜访的数据离根节点很近，而 B 树的非叶子节点自身存有关键字其数据的地址，所以这种数据检索的时候会要比 B + 树快。

为什么应用 B -Tree（B+Tree）

局部性原理与磁盘预读

因为存储介质的个性，磁盘自身存取就比内存慢很多，再加上机械运动消耗，磁盘的存取速度比内存慢很多，因而为了提高效率，要尽量减少磁盘 I /O。为了达到这个目标，磁盘往往不是严格按需读取，而是每次都会预读，即便只须要一个字节，磁盘也会从这个地位开始，程序向后读取肯定长度的数据放入内存。这样做的理论依据是计算机科学中驰名的局部性原理：

当一个数据被用到时，其左近的数据也通常会马上被应用。

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

因为磁盘程序读取的效率很高（不须要寻道工夫，只需很少的旋转工夫），因而对于具备局部性的程序来说，预读能够进步 I / O 效率。

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

B-/+Tree 索引的性能剖析

d 为大于 1 的一个正整数，称为 B -Tree 的度。(下面定义 B -Tree 时，最小子节点数)
h 为一个正整数，称为 B -Tree 的高度。

上文说过个别应用磁盘 I / O 次数评估索引构造的优劣。先从 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 作为索引构造效率是十分高的。

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

上文还说过，B+Tree 更适宜外存索引，起因和内节点出度 d 无关。从下面剖析能够看到，d 越大索引的性能越好，而出度的下限取决于节点内 key 和 data 的大小：

dmax=floor(pagesize/(keysize+datasize+pointsize))

floor 示意向下取整。因为 B +Tree 内节点去掉了 data 域，因而能够领有更大的出度，领有更好的性能。

MySQL 索引实现

在 MySQL 中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现形式是不同的，本文次要探讨 MyISAM 和 InnoDB 两个存储引擎的索引实现形式。

MyISAM 索引实现

MyISAM 引擎应用 B +Tree 作为索引构造，叶节点的 data 域寄存的是数据记录的地址。下图是 MyISAM 索引的原理图：

这里设表一共有三列，假如咱们以 Col1 为主键，则图 8 是一个 MyISAM 表的主索引（Primary key）示意。能够看出 MyISAM 的索引文件仅仅保留数据记录的地址。在 MyISAM 中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求 key 是惟一的，而辅助索引的 key 能够反复。如果咱们在 Col2 上建设一个辅助索引，则此索引的构造如下图所示：

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

MyISAM 的索引形式也叫做“非汇集”的，之所以这么称说是为了与 InnoDB 的汇集索引辨别。

InnoDB 索引实现

尽管 InnoDB 也应用 B +Tree 作为索引构造，但具体实现形式却与 MyISAM 截然不同。

第一个重大区别是 InnoDB 的数据文件自身就是索引文件。从上文晓得，MyISAM 索引文件和数据文件是拆散的，索引文件仅保留数据记录的地址。而在 InnoDB 中，表数据文件自身就是按 B +Tree 组织的一个索引构造，这棵树的叶节点 data 域保留了残缺的数据记录。这个索引的 key 是数据表的主键，因而 InnoDB 表数据文件自身就是主索引。

上图是 InnoDB 主索引（同时也是数据文件）的示意图，能够看到叶节点蕴含了残缺的数据记录。这种索引叫做汇集索引。因为 InnoDB 的数据文件自身要按主键汇集，所以 InnoDB 要求表必须有主键（MyISAM 能够没有），如果没有显式指定，则 MySQL 零碎会主动抉择一个能够惟一标识数据记录的列作为主键，如果不存在这种列，则 MySQL 主动为 InnoDB 表生成一个隐含字段作为主键，这个字段长度为 6 个字节，类型为长整形。

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

这里以英文字符的 ASCII 码作为比拟准则。汇集索引这种实现形式使得按主键的搜寻非常高效，然而辅助索引搜寻须要检索两遍索引：首先检索辅助索引取得主键，而后用主键到主索引中检索取得记录。

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

Explain 执行打算详解

咱们先来看一下当向一台 mysql 发动查问申请时，MySQL 到底做了什么？

MySQL 的优化器有几个重要工作：

1. 抉择最合适的索引；
2. 抉择表扫还是走索引；
3. 抉择表关联程序；
4. 优化 where 子句；
5. 排除治理中无用表；
6. 决定 order by 和 group by 是否走索引；
7. 尝试应用 inner join 替换 outer join；
8. 简化子查问，决定后果缓存；
9. 合并试图；

应用 explain 关键字能够模仿优化器执行 sql 查问语句，从而得悉 MySQL 是如何解决 sql 语句。这就是研发同学本身必须会的技能了，毕竟，没有人比你更懂业务 sql。

+----+-------------+-------+------------+------+---------------+-----+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----+---------+------+------+----------+-------+

id

select 查问的序列号，蕴含一组能够反复的数字，示意查问中执行 sql 语句的程序。个别有三种状况：

• 第一种：id 全副雷同，sql 的执行程序是由上至下；
• 第二种：id 全副不同，sql 的执行程序是依据 id 大的优先执行；
• 第三种：id 既存在雷同，又存在不同的。先依据 id 大的优先执行，再依据雷同 id 从上至下的执行。

select_type

• select 查问的类型，次要是用于区别一般查问，联结查问，嵌套的简单查问
• simple：简略的 select 查问，查问中不蕴含子查问或者 union
• primary：查问中若蕴含任何简单的子查问，最外层查问则被标记为 primary
• subquery：在 select 或 where 列表中蕴含了子查问
• derived：在 from 列表中蕴含的子查问被标记为 derived（衍生）MySQL 会递归执行这些子查问，把后果放在长期表里。
• union：若第二个 select 呈现在 union 之后，则被标记为 union，若 union 蕴含在 from 子句的子查问中，外层 select 将被标记为：derived
• union result：从 union 表获取后果的 select

partitions

表所应用的分区，如果要统计十年公司订单的金额，能够把数据分为十个区，每一年代表一个区。这样能够大大的进步查问效率。

type(*)

这是一个十分重要的参数，连贯类型，常见的有：all , index , range , ref , eq_ref , const , system , null 八个级别。

性能从最优到最差的排序：system > const > eq_ref > ref > range > index > all

对 java 程序员来说，若保障查问至多达到 range 级别或者最好能达到 ref 则算是一个优良而又负责的程序员。

• all：（full table scan）全表扫描无疑是最差，若是百万千万级数据量，全表扫描会十分慢。
• index：（full index scan）全索引文件扫描比 all 好很多，毕竟从索引树中找数据，比从全表中找数据要快。
• range：只检索给定范畴的行，应用索引来匹配行。范畴放大了，当然比全表扫描和全索引文件扫描要快。sql 语句中个别会有 between，in，>，< 等查问。
• ref：非唯一性索引扫描，实质上也是一种索引拜访，返回所有匹配某个独自值的行。比方查问公司所有属于研发团队的共事，匹配的后果是多个并非惟一值。
• const：示意通过索引一次就能够找到，const 用于比拟 primary key 或者 unique 索引。因为只匹配一行数据，所以很快，若将主键置于 where 列表中，MySQL 就能将该查问转换为一个常量。
• system: 表只有一条记录（等于零碎表），这是 const 类型的特列，平时不会呈现，理解即可。

possible_keys

显示查问语句可能用到的索引(一个或多个或为 null)，不肯定被查问理论应用。仅供参考应用。

key(*)

显示查问语句理论应用的索引。若为 null，则示意没有应用索引。有时候，应用的索引和预期的不太统一，那么就要认真钻研一下了。

key_len

显示索引中应用的字节数，可通过 key_len 计算查问中应用的索引长度。在不损失精确性的状况下索引长度越短越好。key_len 显示的值为索引字段的最可能长度，并非理论应用长度，即 key_len 是依据表定义计算而得，并不是通过表内检索出的。

ref

显示索引的哪一列或常量被用于查找索引列上的值。

rows(*)

依据表统计信息及索引选用状况，大抵估算出找到所需的记录所须要读取的行数，值越大越不好。

extra(*)

• Using filesort：阐明 MySQL 会对数据应用一个内部的索引排序，而不是依照表内的索引程序进行读取。MySQL 中无奈利用索引实现的排序操作称为“文件排序”。呈现这个就要立即优化 sql。
• Using temporary：应用了长期表保留两头后果，MySQL 在对查问后果排序时应用长期表。常见于排序 order by 和 分组查问 group by。呈现这个更要立即优化 sql。

• Using index：示意相应的 select 操作中应用了笼罩索引（Covering index），防止拜访了表的数据行，成果不错！如果同时呈现 Using where，表明索引被用来执行索引键值的查找。如果没有同时呈现 Using where，示意索引用来读取数据而非执行查找动作。

  • 笼罩索引（Covering Index）：也叫索引笼罩，就是 select 的数据列只用从索引中就可能获得，不用读取数据行，MySQL 能够利用索引返回 select 列表中的字段，而不用依据索引再次读取数据文件。
• Using index condition：在 5.6 版本后退出的新个性，优化器会在索引存在的状况下，通过合乎 RANGE 范畴的条数 和 总数的比例来抉择是应用索引还是进行全表遍历。
• Using where：表明应用了 where 过滤
• Using join buffer：表明应用了连贯缓存
• impossible where：where 语句的值总是 false，不可用，不能用来获取任何元素
• distinct：优化 distinct 操作，在找到第一匹配的元组后即进行找同样值的动作。

filtered

一个百分比的值，和 rows 列的值一起应用，能够预计出查问执行打算 (QEP) 中的前一个表的后果集，从而确定 join 操作的循环次数。小表驱动大表，加重连贯的次数。

通过 explain 的参数介绍，咱们能够得悉：

1. 表的读取程序(id)
2. 数据读取操作的操作类型(type)
3. 哪些索引被理论应用(key)
4. 表之间的援用(ref)
5. 每张表有多少行被优化器查问(rows)

索引应用策略及优化

索引的长处：

1. 索引大大减少了服务器须要扫描的数据量。
2. 索引能够帮忙服务器防止排序和长期表。
3. 索引能够将随机 I /O 变为程序 I /O。

好索引的规范（Lahdenmaki three-star system）：

1. 索引将相干记录放在一起
2. 索引中的数据程序和查找中的程序统一
3. 索引中的列蕴含了查问中须要的全部列(笼罩索引)

示例数据库

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

参考地址：https://dev.mysql.com/doc/emp…，依据指南下载文件，load 数据即可。

最左前缀原理与相干优化

高效应用索引的首要条件是晓得什么样的查问会应用到索引，这个问题和 B +Tree 中的“最左前缀原理”无关，上面通过例子阐明最左前缀原理。

这里先说一下联结索引 (多列索引) 的概念。在上文中，咱们都是假如索引只援用了单个的列，实际上，MySQL 中的索引能够以肯定程序援用多个列，这种索引叫做联结索引，个别的，一个联结索引是一个有序元组 <a1, a2, …, an>，其中各个元素均为数据表的一列，实际上要严格定义索引须要用到关系代数，然而这里我不想探讨太多关系代数的话题，因为那样会显得很干燥，所以这里就不再做严格定义。另外，单列索引能够看成联结索引元素数为 1 的特例。

如这个表：

CREATE TABLE users (last_name  varchar(50)           not null,
    first_name varchar(50)           not null,
    dob        date                  not null,
    gender     enum('male,'female') not null,
    key(last_name, first_name, dob)
)

多列索引构造如图：

以 employees.titles 表为例，上面先查看其上都有哪些索引：

SHOW INDEX FROM employees.titles;
+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Null | Index_type |
+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+
| 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 表的主索引为 <emp_no, title, from_date>。

1. 全列匹配

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 |       |
+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+

成果是一样的。

2. 最左前缀匹配

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 |       |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+

当查问条件准确匹配索引的右边间断一个或几个列时，如 <emp_no> 或 <emp_no, title>，所以能够被用到，然而只能用到一部分，即条件所组成的最左前缀。下面的查问从剖析后果看用到了 PRIMARY 索引，然而 key_len 为 4，阐明只用到了索引的第一列前缀。

3. 查问条件用到了索引中列的准确匹配，然而两头某个条件未提供

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 未提供，所以查问只用到了索引的第一列，而前面的 from_date 尽管也在索引中，然而因为 title 不存在而无奈和左前缀连贯，因而须要对后果进行扫描过滤 from_date（这里因为 emp_no 惟一，所以不存在扫描）。

4. 查问条件没有指定索引第一列

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 |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

5. 匹配某列的前缀字符串

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 |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

此时能够用到索引, 如果通配符 % 不呈现在结尾，则能够用到索引。

6. 范畴查问

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 |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

7. 查问条件中含有函数或表达式

很可怜，如果查问条件中含有函数或表达式，则 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 在运行时也要耗费资源保护索引，因而索引并不是越多越好。个别两种状况下不倡议建索引。

第一种状况是表记录比拟少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查问做全表扫描就好了。

另一种不倡议建索引的状况是索引的选择性较低。所谓索引的选择性（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 表为例介绍前缀索引的抉择和应用。

从图 12 能够看到 employees 表只有一个索引 <emp_no>，那么如果咱们想按名字搜寻一个人，就只能全表扫描了：

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));

此时再执行一遍按名字查问，比拟剖析一下与建索引前的后果(开启 profiles: set profiles = true;)：

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' |
+----------+------------+---------------------------------------------------------------------------------+

前缀索引兼顾索引大小和查问速度，然而其毛病是不能用于 ORDER BY 和 GROUP BY 操作，也不能用于 Covering index。

InnoDB 的主键抉择与插入优化

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

常常看到有帖子或博客探讨主键抉择问题，有人倡议应用业务无关的自增主键，有人感觉没有必要，齐全能够应用如学号或身份证号这种惟一字段作为主键。不管反对哪种论点，大多数论据都是业务层面的。如果从数据库索引优化角度看，应用 InnoDB 引擎而不应用自增主键相对是一个蹩脚的主见。

上文探讨过 InnoDB 的索引实现，InnoDB 应用汇集索引，数据记录自身被存于主索引（一颗 B +Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键程序寄存，因而每当有一条新的记录插入时，MySQL 会依据其主键将其插入适当的节点和地位，如果页面达到装载因子（InnoDB 默认为 15/16），则开拓一个新的页（节点）。

如果表应用自增主键，那么每次插入新的记录，记录就会程序增加到以后索引节点的后续地位，当一页写满，就会主动开拓一个新的页。如下图所示：

这样就会造成一个紧凑的索引构造，近似程序填满。因为每次插入时也不须要挪动已有数据，因而效率很高，也不会减少很多开销在保护索引上。

如果应用非自增主键（如果身份证号或学号等），因为每次插入主键的值近似于随机，因而每次新纪录都要被插到现有索引页的两头某个地位：

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

因而，只有能够，请尽量在 InnoDB 上采纳自增字段做主键。

InnoDB 中，删除数据，InnoDB 只是把被删除数据标记为已删除状态，后续这个地位能够插入新的数据，然而磁盘文件大小并不会变动。
只能通过重建表来解决：

alter table A engine=InnoDB;

冗余和反复的索引

反复索引是指在雷同的列上依照雷同的程序创立的雷同类型的索引。应该防止这样创立反复索引，发现当前也应该立刻革除。

如：

CREATE TABLE test(
    ID INT NOT NULL PRIMARY KEY,
    A INT NOT NULL,
    UNIQUE(ID),
    INDEX(ID)
) ENGINE=InnoDB;

MySQL 的惟一限度和主键限度都是通过索引实现的，只须要创立一个即可。

冗余索引：如果创立了索引 <A, B>，再创立索引 A 就是冗余索引，因为这只是前一个索引的前缀索引。
因而索引 <A,B> 也能够当成索引 A 来应用，如果再创立索引 <B, A>，则不是冗余索引。
大多数状况下不须要冗余索引，然而有时候为了性能可能也会创立冗余索引。

总结

本文只是对 MySQL 中 InnoDB 引擎的索引机制做了着重介绍，前面的实际局部也是绝对比较简单的局部，对于简单的 SQL，要可能抽丝剥茧，简化问题，B+ 树在心中，索引并不可怕。

参考文献：

1. http://blog.codinglabs.org/articles/theory-of-mysql-index.html (大部分都是拷贝自这篇文章，感激张洋的辛苦整顿)
2. https://zh.wikipedia.org/wiki/ B 树
3. http://www.cnblogs.com/itdragon/p/8146439.html
4. Baron Scbwartz 等 著，王小东等 译；高性能 MySQL（High Performance MySQL）；电子工业出版社，2010

任何人想要转载我的文章，无需和我分割，请转载后把链接私信贴给我，谢谢！