java八股文面试[数据库]——explain

叁歲伎倆 2024-03-08 08:17 115阅读 0赞

使用 `EXPLAIN` 关键字可以模拟优化器来执行SQL查询语句，从而知道MySQL是如何处理我们的SQL语句的。分析出查询语句或是表结构的性能瓶颈。

**MySQL查询过程**

![image.png][]

**通过explain我们可以获得以下信息：**

*  **表的读取顺序**
 *  **数据读取操作的操作类型**
 *  **哪些索引可以被使用**
 *  **哪些索引真正被使用**
 *  **表的直接引用**
 *  **每张表的有多少行被优化器查询了**

Explain使用方式: **explain+sql语句**, 通过执行explain可以获得sql语句执行的相关信息

explain select * from users;

###### type字段中有哪些常见的值？ ######

**type字段在 MySQL 官网文档描述如下：**

> **The join type. For descriptions of the different types.**

**type字段显示的是连接类型 ( join type表示的是用什么样的方式来获取数据)，它描述了找到所需数据所使用的扫描方式, 是较为重要的一个指标。**

**下面给出各种连接类型,按照从最佳类型到最坏类型进行排序:**

-- 完整的连接类型比较多
    system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
    
    -- 简化之后,我们可以只关注一下几种
    system > const > eq_ref > ref > range > index > ALL

> **一般来说,需要保证查询至少达到 range级别,最好能到ref,否则就要就行SQL的优化调整**

下面介绍type字段不同值表示的含义:

<table> 
 <thead> 
  <tr> 
   <th><strong>type类型</strong></th> 
   <th><strong>解释</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>system</td> 
   <td>不进行磁盘IO,查询系统表,仅仅<strong>返回一条数据</strong></td> 
  </tr> 
  <tr> 
   <td>const</td> 
   <td>查找主键索引,最多返回1条或0条数据. 属于<strong>精确查找</strong></td> 
  </tr> 
  <tr> 
   <td>eq_ref</td> 
   <td>查找<strong>唯一性索引</strong>,返回数据最多一条, 属于精确查找</td> 
  </tr> 
  <tr> 
   <td>ref</td> 
   <td>查找<strong>非唯一性索引</strong>,返回匹配某一条件的多条数据,属于精确查找,数据返回可能是多条.</td> 
  </tr> 
  <tr> 
   <td>range</td> 
   <td>查找某个索引的部分索引,只检索给定范围的行,属于范围查找. 比如: &gt; 、 &lt; 、in 、between</td> 
  </tr> 
  <tr> 
   <td>index</td> 
   <td>查找所有索引树,比ALL快一些,因为索引文件要比数据文件小.</td> 
  </tr> 
  <tr> 
   <td>ALL</td> 
   <td>不使用任何索引,直接进行全表扫描</td> 
  </tr> 
 </tbody> 
</table>

###### Extra有哪些主要指标，各自的含义是什么？ ######

Extra 是 EXPLAIN 输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息

<table> 
 <thead> 
  <tr> 
   <th><strong>extra类型</strong></th> 
   <th><strong>解释</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Using filesort</td> 
   <td>MySQL中<strong>无法利用索引</strong>完成的排序操作称为 “文件排序”</td> 
  </tr> 
  <tr> 
   <td>Using index</td> 
   <td>表示<strong>直接访问索引</strong>就能够获取到所需要的数据（覆盖索引），不需要通过索引回表</td> 
  </tr> 
  <tr> 
   <td>Using index condition</td> 
   <td>搜索条件中虽然出现了索引列，但是<strong>有部分条件无法使用索引</strong>， 会根据能用索引的条件先搜索一遍再匹配无法使用索引的条件。</td> 
  </tr> 
  <tr> 
   <td>Using join buffer</td> 
   <td>使用了<strong>连接缓存</strong>, 会显示join连接查询时,MySQL选择的查询算法</td> 
  </tr> 
  <tr> 
   <td>Using temporary</td> 
   <td>表示MySQL需要使用<strong>临时表来存储结果集</strong>，常见于<strong>排序和分组查询</strong></td> 
  </tr> 
  <tr> 
   <td>Using where</td> 
   <td>意味着<strong>全表扫描</strong>或者在查找使用索引的情况下，但是还有<strong>查询条件不在索引字段当中</strong></td> 
  </tr> 
 </tbody> 
