SQL优化（一）：分析利器Explain查看执行计划

墨蓝 2023-01-16 02:55 118阅读 0赞

SQL优化是一个老生常谈的问题，面试爱问，实际工作中也经常会遇到。我们先不谈SQL优化的技巧和实际应用，我们先思考一个问题，就是我们在面对一个SQL，效率不高进场超时，怎么去判断是哪个环节导致，比如是不是没有走索引，或者说因为全表扫描，或者大表关联小表导致导致本来本可以少扫描很多行结果多扫描了不少？

该怎么看呢？这就提到了MySQL的分析语句Explain，以下是我在工作中遇到的一个总是超时的SQL，我对这个SQL的Explain，我们先掌握一下Explain的返回的信息的学习，否则你看都看不懂，怎么通过explain返回的信息去定位问题呢.

（后面我还会用这个SQL来做分析，稍微优化一下，让他从两三秒钟返回结果变成零点零几秒）  
一 例子

EXPLAIN SELECT
    	aco.id,
    	aco.merchant_id,
    	aco.deal_user_id,
    	aco.collected_order_no,
    	aco.collected_order_status,
    	aco.approval_status,
    	aco.use_employee_id,
    	aco.use_company_id,
    	aco.use_department_id,
    	aco.use_type,
    	aco.place_id,
    	aco.use_time,
    	aco.use_remark,
    	aco.data_status,
    	aco.version,
    	aco.remark,
    	aco.create_time,
    	aco.create_user,
    	aco.update_time,
    	aco.update_user,
    	acod.id AS acod_id,
    	acod.merchant_id AS acod_merchant_id,
    	acod.collected_order_id AS acod_collected_order_id,
    	acod.collected_order_no AS acod_collected_order_no,
    	acod.asset_id AS acod_asset_id,
    	acod.asset_code AS acod_asset_code,
    	acod.asset_name AS acod_asset_name,
    	acod.brand AS acod_brand,
    	acod.asset_category_id AS acod_asset_category_id,
    	acod.assets_category_name AS acod_assets_category_name,
    	acod.manager_user_id AS acod_manager_user_id,
    	acod.manager_user_name AS acod_manager_user_name,
    	acod.place_id AS acod_place_id,
    	acod.place_name AS acod_place_name,
    	acod.belong_company_id AS acod_belong_company_id,
    	acod.belong_company_name AS acod_belong_company_name,
    	acod.company_id AS acod_company_id,
    	acod.company_name AS acod_company_name,
    	acod.employee_id AS acod_employee_id,
    	acod.employee_name AS acod_employee_name,
    	acod.model AS acod_model,
    	acod.asset_sequence_no AS acod_asset_sequence_no,
    	acod.department_id AS acod_department_id,
    	acod.amount AS acod_amount,
    	acod.department_name AS acod_department_name,
    	acod.data_status AS acod_data_status,
    	acod.version AS acod_version,
    	acod.remark AS acod_remark,
    	acod.create_time AS acod_create_time,
    	acod.create_user AS acod_create_user,
    	acod.update_time AS acod_update_time,
    	acod.update_user AS acod_update_user,
    	acod.add_asset_type AS acod_add_asset_type,
    	acod.supplier_name AS acod_supplier_name,
    	acod.out_order_no AS acod_out_order_no,
    	ae.employee_name AS use_employee_name,
    	ae.employee_code AS use_employee_code,
    	ac.company_name AS use_company_name,
    	ao.organization_name AS use_department_name,
    	ap.place_name AS use_place_name,
    	ae.email
    FROM
    	(
    		SELECT DISTINCT
    			aco.id,
    			aco.merchant_id,
    			aco.deal_user_id,
    			aco.collected_order_no,
    			aco.collected_order_status,
    			aco.approval_status,
    			aco.use_employee_id,
    			aco.use_company_id,
    			aco.use_department_id,
    			aco.use_type,
    			aco.place_id,
    			aco.use_time,
    			aco.use_remark,
    			aco.data_status,
    			aco.version,
    			aco.remark,
    			aco.create_time,
    			aco.create_user,
    			aco.update_time,
    			aco.update_user
    		FROM
    			ams_collected_order aco
    		INNER JOIN ams_collected_order_detail acod ON aco.id = acod.collected_order_id
    		AND aco.collected_order_no = acod.collected_order_no
    		AND acod.data_status = 1
    		LEFT JOIN ams_employee ae ON aco.use_employee_id = ae.id
    		AND ae.data_status = 1
    		WHERE
    			aco.data_status = 1
    		AND aco.merchant_id = 1002102
    		ORDER BY
    			aco.use_time DESC,
    			collected_order_no DESC
    		LIMIT 0,
    		10000
    	) aco
    INNER JOIN ams_collected_order_detail acod ON aco.id = acod.collected_order_id
    AND aco.collected_order_no = acod.collected_order_no
    AND acod.data_status = 1
    LEFT JOIN ams_employee ae ON aco.use_employee_id = ae.id
    AND ae.data_status = 1
    LEFT JOIN ams_place ap ON aco.place_id = ap.id
    AND ap.data_status = 1
    LEFT JOIN ams_company ac ON aco.use_company_id = ac.id
    AND ac.data_status = 1
    LEFT JOIN ams_organization ao ON aco.use_department_id = ao.id
    AND ao.data_status = 1
    ORDER BY
    	aco.use_time DESC,
    	collected_order_no DESC;

