开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第05天，点击查看活动详情

01-使用索引的代价

在之前介绍索引时我们说过，索引是为了提高查询效率而出现的优化技术。然而这种优化不是没有任何代价的。显而易见的是：

维护索引带来的复杂性，特别在插入、删除时，有可能会发生页分裂、合并。
额外的空间消耗。每创建一个索引，就会建立一个 B+ 树。

建立聚簇索引 B+ 树的大致过程如下：

创建一个 Root 节点（根页面）。初始时，其中不包括任何索引、也不包括任何记录。
随着用户向表中插入数据，记录首先存放到根页面中。
当根页面中的空间用完时，继续插入数据，此时根页面中的所有记录复制到一个新页（临时页）页 a。
然后对页 a 进行页分裂操作，得到另一个新页面 b。（注：页分裂需要复制操作，而且会导致页空间利用率降低，每个页大约空闲50%）
根节点升级为索引页面。
继续插入数据，记录会根据主键大小插入到页面 a 或 b 中。

建立二级索引的过程与上述过程类似，不同的是记录存储的数据。

使用索引的另外一个不太容易发现的代价就是回表。 InnoDB 中二级索引叶子节点中存储的是索引列、主键列的值。如果要查询的信息包含除索引列、主键列外的其他列信息，则需要通过主键列到聚簇索引上查询对应的列信息。回表的代价主要体现在两个方面：

需要用到两个索引进行检索，一个二级索引，一个聚簇索引；
访问二级索引一般会是顺序 I/O 方式读磁盘，再访问聚簇索引的话大概率是随机 I/O 方式，所以性能不高。

有时候，如果需要回表的次数太多，InnoDB 宁愿选择全表扫描聚簇索引的方式进行查询。为避免使用二级索引时的性能问题，在设计二级索引、使用二级索引的时候要相当地谨慎，并尽可能的使用覆盖索引。

02-索引的优化技术

02.1-索引覆盖

索引覆盖是避免回表的一种优化手段。我们通过一个例子来说明，索引覆盖是如何避免回表的。这里我们借用《MySQL 是怎样运行的：从根儿上理解 MySQL》中的 person_info 表定义：

CREATE TABLE `person_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `birthday` date NOT NULL,
  `phone_number` char(11) NOT NULL,
  `country` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_birthday_phone_number` (`name`,`birthday`,`phone_number`)
) ENGINE=InnoDB;

在 person_info 中创建了一个 name\birthday\phone_number 三列组成的联合索引。该索引生成的 B+ 树叶子节点中，存储的是上述三列值和主键 id 列的值。当我们执行如下语句时：

select name, birthday, phone_number from person_info;

只需要使用到 idx_name_birthday_phone_number 这个二级索引，不需要回表。我们可以通过 explain 验证下我们的结论。

--- explain select name, birthday, phone_number from person_info;
+----+-------------+-------------+------------+-------+---------------+--------------------------------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type  | possible_keys | key                            | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+-------+---------------+--------------------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | person_info | NULL       | index | NULL          | idx_name_birthday_phone_number | 449     | NULL |    1 |   100.00 | Using index |
+----+-------------+-------------+------------+-------+---------------+--------------------------------+---------+------+------+----------+-------------+

其中 Extra 中的"Using index"就表示使用了覆盖索引。如果我们增加一个索引列以外的列，就需要回表了，例如：

--- explain select name, birthday, phone_number, country from person_info;
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | person_info | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+

country 在 idx_name_birthday_phone_number 索引中找不到，所以必须回表，到聚簇索引上查询。

02.2-最左前缀原则

对于联合索引，例如上节中的 idx_name_birthday_phone_number，有一个最左前缀原则。满足最左前缀原则，就可以利用索引进行检索，从而加速检索的过程。

回到上节的 person_info 表，我们建立了 idx_name_birthday_phone_number 联合索引。在这个索引中，按照 name\birthday\phone_number 的顺序进行排序。我们来看下以下几种语句是否能够用到这个索引：

-- 满足最左前缀原则，可以使用 idx_name_birthday_phone_number 索引
select name, birthday, phone_number from person_info where name like '张%';
-- 不满足最左前缀原则，不能使用 idx_name_birthday_phone_number 索引
select name, birthday, phone_number from person_info where phone_number = '13100000000';

基于上述讨论，在建立联合索引时，需要合理安排索引内的字段的顺序。引用《MySQL 实战 45 讲》中的描述：第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

02.3-索引下推

什么是索引下推？简单来说就是在遍历索引的时候，先根据条件将一部分不满足的记录筛掉，减少回表的次数。这样说起来可能不好理解，我们继续使用上面的 person_info 表来解释一下。

假设我们要从 person_info 表中查询所有姓张，手机号以131开头的，居住地（country）在中国的人员信息。写成 SQL 语句是：

select * from person_info where name like '张%' and phone_number like '131%' and country = '中国';

上述语句是满足最左前缀规则的，可以使用 idx_name_birthday_phone_number 索引找到第一个姓张的人的信息。然后，根据链表向后遍历，直到找到所有姓张的人的信息，假设有 n 条。

在 MySQL 5.6之前的版本，需要拿着 n 条主键信息回表到聚簇索引上，然后在根据条件筛选复合要求的（即手机号以131开头，居住地在中国）。 MySQL 5.6引入了索引下推，在遍历 idx_name_birthday_phone_number 索引时，可以先根据已有信息对记录进行筛选（索引中包含 phone_number，可以先筛选所有131开头的）。返回 m < n 条主键信息，再去聚簇索引回表，可以减少回表的次数，从而提高查询的效率。

通过 explain 检查上述语句，结果的 Extra 列值为 "Using index condition"，说明使用了索引下推。

02.4-给字符串列增加索引

当给字符串类型的列增加索引时，最需要考虑的是字符串列的长度问题。如果该列是较大长度的值，那么索引占用的空间就比较多，导致空间浪费问题。此时可以考虑的措施是，为字符串列建立前缀索引，例如：

-- 以电话号码前8为建立索引，这里仅作为一个前缀索引的示意，可能并不具备业务上的意义
alter table person_info add index idx_phone_number(phone_number(8));

使用前缀建立索引，索引中存储的就只是该字段的前缀部分，因此需要回表查询具体的内容。结合具体的业务规则，合理地使用前缀索引，可以在不损失业务的前提下，降低索引的空间消耗，提高索引检索效率（索引字段占空间小，每页中存储的记录就会更多）。

03-如何挑选索引

建立索引时，有一些一般原则或者说是建议可以参考：

只为那些查询、排序、分组的列建立索引
建立索引时，需要考虑列可选值的基数，即区别度。区别度越大，索引的效果越好。
因为索引列的值会存储在 B+ 树中，因此，列的长度也是考量的元素之一。如果同等业务效果下，尽量选择列长度较小的。
对字符串类型的列，可以考虑建立前缀索引。
主键最好选择自动、递增，这样在建立聚簇索引时，可以只追加，而不需要处理页分裂。

MySQL「05」索引的使用及高级主题