《MySQL技术内幕---InnoDB存储引擎》读书笔记第5章

本文主要是介绍《MySQL技术内幕---InnoDB存储引擎》读书笔记第5章，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

第5章 索引与算法

索引是应用程序设计和开发的一个重要方面。若索引太多，应用程序的性能可能会受到影响。而索引太少，对查询性能又会产生影响。要找到一个合适的平衡点，这对应用程序的性能至关重要。
一些开发人员总是在事后才想起添加索引——我一直认为，这源于一种错误的开发模式。如果知道数据的使用，从一开始就应该在需要处添加索引。

5.1 InnoDB存储引擎索引概述
InnoDB存储引擎支持以下几种常见的索引：
①B+树索引
②全文索引
③哈希索引
1、InnoDB存储引擎支持的哈希索引是自适应的，InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表中生成哈希索引。
2、B+树索引就是传统意义上的索引，这是目前关系型数据库系统中查找最为常用和最为有效的索引。B+树索引的构造类似于二叉树，根据键值(Key Value)快速找到数据。
3、注意 B+树中的B不是代表二叉（binary)，而是代表平衡（balance)，因为B+树是从最早的平衡二叉树演化而来，但是B+树不是一个二叉树。
4、B+树索引并不能找到一个给定键值的具体行。B+树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入到内存，再在内存中进行查找，最后得到要查找的数据。

5.2 数据结构与算法

5.2.1 二分查找法
二分查找法（binary search)也称为折半查找法，用来查找一组有序的记录数组中的某一记录，其基本思想是：将记录按有序化（递增或递减）排列，在查找过程中采用跳跃式方式查找，即先以有序数列的中点位置为比较对象，如果要找的元素值小于该中点元素，则将待查序列缩小为左半部分，否则为右半部分。通过一次比较，将查找区间缩小一半。
每页Page Directory 中的槽是按照主键的顺序存放的，对于某一条具体记录的查询是通过对Page Directory 进行二分查找得到的。

5.2.2 二叉查找法和平衡二叉树
1、B+树是通过二叉查找树，再由平衡二叉树，B树演化而来。
2、在二叉查找树中，左子树的键值总是小于根的键值，右子树的键值总是大于根的键值。因此可以通过中序遍历得到键值的排序输出。
3、平衡二叉树（AVL树）的定义如下：首先符合二叉查找树的定义，其次必须满足任何节点的两个子树的高度最大差为1。
4、平衡二叉树的查询速度的确很快，但是维护一棵平衡二叉树的代价是非常大的。通常来说，需要1次或多次左旋和右旋来得到插入或更新后树的平衡性。
5、对一棵平衡树的维护是有一定开销的,不过平衡二叉树多用于内存结构对象中,因此维护的开销相对较小。

5.3 B+树
B+树由B树和索引顺序访问方法(ISAM)演化而来。
B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。在B+树中,所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上,由各叶子节点指针进行连接。先来看一个B+树,其高度为2,每页可存放4条记录,扇出( fan out)为5,如图5-6所示

从图5-6可以看出,所有记录都在叶子节点上,并且是顺序存放的,如果用户从最左边的叶子节点开始顺序遍历,可以得到所有键值的顺序排序:5、10、15、20、25、30、50、55、60、65、75、80、85、90。

5.3.1 B+树的插入操作
B+树的插入必须保证插入后叶子节点中的记录依然排序,同时需要考虑插入到B+树的三种情况,每种情况都可能会导致不同的插入算法。如表5-1所示。

这里用一个例子来分析B+树的插入。例如，对于图5-6中的这棵B+树，若用户插入28这个键值，发现当前Leaf Page和Index Page都没有满，直接进行插人即可，之后得图5-7。

接着再插入70这个键值，这时原先的Leaf Page 已经满了，但是Index Page还没有满，符合表5-1的第二种情况，这时插入Leaf Page后的情况为55、55、60、65、70，并根据中间的值60来拆分叶子节点，可得图5-8。

因为图片显示的关系，这次没有能在各叶子节点加上双向链表指针。不过和图5-6、图5-7一样，它还是存在的。
最后插入键值95，这时符合表5-1中讨论的第三种情况，即Leaf Page和Index Page都满了，这时需要做两次拆分，如图5-9所示。

