sql查询调优之where条件排序字段以及limit使用索引的奥秘

2018-11-26 17:05:47

奇怪的慢sql

我们先来看2条sql

第一条:

select * from acct_trans_log WHERE  acct_id = 1000000000009000757 order by create_time desc limit 0,10

  

第二条:

 select * from acct_trans_log WHERE  acct_id = 1000000000009003061 order by create_time desc limit 0,10

表的索引及数据总情况:

 

索引:acct_id,create_time分别是单列索引,数据库总数据为500w

通过acct_id过滤出来的结果集在1w条左右

 

查询结果:第一条要5.018s,第二条0.016s

为什么会是这样的结果呢?第一,acct_id和create_time都有索引,不应该出现5s查询时间这么慢啊

 

那么先来看执行计划

第一条sql执行计划:

 第二条执行计划:

 仔细观察会发现,索引只使用了idx_create_time,没有用到idx_acct_id

这能解释第一条sql很慢,因为where查询未用到索引,那么第二条为什么这么快?

看起来匪夷所思,其实搞清楚mysql查询的原理之后,其实很简单

 

我们来看这2条sql查询,都用到了where order by limit

当有limit存在时,查询的顺序就有可能发生变化,这时并不是从数据库中先通过where过滤再排序再limit

因为如果这样的话,从500万数据中通过where过滤就不会是5s了。

 

 

此时的执行顺序是,先根据idx_create_time索引树,从最右侧叶子节点,反序取出n条,然后逐条去跟where条件匹配

若匹配上,则得出一条数据,直至取满10条为止,为什么第二条sql要快,因为运气好,刚好时间倒序的前几条就全部满足了。

 

搞清楚原理之后,我们了解了为什么第一条慢,第二条快的原因,但是问题又来了

为什么mysql不用idx_acct_id索引,这是一个问题,因为这样的话,我们的建立的索引基本失效了,在此类sql下

查询效率将会是相当低

 

因为通过acct_id过滤出来的结果集比较大,有上万条,mysql认为按时间排序如果不用索引,将会是filesort,这样会很慢,而又不能2个索引都用上

,所以选择了idx_create_time。

 

为什么mysql只用一个索引

这里为什么不能2个索引都用上,可能很多人也不知道为什么,其实道理很简单,每个索引在数据库中都是一个索引树,其数据节点存储了指向实际

数据的指针,如果用一个索引来查询,其原理就是从索引树上去检索,并获得这些指针,然后去取出数据,试想,如果你通过一个索引,得到过滤后的指针,这时,你的另一个条件索引如果再过滤一遍,将得到2组指针的集合,如果这时候取交集,未必就很快,因为如果每个集合都很大的话,取交集的时候,等于扫描2个集合,效率会很低,所以没法用2个索引。当然有时候mysql会考虑临时建立一个联合索引,将2个索引联合起来用,但是并不是每种情况都能奏效,同样的道理,用一个索引检索出结果集之后,排序时,也无法用上另一个索引了。

 

实际上用索引idx_acct_id大多数情况还是要比用索引idx_create_time要快,我们举个例子:

select * from acct_trans_log force index(idx_acct_id) WHERE  acct_id = 1000000000009000757 order by create_time desc limit 0,10

耗时:0.057s

可以看出改情况用idx_acct_id索引是比较快的,那么是不是这样就可以了呢,排序未用上索引,始终是有隐患的。

 

 

联合索引让where和排序字段同时用上索引

我们来看下一条sql:

select * from acct_trans_log force index(idx_acct_id) WHERE  acct_id = 3095  order by create_time desc limit 0,10

耗时: 1.999s

执行计划:

 该sql通过acct_id过滤出来的结果集有100万条,因此排序将会耗时较高,所幸这里只是取出前10条最大的然后排序

查询概况,我们发现时间基本消耗在排序上,其实这是内存排序,对内存消耗是很高的。

 

 那么我们有没有其它解决方案呢,这种sql是我们最常见的,如果处理不好,在大数据量的情况下,耗时以及对数据库资源的消耗都很高

,这是我们所不能接受的,我们的唯一解决方案就是让where条件和排序字段都用上索引

 

解决办法就是建立联合索引:

alter table acct_trans_log add index idx_acct_id_create_time(acct_id,create_time)

然后执行sql:

select * from acct_trans_log WHERE  acct_id = 3095  order by create_time desc limit 0,10

耗时: 0.016s

 联合索引让where条件字段和排序字段都用上了索引,问题解决了!

 

联合索引使用的原理

但是为什么能解决这个问题呢,这时大家可能就会记住一个死理,就是联合索引可以解决where过滤和排序的问题,也不去了解

其原理,这样是不对的,因为当情况发生变化,就懵逼了,下面我们再看一个sql:

select * from acct_trans_log force index(idx_acct_id_create_time) WHERE  acct_id in(3095,1000000000009000757)  order by create_time desc limit 0,10

耗时:1.391s

索引还是用idx_acct_id_create_time,时间居然慢下来了

执行计划是:

 

 

  看执行计划,排序用到了filesort,也就是说,排序未用到索引。

 

