MySQL查询性能优化详解 [复制链接]

1.序言

当前主流互联网应用大多采用MySQL来部署构架应用的存储体系。

在工作中，我们发现，大多数RD同学往往会忽略MySQL查询语句的性能；当出现数据库性能问题的时候，也会一头雾水，无从下手。

在多次经历并协助解决生产环境中MySQL查询性能低下而导致线上服务受影响之后，准备对MySQL的查询性能进行系统总结，旨在让大家深入理解MySQL如何执行查询，并且能够洞察查询高效/低效的本质原因。

MySQL的业务处理分为数据引擎层阶段和数据服务层阶段。

数据服务层主要是进行SQL语句优化、构建查询执行计划工作。数据引擎层主要进行数据存储，并且为数据服务层的查询提供api接口。索引就是在引擎近端来完成的。本文主要从索引角度对MySQL的查询进行优化分析。

2.性能优化简介

2.1何为性能

（定义1）性能：完成某件任务所需要的时间度量称之为性能。

性能优化的目的即：在一定的工作负载（资源消耗）前提下，尽可能的降低任务执行所消耗的时间。值得注意的是这里的前提条件——一定的资源消耗，很多人可能会忽略这一点。在算法设计上，也有以牺牲空间资源来换取时间的情况。但这些换取的前提是：资源消耗控制在可接受的范围，脱离这个前提的任何性能优化，都是不成立的。

2.2性能方法论

在分析MySQL性能之前，我们需要首先需要明确MySQL执行查询的过程。然后才能明确服务器的时间都花在哪了？

MySQL查询执行过程

查询缓存

可以使用如下的语句来判断MySQL是否开启了查询缓存功能：

showvariableslike%query_cache%如果想查看MySQL是否是读取的缓存，可以使用如下的语句：

showstatuslikeqcache_hits;值得注意的是，如果表的数据存在更新的话，和该表相关的所有缓存都会被清空。如果当前的查询正好命中缓存，那么此次查询就会省略之后的所有操作，并直接从缓存中返回数据。

查询执行计划

MySQL服务器层对SQL语句进行一系列优化之后，会生成一颗查询指令树，然后执行引擎会利用api调用完成查询指令树并返回结果。可以使用如下的步骤来查看，MySQL优化之后的查询：

explainextendedselect*fromactor;showwarnings;时间去哪了

SQL执行时间；SQL等待时间；然后通过分析推理确认为什么时间去那里了。

3.MySQL中的查询分类

3.1简单查询

如果select语句中不包含任何子查询或者union，则称之为简单查询。

3.2复杂查询

复杂查询一般有3种类型：

简单子查询select(select1fromactorlimit1)fromfilmlimit1;复杂的派生表select1from(select1fromactorlimit1)limit1;union查询select1unionselect1;4.explain命令

explain用来获取查询执行计划的相关信息，并且对输出结果进行评估和解释。虽然该命令有它的局限性，但是该命令的可以获取到查询经过MySQL查询优化器处理后是如何执行的。在select语句之前增加explain时，MySQL会在该查询上设置一个标记。当查询执行时，MySQL不会真的执行查询，而是返回执行计划中每一步的信息。explain返回的信息包括：执行计划中得每一部分信息和执行的顺序。explain命令的执行结果，一般如下图所示：

在如图所示的结果中，explain的结果一般会包含如下的列：id,select_type,table,possible_keys,key,key_len,ref,rows,Extra。为了能够理解explain描述的真正意义，我们接下来将详细解释这些列的含义。

4.1explain结果纵向分析

id

id为用来标识select所属的行的编号。对于复杂查询而言，explain中得每一行都会对应到查询中用到的一个表。而id则用来表示MySQL在查询过程中得循序，一般而言，id越大，优先级越高，查询越先执行。如果id相同，则顺序执行。接下来我们分别对不同的SQL语句来讨论。

简单查询

selectactor_idfromactorlimit1;explain分析结果如下图所示：

简单查询还有一种就是joininner语句，例如：

selectfilm.film_idfromfilminnerjoinfilm_actorusing(film_id)innerjoinactorusing(actor_id);explain的分析结果如下图所示：

简单子查询

select(selectfilm_idfromfilm_actorlimit1)asidfromfilmlimit1;explain的分析结果如下图所示：

派生表子查询

selectf.film_idfrom(selectfilm_idfromfilm_actor)asflimit1;explain的分析结果如下图所示：

临时表的查询

explainselectfilm_idfromfilmunionselectfilm_idfromfilm_actor;explain的分析结果如下图所示：

可以看到，第三行的id为NULL，这主要是因为MySQL会把union操作的结果放在一个临时表中（从第三行的Extra值可以看出），之后的操作将会直接读取该临时表。而该临时表在原始的SQL语句中并没有出现，因此id被标识为NULL。

select_type

select_type列用来标识explain结果中对应的行上的查询类型，从而用来说明查询是简单查询还是复杂查询。如果是复杂查询，那么是哪种类型的复杂查询。一般而言，select_type的值包括：SIMPLE,PRIMARY,SUBQUERY,DERIVED,UNION,UNIONRESULT。关于select_type的各种类型，请参考上一节对id的描述。

table

table列用来标识对应的行正在访问哪个表。id，select_type，table列的含义简单明了，此处统一使用介绍id列的几个例子来演示。

type

