学MySQL执行计划觉今是而昨非

傷城~ 2022-12-06 12:32 73阅读 0赞

# 目标 #

1.  掌握EXPLAIN使用方法；
2.  明确SQL执行顺序；
3.  明确SQL访问方式的效率等级；
4.  明确SQL使用的具体索引。

--------------------

# 语法 #

EXPLAIN SQL，例如：EXPLAIN select \* from student;

--------------------

# **EXPLAIN输出列** #

<table> MySQL8.0官方释义 
 <thead> 
 <tr> 
 <th>Column</th> 
 <th>JSON Name</th> 
 <th style="width:375px;">Meaning</th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <th><a href="https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_id" rel="nofollow"><code>id</code></a></th> 
 <td><code>select_id</code></td> 
 <td style="width:375px;">The&nbsp;<code>SELECT</code>&nbsp;identifier</td> 
 </tr> 
 <tr> 
 <th><a href="https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_select_type" rel="nofollow"><code>select_type</code></a></th> 
 <td>None</td> 
 <td style="width:375px;">The&nbsp;<code>SELECT</code>&nbsp;type</td> 
 </tr> 
 <tr> 
 <th><a href="https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_table" rel="nofollow"><code>table</code></a></th> 
 <td><code>table_name</code></td> 
 <td style="width:375px;">The table for the output row</td> 
 </tr> 
 <tr> 
 <th><a href="https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_partitions" rel="nofollow"><code>partitions</code></a></th> 
 <td><code>partitions</code></td> 
 <td style="width:375px;">The matching partitions</td> 
 </tr> 
 <tr> 
 <th><a href="https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_type" rel="nofollow"><code>type</code></a></th> 
 <td><code>access_type</code></td> 
 <td style="width:375px;">The join type</td> 
 </tr> 
 <tr> 
 <th><a href="https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_possible_keys" rel="nofollow"><code>possible_keys</code></a></th> 
 <td><code>possible_keys</code></td> 
 <td style="width:375px;">The possible indexes to choose</td> 
 </tr> 
 <tr> 
 <th><a href="https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_key" rel="nofollow"><code>key</code></a></th> 
 <td><code>key</code></td> 
 <td style="width:375px;">The index actually chosen</td> 
 </tr> 
 <tr> 
 <th><a href="https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_key_len" rel="nofollow"><code>key_len</code></a></th> 
 <td><code>key_length</code></td> 
 <td style="width:375px;">The length of the chosen key</td> 
 </tr> 
 <tr> 
 <th><a href="https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_ref" rel="nofollow"><code>ref</code></a></th> 
 <td><code>ref</code></td> 
 <td style="width:375px;">The columns compared to the index</td> 
 </tr> 
 <tr> 
 <th><a href="https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_rows" rel="nofollow"><code>rows</code></a></th> 
 <td><code>rows</code></td> 
 <td style="width:375px;">Estimate of rows to be examined</td> 
 </tr> 
 <tr> 
 <th><a href="https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_filtered" rel="nofollow"><code>filtered</code></a></th> 
 <td><code>filtered</code></td> 
 <td style="width:375px;">Percentage of rows filtered by table condition</td> 
 </tr> 
 <tr> 
 <th><a href="https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_extra" rel="nofollow"><code>Extra</code></a></th> 
 <td>None</td> 
 <td style="width:375px;">Additional information</td> 
 </tr> 
 </tbody> 
</table>

*  ## id ##

表示SQL的执行顺序。
        如果id相同，SQL从上往下执行；id不相同，id值大的优先级更高，会先执行；id有相同也有不同，先执行id值大的SQL，
    在执行相同的id时，从上往下执行。

*  ## select\_type ##

表示SQL的查询类型。

