B+树索引实战篇-Part3(索引用於排序与分组、回表的代价、进一步创建与使用索引)

前情提要-我们前面为了方便解释，建了个表还有索引

mysql> create table single_table(
    -> id int not null auto_increment,
    -> key1 varchar(10),
    -> key2 int,
    -> key3 varchar(100),
    -> key_part1 varchar(100),
    -> key_part2 varchar(100),
    -> key_part3 varchar(100),
    -> common_field varchar(100),
    -> primary key (id),
    -> key idx_key1 (key1),
    -> unique key uk_key2 (key2),
    -> key idx_key3 (key3),
    -> key idx_key_part(key_part1, key_part2, key_part3)
    -> ) engine=InnoDB charset=utf8;
Query OK, 0 rows affected, 1 warning (0.05 sec)

为id列建立的聚簇索引
为key1列建立的idx_key1二级索引
为key2列建立的uk_key2二级索引，且是唯一
为key3列建立的idx_key3二级索引
为key_part1、key_part2、key_part3列建立的idx_key_part二级索引，这也是个联合索引

索引用於排序

我们在编写查询叙述时，经常需要使用order by子句对查询出来的纪录按规则排序。在一般情况下，我们只能把纪录载入到记忆体中，并使用排序演算法对其排序，当空间不够的时候会先将结果暂存在磁碟中，排序完成後再返回用户端。
但如果order by子句中使用了索引列，有可能省去在记忆体中排序的动作。

看看几个直接使用联合索引排序，不需要再在记忆体排序的查询
1.

select * from single_table order by key_part1, key_part2, key_part3 limit 10;

这个查询的结果需要先照key_part1的值小到大排序，key_part1的值相等再照key_part2的值小到大排序，最後key_part1跟key_part2的值都相等，再照key_part3的值小到大排序。而我们都知道联合索引idx_key_part的顺序也就跟其一模一样，因此我们不需要额外再排序，只需要从第一笔idx_key_part二级索引纪录开始，沿着单向链结串列往後找10笔即可。
同理只是order by key_part1和order by key_part1,key_part2这些仅对联合索引的索引列中左边连续的列进行排序，也是可以直接利用B+树索引，不需额外排序。
那如果是反过来order by key_part3, key_part2, key_part1就有所冲突，不行使用索引。

select * from single_table where key_part1 = 'a' and key_part2 = 'b' order by key_part3 limit 10;

另外当联合索引左边连续的列为常数时，也可以使用联合索引对右边的列进行排序(因为顺序是相同的)。

看看几个不能使用联合索引进行排序的查询

1. asc、desc混用不能用。
   很明显的我们排序要嘛就是照asc，要嘛就是照desc，混在一起的话需要使用复杂的演算法，并无法高效率的利用索引。(但MySQL8.0引入了一种称为Descending Index的特性，可以支援order by子句中asc、desc混用的情况。有兴趣可以参考官方文件)
2. 排序列包含的列分属於不同的索引不能用
3. 排序列属於某个联合索引的索引列但没有连续(key_part1及key_part3中间还有个key_part2)不能用。
   如以下查询

select * from single_table order by key_part1, key_part3 limit 10;

4. 用来形成扫描区间的索引列与排序列不同不能用

select * from single_table where key1 = 'a' order by key2 limit 10;

5. 排序列不是以单独列名称的形式出现在order by子句中不能用

select * from single_table order by upper(key1) limit 10;

索引用於分组

select key_part1,key_part2,key_part3, count(*) from single_table group by key_part1, key_part2, key_part3;

这个查询叙述相当於执行了3次分组操作。
分组的概念跟排序是一样的，当分组的顺序也与联合索引idx_key_part一致的话(key_part1 => key_part2 => key_part3)，那就可以直接使用联合索引idx_key_part进行分组，不需要再建立临时表来储存扫描聚簇索引时的中间结果(由外至内为大中小组别，大组是key_part1，key_part1相同的纪录再依照key_part2细分为中组，key_part1与key_part2相同的纪录再依照key_part3细分为小组)。

回表的代价

对於下面这个查询叙述来説

select * from single_table where key1 > 'a' and key1 < 'c';

我们可以选择以下两种方式来执行。

1. 全资料表扫描。直接扫描全部的聚簇索引纪录，检查每一笔是否符合条件，成立就发送到用户端，否则跳过该纪录。
2. 使用idx_key1索引执该查询。可以根据搜索条件key1 > 'a' and key1 < 'c'得到对应的扫描区间('a','c')，然後扫描该区间中的二级索引纪录。由於idx_key1索引的叶子节点储存的是不完整的使用者纪录，仅包含key1、id两列，而查询列表是*，表示我们每获取一笔二级索引纪录都要进行回表操作以获得完整的使用者纪录。

