索引是应用程序设计和开发的一个重要方面。若索引太多，应用程序的性能可能会收到影响。而索引太少，对查询性能又会产生影响。要找到一个合适的平衡点，这对应用程序的性能至关重要。
一、InnoDB 存储引擎索引概述
InnoDB 存储引擎支持以下几种常见的索引：
- B+树索引
- 全文索引
- 哈希索引
哈希索引是自适应的，InnoDB 会根据标的使用情况自动为表生成哈希索引，不能人为干预是否在一张表中生成哈希索引。
B+树索引并不能找到一个给定键值的具体行。能找到的只是被查找数据行所在的页。然后数据库通过把页读入内存，再在内存中进行查找，最后得到想要的数据。
二、数据结构与算法
2.1 二分查找法
二分查找法也称为折半查找法，用来查找一组有序的记录数据中心的某一记录。基本思想是：将记录按有序化排列，在查找中采用跳跃式方式查找，即先以中点位置为比较对象，如果要找的元素值小于中点元素，则将待查序列缩小为左半部分，否则为右半部分，通过一次比较，将查找区间缩小一半。
每页 Page Directory中的槽是按照主键的顺序存放的，对于某一条具体记录的查询是通过对Page Directory 进行二分查找得到的。
2.2 二叉查找树和平衡二叉树
2.2.1 二叉查找树
在二叉查找树中，左子树的键值总是小于根的键值，右子树的键值总是大于根的键值。因此可以通过中序遍历可以得到键值的排序输出。
二叉查找树可以任意地构造，同样是2、3、5、6、7、8这五个数字，也可以按如下方式简历二叉查找树。
显然这颗二叉查找树效率就低了。因此若想最大性能地构造一颗二叉查找树，需要这颗二叉查找树是平衡的，从而引出了新的定义——平衡二叉树（AVL树）
2.2.2 平衡二叉树
平衡二叉树的定义如下：
- 符合二叉查找树的定义
- 必须满足任何节点的两个子树的高度最大差为1
平衡二叉树的查询速度很快，但是维护一颗平衡二叉树的代价非常大。通常来说，需要1次或多次左旋和右旋来得到插入或更新后树的平衡性。下图展示一次旋转及多次旋转的情况。
2.2.3 B+树
B+树是通过二叉查找树、平衡二叉树、B树演化而来。
B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。
2.2.4 B+树索引
B+树索引的本质就是B+树在数据库中的实现，但是B+树索引在数据库中有一个特点是高扇出性。因此B+树的高度一般在2-4层，也就是说查找某一键值的行记录时最多只需要2-4次IO。当前一般的机械磁盘每秒至少可以做100次IO，2~4次的IO意味着查询时间只需0.02-0.04秒。
B+树索引可以分为 聚集索引 和 辅助索引。
无论哪种索引，叶子节点存放着所有的数据。聚集索引与辅助索引不同的是，叶子节点存放的是否是一整行的信息。
聚集索引
聚集索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。每个数据页都通过一个双向链表来进行链接。
由于实际的数据页只能按照一颗B+树进行排序，因此每张表只能拥有一个聚集索引。在多数情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外，由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询，查询优化器能够快速发现某一段范围的数据页需要扫描。
辅助索引
对于辅助索引（非聚集索引），叶子节点不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了相应行数据的聚集索引键，用来告诉存储引擎哪里可以找到与索引对应的行数据。
辅助索引的存在不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB 会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的记录。
三、B+树索引的使用
3.1 联合索引
联合索引是指对表上的多个列进行索引。从本质上来说，联合索引也是一颗B+树，不同的是联合索引的键值的数量不是1，而是大于等于2。联合索引需遵守最左原则。
3.2 覆盖索引
即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量IO操作。
3.3 优化器选择不适用索引的情况
某些情况下，执行EXPLAIN命令进行语句分析时，会发现优化器并没有选择索引去查找数据，而是通过扫描聚集索引（全表扫描），这种情况多发于范围查找、JOIN连接操作等情况下。
举例：orderdetails 表有（OrderID、ProductID）的联合主键，此外还有OrderID的单个索引，但在下列语句中优化器并没有使用OrderID列索引，而是使用了聚集索引。
SELECT * FROM orderdetails WHERE orderId > 10000 and orderId < 102000;
原因在于查询的数据是整行信息，而OrderID索引不能覆盖全部信息，因此需要回表。虽然OrderID索引中数据是顺序存放的，但回表查找的数据是无序的，因此变为了从磁盘上的离散操作。如果要求访问的数据量很小，则优化器还是会选择辅助索引，但当访问的数据占整个表中数据的蛮大一部分时（一般是20%左右），优化器会选择通过聚集索引来查找数据，因为顺序读要远快于离散读。
3.4 索引提示
MySQL 数据库支持索引提示，显示地告诉优化器使用哪个索引。使用场景：
- 优化器错误地选择了某个索引，导致SQL语句运行的很慢。
- 可选择的索引非常多，优化器选择执行计划时间的开销可能会大于SQL语句本身。
用户指定使用某个索引的两种方式：
- USE INDEX： 只是告诉优化器可以选择该索引，实际上优化器还是会再根据自己的判断进行选择。
- FORCE INDEX： 强制优化器选择该索引。
3.5 Multi-Range Read（MRR）优化
MRR 优化的目的是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，这对于IO-bound类型的SQL查询语句可带来性能极大的提升。MRR 优化可是用于 range、range、eq_ref 类型的查询。
MRR优化有以下几个好处：
- MRR 使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键排序，并按照主键排序的顺序进行书签查找。
- 减少缓冲池中页被替换的次数。
- 批量处理对键值的查询操作。
对于范围查询和JOIN查询操作，MRR的工作方式如下：
- 将查询得到的辅助索引键值存放于一个缓存中，这时是根据辅助索引键值排序的。
- 将缓存中的键值根据RowID进行排序。
- 根据RowID的排序顺序来访问实际的数据文件。
3.6 Index Condition Pushdown (ICP) 优化
是一种根据索引进行查询的优化方式。5.6以前，当进行索引查询时，首先根据索引查找记录，再根据 WHERE 条件来过滤记录。 支持 ICP 后，会在取出索引的同时，判断是否可以进行 WHERE 条件的过滤，也就是将 WHERE 的部分过滤操作放在了存储引擎层。
ICP 优化支持 range、ref、eq_ref、ref_or_null类型的查询。
假设某张表有联合索引（zip_code、last_name、first_name）,并且查询语句如下：
SELECT * FROM people
WHERE zip_code = '95043'
AND last_name LIKE '%etrunia%'
AND address LIKE '%Main Street%';
对于上述语句，MySQL 数据库可以通过索引来定位zip_code等于95 054的记录，但是索引对WHERE条件没有任何帮助。
若不支持ICP优化，则数据库需要先通过索引取出所有 zip_code = 95 054 的记录，然后再过滤 WHERE 之后的两个条件。
若支持ICP优化，则在索引取出时，就会进行WHERE条件的过滤，然后再去获取记录。这将极大地提高查询的效率。当然，WHERE可以过滤的条件是该索引可以覆盖到的范围。
总结：使用ICP优化后，查询条件可以在辅助索引完成过滤，缩小回表查询记录。
