大白话mysql|大白话mysql之深入浅出索引原理 - 下数据库|后端|运维|大数据|mys

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

索引覆盖
最左前缀原则
前缀索引
- 合适的前缀索引长度
- 前缀索引的缺点
索引下推
写在最后

【大白话mysql|大白话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=InnoDBDEFAULT 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 标志来控制