<table> MySQL8.0官方释义 
 <thead> 
 <tr> 
 <th style="vertical-align:bottom;width:197px;">select_type&nbsp;Value</th> 
 <th style="vertical-align:bottom;width:176px;">JSON Name</th> 
 <th style="vertical-align:bottom;width:370pt;">Meaning</th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <th style="vertical-align:bottom;width:197px;">SIMPLE</th> 
 <td style="vertical-align:bottom;width:176px;">None</td> 
 <td style="vertical-align:bottom;width:370pt;">Simple&nbsp;SELECT&nbsp;(not using&nbsp;UNION&nbsp;or subqueries)</td> 
 </tr> 
 <tr> 
 <th style="vertical-align:bottom;width:197px;">PRIMARY</th> 
 <td style="vertical-align:bottom;width:176px;">None</td> 
 <td style="vertical-align:bottom;width:370pt;">Outermost&nbsp;SELECT</td> 
 </tr> 
 <tr> 
 <th style="vertical-align:bottom;width:197px;"><a href="https://dev.mysql.com/doc/refman/8.0/en/union.html" rel="nofollow">UNION</a></th> 
 <td style="vertical-align:bottom;width:176px;">None</td> 
 <td style="vertical-align:bottom;width:370pt;">Second or later&nbsp;SELECT&nbsp;statement in a&nbsp;UNION</td> 
 </tr> 
 <tr> 
 <th style="vertical-align:bottom;width:197px;">DEPENDENT UNION</th> 
 <td style="vertical-align:bottom;width:176px;">dependent&nbsp;(true)</td> 
 <td style="vertical-align:bottom;width:370pt;">Second or later&nbsp;SELECT&nbsp;statement in a&nbsp;UNION, dependent on outer query</td> 
 </tr> 
 <tr> 
 <th style="vertical-align:bottom;width:197px;">UNION RESULT</th> 
 <td style="vertical-align:bottom;width:176px;">union_result</td> 
 <td style="vertical-align:bottom;width:370pt;">Result of a&nbsp;UNION.</td> 
 </tr> 
 <tr> 
 <th style="vertical-align:bottom;width:197px;"><a href="https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-subquery" rel="nofollow">SUBQUERY</a></th> 
 <td style="vertical-align:bottom;width:176px;">None</td> 
 <td style="vertical-align:bottom;width:370pt;">First&nbsp;SELECT&nbsp;in subquery</td> 
 </tr> 
 <tr> 
 <th style="vertical-align:bottom;width:197px;">DEPENDENT SUBQUERY</th> 
 <td style="vertical-align:bottom;width:176px;">dependent&nbsp;(true)</td> 
 <td style="vertical-align:bottom;width:370pt;">First&nbsp;SELECT&nbsp;in subquery, dependent on outer query</td> 
 </tr> 
 <tr> 
 <th style="vertical-align:bottom;width:197px;">DERIVED</th> 
 <td style="vertical-align:bottom;width:176px;">None</td> 
 <td style="vertical-align:bottom;width:370pt;">Derived table</td> 
 </tr> 
 <tr> 
 <th style="vertical-align:bottom;width:197px;">DEPENDENT DERIVED</th> 
 <td style="vertical-align:bottom;width:176px;">dependent&nbsp;(true)</td> 
 <td style="vertical-align:bottom;width:370pt;">Derived table dependent on another table</td> 
 </tr> 
 <tr> 
 <th style="vertical-align:bottom;width:197px;">MATERIALIZED</th> 
 <td style="vertical-align:bottom;width:176px;">materialized_from_subquery</td> 
 <td style="vertical-align:bottom;width:370pt;">Materialized subquery</td> 
 </tr> 
 <tr> 
 <th style="vertical-align:bottom;width:197px;">UNCACHEABLE SUBQUERY</th> 
 <td style="vertical-align:bottom;width:176px;">cacheable&nbsp;(false)</td> 
 <td style="vertical-align:bottom;width:370pt;">A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query</td> 
 </tr> 
 <tr> 
 <th style="vertical-align:bottom;width:197px;">UNCACHEABLE UNION</th> 
 <td style="vertical-align:bottom;width:176px;">cacheable&nbsp;(false)</td> 
 <td style="vertical-align:bottom;width:370pt;">The second or later select in a&nbsp;UNION&nbsp;that belongs to an uncacheable subquery (see&nbsp;UNCACHEABLE SUBQUERY)</td> 
 </tr> 
 </tbody> 
</table>

*  ## table ##

表示SQL对应步骤访问的是哪个表或结果集。

*  ## type ##

表示访问类型，访问类型的效率从高到低如下：
 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range 
 > index > ALL 
 ALL表示全表扫描，一般情况下需要进行优化；
 index大多是使用覆盖索引或者进行了索引排序；
 range对索引进行了范围查询，且返回结果集中只有索引列，常见的范围操作：=, <>, >, >=, <, <=, BETWEEN, IN()；
 ref_or_null对同一个字段进行了IS NULL和=判断，或对同一个字段进行了IS NULL和in判断；
 ref对非唯一索引进行了=判断（in也可以，但括号中只能由一个值。），包含关联后字段的等值匹配；
 eq_ref对唯一索引进行了=判断（in也可以，但括号中只能由一个值。），包含关联后字段的等值匹配；
 const使用了唯一索引为条件，且记录只有一行；
 system该表只有一行（=系统表）。这是const联接类型的特例。

*  ## possible\_keys ##

可能用到的索引。

*  ## key ##

实际使用到的索引。

*  ## key\_len ##

表示索引中使用的字节数，可以通过key_len计算查询中使用的索引长度，在不损失精度的情况下长度越短越好。

*  ## ref ##

显示索引的哪一列被使用了，如果可能的话，是一个常数。

*  ## rows ##

估算SQL返回的总行数。

*  ## filtered ##

按表条件过滤的行百分比。

*  ## Extra ##

附加信息。
        using filesort:说明mysql无法利用索引进行排序，只能利用排序算法进行排序；
        using index:这个表示当前的查询时覆盖索引的，直接从索引中读取数据，而不用访问数据表；
        using where:使用where进行条件过滤；
        impossible where：where语句的结果总是false。

--------------------

# 备注 #

[《MySQL8.0官方文档》][MySQL8.0]

[MySQL8.0]: https://dev.mysql.com/doc/refman/8.0/en/explain-output.html