InnoDB存储引擎索引

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

• B+树索引
• 全文索引
• Hash索引

B+树索引

B+树索引的本质就是B+树在数据库中的实现。但是B+树索引在数据库中有一个特点是高扇出性，因此在数据库中，B+树的高度一般都在2~4层，这也就是说查找某个键值的行记录时最多只需要2到4次I/O。
数据库中的B+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index），但是不管是聚集索引还是辅助索引，其内部都是B+树，即高度平衡的，叶子节点存放着所有的数据。
B+树索引并不能找到一个给定键值的具体行。B+树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入到内存，再在内存中进行查找，最后得到要查找的数据。

聚集索引

InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。聚集索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。
聚集索引这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。

可以发现数据页上存放的是完整的每行的记录，而在非数据页的索引页中，存放的仅仅是键值及指向数据页的偏移量，而不是一个完整的行记录。
聚集索引有一个好处是，它对于主键的排序查找和范围查找速度非常快。叶子节点就是用户要查询的数据。

辅助索引

对于辅助索引，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含一个书签。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。

当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向聚集索引的主键，然后再通过聚集索引来找到一个完整的行记录。

B+树索引的管理

索引的创建和删除有两种方法：

• alter table
• create/drop index

alter table 表名 add primary key|index|key 索引名 (列名);
alter table 表名 drop primary key|index|key 索引名 (列名);
create index 索引名 on 表名(列名);
drop index 索引名 on 表名;

用户可以设置对整个列的数据进行索引，也可以只索引一个列的开头部分数据：

alter table t add key idx_b(b(100));

使用命令show index from 表名\G;查看表中索引信息：

命令中每列的含义：

• table：索引所在的表名。
• non_unique：非唯一的索引。primary key是0，因为必须是唯一的。
• key_name：索引的名字。
• seq_in_index：索引中该列的位置。
• column_name：索引列的名称。
• collation：列以什么方式存储在索引中。B+树总是A，即排序的。
• cardinality：表示索引中唯一值的数目的估计值。
• sub_part：是否是列的部分被索引。
• packed：关键字如何被压缩。
• null：是否索引的列含有null值。
• index_type：索引的类型。InnoDB存储引擎只支持B+树索引。
• comment：注释。

如果需要更新cardinality的信息，可以使用命令analyze table 表名;。

cardinality值
并不是在所有查询条件中出现的列都需要添加索引。一般经验是，在访问表中很少一部分时使用B+树索引才有意义。
如果某个字段的取值范围很广，几乎没有重复，即属于高选择性，则此时使用B+树索引是最适合的。
通过show index结果中的列cardinality来查看索引是否是高选择性的。cardinality表示索引中不重复记录数量的预估值。在实际应用中，cardinality/n_rows_in_table应尽可能接近1。如果非常小，那么用户需要考虑是否还有必要创建这个索引。

B+树索引的使用

联合索引

联合索引是指对表上的多个列进行索引。联合索引的创建方法与单个索引创建的方法一样，不同之处仅在于有多个索引列。

create table t(
a int,
b int,
primary key (a),
key idx_a_b (a,b)
)engine=innodb;

联合索引也是一颗B+树，不同的是联合索引的键值数量不是1而是大于等于2。
对于查询select * from table where a=xxx and b=xxx可以使用(a,b)联合索引的。
对于单个的a列查询select * from table where a=xxx也可以使用(a,b)联合索引的。
但对于单个的b列查询select * from table where b=xxx则不可以使用(a,b)联合索引的。

覆盖索引

InnoDB存储引擎支持覆盖索引，从索引中就能取得数据列，不必从数据表中取，换句话说查询列要被所使用的索引覆盖。即从辅助索引就可以得到查询的记录，而不需要查询聚集索引中的记录。
使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此减少大量I/O操作。
对于InnoDB存储引擎的辅助索引而言，由于其包含了主键信息，因此其叶子节点存放的数据为(primary key1,primary key2,…,key1,key2,…)。例如，下列语句都可仅使用一次辅助联合索引来完成查询：

