MySQL Query优化:实战

Published on 2016 - 06 - 07

充分利用Explain和Profiling

Explain的使用

说到Explain,肯定很多读者之前已经用过了,MySQL Query Optimizer通过执行EXPLAIN命令来告诉我们它将使用一个怎样的执行计划来优化Query。所以,可以说Explain是在优化Query时最直接有效地验证我们想法的工具。一个好的SQL Performance Tuner在动手优化一个Query之前,头脑中就应该已经有了一个好的执行计划,后面的优化工作只是为实现该执行计划而作出的各种调整。

在对某个Query优化过程中,须要不断地使用Explain来验证各种调整是否有效。就像前面很多示例都会通过Explain来验证和展示结果一样,所有的Query优化都应该充分利用它。

下面看一下在MySQL Explain功能中展示各种信息的解释。

  • ID:MySQL Query Optimizer选定的执行计划中查询的序列号。
  • Select_type:所使用的查询类型,主要有以下这几种查询类型。

    • DEPENDENT SUBQUERY:子查询内层的第一个SELECT,依赖于外部查询的结果集。
    • DEPENDENT UNION:子查询中的UNION,且为UNION中从第二个SELECT开始的后面所有SELECT,同样依赖于外部查询的结果集。
    • PRIMARY:子查询中的最外层查询,注意并不是主键查询。
    • SIMPLE:除子查询或UNION之外的其他查询。
    • SUBQUERY:子查询内层查询的第一个SELECT,结果不依赖于外部查询结果集。
    • UNCACHEABLE SUBQUERY:结果集无法缓存的子查询。
    • UNION:UNION语句中第二个SELECT开始后面的所有SELECT,第一个SELECT为PRIMARY。
    • UNION RESULT:UNION中的合并结果。
  • Table:显示这一步所访问的数据库中的表的名称。

  • Type:告诉我们对表使用的访问方式,主要包含如下集中类型。

    • all:全表扫描。
    • const:读常量,最多只会有一条记录匹配,由于是常量,实际上只须要读一次。
    • eq_ref:最多只会有一条匹配结果,一般是通过主键或唯一键索引来访问。
    • fulltext:进行全文索引检索。
    • index:全索引扫描。
    • index_merge:查询中同时使用两个(或更多)索引,然后对索引结果进行合并(merge),再读取表数据。
    • index_subquery:子查询中的返回结果字段组合是一个索引(或索引组合),但不是一个主键或唯一索引。
    • rang:索引范围扫描。
    • ref:Join语句中被驱动表索引引用的查询。
    • ref_or_null:与ref的唯一区别就是在使用索引引用的查询之外再增加一个空值的查询。
    • system:系统表,表中只有一行数据;
    • unique_subquery:子查询中的返回结果字段组合是主键或唯一约束。
  • Possible_keys:该查询可以利用的索引。如果没有任何索引可以使用,就会显示成null,这项内容对优化索引时的调整非常重要。

  • Key:MySQL Query Optimizer从possible_keys中所选择使用的索引。

  • Key_len:被选中使用索引的索引键长度。

  • Ref:列出是通过常量(const),还是某个表的某个字段(如果是join)来过滤(通过key)的。

  • Rows:MySQL Query Optimizer通过系统收集的统计信息估算出来的结果集记录条数。

  • Extra:查询中每一步实现的额外细节信息,主要会是以下内容。

    • Distinct:查找distinct值,当mysql找到了第一条匹配的结果时,将停止该值的查询,转为后面其他值查询。
    • Full scan on NULL key:子查询中的一种优化方式,主要在遇到无法通过索引访问null值的使用。
    • Impossible WHERE noticed after reading const tables:MySQL Query Optimizer通过收集到的统计信息判断出不可能存在结果。
    • No tables:Query语句中使用FROM DUAL或不包含任何FROM子句。
    • Not exists:在某些左连接中,MySQL Query Optimizer通过改变原有Query的组成而使用的优化方法,可以部分减少数据访问次数。
    • Range checked for each record (index map: N):通过MySQL官方手册的描述,当MySQL Query Optimizer没有发现好的可以使用的索引时,如果发现前面表的列值已知,部分索引可以使用。对前面表的每个行组合,MySQL检查是否可以使用range或index_merge访问方法来索取行。
    • SELECT tables optimized away:当我们使用某些聚合函数来访问存在索引的某个字段时,MySQL Query Optimizer会通过索引直接一次定位到所需的数据行完成整个查询。当然,前提是在Query中不能有GROUP BY操作。如使用MIN()或MAX()的时候。
    • Using filesort:当Query中包含ORDER BY操作,而且无法利用索引完成排序操作的时候,MySQL Query Optimizer不得不选择相应的排序算法来实现。
    • Using index:所需数据只需在Index即可全部获得,不须要再到表中取数据。
    • Using index for group-by:数据访问和Using index一样,所需数据只须要读取索引,当Query中使用GROUP BY或DISTINCT子句时,如果分组字段也在索引中,Extra中的信息就会是Using index for group-by。
    • Using temporary:当MySQL在某些操作中必须使用临时表时,在Extra信息中就会出现Using temporary。主要常见于GROUP BY和ORDER BY等操作中。
    • Using where:如果不读取表的所有数据,或不是仅仅通过索引就可以获取所有需要的数据,则会出现Using where信息。
    • Using where with pushed condition:这是一个仅仅在NDBCluster存储引擎中才会出现的信息,而且还须要通过打开Condition Pushdown优化功能才可能被使用。控制参数为engine_condition_pushdown。

