MySQL EXPLAIN 查询计划调优

置顶 已于 2023-11-28 11:16:15 修改
阅读量171 收藏
点赞数
分类专栏： MySQL 文章标签： mysql 数据库 sql
于 2021-10-23 22:50:49 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/beslet/article/details/120923812
版权
本文详细介绍了MySQL查询优化的重要概念，包括EXPLAIN的各个字段解析，如id、select_type、type、key等，并通过示例展示了不同查询类型和优化点。通过对查询计划的分析，帮助理解查询执行过程，从而进行更有效的数据库性能优化。
摘要由CSDN通过智能技术生成

MySQL EXPLAIN

在这里插入图片描述

环境准备：MySQL5.7
创建测试表 ：

CREATE TABLE `demo1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key1` varchar(255) NOT NULL,
  `key2` varchar(255) NOT NULL,
  `key3` varchar(255) DEFAULT NULL,
  `part_key_1` varchar(255) DEFAULT NULL,
  `part_key_2` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `demo2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key1` varchar(255) NOT NULL,
  `key2` varchar(255) NOT NULL,
  `key3` varchar(255) DEFAULT NULL,
  `part_key_1` varchar(255) DEFAULT NULL,
  `part_key_2` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

id

一般来说，每个Select关键字对应一个id，表示select查询的序列号。id越大越先执行；id相同从上往下执行。

  • 单表查询
explain SELECT * FROM  demo1 where id=1;

在这里插入图片描述

  • 连接查询
explain SELECT * FROM  demo1 INNER JOIN demo2 on demo1.id=demo2.id  ;

在这里插入图片描述
连接查询，id是同一个，前面的是驱动表，后面的是被驱动表。

  • 子查询
explain SELECT * FROM  demo1 where key1 > (select id from demo2 ) ;

在这里插入图片描述

子查询的id是外层查询是不一样的。子查询更大，表示是最先执行的。

  • union临时表
explain SELECT * FROM  demo1 where key1>1 UNION  SELECT * FROM  demo1 where key2>3;

在这里插入图片描述
union临时表 id为NULL。

select_type

每个Select对应的查询类型

  • SIMPLE ：不包含 UNION 或者子查询的简单查询。
  explain SELECT * FROM  demo1 where id=1;

在这里插入图片描述

  • PRIMARY ： 包含了 UNION、UNION ALL 或者子查询的最外层查询。
explain SELECT * FROM  demo1 where key1 > (select id from demo2 ) ;

在这里插入图片描述

  • UNION ：包含了 UNION 或者 UNION ALL 的非第一个查询。
explain SELECT * FROM  demo1 where key1>1 UNION  SELECT * FROM  demo1 where key2>3;

在这里插入图片描述

  • UNION RESULT ： 一般用临时表来完成 UNION 查询的去重，对临时表的查询就UNION RESULT。
explain SELECT * FROM  demo1 where key1>1 UNION  SELECT * FROM  demo1 where key2>3;

在这里插入图片描述

  • DEPENDENT UNION：包含了 UNION 或者 UNION ALL 的非第一个查询，并且依赖于外层的查询。
explain select * from demo1 t1 where t1.key1 in (SELECT key1 FROM  demo1 t1 where t1.key1>1 UNION SELECT key1 FROM demo2 t2 where t2.key2>3 and t2.key1=t1.key1);

在这里插入图片描述

  • SUBQUERY：包含在 select 中的子查询（不在 from 子句中）的不相关子查询的第一个select。
explain SELECT * FROM  demo1 where key1 > (select id from demo2 ) ;

在这里插入图片描述

  • DEPENDENT SUBQUERY：相关子查询的第一个select。
explain SELECT * FROM  demo1 t1 where t1.key1 > (select t2.id from demo2 t2 where t2.key2>t1.key2) ;

