手机版
你好,游客 登录 注册 搜索
背景:
阅读新闻

Explain 执行计划 和 SQL优化

[日期:2019-01-14] 来源:Linux社区  作者:keme [字体: ]

Explain 介绍

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

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

 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语句中

SQL 优化原则

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

    3. 去掉不影响查询结果的表

 

慢查询日志

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

开启方法my.cnf:

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

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

SQL 优化测试

创建一个有索引的表

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结构

 

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

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

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

考虑性能消耗的情况

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

 

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

truncate table students;
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 a select * from students where gender=1;
create temporary table b  select * from students_noindex  where gender=1;

 

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

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

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

  

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

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

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_>

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

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

Linux公社的RSS地址https://www.linuxidc.com/rssFeed.aspx

本文永久更新链接地址https://www.linuxidc.com/Linux/2019-01/156360.htm

linux
相关资讯       SQL优化  EXPLAIN  Explain执行计划 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款