Mysql索引与底层数据结构深度剖析

1. 索引的本质

索引是帮助MySQL高效获取数据排好序的数据结构。

比如下面这条SQL语句没有索引的话，就会一条条的查询记录，查询出来后，将col2查到的字段与SQL的where条件去比对，如果相等，即获得了结果，如果不相等，就会继续向下去查询

select * from test where test.col2=89;

数据是以文件的形式存放在磁盘上面的，每一行数据都有它的磁盘地址，查询一行数据，就会与磁盘发生一次IO交互。如果没有索引的话，要从数据里面检索一条数据，只能依次遍历这张表的全部数据，直到找到这条数据，效率是极为低下的。

但是有了索引之后，只需要在索引里面去检索这条数据就行了，因为它是一种特殊的专门用来快速检索的数据结构，我们找到数据存放的磁盘地址以后，就可以拿到数据了。

比如这里将col2这行数据放到二叉树里面去，这里再去查找我们的89号数据就会从我们的根节点出发，一比对就知道走根节点的右节点，即找到了我们的89数据。我们这里的节点为K-V，key为我们的索引字段，value为我们的索引所在行的磁盘地址。

2. 索引数据结构与算法

2.1 二分查找法

二分查找法也称为折半查找法，用来查找一组有序的记录数组中的某一记录。
比如现在有5，10，19，21，31，37，42，48，50，52这10个数，现要从这10个数中查找48这行记录

从图中显示用了3次就找到了48这个数，如果是顺序查找，我们需要8次。

2.2 二叉查找树和平衡二叉树

在介绍B+树前，需要先了解一下二叉查找树。B+树是通过二叉查找树，再由平衡二叉树，B树演化而来。左子树的键值总是小于根的键值，右子树的键值总是大于根的键值。因此可以通过中序遍历得到键值的排序输出。

如上图二叉查找树经过中序遍历后输出: 2、3、5、6、7、8。

二叉查找树可以任意地构造，同样是2、3、5、6、7、8这五个数字，也可以按照下图的方式建立二叉查找树。

这样平均查找次数就和顺序查找差不多。显然这棵二叉查找树的查询效率就低了。因此若想最大性能地构造一棵二叉查找树，需要这棵二叉查找树是平衡的，从而引出了新的定义——平衡二叉树， 或称为AVL树。

平衡二叉树的定义如下:首先符合二叉查找树的定义，其次必须满足任何节点的两个子树的高度最大差为1。

平衡二叉树的查询速度的确很快，但是维护一棵平衡二叉树的代价是非常大的。通常来说，需要1次或多次左旋和右旋来得到插人或更新后树的平衡性。

2.3 红黑树

二分查找树：
用数据结构可视化网站来展示一部分，在文章最后会附上此网站链接

红黑树：


红黑树特性：

1. 节点是红色或黑色。
2. 根节点是黑色。
3. 每个叶子节点都是黑色的空节点（NIL节点）。
4. 每个红色节点的两个子节点都是黑色。(从每个叶子到根的所有路径上不能有两个连续的红色节点)
5. 从任一节点到其每个叶子的所有路径都包含相同数目的黑色节点。

平衡二叉树是AVL树，比红黑树更平衡，AVL树是严格的平衡二叉树，平衡条件必须满足（所有节点的左右子树高度差绝对值不超过1），红黑树确保没有一条路径会比其它路径长出两倍，因此，红黑树是一种弱平衡二叉树（由于是弱平衡，可以看到，在相同的节点情况下，AVL树的高度低于红黑树）

但是如果是海量数据，树的高度过高，如果数据在叶子节点，查找次数就会过多，所以红黑树在大量数据的场景下，树的高度不可控，从而导致查询效率 不一定快。

2.4 哈希表

哈希表(Hash Table)也称散列表，由直接寻址表改进而来。我们先来看直接寻址表。当关键字的全域U比较小时，直接寻址是一种简单而有效的技术。假设某应用要用到一个动态集合，其中国每个元素都有一个取自全域U={0，1, .，. m-1}的关键字。同时假设没有两个元素具有相同的关键字。
用一个数组(即直接寻址表) T [0…m-1] 表示动态集合，其中每个位置( 或称槽或桶)对应全域U中的一个关键字。下图说明了这个方法，槽k指向集合中一个关键字为k的元素。如果该集合中没有关键字为k的元素，则T [k] =NULL。

