利用Explain分享SQl语句,优化你的查询

2022-11-19 19:06:48

前言

最近在开发团队内有个成员分享计划，我自己呢，因为sql平时写的不少，有时需要借助explain优化自己写的sql语句，于是自己就打算来深入了解一下explain。

explain干什么用？

知道sql语句的执行顺序
是否用了索引，索引的使用
表之间的关系、关联情况
where 语句的优化
…

环境

mysql-5.7.23版本

explain语句信息，各列(简要)

字段	说明
id	查询序列号
select_type	查询类型
table	表名
type	针对单表的访问方法
possible_key	可能用到的索引
key	实际用到的索引
key_len	实际用到的索引长度
ref	哪些列或常量用于索引列
rows	预估读取表的记录数
filtered	经过搜索条件过滤，剩余记录数/表总记录数（百分比）
extra	额外信息

详解

ID

相同ID，自上而下执行
不同ID，ID越大，优先值越高，越先被执行
既有相同又有不同ID，也符合这个规则

实验准备：表结构

    CREATE TABLE `hro_city` (
    `Province` varchar(50) DEFAULT NULL COMMENT '城市名称:',
    `City` varchar(50) DEFAULT NULL COMMENT '省份:',
    `Code` char(4) NOT NULL COMMENT '城市编号: ',
    PRIMARY KEY (`Code`)
    ) ENGINE=InnoDB DEFAULT CHARSET=gbk;
    
    CREATE TABLE `hro_supplier` (
    `ID` varchar(50) DEFAULT NULL COMMENT 'ID:',
    `CName` varchar(50) DEFAULT NULL COMMENT '商户名称',
    `City` char(4) DEFAULT NULL COMMENT '所在城市',
    PRIMARY KEY (`SupID`),
    KEY `CName` (`CName`)
    ) ENGINE=InnoDB DEFAULT CHARSET=gbk;
    
    CREATE TABLE `adj_emp` (
    `ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID:',
    `AdjCityID` bigint(20) DEFAULT NULL COMMENT '任务ID',
    `EmpID` int(11) DEFAULT NULL COMMENT '员工ID: ',
    `CtmID` int(11) DEFAULT NULL COMMENT '客户ID:',
    `CName` varchar(50) CHARACTER SET utf8mb4 NOT NULL COMMENT '员工姓名',
    `IDCard` varchar(136) NOT NULL DEFAULT '' COMMENT '身份证',
    `Supplier` varchar(50) DEFAULT NULL COMMENT '商户名称',
    UNIQUE KEY `AdjCityCtmEmp` (`AdjCityID`,`CtmID`,`EmpID`),
    KEY `EmpID` (`EmpID`),
    KEY `CtmID` (`CtmID`),
    KEY `IDCard` (`IDCard`,`CName`),
    ) ENGINE=InnoDB AUTO_INCREMENT=6966415 DEFAULT CHARSET=gbk;
    
    CREATE TABLE `adj_ctm` (
    `ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID: ',
    `CtmID` int(11) NOT NULL COMMENT '客户ID:',
    `AdjCityID` bigint(20) NOT NULL COMMENT '任务ID',
    `Status` varchar(2) NOT NULL DEFAULT '1' COMMENT '状态',
    PRIMARY KEY (`ID`),
    KEY `CtmID` (`CtmID`),
    KEY `adjCtm_AdjCityIDCtmID` (`AdjCityID`,`CtmID`),
    KEY `AdjCityID` (`AdjCityID`) USING BTREE
  ) ENGINE=InnoDB AUTO_INCREMENT=201230 DEFAULT CHARSET=gbk;
  
    CREATE TABLE `emp` (
    `id` int(11) NOT NULL,
    `idno` int(8) DEFAULT NULL,
    `cname` varchar(10) DEFAULT NULL,
    `deptid` int(11) DEFAULT NULL,
    `status` tinyint(2) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_idno_name` (`idno`,`cname`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
   CREATE TABLE `dept` (
   `id` int(11) NOT NULL,
   `name` varchar(10) DEFAULT NULL,
   PRIMARY KEY (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1、ID相同

EXPLAIN 
SELECT cname FROM hro_supplier where  City in (SELECT CODE FROM hro_city WHERE province ='河南' AND city='郑州')

图1.1

自上而下加载，hro_city->hro_supplider

2、ID不同

EXPLAIN 
SELECT cname FROM hro_supplier where  City = (SELECT CODE FROM hro_city WHERE province ='河南' AND city='郑州')

图1.2

id值越大越先加载，hro_city->hro_supplier

3、既有相同又有不同ID

EXPLAIN 
SELECT * FROM adj_emp where adjcityid=2 AND supplier in(SELECT cname FROM hro_supplier where  City =(SELECT CODE FROM hro_city WHERE province ='河南' AND city='郑州') )

图1.3

hro_city->hro_supplier,子查询生成临时表subquery2,再依次处理adj_emp->subquery2

SELECT_TYPE

1、SIMPLE

简单查询，不使用union或者子查询(sql优化器编译后的语句中判断时候存在子查询)

2、UNION

两表union联表内层的select

EXPLAIN
SELECT * FROM hro_city where code='0200' union SELECT * FROM hro_city where code='2400'

图2.1

3、UNION RESULT

uinon结果集,如上图

4、PRIMARY

复杂的查询外层select、两表union联表外层的select（参考图1.2,2.1）

5、SUBQUERY

子查询，即是查询语句的嵌套，即在外部查询中还包含一个内部查询，子查询当中，当子查询的结果是另一个查询的条件
(参考图1.2)

6、 DEPENDENT SUBQUERY

依赖子查询, 内部查询依赖外部查询条件

EXPLAIN
SELECT * FROM adj_emp ae where ae.adjcityid= 4 
and EXISTS (SELECT ctmid from adj_ctm ac where ac.adjcityid=4 AND ac.STATUS= 2 and ae.ctmid=ac.ctmid )

图2.2

先去adj_emp查找，每查询一行记录，再去匹配adj_ctm表.子查询的where的条件依赖于外层表

7、DERIVED

衍生表，在Mysql5.5.27版本中，下面的select查询，from列表中的子查询会标记成 DERIVED（衍生）,但在Mysql5.7.23版本下此条sql已经被优化成两个SIMPLE类型的查询

EXPLAIN  SELECT t2.*,t1.name FROM dept t1,(SELECT * from emp  where STATUS=2)t2 where t1.id=t2.deptid

图2.3

8、MATERIALIZED

被物化子子查询，具体实现方式是：在SQL执行过程中，第一次需要子查询结果时执行子查询并将子查询的结果保存为临时表，后续对子查询结果集的访问将直接通过临时表获得

EXPLAIN 
SELECT * FROM adj_emp  where adjcityid=2 
AND supplier in(SELECT cname FROM hro_supplier where isUse='01' AND city IN (SELECT CODE FROM hro_city where province='浙江' ) )

图2.4

9、UNCACHEABLE SUBQUERY

结果集不能被缓存的子查询,必须重新为外层查询的每一行进行评估

10、UNCACHEABLE UNION

UNION中的第二个或随后的 select 查询,属于不可缓存的子查询

TABLE

表名：物理表、衍生表、表别名等

PATITION

匹配的分区信息，如果查询是基于分区表的话，会显示查询将访问的分区

TYPE

1、SYSTEM

表中只有一条记录，是下面const类型的特殊情况

2、CONST

常量类型，当使用主键或唯一索引进行查询且只匹配到一行时，连接类型是const

EXPLAIN 
SELECT * from hro_city where code=0200;   --没用索引
SELECT * from hro_city where code=’0200’; --使用索引

3、EQ_REF

唯一性索引扫描，取本表中和关联表表中的每行组合成的一行，对于每个索引键，表中只有一条记录与之匹配

EXPLAIN 
SELECT ac.* FROM adj_city ac,hro_city hc where ac.citycode = hc.code;

4、REF

EXPLAIN
SELECT ae.* FROM adj_ctm ac,adj_emp ae where ac.adjcityid=ae.adjcityid and ac.ctmid= ae.ctmid and  ae.adjcityid=2 and ac.status=2;

5、FULLTEXT

使用了全文索引

6、REF_OR_NULL

类似ref,但可以搜索值为NULL的行

7、INDEX_MERGE

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

8、UNIQUE_SUBQUERY

代替 eq_ref 针对 IN 子查询的优化，IN 子句返回唯一索引

9、INDEX_SUBQUERY

与 unique_subquery 相似，但 IN 子句返回非唯一索引

10、RANGE

范围查询，有一下几种情况： =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN()

11、INDEX

全索引扫描

SELECT adjcityid,ctmid,empid FROM adj_emp ae where empid not in(2000);

12、ALL

全表扫描

POSSIBLE_KEY

可能用到的索引

KEY

实际上使用的索引

KEY_LEN

根据表中定义索引长度和实际用了索引列计算所得。跟列的数据类型有关，字符串与编码集有关，还有是否允许为0，字符串是否是可变长字段

数据类型	字节数
char(n)	(character set：utf8m64=4,utf8=3,gbk=2,latin1=1)*n
varchar(n)	(character set：utf8m64=4,utf8=3,gbk=2,latin1=1)*n+2【2是varchar是可变长度字段】
tinyint	1
smallint	2
int	4
bigint	8
timestamp	4

允许为空 1字节

REF

哪些列或常量用于查找索引列

EXPLAIN 
SELECT ae.* FROM adj_ctm ac,adj_emp ae where ac.adjcityid = ae.adjcityid and ac.ctmid = ae.ctmid and ac.ctmid=24626 and ac.status ='2'

图2.5

ROWS

预估的需要读取的记录条数

FILTERED

某个表经过搜索条件过滤后剩余记录条数的百分比-估算值,因为统计的信息只有表行数和索引列的唯一值数目，使得优化器经常不能对数据规模有准确的评估。

EXTRA

1、USING WHERE

通常是因为全表扫描或全索引扫描时（type 列显示为 ALL 或 index），又加上了WHERE条件，建议添加适当的索引

2、USING INDEX

使用覆盖索引的时候就会出现，不需要读取数据文件，从索引树（索引文件）中即可获得信息

EXPLAIN
SELECT adjcityid  ,ctmid ,empid FROM adj_emp ae  order by adjcityid,ctmid,empid;

图3.1

3、USING INDEX & USING WHERE

查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

EXPLAIN
SELECT adjcityid  ,ctmid ,empid FROM adj_emp ae where adjcityid=2  order by ctmid,empid;

图3.2

4、USING INDEX CONDITION

查找使用了索引，但是需要回表查询数据

 EXPLAIN
 SELECT adjcityid  ,ctmid ,empid,cname FROM adj_emp ae where adjcityid=2  order by ctmid,empid;

图3.3

5、USING FILESORT

using filesort 是Mysql里一种速度比较慢的外部排序,需要注意避免出现
表示无法利用索引完成排序，不是“基于硬盘的排序”。它有两种排序方法：

(1) 根据sql条件找出符要求的列字段，以及行指针<sort_key,rowid>，放在缓冲区，进行快速排序(quicksort),缓存区超过了
sort_buffer_size的大小，将<sort_key,rowid>生成临时文件，若数据量过大，重复此过程，生成多个临时文件，再进行归并排序(mergesort)，最后再根据行指针回表查询全部的字段信息。

可用命令查看Mysql的sort_buffer_size大小：show GLOBAL VARIABLES like 'sort_buffer_size'，默认2M，如图3.4

(2)根据sql条件所有要用的字段信息<sort_key,additional_fields>，再进行排序，这和上面这种方法比优势在于只用回表查询一次，比上面方法的2次查询要少；它的缺点在于假如要查询的字段列很多，则需要更多的空间来排序。因此Mysql根据max_length_for_sort_data来采用第一种或者第二种算法排序，默认是1024，即在不超出数量为1024列的情况下采用了第二种排序

可用命令查看Mysql的max_length_for_sort_data大小：show GLOBAL VARIABLES like 'max_length_for_sort_data'

图3.4
图3.5

6、USING TEMPORARY

如果在GRPUP BY 或ORDER BY的查询中，出现USING TEMPORARY，说明需要创建临时表满足这样的查询如图3.6。有可能创建内存临时表或硬盘临时表。
那么该是如何选择呢？
当需要的空间大于tmp_table_size、max_heap_table_size其中的最小值时，则是创建硬盘临时表
可以用命令分别查看tmp_table_size、max_heap_table_size的大小,如图3.7,3.8：

show global variables like 'tmp_table_size';
show global variables like 'max_heap_table_size';

可用命令查询已创建的硬盘临时表和内存临时表的数量,如图3.9：show global status like '%tmp%';

 SELECT ae.adjcityid ,ae.ctmid,ac.status  FROM adj_ctm ae,adj_city ac where ae.adjcityid=ac.id and ac.status in('2','3')  order by ae.adjcityid ,ac.status

图3.6

图3.7

图3.8

图3.9

好了，本期就到这里了！欢迎关注我的公众号【肥犊大人】~~，一起讨论，欢迎建议

肥犊大人发布了8 篇原创文章 · 获赞 0 · 访问量 810 私信关注

码农公寓

前言