EXPLAIN讲解

1.使用

EXPLAIN 说明

EXPLAIN  select 
*
from
  `lenyar_bate`.`tbl_user_info` 
  order by school_id

2.属性说明

1.id

id相同时为同一组，顺序从上到下，id越大优先级越高

2.select_type

查询的类型
2.1SIMPLE(简单的)
简单的查询
2.2PRIMART
查询最外层的select
如一个A查询 嵌套 B子查询那么A查询的类型就是PRIMART ，B子查询的类型为UNION
2.3UNION(联合)
处于内层的查询语句
2.4DEPENDENT UNION(从属 联合)
UNION操作时，查询处于内层select
2.5UNION RESULT()
UNION 操作的结果
2.6SUBQUERY(子查询)
子查询中首个查询
2.7DEPEDENT SUBQUERY
子查询中首个查询 (依赖外部的查询)
2.8DERIVED（派生的）
被驱动的SELECT子查询（子查询位于from子句）
2.9MATERIALIZED（实现）
指将指定表物化为临时表
3.0UNCACHEABLE SUBQUERY（）
无法缓存子查询的结果，每次都需要计算
3.1UNCACHEABLE UNION（）
UNION操作中，内存的子查询无法被物化

3.table

说明的表

4.partitions

匹配的分区，记录与查询匹配的分区。值为NULL表示为非分区表。(5.7才有)

5.type

要对type优化的前提是你要有索引
准备

CREATE TABLE course (
		cid INT (3),
		cname VARCHAR (20),
		tid INT (3)
	);

INSERT INTO `lvdi`.`course` (`cid`, `cname`, `tid`)
VALUES
	('1', 'java', '1'),
	('2', 'php天下第一', '1'),
	(
		'3',
		'python我也是天下第一',
		'2'
	);

CREATE TABLE teacher (
	tid INT (3),
	tname VARCHAR (20),
	tcid INT (3)
);

INSERT INTO `lvdi`.`teacher` (`tid`, `tname`, `tcid`)
VALUES
	('1', '小王', '1'),
	('2', '中王', '2');

CREATE TABLE teacherCard (
	tcid INT (3),
	tcdesc VARCHAR (200)
) INSERT INTO `lvdi`.`teacherCard` (`tcid`, `tcdesc`)
VALUES
	('1', '=。='),
	('1', '=。=');



CREATE TABLE test01
(
  tid INT(3),
  tname VARCHAR(20)
)
INSERT INTO test01 VALUES(1,'a')
ALTER TABLE test01 ADD CONSTRAINT tid_pk PRIMARY KEY(tid)

5.1NULL
MySQL不访问任何表，索引，直接返回结果
5.2system
只有一行数据的系统表（系统自带的表 =。=）， 或 衍生表只有一条数据的主查询

EXPLAIN SELECT * FROM (SELECT * FROM test01 WHERE tid=1) t 

不知道为什么我出现的是const

5.3const
仅仅能查询到一条数据的SQL，用于primary key 或者unique

EXPLAIN SELECT * FROM test01 WHERE tid=1

5.4eq_ref
简单的select查询是不会出现这种type的，关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描(表与表之间的数据 必须 一一对应 )

ALTER TABLE teacherCard ADD CONSTRAINT pk_tcid PRIMARY KEY(tcid)
ALTER TABLE teacher ADD CONSTRAINT uk_tcid UNIQUE INDEX(tcid) 

SHOW INDEX FROM teacherCard
EXPLAIN SELECT t.tcid FROM teacher t,teacherCard tc WHERE t.tcid=tc.tcid;

5.5ref
非唯一性索引索引扫描，返回匹配某个单独值的所有行

INSERT INTO `lvdi`.`teacher` (`tid`, `tname`, `tcid`) VALUES ('3', '中王', '2')
ALTER TABLE  teacher ADD INDEX idx_tname (tname)
EXPLAIN  SELECT tname FROM teacher WHERE tname='中王'

5.6range（范围）
范围扫描，基于索引做扫描，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中

ALTER TABLE teacher ADD INDEX idx_tid (tid);
EXPLAIN SELECT * FROM teacher  WHERE tid IN (2,3)

我k为什么 type 是all？
in 是否 导致索引失效和 in中数据的个数与表中数据的比例有关系

可以 强制 使用索引

EXPLAIN  SELECT * FROM teacher FORCE INDEX(`idx_tid`) WHERE tid IN ( 2,3)

5.7Index
index 与 ALL的区别为 index 类型只是遍历了索引树， 通常比ALL 快， ALL 是遍历数据文件。

EXPLAIN SELECT tid FROM teacher

5.8all
将遍历全表以找到匹配的行

EXPLAIN SELECT cid FROM course

6.possible_keys(可能的_键)

可能会使用的键，注意只是可能

7.key

显示MySQL实际决定使用的键

8.key_len

显示使用到的索引长度

9.ref

表之间的引用

10.rows

优化器预估的行数

11.extra

展示额外的信息

Using index： 优化器进行了覆盖索引操作（从辅助索引中就可以得到查询的记录，而不是要查询聚集索引中的记录），是性能高的表现

explain select  tid from teacher

Using where：查询的列未被索引覆盖，需要回表查询
cid是没有加索引的

EXPLAIN SELECT cid FROM course where cid=1

Using where Using index：查询的列被索引覆盖，并且where筛选条件是索引列之一但是不是索引的前导列，意味着无法直接通过索引查找来查询到符合条件的数据

EXPLAIN  SELECT tid,tname FROM teacher FORCE INDEX(idxs_tid_tname) WHERE tname='小王'

Using index condition：在5.6版本后加入的新特性，与Using where类似，查询的列不完全被索引覆盖，where条件中是一个前导列的范围；

EXPLAIN SELECT tid FROM teacher WHERE tid=1 ORDER BY tname

Using filesort：需要额外的一次排序（性能消耗大）

EXPLAIN SELECT tid FROM teacher WHERE tid=1 ORDER BY tname

说以下内容要先了解一下SQL查询执行的流程

where 根据 tid的索引 查询 出来了 然后又根据tname 排序 因为 索引本来就是 有顺序的 所有 “额外的一次排序”了

EXPLAIN SELECT tid FROM teacher WHERE tid=1 ORDER BY tid

可以通过加联合索引解决(索引的创建要的顺序和 列的顺序一样 如：idxs_tid_tname(tid,tname) 与 WHERE tid=1 ORDER BY tname 否则失效)

alter table teacher add index idxs_tid_tname(tid,tname);
EXPLAIN SELECT tid FROM teacher WHERE tid=1 ORDER BY tname

如果 例没有索引

EXPLAIN SELECT cid FROM course order by cid

using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于 order by ，distinct和 group by； （性能消耗大）

explain select  tid from teacher where tid in(1,2,3)group by tname