ORDER BY

在MySQL中,ORDERBY的实现有如下两种类型:

这种利用索引实现数据排序的方法是MySQL中实现结果集排序的最佳做法,可以完全避免因为排序计算所带来的资源消耗。所以,在我们优化Query语句中的ORDERBY的时候,尽可能利用已有的索引来避免实际的排序计算,可以很大幅度的提升ORDERBY操作的性能。在有些Query的优化过程中,即使为了避免实际的排序操作而调整索引字段的顺序,甚至是增加索引字段也是值得的。当然,在调整索引之前,同时还需要评估调整该索引对其他Query所带来的影响,平衡整体得失。

如果没有索引利用的时候,MySQL又如何来实现排序呢?这时候MySQL无法避免需要通过相关的排序算法来将存储引擎返回的数据进行排序运算了。下面我们再针对这种实现方式进行相应的分析。

在MySQL第二种排序实现方式中,必须进行相应的排序算法来实现数据的排序。MySQL目前可以通过两种算法来实现数据的排序操作。

取出满足过滤条件的用于排序条件的字段以及可以直接定位到行数据的行指针信息,在Sort Buffer中进行实际的排序操作,然后利用排好序之后的数据根据行指针信息返回表中取得客户端请求的其他字段的数据,再返回给客户端;

根据过滤条件一次取出排序字段以及客户端请求的所有其他字段的数据,并将不需要排序的字段存放在一块内存区域中,然后在SortBuffer中将排序字段和行指针信息进行排序,最后再利用排序后的行指针与存放在内存区域中和其他字段一起的行指针信息进行匹配合并结果集,再按照顺序返回给客户端。

上面第一种排序算法是MySQL一直以来就有的排序算法,而第二种则是从MySQL4.1版本才开始增加的改进版排序算法。第二种算法与第一种相比较,主要优势就是减少了数据的二次访问。在排序之后不需要再一次回到表中取数据,节省了IO操作。当然,第二种算法会消耗更多的内存,正是一种典型的通过内存空间换取时间的优化方式。

在我们实际应用中,很多时候我们的业务要求可能并不是这样,可能需要排序的字段同时存在于两个表中,或者MySQL在经过一次Join之后才进行排序操作。这样的排序在MySQL中并不能简单的里利用Sort Buffer进行排序,而是必须先通过一个临时表将之前Join的结果集存放入临时表之后在将临时表的数据取到Sort Buffer中进行操作。

GROUP BY 的实现与优化

由于GROUP BY实际上也同样需要进行排序操作,而且与ORDER BY相比,GROUP BY主要只是多了排序之后的分组操作。当然,如果在分组的时候还使用了其他的一些聚合函数,那么还需要一些聚合函数的计算。所以,在GROUP BY的实现过程中,与ORDERBY一样也可以利用到索引。

在MySQL中,GROUP BY的实现同样有多种(三种)方式,其中有两种方式会利用现有的索引信息来完成GROUP BY,另外一种为完全无法使用索引的场景下使用。下面我们分别针对这三种实现方式做一个分析。

  1. 使用松散(Loose)索引扫描实现GROUP BY

何谓松散索引扫描实现GROUP BY呢?实际上就是当MySQL完全利用索引扫描来实现GROUP BY的时候,并不需要扫描所有满足条件的索引键即可完成操作得出结果。

为什么松散索引扫描的效率会很高?

因为在没有WHERE子句,也就是必须经过全索引扫描的时候,松散索引扫描需要读取的键值数量与分组的组数量一样多,也就是说比实际存在的键值数目要少很多。而在WHERE子句包含范围判断式或者等值表达式的时候,松散索引扫描查找满足范围条件的每个组的第1个关键字,并且再次读取尽可能最少数量的关键字。

  1. 使用紧凑(Tight)索引扫描实现GROUP BY

紧凑索引扫描实现GROUP BY和松散索引扫描的区别主要在于他需要在扫描索引的时候,读取所有满足条件的索引键,然后再根据读取恶的数据来完成GROUP BY操作得到相应结果。

在MySQL中,MySQL Query Optimizer首先会选择尝试通过松散索引扫描来实现GROUP BY操作,当发现某些情况无法满足松散索引扫描实现GROUP BY的要求之后,才会尝试通过紧凑索引扫描来实现。

