Explain 执行计划和 SQL优化

共计 5003 个字符，预计需要花费 13 分钟才能阅读完成。

在分析查询性能时，考虑 EXPLAIN 关键字同样很管用。EXPLAIN 关键字一般放在 SELECT 查询语句的前面，用于描述 MySQL 如何执行查询操作、以及 MySQL 成功返回结果集需要执行的行数。explain 可以帮助我们分析 select 语句, 让我们知道查询效率低下的原因, 从而改进我们查询, 让查询优化器能够更好的工作，可以帮助选择更好的索引和写出更优化的查询语句。

执行计划用来显示对应语句在 MySQL 中是如何执行的。Explain 语句对 select，delete，update，insert，replace 语句有效。

Explain 执行计划和 SQL 优化

id 列：

表示执行顺序，值越大则优先级越高；值相同则从上而下执行

select_type 列 常见的有：

simple：表示不需要 union 操作或者不包含子查询的简单 select 查询。有连接查询时，外层的查询为 simple，且只有一个
primary：一个需要 union 操作或者含有子查询的 select，位于最外层的单位查询的 select_type 即为 primary。且只有一个
union：union 连接的两个 select 查询，第一个查询是 dervied 派生表，除了第一个表外，第二个以后的表 select_type 都是 union
dependent union：与 union 一样，出现在 union 或 union all 语句中，但是这个查询要受到外部查询的影响
union result：包含 union 的结果集，在 union 和 union all 语句中, 因为它不需要参与查询，所以 id 字段为 null
subquery：除了 from 字句中包含的子查询外，其他地方出现的子查询都可能是 subquery
dependent subquery：与 dependent union 类似，表示这个 subquery 的查询要受到外部表查询的影响
derived：from 字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌 select

table 列
显示的查询表名，如果查询使用了别名，那么这里显示的是别名，如果不涉及对数据表的操作，那么这显示为 null，如果显示为尖括号括起来的 <derived N> 就表示这个是临时表, 后边的 N 就是执行计划中的 id，表示结果来自于这个查询产生。如果是尖括号括起来 <union M,N>，与 <derived N> 类似，也是一个临时表，表示这个结果来自于 union 查询的 id 为 M,N 的结果集

Type 列
：表示访问类型，性能从低到高依次是：ALL->index->range->ref->eq_ref->const，
system->NULL

ALL：Full Table Scan，MySQL 将遍历全表以找到匹配的行
index：Full Index Scan(覆盖索引)index 与 ALL 区别为 index 类型只遍历索引树
range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于 between、<、> 等的查询
unique_subquery：用于 where 中的 in 形式子查询，子查询返回不重复值唯一值
index_subquery：用于 in 形式子查询使用到了辅助索引或者 in 常数列表，子查询可能返回重复值，可以使用索引将子查询去重
ref：非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引和唯一索引的非唯一前缀进行的查找
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描的多表链接操作中
system：当 MySQL 对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于
where 列表中，MySQL 就能将该查询转换为一个常量。System 为表中只有一行数据或者是空表，且只能用于 myisam 和 memory 表。如果是 Innodb 引擎表，type 列在这个情况通常都是 all 或者 index
const: 使用唯一索引或者主键，返回记录一定是 1 行记录的等值 where 条件时，通常 type 是 const。其他数据库也叫做唯一索引扫描
NULL：MySQL 在优化过程中分解语句，执行时甚至不用访问表或索引

possible_keys 列
表示 MySQL 能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但 不一定被查询使用

Key 列 ：
表示MySQL 在查询中实际使用的索引，若没有使用索引，显示为 NULL

key_len 列 ：
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度

Ref 列 ：
如果是使用的常数等值查询，这里会显示 const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为 func

Rows 列 ：
表示 MySQL 根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数，值越大性能越差

Extra 列：

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

Using index：该值表示相应的 select 操作中使用了覆盖索引（Covering Index）
Using where：表示 MySQL 服务器在存储引擎收到 (使用索引) 记录后进行“后过滤”
Using temporary：表示 MySQL 需要使用临时表来存储结果集，常见于排序和分组查询
Using filesort：MySQL 中无法利用索引完成的排序操作称为“文件排序”，常见于 order by 和 group by 语句中

尽可能消除全表扫描，除非表数据量是在万条一下
增加适当的索引能提高查询的速度，但增加索引需要遵循一定的基本规则：
a. 加在 where 条件上
b. 加在表之间 join 的键值上
c. 如果查询范围是少量字段，可以考虑增加覆盖索引（仅走索引）
d. 有多个查询条件时，考虑增加复合索引，并把最常使用的字段放在索引前面
e. 不要将索引加在区别率不高的字段上
f . 字段上增加函数，则字段上的索引用不了，需考虑改变写法
去掉不影响查询结果的表

开启慢查询日志，分日里面执行时间很长语句，可以针对性的对常用语句进行建立索引