直接寻址表存在一个很明显的问题，如果U很大，要在机器中存储大小为U的一张表T就有点不实际，如果实际要存储的关键字集合K相对于U来说很小，那分配给T的大部分空间就浪费了。

因此，哈希表出现了。在哈希方式下，该元素处于h (k)中，即利用哈希函数h，根据关键字k计算出槽的位置。函数h将关键字域U映射到哈希表T [0…m-1]的槽位上，如下图：

哈希表技术很好地解决了直接寻址遇到的问题，但是这样做有一个小问题，如上图所示的两个关键字可能映射到同一个槽上。一般将这种情况称之为发生了碰撞(collision)。 在数据库中一般采用最简单的碰撞解决技术，这种技术被称为链接法(chaining)。

但是Hash索引也有一定的缺点，比如

• 仅仅能满足"=",“IN”,不能使用范围查询
• 无法被用来避免数据的排序操作
• 不能利用部分索引键查询
• 不能避免表扫描
• 遇到大量Hash值相等的情况后性能低

2.5 B-树和B+树

B树是一种多路自平衡搜索树，它类似普通的二叉树，但是B书允许每个节点有更多的子节点。
B树示意图如下：

B树的特点：

• 所有键值分布在整个树中
• 叶节点具有相同的深度，叶节点的指针为空
• 所有索引节点元素不重复，所以任何关键字出现且只出现在一个节点中
• 节点中的数据索引从左到右递增排列
• 搜索有可能在非叶子节点结束
• 在关键字全集内做一次查找，性能逼近二分查找算法

B+树是B树的变体，也是一种多路平衡查找树，B+树的示意图为：

从图中也可以看到，B+树与B树的不同在于：

• 非叶子节点不存储data，只存储索引（冗余），可以放更多的索引
• 叶子节点包含所有索引字段
• 叶子节点用双向指针连接，提高区间访问的性能

2.5.1 B+树的插入操作

B+树的插入必须保证插人后叶子节点中的记录依然排序，同时需要考虑插人到B+树的三种情况，每种情况都可能会导致不同的插入算法。

例如，下图中若用户插入28这个键值，发现当前LeafPage和Index Page都没有满，直接进行插人即可，之后得下图。

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

图中没有在各叶子节点加上双向链表指针，不过还是存在的。

最后插人键值95，这时符合表中讨论的第三种情况，即Leaf Page和Index Page都满了，这时需要做两次拆分，如下图所示。

不管怎么变化，B+ 树总是会保持平衡。但是为了保持平衡对于新插人的键值可能需要做大量的拆分页(split) 操作。因为B+树结构主要用于磁盘，页的拆分意味着磁盘的操作，所以应该在可能的情况下尽量减少页的拆分操作。因此，B+树同样提供了类似于平衡二叉树的旋转(Rotation) 功能。

旋转发生在LeafPage已经满，但是其的左右兄弟节点没有满的情况下。这时B+树并不会急于去做拆分页的操作，而是将记录移到所在页的兄弟节点上。在通常情况下，左兄弟会被首先检查用来做旋转操作，因此再来看前面的情况，若插人键值70，其实B+树并不会急于去拆分叶子节点，而是去做旋转操作，得到如下图所示的操作。

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

2.5.2 B+树的删除操作

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

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

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

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

3. B+树索引

3.1 聚集索引

聚集索引(clustered index)就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。

聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。

数据页上存放的是完整的每行的记录，而在非数据页的索引页中，存放的仅仅是键值及指向数据页的偏移量（计算机汇编语言中的偏移量定义为：把存储单元的实际地址与其所在段的段地址之间的距离称为段内偏移，也称为“有效地址或偏移量”），而不是一个完整的行记录。

（下图为网络图，稍微有点小问题，比如叶子节点用双向指针连接）

为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？

