Mysql深度讲解InnoDB引擎与Index索引（四）

前言

上篇【Mysql深度讲解InnoDB引擎与Index索引（三）】已经从理论上说明了索引能够对我们查询的效率起到比较大的帮助，以及一些基本的索引创建原则，那么有没有一个比较好的办法直观的比较两个索引的效率呢？当然是有的，它就是索引的选择性，本篇就聊聊如何建一个高效的索引。注：本篇创建的表和索引在附录里，请对照观看。更多Mysql调优内容请点击【Mysql优化-深度讲解系列目录】。

选择性

一般来说要建一个高效的索引，首先要看这个列里面的重复值是否很多，索引需要基于不重复的值才能达到最高效率，过滤哪些列能够建索引的指标就叫做索引的选择性(Selectivity)。选择性的计算简单来说是指不重复的索引值(也叫基数，Cardinality)与表记录数的比值：

索引选择性 = 基数 / 记录数

选择性的取值范围为(0, 1]，选择性越高的索引价值越大。如果选择性等于1，就代表这个列的不重复值和表记录数是一样的，也就是说该列没有重复值，那么对这个列建立索引是非常合适的。如果选择性非常小，那么就代表这个列的重复值是很多的， 不适合建立索引。也就是选择性越大(越接近1)，索引的效率越高。如下建了一个B+树父节点都相同的索引，完全没有任何意义。

索引的空间

比如设想这样一张表：country表只有一个主键索引PRIMARY(此表为Mysql自带的表)。

在目前的条件下我想查询一个国家的名字(Name字段)对应的国家的政体(GovernmentForm字段)，只能进行全表扫描，比如：
explain select name,GovernmentForm from country;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	country	NULL	ALL	NULL	NULL	NULL	NULL	239	100.00	NULL

那么可以对或建立索引，看下两个索引的选择性：

SELECT count(DISTINCT(concat(GovernmentForm)))/count(*) AS Selectivity FROM country;  -- 0.1464
SELECT count(DISTINCT(concat(name,GovernmentForm)))/count(*) AS Selectivity FROM country; -- 1.0000

可以看到，如果我们选择只针对GovernmentForm做索引，选择性是非常低的只有0.1464。如果对Name和GovernmentForm做一个联合索引，那么选择性就直接飙到了最高1。但是还有一个问题，可以看到表设计里面Name字段是char类型Latin字符集占用了52Byte，而GovernmentForm字段占用的也不少有45Byte。这样索引一个节点的长度就有可能达到97Byte，我们如果建立了这样一个全值索引，需要的容量开销也是不小的。有没有办法做到选择性非常高，而占用的空间有比较小的办法呢？

前缀索引

上面的问题，是可以解决的，即前缀索引。这里的前缀索引，并不是之前说的最左原则。而是对某一列的前几个字符进行创建索引，就是用列的前缀代替整个列作为索引key。当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。根据这个前缀索引的策略，我们选择使用Name的前10Byte和GovernmentForm的前10Btye构建一个前缀索引，首先先看下选择性：

SELECT count(DISTINCT(concat(left(name,10),left(GovernmentForm,10))))/count(*) AS Selectivity FROM country; -- 1.0000

选择性依然是无比强大的1，但是我们索引的节点长度最大值已经减小为20Byte了，几乎缩小了5倍，但是选择性依然是1。那么如何建一个前缀索引呢？使用如下语法就可以了，本例中指定索引字段的长度为10即可。

ALTER TABLE country ADD INDEX `idx_cty_nm_gf` (name(10),GovernmentForm(10));

但是前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于覆盖索引。因为这个两个关键字都是对完整的列值进行排序和查找的，前缀索引由于缺乏了部分数据，因而无法应用于这些关键字的查询。

覆盖索引

所谓的覆盖索引是指一个查询的状态，说的是一个查询语句中所有查询的列，和所有条件(where, in, join等等)涉及的列都可以利用索引，也就是被索引覆盖掉。这种查询称为覆盖索引，并不单单指聚簇索引，或者二级索引，也可以指二者通用，只要这次查询的结果不需要进行回表操作，一般来说就可以被称为覆盖索引查询。这点可以用explain关键字返回的Extra列看出来，覆盖索引Extra列显示的是‘Using index’。

回表

再介绍更多索引操作的时候，先说下什么叫做回表。之前几篇文章已经分析过，辅助索引找出来的最终是主键，通过主键再去主键索引里面查找真实的数据，这个过程就被称作回表。基本上Mysql通过二级索引查询都需要需要进行回表操作。

匹配范围值

select * from t1 where b > 1 and b < 20000;

由于我们建立的B+树中的数据页和记录是先按 b列 排序的，所以我们上边的查询过程其实是这样的：

• 首先找到 b列 值为1的记录。
• 然后找到 b列 值为20000的记录。
• 由于所有记录都是由链表连起来的（记录之间用单链表，数据页之间用双链表），所以他们之间的记录都可以很容易的取出来。
• 找到这些记录的主键值，再到聚簇索引中回表查找完整的记录。

不过在使用联合进行范围查找的时候需要注意，如果对多个列同时进行范围查找的话，只有对索引最左边的那个 列进行范围查找的时候才能用到B+树索引，比如：

