一、Explain

1.是什么(查看执行计划)

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是 如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈
官网介绍 https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

2.能干嘛

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

3.怎么玩

Explain + SQL语句

4.建表语句

 CREATE TABLE t1(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));CREATE TABLE t2(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));CREATE TABLE t3(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));CREATE TABLE t4(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000))); INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000)));  INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000)));    INSERT INTO t4(content) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)));

二、索引优化分析_explain_各字段解释

1.id★

1.select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序
2.三种情况
1)id相同，执行顺序由上至下
Explain select * from t1,t2,t3;

2)id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
explain select t1.id from t1 where t1.id = (select t2.id from t2 where t2.id = (select t3.id from t3 where t3.content=‘t3_897’));

• id相同，不同，同时存在;
• id如果相同，可以认为是一组，从上往下顺序执行；
• 在所有组中，id值越大，优先级越高，越先执行;
• 关注点：id号每个号码，表示一趟独立的查询。一个sql的查询趟数越少越好。

11.2.select_type
1.有哪些

2.查询的类型，主要是用于区别 普通查询、联合查询、子查询等的复杂查询
1)SIMPLE

• 简单的 select 查询,查询中不包含子查询或者UNION
  EXPLAIN SELECT * FROM t1;
  

2)PRIMARY

• 查询中若包含任何复杂的子部分，最外层查询则被标记为primary
  EXPLAIN select t1.id from t1 where t1.id = (select t2.id from t2 where t2.id = (select t3.id from t3 where t3.content=‘t3_897’));
  

3)SUBQUERY

• 在SELECT或WHERE列表中包含了子查询
  

4)DEPENDENT SUBQUERY

• 在SELECT或WHERE列表中包含了子查询,子查询基于外层
  EXPLAIN SELECT * FROM t3 WHERE id = ( SELECT id FROM t2 WHERE content = t3.content);
  

5)UNCACHEABLE SUBQUREY

• 表示这个subquery的查询要受到外部系统变量的影响
  EXPLAIN SELECT * FROM t3 WHERE id = ( SELECT id FROM t2 WHERE content = @@character_set_server);

6)UNION

• 若第二个SELECT出现在UNION之后，则被标记为UNION； 若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED
  EXPLAIN SELECT * FROM (SELECT * FROM t1 UNION SELECT * FROM t2) aa;
  

7)UNION RESULT

• 从UNION表获取结果的SELECT
  

3.table

• 显示这一行的数据是关于哪张表的

4.partitions

• 代表分区表中的命中情况，非分区表，该项为null
• https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html

5.type★

1.访问类型排列
type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 

system>const>eq_ref>ref>range>index>ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。
2.类型介绍

5.1.system

表仅有一行记录，必须是系统表，这是const类型的特例，查询起来非常迅速。
explain SELECT * from mysql.proxies_priv WHERE User=‘root’;

5.2.const

explain select * from t1 where id = 1;

• 表示通过索引一次就找到了,const用于primary key或者unique索引。
• 因为只匹配一行数据，所以很快 如将主键置于where列表中，MySQL就能将该查询转换为一个常量

5.3.eq_ref

explain select * from t1,t2 where t1.id = t2.id;

• 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见主键或唯一索引扫描

5.4.ref *

create index idx_content on t1(content);
EXPLAIN SELECT * FROM t1, t2 WHERE t1.content = t2.content;

• 非唯一性索引扫描，返回匹配某个单独值的所有行. 本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而， 它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

5.5.range *

explain select * from t2 where id >1 and id <5;

• 只检索给定范围的行,使用一个索引来选择行。
• key 列显示使用了哪个索引 一般就是在你的where语句中出现了between、<、>、in等的查询 这种范围索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

5.6.Index

explain select id from t1;

• 出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组
• Full Index Scan，index与ALL区别为index类型只遍历索引树。
• 这通常比ALL快，因为索引文件通常比数据文件小。
• 也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的

5.7.all

explain select * from t2;

• Full Table Scan，将遍历全表以找到匹配的行

6.possible_keys

• 显示可能应用在这张表中的索引，一个或多个。 查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

7.key

• 实际使用的索引。如果为NULL，则没有使用索引
  

8.key_len ★

key_len表示索引使用的字节数，根据这个值可以判断索引的使用情况，特别是在组合索引的时候，判断该索引有多少部分被使用到非常重要。值越大越好。

创建索引，并查询表结构

create index idx_age_deptId_name on emp(age,deptId,name); 

如何计算
key_len=age的字节长度+deptid的字节长度+name的字节长度=(4+1) + (4+1) +(20*3+2+1)=73

key_len的长度计算公式：

varchr(10)变长字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
varchr(10)变长字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)
char(10)固定字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)
char(10)固定字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)

9.ref

显示将哪些列或常量与键列中命名的索引进行比较，以从表中选择行。
explain select * from t1,t2 where t1.id = t2.id;

10.rows★

• rows列显示MySQL认为它执行查询时必须检查的行数。值越小越好

11.filtered

• 这个字段表示存储引擎返回的数据在mysql server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数

12.Extra★

• 包含不适合在其他列中显示,但十分重要的额外信息

12.1.Using filesort *

• 出现filesort的情况：order by 没有用上索引。
• explain select id,empno,name,age from emp where deptid=100 order by name limit 10;

• 优化后（给deptid和name字段建立复合索引），去掉filesort
  create index idx_deptid_name on emp (deptid,name);
  
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
• 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。 MySQL中无法利用索引完成的排序操作称为“文件排序”

12.2.Using temporary *

• 出现Using temporary情况：分组没有用上索引。产生临时表。注意：分组操作是需要先排序后分组的。所以，也会出现Using filesort。

• 优化前存在 using temporary 和 using filesort

explain select name,count(*) from emp where deptid=100 group by name limit 10;

• 优化后（给deptno和ename建立复合索引）去掉using temporary 和 using filesort，性能发生明显变化：
  create index idx_deptno_ename on emp (deptno,ename);
  
• 使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询group by。

12.3.Using index *

表示使用了覆盖索引 [content是一个索引]

• 如果同时出现using where，表明索引被用来执行索引键值的查找;
  
• 如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
  

12.4.Using where

• 表明使用了where过滤！

12.5.using join buffer *

如果有它则表明关联字段没有使用索引！

• 使用了连接缓存

12.6.impossible where

• where 后面筛选条件有错误！
  

中英文歌词