在这里插入图片描述

  • DERIVED：包含在FROM子句的子查询中的SELECT，MySQL会将结果放到Derived table（派生表）中，以物化派生表的方式进行的查询 。
explain SELECT * from (SELECT key1 ,count(1) from demo1) as t

在这里插入图片描述

  • MATERIALIZED：子查询物化之后，于外层查询进行连接查询的方式。
explain SELECT * from demo1 where key1 in(SELECT key1 from demo2)

在这里插入图片描述

  • UNCACHEABLE SUBQUERY：子查询，结果无法缓存，必须针对外部查询的每一行重新评估，不常用。

  • UNCACHEABLE UNION：UNION属于UNCACHEABLE SUBQUERY的第二个或后面的查询，不常用。

table

访问表的表名

partitions

当前查询匹配记录的分区，非分区表该值为NULL。

type

表示对单个表的访问方法

  • system：表中只有一条数据，且存储引擎的统计数据是精确的，innodb没有此类型。

  • const：通过主键或者唯一索引进行常数匹配的。

explain SELECT * FROM  demo1 where id=1;

在这里插入图片描述

  • eq_ref：执行连接查询时，被驱动表是通过主键或者不允许为NULL的唯一二级索引等值查询的方式访问，这几个条件缺一不可，这是最好的连接类型。
explain SELECT * FROM  demo1 INNER JOIN demo2 on demo1.id=demo2.id  ;

在这里插入图片描述

  • ref：非唯一二级索引进行常量等值匹配查询。
explain SELECT * FROM  demo1 where key2="22"  ;

在这里插入图片描述

  • ref_or_null：非唯一二级索引进行常量等值匹配查询，并且只可以为NULL时。
explain SELECT * FROM  demo1 where key2="22" or key2 is NULL ;

在这里插入图片描述

  • fulltext：使用全文索引

  • index_merge：表示使用了索引合并的优化方法

  • range：索引范围查询

explain SELECT * FROM  demo1 where id>1;

在这里插入图片描述

  • index：使用覆盖索引，只需扫描索引树。
explain SELECT part_key_1,part_key_2 FROM  demo1 where part_key_1>1;

在这里插入图片描述

  • unique_subquery：针对包含IN 子查询，通过主键或者不允许为NULL的唯一二级索引等值查询。

  • index_subquery：和unique_subquery类似，使用的是非唯一索引。

  • all：全表扫描。

possible_keys

可能用到的索引，如果是覆盖索引，该值为NULL

key

实际用到的索引

key_len

实际使用索引的长度，可以用来确定联合索引中实际使用了哪些列

ref

表示与索引列进行等值匹配的对象（常数、字段或者函数）

rows

完成查询预估要扫描的记录数

filtered

按表条件过滤的表行的估计百分比

Extra

其他额外的信息

  • Using where：需要在server层进行where条件过滤
explain SELECT * FROM  demo1 where key2 = 'a' and field1='d' ;

在这里插入图片描述

  • Using index condition：使用索引条件下推
explain SELECT * FROM  demo1 where part_key_1="a" and part_key_2>'b';

在这里插入图片描述

  • Using index：使用覆盖索引
explain SELECT part_key_1,part_key_2 FROM  demo1 where part_key_1>1;

在这里插入图片描述

  • Using filesort：在内存或者磁盘文件中进行排序
explain SELECT * FROM  demo1 where id>1 order by field1;

在这里插入图片描述

  • Using temporary：使用临时表
explain SELECT DISTINCT field1  FROM  demo1 where id>1 order by field1 ;

在这里插入图片描述

  • Using join buffer：使用连接缓冲区
explain SELECT * FROM  demo1 t1 INNER JOIN demo2 t2 on t1.field1=t2.field1  ;

在这里插入图片描述

需要优化的点

以下几种情况，可能就需要考虑进行优化：

  • select_type 为 DEPENDENT SUBQUERY
  • type 为 all，key 为 null
  • Extra 为 Using filesort 、Using temporary、Using join buffer
