登录
首页 >  数据库 >  MySQL

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

来源:SegmentFault

时间:2023-02-23 18:22:26 222浏览 收藏

编程并不是一个机械性的工作,而是需要有思考,有创新的工作,语法是固定的,但解决问题的思路则是依靠人的思维,这就需要我们坚持学习和更新自己的知识。今天golang学习网就整理分享《大白话mysql之深入浅出索引原理 - 下》,文章讲解的知识点主要包括MySQL、数据库、运维、后端、程序员,如果你对数据库方面的知识点感兴趣,就不要错过golang学习网,在这可以对大家的知识积累有所帮助,助力开发能力的提升。

索引覆盖

在之前《大白话 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,索引效果不理想。

image.png

当执行

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 的用户数据。过程如下图。

image.png

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

image.png

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

索引下推特性是mysql5.6引入的,默认启用,我们也可以通过修改系统变量optimizer_switch 的

index_condition_pushdown
标志来控制

SET optimizer_switch = 'index_condition_pushdown=off';

写在最后

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

image.png

到这里,我们也就讲完了《大白话mysql之深入浅出索引原理 - 下》的内容了。个人认为,基础知识的学习和巩固,是为了更好的将其运用到项目中,欢迎关注golang学习网公众号,带你了解更多关于mysql的知识点!

声明:本文转载于:SegmentFault 如有侵犯,请联系study_golang@163.com删除
相关阅读
更多>
最新阅读
更多>
课程推荐
更多>