|
|
|
|
公众号矩阵

明明加了索引,为什么查询还是慢?

小李今天刚上班就收到客户的反馈,说查询用户信息会非常的慢,有时甚至会出现超时的现象。

作者:朱钢来源:51CTO技术栈|2020-10-29 09:19

【51CTO.com原创稿件】小李今天刚上班就收到客户的反馈,说查询用户信息会非常的慢,有时甚至会出现超时的现象。

图片来自 Pexels

小李这就纳闷了分明已经给表加上了索引为什么还这么慢呢。小李分析了好久都没分析出原因,于是只能找到同部门的扫地僧大林子。

大林子一边听着小李的描述一边看着项目,就在小李刚把问题描述完大林子就对小李说:“问题解决了”,小李震惊不已,问道:“这么 6,是什么原因导致的呢?分明我已经加了索引了啊?”

大林子说:“这是很多开发人员很容易忽视的问题......”听完大林子的讲解小李瞬间茅塞顿开。那么具体什么原因呢,下面我就给大家讲解一下。

原因讲解

首先,我们来创建一个存储引擎为 InnoDB 的 User 表,这个表包含三个字段分别是 id,name 和 age。

其中 id 为主键 name 上添加了一个普通索引名字叫 n,接着我们像这条表中插入 10 亿条数据。

表和数据都创建完了,下面我们就来说说为什么加上了索引还是查询很慢,以及解决方案。

MySQL 会根据语句的执行时间来判断 SQL 语句是否是慢查询语句。

当一个 SQL 语句在执行时,MySQL 把语句执行时间和系统参数 long_query_time(这个参数的默认值是 10 秒,但是在实际项目中我们会将这个参数值设置为1秒甚至更短的时间) 作比较。

如果执行时间大于这个参数的值,那么就把这个语句记录到慢查询日志中。那么在语句执行过程中我们如何得知是否使用了索引呢?

这时我们就可以使用 explain 语句来查看数据结果中 Key 的值是否 null ,如果是 null 则说明没有使用索引。

下面我们来看一个例子:

  1. explain select * from user
  2. explain select * from user where id=1; 
  3. explain select name from user

上面三个 explain 语句返回的 key 如下表所示:

从上表我们可以看出第一个语句没有使用索引,第二个使用了主键索引,第三个语句使用 n 这个索引。

我们的 user 表有 10 亿条数据,可想而知第一条查询语句执行效率肯定低,第二个查询语句看似执行效率高,其实在极端环境下(比如 CPU 高负载)也会出现查询效率低的问题。

最后一个查询语句呢虽然使用了 n 这个索引,但是它实际上执行了扫描整个索引树的操作,因此查询效率也高不到哪去。

综上所述,我们可知索引是否使用和是否被记录到慢查询中几乎没有联系,索引只是 SQL 的一个执行过程,SQL 的执行时间才是决定是否被记录到慢查询中的关键。

前面一小节我们只是简单的分析了一下问题,下面我们进一步看这个问题。我们知道 InnoDB 是索引组织表,所有数据都存储在索引树上。

在 InnoDB 中数据放在主键索引里,因此理论上来说所有在 InnoDB 表中的查询至少使用了一个索引。

比如这个 SQL 查询语句 select * from user where id > 1000,很明显它使用主键索引,并且这个语句一定执行了整个索引树的扫描。

在 InnoDB 中只有一种情况叫不使用索引,就是从主键索引的最左边的叶子结点开始向右扫描整个索引树。

到目前为止我们已经知道了全索引扫描会造成查询变慢,下面我们就来说一下另一个知识点过滤性 。

假如我们要查询 user 表中 age 在 70 岁以上 80 岁以下的人员信息,你一定会在 age 字段上加入索引来避免全局扫描。

不错,这是个好的想法,但是当你运行查询语句时就会发现它依然执行的很慢,这是为什么呢?