可以看到，不管怎么变化，B+树总是会保持平衡。但是为了保持平衡对于新插入的键值可能需要做大量的拆分页(split)操作。因为B+树结构主要用于磁盘，页的拆分意味着磁盘的操作，所以应该在可能的情况下尽量减少页的拆分操作。因此，B+树同样提供了类似于平衡二叉树的旋转(Rotation)功能。
旋转发生在Leaf Page已经满，但是其的左右兄弟节点没有满的情况下。这时B+树并不会急于去做拆分页的操作，而是将记录移到所在页的兄弟节点上。在通常情况下，左兄弟会被首先检查用来做旋转操作，因此再来看图5-7的情况，若插入键值70，其实B+树并不会急于去拆分叶子节点，而是去做旋转操作，得到如图5-10所示的操作。

从图5-10可以看到，采用旋转操作使B+树减少了一次页的拆分操作，同时这棵B+树的高度依然还是2。

5.3.2 B+树的删除操作
B+树使用填充因子(fill factor)来控制树的删除变化，50%是填充因子可设的最小值。B+树的删除操作同样必须保证删除后叶子节点中的记录依然排序，同插入一样，B+树的删除操作同样需要考虑以下表5-2中的三种情况，与插入不同的是，删除根据填充因子的变化来衡量。

根据图5-9的B+树来进行删除操作。首先删除键值为70的这条记录，该记录符合表5-2讨论的第一种情况，删除后可得到图5-11。

接着删除键值为25的记录，这也是表5-2讨论的第一种情况，但是该值还是Index Page中的值，因此在删除Leaf Page中的25后，还应将25的右兄弟节点的28更新到Page Index中，最后可得图5-12。

最后看删除键值为60的情况。删除Leaf Page中键值为60的记录后，Fill Factor小于50%，这时需要做合并操作，同样，在删除Index Page中相关记录后需要做Index Page的合并操作，最后得到图5-13。

5.4 B+树索引
前面讨论的都是B+树的数据结构及其一般操作，B+树索引的本质就是B+树在数据库中的实现。但是B+索引在数据库中有一个特点是高扇出性，因此在数据库中，B+ 树的高度一般都在2～4层，这也就是说查找某一键值的行记录时最多只需要2到4次IO，这倒不错。因为当前一般的机械磁盘每秒至少可以做100次10，2～4次的IO意味着查询时间只需0.02～0.04秒。
数据库中的B+树索引可以分为聚集索引(clustered inex）和辅助索引(secondary index)，但是不管是聚集还是辅助的索引，其内部都是B+树的，即高度平衡的，叶子节点存放着所有的数据。聚集索引与辅助索引不同的是，叶子节点存放的是否是一整行的信息。
注意：辅助索引有时也称非聚集索引（non-clustered index)。