</table>

##### 实际使用示例： #####

先解析一条sql语句，看出现什么内容

`EXPLAIN SELECT s.uid,s.username,s.name,f.email,f.mobile,f.phone,f.postalcode,f.address FROM uchome_space AS s,uchome_spacefield AS f WHERE 1 AND s.groupid=0 AND s.uid=f.uid`  
![SouthEast][]

###### **1. id** ######

SELECT识别符。这是SELECT查询序列号。这个不重要,查询序号即为sql语句**执行的顺序**，看下面这条sql

`EXPLAIN SELECT * FROM (`SELECT\* FROM uchome\_space LIMIT 10) AS s

它的执行结果为

![SouthEast 1][]

可以看到这时的**id变化了**

如果是子查询，id序号会自增，id值**越大优先级就越高**，越先被执行

id 相同与不同，同时存在

###### **2.select\_type** ######

select类型，**数据读取操作的操作类型，**它有以下几种值

###### 2.1 **simple**  ######

###### 它表示**简单的select**,没有**union**和**子查询** ######

###### 2.2 **primary**  ######

###### 最外面的select,在有子查询的语句中，最外面的select查询就是primary,上图中就是这样 ######

###### 2.3 **union**  ######

###### union语句的第二个或者说是后面那一个.现执行一条语句， ######

explain select \* from uchome\_space limit 10 **union** select \* from uchome\_space limit 10,10

会有如下结果

![SouthEast 2][]

第二条语句使用了union

###### 2.4 **dependent union**  ######

###### UNION中的第二个或后面的SELECT语句，取决于外面的查询 ######

###### 2.5 **union result**  ######

从UNION表获取结果的select，UNION的结果,如上面所示

######  ######

###### 2.6 DERIVED ######

在FROM列表中包含的子查询会被标记为DERIVED(衍生表)，MYSQL会递归执行这些子查询，把结果集放到零时表中。

######  ######

还有几个参数，这里就不说了，不重要

###### **3 table** ######

**该行数据是关于哪张表，**输出的行所用的表，这个参数显而易见，容易理解

###### **4 type** ######

**连接类型**。有多个参数，先从最佳类型到最差类型介绍 **重要且困难**

**访问类型 由好到差system > const > eq\_ref > ref > range > index > ALL**

###### 4.1 **system** ######

表**仅有一行**，这是const类型的特列，平时不会出现，这个也可以忽略不计

###### 4.2 **const** ######

表**最多有一个匹配行**，const用于比较**primary key** 或者**unique**索引。因为只匹配一行数据，所以很快

记住**一定是用到**primary key 或者unique，并且只检索出两条数据的 情况下才会是const,看下面这条语句

