MySQL技术内幕 InnoDB存储引擎：B+树索引的使用- 数据库

1、联合索引

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

CREATE TABLE t（ a INT, b INT,
PRIMARY KEY（a）,
KEY idx_a_b（a, b）
）ENGINE=InnoDB

多个键值的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+树索引。

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

2、覆盖索引（covering index）

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

对于InnoDB存储引擎的辅助索引而言，由于其包含了主键信息，因此其叶子节点存放的数据为（primary key1，priamey key2，…,key1，key2，…）。例如，下面语句都可仅使用一次辅助联合索引来完成查询

SELECT key2 FROM table WHERE key1=xxx;
SELECT primary key2,key2 FROM table key1=xxx;
SELECT primary key1,key2 FROM table key1=xxx;
SELECT primary key1,primary key2,key2 FROM table key1=xxx;

覆盖索引的另一个好处是对某些统计问题而言，如上的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);
5
6
7
8
9
10
11
12
13

explain SELECT COUNT(*) FROM buy_log;
1

InnoDB存储引擎并不会选择通过查询聚集索引来进行统计。由于buy_log还有辅助索引，而辅助索引远小于聚集索引。选择辅助索引可以减少IO操作。
在这里插入图片描述
如图显示。possible_keys是NULL，但是实际执行优化器却选择了userid，而列Extra的Using index就是代表优化器选择了覆盖索引的操作。

3、索引提示

MySQL数据库支持索引提示(INDEX HINT)显式的高速优化器使用了哪个索引。以下是可能需要用到INDEX HINT的情况

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