深入理解 MySQL 索引(二)

索引字段的查询方式

上篇文章我们介绍了什么是索引，索引的结构以及一些关于索引的细节问题，那 MySQL 又是怎么利用索引进行查询的，来看一个具体的查询示例，下面是 users 表的建表语句:

CREATE TABLE `users` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `identity` varchar(18) NOT NULL COMMENT '身份证号',
  `name` varchar(32) NOT NULL COMMENT '用户名',
  `age` int DEFAULT '0' COMMENT '年龄',
  `sex` tinyint(1) NOT NULL DEFAULT '0' COMMENT '性别 0：男，1：女',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `address` varchar(128) NOT NULL DEFAULT '' COMMENT '地址',
  PRIMARY KEY (`id`),
  KEY `idx_identity` (`identity`),
  KEY `idx_name` (`name`, `age`)
) ENGINE=InnoDB AUTO_INCREMENT=0 COMMENT='用户表';
# 初始化数据
INSERT INTO users VALUES
(1, '362422199401014501', '张三', 40, 0, '1994-01-01', ''),
(2, '362422199402024502', '李四', 27, 0, '1994-02-02', ''),
(3, '362422199403034503', '王五', 27, 1, '1994-03-03', ''),
(4, '362422199404044504', '赵六', 27, 1, '1994-04-04', ''),
(5, '362422199505054505', '王五', 26, 1, '1994-05-05', '');

从建表语句可以看到 users 表有三个索引，主键索引 id，两个普通索引 idx_identity 和 idx_name，假设这三棵索引树目前对应的索引结构分别如下:

![identity 索引树](/Users/xxx-xifan/Dropbox/drawio/images/identity 索引树.png)

![name, age 联合索引树](/Users/xxx-xifan/Dropbox/drawio/images/name, age 联合索引树.png)

现在有这样一个 SQL 查询语句，根据身份证号查询用户信息:

1	select * from users where identity = '362422199403034503';

在 identity 条件字段上建有索引，于是可以利用索引加快搜索，通过 idx_identity 索引树定位到第二个叶子节点，取出第一条记录 4502，小于 4503 不满足条件，取下一个值 4503，满足条件，取出主键 id = 3，然后回到主键索引树中查询得到 id = 3 的数据 data3，再取下一个值 4504，大于 4503 不满足条件，查询结束，最终查到 identity = ‘362422199403034503’ 的数据记录 data3。

在上面的查询过程中，通过 idx_identity 索引树查询到主键值后，由于我们需要查询这条记录完整的数据值，因此还需要利用主键字段的值回到主键索引树再搜索一次才能得到具体的数据，这个过程就是回表的过程。

索引的几种优化方式

索引覆盖
在上面的查询中，如果我们的业务需求主要是根据身份证查询用户姓名，而不需要其他字段的值，比如下面的 SQL 语句:
1
select name from users where identity = '362422199403034503';
这时我们就可以建一个 (identity, name) 的联合索引，达到索引已经包含要查询的字段，这时就可以利用索引覆盖的特性，而不需要再到主键索引树中查询该记录完整的数据，减少回表的过程，极大的提高了查询效率。当然建立联合索引也是有代价的，首先存储空间毫无意外增加了，同时索引的维护代价也增加了，现在需要维护两个字段的索引，任意一个字段发生变化都有可能需要调整索引的结构，但一般如果对性能的提升很大的话，牺牲一点存储空间也就无所谓了。
接着看下面两个 SQL 语句是否也能使用索引覆盖的特性:
1
2
1. select id, identity, name from users where identity = '362422199403034503';
2. select id, identity, name from users where identity = '362422199403034503' and age = 30;
对于 SQL1 来说，我们需要查询 id，identity，name 三个字段的值，(identity,name) 联合索引已经包含了 identity，name 两个字段，id 也是可以从叶子节点中直接获取得到，因此是可以使用到索引覆盖的特性。

对于 SQL2 来说，虽然查询的字段值都已经覆盖了，但还有一个 age = 30 的条件，age 字段的值光从联合索引是没办法拿到的，只能通过先找到满足 identity 条件的主键的值，再通过主键值搜索主键索引树拿到整条记录的值来判断 age 是否满足条件，满足条件则返回，不满足则丢弃，接着继续获取下一个满足 identity 条件的值。

值得注意的是覆盖索引指的不仅仅是要覆盖查询的字段，同时条件字段也需要覆盖才能够利用索引覆盖的特性。