• 如果设置了主键，那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增)。
• 整型的存储比字段类型要小，而且应为是InnoDB存储引擎使用的是B+Tree数据结构，在进行查询数据是需要对每个元素进行比较，而整型的对比效率是高于其他数据结构的，字符串等。
• 如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，主键的顺序按照数据记录的插入顺序排列，自动有序。当一页写满，就会自动开辟一个新的页
• 如果使用非自增主键（UUID，身份证）由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE（该命令允许用户手工地将已经删除的记录从索引中彻底删除）来重建表并优化填充页面。

为什么非主键索引结构叶子结点存储的是主键值？（一致性和节省存储空间）

• 减少了出现行移动或者数据页分裂时辅助索引的维护工作（当数据需要更新的时候，辅助索引不需要修改，只需要修改聚集索引，一个表只能有一个聚集索引，其他的都是辅助索引，这样只需要修改聚集索引就可以了，不需要重新构建辅助索引）
• 聚集索引也称为主键索引，其索引树的叶子节点中存的是整行数据，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。因为索引（目录）只能按照一种方法进行排序。
• 非聚集索引（普通索引）的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为辅助索引（secondary index）。

3.2 辅助索引

辅助索引(Secondary Index，也称非聚集索引)，叶子节点并不包含行记录的全部数据。

叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签(bookmark)。

该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据，辅助索引的书签就是相应行数据的聚集索引键。

3.3 B+树索引的分裂

B+树索引页的分裂并不总是从页的中间记录开始，这样可能会导致页空间的浪费。

例如下面的记录:
1、2、3、4、5、6、7、8、9
插人是根据自增顺序进行的，若这时插人10这条记录后需要进行页的分裂操作，那么根据前面介绍的分裂方法，会将记录5作为分裂点记录(split record)，分裂后得到下面两个页:
P1：1、2、3、4
P2：5、6、7、8、9、10
然而由于插人是顺序的，P1这个页中将不会再有记录被插人，从而导致空间的浪费。而P2又会再次进行分裂。

InnoDB存储引擎的Page Header中有以下几个部分用来保存插人的顺序信息:

• PAGE_ LAST_ INSERT
• PAGE_ DIRECTION
• PAGE_N_ DIRECTION

通过这些信息，InnoDB 存储引擎可以决定是向左还是向右进行分裂，同时决定将分裂点记录为哪-一个。若插人是随机的，则取页的中间记录作为分裂点的记录，这和之前介绍的相同。若往同-方向进行插人的记录数量为5,并且目前已经定位(cursor)到的记录( InnoDB存储引擎插入时，首先需要进行定位，定位到的记录为待插人记录的前一条记录)之后还有3条记录，则分裂点的记录为定位到的记录后的第三条记录，否则分裂点记录就是待插人的记录。来看一个向右分裂的例子，并且定位到的记录之后还有3条记录，则分裂点记录如图所示。

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

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

3.4 B+树索引的管理

3.4.1 索引管理

索引的创建和删除可以通过两种方法，一种是ALTER TABLE，另一种是CREATE/DROP INDEX。
通过ALTER TABLE创建索引的语法为:

ALTER TABLE tbl_ name 
｜ ADD {INDEXIKEY} [index_ name] 
[index_ type] (index_col_name,...) [index_ option] ...

ALTER TABLE tbl_ name 
DROP PRIMARY KEY 
| DROP {INDEX|KEY} index_ name

CREATE/DROP INDEX的语法同样很简单:

CREATE [UNIQUE] INDEX index_ name
[index_ type]
ON tbl_ name (index_ col_ name,...)

DROP INDEX index_ name ON tbl_ name

用户可以设置对整个列的数据进行索引，也可以只索引一个列的开头部分数据，如前面创建的表t,列b为varchar (8000)， 但是用户可以只索引前100个字段，如：

mysql>ALTER TABLE t
-> ADD KEY idx_ b (b(100)) ;

在表t，加一个对于列(a, c)的联合索引idx_ a_ c：

mysql>ALTER TABLE t
-> ADD KEY idx_a_c (a,c) ;

