Explain 执行计划 和 SQL优化

Explain 介绍

在分析查询性能时,考虑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,如果显示为尖括号括起来的就表示这个是临时表,后边的N就是执行计划 中的id,表示结果来自于这个查询产生。如果是尖括号括起来,与类似, 也是一个临时表,表示这个结果来自于union查询的id为M,N的结果集

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

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

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_id=b.id;  # dept_id字段有索引
explain select count(*) from students_noindex a inner join dept b on a.dept_id=b.id; #students_noindex 的表没有任何索引
select SQL_NO_CACHE count(*) from students a inner join dept b on a.dept_id=b.id;
select SQL_NO_CACHE count(*) from students_noindex a inner join dept b on a.dept_id=b.id


Explain 执行计划 和 SQL优化

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


如果本文对你有帮助,别忘记给我个3连 ,点赞,转发,评论,

咱们下期见!答案获取方式:已赞 已评 已关~

学习更多JAVA知识与技巧,关注与私信博主(666)

Explain 执行计划 和 SQL优化

展开阅读全文

页面更新:2024-05-01

标签:尖括号   表链   常量   遍历   字段   语句   索引   性能   条件   操作   计划

1 2 3 4 5

上滑加载更多 ↓
推荐阅读:
友情链接:
更多:

本站资料均由网友自行发布提供,仅用于学习交流。如有版权问题,请与我联系,QQ:4156828  

© CopyRight 2008-2024 All Rights Reserved. Powered By bs178.com 闽ICP备11008920号-3
闽公网安备35020302034844号

Top