CodeMavs
关注
专栏目录
MySQL慢查询查找和调优测试
09-10
总之，MySQL慢查询查找和调优是一个综合的过程，需要结合日志分析、执行计划解读、索引优化、查询重构等多种技术手段。只有深入理解数据库工作原理，才能有效地定位和解决性能问题，提高系统的响应速度和整体性能。
mysql sql语句性能调优简单实例
09-09
4. **使用EXPLAIN分析查询计划**：`EXPLAIN`可以展示MySQL如何执行SQL语句，帮助理解查询的执行路径和优化策略。 5. **优化GROUP BY和ORDER BY**：确保在GROUP BY或ORDER BY中使用的列有索引，以提高排序效率。 6. ...
MySQL之查询优化（EXPLAIN）
qq_45061361的博客
10-31 211
MySQL之查询优化（EXPLAIN） 查询优化器查询瓶颈执行计划（EXPLAIN）作用 执行计划字段介绍结构解释1.`id `2. `select_type`3. table4. `type`5. possible-keys6. `key`7. key_len8. `ref`9. rows10. ExTra覆盖索引（C...
mysql优化（3）——explain详解索引优化实战
hello_wang_world的博客
12-07 281
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
MySQL优化从执行计划开始(explain超详细)，java基础教程第三版
m0_64205564的博客
11-15 578
EXPLAIN SELECT t.ID,t.USER_NAME,r.ROLE_NAME FROM USER t JOIN USER_ROLE tr ON t.ID = tr.USER_ID JOIN ROLE r ON tr.ROLE_ID = r.ID 执行如下语句，得如下结果： 如上图所示，id一样，从上到下依次执行，所对应表加载顺序为t->tr->r(这里的表是别名)； id不同的时候：当id不同的时，id越大的越先执行； EXPLAIN SELECT t.ID,t.MENU_NAM
mysql中explain关键字段解释以及索引优化
紫漪的博客
08-26 1477
一、explain关键字解释 1、id MySQL QueryOptimizer选定的执行计划中查询的序列号,表示查询中执行select子句或操作表的顺序。id值越大优先级越高,越先被执行。id相同,执行顺序由上至下。 2、select_type (1)SIMPLE 简单的select查询(不使用union及子查询)。 (2)PRIMARY 最外层的select查询。 如果两表...
mysql explain 性能分析_mysql性能分析之explain
weixin_29369363的博客
02-07 297
Explain命令在解决数据库性能上是第一推荐使用命令，大部分的性能问题可以通过此命令来简单的解决，Explain可以用来查看SQL语句的执行效 果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。Explain语法：explain select … from … [where …]例如：explain select * from news;输出：+--+-----------+----...
mysql调优explain_MySQL调优之explain执行计划详解
weixin_42524308的博客
01-19 202
介绍explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。使用方法，在select语句前加上explain就可以了(MySQL 5.6开始，不仅仅支持select )各列含义详解id标识select，id列数字越大越先执行，如果说数字一样大，那么就从上往下依次执行，id列为null的就表是这是一个结果集，不需要使用它来进行查询。s...
【MySQL】MySQL Explain性能调优详解
DreamSun的博客
07-05 1068
mysql查看性能工具explain中type有很多种，主要的有： 链接类型 说明 system 表只有一行，MyISAM引擎。 const 常量连接，表最多只有一行匹配，通用用于主键或者唯一索引比较时 eq_ref 每次与之前的表合并行都只在该表读取一行，这是除了system，const之外最好的一种，特点是使用=，而且索引的所有部分都参与join且索引是主键或非空唯一键的索引 ref 如果每次只匹配少数行，那就是比较好的一种，使用=或<=>，可以是左覆盖索引或非主键或非唯一键 fulltex
MySQL数据库调优之 explain的学习
qq_26641137的博客
02-20 864
定位了查询慢的SQL后，就可以使用explain或describe工具做针对性的分析查询语句。describe语句的使用方法与explain语句是一样的，并且分析结果也是一样的。Mysql中有专门负责优化SELECT语句的优化器模块，主要功能：通过计算饭呢西系统中收集到的统计信息，为客户端请求的Qurey 提供它认为最优的。
一篇文章彻底搞懂MySql之Explain调优工具
大尾巴狼学编程的博客
11-03 1092
介绍了Expplain使用、查询结果的每个列图文详解，对Expplain有个深刻的认识。
MySql中如何使用 explain 查询 SQL 的执行计划
09-09
总结来说，`EXPLAIN`是MySQL性能调优的重要工具，通过深入理解执行计划，我们可以更好地优化SQL查询，提高数据库的运行效率。熟练掌握`EXPLAIN`的使用，能够帮助我们避免不必要的全表扫描，利用索引提高查询速度，...
MySql数据库使用Sql调优
08-22
使用EXPLAIN语句可以帮助分析查询计划，理解MySQL如何使用索引。此外，合理设计数据库结构，避免JOIN操作过多，以及适当使用子查询和临时表，都可以提升SQL执行效率。 对于性能监控，还可以借助其他工具，如MySQL ...
【Linux】MySQLMGR主从复制
Stringzhua的博客
10-30 842
MySQL是⽬前最流⾏的开源关系型数据库，国内⾦融⾏业也开始全⾯使⽤，其中MySQL5.7.17提出的 MGR(MySQL Group Replication)既可以很好的保证数据⼀致性⼜可以⾃动切换，具备故障检测功能、 ⽀持多节点写⼊，MGR是⼀项被普遍看好的技术。MGR(MySQL Group Replication)是MVSQL⾃带的⼀个插件，可以灵活部署。
easy-es使用以及Es和MySQL同步
weixin_51918722的博客
10-31 444
它主要就是简化了ES相关的API, 使用起来像MP一样舒服。
【重学 MySQL】七十九、深入探索用户变量
CSDN全栈领域优秀博主，阿里云博客专家，工信部权威认证物联网技术开发工程师，曾30分钟高分通过C语言二级，两天手撸代码完成EasyX飞机大战，靠接软件私活一个月换苹果电脑
10-30 871
定义：用户变量以“@”符号开头，后面跟着变量名。变量名可以是任何随机的、复合的标量表达式，只要其中没有列指定。赋值可以使用SET语句来定义和赋值用户变量，如。也可以使用SELECT语句来为用户变量赋值，但必须使用“:=”赋值运算符，因为在SELECT语句中，MySQL将“=”运算符视为等于运算符。例如，。作用域：会话用户变量的作用域是当前会话，而局部变量的作用域是其定义的BEGIN/END语句块。命名：会话用户变量以“@”符号开头，而局部变量不需要。使用场景。
【MySql】第五章（存储函数与存储过程）习题
最新发布
她是不会无条件的！！
11-01 518
【MySql】第五章（存储函数与存储过程）习题
mysql 8.0.39 Caused by: java.sql.SQLException: Illegal mix of collations 异常解决
LFCuiYs的博客
10-30 393
发现设置utf8mb3_general_ci 会报错，所以要与参数一直 改成utf8mb4_general_ci 即可生效。java服务可以正常启动，页面发现查询报错Illegal mix of collations。经查看发现是关联表排序异常，因是mysql 8.0.39。然后定位异常 调成一直参数问题解决。
MySQL常用sql语句
yangyang3401的博客
10-29 296
mysql基本操作
MySQL执行计划详解与调优
MySQL执行计划是数据库管理员用来分析SQL查询性能的重要工具，它能够揭示MySQL如何执行SQL查询，帮助理解数据检索的步骤和优化查询性能。本资源主要介绍了两种调用MySQL执行计划的方式以及执行计划中包含的关键信息...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

CodeMavs

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值