MySQL官方文档中对type的定义如下（MySQL5.6ReferenceManualP）：ThetypecolumnofEXPLAINoutputdescribeshowtablesarejoined。翻译一下：type标识了对应行的table的关联类型。在MySQL中，关联（join）所表示的含义比一般的关联要广泛。并不是只有需要多个表的查询才能称为关联，在MySQL中，任何一个查询都是一次关联（join）。因此，我们可以认为，type反应了MySQL采用什么方式查找表中的数据。根据MySQL官方文档的描述，type的性能，从最好到最坏依次如下所示：const,system-eq_ref-ref-range-index-ALL。

const

当利用主键索引或唯一索引来进行等值条件查询的时候，就会采用const的方式来访问表。

range

当使用范围条件来对索引进行查询时，会采用range的方式访问表数据。对于range需要注意，MySQL是不能使用range列之后的条件的索引列的索引的。

index

index访问类型和ALL全表扫描基本一样（不要惊慌，真理就是这么让人难以接受，接着看...），唯一不同点在于index是按照索引顺序进行扫描。而ALL访问类型是按照行顺序进行扫描。这里的扫描可能是扫描索引，也可能是扫描表。index的优点在于避免了排序，提升了查询效率。但是，缺点也显而易见，当待扫描的行较大时，开销也较大。在后面的Extra字段中，我还会介绍Extra字段中得Usingindex。一定要对这两者进行区分。Usingindex表示MySQL使用覆盖索引，覆盖索引只会扫描索引的数据，避免扫描表，从而提升效率。

ALL

一旦出现type=ALL的情况，表明当前行正在采用全表扫描的方式。

possible_keys

该列显示查询可以使用的索引。MySQL通过查询访问的列和使用的比较操作来判断该列的值。

key

对于explain结果中得某一行的查询，该列的值标识MySQL决定采用的索引。主要注意possible_keys列和keylie的关系。possible_keys显示出查询可用的索引，而key则显示决定查询最终使用的索引。某些情况下，key的值在possible_keys中不存在，这个时候说明MySQL出于某种原因对查询进行了优化——在没有where查询子句时使用覆盖索引。

explainselectactor_id,film_idfromfilm_actor;

key_len

key_len表示MySQL在索引里使用的字节数。对于覆盖索引，如果MySQL只使用了索引的某些列，那么我们就可以通过key，key_len，索引最左前缀匹配原则来推算究竟使用了那一列作为索引。

explainselectactor_id,film_idfromfilm_actorwhereactor_id=3;

key=PRIMARY,PRIMARY=(actor_id,film_id),len=2,根据最左匹配原则，该查询使用了actor_id索引列。值得注意的是，key_len显示了在索引字段中可能的最大长度，而不是表中数据使用的实际字节数。实际上真正的索引结构中存储的数据有可能比key_len要少。

ref

ref显示了某次查询在利用key中得索引进行查找时，查找值所用的列或者常量。

explainselectlast_namefromactorinnerjoinfilm_actorwhereactor.actor_id=film_actor.actor_id;

如果把innerjoin的连个表调换一下顺序，我们会发现explain的结果是一致的。从这一点也可以看出，MySQL底层会进行一定程度的优化。

rows

rows表示MySQL为了找到所需要的数据而要读取的表的行数。

explainselectlast_namefromactorinnerjoinfilm_actorwhereactor.actor_id=film_actor.actor_id;

一般我们可以通过将explain结果中所有行的rows相乘的结果来估算某次查询需要读取的行数。注意，此处是估算的行数，而不是真正执行查询时要读取的行。

Extra

这里列的数据有些特别，Extra的用途和Linux目录结构标准中得misc目录类似，用来存放一些杂项，或者不适合放到其它列中得数据。Extra的值需要参考其它列的值才能有意义，单纯的观察Extra的值有时候也是没有意义的。一般，Extra的值主要有：Usingindex，Usingwhere，Usingtemporary，Usingfilesort。

Usingindex

Usingindex表示MySQL使用覆盖索引，覆盖索引只会扫描索引的数据，避免扫描表，从而提升效率。一定不要和type字段中得index混淆。type字段中的index访问类型和ALL全表扫描基本一样（不要再次惊慌，真理就是这么让人难以接受，接着看...），唯一不同点在于index是按照索引顺序进行表扫描。而ALL访问类型是按照行顺序进行表扫描。index的优点在于避免了排序，提升了查询效率。但是，缺点也显而易见，当待扫描的行较大时，开销也较大。

Usingwhere

Usingwhere表示：MySQL服务器在存储引擎检索行之后，需要再次进行过滤。来比较一下如下的两条语句(first_name没有索引，而last_name有索引)：

S1)explainselect*fromactorwherefirst_name=;S2)explainselect*fromactorwherelast_name=;

S1中得type=ALL，Extra=Usingwhere，从而我们可以推测，数据引擎会按行扫描全表数据并返回全表结果，然后数据库服务器再对全表数据进行过滤。S2中type=ref，Extra=Usingindex，从而可以知道，数据库引擎在检索的时候通过利用覆盖索引的方式进行检索，检索之后的数据不需要在经过数据库的筛选。因此，并不是所有的where查询都会导致数据检索后的过滤操作。这带给我们无限的遐想：可以利用不同的索引优化查询效率。

Usingtemporary

Usingtemporay表示：在查询中，MySQL会使用一个临时表。

explainselect1unionselect2;

Usingfilesort

一旦出现这个东东，大家就要