【大白话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=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’;
复制代码
写在最后
喜欢本文的朋友,欢迎关注公众号「跬步匠心」,专注大白话分享实用技术

稀土掘金
我还没有学会写个人说明!
上一篇

Redmi K40手机背面公布,全系标配4520mAh电池

下一篇

面试官问:在读多写少的情况下,如果优化 MySQL 的数据查询方案

你也可能喜欢

评论已经被关闭。

插入图片