若用户想要查看表中索引的信息，可以使用命令SHOW INDEX。

mysql> SHOW INDEX FROM t;

★★★★★★★★★★★★★★★★★★★★★★★★★★★1. row *★★★★★★★★★★★★★★★★★★★★★★★★★★
Table: t
Non_ unique: 0
Key_ name: PRIMARY
Seq_ in_ index: 1
Column_ name: a
Collation: A
Cardinality: 2
Sub_ part: NULL
Packed: NULL
Null:
Index_ type: BTREE
Comment :
★★★★★★★★★★★★★★★★★★★★★★★★★★★2. row *★★★★★★★★★★★★★★★★★★★★★★★★★★
Table: t
Non_ unique: 1
Key_ name: idx_ b
Seq_ in_ index: 1
Column_ name: b
Collation: A
Cardinality: 2
Sub_ part: 100
Packed : NULL
Null: YES
Index_ type: BTREE
Comment:
★★★★★★★★★★★★★★★★★★★★★★★★★★★3. row *★★★★★★★★★★★★★★★★★★★★★★★★★★
Table: t
Non_ unique: 1
Key_ name: idx_ a_ c
Seq_ in_ index: 1
Column_ name: a
Collation: A
Cardinality: 2
Sub_ part: NULL
Packed: NULL
Null :
Index_ type: BTREE
Comment :
★★★★★★★★★★★★★★★★★★★★★★★★★★★4. row *★★★★★★★★★★★★★★★★★★★★★★★★★★
Table: t
Non_ unique: 1
Key_ name: idx_ a_ c
Seq_ in_ index: 2
Column_ name: c
Collation: A
Cardinality: 2
Sub_ part: NULL
Packed: NULL
Null :
Index_ type: BTREE
Comment :
★★★★★★★★★★★★★★★★★★★★★★★★★★★5. row *★★★★★★★★★★★★★★★★★★★★★★★★★★
Table: t
Non_ unique: 1
Key_ name: idx_ c
Seq_ in_ index: 1
Column_ name: c
Collation: A
Cardinality: 2
Sub_ part: NULL
Packed: NULL
Null :
Index_ type: BTREE
Comment :

通过命令SHOW INDEX FROM可以观察到表t上有4个索引，分别为主键索引、c列上的辅助索引、b列的前100字节构成的辅助索引，以及(a、c)的联合辅助索引。接着具体阐述命令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值非常关键，优化器会根据这个值来判断是否使用这个索引，以及Fast Index Creation、Online Schema Change、Online DDL也很重要，这里就不赘述，可看书籍《MySQL技术内幕-InnoDB存储引擎第2版》，在文章最后也会附上书籍链接。

4 B+树索引的使用

4.1 联合索引

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

例如，以下代码创建了一张t表，并且索引idx _a_b是联合索引，联合的列为（a，b）

CREATE TABLE t (
a INT,
bINT,
PRIMARY KEY (a) ,
KEY idx_a_b(a,b)
)ENGINE= INNODB

从本质上来说，联合索引也是一棵B+树，不同的是联合索引的键值的数量不是1，而是大于等于2。接着来讨论两个整型列组成的联合索引，假定两个键值的名称分别为a、b,如图所示。

数据按（a，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 WHERE b=xxx，则不可以使用这棵B+树索引。可以发现叶子节点上的b值为1、2、1、4、1、2，显然不是排序的，因此对于b列的查询使用不到(a, b)的索引。

联合索引的第二个好处是已经对第二个键值进行了排序处理。例如，在很多情况下应用程序都需要查询某个用户的购物情况，并按照时间进行排序，最后取出最近三次的购买记录，这时使用联合索引可以避免多一次的排序操作，因为索引本身在叶子节点已经排序了。来看一个例子，首先根据如下代码来创建测试表buy_ log。

CREATE TABLE buy_log (
userid INT UNSIGNED NOT NULL,
buy_date DATE
)ENGINE=InnoDB;