要解答这个问题我们先来看一下 SQL 查询语句的执行流程:

  • 搜索 age 索引树,获取到第一个 age 为 70 的记录。
  • 拿到主键值,根据主键值去主键索引树上获取对应的信息,并将信息加入结果集。
  • 在 age 索引树上向右侧扫描,获取到下一个主键值,执行第二部的操作。
  • 不断执行上面的步骤,直到遇到第一个 age 大于 80 的记录为止。

从上面的步骤中我们可以看出虽然使用了索引,但是查询过程中扫描了上万行甚至上亿行。

因此我们可以得出结论:对于这种数据非常多的表,我们所要做的不仅仅是加入索引,还要保证索引的过滤性足够优秀。

假如说我们把索引的过滤性也处理好了,是不是查询时要扫描的行数就一定会表少呢?

这个答案是否定的,比如说我们的 user 表中的 name 和 age 字段共同组成了联合索引并处理好了过滤性,这时当我们查询姓李的并且年龄是 60 岁的数据时查询效率依然很低。

我们先来看一下查询语句的执行流程:

  • 首先从联合索引上找到姓名字段是李字开头的数据记录。
  • 拿到主键值,根据主键值在主键索引书上去除匹配的数据。
  • 接着根据 age 字段去判断年龄是否等于 60,如果符合就加入结果集。
  • 然后再联合所以上向右侧遍历,并不断做回表和判断,直到遇到 name 的第一个字不是李的为止。

Tip:所谓的回表就是根据主键值去主键索引树上查找对应的数据。

从上面的步骤中我们可以看出最耗时的就是回表,如果姓李的数据有 2 亿条那么就要回表 2 亿次,并且 SQL 在定位第一行数据时只能使用最左前缀原则。

这种耗时的回表操作步骤在 MySQL 5.6 及其以后的版本中已经做了 index condition pushdown 优化。

优化后的流程很简单:

  • 首先从联合索引上找到姓名字段是李字开头的数据记录,并判断这个记录里 age 是不是 60,如果是就执行回表取出数据假如结果集。
  • 重复步骤1,直到配当第一个字不是李字的记录为止。

优化后和优化前的区别是把 age 的对比步骤放在了遍历联合索引树上,减少了回表次数。

但是虽然减少了回表次数,联合索引树的遍历去没有减少依然要遍历 2 亿次,那么有没有更好的优化方案呢?答案是有的,我们可以实虚拟列来进行处理。

首先我们需要把 name 的第一个字和 age 做一个联合索引,让虚拟列的值总是等于 name 字段的前两个字节,这里需要注意的是虚拟列不随着 insert 和 update 变化,它的值是自定义生成的。

语句如下:

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

经过上述的优化后联合索引树的查询次数也降低了,本质上就是创建一个紧凑的索引加快查询。

总结

这篇文章主要介绍了查询优化的基本思路,只要记住优化查询的过程都是减少扫描行数的过程,就可以做到在 SQL 查询面前百战百胜。

作者:朱钢,笔名喵叔

简介:.NET 高级开发人员,2019 年度博客之星 20 强,长期从事电子政务系统和AI客服系统的设计与开发,目前就职于国内某 BIM 大厂从事招投标软件的开发。

编辑:陶家龙

征稿:有投稿、寻求报道意向技术人请联络 editor@51cto.com

【51CTO原创稿件,合作站点转载请注明原文作者和出处为51CTO.com】

【编辑推荐】

  1. 视频存储的未来有哪些新趋势和机遇?
  2. 七种常见存储网络协议的比较
  3. vlookup函数查询出错怎么办?全部4种错误类型都在这
  4. MySQL实现一个简单版搜索引擎,真是绝了!
  5. 为什么MySQL分库分表后总存储大小变大了?
【责任编辑:武晓燕 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

云原生架构实践

云原生架构实践

新技术引领移动互联网进入急速赛道
共3章 | KaliArch

26人订阅学习

数据中心和VPDN网络建设案例

数据中心和VPDN网络建设案例

漫画+案例
共20章 | 捷哥CCIE

189人订阅学习

搭建数据中心实验Lab

搭建数据中心实验Lab

实验平台Datacenter
共5章 | ITGO(老曾)

118人订阅学习

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微