使用 EXPLAIN 关键字可以模拟优化器执行SQL语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是结构的性能瓶颈,另外
- explain extend + show warnings:可以查看mysql优化后的sql语句
- explain partitions:相比 explain 多了个 partitions 字段,如果查询是基于分区表的话,会显示查询将访问的分区。
在看执行计划前,我们先创建三张表。一张课程表,一张老师表,一张老师联系方式表(三张表没有任何索引):
-- 课程表
DROP TABLE IF EXISTS course;
CREATE TABLE `course`(
`cid` int(3) DEFAULT NULL,
`cname` varchar(20) DEFAULT NULL,
`tid` int(3) DEFAULT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 老师表
DROP TABLE IF EXISTS teacher;
CREATE TABLE `teacher`(
`tid` int(3) DEFAULT NULL,
`tname` varchar(20) DEFAULT NULL,
`tcid`int(3) DEFAULT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 老师联系方式表
DROP TABLE IF EXISTS teacher_contact;
CREATE TABLE `teacher_contact`(
`tcid` int(3) DEFAULT NULL,
`phone` varchar(200) DEFAULT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `course` VALUES('1','mysql','1');
INSERT INTO `course` VALUES('2','jvm','1');
INSERT INTO `course` VALUES('3','juc','2');
INSERT INTO `course` VALUES('4','spring','3');
INSERT INTO `teacher` VALUES('1','zhangsan','1');
INSERT INTO `teacher` VALUES('2','lisi','2');
INSERT INTO `teacher` VALUES('3','wangwu','3');
INSERT INTO `teacher_contact` VALUES('1','13688888888');
INSERT INTO `teacher_contact` VALUES('2','18166669999');
INSERT INTO `teacher_contact` VALUES('3','17722225555');
explain的结果有很多的字段,下面的篇幅我们会逐一分析。先确认一下环境:
select version();
show variables like '%engine%';
- 1
- 2
1.id:查询序列编号
1.id值不同
id值不同的时候,先查询id值大的(先大后小)。
-- 查询 mysql 课程的老师手机号
EXPLAIN SELECT tc.phone FROM teacher_contact tc WHERE tcid=(
SELECT tcid FROM teacher t WHERE t.tid=(
SELECT c.tid FROM course c WHERE c.cname='mysql'
)
);
查询顺序:course c --> teacher t --> teacher_contact tc。
2.id值相同
-- 查询课程 ID 为 2,或者联系表 ID 为 3 的老师
EXPLAIN SELECT t.tname, c.cname, tc.phone
FROM teacher t,course c,teacher_contact tc
WHERE t.tid=c.tid AND t.tcid=tc.tcid AND(c.cid=2 OR tc.tcid=3);
id 值相同时,表的查询顺序是从上往下顺序执行。例如这次查询的id都是1,查询的顺序是:teacher t(3条)–> course c(4条)–> teacher_contact tc(3条)。
现在给teacher表插入3条数据,然后再执行上面的查询语句
INSERT INTO `teacher` VALUES(4,'james',4);
INSERT INTO `teacher` VALUES(5,'tom',5);
INSERT INTO `teacher` VALUES(6,'seven',6);
-- 测试完后删除
DELETE FROM teacher where tid in(4,5,6);
id 也都是1,但是从上往下查询顺序变成了: teacher_contact tc(3条)–> teacher t(6条)–> course c(4条)。
为什么数据量不同的时候顺序会发生变化呢?这个是由笛卡尔积决定的。
举例:假如有a、b、c三张表,分别有2、3、4条数据,如果做三张表的联合查询,当查询顺序是 a→b→c 的时候,它的笛卡尔积是:
2*3*4=6*4=24
。如果查询顺序是 c→b→a,它的笛卡尔积是4*3*2=12*2=24
因为MySQL要把查询的结果,包括中间结果和最终结果都保存到内存,所以MySQL会优先选择中间结果数据量比较小的顺序进行查询。所以最终联表查询的顺序是a→b→c。这个就是为什么teacher表插入数据以后查询顺序会发生变化。(小表驱动大表的思想)
如果ID有相同也有不同,就是ID不同的先大后小,ID相同的从上往下。
2.select type:查询类型
下面列举了一些常见的查询类型:
1.简单查询
SIMPLE,简单查询,不包含子查询,不包含关联查询union。
EXPLAIN SELECT * FROM teacher;
2.子查询型
- PRIMARY:子查询SQL语句中的主查询,也就是最外面的那层查询。
- SUBQUERY:子查询中所有的内层查询都是SUBQUERY类型的。where后的子查询
- DERIVED:MySQL 会将结果存放在一个临时表中,也称为派生表。from后的子查询
-- 查询 mysql 课程的老师手机号
EXPLAIN SELECT tc.phone
FROM (SELECT * FROM teacher_contact) tc
WHERE tcid=(
SELECT tcid FROM teacher t WHERE t.tid=(
SELECT c.tid FROM course c WHERE c.cname='mysql'
)
);
3.并查询型
- UNION:用到了UNION查询(对于关联查询,先执行右边的 table(UNION),再执行左边的 table)
- UNIONRESULT:主要是显示哪些表之间存在UNION 查询。<union2,3>代表 id=2 和id=3 的查询存在UNION
-- 查询 ID 为 1 或 2 的老师教授的课程
EXPLAIN SELECT cr.cname
FROM(
SELECT * FROM course WHERE tid=1
UNION
SELECT * FROM course WHERE tid=2
)cr;
这里并没有列举全部(其它:DEPENDENT UNION、DEPENDENT SUBQUERY、MATERIALIZED、UNCACHEABLE SUBQUERY、UNCACHEABLE UNION)。
3.table:查询的表
访问的哪个表,注意以下三种特殊情况:
<derivenN>
:当 from 子句中有子查询时,表示当前查询依赖 id=N 的查询<union1,2>
:当有 union 时,UNION RESULT 的 table 列的值为,1和2表示参与 union 的 select 行id- NULL:mysql能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引。例如:在索引列中选取最小值,可以单独查找索引来完成,不需要在执行时访问表
4.type:连接类型
在常用的链接类型中:system > const > eq_ref > ref > range > index > all。以上访问类型除了all,都能用到索引。一般保证达到range,最好是ref。
-- 为teacher表的tcid(第三个字段)创建普通索引。
ALTER TABLE teacher ADD INDEX idx_tcid(tcid);
-- 为 teacher_contact 表的 tcid(第一个字段)创建一个索引。
ALTER TABLE teacher_contact ADD PRIMARY KEY(tcid);
1.const
主键索引或者唯一索引,只能查到一条数据的SQL。
-- 查询id为1的teacher联系方式
EXPLAIN SELECT * FROM teacher_contact WHERE tcid=1;
2.system
system 是 const 的一种特例。表中只有一行数据,且使用唯一索引(primary key / unique key)查询。
EXPLAIN SELECT * FROM (SELECT * FROM `teacher_contact` where tcid=1)tmp;
3.eq_ref
通常出现在多表的联查(join),primary / unique key被作为连接部分(on),所以对于前表的每一个结果,都只能匹配到后表的一行结果。
EXPLAIN select t.tcid from teacher t LEFT JOIN teacher_contact tc ON t.tcid=tc.tcid;
简单查询不会出现该类型。eq_ref是除const之外最好的访问类型。
小结:以上三种system,const,eq_ref,都是可遇而不可求的,基本上很难优化到这个状态。
4.ref
使用非唯一索引查询,或者关联操作只使用了索引的最左前缀,可能匹配到多行。
-- 使用tcid上的普通索引查询
explain SELECT * FROM teacher where tcid=3;
5.range
索引范围扫描。如果where后面是 between and 或 <或 > 或 >= 或 <=或in这些, type类型就为range。
EXPLAIN SELECT * FROM teacher_contact WHERE tcid>=1;
EXPLAIN SELECT * FROM teacher_contact WHERE tcid BETWEEN 1 AND 2;
EXPLAIN SELECT * FROM teacher_contact WHERE tcid IN (1,2,3);
6.index
扫描全表索引(Full Index Scan),这通常比ALL快一些。(index是从(覆盖)索引中读取的,而all是从硬盘中读取)
EXPLAIN SELECT tcid FROM teacher;
7.ALL
Full Table Scan,如果没有索引或者没有用到索引,type就是ALL。代表全表扫描。
EXPLAIN SELECT * FROM teacher;
8.Null
不用访问表或者索引就能得到结果,例如:
EXPLAIN select 1 from dual where 1=1;
一般来说,需要保证查询至少达到range级别,最好能达到ref。ALL(全表扫描)和index(查询全部索引)都是需要优化的。
5.possible_keys、key、key_len:索引信息
- possible_keys: 可能使用的索引
- keys: 实际使用的索引,可能出现由于数据较少possible_keys有值,keys=NULL,因为mysql内部优化为全表扫描
- key_len:索引的长度(使用的字节数)。
- 跟索引字段的类型、长度有关
- 对于联合索引而言,可以推算出使用了什么索引列
-- 给Course表创建一个联合索引
ALTER TABLE course ADD INDEX idx_cname_tid(cname,tid);
EXPLAIN SELECT * FROM course WHERE tid=1;
key_len是5,这个5怎么来的呢?
- 字符串:
- char(n):n
- varchar(n):3n+2 (2字节用来保存长度)
- 数值类型:
- tinyint:1
- smallint:2
- int:4
- bigint:8
- 时间类型:
- date:3
- timestamp:4
- datatime:8
- 若允许NULL还有一位用来记录
注:如果数值类型不采用默认长度,而是自己指定了长度,就需要2个字节保存。所以上面的5=指定的3+保存长度的2。
6.ref、rows
- ref:这一列显示了在key列记录的索引中,表查找值所用到的列或常量,常见的有:const(常量),字段名(例:film.id)
- rows:读取并检测的行数,不是结果集数。是一个预估值,一般来说行数越少越好。
还有一个字段 filtered,这个字段表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例,它是一个百分比。
7.Extra:额外信息
1.Using index
覆盖索引 + 相应索引查找
EXPLAIN SELECT tcid FROM teacher;
2.Using where
无覆盖索引 + 无索引查找 / 所用索引非前导列
EXPLAIN SELECT * FROM teacher WHERE tname='张三';
3.Using index;Using where
覆盖索引 + 无索引查找(其实是索引下推)
EXPLAIN SELECT tid FROM course WHERE cname='jvm';
4.NULL
无覆盖索引 + 索引查找
EXPLAIN SELECT * FROM teacher WHERE tcid=1;
5.Using index condition
联合索引下,只使用了部分索引列
EXPLAIN SELECT * FROM course WHERE tid>1;
6.Using temporary
创建了临时表。一般需要索引去优化,例如创建复合索引。
EXPLAIN select DISTINCT(tid) from teachert; -- distinct 非索引列
EXPLAIN select tname from teacher group by tname; -- group by 非索引列
EXPLAIN select t.tid from teacher t join course c on t.tid=c.tid group by t.tid; -- 使用join的时候,group任意列
7.Using filesort
不能使用索引来排序,数据都查出来后排序,用到了额外的排序(跟磁盘或文件没有关系)。需优化。
EXPLAIN select * from teacher order by(tname);
总结一下:模拟优化器执行SQL查询语句的过程, 来知道MySQL是怎么处理一条SQL语句的。通过这种方式我们可以分析语句或者表的性能瓶颈。分析出问题之后,就是对SQL语句的具体优化。比如怎么用到索引,怎么减少锁的阻塞等待…