InnoDB存储引擎支持事务，是一个通用的、平衡了高可用与高性能的存储引擎。它的设计目标主要面向在线事务处理（OLTP）的应用。它的特点有行锁设计、支持外键、支持类似Oracle的非锁定读等。

InnoDB存储引擎支持以下几种常见索引：B+树索引、全文索引、哈希索引。我们平时在开发中使用最多的，就是B+树索引。
B+树索引是传统意义上的索引，这是目前关系数据库系统中最常用和最有效的索引。需要注意的是，B+树索引并不能找到一个给定键值的具体行。B+树索引能够找到的只是被查找的数据行所在的页。然后通过数据库把页读入内存，在内存中进行查找，最后得到所需的数据。

B+树索引是基于B+树数据结构构建的，B+树的数据结构在很多数据结构书中都有详细描述，在此不再详述。数据库中的B+树索引可以分为聚集索引和辅助索引，它们的内部都是高度平衡的，叶子存放着所有的数据的树结构。聚集索引与辅助索引不同的是，叶子节点存放的是一整行信息。

聚集索引（Cluster Index)

聚集索引就是按照每张表的主键构建一个B+树，同时叶子节点存放的即为整张表的行记录数据，我们也将聚集索引的叶子节点称为数据页。聚集索引的结构决定了索引组织表中数据也是索引的一部分。由于B+树索引本身是有序的，同时数据行也存储在叶子节点上，因此通过聚集索引我们能够很快的访问针对范围值的查询。

注：

• 当你在表中创建了一个PRIMARY KEY时，InnoDB就将该索引作为了聚集索引，因此最好为任何一个你创建的表添加PRIMARY KEY。
• 如果一个表中没有定义一个PRIMARY KEY，MySQL会把表中第一个非NULL且唯一的索引作为聚集索引。
• 如果表中没有符合上述两种情况的索引，InnnoDB就会隐式的在包含rowID的合成列上生成聚集索引。

（也即不管如何，InnoDB的表中一定会有一个聚集索引，不管是显示还是隐式创建的）

由于实际的数据页只能按照一颗B+树进行排序，因此每张表只能拥有一个聚集索引。在多数情况下，查询优化器倾向于采用聚集索引，因为聚集索引能够在B+树叶子节点上直接找到数据。（聚集索引对主键的排序查找和范围查找速度非常快。因为叶子节点就维护了数据行。）图一展示了一个聚集索引的数据分布(所有数据行就直接在叶子节点上)：

聚集索引(局部)

辅助索引(Secondary Index)

InnoDB中所有除聚集索引以外的所有索引都被称为辅助索引。对于辅助索引，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点的索引行中还包含一个主键值。通过主键值InnoDB存储引擎可以再次在聚集索引中找到对应索引项的行数据。辅助索引的存在不会影响数据在聚集索引中的组织，因此每张表上可以存在多个辅助索引。当通过辅助索引来查找数据时，InnoDB存储引擎会遍历辅助索引并获得页中的指向主键索引的主键，然后再通过主键索引依次找到一个完整的行记录。因此，这就意味着通过辅助索引查找行，存储引擎先找到辅助索引的叶子节点，得到对应的主键值，然后根据该主键值去聚集索引上查找对应的行数据。也即一次辅助索引查找需要两次对B+树进行查找。
辅助索引数据分布图示：

辅助索引

B+树索引的管理

索引的创建删除通过两种方法，一种是ALTER TABLE，一种是CREATE/DROP INDEX，具体语法如下：

CREATE INDEX | KEY idx_b ON t
ALTER TABLE t ADD INDEX | KEY idx_b (b(100));//前缀索引
ALTER TABLE t ADD INDEX | KEY idx_a_b (a,b);//联合索引
ALTER TABLE t DROP INDEX | KEY idx_b;
DROP INDEX idx_b ON T;//删除索引

通过SQL命令

SHOW INDEX FROM t;

可以查看表t上的索引情况，图示如下：

该结果每行的含义如下：

在所有这些参数中，Cardinality的值十分关键，优化器会根据该值来判断是否使用该索引。但并非每次更新索引都会更新该值，这样做的代价太大。如果我们需要主动去更新该值，可以使用ANALYZE TABLE命令。

这里，我们会多了解一下Cardinality值的意义：Cardinality值表示索引中不重复记录的预估值。虽然该值是一个预估值，但是对于我们是否需要在某列上建立索引具有很重要的指导意义。有一个参数Cardinality/n_rows_in_tables应该尽可能的接近1，此时表明该列的值基本是相互唯一的，表明该列具有很高的选择性，在该列上建立索引并通过该列进行条件查询，可以极大的改善查询速度,如果该值过于小，我们就需要考虑是否有必要在该列上建立索引。举一个例子：

CREATE TABLE test(
    id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '',
    customer_id BIGINT(20) NOT NULL COMMENT '客户ID',
    staff_id BIGINT(20) NOT NULL COMMENT '职员ID',
    sex TINYINT(1) DEFAULT 0 COMMENT '性别0-女 1-男',
    company VARCHAR(50) NOT NULL COMMENT '公司名称'.
    PRIMARY KEY (id),
    KEY idx_sex (sex),
    KEY idx_cus_sta (customer_id,staff_id)
)ENGINE=InnoDB CHARSET=utf8;