那么我们还是来看看,索引排序的原理,我们先来看一个sql:

select * from acct_trans_log ORDER BY create_time limit 0,100

耗时:0.029s

执行计划为:

 这里执行的步骤是,先从索引树中,按时间升序取出前100条,因为索引是排好序的,直接左序遍历即可了

因此,这里mysql并没有做排序动作,如果想降序,则右序遍历索引树,取出100条即可,查询固然快,

 

那么联合索引的时候,是怎样的呢?

select * from acct_trans_log WHERE  acct_id = 3095  order by create_time desc limit 0,10

使用组合索引:idx_acct_id_create_time

这个时候,因为acct_id是联合索引的前缀,因此可以很快实行检索,

如果sql是

select * from acct_trans_log WHERE  acct_id = 3095

出来的数据是按如下逻辑排序的

3095+time1

3095+time2

3095+time3

默认是升序的,也就是说,次sql相当于

select * from acct_trans_log WHERE  acct_id = 3095 order by create_time

他们是等效的。

如果我们把条件换成order by create_time desc limit 0,10呢

这时候,应该从idx_acct_id_create_time树右边叶子节点倒序遍历,取出前10条即可

因为数据的前缀都是3095,后缀是时间升序。那么我们倒序遍历出的数据,刚好满足order by create_time desc

因此也无需排序。

 

那么语句:

select * from acct_trans_log force index(idx_acct_id_create_time) WHERE  acct_id in(3095,1000000000009000757)  order by create_time desc limit 0,10

为什么排序无法用索引呢?

我们先分析下索引的排序规则

已知:id1<id2<id3...  time1<time2<time3....

查询结果集排序如下:

id1+time1

id1+time2

id1+time3

id2+time1

id2+time2

id2+time3

 

索引出来的默认排序是这样的,id是有序的,时间是无序的,因为有2个id,优先按id排序,时间就是乱的了,

这样排序将会用filesort,这就是慢的原因,也是排序没有用到索引的原因。

  

查询计划使用以及使用说明

table:显示这一行数据是关于哪张表的

type:显示使用了何种类型,从最好到最差的连接类型为const,eq_ref,ref,range,index,all

possible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引

key:实际使用的索引,如果为null,则没有使用索引。

key_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好

ref:显示索引的哪一列被使用了,如果可能的话,是一个常数

rows:mysql认为必须检查的用来返回请求数据的行数

  • 2018-11-19 15:10:23

    nodemailer的使用,nodejs发送邮件

    前段时间有个很普通的项目需要发邮件的功能,而且是刚开始学nodejs,所以只是搜索了下用什么好的库能实现,就找到了nodemailer了。这篇文章主要是记录一下使用的过程和经验。

  • 2018-11-21 09:07:37

    Android为每个应用分配多少内存?

    熟悉Android内存分配机制的朋友都知道,Android为每个进程分配内存时,采用弹性的分配方式,即刚开始并不会给应用分配很多的内存,而是给每一个进程分配一个“够用”的内存大小。

  • 2018-11-22 21:13:28

    webview之独立进程

    app内存占用大,被系统回收的概率就高,当每次把app切到后台再回到app时,可能每次app都会重启,最常见的是activity或fragment被回收了,导致fragment使用activity的数据时,出现NullPointerException。内存占用大,app越不稳定。运行性能差。webview加载页面后会占用更多的内存,从而导致app内存占用大,最终导致出现以上问题。

  • 2018-11-22 21:14:34

    为什么要采用WebView独立进程

    App中大量Web页面的使用容易导致App内存占用巨大,存在内存泄露,崩溃率高等问题,WebView独立进程的使用是解决Android WebView相关问题的一个合理的方案。

  • 2018-11-22 21:15:45

    Android WebView: 性能优化不得不说的事

    Mo说:大家通过前两篇文章想必都能顺利的 get 到 WebView 与 JavaScript 交互的技能了。现在 App 嵌入 H5 页面已经是稀松平常的事情了,开发者要面对 WebView 也越来越多的爆发出来,比如页面加载慢,内存泄露,不同 Android 系统版本采用了不同内核的兼容问题等等。 所以当我们使用了 WebView 这个组件的时候,性能优化的事情就不能不提上议程了。这篇文章我们就针对上述问题来总结下 Android WebView 性能优化的常见方法。 作者:MoTalksCn_林墨 链接:https://www.jianshu.com/p/95d4d73be3d1 來源:简书 简书著作权归作者所有,任何形式的转载都请联系作者获得授权并注明出处。

  • 2018-11-22 21:20:04

    WebView内存泄漏--解决方法小结

    Android混合开发时经常用到WebView加载html等页面,而WebView的内存泄漏就是最经常遇到的问题,尤其是当项目中需要用webview加载的页面比较多时。 即使当我退出页面时在我的BrowserActivity的onDestroy()方法中进行内存占用回收(如下图)但并没有效果:

  • 2018-11-23 09:19:27

    论索引的重要性

    我还有什么能说的呢,看来索引基本能解决一切慢sql。好开心。