explain SELECT \* FROM \`asj\_admin\_log\` limit 1,结果是

![SouthEast 3][]

虽然只搜索一条数据，但是因为**没有用到指定的索引**，所以不会使用const.继续看下面这个

explain SELECT \* FROM \`asj\_admin\_log\` where log\_id = 111

![SouthEast 4][]

log\_id是主键，所以使用了const。所以说可以理解为const是最优化的

###### 4.3 **eq\_ref** ######

对于eq\_ref的解释，mysql手册是这样说的:"对于每个来自于**前面的表的行组合**，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分**被联接**使用并且索引是UNIQUE或PRIMARY KEY"。eq\_ref可以用于使用=比较带索引的列。看下面的语句

explain select \* from uchome\_spacefield,uchome\_space where uchome\_spacefield.uid = uchome\_space.uid

得到的结果是下图所示。很明显，mysql使用eq\_ref**联接**来处理uchome\_space表。

![SouthEast 5][]

目前的疑问：

4.3.1 为什么是只有uchome\_space一个表用到了eq\_ref,并且sql语句如果变成

explain select \* from uchome\_space,uchome\_spacefield where uchome\_space.uid = uchome\_spacefield.uid

结果**还是一样**，需要说明的是uid在这两个表中都是primary

###### 4.4 ref ######

对于每个来自于**前面的表的行组合**，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的**最左边的前缀**，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。

看下面这条语句 explain select \* from uchome\_space where uchome\_space.friendnum = 0，得到结果如下，这条语句能搜出**1w**条数据

![SouthEast 6][]

###### 4.5 ref\_or\_null ######

该联接类型如同ref，但是添加了MySQL可以专门搜索**包含NULL值**的行。在解决子查询中经常使用该联接类型的优化。

上面这五种情况都是很理想的索引使用情况

###### 4.6 index\_merge ######

该联接类型表示使用了**索引合并**优化方法。在这种情况下，key列包含了使用的索引的清单，key\_len包含了使用的索引的最长的关键元素。

###### 4.7 unique\_subquery ######

###### 4.8 index\_subquery ######

###### 4.9 range ######

给定范围内的检索，使用一个索引来检查行。看下面两条语句

explain select \* from uchome\_space where uid in (1,2)

explain select \* from uchome\_space where groupid in (1,2)

uid有索引，groupid没有索引，结果是第一条语句的联接类型是range,第二个是ALL.以为是一定范围所以说像 between也可以这种联接,很明显

explain select \* from uchome\_space where friendnum = 17

这样的语句是不会使用range的，它会使用更好的联接类型就是上面介绍的ref

只检索给定范围的行，使用**一个索引**来选着行。key列显示使用了哪个索引。一般在你的WHERE 语句中出现between 、< 、> 、in 等查询，这种给定范围扫描比全表扫描要好。因为他只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

###### 4.10 index ######

该联接类型与**ALL相同**，除了只有**索引树**被扫描。这通常**比ALL快**，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）

当查询只使用作为单索引一部分的列时，MySQL可以使用该联接类型。

FUll Index Scan 扫描遍历索引树(扫描全表的索引，从索引中获取数据)。

###### 4.11 ALL ######

对于每个来自于先前的表的行组合，进行**完整的表扫描**。如果表是第一个没标记 const的表，这通常不好，并且通常在它情况下 ***很***差。通常可以增加更多的索引而不要使用 ALL，使得行能基于前面的表中的常数值或列值被检索出。

全表扫描 从磁盘中获取数据 百万级别的数据ALL类型的数据尽量优化

###### **5 possible\_keys** ######

提示使用哪个索引会在该表中找到行，不太重要

显示可能应用在这张表的索引，一个或者多个。查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用。

###### **6 keys** ######

MYSQL使用的索引，简单且重要

**实际使用到的索引**。如果为NULL，则没有使用索引。查询中如果使用了覆盖索引，则该索引仅出现在key列表中。覆盖索引：select 后的 字段与我们建立索引的字段个数一致。

###### **7 key\_len** ######

MYSQL使用的索引长度

表示索引中使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。key\_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即key\_len是根据表定义计算而得，不是通过表内检索出来的。

###### **8 ref** ######

ref列显示使用哪个列或常数与key一起从表中选择行。

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

######  ######

###### **9 rows** ######

显示MYSQL执行查询的行数，简单且重要，数值越大越不好，说明没有用好索引

(每张表有多少行被优化器查询):根据表统计信息及索引选用的情况，大致估算找到所需记录需要读取的行数。

###### **10 Extra** ######

该列包含MySQL解决查询的详细信息。

###### 10.1 Distinct ######

MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。一直没见过这个值

###### 10.2 Not exists ######

###### 10.3 range checked for each record ######

没有找到合适的索引

###### 10.4 using filesort ######

MYSQL手册是这么解释的“MySQL需要**额外的一次传递**，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。”目前不太明白

我的理解：如果存在排序并且取出的列包括text类型会使用到using filesort，这会非常慢

###### 10.5 using index ######

只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的信息。这个比较容易理解，就是说明是否使用了索引

explain select \* from ucspace\_uchome where uid = 1的extra为using index（uid建有索引）

explain select count(\*) from uchome\_space where groupid=1 的extra为using where(groupid未建立索引)

###### 10.6 using temporary ######

为了解决查询，MySQL需要创建一个**临时表来容纳结果**。典型情况如查询包含可以**按不同情况列出列**的GROUP BY和ORDER BY子句时。

出现using temporary就说明语句需**要优化**了，举个例子来说

EXPLAIN SELECT ads.id FROM **ads**, city WHERE city.city\_id = 8005 AND ads.status = 'online' AND city.ads\_id=ads.id **ORDER BY ads.id desc**

**id select\_type table type possible\_keys key key\_len ref rows filtered Extra  
\------ ----------- ------ ------ -------------- ------- ------- -------------------- ------ -------- -------------------------------  
1 SIMPLE city ref ads\_id,city\_id city\_id 4 const 2838 100.00 Using temporary; Using filesort  
1 SIMPLE ads eq\_ref PRIMARY PRIMARY 4 city.ads\_id 1 100.00 Using where**

**这条语句会使用using temporary,而下面这条语句则不会**

EXPLAIN SELECT ads.id FROM **ads**, city WHERE city.city\_id = 8005 AND ads.status = 'online' AND city.ads\_id=ads.id **ORDER BYcity.ads\_id desc**

**id select\_type table type possible\_keys key key\_len ref rows filtered Extra  
\------ ----------- ------ ------ -------------- ------- ------- -------------------- ------ -------- ---------------------------  
1 SIMPLE city ref ads\_id,city\_id city\_id 4 const 2838 100.00 Using where; Using filesort  
1 SIMPLE ads eq\_ref PRIMARY PRIMARY 4 city.ads\_id 1 100.00 Using where**

这是为什么呢？他俩之间**只是一个order by不同**，MySQL 表关联的算法是 **Nest Loop Join**，是通过驱动表的**结果集**作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。EXPLAIN 结果中，第一行出现的表就是驱动表（Important!）以上两个查询语句，驱动表都是 city，如上面的执行计划所示！

**对驱动表可以直接排序**，对非驱动表（的字段排序）需要对循环查询的**合并结果**（临时表）进行排序 （Important!）

因此，order by ads.id desc 时，就要先 using temporary 了

驱动表的定义  
1）指定了联接条件时，满足查询条件的**记录行数少的表**为\[驱动表\]；  
2）未指定联接条件时，**行数少**的表为\[驱动表\]（Important!）。

**永远用小结果集驱动大结果集**

**当不确定是用哪种类型的join时，让mysql优化器自动去判断，我们只需写select \* from t1,t2 where t1.field = t2.field**

通俗解释：select \* from table order by field

其中filed建普通索引，这种情况会使用到using temporary,因为虽然这时候使用到了索引，但因为**扫描的是全表**，mysql优化器会判断：反正是搜索全表而且要排序，因为这时候要回行，我还不如不**沿着索引找数据**，直接全部检索出所有数据来在排序。

如果语句这么写 select \* from table where field > 1 order by field.mysql优化器就会这么判断：这时候不是搜全表，我需要先根据where条件,沿着**索引树搜**出想要的相应索引数据，在回行（一边找索引一边回行）。这时候就**不需要临时表了**

###### 10.7 using where ######

WHERE子句用于**限制哪一个行匹配下一个表**或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。（这个说明不是很理解，因为很多很多语句都会有where条件，而type为all或index只能说明检索的数据多，并不能说明错误，useing where不是很重要，但是很常见）

如果想要使查询尽可能快，应找出Using filesort 和Using temporary的Extra值。

###### 10.8 Using sort\_union(...) , Using union(...) , Using intersect(...) ######

这些函数说明如何为index\_merge联接类型合并索引扫描

###### 10.9 Using index for group-by ######

类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。

知识来源：马士兵教育

[MYSQL explain详解\_Venlenter的博客-CSDN博客][MYSQL explain_Venlenter_-CSDN]

[Mysql中explain作用详解\_Mysql\_脚本之家][Mysql_explain_Mysql]

[image.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/6e8bb3521bcd4ea4865f5cec78c54ff1.png
[SouthEast]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/eb2ba263170547e88b99e411029ebde4.png
[SouthEast 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/eddb0a739181404a82786d089db986b3.png
[SouthEast 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/3e00618d8a324e5fa37cea06e4b78911.png
[SouthEast 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/c1b6e86eec90417d896e6536f096f63c.png
[SouthEast 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/9d7da48d34bc4457a5444cc6d7066b02.png
[SouthEast 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/847b6312ced841acb4fcb8b3254aed1e.png
[SouthEast 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/48ab734ca3254cdebf6d3b89f4c5df8b.png
[MYSQL explain_Venlenter_-CSDN]: https://blog.csdn.net/asd051377305/article/details/113979657
[Mysql_explain_Mysql]: https://www.jb51.net/article/126107.htm