如该表t所示，sex列只有两种情况,可以想象在一般情况下通过SHOW INDEX查询该表得到sex列的Cardinality的值一定是2，Cardinality/n_rows_in_tables的值一定非常小（接近于0）以sex为条件进行查询时，每次也只能筛选一半的符合条件的数据记录，该列的选择性很低，一般没有必要在这种列上建立索引。

在InnoDB存储引擎中，Cardinality统计信息的更新发生在两个操作中：INSERT和UPDATE。如果每次发生INSERT和UPDATE操作就进行Cardinality信息统计更新，会给数据库带来很大的负担，因此InnoDB存储引擎内部对Cardinality值的更新策略是：

• 表中1/6数据发生了变化；
• stat_modified_counter>2000 000 000 ；这是为了应对这样一种情况，表中某一行记录频繁的更新，但实际上表中的数据行数并没有增加，故在InnoDB存储引擎内部有一个计数器stat_modified_counter,用来表示发生变化的次数，大于2000 000 000同样更新Cardinality的值。

InnoDB存储引擎内部对Cardinality值的统计和更新操作是通过采样方法的。InnoDB存储引擎默认对8个叶子节点进行采样，过程如下：

• 取得B+树索引中叶子节点的数目，记为N
• 随机取得B+树索引中的8个叶子节点，统计每个页中不同记录的个数，记为M1,M2,M3...M8
• Cardinality=(M1+M2+...+M8)*N/8

很明显我们可以看出，Cardinality值是通过对8个叶子节点预估而来。根据它的计算方法，我们可以知道Cardinality值是在不断变化的。

B+树索引的使用

在OLTP应用中，我们的查询操作一次只是从数据库中获取一小部分数据，如根据主键值查询订单信息，这就是一种典型的OLTP查询。在这种情况下，通过该主键建立的B+树索引能够很快的获取对应数据。在实际使用中，我们可能会用到以下一些索引。

• 前缀索引

对于Mysql中的字符列，我们可以选择在某些列上建立前缀索引。因为有些时候，索引列很长，这会使得索引变得大且慢，通常这时候，我们可以索引该字符列开始的部分字符，这样可以大幅节约索引空间，提高索引效率，但是需要注意的是，这样也可能会降低索引的选择性。因此，前缀索引的目标应该是：要选择足够长的前缀以保证高的选择性，同时前缀不要太长（以节约空间）还是以test表为例，我们在company字符列上创建一个前缀索引：

ALTER TABLE test ADD INDEX idx_com company(20);//示例，前缀长度按实际情况选择

具体在company字段上选择多长的前缀呢？这个需要我们实际去试一试。

SELECT COUNT(DISTINCT LEFT(company,4))/COUNT(*) AS sel4,
       COUNT(DISTINCT LEFT(company,5))/COUNT(*) AS sel5,
       COUNT(DISTINCT LEFT(company,6))/COUNT(*) AS sel6,
       ......
       FROM test;

通过对比sel4,sel5,sel6...的结果，就可以找到一个比较合适的前缀长度。

• 联合索引

联合索引是指在多个列上建立的索引。如下sql语句：

ALTER TABLE t ADD INDEX | KEY idx_a_b (a,b);//联合索引

在B+树中，所有键值都是排序的，而对于联合索引，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，依次等等。因此，索引的顺序非常重要。一个简单的联合索引的例子如下图所示：

联合索引

当不需要考虑排序和分组时，将选择性最高的列放在索引的前面通常是更好的。这么做可以最快的过滤出所需要的行。以前面的test表为例，如果我们想要建立一个包含customer_id与staff_id的联合索引，我们可以先比较一下两个列的选择性，可以执行一下SQL语句：

SELECT COUNT(DISTINCT coustomer_id)/COUNT(*) AS customer_id_selectivity,
       COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity 
FROM test;

比较customer_id_selectivity与staff_id_selectivity哪个值更接近1，也即对应列的选择性就更高，该列就更适合放在联合索引的前面。

• 覆盖索引

InnoDB支持覆盖索引（覆盖索引并非是一种可以通过SQL语句创建的索引，与前缀索引、联合索引不同，我们可以认为这是一种逻辑上的索引，即符合一定条件的索引我们都可以称之为是覆盖索引），即从辅助索引中就可以直接得到查询的记录，而不需要再次查询聚集索引中的记录。使用覆盖索引的一个好处就是辅助索引不包含整行记录，因此它的大小远小于聚集索引，可以减少大量IO操作，查询速度很快。

不是所有的索引都能够成为覆盖索引，覆盖索引必须要存储索引列的值。当我们发起一个一个被索引覆盖的查询时，在EXPLAIN的Extra列可以看到Using Index信息，表示该次查询在索引上就直接获得了所有需要的数据，也即这是一个覆盖索引。如下图所示：

由于customer_id与staff_id同时存在于索引中，因此当查询customer_id与staff_id时，就可以直接从辅助索引中可以获取所需的字段，而不需要再次去查找聚集索引。

最后

InnoDB存储引擎是我们平时使用最广泛的MySQL存储引擎。同时B+树索引也是我们默认选择的索引类型。正确的创建和使用索引是实现高性能查询的基础。而这些合理的索引操作是基于我们对InnoDB存储引擎的内部原理有一定的了解之上的。

本文只是关于InnoDB索引的简单总结，如有问题，欢迎大家一起交流、讨论。

参考

• mysql reference
• 高性能MySQL
• MySQL技术内幕