定位了查询慢的SQL之后,就可以使用EXPLAIN或者DESCRIBE工具做针对性的分析查询.两者使用方法相同,并且分析结果也是相同的.
这个执行计划展示了此时此刻呢进行具体查询的方式,比如多表连接的顺序、对每个表采用什么方式进行具体的查询等等,MySQL提供的EXPLAIN语句可以用来查询某个查询语句的具体执行计划,根据EXPLAIN语句的输出项,可以有针对性地提升查询SQL的性能.
能查到什么?表的读取顺序
数据读取操作的操作类型
哪些索引可以被使用
哪些索引实际被使用
表之间的引用关系
每张表有多少行被优化器查询
CREATE TABLE s1 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2(key2),
INDEX idx_key3(key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
)ENGINE=INNODB CHARSET=utf8
CREATE TABLE s2 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2(key2),
INDEX idx_key3(key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
)ENGINE=INNODB CHARSET=utf8
-- 函数返回随机字符串
DELIMITER //
CREATE FUNCTION +rand_string+(n INT) RETURNS varchar(255) CHARSET utf8mb4
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
RETURN return_str;
END //
DELIMITER ;
首先要确保相信函数的变量log_bin_trust_function_creators为1
SELECT @@log_bin_trust_function_creators variable;
SET GLOBAL log_bin_trust_function_creators = 1;
DELIMITER //
CREATE PROCEDURE insert_s1 (IN min_num INT (10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO s1 VALUES(
(min_num + i),
rand_string(6),
(min_num + 30* i + 5),
rand_string(6),
rand_string(10),
rand_string(5),
rand_string(10),
rand_string(10)
);
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER;
DELIMITER //
CREATE PROCEDURE insert_s2 (IN min_num INT (10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO s1 VALUES(
(min_num + i),
rand_string(6),
(min_num + 30* i + 5),
rand_string(6),
rand_string(10),
rand_string(5),
rand_string(10),
rand_string(10)
);
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER;
CALL insert_s1(10001, 10000);
CALL insert_s2(10001, 10000);
id,在一个大的查询语句中每个SELECT关键字都对应着一个唯一的id,所以有几个select关键字就会有几个id：
EXPLAIN SELECT * FROM s1
EXPLAIN SELECT * FROM s1 INNER JOIN s2
上面的两个SQL都只有一个select所以只有一个id
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a'
查询优化器可能会对涉及子查询的查询语句进行重写：
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a')
重写后的sql变成了一个select,所以查询结果仍然是一个id
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE common_field = 'a')
UNION去重
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
如果使用的是UNION ALL不进行去重,则是：
EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;
小结：
id如果相同,则会被认为是同一组查询,会按照从上往下的顺序执行
如果不同,则id越大的优先级越高,越先执行
id的号码表示一趟独立的查询,一个sql的查询趟数越少越好
一个大的查询里面可以包含多个select关键字,每个select关键字代表一个小的查询语句,而每个小的查询中都包含着若干的表进行连接操作,而每一张表都对应着EXPLAIN查询计划的一条记录,对于在同一个select关键字的表来说,他们的id是相同的.
select_type：SELECT关键字对应查询的类型,即我们只要知道了某个小查询的select_type属性,就能知道这个小查询在大查询中扮演的角色、起到的作用
常见的select_type：
SIMPLE：不包含UNION或者子查询的查询都算是SIMPLE类型
UNION、PRIMARY、UNION RESULT：对于包含UNION和UNION ALL的语句,它是由几个小的查询组成的,除了最左边的查询的select_type是PRIMARY,其余的均为UNION,而针对临时表的select则是UNION RESULT
SUBQUERY：如果包含子查询的查询语句不能够转化为semi-join的方式（即优化器将子查询优化为表连接）,并且子查询不是相关子查询(即用到了外表的子查询),则该子查询的第一个select关键字代表的那个查询的select_type就是SUBQUERY
explain select * from s1 where key1 in (select key1 from s2) or key3 = 'a'
首先这个子查询不是相关子查询,那么这个sql能不能优化成表连接的sql呢?
select * from s1 INNER JOIN s2 on s1.key1 = s2.key1
DEPENDENT SUBQUERY：如果包含子查询的查询语句不能够转化为semi-join的方式,但是子查询涉及到了外表,也就是为相关子查询,那么该子查询的第一个select关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 from s2 WHERE s1.key2 = s2.key2) OR key3 = 'a'
select_type 为 DEPENDENT SUBQUERY 的查询可能会被执行多次
DEPENDENT UNION：在包含UNION和UNION ALL的大查询中,如果各个小查询都依赖于外层查询的话,那除了最左边的小查询之外,其余查询的select_type均为DEPENDENT UNION
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b')
第二个子查询UNION加上了DEPENDENT 好理解,因为用到了外表
但是,为什么第一个子查询没有用到外表,也是DEPENDENT SUBQUERY呢?
这是由于优化器对于in的改动：
DERIVED：派生表对应子查询的select_type为DERIVED
EXPLAIN SELECT * FROM (SELECT key1, count(*) AS c FROM s1 GROUP BY key1) AS derived_s1 WHERE c > 1
MATERIALIZED（物化）：当查询优化器在执行包含子查询语句的时候,选择将子查询之后与外层查询进行连接时,该子查询对应的select_type就是MATERIALIZED
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2)
而外层select直接将子查询成的物化表看做普通的表,查询方式为SIMPLE
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) or key3 = 'a'
table,即表名
查询出来的每一行记录都对应着一个单表
EXPLAIN SELECT * FROM s1
EXPLAIN SELECT * FROM s1, s2
可以看到两个记录的id是一样的,因为属于同一个大的查询语句（只有一个select）
执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法,又称访问类型,即这里的type.比如,type是ref,表名mysql将使用ref方法对改行记录的表进行查询.
all,越靠前代表效率越高
SQL性能优化的目标：至少要达到range级别,要求是ref级别,最好是const级别.
system：当表中只有一条记录,并且该表使用的存储引擎的统计数据是精确的,比如MyISAM、Memory,那么对该表的访问方法就是system
CREATE TABLE t(i INT) ENGINE=MYISAM;
INSERT INTO t VALUES(1);
EXPLAIN SELECT * FROM t
存储引擎的统计数据是精确的,意思是例如MyISAM存储存储引擎有记录的记录的个数
system是性能最高的情况
而如果再添加一条记录,会变为all,而InnoDB即使一条数据也是all
于此同时,INNODB访问count()的数据也是all的
CREATE TABLE tt(i INT) ENGINE=INNODB;
INSERT INTO tt VALUES(1);
EXPLAIN SELECT count(*) FROM tt
const：当根据主键或者唯一的二级索引与常数进行等值匹配的时候,对单表的访问就是const,表示常数级别
EXPLAIN SELECT * FROM s1 WHERE id = 10005;
EXPLAIN SELECT * FROM s1 WHERE key2 = 10066;
EXPLAIN SELECT * FROM s1 WHERE key3 = 1006;
eq_ref：在连接查询的时候,如果被驱动表是通过主键或者唯一的二级索引等值匹配的方式进行访问的（如果主键或者唯一的二级索引是联合索引,则要求索引的每一列进行联合匹配）,则对于该被驱动表的访问方式就是eq_ref
EXPLAIN SELECT * from s1 INNER JOIN s2 WHERE s1.key2 = s2.key2
其中ref表示查询的值已经被指定：即通过all方式查询的s1表指定的
ref：当通过普通的二级索引与常量进行等值匹配来查询某个表,对该表的访问方式可能是ref
EXPLAIN SELECT * FROM s1 WHERE key3 = 'CUTLVwqweqweq';
ref_or_null：当通过普通的二级索引与常量进行等值匹配来查询某个表,当该值也可能是null值时,那么对该 表的访问方式可能就是ref_not_null
EXPLAIN SELECT * FROM s1 WHERE key3 = 'CUTLVwqweqweq' OR key3 IS NULL;
index_merge：单表访问在某些情况下可以使用Intersection、Union、Sort-Union这三种索引合并的方式来执行查询
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key2 = 123131
但是,如果是AND的情况,只会使用一个索引（这里是唯一的二级索引.故而是const）
EXPLAIN SELECT * FROM s1 WHERE key1 = 'rCLXEg' AND key2 = 10036
unique_subquery：是针对一些包含IN子查询的查询语句中,如果查询优化器决定将In子查询语句变为EXISTS子查询,并且子查询可以使用到主键的等值匹配的话,那么子查询的type就是unique_subquery
EXPLAIN SELECT * FROM s1
WHERE key2 IN (SELECT id FROM s2 WHERE s1.key1 = s2.key1) OR key3 = 'a'
range：如果使用索引获取某些范围区间的记录,就可能使用到range方法
EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c')
非索引列则为all
index：当可以使用索引覆盖,并且需要扫描全部的索引记录,该表的访问方法就是index
EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a'
可以看到在key里面还是用到了联合索引的,尽管根据最左前缀原则,只有检索条件为key_part1才能用到索引,这里是因为检索条件和select返回列都是和联合索引相关的列,所以使用了联合索引扫描了全部的索引记录,因为这样就不需要再回表找其他的列了（查的列都在索引上）
不需要回表就能查找到所需要的数据,称作索引覆盖
这时候再添加一个其他的列：
EXPLAIN SELECT key1, key_part2 FROM s1 WHERE key_part3 = 'a'
结果为ALL,因为联合索引列上没有key1的信息,需要回表去查key1
all：全表扫描
在EXPLAIN语句输出的执行计划中,possible_key 表示在单表查询中可能会用到的索引,一般查询涉及到的字段上存在索引,则该索引就将被列出,但不一定被查询使用.
key则表示经过查询优化器计算使用不同索引的查询成本之后,最终确定使用的索引.
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a'
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' OR key3 = 'a'
而如果这里改成OR,则会演变成之前讲的 index_merge 合并索引,即将两个索引树的主键提取取并集,然后统一到聚簇索引中执行一次回表操作
EXPLAIN SELECT key1, key3 FROM s1 WHERE key1 > 'z' OR key3 = 'a'
再扩展一下,即使查询列可以使用覆盖索引（即查询列的值都可以在索引树中找到）,仍然需要进行一次回表操作,所以呢两个查询的执行计划是相同的：
实际使用到的索引的长度（即字节数）,用来查看是否充分利用了索引,index_len的值越大越好
这里的越大越好是跟自己进行的比较,因为主要是针对的联合索引,因为利用联合索引的长度越大,查询需要读入的数据页就越少,效率也就越高
EXPLAIN SELECT * FROM s1 WHERE id = 10005
EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';
EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';
ref表示当我们使用索引列等值查询的时候,与索引列进行等值匹配的对象的信息.
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
key1是普通的二级索引,所以type是ref（唯一的二级索引是const）,而等值的匹配类型是一个常量,所以呢ref列的值是const
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);
key1是普通的二级索引,所以呢type为ref,等值比较的类型是一个函数返回值,所以呢ref列的值为func
rows：预估需要读取的记录条数,值越小越好
值越小表示在同一个数据页中的可能性越大,IO的次数也就越少
filtered：表示某个表经过条件过滤之后,剩余记录条数的百分比,值越大越好
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';
如上表示经过条件过滤后,100%都是符合要求的
而如果执行的是索引的单表扫描,那么计算的时候除了估计出满足对应索引的搜索条件,还应计算同时满足其他条件的记录是多少条
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'b';
对于单表查询这个filtered列其实没有太大作用,但是它在多表连接中驱动表对应的执行计划记录的filtered值,决定了被驱动表的执行次数.
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';
Extra用来说明一些不适合在其他列中展示但是十二分重要的额外信息.通过这些额外信息可以更准确地知道mysql导致是怎么执行给定的查询语句的.
no tables used：没有from字句,即没有用到表的情况
EXPLAIN select 1
impossible where：where语句永远为false的情况
EXPLAIN select * FROM s1 WHERE 1 != 1
这样也没有用到表,反正条件都不对
where：使用全表扫描来执行针对某个表的查询,字句中有针对该表的搜索条件,则在Extra中展现
EXPLAIN select * FROM s1 WHERE common_field = 'a'
common_field是一个不带索引的普通字段,所以呢type为all,Extra展现了语句是通过where执行的
no matching min/max row当查询列表处有min或者max聚合函数,但是没有符合where条件的记录时,将会提示该额外信息
EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'adqwdqweqwe'
而当where条件符合（或者根本没有where条件的时候）,则显示Select tables optimized away,表示选择优化后的表
EXPLAIN SELECT MIN(key1) FROM s1
using index：当出现索引覆盖,即查询和检索条件的列都在使用的索引里面,也即是不需要回表操作的情况
EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a'
当出现主键的情况也是覆盖索引
using index condition：即索引条件下推,考虑下面的sql查询：
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 like '%a%'
using join buffer：即基于块的嵌套循环算法：当被驱动表不能够有效利用索引加快访问速度,mysql就会为其在内存中分配一块join buffer的内存块来加快访问的速度
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field
common_field是一个没有索引的列
not exists：在表连接的时候,当where条件中被驱动表的某个列等于null,而这个列又有非空约束的时候,Extra就会展现not exists
EXPLAIN SELECT * FROM s1 LEFT JOIN s2 on s1.key1 = s2.key1 WHERE s2.id IS NULL
注意一定是被驱动表的列,如果是主驱动表出现这种情况,会直接显示为impossible where,就不会再看被驱动表了
using union(index_merge)：or使用两个索引的情况,即前面type讲到的index_merge,这时候会将两个索引树查出的id取并集然后再回表在进行where条件过滤
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a'
zero limit：limit为0的情况
file sort 文件排序：
有一些情况排序是能够用到索引的：
EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;
这个查询利用idx_key1索引直接取出key1列的10条记录（按照索引列排序的）,然后再拿着记录的主键值进行回表得到全部列的值.但是更多情况下的排序操作无法利用到索引,只能在内存中（记录较少的情况）或者磁盘中进行排序,mysql把这种在内存或者磁盘中排序的方式统称为文件排序 file sort
但是这里有个地方很不理解,为什么去掉limit或者limit较大的时候,就会变成文件排序?
EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 97;
对于没有索引的查询,自然只能文件排序了：
EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;
using temporary：mysql在进行一些如去重、排序的功能的时候,如果不能够有效地利用索引,就可能需要通过建立内部的临时表来完成.
EXPLAIN SELECT DISTINCT common_field FROM s1;
执行计划中出现临时表不是一个很好的征兆,因为建立和维护临时表都需要很大的成本,应该尽量通过使用索引来替换临时表
小结Explain不考虑Cache（不考虑记录的加载方式,只是考量sql语句）
Explain不能显示mysql在执行查询时做的优化工作
Explain不会显示关于触发器、存储过程或用户自定义函数对于查询的影响
部分信息是估算的,并非精确的值
Explain的四种输出格式：传统格式、Json格式、Tree格式、可视化格式
即上面一直在使用的EXPLAIN语句,概要说明查询计划
EXPLAIN SELECT * FROM s1 INNER JOIN s2 on s1.key1 = s2.key2 WHERE s1.common_field = 'a'
EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 on s1.key1 = s2.key2 WHERE s1.common_field = 'a'
{
"query_block": {
]
}
}
read_cost：由两部分组成：IO成本
eval_cost： rows * filtered
EXPLAIN FORMAT=TREE SELECT * FROM s1 INNER JOIN s2 on s1.key1 = s2.key2 WHERE s1.common_field = 'a'
-> Nested loop inner join (cost=139④77 rows=1015)
-> Single-row index lookup on s2 using idx_key2 (key2=s1.key1), with index condition: (cast(s1.key1 as double) = cast(s2.key2 as double)) (cost=0.25 rows=1)
需要安装MySQL workbench
当我们使用Explain语句查看了某个查询语句的执行计划之后,紧接着还可以使用Show warnings来查看与这个查询计划有关的一些扩展信息,比如：
EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 on s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;
mysql> show warnings \G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select +atguigudb1+.+s1+.+key1+ AS +key1+,+atguigudb1+.+s2+.+key1+ AS +key1+ from +atguigudb1+.+s1+ join +atguigudb1+.+s2+ where ((+atguigudb1+.+s1+.+key1+ = +atguigudb1+.+s2+.+key1+) and (+atguigudb1+.+s2+.+common_field+ is not null))
1 row in set (0.00 sec)
看着挺别扭,即下面：
select s1.key1, s2.key1
from s1 join s2
where s1.key1 = s2.key1 and s2.common_field is not null;
以上就是土嘎嘎小编为大家整理的MySQL_十四)分析查询语句Explain 七千字总结相关主题介绍,如果您觉得小编更新的文章只要能对粉丝们有用,就是我们最大的鼓励和动力,不要忘记讲本站分享给您身边的朋友哦！！