MySQL关键字explain

MySQL关键字explain

id：唯一标识

• 每个select语句都会自动分配一个唯一标识符
• id越大，优先级越高
• id为nulll，表示这是一个结果集，不需要使用它来进行查询

select_type：查询类型

• simple:表示不需要union操作或者不包含子查询的简单select查询

  EXPLAIN SELECT * FROM tuser;
  

• primary：一个需要union操作或者含有子查询的select,位于最外层的查询。

  EXPLAIN SELECT (SELECT NAME FROM tuser) FROM tuser;
  

• subquery：除from字句中包含的子查询外，其它地方出现的子查询都可能是subquery

  EXPLAIN SELECT * FROM tuser WHERE id = (SELECT MAX(id) FROM tuser);
  

• dependent subquery：表示这个subquery的查询要受到外表查询的影响。

  EXPLAIN SELECT id,NAME,(SELECT id FROM tdep a WHERE a.id = b.dep) FROM tuser b;
  

• union：连接两个select 查询，第一个查询是primary，第二个以后的表select_type都是union

  EXPLAIN 
  SELECT * FROM tuser 
  	WHERE sex='1' 
  UNION 
  SELECT * FROM tuser 
  	WHERE sex = '2';
  

• dependent union：出现在union或union all语句中，但是这个查询要受到外部查询的影响。

  EXPLAIN SELECT * FROM tuser 
  	WHERE sex 
  	IN(SELECT sex FROM tuser 
         		WHERE sex = '1' 
         UNION 
         SELECT sex FROM tuser 
         		WHERE sex = '2');
  

• union result：包含union的结果集，在union和union all语句中，因为它不需要参与查询，所以id字段为null

  EXPLAIN SELECT * FROM tuser 
  	WHERE sex 
  	IN(SELECT sex FROM tuser 
         		WHERE sex = '1' 
         UNION 
         SELECT sex FROM tuser 
         		WHERE sex = '2');
  

• derived：from字句中出现的子查询，也叫派生表，其他数据库中可能叫内联视图或者嵌套select

  EXPLAIN SELECT * FROM (SELECT * FROM tuser WHERE sex = '1') b;
  

table：显示查询表名

• 如果使用了别名，就显示别名
• 如果不涉及数据表，就显示null.
• 显示尖括号的表示临时表

type：索引类型

性能关系:All < index < range < index_merge < ref < eq_ref < const < system

• system：表示只有一行数据或者是空表

  EXPLAIN SELECT * FROM (SELECT * FROM tuser WHERE id = 1) a;
  

• const：使用唯一索引或者主键

  EXPLAIN SELECT * FROM tuser WHERE id = 1;
  EXPLAIN SELECT * FROM tuser WHERE loginname = 'wenhua';
  

• eq_ref：连接字段主键或者唯一性索引

  • 此类型通常出现在多表的join查询中，表示对于前表的每一个结果，都只能匹配到后表的一行结果，并且查询的比较操作通常是‘=’，查询效率较高

EXPLAIN SELECT a.id FROM tuser a 
	LEFT JOIN tdep b 
	ON a.`dep`=b.`id`;
	
EXPLAIN SELECT tuser.`name`,tdep.`name`,age 
	FROM tuser,tdep 
		WHERE tuser.`name` LIKE 'deng%' 
			AND  sex ='男' 
			AND tuser.`dep` = tdep.`id`;

• ref：非唯一性索引，使用等值=查询非主键。

--非唯一索引
EXPLAIN SELECT * FROM tuser WHERE sex = 1;
-- 等值非主键连接
EXPLAIN SELECT a.`age` FROM tuser a LEFT JOIN tdep b ON a.`name` = b.`name`;
-- 最左前缀
EXPLAIN SELECT * FROM tuser WHERE NAME = 'dengwenhua';
EXPLAIN SELECT * FROM tuser WHERE NAME = 'deng%';

• fulltext：全文索引检索。优先级最高

EXPLAIN SELECT * FROM taddr WHERE MATCH(addr) AGAINST('bei');

• ref_or_null

  • 与ref方法类似，只是增加了null值得比较。

• unique_subquery

  • 用于where中的int形势子查询，子查询返回不重复值唯一值

• index_subquery

  • 用于in形式子查询使用到的辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。

• range:索引范围扫描

  • 常见于使用>,<,is null,between,in,like等运算符中

EXPLAIN SELECT id FROM tuser WHERE id > 2;
-- like前缀索引，不能使用%wen
EXPLAIN SELECT id FROM tuser WHERE NAME LIKE 'wen%';

• index_merge

  • 表示查询使用了两个以上的索引，最后去交集或者并集。

• index

  • 条件是出现在索引树中的节点。可能没有完全匹配索引。

-- 单索引
EXPLAIN SELECT loginname FROM tuser;
-- 组合索引
EXPLAIN SELECT age FROM tuser;

• all

  • 全表扫描，在server层进行过滤

EXPLAIN SELECT * FROM tuser;

possible_keys：应用索引

• 此次查询中可能选用的索引，一个或多个

key：索引名称

• 查询真正使用到的索引

key_len：索引长度

• 字符串
  • char(n): n 字节长度
  • varchar(n): 如果是 utf8 编码, 则是 3 n + 2字节; 如果是 utf8mb4 编码, 则是 4 n + 2 字节.
• 数值类型:
  • TINYINT: 1字节
  • SMALLINT: 2字节
  • MEDIUMINT: 3字节
  • INT: 4字节
  • BIGINT: 8字节
• 时间类型
  • DATE: 3字节
  • TIMESTAMP: 4字节
  • DATETIME: 8字节
• 字段属性: NULL 属性 占用一个字节. 如果一个字段是 NOT NULL 的, 则没有此属性.

ref：查询方式

• 如果是常数等值查询，会显示const
• 如果是连接查询，显示驱动表的关联字段
• 如果是条件表达式或者函数，显示func

rows：执行计划中估算的扫描行数。

• InnoDB不是精确值，MyISAM是精确值，主要原因是InnoDB里面使用了MVCC并发机制
• 非常直观显示SQL的效率好坏，原则上rows越少越好

Extra：额外信息

• using temporary:查询效率不高

  • 表示使用了临时存储中间结果

• 一般在order by 和group by是使用临时表

EXPLAIN SELECT DISTINCT a.id 
	FROM tuser a,tdep b 
	WHERE a.`dep` = b.`id`;

• no tables used：不带from字句的查询或者from dual查询

  • 使用not in()形式子查询或者not exists运算符的连接查询，叫反连接

    （一般连接查询先查内表，再查询外表，反连接就是先查询外表，在查询内表)。

EXPLAIN SELECT 1 = 0;
EXPLAIN SELECT 1 = 1;
EXPLAIN SELECT 1 = '1';

• using filesort：CPU资源消耗大

  • 由于排序而无法使用索引时。常见于order by 和group by 语句中

EXPLAIN SELECT * FROM tuser ORDER BY address;

• using index："覆盖索引扫描"查询性能不错

  • 查询时不需要回表查询，直接通过索引就可以获取查询的数据。

EXPLAIN SELECT NAME,age,sex FROM tuser;

• using where：表示存储引擎返回的记录并不是所有的都满足查询条件即要回表查询。

-- 查询条件无索引
EXPLAIN SELECT * FROM tuser WHERE address = '西安';
-- 索引失效
EXPLAIN SELECT * FROM tuser WHERE age = 21;
EXPLAIN SELECT * FROM tuser WHERE id IN(1,2);