一个常见的理解错误:mysql在执行explain时不会执行sql语句,事实上如果查询的from字段有子查询,explain会执行子查询

explain只能解释select查询,对update,delete,insert需要重写为select

ColumnJSONNameMeaning
idselect_idid越大优先;id相同自上而下执行;
select_typeNone查询类型
tabletable_name查询的表
partitionspartitionsThematching partitions
typeaccess_type连接类型
possible_keyspossible_keys可能选择的索引
keykey实际使用的索引
key_lenkey_length使用的索引长度
refref哪一列或常数在查询中与索引键列一起使用
rowsrows估计查询的行数
filteredfiltered被条件过滤掉的行数百分比
ExtraNone解决查询的一些额外信息
  • select_type

表示简单还是复杂的查询,不包括子查询和union的查询为简单查询

如果查询中有任何复杂的部分,外层查询标记为primary

复杂查询分四大类(SUBQUERY,DERIVED,UNION,UNION RESULT)

  1. SUBQUERY:包含在select列表中的子查询中的select,不在from子句中的select
  2. DERIVED:包含在from子句中的select。mysql会递归的执行并将结果放在一个临时表中,服务器内部称其为“派生表”
  3. UNION:在union中第二个和随后的select被标记为union
  4. UNION RESULT:在UNION产生的匿名临时表检索结果的select被标记为union result

select_type共有SIMPLE,PRIMARY,SUBQUERY,DERIVED,UNION,UNION RESULT 六种常见情况


type 决定如何查找表中的行,最差到最优:

  1. ALL:全表扫描
  2. index:和全表扫描一样,只是扫表时按索引次序进行而不是行,主要优点避免了排序,最大的缺点是承担按索引次数读取整张表的开销。如果Extra字段看到Using index,说明用覆盖索引,比按索引次序全表扫描开销少得多
  3. range:有限制的索引扫描,始于索引某一点,返回匹配值域的行。比全索引扫描好一些,用不着遍历全部索引。between或where>,用索引去查找in()和or时也显示range。但这两者性能很重要差异
  4. ref:索引访问(索引查找),它返回所有匹配某个单个值得行,可能找到多个符合条件的行,查找和扫描的混合体。只有在使用非唯一索引或者唯一索引的非唯一前缀时才发生
  5. eq_ref:最多只返回一条记录。主键或者唯一索引查找时看到。它会将他们与某个参考值作比较
  6. const,能从某部分进行优化将其转换为一个常量时,使用这些访问类型。比如:explain select id from mis_audit_comment where id = 1\G;
  7. NULL 意味着能在优化阶段分解查询语句,在执行阶段甚至用不着访问表和索引
ValueJSONNameMeaning
SIMPLENone简单查询(不使用UNION或子查询)
PRIMARYNone外层查询,主查询
UNIONNoneUNION第二个语句或后面的语句
DEPENDENTUNIONdependent (true)UNION中第二个语句或后面的语句,独立于外部查询
UNIONRESULTunion_resultUNION的结果
SUBQUERYNone子查询中第一个SELECT
DEPENDENTSUBQUERYdependent (true)子查询中第一个SELECT,独立于外部查询
DERIVEDNone子查询在 FROM子句中
MATERIALIZEDmaterialized_from_subquery物化子查询(不清楚是什么样的查询语句?)
UNCACHEABLESUBQUERYcacheable (false)结果集不能被缓存的子查询,必须重新评估外层查询的每一行
UNCACHEABLEUNIONcacheable (false)UNION中第二个语句或后面的语句属于不可缓存的子查询

possible_key,key 可能用的,和实际被选择的索引


Extra
  1. Using index:用覆盖索引,避免访问表
  2. Using where:检索行后再进行过滤
  3. Using temporary:对结果排序时使用了临时表
  4. Using filesort:使用外部索引排序,而不是按索引的顺序读取表

drop table if exists tabname,tabname2;  
  
create table tabname (    
	id int auto_increment not null primary key,    
	name varchar(10) null,    
	indate datetime null,    
	tid int null,    
	key(tid),    
	key(indate)    
);    

create table tabname2 (    
	id int auto_increment not null primary key,    
	name varchar(10) null,    
	indate datetime null,    
	tid int null,    
	key(tid),    
	key(indate)    
);


drop procedure if exists inserttab;  
 
delimiter //  
create procedure inserttab()  
begin  
	declare i int default 1 ;  
	while i<= 10 do  
		insert into tabname(name,indate,tid) values('love',now(),2),('lucky',now(),3),('passion',now(),4);    
		insert into tabname2(name,indate,tid) values('love',now(),2),('lucky',now(),3);  

	set i= i + 1;  
	end while;  
end;//  

delimiter ;  
  
call inserttab;  
 
select count(*) from test.tabname;  
select count(*) from test.tabname2;  

simple

explain select * from tabname;

PRIMARY / DERIVED

explain select * from (select * from tabname) as a;  

以前会如下

| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra |  
+----+-------------+------------+------+---------------+------+---------+------+------+-------+  
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |  
|  2 | DERIVED     | tabname    | ALL  | NULL          | NULL | NULL    | NULL |    3 |       | 

现在和上面一样

PRIMARY / UNION / UNION RESULT

explain select * from tabname union select * from tabname; 

explain select * from tabname union all select * from tabname; 

PRIMARY / SUBQUERY

explain select * from tabname where id=(select max(id) from tabname);

PRIMARY / DEPENDENT SUBQUERY

explain select * from tabname a where exists(select 1 from tabname b where a.id=b.id);

explain select *,(select name from tabname b where a.id=b.id) from tabname a;

explain select * from tabname where id not in(select id from tabname);

PRIMARY / DEPENDENT UNION / DEPENDENT SUBQUERY / UNION RESULT

explain select * from tabname where id in (select id from tabname union select id from tabname); 


select * from tabname where id in(select id from tabname2 where name='love'); 

理想情况是先执行子查询,返回所有包含 tabname2 中所有符合条件的 id,外层查询则搜索内层的结果集

select group_concat(id) from tabname2 where name='love';  

--内层查询结果:1,3,5,7,9,11,13,15,17,1  
  
select * from tabname where id in(1,3,5,7,9,11,13,15,17,19); 

旧版会将相关的外层表压缩到子查询中。将查询改写成

select * from tabname   
where exists(  
    select * from tabname2   
    where tabname.id=tabname2.id   
    and tabname2.name='love');  

子查询先根据 id 来关联外部表 tabname ,因需 id 字段,所以无法先执行子查询,explain可看是一个相关子查询(DEPENDENT SUBQUERY )

先对  tabname 全表扫描 30 次,然后根据返回的 id 逐个执行子查询,大表性能非常糟糕

解决方法:连接查询

select * from tabname where id in(select id from tabname2 where name='love');  

select tabname. * from tabname inner join tabname2 using(id) where tabname2.name='love'; 

select_type 变为 简单查询。先访问 tabname2 ,因为记录较少,只需该表全表扫描,再查询子查询,省去较多IO