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

- 日理万妓 2022-05-28 05:52 71阅读 0赞

奇怪的慢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执行计划：

![722277-20170208160948963-482705411.png][]

第二条执行计划：

![722277-20170208161006604-1662750158.png][]

仔细观察会发现，索引只使用了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

执行计划：

![722277-20170208161057916-1507593576.png][]

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

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

![722277-20170208161128651-100310261.png][]

那么我们有没有其它解决方案呢，这种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

![722277-20170208161146901-1616854084.png][]

联合索引让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，时间居然慢下来了

执行计划是：

![722277-20170208161203869-1827955458.png][]

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

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

select \* from acct\_trans\_log ORDER BY create\_time limit 0,100

耗时：0.029s

执行计划为：

![722277-20170208161226307-517033009.png][]

这里执行的步骤是，先从索引树中，按时间升序取出前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认为必须检查的用来返回请求数据的行数

[722277-20170208160948963-482705411.png]: /images/20220528/16e63ccf8f0046749af39d609785953c.png
[722277-20170208161006604-1662750158.png]: /images/20220528/f856d6495c414d4db904fd6d9582dc40.png
[722277-20170208161057916-1507593576.png]: /images/20220528/e645cd10efc64c01901ae3a71a3a869c.png
[722277-20170208161128651-100310261.png]: /images/20220528/199ec96aa1024c978aca6f445f1ee3db.png
[722277-20170208161146901-1616854084.png]: /images/20220528/a6f29ad511474105a7fe001391063491.png
[722277-20170208161203869-1827955458.png]: /images/20220528/dd447de1a6694c06b4dcf2123076e1d1.png
[722277-20170208161226307-517033009.png]: /images/20220528/8f8c43da70914a3cb51da6736ad703de.png