最左前缀
假设现在系统里面既有根据 name 字段查询用户信息，又有根据 name 和 age 字段的高频查询，这时我们就可以直接建 (name, age) 的联合索引，而不需要在 name 字段上单独建一个索引，因为根据最左前缀原则，不管是满足索引的左边字段的一部分还是左边一部分字段或者是完整的索引字段都能够利用该索引来定位记录。比如说 (name, age) 的联合索引对于下面的 SQL 语句来说都能够使用该索引来加快搜索:
1
2
3
select * from users where name like '王%';
select * from users where name = '王七';
select * from users where name = '王七' and age = 27;
但如果后期发现还需要根据 age 字段来查询用户信息的需求，对应 SQL 语句如下:
1
select * from users where age = 26;
可以发现上面的 SQL 已经没法使用到 (name, age) 的联合索引了，于是你不得不再建一个单独的字段索引，这时你可能就直接在 age 字段上建一个索引，这样就既可以根据 name 字段查询用户信息，又可以满足 name 和 age 字段的联合查询，同时也满足了根据 age 字段查询用户信息的需求。

但其实还有一种组合方式，建一个 (age, name) 的联合索引，然后在 name 字段上单独建一个索引，这种组合也是可以满足上面的查询需求的，这时我们应该怎么去选择呢，其实可以根据字段的长度来看，由于 name 字段明显要比 age 字段占用的空间要大，而 (age, name) 联合索引加 name 单字段的索引组合，两棵索引树上都要存储 name 字段，这时总的占用空间就比 (name, age) 联合索引加 age 单字段索引的占用空间要大了，因此考虑到空间占用就可以采用第一种组合方式。

索引下推
在上面的 users 表中，我们建了一个 (name, age) 的联合索引，但现在系统中又有下面这样的查询语句:
1
select * from users where name like '王%' and age = 26;
![name, age 联合索引树](/Users/xxx-xifan/Dropbox/drawio/images/name, age 联合索引树.png)

这时你会发现满足最左前缀的原则，使用 ‘王’ 去 (name, age) 联合索引树上定位到第一个满足 name 字段以王字打头的记录，然后依次遍历下去，寻找同时满足 age = 26 的记录。

对于上面这种情况在 MySQL 5.6 之前，它的的执行过程是这样的:

1.使用 ‘王’ 去 (name, age) 联合索引树上定位到第一个满足 name 字段以王字打头的记录

2.这时定位到第二个叶子节点，找到 (王五, 26) 的主键值 5

3.通过主键值 5 再去主键索引树中搜索得到整条记录的值，判断 age = 26 是否成立

4.再取下一个满足 name 字段以王字打头的记录 (王五, 27) 的主键值 3

5.重复第三步的过程，发现 age = 26 不成立，结束查询

你可以看到每次查询得到满足 name 字段以王字打头的记录时都需要回到主键索引树上去查询得到完整数据后，再判断 age = 26 的条件是否成立，但其实对于联合索引 (name，age) 来说，从索引中的值就已经可以知道 age = 26 的条件是否成立，对于不满足条件的可以直接丢弃，取下一个记录进行判断，而不用频繁的进行回表。

因此在 MySQL 5.6 之后做了一个索引下推(ICP)的优化，在 “仅能利用最左前缀索引的场景” 下(无法能利用全部联合索引)，对不在最左前缀索引中的其他联合索引字段加以利用，在遍历索引时，由只传入可以利用到的字段值，改成了多传入下推字段值进行过滤，过滤会减少遍历索引查出的主键条数，从而减少回表次数，提升整体性能。引入索引下推优化之后，执行过程变成下面这样:

1.使用 ‘王’ 去 (name, age) 联合索引树上定位到第一个满足 name 字段以王字打头的记录

2.定位到第二个叶子节点，找到 (王五, 26)，判断 age = 26 满足条件，取主键值 5 的记录回表查询整条数据的值

3.再取下一个满足 name 字段以王字打头的记录 (王五, 27)，判断 age = 26 条件不满足，直接丢弃，结束查询

可以看到通过引入索引下推的优化之后，由之前的回表两次变成了仅回表一次，尤其当满足最左前缀而其他索引字段不满足条件的数据记录很多的时候，就会大大减少回表的次数，性能提升更明显。

MyISAM 和 InnoDB 存储引擎索引实现

上次只提到了 InnoDB 的索引实现，这里再补充下 MyISAM 存储引擎的索引实现，首先 MyISAM 和 InnoDB 两个存储引擎的索引实现方式都是基于 B+ 树，不同点是:
MyISAM：不管主键索引还是二级索引，叶子节点 data 存放的都是数据记录的地址，因此对于 MyISAM 存储引擎来说，就不存在回表这一说，通过二级索引可以直接获取到完整的数据值，二级索引在结构上和主键索引没有任何区别，只不过主键索引要求 key 是唯一的，而二级索引的 key 可以重复。
InnoDB：InnoDB 的数据文件本身就是索引文件，主键索引树的叶子节点 data 保存了完整的数据记录，而二级索引树 data 存放的则是相应记录主键的值，所以每次只能通过主键的值去回到主键索引树上查询得到完整的数据。