这里通过分析示例来看一下不同的Query语句通过Explain所显示的不同信息。

先看一个简单的单表Query,如示例代码4所示:

对user表的单表查询,查询类型为SIMPLE,因为既没有UNION也不是子查询。聚合函数MAX、MIN及COUNT三者需要的数据都可以通过索引直接定位得到,所以整个实现的Extra信息为SELECT tables optimized away。

再来看一个稍微复杂一点的Query,一个子查询,如示例代码5所示:

通过id信息可以得知MySQL Query Optimizer给出的执行计划,首先是对groups进行全表扫描,第二步才访问user_group表,所使用的查询方式是DEPENDENT SUBQUERY,对所需数据的访问方式是索引扫描,由于过滤条件是一个整数,所以索引扫描的类型为ref,过滤条件是const。可以使用的索引有两个,一个是基于user_id,另一个则是基于group_id的。为什么基于group_id的索引user_group_gid_ind也被列为可选索引了呢?是因为与子查询的外层查询所关联的条件是基于group_id的。当然,最后MySQL Query Optimizer还是选择了使用基于user_id的索引user_group_uid_ind。

Profiling的使用

要想优化一条Query,就须要清楚这条Query的性能瓶颈到底在哪里,是消耗的CPU计算太多,还是需要的IO操作太多?要想能够清楚地了解这些信息,在MySQL 5.0和MySQL 5.1正式版中已经非常容易做到,即通过Query Profiler功能。

MySQL的Query Profiler是一个使用非常方便的Query诊断分析工具,通过该工具可以获取一条Query在整个执行过程中多种资源的消耗情况,如CPU、IO、IPC、SWAP等,以及发生的PAGE FAULTS、CONTEXT SWITCHE等,同时还能得到该Query执行过程中MySQL所调用的各个函数在源文件中的位置。下面看看Query Profiler的具体用法。

  • 通过执行“set profiling”命令,可以开启关闭Query Profiler功能。先开启profiling参数,如示例代码6所示:

  • 在开启Query Profiler功能之后,MySQL就会自动记录所有执行的Query的profile信息。下面执行Query,如示例代码7所示:

  • 通过执行“SHOW PROFILE”命令获取当前系统中保存的多个Query的profile的概要信息,如示例代码8所示:

  • 针对单个Query获取详细的profile信息。

在获取概要信息之后,就可以根据概要信息中的Query_ID来获取某个Query在执行过程中详细的profile信息了,具体操作如示例代码9所示:

上面的例子获取了CPU和Block IO的消耗,非常清晰,对于定位性能瓶颈非常适用。若希望得到其他的信息,都可以通过执行“SHOW PROFILE *** FOR QUERY n”来获取。

Join的实现原理及优化思路

Join的实现原理

在寻找Join语句的优化思路之前,首先要理解在MySQL中是如何实现Join的,只要理解了实现原理,优化就比较简单了。下面先分析一下MySQL中Join的实现原理。

在MySQL中,只有一种Join算法,就是大名鼎鼎的Nested Loop Join,它没有很多其他数据库所提供的Hash Join,也没有Sort Merge Join。顾名思义,Nested Loop Join实际上就是通过驱动表的结果集作为循环基础数据,然后将该结果集中的数据作为过滤条件一条条地到下一个表中查询数据,最后合并结果。如果还有第三个表参与Join,则把前两个表的Join结果集作为循环基础数据,再一次通过循环查询条件到第三个表中查询数据,如此往复。

