MySQL语句优化利器EXPLAIN

待我称王封你为后i 2024-04-17 05:42 86阅读 0赞

##### EXPLAIN是什么 #####

使用`EXPLAIN`关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈。`EXPLAIN`可以帮助选择更好的索引和写出更优化的查询语句。在MySQL5.6以及以后的版本中，除过select，其他比如insert，update和delete均可以使用`EXPLAIN`查看执行计划。

##### 通过EXPLAIN，我们可以分析出以下结果： #####

*  表的读取顺序
 *  数据读取操作的操作类型
 *  哪些索引可以使用
 *  哪些索引被实际使用
 *  表之间的引用
 *  每张表有多少行被优化器查询

##### EXPLAIN语法 #####

> EXPLAIN SELECT \* FROM table

##### 执行计划各字段含义 #####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdjaGVuZ21pbmcx_size_16_color_FFFFFF_t_70]

*  id  
    SELECT识别符。这是SELECT的查询序列号。id相同，执行顺序从上往下，  
    id不同，id值越大，优先级越高，越先执行。
 *  select\_type  
    查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询。
    
     *  simple ——简单的select查询，查询中不包含子查询或者UNION
     *  primary ——查询中若包含任何复杂的子部分，最外层查询被标记
     *  subquery——在select或where列表中包含了子查询
     *  derived——在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中
     *  union——如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived
     *  union result:UNION 的结果
 *  table  
    查询所涉及的表。
 *  partitions  
    版本5.7以前，该项是explain partitions显示的选项，5.7以后成为了默认选项。该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。
 *  type
    
     *  `system`：表中仅有一行（=系统表）这是const联结类型的一个特例。
     *  `const`：表示通过索引一次就找到，const用于比较primary key或者unique索引。因为只匹配一行数据，所以如果将主键置于where列表中，mysql能将该查询转换为一个常量
     *  `eq_ref`：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于唯一索引或者主键扫描
     *  `ref`：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，可能会找多个符合条件的行，属于查找和扫描的混合体
     *  `range`：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束另一个点，不用全表扫描
     *  `index`：index 与all区别为index类型只遍历索引树。通常比all快，因为索引文件比数据文件小很多。
     *  `all`：遍历全表以找到匹配的行
     *  从好到坏的顺序一次是：`system > const > eq_ref > ref > range > index > all`
 *  possible\_keys  
    查询可能使用到的索引都会在这里列出来
 *  key  
    查询真正使用到的索引，select\_type为index\_merge时，这里可能出现两个以上的索引，其他的select\_type这里只会出现一个。
 *  key\_len  
    表示索引中使用的字节数，该列计算查询中使用的索引的长度在不损失精度的情况下，长度越短越好。如果键是NULL,则长度为NULL。该字段显示为索引字段的最大可能长度，并非实际使用长度。
 *  ref  
    显示索引的哪一列被使用了，如果有可能是一个常数，哪些列或常量被用于查询索引列上的值
 *  rows  
    根据表统计信息以及索引选用情况，大致估算出找到所需的记录所需要读取的行数
 *  filtered  
    查询条件过滤的行数的百分比
 *  Extra  
    EXplain 中的很多额外的信息会在 Extra 字段显示, 常见的有以下几种内容
    
     *  `distinct`：在select部分使用了distinct关键字
     *  `Using filesort`：当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.
     *  `Using index`：“覆盖索引扫描”, 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错
     *  `Using temporary`：查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdjaGVuZ21pbmcx_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/9666f96d13ed4ca9a15d3b123b988601.png