本文转载自微信公众号《MySQL技术》,作者MySQL技术。转载本文请联系MySQL技术公众号。前言:在上一篇文章中,我们了解了MySQL慢日志。我们筛选出具体的慢SQL之后,就得想办法优化了。优化SQL的第一步应该是了解SQL的执行计划。在这篇文章中,我们来了解一下MySQL的explain执行计划。1、执行计划介绍执行计划是指一条SQL语句经过MySQL查询优化器优化后的具体执行方式。MySQL为我们提供了EXPLAIN语句来获取执行计划的信息。需要注意的是,EXPLAIN语句并不真正执行相关语句,而是通过查询优化器对语句进行分析,找到最优的查询计划,并显示相应的信息。执行计划通常用于SQL性能分析和优化等场景。通过explain的结果可以了解到数据表的查询顺序、数据查询操作的操作类型、可以命中哪些索引、实际会命中哪些索引、每个数据表有多少行等信息被查询。说明执行计划支持SELECT、DELETE、INSERT、REPLACE和UPDATE语句。我们一般用它来分析select查询语句。2.执行计划实践下面简单看一下下一条查询语句的执行计划:mysql>explainSELECT*FROMdept_empWHEREemp_noIN(SELECTemp_noFROMdept_empGROUPBYemp_noHAVINGCOUNT(emp_no)>1);+----+-------------+------------+------------+--------+---------------+--------+----------+------+--------+----------+------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+------------+----------+------------+--------+-----------------+--------+--------+-----+--------+---------+------------+|1|PRIMARY|dept_emp|NULL|ALL|NULL|NULL|NULL|NULL|331143|100.00|Usingwhere||2|SUBQUERY|dept_emp|NULL|index|PRIMARY,dept_no|PRIMARY|16|NULL|331143|100.00|Usingindex|+----+------------+----------+------------+--------+----------------+--------+----------+------+--------+----------+------------+可以看出执行计划结果中有12列,每列代表的含义汇总如下表:列名含义idSELECT查询的序列标识select_typeSELECT关键字对应的查询类型table使用的表名partitions匹配的分区,对于非分区表,值为NULLtype表的访问方式possible_keys实际上可能的索引键usedIndexkey_len所选索引的长度ref使用索引等效查询时,要与索引进行比较的列或常量行Estimatednumberofrowstobereadfiltered按表条件过滤后保留记录的百分比Extra附加信息我们看一下执行计划中一些重要列的详细信息:id:SELECTidentifier这是查询中SELECT的序号。如果该行引用其他行的联合结果,则该值可以为NULL。id相同时,执行顺序为从上到下;当id不同时,id值越大,优先级越高,越早执行。select_type:查询的类型,常用值有:SIMPLE:简单查询,不包含UNION或子查询。PRIMARY:如果查询包含子查询或其他部分,则外层SELECT将被标记为PRIMARY。SUBQUERY:子查询中的第一个SELECT。UNION:在UNION语句中,出现在UNION之后的SELECT。DERIVED:出现在FROM中的子查询将被标记为DERIVED。UNIONRESULT:UNION查询的结果。table:表示查询中使用的表名。每行都有一个对应的表名。除了普通的表,表名还可能是下面列出的值::这一行引用了id为N的表的派生表结果。派生表可能来自FROM语句中的子查询。:这一行是指id为N的表生成的物化子查询结果。type:查询执行的类型,描述了查询是如何执行的。所有值从最好到最差的顺序是:system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL几种常见类型的具体含义如下:system:If表使用引擎对表行统计是准确的(比如MyISAM),当表只有一行时,访问方式是system,是const的特例。const:表中最多有一行匹配的记录,一次查询就可以查到。常用于将主键或唯一索引的所有字段作为查询条件。eq_ref:查询联表时,当前表中只有上一张表中的行对应。它是除system和const之外最好的连接方法。常用于将主键或唯一索引的所有字段作为连接条件。ref:使用普通索引作为查询条件,查询结果可能会找到多行满足条件。index_merge:当查询条件使用多个索引时,表示启用IndexMerge优化,执行计划中的key列列出使用的索引。range:对索引列进行范围查询,执行计划中的key列表示使用哪个索引。index:查询遍历整个索引树,和ALL类似,只是扫描索引,索引一般在内存中,速度较快。ALL:全表扫描。possible_keys:possible_keys列表示MySQL在执行查询时可能使用的索引。如果该列为NULL,表示没有可以使用的索引;在这种情况下,您需要检查WHERE语句中使用的列,以查看是否可以向这些列中的一个或多个添加索引。查询性能。key:key列代表MySQL实际使用的索引。如果为NULL,则不使用索引。key_len:key_len列表示MySQL实际使用的索引的最大长度;当使用联合索引时,它可能是多个列的长度之和。在满足需求的前提下,越短越好。如果key列显示NULL,则key_len列也显示NULL。rows:rows列表示根据表统计和选择粗略估计要查找或读取的行数。值越小越好。Extra:此列包含有关MySQL解析查询的附加信息。通过这些信息,您可以更准确地了解MySQL是如何执行查询的。常用值如下:Usingfilesort:使用外部索引进行排序,不使用内部索引进行排序。使用临时表:MySQL需要创建一个临时表来存储查询结果,常见于ORDERBY和GROUPBY。Usingindex:表示查询使用覆盖索引,不回表,查询效率很高。Usingindexcondition:表示查询优化器选择使用索引条件下推的特性。Usingwhere:表示查询使用WHERE子句进行条件过滤。它通常在不使用索引时发生。Usingjoinbuffer(BlockNestedLoop):连接表的方法,即当被驱动表不使用索引时,MySQL会先读取驱动表放入joinbuffer中,然后遍历被驱动表并驾驶台。查询。提醒一下,当Extra列包含Usingfilesort或Usingtemporary时,MySQL性能可能会出现问题,应尽量避免。参考:https://dev.mysql.com/doc/refman/5.7/en/explain-output.htmlhttps://juejin.cn/post/6953444668973514789
