为什么我使用了索引,查询还是慢?

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

另外插入一个题外话,个人认为团队要合理的使用ORM,可以参考 ORM的权衡和抉择(http://rrd.me/f9jCg)。合理利用的是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,实时展示统计数据,又该怎么解决?

来源:rrd.me/f9jAG

Image placeholder
IT头条
未设置
  100人点赞

没有讨论,发表一下自己的看法吧

推荐文章
InnoDB到底支不支持哈希索引,为啥不同的人说的不一样?

继续回答水友提问(最近问MySQL的多):沈老师,我在网上看到不同的资料,有的说InnoDB支持哈希索引,有的说不支持,到底哪个是正确的呢?对于InnoDB的哈希索引,确切的应该这么说:(1)Inno

为什么mysql索引要使用B+树,而不是B树,红黑树

我们在MySQL中的数据一般是放在磁盘中的,读取数据的时候肯定会有访问磁盘的操作,磁盘中有两个机械运动的部分,分别是盘片旋转和磁臂移动。盘片旋转就是我们市面上所提到的多少转每分钟,而磁盘移动则是在盘片

为什么我3岁的儿子有不良信用记录?儿童数据泄露问题暗潮汹涌

大数据文摘出品作者:林安安、蒋宝尚2019年初,在暗网(darkweb)上出现了一波特别的数据集贩卖。这一次的泄露的数据不同以往,其信息所属者多是3-20岁的未成年人。具体来说是1998年到2015年

JanusGraph索引入门及模糊查询

JanusGraph模糊查询与索引1.索引JanusGraph支持两种不同类型的索引来加快查询处理:图形索引和中心顶点索引 图形索引使从由其属性标识的顶点或边列表开始遍历的全局检索操作在大型图形上高效

日均5亿查询量的京东订单中心,为什么舍MySQL用ES?

京东到家订单中心系统业务中,无论是外部商家的订单生产,或是内部上下游系统的依赖,订单查询的调用量都非常大,造成了订单数据读多写少的情况。我们把订单数据存储在MySQL中,但显然只通过DB来支撑大量的查

MySQL优化之覆盖索引的使用

查看测试表结构:mysql>showcreatetableim_message\G ***************************1.row**************************

AI赌神升级!无惧bluff,6人局德扑完胜世界冠军,训练只用了8天

大数据文摘出品作者:曹培信、宁静2017年年初,BrainvsAI的德州扑克人机大战在卡耐基梅隆大学(CMU)落幕,由4名人类职业玩家组成的人类大脑不敌人工智能程序Libratus。获胜后人类还遭到了

搜索引擎百度已死,但其他业务在重生

年初有一篇《搜索引擎百度已死》的文章在全网刷屏,文章尖锐指出百度搜索有一半以上结果导向了自己的百家号,而百家号上大量低劣和营销的内容严重误导了用户,事后百度回应说其百家号的内容占比小于10%。与此同时

微软张若非:搜索引擎和广告系统,那些你所不知的AI落地技术

这两年,被誉为“ 皇冠上的明珠”的自然语言处理领域发展愈发火热,成为了业内新宠,而 搜索和广告这两大老牌技术领域似乎已被大家遗忘。其实,这两大接地气的工程领域仍是各企业竞相抢夺的市场之一。近日,AI科

【MySQL实战45讲】索引部分整理

本文摘抄自极客时间【MySQL实战45讲】为什么要有索引?索引的作用是什么?索引的出现其实就是为了提高数据查询的效率,就像书的目录一样。一本书我们可以通过目录中快速的定位其中的某一个知识点;对于数据库

Elasticsearch 与传统关系型数据库的对比、倒排索引原理解析

Elasticsearch和传统关系型数据库的对比Elasticsearch中的概念与关系型数据库对比 RelationalDB Databases Tables Rows Columns 关系

使用Jupyter NoteBook进行IB查询和交易,以及使用算法交易示例

在搞好IB盈透接口后,试了下客户端交易,但是最终目的还是使用程序化交易。发现vnpy已经提供的Script_engine来支持JupyterNoteBook交易的,而且非常方便调用。 这里就用写了基于

为什么要前后端分离?有什么优缺点

作者:Cherry300来源:jianshu.com/p/c86cee16b418一、前戏前后端分离已成为互联网项目开发的业界标准使用方式,通过nginx+tomcat的方式(也可以中间加一个node

为什么SQL正在击败NoSQL,这对未来的数据意味着什么

导读:经过多年的沉寂之后,今天的SQL正在复出。缘由如何?这对数据社区有什么影响?看看本文的分析。以下为译文。自从可以利用计算机做事以来,我们一直在收集的数据以指数级的速度在增长,因此对于数据存储、处

为什么不建议在 MySQL 中使用 UTF-8?

最近我遇到了一个bug,我试着通过Rails在以“utf8”编码的MariaDB中保存一个UTF-8字符串,然后出现了一个离奇的错误:Incorrect string value: ‘😃 

为什么阿里巴巴建议开发者谨慎使用继承?

从学习Java的第一天起,我们就知道Java是一种面向对象语言,而学习Java的第二天,我们就知道了面向对象的三大基本特性是:封装、继承、多态。所以,对于很多开发者来说,继承肯定都是不陌生的。但是,继

为什么使用div css布局?

提起DIV+CSS组合,还要从XHTML说起。XHTML是一种在HTML(标准通用标记语言的子集)基础上优化和改进的新语言,目的是基于XML应用与强大的数据转换能力,适应未来网络应用更多的需求。为什么

你的公司是需要数据科学家还是数据工程师?差别有点大

越来越多的企业关注AI,企业组织也意识到拥有相关人才和技能非常重要。特别是最近对AI、机器学习(ML)、非ML预测分析和“大数据”的应用,使得数据科学家的需求有了显著的增长,未来还将继续。事实上,对数

Stack Overflow上188万浏览量的提问:Java 到底是值传递还是引用传递?

在逛StackOverflow的时候,发现了一些访问量像阿尔卑斯山一样高的问题,比如说这个:Java到底是值传递还是引用传递?访问量足足有188万+,这不得了啊!说明有很多很多的程序员被这个问题困扰过

跳来跳去,到底去大公司还是小公司?

作者:一颗卤蛋,https://dwz.cn/NA3E0JFG职场生涯总会面临着选择,尤其对我们这些IT人来说,跳槽的频率应该是所有行业中相当大的了。那么我们跳来跳去,究竟该选择什么样的公司?大or小

不论“云唯一”还是“无云”,都很糟糕

单纯说技术的好坏是不明智的,新技术与产业的融合也是一步一步慢慢来,不能走极端。DavidS.Linthicum是德勤咨询公司的首席云战略官,也是国际公认的行业专家和思想领袖。在近日的一篇文章中指出云战

博通终归还是下手了 作价107亿美元收购赛门铁克企业安全业务!

说起财大气粗,博通绝对是排的上号的企业,早在去年,博通就曾试图斥资1170亿美元收购竞争对手高通,最终特朗普政府以国家安全为由阻止了收购。而后博通在2018年7月斥资189亿美元收购了IT管理软件和解

女朋友还是游戏?一起来分析下游戏的开发与销售情况

有人问:游戏重要还是女朋友重要?我想说,你是不是傻?游戏没了可以再下!要是女朋友没了……就没人会打扰你玩游戏了,哈哈哈哈哈哈哈哈哈哈!大部分男生都喜欢游戏,但对游戏的了解却不是很多,让我们来分析一下“

DBA跳槽应该去大公司还是小公司?99%的人这样说…

职场生涯总会面临着选择,尤其对我们这些IT人来说,跳槽的频率应该是所有行业中相当大的了。那么我们跳来跳去,究竟该选择什么样的公司?大or小。 工作三年多了,经历一大一小,最近也面试了不少家公司,形态各

爬虫究竟是合法还是违法的?

据说互联网上50%以上的流量都是爬虫创造的,也许你看到很多热门数据都是爬虫所创造的,所以可以说无爬虫就无互联网的繁荣。前天写了一篇文章《只因写了一段爬虫,公司200多人被抓!》,讲述程序员因写爬虫而被