INSERT INTO buy_log VALUES(1, '2009-01-01');
INSERT INTO buy_log VALUES(2, '2009-01-01');
INSERT INTO buy_log VALUES(3, '2009-01-01');
INSERT INTO buy_log VALUES(1, '2009-02-01');
INSERT INTO buy_log VALUES(3, '2009-02-01');
INSERT INTO buy_log VALUES(1, '2009-03-01');
INSERT INTO buy_log VALUES(1, '2009-04-01');

ALTER TABLE buy_log ADD KEY(userid);
ALTER TABLE buy_log ADD KEY(userid, buy_date);

以上代码建立了两个索引来进行比较。两个索引都包含了userid字段。如果只对于userid进行查询，如：

SELECT * FROM buy_ log WHERE userid=2;

从图中可以发现，possible_ keys 在这里有两个索引可供使用，分别是单个的userid索引和(userid，buy__date)的联合索引。但是优化器最终的选择是索引userid，因为该索引的叶子节点包含单个键值，所以理论上一个页能存放的记录应该更多。

接着假定要取出userid为1的最近3次的购买记录，其SQL语句如下，执行计划如图所示。

SELECT * FROM buy_log WHERE userid=1 ORDER BY buy_date DESC LIMIT 3

同样的，对于上述的SQL语句既可以使用userid索引，也可以使用(userid, buy_date)索引。但是这次优化器使用了(userid， buy_ date) 的联合索引userid 2，因为在这个联合索引中buy_date 已经排序好了。根据该联合索引取出数据，无须再对buy_ date做一次额外的排序操作。若强制使用userid 索引，则执行计划如图所示。

在Extra选项中可以看到Using filesort,即需要额外的一次排序操作才能完成查询。而这次显然需要对列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

但是对于下面的语句，联合索引不能直接得到结果，其还需要执行一次filesort排序操作，因为索引(a, c)并未排序:

SELECT FROM TABLE WHERE a=xxx ORDER BY c

4.2 覆盖索引

InnoDB存储引擎支持覆盖索引( covering index,或称索引覆盖)，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。

注意：覆盖索引技术最早是在InnoDB Plugin中完成并实现。这意味着对于InnoDB版本小于1.0的，或者MySQL数据库版本为5.0 或以下的，InnoDB存储引擎不支持覆盖索引特性。

对于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 keyl, key2 FROM table WHERE keyl=xxx;
SELECT primary key1, primary key2, key2 FROM table WHERE key1=xxx;

覆盖索引的另一个好处是对某些统计问题而言的。还是对于上一小节创建的表buy_log，要进行如下的查询:

SELECT COUNT(*) FROM buy_log;

InnoDB存储引擎并不会选择通过查询聚集索引来进行统计。由于buy_log 表上还有辅助索引，而辅助索引远小于聚集索引，选择辅助索引可以减少I0操作，故优化器的选择为如图所示。

通过上图可以看到，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查询是统计操作，并且可以利用到覆盖索引的信息，因此优化器会选择该联合索引，其执行计划如图所示。

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

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

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

SELECT FROM orderdetai is WHERE orderid>10000 and orderid<102000;

上述这句SQL语句查找订单号大于10000的订单详情，通过命令SHOWINDEX FROM orderdetails,可观察到的索引如图所示。

可以看到表orderdetails有(OrderID, ProductID) 的联合主键，此外还有对于列OrderID的单个索引。上述这句SQL显然是可以通过扫描OrderID.上的索引进行数据的查找。然而通过EXPLAIN命令，用户会发现优化器并没有按照OrderID上的索引来查找数据，如图所示。

在possible_ keys-列可以看到查询可以使用PRIMARY、OrderID、OrdersOrder_Details三个索引，但是在最后的索引使用中，优化器选择了PRIMARY聚集索引，也就是表扫描(table scan),而非OrderID辅助索引扫描( index scan)。

这是为什么呢?原因在于用户要选取的数据是整行信息，而OrderID索引不能覆盖到我们要查询的信息，因此在对OrderID索引查询到指定数据后，还需要一次书签访问来查找整行数据的信息。虽然OrderID索引中数据是顺序存放的，但是再一次进行 书签查找的数据则是无序的，因此变为了磁盘上的离散读操作。如果要求访问的数据量很小，则优化器还是会选择辅助索引，但是当访问的数据占整个表中数据的蛮大一部分时(一般是20%左右)，优化器会选择通过聚集索引来查找数据。因为之前已经提到过，顺序读要远远快于离散读。