select key2 from table where key1=xxx;
select primary key2,key3 from table where key1=xxx;
select primary key1,key2 from table where key1=xxx;
select primary key1,primary key2,key2 from table where key1=xxx;

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

在某些情况下，当执行explain命令进行SQL语句分析时，会发现优化器并没有选择索引去查找数据，而是通过扫描聚集索引，也就是直接进行全表扫描来得到数据。

索引提示

MySQL支持索引提示，显示告诉优化器使用哪个索引。

select * from t use index(a) where a=1 and b=1;

哈希算法

InnoDB存储引擎使用哈希算法来对字典进行查找，其冲突机制采用链表方式，哈希函数采用除法散列方式。InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表中生成哈希索引。

全文检索

全文检索（Full-Text Search）是将存储于数据库中的整本书或整篇文章中任意内容信息查找出来的技术。它可以根据需要获得全文中有关的章、节、段、句、词等信息，也可以进行各种统计和分析。
从InnoDB 1.2.x版本开始，InnoDB存储引擎开始支持全文检索。
全文检索通常使用倒排索引来实现，倒排索引在辅助表中存储了单词与单词自身在一个或多个文档中所在位置之间的映射，通常利用关联数组实现，其拥有两种表现形式：

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

InnoDB全文检索

InnoDB全文检索技术采用full inverted index的方式。在InnoDB存储引擎中，将(documentId，position)视为一个ilist，因此在全文检索的表中，有两个列，一个是word字段，另一个是ilist字段，并且在word字段上设有索引。
在InnoDB存储引擎中，为了支持全文检索，必须有一个列与word进行映射，在InnoDB中这个列被命名为FTS_DOC_ID，其类型必须是BIGINT UNSIGNED NOT NULL，并且InnoDB存储引擎自动会在该列上加入一个名为FTS_DOC_ID_INDEX的unique index。
上述这些操作都由InnoDB存储引擎自己完成，用户也可以在建表时自动添加FTS_DOC_ID，以及相应的Unique index。
示例：
建表：

create table fts_a(
FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
body TEXT,
Primary KEY(FTS_DOC_ID);

插入一些数据：

insert into fts_a values(null,'oease porridge in the pot');
.
.
.

创建全文检索：

create fulltext index idx_fts on fts_a(body); 

通过设置参数InnoDB_ft_aux_table来查看分词对应的信息：

set global innodb_ft_aux_table='数据库名/fts_a';
select * from information_schema.innodb_ft_index_table;

可以看到每个word都对应了一个DOC_ID和POSITION。此外，还记录了FIRST_DOC_ID、LAST_DOC_ID以及DOC_COUNT，分别代表了该word第一次出现的文档ID，最后一次出现的文档ID，以及该word在多少个文档中存在。

stopword列表：其表示该列表中的word不需要对其进行索引分词操作。例如，对于the这个单词，由于其不具有具体的意义，因此将其视为stopword。在information_schema数据库中InnoDB存储引擎有一张默认的stopword列表，默认共有36个stopword。此外用户还可以通过参数innodb_ft_server_stopword_table来自定义stopword列表：

create table user_stopword{
value varchar(30)
)engine=innodb;

set global innodb_ft_server_stopword_table="数据库名/user_stopword";

当前InnoDB存储引擎的全文检索有以下限制：

• 每张表只能有一个全文检索的索引
• 由多列组合而成的全文检索的索引必须使用相同的字符集与排序规则
• 不支持没有单词界定符的语言，如中文、日语等

全文检索

全文检索中match指定需要被查询的列，against指定了使用何种方法去进行查询。
有三种模式：

1. natural language模式（默认）：表示查询带有指定word的文档。

select * from fts_a where match(body) against('pease' in natural language mode);

2. boolean模式：当使用该模式，查询字符串的前后字符会有特殊含义。

select * from fts_a where match(body) against('+pease -hot' in boolean mode);

其中，+和-分别表示这个单词必须出现，或者一定不存在。
3. query expansion：这种查询通常在查询中关键词太短，用户需要implied knowledge时进行。
该查询分两个阶段：

• 第一阶段：根据搜索的单词进行全文索引查询。
• 第二阶段：根据第一阶段产生的分词再进行一次全文检索。