结果返回（这是测试环境的，线上数据几十万条起步，导致线上的返回时间比测试环境更严重）：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0ZlaUNoYW5nV3VSYW8_size_16_color_FFFFFF_t_70]  
返回结果分析：

## 1 id ##

按我们之前的理解，id应该是一个标识，一行一个。但是从上图明显看出，并不是很多行用的是同一个id。

那这个id是干嘛的？实际你可以理解为分组id，同一个id就是同一个组的。

id相同从上往下执行。  
id不同，则是id数越大，反而越优先执行。

这里因为返回的结果，也是按照id大小顺序排下去的，导致我之前错误的认为越小的优先级高，导致我有时候看分析计划想不明白一些东西  
所以这里我重复说三遍：  
**id 相同上到下，id不同大到小  
id 相同上到下，id不同大到小  
id 相同上到下，id不同大到小**  
不要按照被返回的上下顺序误导

## 2 select type ##

查询子句的类型，主要用于区别子句是普通查询、联合查询、子查询等复杂查询。

实际上一个SQL去执行实际会根据子句一个一个去执行，不是一整个去执行。

**(1) SIMPLE(简单SELECT，不使用UNION或子查询等)  
(2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)  
(3) UNION(UNION中的第二个或后面的SELECT语句)  
(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)  
(5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)  
(6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)  
(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)  
(8) DERIVED(派生表的SELECT, FROM子句的子查询)  
(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)**

这里我们常见的就是SIMPLIE，平时写的很多都是simple的，比如select \* from XXXX where id = xxx这些  
Primary表的是外层的，DERIVED是派生表举个例子，他俩经常是成对出现的

SELECT 
        column_list
    FROM
    *    (SELECT 
    *        column_list
    *    FROM
    *        table_1) derived_table_name;
    WHERE derived_table_name.column > 1...

星号表示的是派生表，你可以把它看作一个临时表，经常写复杂的SQL会用到，从这个临时表结果集里取数据。

## 3 table ##

显示这一步所访问数据库中表名称（表名，如果有别名就显示表的别名，派生表没有名字会生成derived+数字来代替）

## 4 partition ##

查询表分区的，这个测试环境没做什么表分区所以上图没展示

## 5 type ##

访问类型，这个其实很重要，根据访问的类型可以判断SQL查询的扫描方式，不同的访问类型访问效率也不同。

ALL, index, range, ref, eq\_ref, const, system, NULL

![在这里插入图片描述][20210423172923466.png]  
从左到右，性能从最差到最好  
最差的是ALL，全表查询，也是没谁了整个表都过一遍，表越大访问量越大。实际上通过这个你就可以看出我给的那个SQL耗时长的问题，走了两次全表查询，能不慢吗。