开启方法 my.cnf：

slow_query_log= on #开启
slow_query_log_file = /path/mysql-slow.log # 慢查询文件存放位置
long_query_time= 2 #2 秒以上的语句被记录

慢查询日志并不是只是记录的查出 select 语句，dml 对数据语句都会记录

创建一个有索引的表

create table students (
sid int,
sname varchar(64),
gender int,
dept_id int,
primary key(sid)
);

创建一个什么索引都没有的表

create table students_noindex (
sid int,
sname varchar(64),
gender int,
dept_id int
);

利用存储过程, 分别给有索引的表和没有索引的表创建测试数据

# 有索引的表
delimiter //
CREATE PROCEDURE `proc_students`()
Begin
Declare n int default 1;
while n<=500000 do
Insert into students values(n, concat(‘zhang
san’,n),floor(1+rand()*2),floor(1+rand()*4));
Set n=n+1;
End while;
End;
//
delimiter ;

# 没有索引的表
delimiter //
CREATE PROCEDURE `proc_students_noindex`()
Begin
Declare n int default 1;
while n<=500000 do
Insert into students_noindex values(n, concat(‘zhang
san’,n),floor(1+rand()*2),floor(1+rand()*4));
Set n=n+1;
End while;
End;
//
delimiter ;

如果表上所有字段都有索引的情况下，测试对插入性能的影响：

create index idx_sname on students(sname);
create index idx_gender on students(gender);

看看两个表 students，students_noindex 结构

Explain 执行计划和 SQL 优化

分别在两个表插入数据看时间消耗

set autocommit=0;
call proc_students();
commit;

call proc_students_noindex();
commit;

Explain 执行计划和 SQL 优化

没有索引的表插入数据更快

这是 500000 万行的记录插入，有索引的插入时间更久，没有索引的插入更快
用时整体时间都比没有索引的插入数据慢，反应情况来看是 索引建的越多对 SQL 增删改消耗的性能越大 , 因为不仅会修改表数据，还会整理一些索引信息
如果是上亿条的数据记录插入，想想插入时间，还有大表数据迁移在目标表都把索引给删掉，插入数据完成的，在目标表统一建立索引

打开 autocommit 和关闭 autocommit 插入数据的区别

truncate table students;
truncate table students_noindex;
set autocommit=1;
call proc_students();

Explain 执行计划和 SQL 优化

插入数据中途可以在打开一个会话窗口看插入了多少数据
select count(*) from students;

自动提交开启插入 500000 条记录真的要花很长很长时间，而自动提交关闭几十秒的时间都把 500000 行数据插入完了

是因为每条数据插入都会写入磁盘，而关闭 autocommit 是在插入完数据在统一把 500000 条记录 commit; 写入到磁盘

Explain 执行计划和 SQL 优化

我在把原来没有索引的 students_noindex 数据插入回去

Explain 执行计划和 SQL 优化

测试单表在没有索引下全表扫描和走索引情况下的性能对比：

Explain 执行计划和 SQL 优化

select 查询加上 sql_no_cache 查询的时候不使用缓存，突出我的实验结果

上面图片很明显是走索引情况查询速度更快

通过 explain 看下

Explain 执行计划和 SQL 优化

没有索引走的全表扫描

测试通过区别度不高的字段（如 gender）上查询和全表查询的性能对比：

create temporary table a select * from students where gender=1;
create temporary table b select * from students_noindex where gender=1;

Explain 执行计划和 SQL 优化

在区别度很低（gender 上有索引）查询和全表查询性能上差不多

测试通过索引查询表中绝大多数数据和全表查询的性能对比：

select SQL_NO_CACHE count(*) from students where sid>1; # 类似全表查询了
select SQL_NO_CACHE count(*) from students where sid>10000; # 查询表的大多数数据

Explain 执行计划和 SQL 优化

查询时间是一样的。
使用查询条件更可能小的约束过滤范围

测试表链接关联字段走索引和不走索引的性能对比：

create index idx_deptid on students(dept_id);
explain select count(*) from students a inner join dept b on a.dept_>explain select count(*) from students_noindex a inner join dept b on a.dept_>select SQL_NO_CACHE count(*) from students a inner join dept b on a.dept_>select SQL_NO_CACHE count(*) from students_noindex a inner join dept b on a.dept_>

Explain 执行计划和 SQL 优化

在关联字段上加了索引查询时间只用了 0.07s 用时比没有走索引的快了很多很多

总结：
优化手段不只一种，要根据实际情况，很多情况都是以最低成本去处理，例如
有可能加索引就能解决，有可能解决不了，语句的写法的可能有问题（例如语句有函数，表达式），也有可能去改表的结构(例如增加冗余字段), 有可能数据库瓶颈问题，网络情况问题，服务器性能 IO 问题，等等。

：