下面将通过一个三表Join语句(如示例代码17)来说明MySQL的Nested Loop Join的实现方式。

然后看看Query的执行计划,如示例代码19所示:

可以看出,MySQL Query Optimizer选择了user_group作为驱动表,首先利用传入的条件(user_id)通过该表上的索引(user_group_uid_ind)进行const条件的索引ref查找,再以user_group表过滤出来的结果集中的group_id字段作为查询条件,对group_message循环查询,然后通过将user_group和group_message这两个表的结果集中的group_message的id作为条件,与group_message_content的group_msg_id比较进行循环查询,才得到最终的结果。

这个过程可以通过如下表达式来表示:

图示2可以更清晰的标识出实际的执行情况:

假如去掉group_message_content表中group_msg_id字段的索引,看看执行计划会变成怎样,如示例代码20所示:

不仅user_group表的访问从ref变成了ALL,而且最后一行的Extra信息从没有任何内容变成为Using where; Using join buffer,也就是说,对于从ref变成ALL很容易理解——既然没有可以使用的索引了,当然得进行全表扫描了,Using where也是因为变成全表扫描之后,content字段只能通过对表中的数据进行WHERE过滤才能取得,但是后面出现的Using join buffer是什么呢?

实际上,Join Buffer只有当Join类型为ALL(如示例中)、index、rang或index_merge时才能够使用,因此在去掉group_message_content表的group_msg_id字段的索引前,由于Join是ref类型的,所以执行计划中并没有使用Join Buffer。

在使用了Join Buffer之后,可以通过下面这个表达式描述出示例中Join的完成过程:

当然,通过图片(如图3)来展现或许更易于理解一些,如下:

通过上面的示例,我想大家应该对MySQL中Nested Join的实现原理有了一定的了解,也应该清楚MySQL使用Join Buffer的方法了。当然,这里并没有涉及外连接的内容,实际对于外连接来说,区别可能主要存在于连接顺序及组合空值记录方面。

Join语句的优化

在明白了MySQL中Join的实现原理后,我们就比较清楚该如何去优化Join语句了。

  • 尽可能减少Join语句中Nested Loop的循环总次数

如何减少Nested Loop的循环总次数?最有效的办法是让驱动表的结果集尽可能地小,这也正是在本章第二节中所提到的优化基本原则之一——“永远用小结果集驱动大结果集”。

因为驱动结果集越大,意味着需要循环的次数越多,也就是说在被驱动结果集上所需要执行的查询检索次数会越多。比如,当两个表(表A和表B)Join时,如果表A通过WHERE条件过滤后有10条记录,而表B有20条记录。如果选择表A作为驱动表,也就是被驱动表的结果集为20,那么我们通过Join条件对被驱动表(表B)的比较过滤就会进行10次。反之,如果选择表B作为驱动表,则须要进行20次对表A的比较过滤。

当然,此优化的前提条件是通过Join条件每次对各个表进行访问的资源消耗差别不是太大。如果资源消耗存在较大的差别(一般都是因为索引的区别),就不能简单地通过结果集的大小来判断Join语句的驱动顺序,而是要通过比较循环次数和每次循环所须消耗之乘积大小来确定优化方案了。

  • 优先优化Nested Loop的内层循环

不仅在数据库的Join中应该这样做,实际上在优化程序语言时也有类似的优化原则。内层循环是循环中执行次数最多的,每次循环节约很少的资源,就能在整个循环中节约很多的资源。

  • 保证Join语句中被驱动表的Join条件字段已经被索引

其目的正是基于上面两点的考虑,只有让被驱动表的Join条件字段被索引了,才能保证循环中每次查询都能够消耗较少的资源,这也正是内层循环的实际优化方法。

  • 当无法保证被驱动表的Join条件字段被索引且内存资源充足时,不要太吝惜Join Buffer的设置

在Join是All、Index、range或index_merge类型的特殊情况下,Join Buffer才能派上用场。在这种情况下,Join Buffer的大小将对整个Join语句的消耗起到非常关键的作用。

ORDER BY、GROUP BY和DISTINCT的优化

除了常规的Join语句之外,还有一类Query语句也是使用比较频繁的,即ORDER BY、GROUP BY及DISTINCT这三类查询。考虑到这三类查询都涉及数据的排序等操作,所以将它们放在了一起,下面就针对这三类Query语句做基本的分析。

ORDER BY的实现与优化

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

  • 一种是通过有序索引直接取得有序的数据,这样不用进行任何排序操作即可得到满足客户端要求的有序数据并返回给客户端;
  • 另外一种则须通过MySQL的排序算法将存储引擎中返回的数据进行排序后,再将排序后的数据返回给客户端。