一般来说  
我们的SQL要做到最起码是range级别的，最好各个子查询是ref级别。

以下是各个访问类型的解释（为什么到了ref就很好，而不是更高一级别，看了这个就明白了）

**system：  
表只有一行记录（等于系统表），这是const类型的特别，平时不会出现，这个可以忽略不计。  
const:  
表示通过索引一次就找到了，const用于比较primary key或者unique索引，因为只匹配一行数据，所以很快  
如将主键置于where列表中，Mysql就能将该查询转换为一个常量  
eq\_ref：  
唯一性索引扫描，对于每个索引键，表只只有一条记录与之匹配。常见于主键或唯一索引扫描  
ref：  
非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回匹配某个单独值的行，然而，它可能会找到多个符合条件的行，索引他应该属于查找和扫描的混合体  
range：  
只检索给定范围的行，使用一个索引来选择行。  
key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询  
这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，面结束另一点，不用扫描全部索引。  
index:  
Full Index Scan，index与ALL区别为index类型只遍历索引树，这通常比ALL快，因为索引文件通常比数据文件小，  
也就是说虽然ALL和Index都是读全表，但Index是从索引中读取的，而ALL都是从磁盘中读的。  
ALL：  
Full Table Scan， MySQL将遍历全表以找到匹配的行。NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。**

6 possible-key  
显示可能应用在这张表中的索引，一个或多个。  
查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。possible-key的几个索引，可以理解为，用上了，但是没有完全用上

7 key  
key列显示MySQL实际决定使用的键（索引），这个索引一定是SQL用到的  
当然他也包含在possible\_keys中

8 key\_len  
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。

这个表示的是可能索引有多长，根据表定义得到的。不能代表实际表的索引长度，可能实际长度是1，但是定义了10这种情况。

对SQL查询的影响：不损失精度的钱体系，索引越短效率越高，所以不是所有的东西都是越长越好是吧。当然要注意精度，比如一个Long整型的为了变短边长Integer类型的，精度缺失，得不偿失。

9 ref  
对比我们上面的访问类型type里面的唯一型索引eq\_ref，ref索引对应多行结果，这个ref指的是用到的索引具体是那列。

没用到就为空，用到了就把索引对应的表名加字段表示出来。

10 rows  
估计的扫描行数，最好的结果就是只扫描一行，一查就查到。但实际情况不一定允许。

实际上预估的，不一定就是实际扫描的，但是也足以表示出效率瓶颈在哪，扫描行数特别多的要特别注意，是不是没走索引扫描了全表，还是选择的字段不行，比如说你查看性别男性的数据，整张表几乎都是男性（这客户群妥妥的直男群体，难受）。

11 filtered  
Filtered表示返回结果的行数占需读取行数的百分比 Filtered列的值越大越好 Filtered列的值依赖于统计信息  
举个例子，你读取的结果集100，但是结果只有10行，浪费了很多读取的数据，或者说你压根不用读取这么多，条件匹配的好，读取10行结果返回输出10行

12 Extra  
额外的信息说明，这个我搜了一下资料有的给了很多信息，我挑两个常用的吧

**Using temporary:使用了临时表中保存中间结果，Mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。**

**Using index： 表示相应的select操作中使用了覆盖索引（Covering Index）,避免访问了表的数据行，效率不错。 如果同时出现**

**using where，表明索引被用于来执行索引键值的查找。 如果没有同时出现wing where，表明索引用来读取数据而非执行查找动作。**

**Using where ： 表明使用了where过滤 Using join buffer： 使用了连接缓存**

**impossible where： where子句中的值总是false，不能用来获取任何元组**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0ZlaUNoYW5nV3VSYW8_size_16_color_FFFFFF_t_70]: /images/20221022/f4907dceabbf4cb3b17bf6d9f1acb3d5.png
[20210423172923466.png]: /images/20221022/c10c3f2db4414ca3a9956ef647084356.png