因此对于不能进行索引覆盖的情况，优化器选择辅助索引的情况是，通过辅助索引查找的数据是少量的。这是由当前传统机械硬盘的特性所决定的，即利用顺序读来替换随机读的查找。若用户使用的磁盘是固态硬盘，随机读操作非常快，同时有足够的自信来确认使用辅助索引可以带来更好的性能，那么可以使用关键字FORCE INDEX来强制使用某个索引，如:

SELECT * FROM orderdetails FORCE INDEX (OrderID) WHERE orderid>10000 and orderid<102000;

4.4 索引提示

MySQL数据库支持索引提示(INDEX HINT),显式地告诉优化器使用哪个索引。个人总结以下两种情况可能需要用到INDEX HINT：

• MySQL数据库的优化器错误地选择了某个索引，导致SQL语句运行的很慢。这种情况在最新的MySQL数据库版本中非常非常的少见。优化器在绝大部分情况下工作得都非常有效和正确。这时有经验的DBA或开发人员可以强制优化器使用某个索引，以此来提高SQL运行的速度。
• 某SQL语句可以选择的索引非常多，这时优化器选择执行计划时间的开销可能会大于SQL语句本身。例如，优化器分析Range查询本身就是比较耗时的操作。这时DBA或开发人员分析最优的索引选择，通过IndexHint来强制使优化器不进行各个执行路径的成本分析，直接选择指定的索引来完成查询。在MySQL数据库中Index Hint的语法如下:

tbl_name [[AS] alias] [index_hint_list]
index_hint_list:
index_hint [, index_ hint]
index_hint:
USE {INDEX|KEY}
[{FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
| GNORE {INDEX|KEY}
[{FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
I FORCE ( INDEX IKEY )
[{FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
index_ list:
index_name [, index_ name]...

接着来看一个例子，首先根据如下代码创建测试表t,并填充相应数据.

CREATE TABLE t (
a INT,
b INT,
KEY(a),
KEY(b)
)ENGINE=INNODB;

INSERT INTO t SELECT 1, 1;
INSERT INTO t SELECT 1, 2;
INSERT INTO t SELECT 2, 3;
INSERT INTO t SELECT 2, 4;
INSERT INTO t SELECT 1, 2;

然后执行如下的SQL语句:

SELECT * FROM t WHERE a=1 AND b=2;

上图中的列possible_keys 显示了上述SQL语句可使用的索引为a, b，而实际使用的索引为列key所示，同样为a, b。也就是MySQL数据库使用a, b两个索引来完成这一个查询。列Extra提示的Using intersect (b, a) 表示根据两个索引得到的结果进行求交的数学运算，最后得到结果。如果我们使用USE INDEX的索引提示来使用a这个索引，如:

SELECT * FROM t USE INDEX(a) WHERE a=1 AND b=2;

可以看到，虽然我们指定使用a索引，但是优化器实际选择的是通过表扫描的方式。因此，USE INDEX只是告诉优化器可以选择该索引，实际上优化器还是会再根据自己的判断进行选择。而如果使用FORCE INDEX的索引提示，如:

SELECT FROM t FORCE INDEX (a) WHERE a=1 AND b=2;

可以看到，这时优化器的最终选择和用户指定的索引是一致的。因此，如果用户确定指定某个索引来完成查询，那么最可靠的是使用FORCE INDEX，而不是USE INDEX。

5结语

对于索引，在《MySQL技术内幕-InnoDB存储引擎第2版》中还有Multi-Range-Read优化、Index Condition Pushdown(ICP)优化以及全文检索等内容，这里就不再赘述了。

数据结构可视化网站及《MySQL技术内幕-InnoDB存储引擎第2版》一书PDF的链接：

数据结构可视化网站

《MySQL技术内幕-InnoDB存储引擎第2版》密码：bm4v