下面就针对这两种实现方式做一个简单的分析。首先分析一下第一种不用排序的实现方式。如示例代码21所示:

该Query语句中明明有ORDER BY user_id,为什么在执行计划中却没有排序操作呢?其实正因为MySQL Query Optimizer选择了一个有序的索引来访问表中的数据(idx_group_message_ gid_uid),这样,通过group_id条件得到的数据已经按照group_id和user_id进行排序了。虽然排序条件仅有一个user_id,但是WHERE条件决定了返回数据的group_id全部一样,也就是说不管有没有根据group_id进行排序,返回的结果集都是完全一样的。可以通过图4来描述整个执行过程:

图中的Table A和Table B分别为上面Query中的group_message和gruop_message_content这两个表。

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

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

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

  1. 取出满足过滤条件作为排序条件的字段,以及可以直接定位到行数据的行指针信息,在Sort Buffer中进行实际的排序操作,然后利用排好序的数据根据行指针信息返回表中取得客户端请求的其他字段的数据,再返回给客户端;
  2. 根据过滤条件一次取出排序字段及客户端请求的所有其他字段的数据,并将不须要排序的字段存放在一块内存区域中,然后在Sort Buffer中将排序字段和行指针信息进行排序,最后再利用排序后的行指针与存放在内存区域中和其他字段一起的行指针信息进行匹配、合并结果集,再按照顺序返回给客户端。

上述第一种排序算法是MySQL一直以来就有的,而第二种则是从MySQL 4.1版本才开始增加的改进版排序算法。第二种算法与第一种相比,其主要优势就是减少了数据的二次访问。在排序之后不须要再一次回到表中取数据,节省了IO操作。当然,第二种算法会消耗更多的内存,这正是一种典型的通过内存空间换取时间的优化方式。下面同样通过一个实例(如示例代码22)来看看当MySQL不得不使用排序算法时的执行计划,仅须更改一下排序字段:

乍一看,好像整个执行计划并没有什么区别啊?但是细心的读者可能已经发现,在group_message表的Extra信息中,多了一个“Using filesort”的信息,实际上这就是MySQL Query Optimizer在告诉我们,它需要进行排序操作才能按照客户端的要求返回有序的数据。如图5所示。

这里可以看到,MySQL在取得第一个表的数据之后,先根据排序条件将数据进行了一次filesort,也就是排序操作。然后再将排序后的结果集作为驱动结果集通过Nested Loop Join访问第二个表。然而,大家不要误解,这个filesort并不是说通过磁盘文件进行排序,而只是告诉我们进行了一个排序操作。

上面看到了排序结果集来源只是单个表的比较简单的filesort操作。而在实际应用中,很多时候业务要求并不是这样,须要排序的字段可能同时存在于两个表中,或者MySQL在经过一次Join之后才进行排序操作。这样的排序在MySQL中并不能简单地利用Sort Buffer进行排序,而是必须先通过一个临时表将之前Join的结果集存放入临时表之后,再将临时表的数据取到Sort Buffer中进行操作。下面通过再次更改排序要求来说明这样的执行计划,在选择通过group_message_content表上的content字段来进行排序之后,结果如示例代码23所示:

这时执行计划中出现了“Using temporary”,正是因为排序操作须要在两个表Join之后才能进行,图6展示了这个Query的执行过程:

首先是Table A和Table B进行Join,然后结果集进入临时表,再进行filesort,最后得到有序的结果集数据返回给客户端。

上面通过两个不同的示例展示了当MySQL无法避免要使用相应的排序算法进行排序操作时的实现原理。虽然在排序过程中所使用的排序算法有两种,但是两种排序的内部实现机制大体上差不多。

当无法避免排序操作时,又该如何来优化呢?很显然,应该尽可能让MySQL选择使用第二种算法来进行排序。这样可以减少大量的随机IO操作,很大幅度地提高排序工作的效率。

  • 加大max_length_for_sort_data参数的设置

在MySQL中,决定使用老式排序算法还是改进版排序算法是通过参数max_length_for_ sort_data来决定的。当所有返回字段的最大长度小于这个参数值时,MySQL就会选择改进后的排序算法,反之,则选择老式的算法。所以,如果有充足的内存让MySQL存放须要返回的非排序字段,就可以加大这个参数的值来让MySQL选择使用改进版的排序算法。

  • 去掉不必要的返回字段