5.4.1 聚集索引
1、InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。而聚集索引(clustered index）就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。
2、由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索
引。在多数情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引
的叶子节点上直接找到数据。此外，由于定义了数据的逻辑顺序，聚集索引能够特别快
地访问针对范围值的查询。查询优化器能够快速发现某一段范围的数据页需要扫描。
3、数据页上存放的是完整的每行的记录，而在非数据页的索引页中，存放的仅仅是键值及指向数据页的偏移量，而不是一个完整的行记录。因此这棵聚集索引树的构造大致如图5-14所示。

4、如果聚集索引必须按照特定顺序存放物理记录，则维护成本显得非常之高。所以，聚集索引的存储并不是物理上连续的，而是逻辑上连续的。这其中有两点：一是前面说过的页通过双向链表链接，页按照主键的顺序排序；另一点是每个页中的记录是通过单向链表进行维护的，物理存储上可以同样不按照主键顺序存储。
5、聚集索引的另一个好处是，它对于主键的排序查找和范围查找速度非常快。叶子节点的数据就是用户所要查询的数据。
6、范围查询(range query)，即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可。
7、 执行EXPLAIN得到了MySQL数据库的执行计划(execute plan)，并且在rows 列中给出了一个查询结果的预估返回行数。要注意的是，rows代表的是一个预估值，不是确切的值。

5.4.2 辅助索引
对于辅助索引(Secondary Index，也称非聚集索引），叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签
(bookmark)。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。由
于InnoDB存储引擎表是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。图5-15显示了InnoDB存储引擎中辅助索引与聚集索引的关系。

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。
非聚集索引的离散读，一般的数据库都通过实现预读(read ahead）技术来避免多次的离散读操作。

5.4.3 B+树索引的分裂
1、5.3节中介绍B+树的分裂是最为简单的一种情况，这和数据库中B+树索引的情况可能略有不同。此外5.3节页没有涉及并发，而这才是B+树索引实现最为困难的部分。B+树索引页的分裂并不总是从页的中间记录开始，这样可能会导致页空间的浪费。
2、InnoDB存储引擎的Page Header中有以下几个部分用来保存插入的顺序信息：
①PAGE_LAST_INSERT
②PAGE_DIRECTION
③PAGE_N_DIRECTION
通过这些信息，InnoDB存储引擎可以决定是向左还是向右进行分裂，同时决定将分裂点记录为哪一个。若插入是随机的，则取页的中间记录作为分裂点的记录，这和之前介绍的相同。若往同一方向进行插入的记录数量为5，并且目前已经定位（cursor)到的记录（InnoDB存储引擎插入时，首先需要进行定位，定位到的记录为待插人记录的前一条记录)之后还有3条记录，则分裂点的记录为定位到的记录后的第三条记录，否则分裂点记录就是待插入的记录。
来看一个向右分裂的例子，并且定位到的记录之后还有3条记录，则分裂点记录如图5-17所示。

图5-17向右分裂且定位到的记录之后还有3条记录，split record为分裂点记录最终向右分裂得到如图5-18所示的情况。

对于图5-19的情况，分裂点就为插入记录本身，向右分裂后仅插入记录本身，这在自增插入时是普遍存在的一种情况。

5.4.4 B+树索引的管理
1.索引管理
索引的创建和删除可以通过两种方法，一种是ALTER TABLE，另一种是
CREATE/DROP INDEX。
用户可以设置对整个列的数据进行索引，也可以只索引一个列的开头部分数据。
查看表中索引的信息：SHOW INDEX FROM xxxxxx
SHOW INDEX展现结果中每列的含义:
口Table：索引所在的表名。
口Non_unique：非唯一的索引，可以看到primary key是0，因为必须是唯一的。
口Key_name：索引的名字，用户可以通过这个名字来执行DROP INDEX。
口Seq_in_index：索引中该列的位置，如果看联合索引idx_a_c就比较直观了。
口Column_name：索引列的名称。
口Collation：列以什么方式存储在索引中。可以是A或NULL。B+树索引总是A，即排序的。如果使用了Heap存储引擎，并且建立了Hash索引，这里就会显示NULL了。因为Hash根据Hash桶存放索引数据，而不是对数据进行排序。
口Cardinality：非常关键的值，表示索引中唯一值的数目的估计值。Cardinality 表的行数应尽可能接近1，如果非常小，那么用户需要考虑是否可以删除此索引。
口Sub_part：是否是列的部分被索引。如果看idx_b这个索引，这里显示100，表示只对b列的前100字符进行索引。如果索引整个列，则该字段为NULL。
囗Packed：关键字如何被压缩。如果没有被压缩，则为NULL。
囗Null：是否索引的列含有NULL值。可以看到idx_b这里为Yes,因为定义的列b允许NULL值。
口Index_type：索引的类型。InnoDB存储引擎只支持B+树索引，所以这里显示的都是BTREE。
口Comment：注释。
Cardinality值非常关键，优化器会根据这个值来判断是否使用这个索引。但是这个值并不是实时更新的，即并非每次索引的更新都会更新该值，因为这样代价太大了。因此这个值是不太准确的，只是一个大概的值。
如果需要更新索引Cardinality 的信息，可以使用ANALYZE TABLE xxxxxx命令。
2.Fast Index Creation
①InnoDB存储引擎从InnoDB 1.0.x版本开始支持一种称为Fast Index Creation(快速索引创建)的索引创建方式——简称FIC。
②对于辅助索引的创建，InnoDB存储引擎会对创建索引的表加上一个S锁。在创建的过程中，不需要重建表，因此速度较之前提高很多，并且数据库的可用性也得到了提高。删除辅助索引操作就更简单了，InnoDB存储引擎只需更新内部视图，并将辅助索引的空间标记为可用，同时删除MySQL数据库内部视图上对该表的索引定义即可。
③这里需要特别注意的是，临时表的创建路径是通过参数tmpdir进行设置的。用户必须保证tmpdir有足够的空间可以存放临时表，否则会导致创建索引失败。
④由于FIC在索引的创建的过程中对表加上了S锁，因此在创建的过程中只能对该表进行读操作，若有大量的事务需要对目标表进行写操作，那么数据库的服务同样不可 用。
⑤此外，FIC方式只限定于辅助索引，对于主键的创建和删除同样需要重建一张表。
3.Online Schema Change
Online Schema Change(在线架构改变，简称OsC）最早是由Facebook实现的一种在线执行DDL的方式，并广泛地应用于Facebook的MySQL数据库。所谓“在线”是指在事务的创建过程中，可以有读写事务对表进行操作，这提高了原有MySQL数据库在DDL操作时的并发性。
4.Online DDL
虽然FIC可以让InnoDB存储引擎避免创建临时表，从而提高索引创建的效率。但正如前面小节所说的，索引创建时会阻塞表上的DML操作。OSC虽然解决了上述的部分问题，但是还是有很大的局限性。MySQL 5.6版本开始支持Online DDL(在线数据定义）操作，其允许辅助索引创建的同时，还允许其他诸如INSERT、UPDATE、DELETE这类DML操作，这极大地提高了MySQL数据库在生产环境中的可用性。
此外，不仅是辅助索引，以下这几类DDL操作都可以通过“在线”的方式进行操作：
口辅助索引的创建与删除
口改变自增长值
口添加或删除外键约束
囗列的重命名
InnoDB存储引擎实现Online DDL的原理是在执行创建或者删除操作的同时，将 INSERT、UPDATE、DELETE这类DML操作日志写入到一个缓存中。待完成索引创
建后再将重做应用到表上，以此达到数据的一致性。
需要特别注意的是，由于Online DDL在创建索引完成后再通过重做日志达到数据库的最终一致性，这意味着在索引创建过程中，SQL优化器不会选择正在创建中的索引。

5.5 Cardinality值

5.5.1 什么是Cardinality
并不是在所有的查询条件中出现的列都需要添加索引。对于什么时候添加B+树索引，一般的经验是，在访问表中很少一部分时使用B+树索引才有意义。对于性别字段、 地区字段、类型字段，它们可取值的范围很小，称为低选择性。相反，如果某个字段的取值范围很广，几乎没有重复，即属于高选择性，则此时使用B+树索引是最适合的。
在访问高选择性属性的字段并从表中取出很少一部分数据时，对这个字段添加B+树索引是非常有必要的。

5.5.2 InnoDB存储引擎的Cardinality 统计
1、建立索引的前提是列中的数据是高选择性的，这对数据库来说才具有实际意义。然而数据库是怎样来统计Cardinality信息的呢?因为MysQL数据库中有各种不同的存储引擎，而每种存储引擎对于B+树索引的实现又各不相同，所以对Cardinality 的统计是放在存储引擎层进行的。
2、如果一张表的数据非常大，如一张表有50G的数据，那么统计一次Cardinality信息所需要的时间可能非常长。这在生产环境下，也是不能接受的。因此，数据库对于Cardinality的统计都是通过采样（Sample)的方法来完成的。
3、在InnoDB存储引擎中，Cardinality统计信息的更新发生在两个操作中：INSERT和UPDATE。InnoDB存储引擎内部对更新Cardinality 信息的策略为：
口表中1/16的数据已发生过变化。
口 stat_ modified_counter>2 000 000 000。
第一种策略为自从上次统计Cardinality信息后，表中1/16的数据已经发生过变化，这时需要更新Cardinality信息。
第二种情况考虑的是，如果对表中某一行数据频繁地进行更新操作，这时表中的数据实际并没有增加，实际发生变化的还是这一行数据，则第一种更新策略就无法适用这这种情况。故在InnoDB存储引擎内部有一个计数器stat_modified_counter,用来表示发生变化的次数，当stat_modified_counter大于2000 000 000时，则同样需要更新Cardinality信息。
接着考虑InnoDB存储引擎内部是怎样来进行Cardinality信息的统计和更新操作的呢?同样是通过采样的方法。默认InnoDB存储引擎对8个叶子节点（Leaf Page）进行采用。采样的过程如下：
囗取得B+树索引中叶子节点的数量，记为A。
口随机取得B+树索引中的8个叶子节点。统计每个页不同记录的个数，即为P1，P2……,p8。
口根据采样信息给出Cardinality的预估值：Cardinality=(P1+P2+…+P8)*A/8。
通过上述的说明可以发现，在InnoDB存储引擎中，Cardinality值是通过对8个叶子节点预估而得的，不是一个实际精确的值。再者，每次对Cardinality 值的统计，都是通过随机取8个叶子节点得到的，这同时又暗示了另一个Cardinality 现象，即每次得到的Cardinality值可能是不同的。
当执行SQL语句ANALYZE TABLE、SHOW TABLE STATUS、SHOW INDEX以及访问INFORMATION_SCHEMA架构下的表TABLES和STATISTICS时会导致InnoDB存储引擎去重新计算索引的Cardinality值。若表中的数据量非常大，并且表中存在多个辅助索引时，执行上述这些操作可能会非常慢。

5.6 B+树索引的使用

5.6.1 不同应用中B+树索引的使用
实际的生产环境使用中，每个DBA和开发人员，还是需要根据自己的具体生产环境来使用索引，并观察索引使用的情况，判断是否需要添加索引。不要盲从任何人给你的经验意见，Think Different。

5.6.2 联合索引
联合索引是指对表上的多个列进行索引。
那么何时需要使用联合索引呢?从本质上来说，联合索引也是一棵B+树，不同的是联合索引的键值的数量不是1，而是大于等于2。

和之前讨论的单个键值的B+树并没有什么不同，键值都是排序的，通过叶子节点可以逻辑上顺序地读出所有数据。
因此，对.于查询SELECT…FROM TABLE WHERE a=xxx and b=xxx,显然是可以使用(a，b)这个联合索引的。对于单个的a列查询SELECT…FROM TABLE WHERE a=xxx，也可以使用这个(a，b)索引。但对于b列的查询SELECT…FROM TABLE WHEREb=xxx，则不可以使用这棵B+树索引。可以发现叶子节点上的b值为1、2、1、4、1、2，显然不是排序的，因此对于b列的查询使用不到（a，b）的索引。
联合索引的第二个好处是已经对第二个键值进行了排序处理。这时使用联合索引可以避免多一次的排序操作，因为索引本身在叶子节点已经排序了。

在Extra 选项中可以看到Using flesort，即需要额外的一次排序操作才能完成查询。而这次显然需要对列buy_date排序，因为索引userid中的buy_date是未排序的。正如前面所介绍的那样，联合索引(a，b)其实是根据列a、b进行排序，因此下列语句可以直接使用联合索引得到结果：
SELECT…FROM TABLE WHERE a=xxx ORDER BY b
然而对于联合索引(a，b，c）来说，下列语句同样可以直接通过联合索引得到结果：
SELECT…FROM TABLE WHERE a=xxx ORDER BY b
SELECT…FROM TABLE WHERE a=xxx AND b=Xxx ORDER BY c
但是对于下面的语句，联合索引不能直接得到结果，其还需要执行一次flesort排序操作，因为索引(a，c)并未排序：
SELECT…FROM TABLE WHERE a=xxx ORDER BY c

5.6.3 覆盖索引
1、InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。
2、对于InnoDB存储引擎的辅助索引而言，由于其包含了主键信息，因此其叶子节点存放的数据为（primary key1，primary key2，…，key1，key2，…)。例如，下列语句都可仅使用一次辅助联合索引来完成查询：
SELECT key2 FROM table WHERE key1=xxx;
SELECT primary key2,key2 FROM table WHERE key1=xxx;
SELECT primary key1,key2 FROM table WHERE key1=xxx;
SELECT primary keyl,primary key2,key2 FROM table WHERE key1=xxx;
3、覆盖索引的另一个好处是对某些统计问题而言的。还是对于上一小节创建的表buy_log，要进行如下的查询：
SELECT COUNT(/) FROM buy_1og ;
InnoDB存储引擎并不会选择通过查询聚集索引来进行统计。由于buy_log表上还有辅助索引，而辅助索引远小于聚集索引，选择辅助索引可以减少IO操作，故优化器的选择为如图5-26所示。

通过图5-26可以看到，possible_keys列为NULL，但是实际执行时优化器却选择了userid索引，而列Extra列的Using index就是代表了优化器进行了覆盖索引操作。
此外，在通常情况下，诸如（a，b）的联合索引，一般是不可以选择列b中所谓的查询条件。但是如果是统计操作，并且是覆盖索引的，则优化器会进行选择，如下述语句：
SELECT COUNT() FROM buy_log WHERE buy_date>='2011-01-01’AND buy_date<‘2011-02-01’
表buy_log有(userid,buy_date)的联合索引，这里只根据列b进行条件查询，一般情况下是不能进行该联合索引的，但是这句SQL查询是统计操作，并且可以利用到覆盖索引的信息，因此优化器会选择该联合索引，其执行计划如图5-27所示。

从图5-27中可以发现列possible_keys依然为NULL，但是列key 为userid_2，即表示（userid,buy_date)的联合索引。在列Extra同样可以发现Using index提示，表示为覆盖索引。

5.6.4 优化器选择不使用索引的情况
在某些情况下，当执行EXPLAIN命令进行SQL语句的分析时，会发现优化器并没有选择索引去查找数据，而是通过扫描聚集索引，也就是直接进行全表的扫描来得到数据。这种情况多发生于范围查找、JOIN链接操作等情况下。

在possible_keys一列可以看到查询可以使用PRIMARY、OrderID、OrdersOrder_Details三个索引，但是在最后的索引使用中，优化器选择了 PRIMARY聚集索引，也就是表扫描（table scan)，而非OrderID辅助索引扫描（index scan)。
这是为什么呢?原因在于用户要选取的数据是整行信息，而OrderlD索引不能覆盖到我们要查询的信息，因此在对OrderID索引查询到指定数据后，还需要一次书签访问来查找整行数据的信息。虽然OrderID索引中数据是顺序存放的，但是再一次进行书签查找的数据则是无序的，因此变为了磁盘上的离散读操作。如果要求访问的数据量很小，则优化器还是会选择辅助索引，但是当访问的数据占整个表中数据的蛮大一部分时（一般是20%左右)，优化器会选择通过聚集索引来查找数据。因为之前已经提到过，顺序读要远远快于离散读。
因此对于不能进行索引覆盖的情况，优化器选择辅助索引的情况是，通过辅助索引查找的数据是少量的。这是由当前传统机械硬盘的特性所决定的，即利用顺序读来替换随机读的查找。若用户使用的磁盘是固态硬盘，随机读操作非常快，同时有足够的自信来确认使用辅助索引可以带来更好的性能，那么可以使用关键字FORCE INDEX来强制使用某个索引，如：
SELECT * FROM orderdetails FORCE INDEX(OrderID)
WHERE orderid>10000 and orderid<102000;
这时的执行计划如图5-30所示。