当GROUP BY条件字段并不连续或者不是索引前缀部分的时候,MySQL Query Optimizer无法使用松散索引扫描,设置无法直接通过索引完成GROUP BY操作,因为缺失的索引键信息无法得到。但是,如果Query语句中存在一个常量值来引用缺失的索引键,则可以使用紧凑索引扫描完成GROUP BY操作,因为常量填充了搜索关键字中的“差距”,可以形成完整的索引前缀。这些索引前缀可以用于索引查找。而如果需要排序GROUP BY结果,并且能够形成索引前缀的搜索关键字,MySQL还可以避免额外的排序操作,因为使用有顺序的索引的前缀进行搜索已经按顺序检索到了所有关键字。

  1. 使用临时表实现GROUP BY

MySQL在进行GROUP BY操作的时候要想利用所有,必须满足GROUP BY的字段必须同时存放于同一个索引中,且该索引是一个有序索引(如Hash索引就不能满足要求)。而且,并不只是如此,是否能够利用索引来实现GROUP BY还与使用的聚合函数也有关系。

前面两种GROUP BY的实现方式都是在有可以利用的索引的时候使用的,当MySQL Query Optimizer无法找到合适的索引可以利用的时候,就不得不先读取需要的数据,然后通过临时表来完成GROUP BY操作。

当MySQL Query Optimizer发现仅仅通过索引扫描并不能直接得到GROUP BY的结果之后,他就不得不选择通过使用临时表然后再排序的方式来实现GROUP BY了。

在这样示例中即是这样的情况。group_id并不是一个常量条件,而是一个范围,而且GROUP BY字段为user_id。所以MySQL无法根据索引的顺序来帮助GROUP BY的实现,只能先通过索引范围扫描得到需要的数据,然后将数据存入临时表,然后再进行排序和分组操作来完成GROUP BY。

对于上面三种MySQL处理GROUP BY的方式,我们可以针对性的得出如下两种优化思路:

1.尽可能让MySQL可以利用索引来完成GROUP BY操作,当然最好是松散索引扫描的方式最佳。在系统允许的情况下,我们可以通过调整索引或者调整Query这两种方式来达到目的;

2.当无法使用索引完成GROUP BY的时候,由于要使用到临时表且需要filesort,所以我们必须要有足够的sort_buffer_size来供MySQL排序的时候使用,而且尽量不要进行大结果集的GROUP BY操作,因为如果超出系统设置的临时表大小的时候会出现将临时表数据copy到磁盘上面再进行操作,这时候的排序分组操作性能将是成数量级的下降;

至于如何利用好这两种思路,还需要大家在自己的实际应用场景中不断的尝试并测试效果,最终才能得到较佳的方案。此外,在优化GROUP BY的时候还有一个小技巧可以让我们在有些无法利用到索引的情况下避免filesort操作,也就是在整个语句最后添加一个以null排序(ORDER BY null)的子句,大家可以尝试一下试试看会有什么效果。

DISTINCT 的实现与优化

DISTINCT实际上和GROUP BY的操作非常相似,只不过是在GROUP BY之后的每组中只取出一条记录而已。所以,DISTINCT的实现和GROUP BY的实现也基本差不多,没有太大的区别。同样可以通过松散索引扫描或者是紧凑索引扫描来实现,当然,在无法仅仅使用索引即能完成DISTINCT的时候,MySQL只能通过临时表来完成。但是,和GROUP BY有一点差别的是,DISTINCT并不需要进行排序。也就是说,在仅仅只是DISTINCT操作的Query如果无法仅仅利用索引完成操作的时候,MySQL会利用临时表来做一次数据的“缓存”,但是不会对临时表中的数据进行filesort操作。当然,如果我们在进行DISTINCT的时候还使用了GROUP BY并进行了分组,并使用了类似于MAX之类的聚合函数操作,就无法避免filesort了。

对于DISTINCT的优化,和GROUP BY基本上一致的思路,关键在于利用好索引,在无法利用索引的时候,确保尽量不要在大结果集上面进行DISTINCT操作,磁盘上面的IO操作和内存中的IO操作性能完全不是一个数量级的差距。

参考

tiankonguse +
穿越