当内存不是很充裕时,不能简单地通过强行加大上面的参数来强迫MySQL去使用改进版的排序算法,否则可能会造成MySQL不得不将数据分成很多段,然后进行排序,这样可能会得不偿失。此时就须要去掉不必要的返回字段,让返回结果长度适应max_length_for_sort_data参数的限制。

  • 增大sort_buffer_size参数设置

增大sort_buffer_size并不是为了让MySQL选择改进版的排序算法,而是为了让MySQL尽量减少在排序过程中对须要排序的数据进行分段,因为分段会造成MySQL不得不使用临时表来进行交换排序。

GROUP BY的实现与优化

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

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

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

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

下面通过一个示例来描述松散索引扫描实现GROUP BY,在示例之前须要首先调整一下group_message表的索引,将gmt_create字段添加到group_id和user_id字段的索引中,如示例代码24所示:

然后再看如下Query的执行计划,如示例代码25所示:

可以看到在执行计划的Extra信息中显示有“Using index for group-by”,实际上这就是在告诉我们,MySQL Query Optimizer通过使用松散索引扫描实现了GROUP BY操作。

图7描绘了扫描的主要实现过程:

要利用到松散索引扫描实现GROUP BY,需要至少满足以下几个条件:

  • GROUP BY条件字段必须处在同一个索引中最前面的连续位置;
  • 在使用GROUP BY的同时,只能使用MAX和MIN这两个聚合函数;
  • 如果引用到了该索引中GROUP BY条件之外的字段条件,它就必须以常量形式存在;

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

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

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

紧凑索引扫描和松散索引扫描的区别主要在于前者须要在扫描索引时,读取所有满足条件的索引键,然后再根据读取到的数据来完成GROUP BY操作,以得到相应结果,见代码26。

这时执行计划的Extra信息中已经没有“Using index for group-by”了,但并不是说MySQL的GROUP BY操作不是通过索引完成的,只不过是须要访问WHERE条件所限定的所有索引键信息之后才能得出结果。这就是通过紧凑索引扫描来实现GROUP BY的执行计划输出信息。

图8展示了主要的执行过程。

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

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

  • 使用临时表实现GROUP BY

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

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

这次的执行计划非常明显地告诉了我们MySQL通过索引找到了所需的数据,然后创建了临时表,又进行了排序操作,才得到所需的GROUP BY结果。整个执行过程大概如图9所示:

当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会利用临时表来做一次MySQL会利用临时表来做一次数据的“缓存”,但不会对临时表中的数据进行filesort操作。当然,如果在进行DISTINCT操作时还使用了GROUP BY,并进行了分组,且使用了类似于MAX之类的聚合函数操作,就无法避免filesort了。

下面就通过几个简单的Query示例来展示一下DISTINCT的实现。

  • 首先看看通过松散索引扫描完成DISTINCT操作的过程,如示例代码28所示:

可以很清晰地看到,执行计划中的Extra信息为“Using index for group-by”,这代表什么?为什么在没有进行GROUP BY操作时,执行计划中会告诉我这里通过索引进行了GROUP BY呢?其实它与DISTINCT的实现原理相关,在实现DISTINCT的过程中,同样也是须要分组的,然后再从每组数据中取出一条返回给客户端。而这里的Extra信息就是在告诉我们,MySQL利用松散索引扫描就完成了整个操作。当然,如果MySQL Query Optimizer能够做得再人性化一点,将这里的信息换成“Using index for distinct”那就更容易让人理解了。

  • 再来看看紧凑索引扫描的结果,如示例代码29所示:

该显示和通过紧凑索引扫描实现GROUP BY的结果完全一样。实际上,在这个Query的实现过程中,MySQL会让存储引擎扫描group_id = 2的所有索引键,得出所有的user_id,然后利用索引的已排序特性,在每更换一个user_id的索引键值时保留一条信息,这样就可在扫描完所有group_id = 2的索引键时完成整个DISTINCT操作。

  • 下面再看看单独使用索引无法完成DISTINCT操作时会是怎样,如果如示例代码30所示:

当MySQL仅依赖索引无法完成DISTINCT操作时,就不得不使用临时表来进行相应的操作了。但是可以看到,当MySQL利用临时表来完成DISTINCT时,和处理GROUP BY有一点区别,就是少了filesort。实际上,在MySQL的分组算法中,并不一定非要排序才能完成分组操作,这一点在上面的GROUP BY优化小技巧中已经提到过了。实际上这里MySQL正是在没有排序的情况下实现分组,最后完成DISTINCT操作的,所以少了filesort这个排序操作。

参考文档