InnoDB存储引擎的索引

　　InnoDB存储引擎的索引

一. 索引组织表

　　在InnoDB存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table）。在InnoDB存储引擎表中，每张表都有个主键，如果在创建表时没有显示地定义主键，则InnoDB存储引擎会按照如下方式选择或创建主键：

1. 首先判断表中是否有非空的唯一索引，如果有，则选择建表时第一个定义的非空唯一索引为主键。
2. 如果不符合上述条件，InnoDB存储引擎自动创建一个6字节大小的指针。

二. InnoDB存储引擎索引概述

InnoDB存储引擎支持以下几种常见的索引：

• B+树索引
• 全文索引
• 哈希索引

B+树索引

　　B+树索引就是传统意义上的索引，这是目前关系型数据库系统中查找最为常用和最为有效的索引。B+树索引的构造类似于二叉树，根据键值快速找到数据。B+树不是一个二叉树，它最早从平衡二叉树演化而来。另一个容易被忽视的问题，B+树索引并不能找到一个给定键值的具体行。B+树索引能找到的只是被查找数据行所在的页，然后数据库通过把页读入到内存，再在内存中进行查找，最后得到要查找的数据。

聚集索引和辅助索引

　　数据库中B+树索引可以分为聚集索引和辅助索引。但是不管是聚集索引还是辅助索引，其内部都是B+树，即高度平衡的。聚集索引与辅助索引不同的是，叶子节点存放的是否是一整行的信息。

　　聚集索引：聚集索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。

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

联合索引

　　联合索引是指对表上的多个列进行索引。联合索引的创建方法与单个索引创建的方法一样，不同之处在于有多个索引列。例如，以下代码创建了一张t表，并且索引idx_a_b是联合索引：

　　CREATE TABLE t (

　　　　a INT,

　　　　b INT,

　　　　PRIMARY KEY (a),

　　　　KEY idx_a_b (a,b)

　　)ENGINE=INNODB

　　那么何时需要使用联合索引呢？对于查询 SELECT * FROM TABLE WHERE a = xxx AND b = xxx，显然可以使用(a,b)这个联合索引。对于单个的a列查询 SELECT  * FROM TABLE WHERE a = xxx，也可以使用这个(a,b)索引，但对于单个的b列查询，则不可以使用这个索引。

　　联合索引的第二个好处，是已经对第二个键值进行了排序处理，查询结果需要根据第二个键值进行排序，则非常方便。

覆盖索引

　　InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。

　　使用索引覆盖的好处就是，辅助索引不包含整行记录的所有信息，故其大小远远小于聚集索引，因此可以减少大量的IO操作。

　　覆盖索引的另一个好处是对某些统计问题而言的。例如要进行如下的查询：

　　SELECT COUNT(*) FROM buy_log;

　　InnoDB存储引擎并不会选择通过查询聚集索引来进行统计，因为buy_log上还有辅助索引（已设置的），而辅助索引远小于聚集索引，选择辅助索引可以减少IO操作。此外，通常情况下，诸如(a,b)的联合索引，一般是不可以选择列b中所谓的查询条件。但是如果是统计操作，并且是覆盖索引的，则优化器会进行选择。

优化器选择不使用索引的情况

　　在某些情况下，当执行EXPLAIN命令进行SQL语句的分析时，会发现优化器并没有选择索引去查找数据，而是通过扫描聚集索引，也就是直接进行全表的扫描来得到数据。这种情况多发生于范围查找、JOIN链接操作等情况下。如果在一个表中有3个索引：聚集索引、辅助索引和覆盖索引，查询的时候会优先选取覆盖索引。但是若覆盖索引不包含查询所需要的全部信息，则优化器可能会选择聚集索引，也就是表扫描，而非辅助索引扫描。那么对于不能进行索引覆盖的时候，优化器选择辅助索引的情况是：通过辅助索引查找的数据是少量的。这是由当前传统机械硬盘的特性所决定的，即利用顺序读来代替随机读。若用户使用的磁盘是固态硬盘，随机读操作非常快，同时有足够的自信来确认使用辅助索引可以带来更好的性能，那么可以使用关键字FORCE INDEX 来强制使用某个索引，如：

　　SELECT * FROM ORDER FORCE INDEX(order_id)  WHERE  order_id >1000 AND orde_id <1000000;

Multi-Range Read 优化

　　MySQL5.6版本开始支持Multi-Range Read (MRR) 优化。MRR优化的目的是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，这对于IO-bound 类型的SQL查询语句可带来性能极大的提升。MRR优化可适用于range，ref，eq_ref类型的查询。

Index Condition Pushdown (ICP) 优化

　　ICP也是MySQL5.6开始支持的一种根据索引进行查询的优化方式。不开启ICP时，当进行索引查询，首先根据索引来查找记录，然后再根据WHERE条件来过滤记录。开启ICP后，MySQL数据库会在取出索引的同时，判断是否可以进行WHERE条件的过滤，也就是将WHERE的部分过滤操作放在了存储引擎层。

全文索引

　　通过上面的介绍可知，通过索引字段的前缀进行查找，B+树索引是支持的：SELECT * FROM blog WHERE content like 'xxx%'。然而这种查询不能满足用户的要求，因为更多的情况下，用户需要查询的是博客内容包含单词xxx的文章，即：

　　SELECT * FROM blog WHERE content LIKE '%xxx%'

　　根据B+树索引的特性，上述SQL语句即便添加了B+树索引也是需要进行索引的扫描来得到结果，因此这种需求不是B+树索引能够很好地完成的工作。

　　全文检索（Full-Text Search）是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。它可以根据需要获取全文中的章、节、段、句、词等信息，也可以进行各种统计和分析。从InnoDB1.2.x版本开始，InnoDB存储引擎开始支持全文索引。

　　倒排索引（inverted index）

　　全文检索通常使用倒排索引来实现。倒排索引同B+树索引一样，也是一种索引结构。它在辅助表中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。这通常利用关联数组实现，其拥有两种表现形式：

　　inverted file index，其表现形式为{单词，单词所在文档的ID}

　　full inverted index，其表现形式为{单词，(单词所在文档的ID，在具体文档中的位置)}

哈希索引

　　InnoDB存储引擎支持的哈希索引是自适应的，InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表中生成哈希索引。自适应哈希索引经哈希函数映射到一个哈希表中，对于字典类型的查找非常快速，如 SELECT * FROM TABLE WHERE index_col = 'xxx'。但是对于范围查找就无能为力了。