EXPLAIN 详解（SQL优化）

先了解 SQL优化

EXPLAIN 详解

explain是干嘛的？

使用explain可以模拟优化器执行SQL语句，分析查询语句的结构，是否使用索引等等

使用方法

在查询语句select关键字前面加上explain关键字，如下图的格式，然后就会返回分析的结果

explain select * from film where id = 2;

EXPLAIN
SELECT * FROM (
SELECT DISTINCT 
a.id, a.order_no orderNo,a.product_type,b.origin_visit_date,a.created_time orderTime,c.org_name orderOrgName,c.channel_abbr,
a.channel_id orderOrgId,a.order_channel,b.product_name,b.quantity,a.order_amount,a.payment_status,a.audit_status,
ps.start_time sessionStartTime,ps.end_time sessionEndTime,a.act_order_amount, 
(CASE DATE_FORMAT(b.origin_visit_date,'%Y-%m-%d') < DATE_FORMAT(NOW(),'%Y-%m-%d') WHEN 1 THEN 'overdue' ELSE 'not_overdue' END) overdue_status
FROM order_info a
LEFT JOIN order_visitor_product b ON a.id = b.order_id
LEFT JOIN org_info c ON a.channel_id = c.id
LEFT JOIN order_visitor d ON d.order_id = a.id
LEFT JOIN policy_session ps ON ps.id = a.policy_session_id
WHERE 1 = 1 
AND a.company_id = 1182834566247878656
AND DATE(b.origin_visit_date) >='1990-07-01' AND DATE(b.origin_visit_date)<='2022-08-31'
AND a.product_type='ticket'
AND a.order_type = 'team' 
AND ((a.product_type != 'hotel' AND a.parent_pck_id IS NULL) OR (a.product_type = 'pck' AND a.parent_pck_id IS NOT NULL))
AND a.parent_product_type IS NULL
ORDER BY a.created_time DESC
) alias_table

下面对explain执行计划返回信息进行列的分析

表结构

DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (
`id` INT(11) NOT NULL, 
`name` VARCHAR(45) DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) 
ENGINE=InnoDB DEFAULT CHARSET=UTF8; 
INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017‐12‐22 15:27:18'), (2,'b','20 17‐12‐22 15:27:18'), (3,'c','2017‐12‐22 15:27:18'); 

DROP TABLE IF EXISTS `film`; 
CREATE TABLE `film` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)) 
ENGINE=InnoDB DEFAULT CHARSET=UTF8;
INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2'); 

DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor` (
`id` int(11) NOT NULL,
`film_id` int(11) NOT NULL,
`actor_id` int(11) NOT NULL,
`remark` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_film_actor_id` (`film_id`,`actor_id`)) 
ENGINE=InnoDB DEFAULT CHARSET=UTF8;
INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);

id字段：

id有几个就代表这个sql语句被解析成多少个select. 其中id越大，代表执行的优先级别越高，id相等则从上到下依次执行，id为NULL的最后执行；

select_type字段列：

代表查询的语句的复杂类型：

1）.simple:简单查询

2）.primary:复合查询的最外层查询

3）.subquery:是select 后面的子查询

4）.derived是紧跟在from后面的子查询，MySQL会将结果存放在一个临时表中，也称为派生表

为什么这里查出来的是一个简单的语句呢？而不是想象中的derived类型呢，原因很简单，在mysql5.7以后，对派生表进行优化了，优化器引入derived_merge。当然我们也可以通过设置参数将派生表优化给关闭。

5）union:紧跟union后面的select

table字段列：表示访问的是哪一个表

type列：表示MYSQL决定如何查找表中的行。

他有七个级别，依次从最优到最差分别为：system>const>eq_red>red>range>index>ALL
一般来说，最好达到ref级别，实在达不到，range级别也不差。

1）NULL:当type为null时，表示查询语句在执行过程中被优化，不用访问表，直接从索引中得到值

2）const:表示对查询的部分进行优化，并将其转化为一个常量。用于primary key 或者unique key的所有列与常量比较时，所以表中只有一条记录，查询速度快。

3）eq_ref:primary key 或者unique key 索引的所有部分被连接使用，最多只返回一条符合条件的记录。

4）ref:相比rq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行

5）range:范围扫描通常出现在in(),between,>,<,>=等操作中。

6)index:扫描全索引，一般是扫描某个二级索引，她不会从索引根节点查找，而是直接对二级索引的叶子节点遍历和扫描。速度较慢。这种一般为使用了覆盖索引，二级索引一般比较小。（因为select * 这里面包含二级索引字段，所以就会走二级索引）

7）all:即全表扫描，扫描聚集索引下的所有叶子节点，因为select * 这里面的字段都没有建立除了聚集索引外的索引，所以就只会走聚集索引，如果不知道聚集索引的可以去看一下这篇博文（https://editor.csdn.net/md/?articleId=107474948），因为聚集索引叶子节点存了data数据多，所以效率就会低

possible_keys列

表示该查询语句可能使用哪个索引，如果为NULL,则表明没有相关索引以供查询，这时候可以在where语句后面字段加个索引，看是否提高了性能

key列

表示实际用了哪个索引，如果possible_keys不为空，key为null，说明数据少，不需要走索引，直接全表扫描。

key_len列

这表示用到的索引字段的字节数

ref列

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）

rows列

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数

filtered列

根据查询条件返回结果的行数占需读取行数的百分比 Filtered列的值越大越好 Filtered列的值依赖于统计信息

Extra列

这一列展示的是额外信息。常见的重要值如下

1）Using index：使用覆盖索引覆盖索引定义：如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键

2)Using where：where后面查询的字段没有建立索引，使用 where 语句来处理结果

3）Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；

4）Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化
5）Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。
6）Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段是

参考：https://blog.csdn.net/weixin_42328375/article/details/108181357

posted @ 2022-07-21 11:07 DHaiLin 阅读(1075) 评论(0) 编辑收藏举报

刷新页面返回顶部

DHaiLin