select * from t1 where b > 1 and c > 1;

上边这个查询可以分成两个部分：

1. 通过条件b > 1来对b进行范围，查找的结果可能有多条b值不同的记录。
2. 对这些b值不同的记录继续通过c > 1继续过滤。

这样子对于联合索引来说，只能用到 b列 的部分，而用不到 c列 的部分，因为只有b值相同的情况下才能用 c列 的值 进行排序，而这个查询中通过 b列 进行范围查找的记录中可能并不是按照 c列 进行排序的，所以在搜索条件中继续以 c列 进行查找时是用不到这个B+树索引的。

精确匹配某一列并发未匹配另一列

对于同一个联合索引来说，虽然对多个列都进行范围查找时只能用到最左边那个索引列，但是如果左边的列是精确查找，则右边的列可以进行范围查找。比如下面的sql，当b=1确定一个结果集以后，那么c>1就可以利用索引idx_t1_bcd了。

select * from t1 where b = 1 and c > 1;

排序 order by

例如一个排序sql是如何利用索引的呢？

select * from t1 order by b, c, d;

这个查询的结果集需要先按照 b列 值排序，如果记录的 b列 值相同，则需要按照 c列 来排序，如果 c列 的值相同，则需要按照 d列 排序。因为这个B+树索引本身就是按照上述规则排好序的，所以直接从索引中提取数据，然后进行回表操作取出该索引中不包含的列就好了。

分组 group by

分组利用索引的方式和排序类似，比如：

select b, c, d, count(*) from t1 group by b, c, d;

这个查询语句相当于做了3次分组操作：

1. 先把记录按照 b列 值进行分组，所有 b列 值相同的记录划分为一组。
2. 将每个 b列 值相同的分组里的记录再按照 c列 的值进行分组，将 c列 值相同的记录放到一个分组里。
3. 再将上一步中产生的分组按照 d列 的值分成更小的分组。

如果没有索引的话，这个分组过程全部需要在内存里实现，而如果有索引的话，正好这个分组顺序又和B+树中的索引列的顺序是一致的，所以可以直接使用B+树索引进行分组。但是对于联合索引有个问题需要注意，ORDER BY的子句后边的列的顺序也必须按照索引列的顺序给出，如果给出 order by c, b, d 的顺序，也是用不了B+树索引的。同理， order by b或者order by b, c 这种匹配索引左边的列的形式可以使用部分的B+树索引。当联合索引 左边列的值为常量，也可以使用后边的列进行排序，比如这样：

select * from t1 where b = 1 order by c, d;

这个查询能使用联合索引进行排序是因为 b列 的值相同的记录是按照 c列, d列 排序的。

ASC & DECS索引

对于使用联合索引进行排序的场景，我们要求各个排序列的排序顺序是一致的，也就是要么各个列都是ASC规则 排序，要么都是DESC规则排序。ORDER BY子句后的列如果不加ASC或者DESC默认是按照ASC排序规则排序的，也就是升序排序的，比如下面这个查询就是用不到索引的。

select * from t1 order by b ASC, c DESC;

说到了ASC和DECS关键字，InnoDB中的索引并不单单是一个B+树，而是一个B+树的变种，因为其最后的叶子指针是一个双向指针，用来快速查找逆序，如下图。

修改默认的索引(index hint)

修改使用索引use关键字：
explain select * from t1 use index(idx_t1_bcd) where a=1 and b=6;
如果use index(idx_t1_bcd)里面指定了索引，那么就会使用那个索引。而且这个语法里可以写多个索引，查询优化器只会根据指定的索引进行成本的计算。本例中就是只会用并且计算idx_t1_bcd的结果，即便有其他的索引也不会使用。

---

与之类似的force关键字：
explain select * from t1 force index(idx_t1_bcd) where a=1 and b=6;
强制使用某个索引，这里只能指定使用一个索引。

---

忽略索引ignore关键字：
explain select * from t1 ignore index(idx_t1_bcd) where a=1 and b=6;
忽略使用某个索引，这里能指定忽略多个索引。

---

总结

总之建立索引的规律就是：

• 索引列的类型尽量小
• 利用索引字符串值的前缀
• 主键自增
• 定位并删除表中的重复和冗余索引
• 尽量使用覆盖索引进行查询，避免回表带来的性能损耗。

附：本例中构建的表：

create table t1(
a int primary key,
b int,
c int,
d int,
e varchar(20)
) engine=InnoDB;
create index idx_t1_bcd on t1(b,c,d);
insert into t1 values(12,1,2,4,'a');
insert into t1 values(6,4,5,4,'b');
insert into t1 values(9,1,1,1,'c');
insert into t1 values(1,6,7,4,'d');
insert into t1 values(15,2,2,5,'e');
insert into t1 values(7,9,3,6,'f');
insert into t1 values(4,2,1,7,'g');
insert into t1 values(3,3,3,3,'h');
insert into t1 values(10,5,5,5,'ss');

create table t2(
a int primary key,
b int,
c int,
d int,
e varchar(20)
) engine=InnoDB;
insert into t2 values(1,6,7,4,'d');
insert into t2 values(4,2,1,7,'g');
insert into t2 values(3,3,3,3,'h');