二、mysql——Explain工具介绍和查看mysql运行时间

一、myql索引底层数据结构与算法
二、mysql——Explain工具介绍
三、mysql——索引最佳实践
四、mysql索引优化实战
五、mysql——常见sql语句优化
六、mysql索引优化实战二
七、mysql事务隔离机制和锁机制

使用的数据库脚本

DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (
`id` int(11) NOT NULL,
`name` varchar(45) DEFAULT NULL,
 `update_time` datetime DEFAULT NULL,
 PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017‐12‐22
15:27:18'), (2,'b','2017‐12‐22 15:27:18'), (3,'c','2017‐12‐22 15:27:18');

 DROP TABLE IF EXISTS `film`;
 CREATE TABLE `film` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');

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 `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);

explain中的列

EXPLAIN SELECT * from actor;

1、id 列

id 列的编号是select的序列号，有几个select就有几个id，并且id的顺序是按select出现的顺序增长的。id越大优先级越高，id相同则从上往下执行。

2、select_type

表示是简单查询还是复杂查询。
（1）simple：简单查询
（2）primary：负载查询中最外层的select；
（3）subquery：包含在select中的子查询；
（4）derived：包含在from子句中的子查询。Mysql会将结果存放在一个临时表中，也称为派生表。

set SESSION OPTIMIZER_SWITCH=`derived_merge=off`;# 关闭mysql对衍生表的合并优化
explain SELECT  (SELECT 1 from actor WHERE id=1) from (SELECT * from film where id=1) der;

set SESSION OPTIMIZER_SWITCH=`derived_merge=on`;# 打开mysql对衍生表的合并优化

（5）union：在union中的第二个和随后的select；

EXPLAIN select 1 union all SELECT 1;

3、table列

这一列表示explain的一行正在访问哪个表。
当from字句有子查询时，table列时格式，表示当前查询依赖id=N的查询，于是先执行id=N的查询；
当有union时，UNION RESULT的table列的值为<union 1，2>，1和2表示参加union的select的行id；

4、type

表示关联类型或访问类型；即mysql如何查找表中的行。
依次最优到最差：system>const>eq_ref>ref>range>index>ALL
一般来说sql语句需要达到range级别，最好达到ref。
（1）NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问索引或者表。

EXPLAIN SELECT min(id) from film;

（2）const、system：mysql能对查询的某部分队形优化并将其转化为一个常量。用于primary key或者unique kye的所有列与常数比较时，所以表最多有一行匹配，读取1次，速度比较快，system是const的特例，表里只有一条元组匹配时为system。

(3) eq_ref: primary key或unique key索引的所有部分被连接使用，最多只会返回一条符合条件的记录。

EXPLAIN SELECT * from film_actor LEFT JOIN film on film_actor.film_id=film.id;

(4) ref:相比eq_ref，不实用唯一索引，而是使用普通或者唯一性索引的部分前缀，，可能会找到多个符合条件的行。

简单select查询，name是普通索引(非唯一性索引)

EXPLAIN SELECT * from film where name = 'film1';

关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用了film_actor的左边钱对film_id部分。

EXPLAIN SELECT film_id from film LEFT JOIN film_actor on film.id = film_actor.film_id;

(5)range:范围扫描通常出现在in(),between,>,<，>=中，使用一个索引来给定范围的行。

EXPLAIN SELECT * from actor where id>1;

（6）index：扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点便利扫描，这种查询一般使用覆盖索引，比all快一些。

EXPLAIN SELECT * from film

(7)all:即全表扫描，扫描你的聚簇索引的所有叶子节点，通常这种情况需要优化一下。

Extra列

(1) Using temporary:mysql需要创建一张临时表来处理查询。出现这种情况，需要优化，首先想到利用索引优化。

EXPLAIN SELECT distinct name from actor;

film.name建立了idx_name索引，此时查询时extra时using index，没用临时表。

EXPLAIN SELECT distinct name from film;

（2）using filesort：将用外部排序而不是索引排序，考虑用索引优化。
1、actor.name未创建索引，会浏览整个表，保存关键排序字段，然后排序name并检索记录。

EXPLAIN SELECT  * from actor ORDER BY name;

查看mysql语句运行时间