关于java:为什么我使用了索引查询还是慢

36次阅读

共计 4266 个字符,预计需要花费 11 分钟才能阅读完成。

常常有同学问我,我的一个 SQL 语句应用了索引,为什么还是会进入到慢查问之中呢?明天咱们就从这个问题开始来聊一聊索引和慢查问。

另外插入一个题外话,集体认为团队要正当的应用 ORM,能够参考 ORM 的衡量和抉择。正当利用的是 ORM 在面向对象和写操作方面的劣势,防止联结查问上可能产生的坑(当然如果你的 Linq 查问能力很强另当别论),因为 ORM 屏蔽了太多的 DB 底层的常识内容,对程序员不是件坏事,对性能有极致谋求,然而 ORM 了解不透彻的团队更加要审慎。

案例分析

言归正传,为了试验,我创立了如下表:

CREATE TABLE `T`(`id` int(11) NOT NULL,
`a` int(11) DEFAUT NULL,
PRIMARY KEY(`id`),
KEY `a`(`a`)
) ENGINE=InnoDB;

该表有三个字段,其中用 id 是主键索引,a 是一般索引。

首先 SQL 判断一个语句是不是慢查问语句,用的是语句的执行工夫。他把语句执行工夫跟 long_query_time 这个零碎参数作比拟,如果语句执行工夫比它还大,就会把这个语句记录到慢查问日志外面,这个参数的默认值是 10 秒。当然在生产上,咱们不会设置这么大,个别会设置 1 秒,对于一些比拟敏感的业务,可能会设置一个比 1 秒还小的值。

语句执行过程中有没有用到表的索引,能够通过 explain 一个语句的输入后果来看 KEY 的值不是 NULL。

咱们看下 explain select * from t;的 KEY 后果是 NULL


(图一)

explain select * from t where id=2;的 KEY 后果是 PRIMARY,就是咱们常说的应用了主键索引


(图二)

explain select a from t;的 KEY 后果是 a,示意应用了 a 这个索引。


(图三)

尽管后两个查问的 KEY 都不是 NULL,然而最初一个实际上扫描了整个索引树 a。

假如这个表的数据量有 100 万行,图二的语句还是能够执行很快,然而图三就必定很慢了。如果是更极其的状况,比方,这个数据库上 CPU 压力十分的高,那么可能第 2 个语句的执行工夫也会超过 long_query_time,会进入到慢查问日志外面。

所以咱们能够得出一个论断:是否应用索引和是否进入慢查问之间并没有必然的分割。应用索引只是示意了一个 SQL 语句的执行过程,而是否进入到慢查问是由它的执行工夫决定的,而这个执行工夫,可能会受各种内部因素的影响。换句话来说,应用了索引你的语句可能仍然会很慢。

全索引扫描的有余

那如果咱们在更深层次的看这个问题,其实他还潜藏了一个问题须要廓清,就是什么叫做应用了索引。

咱们都晓得,InnoDB 是索引组织表,所有的数据都是存储在索引树下面的。比方下面的表 t,这个表蕴含了两个索引,一个主键索引和一个一般索引。在 InnoDB 里,数据是放在主键索引里的。如图所示:


能够看到数据都放在主键索引上,如果从逻辑上说,所有的 InnoDB 表上的查问,都至多用了一个索引,所以当初我问你一个问题,如果你执行select from t where id>0,你感觉这个语句有用上索引吗?


咱们看下面这个语句的 explain 的输入结果显示的是 PRIMARY。其实从数据上你是晓得的,这个语句肯定是做了全面扫描。然而优化器认为,这个语句的执行过程中,须要依据主键索引,定位到第 1 个满足 ID>0 的值,也算用到了索引。

所以即便 explain 的后果里写的 KEY 不是 NULL,实际上也可能是全表扫描的,因而 InnoDB 外面只有一种状况叫做没有应用索引,那就是从主键索引的最右边的叶节点开始,向右扫描整个索引树。

也就是说,没有应用索引并不是一个精确的形容。

你能够用全表扫描来示意一个查问遍历了整个主键索引树;

也能够用全索引扫描,来阐明像 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 的值,到主键索引上取整行信息,作为后果集的一部分返回;

反复下面的步骤,直到碰到第 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 语句:

alter table t_people add name_first varchar(2) generated (left(name,1)),add index(name_first,age);

咱们来看这个 SQL 语句的执行成果:

CREATE TABLE `t_people`(`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAUT NULL,
`name_first` varchar(2) GENERATED ALWAYS AS (left(`name`,1)) VIRTUAL,KEY `name_first`(`name_first`,'age')
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

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

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

这样这个语句的执行过程,就只须要扫描联结索引的 100 万行,并回表 100 万次,这个优化的实质是咱们创立了一个更紧凑的索引,来减速了查问的过程。

总结

本文给你介绍了索引的根本构造和一些查问优化的基本思路,你当初晓得了,应用索引的语句也有可能是慢查问,咱们的查问优化的过程,往往就是缩小扫描行数的过程。

慢查问归纳起来大略有这么几种状况:

  • 全表扫描
  • 全索引扫描
  • 索引过滤性不好
  • 频繁回表的开销

思考

假如业务要求的就是要统计年龄在 10-15 岁的 14 亿人的数量,不能减少过滤因子,那该怎么办?(select * from t_people where age between 10 and 15)

假如该统计必须是 OLTP,实时展现统计数据,又该怎么解决?

欢送关注公众号【码农开花】一起学习成长
我会始终分享 Java 干货,也会分享收费的学习材料课程和面试宝典
回复:【计算机】【设计模式】【面试】有惊喜哦

正文完
 0