如果需要执行回表操作的纪录越多，比如key1值在'a'跟'c'之间的使用者数量占全部数量的99%以上，则使用二级索引查询的效能也就会越差，此时倒不如直接全资料表扫描反而比较快。
至於什麽时候该全资料表扫描？什麽时候该使用二级索引+回表的方式？这就是查询最佳化工具要做的事情。查询最佳化工具会先针对表中的纪录计算一些统计资料，然後再利用这些统计资料或存取表中少量纪录来计算需要回表操作的纪录数。这个工具之後会再进一步説明，大家先有个概念就好。

一般情况下，可以给查询叙述指定limit子句限制返回的纪录数，这会让查询最佳化工具倾向於选择使用二级索引+回表的方式。原因是回表的纪录越少，查询性能越好。
上述叙述会改成像这样

select * from single_table where key1 > 'a' and key1 < 'c' limit 10;

而对於需要对结果进行排序的查询，如果再采用二级索引执行查询时需要回表操作的纪录过多。
查询最佳化工具则倾向於使用全资料表扫描的方式来执行。
像下面的叙述

select * from single_table where order by key1;

进一步创建与使用索引

1. 只为用於搜索、排序或分组的列创建索引。
   我们只为出现在where子句中的列、连接子句中的连接列、或出现在order by或group by子句中的列创建索引。仅出现在查询列表中的列就没必要建立索引了。像以下的叙述

select common_field, key_part3 from single_table where key1 = 'a';

查询列表中的common_field, key_part3就没必要创建索引。我们只要为where子句中的key1列创建索引即可。

2. 考虑索引列中不重复值的个数。
   前面说到，在透过二级索引+回表的方式执行查询时，某个区间的二级索引数量过多的时候，效能会越差，所以在创建索引时，需要考量到该列中不重复值的个数占全部纪录的比例，如果太低，表示有大量重复值，则会造成执行太多回表操作，速度太慢。

3. 索引列的类型尽量越小越好(因为越小，索引占用的储存空间就越小，一个资料页就能存放更多的纪录，磁碟I/O带来的性能损耗也越小，读写效率也越高)，能使用INT就不要使用BIGINT，能使用MEDIUMINT就不要使用INT。

4. 只为索引列字首创建索引，以减少索引占用的储存空间。
   像这样只用前10个字元来当索引(当字元较多的时候就可以明显减少索引的大小)

alter table single_table drop index idx_key1;
alter table single_table add index idx_key1(key1(10));

但由於只有前十个字元资料不完整，所以下面这个查询就不能使用索引来完成排序了

select * from single_table order by key1 limit 10;

5. 尽量使用覆盖索引进行查询，以避免回表带来的性能损耗。
   为了彻底告别回表操作带来的性能损耗，建议最好在查询清单中只包含索引列有的栏位，如下面这样子

select key1,id from single_table where key1 > 'a' and key1 < 'c';

由於二级索引列中的值就已包含了key1,id，因此不需要再去回表操作取得完整资料，这种索引中已包含所有需要读取的列的查询方式就称为覆盖索引。

排序操作也优先使用覆盖索引进行查询。如下

select key1 from single_table oredr by key1;

6. 让索引列以列名称的形式在搜索条件中单独出现。

select * from single_table where key2 * 2 < 4;
select * from single_table where key2 * 4/2;

在第一个查询key2列并不是以单独列名称出现，而是key2 * 2这样的形式，MySQL并不会尝试简化key2 * 2 < 4这个运算式，而是会直接认爲这个搜索条件不能形成合适的扫描区间，所以会用全表搜索的方式来执行。

但在第二个查询，MySQL可以分析出如果使用uk_key2执行查询，对应的扫描区间就是(-无限大,2)，可以减少扫描的数量，所以会使用uk_key2索引来执行。因此请让索引列以列名称的形式在搜索条件中单独出现比较好。

7. 新插入纪录主键大小对效率的影响。
   假设某个资料页储存的聚簇索引纪录已经满了(总共100笔)，它储存的主键值在1~100之间，此时要再插入一笔主键值为9的纪录，应要插入在主键值为8跟10的纪录之间，但该页已经满了，无法插在该页8跟10之间，所以需要执行页分裂的动作(新增一页并把较大的纪录移动到新页)，而每次的页分裂都是一个性能损耗。
   因此如果想降低性能损耗，最好让插入的主键值依次递增。就像single_table的主键id列具有auto_increment属性那样会自动增加。

8. 定位并删除表中的容错和重复索引。

alter table single_table add index idx_key_part1(key_part1);

我们针对key_part1列建立一个idx_key_part1索引
其实我们已经有一个针对key_part1、key_part2、key_part3建立的联合索引idx_key_part。
idx_key_part索引的二级索引纪录本身就是按照key_part1列的值排序的，所以完全不需要单独为key_part1列再建立一个索引(也叫容错索引)。

有时可能会对同一个列创建多个索引

alter table single_table add index unique key uk_id(id);

可是id列本身就是single_table的主键(不重复)，所以这个唯一二级索引uk_id就是不需要的。

以上这些容错索引跟重复索引都要避免掉。

今天整个B+树索引的部分就告一段落了，不知道大家吸收得如何？
如有任何问题欢迎发问唷！

接下来会开始学习MySQL的资料目录，请大家敬请期待^^