大白话mysql之深入浅出索引原理 - 下

索引覆盖

在之前《大白话mysql之深入浅出索引原理 - 下》这篇文章中提到过，mysql 的 innodb 引擎通过搜索树方式实现索引，索引类型分为主键索引和二级索引（非主键索引），主键索引树中，叶子结点保存着主键即对应行的全部数据；而二级索引树中，叶子结点保存着索引值和主键值，当使用二级索引进行查询时，需要进行回表操作。假如我们现在有如下表结构。

CREATE TABLE `user_table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) DEFAULT NULL,
  `age` int(11) unsigned Not NULL,
  PRIMARY KEY (`id`),
  key (`username`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8

执行语句(A) select id from user_table where username = '张三'时，因为 username 索引树的叶子结点上保存有 username 和 id 的值，所以通过 username 索引树查找到 id 后，我们就已经得到所需的数据了，这时候就不需要再去主键索引上继续查找了。
执行语句(B) select password from user_table where username = '张三' 时，流程如下

1. username 索引树上找到 username='张三' 对应的主键 id。
2. 通过回表在主键索引树上找到满足条件的数据。

由上面可知，当 sql 语句的所求查询字段（select 列）和查询条件字段（where 子句）全都包含在一个索引树中，可以直接使用索引查询而不需要回表。这就是覆盖索引，通过使用覆盖索引，可以减少搜索树的次数，是常用的性能优化手段。

例如上面的语句 B 是一个高频查询的语句，我们可以建立 (username,password) 的联合索引，这样，查询的时候就不需要再去回表操作了，可以提高查询效率。当然，添加索引是有维护代价的，所以添加时也要权衡一下。

最左前缀原则

联合索引的多个字段中，只有当查询条件为联合索引的第一个字段时，查询才能使用该索引。

继续以上面的例子来说明，为了提高语句 B 的执行速度，我们添加了一个联合索引（username,password）, 特别注意这个联合索引的顺序，如果我们颠倒下顺序改成（password,username), 这样查询能使用这个索引吗？答案是不能的！

我们知道B+树中的各个节点是有顺序的，在联合索引中是根据索引的第一个字段进行排序构建索引树的(当第一个字段相同时，按第二个字段进行排序)。所以只有当查询条件为联合索引的第一个字段时，查询才能使用该索引。

索引可以根据字段值最左若干个字符进行模糊查询。

现在，假设我们有一下三种查询情景：

• 查出用户名的第一个字是 “张” 开头的人的密码。即查询条件子句为 “where username like ‘张%’”
• 查处用户名中含有 “张” 字的人的密码。即查询条件子句为 “where username like ‘%张%’”
• 查出用户名以 “张” 字结尾的人的密码。即查询条件子句为 “where username like ‘%张’”

以上三种情况下，只有第 1 种能够使用（username,password）联合索引来加快查询速度。

语句select id, username from user_table where username like '%张%'能否使用到(username)索引？

答案是可以的，因为查询的所有字段(id, username)在二级索引（username）中都存在，二级索引树比主键索引树小很多，所以会直接遍历二级索引。值得注意的是，这里是遍历整个索引树，而不是在索引树中快速定位数据。

前缀索引

现在，我们有一个需要根据email字段查找用户信息的需求，当然我们可以直接给email字段创建一个索引，但我们仔细想想，有必要为整个email字段创建索引吗？

其实没必要的，因为邮箱地址是有一个格式的，都是"xxxx@xxx.com",所以其实email字段的后面几位区分度不高。这时为整个email字段创建索引很浪费空间，我们可以创建前缀索引，将字段的前几个字符作为索引即可。mysql中使用ADD KEY (column_name (prefix_length))为字段创建前缀索引。

合适的前缀索引长度

前缀索引设计的好坏在于选择合适的前缀索引长度。如果选择太长，会造成索引空间的浪费；如果选择太短，会导致索引树大量重复的key，索引效果不理想。

当执行select * from user_table where email = '1111aaaa@xx.com'时，通过搜索前缀索引树，会搜到4个1111开头的数据结点，并将这4个进行回表查询，筛出满足条件的row1。 所以，前缀索引长度选择过短，会增加回表查询的行数，影响查询效率。

确定前缀索引的长度，我们可以通过比较count(distinct column_name)和count(distinct LEFT(column_name, prefix_length))的值。两者接近表示prefix_length比较合理。

前缀索引的缺点

因为前缀索引是取前几个字符去排序构建的索引树，不保证完整字段的排序，因此前缀索引无法用于对字段排序（order by column_name）。

前缀索引没有完整的字段信息，匹配到后必须回表查询才能确定查询结果。所以没法利用索引覆盖来提高查询性能。

索引下推

对于 user_table 表，我们现在有（username,age）联合索引
如果现在有一个需求，查出名称中以 “张” 开头且年龄小于等于 10 的用户信息，语句 C 如下：“select * from user_table where username like ’ 张 %’ and age > 10”.
语句 C 有两种执行可能：

1、根据（username,age）联合索引查询所有满足名称以 “张” 开头的索引，然后回表查询出相应的全行数据，然后再筛选出满足年龄小于等于 10 的用户数据。过程如下图。

2、根据（username,age）联合索引查询所有满足名称以 “张” 开头的索引，然后直接再筛选出年龄小于等于 10 的索引，之后再回表查询全行数据。过程如下图。

明显的，第二种方式需要回表查询的全行数据比较少，这就是 mysql 的索引下推,在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

索引下推特性是mysql5.6引入的，默认启用，我们也可以通过修改系统变量optimizer_switch 的 index_condition_pushdown 标志来控制

SET optimizer_switch = 'index_condition_pushdown=off';

写在最后

喜欢本文的朋友，欢迎关注公众号「会玩code」，专注大白话分享实用技术