Explain 执行计划 和 SQL优化
Explain 介绍
在分析查询性能时，考虑EXPLAIN关键字同样很管用。EXPLAIN关键字一般放在SELECT查询语句的前面，用于描述MySQL如何执行查询操作、以及MySQL成功返回结果集需要执行的行数。explain 可以帮助我们分析 select 语句,让我们知道查询效率低下的原因,从而改进我们查询,让查询优化器能够更好的工作，可以帮助选择更好的索引和写出更优化的查询语句。
执行计划用来显示对应语句在MySQL中是如何执行的。 Explain语句对select，delete，update，insert，replace语句有效。
id列：
表示执行顺序，值越大则优先级越高；值相同则从上而下执行
select_type列常见的有：
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语句中
SQL 优化原则
- 尽可能消除全表扫描，除非表数据量是在万条一下
增加适当的索引能提高查询的速度，但增加索引需要遵循一定的基本规则：
a. 加在where条件上
b. 加在表之间join的键值上
c. 如果查询范围是少量字段，可以考虑增加覆盖索引（仅走索引）
d. 有多个查询条件时，考虑增加复合索引，并把最常使用的字段放在索引前面
e. 不要将索引加在区别率不高的字段上
f . 字段上增加函数，则字段上的索引用不了，需考虑改变写法去掉不影响查询结果的表
慢查询日志
开启慢查询日志，分日里面执行时间很长语句 ， 可以针对性的对常用语句进行建立索引
开启方法my.cnf：
slow_query_log_file = /path/mysql-slow.log # 慢查询文件存放位置
long_query_time= 2 #2秒以上的语句被记录
慢查询日志并不是只是记录的查出select 语句 ，dml 对数据语句都会记录
SQL 优化测试
创建一个有索引的表
sid int,
sname varchar(64),
gender int,
dept_id int,
primary key(sid)
);
创建一个什么索引都没有的表
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_gender on students(gender);
看看两个表students，students_noindex结构
分别在两个表插入数据看时间消耗
call proc_students();
commit;
call proc_students_noindex();
commit;
没有索引的表插入数据更快
考虑性能消耗的情况
这是500000万行的记录插入，有索引的插入时间更久 ，没有索引的插入更快
用时整体时间都比没有索引的插入数据慢 ， 反应情况来看是索引建的越多对SQL增删改消耗的性能越大 ,因为不仅会修改表数据，还会整理一些索引信息
如果是上亿条的数据记录插入，想想插入时间 ， 还有大表数据迁移 在目标表都把索引给删掉，插入数据完成的，在目标表统一建立索引
打开autocommit和关闭autocommit插入数据的区别
truncate table students_noindex;
set autocommit=1;
call proc_students();
插入数据中途可以在打开一个会话窗口看插入了多少数据
select count(*) from students;
自动提交开启插入500000条记录真的要花很长很长时间， 而自动提交关闭 几十秒的时间都把500000行数据插入完了
是因为每条数据插入都会写入磁盘 ，而关闭autocommit 是在插入完数据在统一把500000条记录commit;写入到磁盘
我在把原来没有索引的students_noindex 数据插入回去
测试单表在没有索引下全表扫描和走索引情况下的性能对比：
select 查询加上sql_no_cache 查询的时候不使用缓存 ，突出我的实验结果
上面图片很明显是 走索引情况查询速度更快
通过explain 看下
没有索引走的全表扫描
测试通过区别度不高的字段（如gender）上查询和全表查询的性能对比：
create temporary table b select * from students_noindex where gender=1;
在区别度很低 （gender上有索引）查询和全表查询 性能上差不多
测试通过索引查询表中绝大多数数据和全表查询的性能对比：
select SQL_NO_CACHE count(*) from students where sid>10000; # 查询表的大多数数据
查询时间是一样的 。
使用查询条件更可能小的约束过滤范围
测试表链接关联字段走索引和不走索引的性能对比：
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_>
在关联字段上加了索引 查询时间只用了0.07s 用时 比没有走索引的快了很多很多
总结：
优化手段不只一种 ，要根据实际情况，很多情况都是以最低成本去处理， 例如
有可能加索引就能解决， 有可能解决不了，语句的写法的可能有问题（例如语句有函数，表达式），也有可能去改表的结构(例如增加冗余字段),有可能数据库瓶颈问题， 网络情况问题，服务器性能IO 问题，等等。