Mysql调优(2)-使用执行计划Explain分析你的SQL

最新推荐文章于 2022-05-23 17:15:10 发布

JunSIr_#

最新推荐文章于 2022-05-23 17:15:10 发布

阅读量801

点赞数 2

分类专栏：数据库文章标签：数据库 mysql 索引 sql explain

本文链接：https://blog.csdn.net/JunSIrhl/article/details/105639359

版权

数据库专栏收录该内容

20 篇文章 1 订阅

订阅专栏

文章目录

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

这也是我们调优工作中重要的一环

用法：Explain + SQL语句

通过查看以上字段的信息，可以分析我们的Mysql，接下来逐一讲解每一个字段

演示数据SQL

四张表，每张表两个字段id与content

CREATE TABLE t1(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id)); 
CREATE TABLE t2(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
CREATE TABLE t3(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
CREATE TABLE t4(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id)); 
INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000))); 
INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000)));
INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000)));
INSERT INTO t4(content) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)));

文章目录

1.执行顺序-ID

	1、如果id相同，那么执行顺序从上到下
	2、如果id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
	3、id相同和不同的，同时存在：相同的可以认为是一组，从上往下顺序执行，在所有组中，id值越大，优先级越高，越先执行

连表查询示例：

EXPLAIN SELECT * FROM t1,t2,t3 WHERE t1.id = t2.id AND t2.id = t3.id

先查t1，再查t2，最后查t3

嵌套查询示例：

EXPLAIN SELECT t1.id from t1 WHERE t1.id IN
(SELECT t2.id FROM t2 WHERE t2.id IN
(SELECT t3.id FROM t3 WHERE t3.content='')
);

先查t3，再查t2，最后查t1
即嵌套查询的查询顺序由里到外

2. 查询类型-SELECT TYPE

代表查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询，看上图，就出现了简单查询（Simple）与依赖子查询（DEPEDENT SUBQUERY ）与主查询（Primary）

以下列出Mysql的主要查询类型

SIMPLE

explain select * from emp;

PRIMARY

EXPLAIN SELECT * FROM (SELECT t1.content FROM t1) a  ;

DERIVED

在 FROM 列表中包含的子查询被标记为 DERIVED(衍生)

SUBQUERY

在SELECT或WHERE列表中包含了子查询

如

EXPLAIN SELECT t2.id FROM t2 WHERE t2.id = (SELECT t3.id FROM t3 WHERE t3.id =2 ) ;

UNION

若第二个SELECT出现在UNION之后，则被标记为UNION；
如

EXPLAIN 
SELECT t2.id ,t2.content FROM t2 
UNION ALL
SELECT t3.id ,t3.content FROM t3 ;

UNION RESULT

从UNION表获取结果的SELECT 如上图

Type - 索引

type 是查询的访问类型,是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >ALL

大概记住以下顺序即可
system > const > ref > range > index >ALL

类型越靠前代表性能越高，速度越快

一般来说，得保证查询至少达到 range 级别，最好能达到 ref

下面我们来逐一分析

System

表只有一行记录（等于系统表），这是 const 类型的特列，用户查询不会出现
Const
表示通过索引一次就找到了

  explain select * from emp where empno = 7369;

eq_ref

使用唯一性索引进行数据查找

  EXPLAIN 
  SELECT * FROM t1 ,t2 WHERE t1.id = t2.id ;

ref：使用了非唯一性索引进行数据的查找

 create index idx_3 on emp(deptno);
 explain select * from emp e,dept d where e.deptno =d.deptno;

range：表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了index的全索引扫描，适用的操作符： =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN()

explain select * from emp where empno between 7000 and 7500;

Index
相对于全表扫描的全索引扫描
主要有两种情况，一种是当前的查询时覆盖索引（避免回表），即我们需要的数据在索引中就可以索取，或者是使用了索引进行排序，这样就避免数据的重排序

explain  select empno from emp;

ALL

Full Table Scan，将遍历全表以找到匹配的行

Possible_keys - 可能使用的索引

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

Key

实际使用的索引。如果为NULL，则没有使用索引

Key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 key_len 字段能够帮你检查是否充分的利用上了索引。ken_len 越长，说明索引使用的越充分

Ref

显示索引哪一列被使用，哪些列或常量被用于查找索引列上的值

Rows

rows 列显示 MySQL 认为它执行查询时必须检查的行数，越少越好！

Extra

出现Using filesort

优化：无法利用索引进行排序、考虑对排序字段建立索引或更改排序字段

可见，Mysql无法使用主键索引进行排序，而要对ename列进行排序

优化策略：对ename列建立索引

出现Using temporary

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

表中同时对ename进行了分组

优化策略：同上
Using index

代表相应的 select 操作中使用了覆盖索引(Covering Index)，避免回表
如果同时出现 using where，表明是通过查找索引取得数据，否则就是直接从索引取得数据
Using Index_condition
使用了索引下推
将存储引擎的数据取出，在Server层处理而非在存储引擎中处理，
索引下推是由优化器自动处理的
出现Using join buffer

使用了连接缓存，在后面关于Join连接方式的文章再说