mysql的执行计划.docx
《mysql的执行计划.docx》由会员分享，可在线阅读，更多相关《mysql的执行计划.docx（21页珍藏版）》请在冰豆网上搜索。
mysql的执行计划
MySQL执行计划explain详解
explain命令是查看查询优化器如何决定执行查询的主要方法。
这个功能有局限性，并不总会说出真相，但它的输出是可以获取的最好信息，值得花时间去了解，因为可以学习到查询是如何执行的。
SQL优化准则
禁用select*
使用selectcount（*）统计行数
尽量少运算
尽量避免全表扫描，如果可以，在过滤列建立索引
尽量避免在where子句对字段进行null判断
尽量避免在where子句使用!
=或者<>
尽量避免在where子句使用or连接
尽量避免对字段进行表达式计算
尽量避免对字段进行函数操作
尽量避免使用不是复合索引的前缀列进行过滤连接
尽量少排序，如果可以，建立索引
尽量少join
尽量用join代替子查询
尽量避免在where子句中使用in,notin或者having，使用exists,notexists代替
尽量避免两端模糊匹配like%***%
尽量用unionall代替union
尽量早过滤
避免类型转换
尽量批量insert
优先优化高并发sql，而不是频率低的大sql
尽可能对每一条sql进行explain
尽可能从全局出发
2、 执行计划的生成和查看
2.1执行计划的生成方法：
explainselect…………….
生成的方法很简单在相应的select前面加explain即可
2.2执行计划的查看
Id：
包含一组数字，表示查询中执行select子句或操作表的顺序；
执行顺序从大到小执行；
当id值一样的时候，执行顺序由上往下；
Select_type:
表示查询中每个select子句的类型（简单OR复杂），有以下几种
?
SIMPLE：
查询中不包含子查询或者UNION
?
PRIMARY：
查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
?
SUBQUERY：
在SELECT或WHERE列表中包含了子查询，该子查询被标记为SUBQUERY
?
DERIVED：
在FROM列表中包含的子查询被标记为DERIVED（衍生）
?
若第二个SELECT出现在UNION之后，则被标记为UNION；
?
若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：
DERIVED
?
从UNION表获取结果的SELECT被标记为：
UNIONRESULT
Type：
表示MySQL在表中找到所需行的方式，又称“访问类型”，常见有以下几种
?
ALL：
FullTableScan，MySQL将进行全表扫描；
?
index：
FullIndexScan，index与ALL区别为index类型只遍历索引树；
?
range：
rangeIndexScan，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询；
?
ref：
非唯一性索引扫描，返回匹配摸个单独值的所有行。
常见于使用非唯一索引或唯一索引的非唯一前缀进行的查找；
?
eq_ref：
唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。
常见于主键或唯一索引扫描
?
const、system：
当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。
如将主键置于where列表中，MySQL就能将该查询转换为一个常量
?
NULL：
MySQL在优化过程中分解语句，执行时甚至不用访问表或索引
possible_keys：
指出MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用；
key：
显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。
当查询中若使用了覆盖索引，则该索引仅出现在key列表中
key_len：
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
ref：
表示上述表的连接匹配条件，即那些列或常量被用于查找索引列上的值；
rows：
表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数；
Extra：
包含不适合在其他列中显示但十分重要的额外信息；
?
Usingwhere：
表示MySQL服务器在存储引擎受到记录后进行“后过滤”（Post-filter）,如果查询未能使用索引，Usingwhere的作用只是提醒我们MySQL将用where子句来过滤结果集
?
Usingtemporary：
表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询；
?
Usingfilesort：
MySQL中无法利用索引完成的排序操作称为“文件排序”；
2.3mysql执行计划的局限
EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
EXPLAIN不考虑各种Cache
EXPLAIN不能显示MySQL在执行查询时所作的优化工作
部分统计信息是估算的，并非精确值
EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划
3、对于非select语句查看执行计划
在实际的工作中也经常需要查看一些诸如update、delete的执行计划，（mysql5.6的版本已经支持直接查看）但是这时候并不能直接通过explain来进行查看，而需要通过改写语句进行查看执行计划；
在一个生产数据库的慢查询日志发现有条语句如下：
Count:
13Time=73.44s（954s）Lock=0.00s（0s）Rows=0.0（0）,ipos[ipos]@2hosts
updateipos_zdjhdm,ipos_zdjhdtjtjsetm.qr=N,m.qrrq='S',m.qrr='S',tj.qr=N,tj.qrrq='S'
wherem.ydjh='S'andtj.djbh='S'
可以改写如下：
ExplainSelectm.qr,m.qrr,tj.qr,tj.qrrqfromipos_zdjhdm,ipos_zdjhdtjtjwherem.ydjh='17233'andtj.djbh='48632';
马上可以发现ipos_zdjhd表进行了全表扫描，而ipos_zdjhd表有1076971行的数据，所以整个update的操作肯定是一个很慢的过程，经过和开发人员沟通后，在ipos_zdjhd表增加相应的索引便让整个过程提升了500倍。
小结：
执行计划加上慢查询日志组成了mysql调优过程的一组调优利器，当数据库稳定过后参数的调优是很少的一部分，80%以上的调优都会是SQL调优。
调用EXPLAIN
在select之前添加explain，mysql会在查询上设置一个标记，当执行查询计划时，这个标记会使其返回关于执行计划中每一步的信息，而不是执行它。
它会返回一行或多行信息，显示出执行计划中的每一部分和执行次序。
这是一个简单的explain效果：
在查询中每个表在输出只有一行，如果查询是两个表的联接，那么输出中将有两行。
别名表单算为一个表，因此，如果把一个表与自己联接，输出中也会有两行。
“表”的意义在这里相当广，可以是一个子查询，一个union结果等。
同时explain有两个变种
EXPLAINEXTENDED会告诉服务器“逆向编译”执行计划为一个select语句。
可以通过紧接其后运行showwarnings看到这个生成的语句，这个语句直接来自执行计划，而不是原SQL语句，到这点上已经变成一个数据结构。
大部分场景下，它都与原语句不相同，你可以检测查询偶花旗到底是如何转化语句的。
EXPLAINEXTENDED在mysql5.0以上版本中可用，在5.1中增加了一个filtered列。
EXPLAINPARTITIONS会显示查询将访问的分区，如果查询是基于分区表的话。
在mysql5.1以上的版本中会存在。
EXPLAIN限制：
·explain根本不会告诉你触发器、存储过程或UDF会如何影响查询
·不支持存储过程，尽管可以手动抽取查询并单独地对其进行explain操作
·它并不会告诉你mysql在执行计划中所做的特定优化
·它并不会显示关于查询的执行计划的所有信息
·它并不区分具有相同名字的事物，例如，它对内存排列和临时文件都使用“filesort”，并且对于磁盘上和内存中的临时表都显示“Usingtemporary”
·可能会产生误导，比如，它会对一个有着很小limit的查询显示全索引扫描（mysql5.1的explain关于检查的行数会显示更精准的信息，但早期版本并不考虑limit）
重写非SELECT查询
mysqlexplain只能解释select查询，并不会对存储程序调用和insert、update、delete或其他语句做解释。
然而，你可以重写某些非select查询以利用explain。
为了达到这个目的，只需要将该语句转化成一个等价的访问所有相同列的select，任何体积的列都必须在select列表，关联子句，或者where子句中。
假如，你想重写下面的update语句使其可以利用explain
1.UPDATE sakila.actor
2.INNERJOINsakila.film_actorUSING （actor_id）
3.SET actor.last_update=film_actor.last_update;
下面的explain语句并不等价于上面的update，因为它并不要求服务器从任何一个表上获取last_update列
这个差别非常重要。
例如，输出结果显示mysql将使用覆盖索引，但是，当检索并更新last_updated列时，就无法使用覆盖索引了，下面这种改写法就更接近原来的语句：
像这样重写查询并不非常科学，但对帮助理解查询是怎么做的已经足够好了。
（MySQL5.6将允许解释非SELECT查询）
显示查询计划时，对于写查询并没有“等价”的读查询，理解这一点非常重要。
一个SELECT查询只需要找到数据的一份副本并返回。
而任何修改数据的查询必须在所有索引上查找并修改其所有副本，这常常比看起来等价的SELECT查询的消耗要高得多。
EXPLAIN中的列
将在下一部分展示explain结果中每一列的意义。
输出中的行以mysql实际执行的查询部分的顺序出现，而这个顺序不总是与其在原始SQL中的一致。
【id列】
这一列总是包含一个编号，识别select所属的行，如果在语句当中没有子查询或联合，那么只会有唯一的select，于是每一行在这个列中都将显示一个1，否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置。
mysql将select查询氛围简单和复杂类型，复杂类型可分为三大类：
简单子查询，所谓的派生表（在from子句中的子查询），以及union查询。
下面是一个简单的子查询：
from子句中的子查询和联合给id列增加了更多的复杂性。
下面是一个from子句中的基本子查询：
如你所知，这个查询执行时有一个匿名的临时表，mysql内部通过别名（der）在外层查询中引用这个临时表，在更复杂的查询中可以看到ref列。
最后下面是一个union查询：
注意第三个额外的行，union的结果总是放在一个匿名临时表中，之后mysql将结果读取到临时表外，临时表并不在原SQL中出现，因此它的id列为null。
与之前的例子相比（演示子查询的那个from子句中），从这个查询产生的临时表在结果中出现在最后一行，而不是第一行。
到目前为止这些都非常直截了当，但这三类语句的混合则会使输出变得非常复杂，我们稍后就会看到。
【select_type列】
这一列显示了对应行是简单还是复杂的select（如果是后者，那么是三种复杂类型中的哪一种）。
simple值意味着查询不包括子查询和union，如果查询有任何负责的子部分，则最外层部分标记为primary，其他部分标记如下：
SUBQUERY
包含在select列表中的子查询中的select（换句话说，不在from子句中）标记为SUBQUERY
DERIVED
DERIVED值用来表示包含在FROM子句的子查询中的select，mysql会递归执行并将结果放到一个临时表中。
服务器内部称其“派生表”，因为该临时表是从子查询中派生来的。
UNION
在UNION中的第二个和随后的select被标记为unoin，第一个select被标记就好像它以部分外查询来执行。
这就是之前的例子中在union中的第一个select显示为primary的原因。
如果union被from子句中的子查询包含，那么它的第一个select会被标记为derived。
UNIONRESULT
用来从union的匿名临时表检索结果的select被标记为UNIONRESULT。
除了这些值，SUBQUERY和UNION还可以被标记为DEPENDENT何UNCACHEABLE。
DEPENDENT意味着select依赖于外层查询中发现的数据。
UNCACHEABLE意味着select中的某些特性阻止结果被缓存于一个Item_cache中。
（Item_cache未被文档记载，它与查询缓存不是一回事，尽管它可以被一些相同类型的构件否定，例如RAND（）函数。
）
【table列】
这一列显示了对应行正在访问哪个表，在通常情况下，它相当明了：
它就是哪个表，或是该表的列明（如果SQL中定义了别名）。
可以在这一列中从上往下观察mysql的关联优化器为查询选择的关联顺序，例如，可以看到在下面的查询中mysql选择的关联顺序不同于语句中所指定的顺序：
mysql的执行计划总是左侧深度优先树，如果把这个计划放倒，就能按顺序读出叶子节点，它们直接对应于explain中的行，之前的查询计划看起来如下图所示：
派生表和联合
当from子句中有子查询或有union时，table列会变得复杂很多，这些场景下，确实没有一个“表”可以参考到，因为mysql创建的匿名临时表仅在查询执行过程中存在。
当在from子句中有子查询时，table列是的形式，其中N是子查询的id。
这总是“向前引用”——换言之，N指向explain输出中后面的一行。
当有union时，unionresult的table列包含一个参与union的id列表。
这总是“向后引用”，因为unionresult出现在union中所有参与行之后，如果在列表中有超过20个id，table列卡诺被截断以防止太长，此时不可能看到所有的值。
幸运的是，仍然可以推测包括哪些行，因为你可以看到第一行的id，在这一行和unionresult之间出现的一切都会以某种方式被包含。
一个复杂select类型的例子
下面是一个无意义的查询，我们这里把它用作某种复杂select类型的紧凑示例
limit子句只是为了方便起见，以防你打算不以explain方式执行来看结果。
以下是explain的部分结果：
我们特意让每个查询部分访问不同的表，以便可以弄清楚问题所在，但仍然难以解决，从上面开始看起：
第1行向前引用了der_1，这个查询被标记为，在原SQL中是第2行，想了解输出中哪些行引用了中的select语句，往下看。
第2行，它的id是3，因为它是查询中第3个select的一部分，归为derived类型是因为它嵌套在from子句中的子查询内部，在原sql中为第4行。
第3行的id为2，在原sql中为第3行，注意，它在具有更高id的行的后面，暗示后面再执行，这是合理的。
它被归为DEPENDENTSUBQUERY，意味着其结果依赖于外层查询（亦即某个相关子查询）。
本例中的外查询从第2行开始，从der_1中检索数据的select。
第4行被归为union，意味着它是union中的第2个或之后的select，它的表为，意味着是从子句from的子查询中检索数据并附加到union的临时表。
像之前一样，要找到显示这个子查询的查询计划的explain行，往下看。
第5行是在原sql中的第8行的der_2子查询，explain称其为。
第6行是的select列表中的一个普通子查询，它的id为7，这非常重要……
……因为它比5大，而5是第7行的id。
为什么重要？
因为它显示了子查询的边界。
当explain输出select类型为derived的一行时，表示一个“嵌套范围”开始。
如果后续行的id更小（本例中，5小于6），意味着嵌套范围已经被关闭。
这就让我们知道第7行是从中检索数据的select列表中的部分——例如，第4行的select列表的第一部分（原sql中的第7行）。
这个例子相当容易理解，不需要知道嵌套范围的意义和规则，当然有时候并不是这么容易。
关于输出中的这一行另外一个要注意的是，因为有用户变量，它被列为UNCACHEABLESUBQUERY。
最后一行unionresult，它代表从union的临时表中读取行的阶段。
你可以从这行开始反过来向后，如果你愿意的话，它返回id是1和4的行结果，它们分别引用了和
如你所见，这些复杂的select类型的组合会使explain的输出相当难懂，理解规则会使其简单些，但仍然需要多时间。
阅读explain的输出经常需要在列表中跳来跳去，例如，再查看第1行输出，仅仅盯着看，是无法知道它是union的一部分的，只有看到最后1行你才会明白过来。
【type列】
mysql用户手册上说这一列显示了“关联类型”，但我们认为更准确的说法是访问类型——换言之就是mysql决定如何查找表中的行。
下面是最重要的访问方法，依次从最差到最优：
ALL：
这就是所谓的全表扫描，意味着mysql必须扫描整张表，从头到尾，去找到需要的行。
（有个例外，例如在查询里使用了limit，或者在extra列中显示“Usingdistinct/notexists”。
index：
这个跟全表扫描一样，只是mysql扫描表时按索引次序而不是行，它的主要优点是避免了排序；最大缺点是要承担按索引次序读取整个表的开销。
这通常意味着若是按随机次序访问行，开销将非常大。
如果在extra列中看到“Usingindex”，说明mysql正在使用索引覆盖，它只扫描索引的数据，而不是按索引次序的每一行，它比按索引次序全表扫描的开销要少很多。
range：
范围扫描就是一个有限制的索引扫描，它开始与索引里的某一点，返回匹配这个值域的行，这比全索引扫描要好一点，因为它用不着遍历全部索引，显而易见的扫描是带有between或在where子句里带有>的查询。
当mysql使用索引去查找一系列值时，例如in（）和or列表，也会显示为范围扫描，然而，这两者其实是相当不同的访问类型，在性能上有重要的差异。
此类扫描的开销跟索引类型的相当。
ref：
这是一种索引访问（有时也叫做索引查找），它返回所有匹配某个单个值的行，然而，它可能会找到多个符合条件的行，因此，它是查找和扫描的混合体，此类索引访问只有当使用非唯一性索引或者唯一性索引的非唯一性前缀时才会发生。
把它叫做ref是因为索引要跟某个参考值相比较。
这个参考值或者是一个常数，或者是来自多表查询前一个表里的结果值。
ref_or_null是ref之上的一个变体，它意味着mysql必须在初次查找的结果里进行第二次查找以找出null条目。
eq_ref：
使用这种索引查找，mysql知道最多只返回一条符合条件的记录，这种访问方法可以在mysql使用主键或者唯一性索引查找时看到，它会将它们与某个参考值做比较。
mysql对于这类访问类型的优化做得非常好，因为它知道无需估计匹配行的范围或在找到匹配行后再继续查找。
const，system：
当mysql能对查询的某部分进行优化并将其转换成一个常量时，他就会使用这些访问类型，举例来说，如果你通过将某一行的主键放入where子句里的方式来选取此行的主键，mysql就能把这个查询转换为一个常量，然后就可以高效地将表从联接执行中移除。
null：
这种访问方式意味着mysql能在优化阶段分解查询语句，在执行阶段甚至用不着再访问表或者索引。
例如，从一个索引列里选取最小值可以通过单独查找索引来完成，不需要在执行时访问表。
【possible_keys列】
这一列显示了查询可以使用哪些索引，这是基于查询访问的列和使用的比较操作符来判断的。
这个列表是在优化过程的早期创建的，因此有些罗列出来的索引可能对于后续优化过程是没用的。
【key列】
这一列显示了mysql决定采用哪个索引来优化对该表的访问。
如果该索引没有出现在possible_keys列中，那么mysql选用它是处于另外的原因——例如，它可能选择了一个覆盖索引，哪怕没有where子句。
换句话说，possible_keys揭示了哪一个索引能有助于高效地进行查找，而key显示的是优化采用哪一个索引可以最小化查询成本。
下面是一个例子：
【key_len列】
该列显示了mysql在索引里使用的字节数，如果mysql正在使用的只是索引里的某些列，那么就可以用这个值来算出具体是哪些列，要记住，mysql5.5及之前的版本只能使用索引的最左前缀，举例来说，film_actor的主键是两个smallint列，并且每个smallint列是两字节，那么索引中的每项是4字节，以下就是一个查询的示例：
基于结果中的key_len列，可以推断出查询使用唯一的首列——actor_id列，来执行索引查找，当我们计算列的使用情况时，务必把字符列中的字符集页考虑进去。
查看执行计划：
这个查询中平均长度为13字节，即为a列和b列的总长度，a列是3个字符，utf8下每一个最多为3字节，而b列是一个4字节整型。
mysql并不总是显示一个索引真正使用了多少，例如，如果对一个前缀模式匹配执行like查询，它会显示列的完全宽度正在被使用。
key_len列显示了在索引字段中可能的最大长度，而不是表中数据使用的实际字节数，在前面例子中mysql总是显示13字节，即使a列恰巧只包含一个字符长度。
换言之，key_len通过查找表的定义而被计算出，而不是表中的数据。
【ref列】
这一列显示了之前的表在key列记录的索引中查找值所用的列或常量，下面是一个展示关联条件和别名组合的例子，注意，ref列反映了在查询文本中film表是如何以f为别名的：
【rows列】
这一列是mysql估计为了找到所需的行而要读取的行数。
这个数字是内嵌循环关联计划里的循环数目，也就是说它不是mysql认为它最终要从表里读取出来的行数，而是mysql为了找到符合查询的每一点上标
升级会员 