5.6.5 索引提示
1、MySQL数据库支持索引提示（INDEX HINT)，显式地告诉优化器使用哪个索引。个人总结以下两种情况可能需要用到INDEX HINT：
口MySQL数据库的优化器错误地选择了某个索引，导致SQL语句运行的很慢。这种情况在最新的MySQL数据库版本中非常非常的少见。优化器在绝大部分情况下工作得都非常有效和正确。这时有经验的DBA或开发人员可以强制优化器使用某个索引，以此来提高SOL运行的速度。
口某SQL语句可以选择的索引非常多，这时优化器选择执行计划时间的开销可能会大于SQL语句本身。例如，优化器分析Range查询本身就是比较耗时的操作。这时DBA或开发人员分析最优的索引选择，通过Index Hint来强制使优化器不进行各个执行路径的成本分析，直接选择指定的索引来完成查询。
2、列Extra提示的Using intersect(b，a）表示根据两个索引得到的结果进行求交的数学运算，最后得到结果。
3、如果我们使用USE INDEX的索引提示来使用a这个索引，如：
SELECT * FROM t USE INDEX(a) WHERE a=1 AND b = 2;
虽然我们指定使用a索引，但是优化器实际选择的是通过表扫描的方式。因此，USE INDEX只是告诉优化器可以选择该索引，实际上优化器还是会再根据自己的判断进行选择。
4、如果使用FORCE INDEX的索引提示，如：
SELECT FROM t FORCE INDEX(a) WHERE a=1 AND b= 2;
可以看到，这时优化器的最终选择和用户指定的索引是一致的。如果用户确定指定某个索引来完成查询，那么最可靠的是使用FORCE INDEX，而不是USE INDEX。

5.6.6 Multi-Range Read优化
MySQL5.6版本开始支持Multi-Range Read(MRR)优化。Multi-Range Read 优化的目的就是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，这对于IO-bound类型的SQL查询语句可带来性能极大的提升。Multi-Range Read优化可适用于range,ref,eq_ref类型的查询。
MRR优化有以下几个好处：
口MRR使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找。
口减少缓冲池中页被替换的次数。
囗批量处理对键值的查询操作。
对于InnoDB和MyISAM存储引擎的范围查询和JOIN查询操作，MRR的工作方式如下：
口将查询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引键值排序的。
口将缓存中的键值根据RowID进行排序。
口根据RowID的排序顺序来访问实际的数据文件。
此外，若InnoDB存储引擎或者MyISAM存储引擎的缓冲池不是足够大，即不能存放下一张表中的所有数据，此时频繁的离散读操作还会导致缓存中的页被替换出缓冲池，然后又不断地被读入缓冲池。若是按照主键顺序进行访问，则可以将此重复行为降为最低。

这篇关于《MySQL技术内幕---InnoDB存储引擎》读书笔记第5章的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！