写点什么

MySQL 热点面试题:为什么我使用了索引,查询还是慢,java 基础入门第二版第四章答案

用户头像
极客good
关注
发布于: 刚刚

也可以用全索引扫描,来说明像 select a from t;这样的查询,他扫描了整个普通索引树;


而 select * from t where id=2 这样的语句,才是我们平时说的使用了索引。他表示的意思是,我们使用了索引的快速搜索功能,并且有效的减少了扫描行数。


索引的过滤性要足够好


==========


根据以上解剖,我们知道全索引扫描会让查询变慢,接下来就要来谈谈索引的过滤性。


假设你现在维护了一个表,这个表记录了中国 14 亿人的基本信息,现在要查出所有年龄在 10~15 岁之间的姓名和基本信息,那么你的语句会这么写, select * from t_people where age between 10 and 15 。


你一看这个语句一定要在 age 字段上开始建立索引了,否则就是个全面扫描,但是你会发现,在你建立索引以后,这个语句还是执行慢,因为满足这个条件的数据可能有超过 1 亿行。


我们来看看建立索引以后,这个表的组织结构图:



这个语句的执行流程是这样的:


从索引上用树搜索,取到第 1 个 age 等于 10 的记录,得到它的主键 id 的值,根据 id 的值去主键索引取整行的信息,作为结果集的一部分返回;


在索引 age 上向右扫描,取下一个 id 的值,到主键索引上取整行信息,作为结果集的一部分返回;


重复上面的步骤,直到


【一线大厂Java面试题解析+核心总结学习笔记+最新架构讲解视频+实战项目源码讲义】
浏览器打开:qq.cn.hn/FTf 免费领取
复制代码


碰到第 1 个 age 大于 15 的记录;


你看这个语句,虽然他用了索引,但是他扫描超过了 1 亿行。所以你现在知道了,当我们在讨论有没有使用索引的时候,其实我们关心的是扫描行数。


对于一个大表,不止要有索引,索引的过滤性还要足够好。


像刚才这个例子的 age,它的过滤性就不够好,在设计表结构的时候,我们要让所有的过滤性足够好,也就是区分度足够高。


回表的代价


=====


那么过滤性好了,是不是表示查询的扫描行数就一定少呢?


我们再来看一个例子:


如果你的执行语句是 select * from t_people where name='张三' and age=8


t_people 表上有一个索引是姓名和年龄的联合索引,那这个联合索引的过滤性应该不错,可以在联合索引上快速找到第 1 个姓名是张三,并且年龄是 8 的小朋友,当然这样的小朋友应该不多,因此向右扫描的行数很少,查询效率就很高。


但是查询的过滤性和索引的过滤性可不一定是一样的,如果现在你的需求是查出所有名字的第 1 个字是张,并且年龄是 8 岁的所有小朋友,你的语句会怎么写呢?


你的语句要怎么写?很显然你会这么写: select * from t_people where name like '张 %' and age=8;


在 MySQL5.5 和之前的版本中,这个语句的执行流程是这样的:


首先从联合索引上找到第 1 个年龄字段是张开头的记录,取出主键 id,然后到主键索引树上,根据 id 取出整行的值;


判断年龄字段是否等于 8,如果是就作为结果集的一行返回,如果不是就丢弃。


在联合索引上向右遍历,并重复做回表和判断的逻辑,直到碰到联合索引树上名字的第 1 个字不是张的记录为止。


我们把根据 id 到主键索引上查找整行数据这个动作,称为回表。你可以看到这个执行过程里面,最耗费时间的步骤就是回表,假设全国名字第 1 个字是张的人有 8000 万,那么这个过程就要回表 8000 万次,在定位第一行记录的时候,只能使用索引和联合索引的最左前缀,最称为最左前缀原则。


你可以看到这个执行过程,它的回表次数特别多,性能不够好,有没有优化的方法呢?


在 MySQL5.6 版本,引入了 index condition pushdown 的优化。我们来看看这个优化的执行流程:



首先从联合索引树上,找到第 1 个年龄字段是张开头的记录,判断这个索引记录里面,年龄的值是不是 8,如果是就回表,取出整行数据,作为结果集的一部分返回,如果不是就丢弃;


在联合索引树上,向右遍历,并判断年龄字段后,根据需要做回表,直到碰到联合索引树上名字的第 1 个字不是张的记录为止;


这个过程跟上面的差别,是在遍历联合索引的过程中,将年龄等于 8 的条件下推到所有遍历的过程中,减少了回表的次数,假设全国名字第 1 个字是张的人里面,有 100 万个是 8 岁的小朋友,那么这个查询过程中在联合索引里要遍历 8000 万次,而回表只需要 100 万次。


虚拟列


===


可以看到这个优化的效果还是很不错的,但是这个优化还是没有绕开最左前缀原则的限制,因此在联合索引你还是要扫描 8000 万行,那有没有更进一步的优化方法呢?


我们可以考虑把名字的第一个字和 age 来做一个联合索引。这里可以使用 MySQL5.7 引入的虚拟列来实现。对应的修改表结构的 SQL 语句:


我们来看这个 SQL 语句的执行效果:


首先他在 people 上创建一个字段叫 name_first 的虚拟列,然后给 name_first 和 age 上创建一个联合索引,并且,让这个虚拟列的值总是等于 name 字段的前两个字节,虚拟列在插入数据的时候不能指定值,在更新的时候也不能主动修改,它的值会根据定义自动生成,在 name 字段修改的时候也会自动修改。


有了这个新的联合索引,我们在找名字的第 1 个字是张,并且年龄为 8 的小朋友的时候,这个 SQL 语句就可以这么写:select * from t_people where name_first='张' and age=8。


这样这个语句的执行过程,就只需要扫描联合索引的 100 万行,并回表 100 万次,这个优化的本质是我们创建了一个更紧凑的索引,来加速了查询的过程。


总结

用户头像

极客good

关注

还未添加个人签名 2021.03.18 加入

还未添加个人简介

评论

发布
暂无评论
MySQL热点面试题:为什么我使用了索引,查询还是慢,java基础入门第二版第四章答案