MySQL----explain/trace
如何使用慢查询快速定位执行慢的 SQL？
慢查询可以帮我们找到执行慢的 SQL
- 查看慢查询是否已经开启
show variables like '%slow_query_log';
- 我们能看到slow_query_log=OFF，也就是说慢查询日志此时是关上的。我们可以把慢查询日志打开，注意设置变量值的时候需要使用 global，否则会报错：
set global slow_query_log='ON';
- 然后我们再来查看下慢查询日志是否开启，以及慢查询日志文件的位置
show variables like '%slow_query_log%';
- 如果我们想把时间缩短，比如设置为 3 秒，可以这样设置：
set global long_query_time = 3;
mysqldumpslow 工具
MySQL 自带的 mysqldumpslow 工具统计慢查询日志（这个工具是个 Perl 脚本，你需要先安装好 Perl）
perl下载：http://www.activestate.com/activeperl/downloads
mysqldumpslow 命令的具体参数如下：
-s：采用 order 排序的方式，排序方式可以有以下几种。分别是 c（访问次数）、t（查询时间）、l（锁定时间）、r（返回记录）、ac（平均查询次数）、al（平均锁定时间）、ar（平均返回记录数）和 at（平均查询时间）。其中 at 为默认排序方式。
-t：返回前 N 条数据 。
-g：后面可以是正则表达式，对大小写不敏感。
explain：
id
select 执行顺序
1. 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
2. id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
select_type
(1) SIMPLE(简单SELECT，不使用UNION或子查询等)
(2) PRIMARY(复杂查询最外面的select)
(3) UNION(UNION中的第二个或后面的SELECT语句)
(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
(5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
(6) SUBQUERY(包含在 select 中的子查询(不在 from 子句中)，就是select (select * from ) from xx，中第二个select，子查询from前面的查询)
(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
(8) DERIVED(派生表的SELECT, FROM后面的子查询)
(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
table
显示这一步所访问数据库中表名称
type
ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好)
ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
index: Full Index Scan，index与ALL区别为index类型只遍历索引树
range:只检索给定范围的行，使用一个索引来选择行
ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
possible_keys
该表可以利用的索引
示例
#关闭mysql5.7新特性对衍生表的合并优化
set session optimizer_switch='derived_merge=off'; //设置on恢复
创建表和插入数据
drop table if exists actor;
create table actor(
id int(11) not null,
name varchar(20) default null,
update_time datetime default null,
primary key (id)
)ENGINE=InnoDB default charset utf8;
drop table if exists film;
create table film(
id int(11) not null auto_increment,
name varchar(20) default null,
primary key (id),
key idx_name (name)
)ENGINE=InnoDB default charset utf8;
DROP TABLE IF EXISTs film_actor;
CREATE TABLE film_actor(
id int(11) NOT NULL,
film_id int(11)NOT NULL,
actor_id int(11) NOT NULL,
remark varchar(255) DEFAULT NULL,
PRIMARY KEY (id),
KEY idx_film_actor_id(film_id,actor_id)
)ENGINE=InnoDB default charset utf8;
insert into actor(id, name, update_time) VALUES (1,'小明',current_date);
insert into actor(id, name, update_time) VALUES (2,'小红',current_date);
insert into actor(id, name, update_time) VALUES (3,'小花',current_date);
insert into film(name) values ('film1'),('film2'),('film2');
INSERT INTO film_actor(id,film_id,actor_id) values (2,1,2),(3,2,1),(1,1,1);
select_type
primary、subquery 和 derived 类型
explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;
select_type
union
explain select 1 union all select 1;
type
sytem,const
system是const的特例，表里只有一条元组匹配时为 system
explain extended select * from (select * from film where id = 1) tmp;
type
eq_ref，关联表才会出现这种连接
primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。
explain select * from film_actor left join film on film_actor.film_id = film.id;
- film表用film.id和其他表关联，film_id是唯一的（primary key），所以type是eq_ref
- 如果film_actor.film_id = film.name;那么type就是index，因为name不唯一，就要进行索引扫描
type
ref
相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
explain select * from film where name = 'film1';
index
range
范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。
explain select * from actor where id > 1;
type
index
扫描全表索引，这通常比ALL快一些。
explain select * from film;
type
ALL
即全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了
explain select * from actor;
possible_keys
explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。 如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。
key
这一列显示mysql实际采用哪个索引来优化对该表的访问。如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。
key_len
这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。
char(n)：n字节长度 varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n+2 tinyint：1字节 smallint：2字节 int：4字节 bigint：8字节 date：3字节 timestamp：4字节 datetime：8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。
Extra
Using index
使用覆盖索引，select中的字段都在索引中
Extra
Using where
查询的列未被索引覆盖，where中的字段不在索引中
Extra
Using index condition
查询的列不完全被索引覆盖，where条件中是一个前导列的范围；
explain select * from film_actor where film_id > 1;
Extra
Using temporary
mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。
explain select distinct name from actor;
如果将distict后面的name建立了索引
Extra
Using filesort
将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。
explain select * from actor order by name;
如果将order by后面的字段加上索引
trace：
