关于后端:大白话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 = '张三' 时，流程如下

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

由下面可知，当 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」，专一大白话分享实用技术

关于后端:大白话mysql之深入浅出索引原理-下

索引笼罩

最左前缀准则

前缀索引

适合的前缀索引长度

前缀索引的毛病

索引下推

写在最初