图解: EXPLAIN 实战-1

我欲只争朝夕
发布于 2023-11-3 10:44
浏览
0收藏

介绍

在工作中,我们用于捕捉性能问题最常用的就是打开慢查询,定位执行效率差的SQL,那么当我们定位到一个SQL以后还不算完事,我们还需要知道该SQL的执行计划,比如是全表扫描,还是索引扫描,这些都需要通过EXPLAIN去完成。EXPLAIN命令是查看优化器如何决定执行查询的主要方法。可以帮助我们深入了解MySQL的基于开销的优化器,还可以获得很多可能被优化器考虑到的访问策略的细节,以及当运行SQL语句时哪种策略预计会被优化器采用。


建议先看一下这篇分享​​MySQL索引为什么要用B+树实现?​​,这样你就能更好的理解什么时候需要回表查询,什么时候不需要,explain用法很简单,只需要在执行的select语句前加上explain即可


explain select * from teacher

图解: EXPLAIN 实战-1-开源基础软件社区

图解: EXPLAIN 实战-1-开源基础软件社区

下面来具体分析,准备的数据如下


course表


CREATE TABLE `course` (
 `cid` int(3) NOT NULL,
 `cname` varchar(20) NOT NULL,
 `tid` int(3) NOT NULL,
 PRIMARY KEY (`cid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

图解: EXPLAIN 实战-1-开源基础软件社区

teacher表


CREATE TABLE `teacher` (
 `tid` int(3) NOT NULL,
 `tname` varchar(20) NOT NULL,
 `tcid` int(3) NOT NULL,
 PRIMARY KEY (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

图解: EXPLAIN 实战-1-开源基础软件社区

teacher_card表


CREATE TABLE `teacher_card` (
 `tcid` int(3) NOT NULL,
 `tcdesc` varchar(20) NOT NULL,
 PRIMARY KEY (`tcid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

图解: EXPLAIN 实战-1-开源基础软件社区

id

我们来查询课程编号为2或者教师证编号为3的老师信息


SELECT
 t.*
FROM
 course c,
 teacher t,
 teacher_card tc
WHERE
 c.tid = t.tid
 AND t.tcid = tc.tcid
 AND ( c.cid = 2 OR t.tcid = 3 )


explain上述SQL后,如下所示

图解: EXPLAIN 实战-1-开源基础软件社区

id值相同,从上往下顺序执行


为什么要先从t表中读取数据,然后和tc表做表联结,最后再和c表做表联结呢?(上面的SQL可以改写为join on的形式,执行效果一样)


我们写的SQL会被优化器优化,MySQL会按照小结果集驱动大结果集的方式进行表联结,表联结即对2个表做笛卡尔积,所以联结的顺序是t(3)-tc(3)-c(4),括号中为个数。如果表t表的个数为5,则联结的顺序为tc(3)-c(4)-t(5),注意括号里的数字不是表的数量,是结果集的数量


如果查询课程编号为2的老师信息


SELECT
 t.*
FROM
 course c,
 teacher t,
 teacher_card tc
WHERE
 c.tid = t.tid
 AND t.tcid = tc.tcid
 AND c.cid = 2

图解: EXPLAIN 实战-1-开源基础软件社区

因为可以确定c的结果集最小,只有一个记录,因此联结顺序为c(1)-t(3)-tc(3)


查询教授SQL课程的老师的描述


SELECT
 tc.tcdesc
FROM
 teacher_card tc
WHERE
 tc.tcid = (
SELECT
 t.tcid
FROM
 teacher t
WHERE
 t.tid = ( SELECT c.tid FROM course c WHERE c.cname = "sql" )
 );


这个SQL是先查询c表,再查询t表,最后查询tc表,执行explain看一下

图解: EXPLAIN 实战-1-开源基础软件社区

id值不同,id值越大,越优先执行。将上述SQL改为如下形式


SELECT
 tc.tcdesc
FROM
 teacher t,
 teacher_card tc
WHERE
 tc.tcid = t.tcid
 AND t.tid = ( SELECT c.tid FROM course c WHERE c.cname = "sql" )

图解: EXPLAIN 实战-1-开源基础软件社区

id值有相同,又有不同,id值越大越优先,id值相同,从上往下顺序执行

select_type

SIMPLE:查询不包含子查询和UNION


SELECT * FROM teacher

图解: EXPLAIN 实战-1-开源基础软件社区

PRIMARY:查询有任何复杂的子部分,则外层部分标记为PRIMARYSUBQUERY:包含在SELECT列表中的子查询中的SELECT(换句话说,不在FROM子句中)标记为SUBQUERY


查询教授SQL课程的老师的描述


SELECT
 tc.tcdesc
FROM
 teacher_card tc
WHERE
 tc.tcid = (
SELECT
 t.tcid
FROM
 teacher t
WHERE
 t.tid = ( SELECT c.tid FROM course c WHERE c.cname = "sql" )
 );

图解: EXPLAIN 实战-1-开源基础软件社区

DERIVED:DERIVED值用来表示包含在FROM子句的子查询中的SELECT,MySQL会递归执行并将结果放到一个临时表中。服务器内部称其“派生表”,因为该临时表是从子查询中派生来的


SELECT
 cr.cname
FROM
 ( SELECT * FROM course WHERE tid IN ( 1, 2 ) ) cr

图解: EXPLAIN 实战-1-开源基础软件社区

id为1的table表名为<derived2>,表示是一张派生表,派生表从id为2的执行过程中来


UNION:在UNION中的第二个和随后 的SELECT被标记为UNION。第一个SELECT被标记就好像它以部分外查询来执行。这就是下面第一个例子中在UNION中的第一个SELECT显示为PRIMARY的原因。如果UNION被FROM子句中的子查询包含,那么它的第一个SELECT会被标记为DERIVED,即下面的第二个例子


UNION RESULT:用来从UNION的匿名临时表检索结果的SELECT被标记为UNION RESULT


SELECT * FROM course WHERE tid = 1 UNION SELECT * FROM course WHERE tid = 2

图解: EXPLAIN 实战-1-开源基础软件社区

SELECT
 cr.cname
FROM
 ( SELECT * FROM course WHERE tid = 1 UNION SELECT * FROM course WHERE tid = 2 ) cr

图解: EXPLAIN 实战-1-开源基础软件社区

type

索引类型, 要对type进行优化的前提是有索引。type类型有很多,这里不一一介绍,值介绍几个常用的,性能依次从最优到最差const,system>eq_ref>>ref>range>index>all其中system,const只是理想情况,实际能达到ref>range


system:只有一条数据的系统表,或派生表只有一条数据的子查询


SELECT
 a.tname
FROM
 ( SELECT * FROM teacher t WHERE t.tid = 1 ) a

图解: EXPLAIN 实战-1-开源基础软件社区

const:仅仅能查到一条数据的SQL,用于Primary key或unique索引。如果只是能查到一条数据,但是条件列上没有Primary key或unique索引,则不是const


SELECT * FROM teacher t WHERE t.tid = 1

图解: EXPLAIN 实战-1-开源基础软件社区

假如说去掉tid上的主键(只是针对这个例子临时更改),执行下面的语句,结果如下


SELECT * FROM teacher t WHERE t.tid = 1

图解: EXPLAIN 实战-1-开源基础软件社区

此时type为ALL,验证了我们的想法,即虽然结果只有一条,但条件列上没有Primary key或unique索引,也不是const


eq_ref:唯一性索引,对于每个键的查询,返回匹配唯一行数据(有且只有一个,不能多,不能0),常见于唯一索引和主键索引,不是必须的有索引,我下面句的例子中teacher表的tcid字段就没有加任何索引,当然是我数据太简单的问题,才能在不建索引的情况下出现eq_ref


SELECT
 t.tcid
FROM
 teacher t,
 teacher_card tc
WHERE
 t.tcid = tc.tcid

图解: EXPLAIN 实战-1-开源基础软件社区

来看看此时表的数据

teacher表

图解: EXPLAIN 实战-1-开源基础软件社区

teacher_card表

图解: EXPLAIN 实战-1-开源基础软件社区

此时对于teacher表的tcid这个键,都会返回唯一条数据,所以type为eq_ref,假如说将teacher表增加一条数据(只是针对这个例子临时更改),teacher_card表不变,数据如下


teacher表

图解: EXPLAIN 实战-1-开源基础软件社区

重新执行上述SQL


SELECT
 t.tcid
FROM
 teacher t,
 teacher_card tc
WHERE
 t.tcid = tc.tcid

图解: EXPLAIN 实战-1-开源基础软件社区

可以看到type类型变为了ALL,因为对于teacher表,4这个键,返回了0条数据,不是每个键值都返回了一条数据


上面的例子teacher_card都保持了不变,是为了验证的严密性,如果给teacher_card表增加数据再执行eq_ref的2个例子,其实是没有影响的,因为eq_ref针对的是teacher的键都能返回唯一行数据


ref:非唯一性索引,对于每个索引键的查询,返回匹配所有行(0,多)


修改表为如下(只是针对这个例子临时更改),出现了一个同名的老师张三,并且在teacher表的name列加上普通索引,演示一下匹配行有多个的情况


teacher表

图解: EXPLAIN 实战-1-开源基础软件社区

SELECT
 *
FROM
 teacher
WHERE
 tname = "张三"


当查询姓名为张三的老师时,会返回2条数据

图解: EXPLAIN 实战-1-开源基础软件社区

range:检索指定范围的行,where后面是一个范围查询(between,>,<,>=,in有时候会失效,从而转为无索引ALL)


SELECT * FROM teacher WHERE tid < 3

图解: EXPLAIN 实战-1-开源基础软件社区

index:这个跟全表扫描一样,只是MySQL扫描表时按索引次序进行而不是行


对teacher表的name字段建索引(只是针对这个例子临时更改)


SELECT tname FROM teacher

图解: EXPLAIN 实战-1-开源基础软件社区

对tname建立索引,当我们只查询tid时,它的值已经在B+树的叶子节点上了,不需要回表查询,从索引中就可以拿到,因为非聚集索引,叶子节点存放索引键值,以及该索引键值指向的主键


接着,同时查tname(tname上有索引),tcid(tcid上没有索引)


SELECT tname, tcid FROM teacher

SELECT tname, tcid FROM teacher

图解: EXPLAIN 实战-1-开源基础软件社区

可以看到type为ALL,因为从tname索引中拿不到tcid的数据,只能通过全表扫描


现在我们想同时查询tname和tcid,不想通过回表,只想通过索引表拿到数据,应该怎么建索引呢?现在我们可以肯定的是,只对tname加索引,或者只对tcid加索引肯定是不行的,那么在tname和tcid上都分别加索引呢(只是针对这个例子临时更改)?

图解: EXPLAIN 实战-1-开源基础软件社区

执行如下sql


SELECT tname, tcid FROM teacher

图解: EXPLAIN 实战-1-开源基础软件社区

可以看到是ALL,针对这种情况我们得对tname和tcid建联合索引,因为只有联合索引才能拿到tname和tcid的值,还不用回表

图解: EXPLAIN 实战-1-开源基础软件社区

再次执行


SELECT tname, tcid FROM teacher

图解: EXPLAIN 实战-1-开源基础软件社区

key使用了联合索引


all:全表扫描


SELECT * FROM teacher WHERE tcid = 1

图解: EXPLAIN 实战-1-开源基础软件社区

possible_keys

可能用到的索引,是一种预测,不准

key

实际使用到的索引

key_len

​索引的长度,用于判断联合索引是否被完全使用,单开一文来讲

ref

列出是通过常量(const),还是某个表的某个字段(如果是join)来过滤(通过key)


先对teacher表的tname字段和course表的tid字段增加索引

SELECT
 c.cid,
 t.tname
FROM
 course c,
 teacher t
WHERE
 c.tid = t.tid
 AND t.tname = "张三"

图解: EXPLAIN 实战-1-开源基础软件社区

第一个为const表示常量,即张三,第二个为test2.t.tid,表示couse表引用的是test2库中的t(teacher)表的tid字段

rows

这一列是MySQL估计为了找到所需的行而要读取的行数

Extra

Using index:所需要的数据,只需要在索引即可全部获得,而不需要再到表中取数据


Using where:如果我们不是读取表的所有数据,或者不是仅仅通过索引就可以获取所有需要的数据,则会出现Using where信息。


Using indexUsing where前面已经有例子,当出现索引覆盖时,会显示Using index,性能得到了提升,出现Using temporary和Using filesort说明性能损耗比较大


Using temporary:当MySQL某些操作中必须使用临时表时,在Extra信息中就会出现Using temporary。主要常见于GROUP BY和ORDER BY等操作中


Using filesort:这意味着MySQL会对结果使用一个外部索引排序,而不是按索引次序从表里读取行


对teacher表的name字段建索引(只是针对这个例子临时更改),


SELECT * FROM teacher WHERE tname = "张三" ORDER BY tname

图解: EXPLAIN 实战-1-开源基础软件社区

SELECT * FROM teacher WHERE tname = "张三" ORDER BY tcid

图解: EXPLAIN 实战-1-开源基础软件社区




文章转载自公众号:Java识堂

分类
已于2023-11-3 10:44:39修改
收藏
回复
举报
回复
    相关推荐