文档章节

SQL性能优化

 一字见心
发布于 2019/12/07 00:23
字数 2350
阅读 9
收藏 0

SQL性能优化 一、SQL的执行顺序

顺序:FROM——ON——JOIN——WHERE——GROUP BY——SUM、COUNT——HAVING——SELECT——DISTINCT——ORDER BY——LIMIT

与写SQL的顺序不同,SQL的执行顺序并不是从select开始,而是从from开始

1、FROM:先去获取from里面的表,拿到对应的数据,生成虚拟表1。

2、ON:对虚拟表1应用ON筛选,符合条件的数据生成虚拟表2。

3、JOIN:根据JOIN的类型去执行相对应的操作,获取对应的数据,生成虚拟表3。

4、WHERE:对虚拟表3的数据进行条件过滤,符合记录的数据生成虚拟表4。

5、GROUP BY:根据group by中的列,对虚拟表4进行数据分组操作,生成虚拟表5。

6、CUBE|ROLLUP(聚合函数使用):主要是使用相关的聚合函数,生成虚拟表6。

7、HAVING:对虚拟表6的数据过滤,生成虚拟表7,这个过滤是在where中无法完成的,同时count(expr)返回不为NULL的行数,而count(1)和count(*)是会返回包括NULL在内的行数。

8、SELECT:选择指定的列,生成虚拟表8。

9、DISTINCT:数据去重,生成虚拟表9。

10、ORDER BY:对虚拟表9中的数据进行指定列的排序,生成虚拟表10。

11、LIMIT:取出指定行的记录,生成虚拟表11,返回给查询用户。

以上是SQL各关键词的执行顺序,如果在一条SQL语句里面你没有用到某个关键词那就不会被执行了。理解SQL的逻辑执行顺序对我们在实际写SQL的过程中也会有帮助的。

二、执行计划——EXPLAIN

执行计划,是SQL在数据库中执行时的表现情况,通常用于SQL性能分析,优化等场景。在MySQL使用 explain 关键字来查看SQL的执行计划。

基本的语法:EXPLAIN(select * from table)

在常规SQL语句前面加上EXPLAIN即可

运行结果:

image 参数解释:

1、id:数字越大越先执行,一样大则从上往下执行,如果为NULL则表示是结果集,不需要用来查询。

2、select_type:

simple:不需要union的操作或者是不包含子查询的简单select语句。

primary:需要union操作或者含有子查询的select语句。

union:连接两个select查询,第一个查询是dervied派生表,第二个及后面的表select_type都是union。

dependent union:与union一样,出现在union 或union all语句中,但是这个查询要受到外部查询的影响。

union result:包含union的结果集。

subquery:除了from字句中包含的子查询外,其他地方出现的子查询都可能是subquery。

dependent subquery:与dependent union类似,表示这个subquery的查询要受到外部表查询的影响。

derived:from字句中出现的子查询,也叫做派生表,其他数据库中可能叫做内联视图或嵌套select。

3、table

表名,如果是用了别名,则显示别名

4、type

依次从好到差:system,const,eq_ref,ref,fulltext,ref_or_null,unique_subquery,index_subquery,range,index_merge,index,ALL,除了all之外,其他的type都可以使用到索引,除了index_merge之外,其他的type只可以用到一个索引。

system:表中只有一行数据或者是空表。

const:使用唯一索引或者主键,返回记录一定是1行记录的等值where条件时,通常type是const。

eq_ref:出现在要连接过个表的查询计划中,驱动表只返回一行数据,且这行数据是第二个表的主键或者唯一索引,且必须为not null,唯一索引和主键是多列时,只有所有的列都用作比较时才会出现eq_ref。

ref:不像eq_ref那样要求连接顺序,也没有主键和唯一索引的要求,只要使用相等条件检索时就可能出现,常见与辅助索引的等值查找。

fulltext:全文索引检索,要注意,全文索引的优先级很高,若全文索引和普通索引同时存在时,mysql不管代价,优先选择使用全文索引。

ref_or_null:与ref方法类似,只是增加了null值的比较。实际用的不多。

unique_subquery:用于where中的in形式子查询,子查询返回不重复值唯一值。

index_subquery:用于in形式子查询使用到了辅助索引或者in常数列表,子查询可能返回重复值,可以使用索引将子查询去重。

range:索引范围扫描,常见于使用>,<,is null,between ,in ,like等运算符的查询中。

index_merge:表示查询使用了两个以上的索引,最后取交集或者并集,常见and ,or的条件使用了不同的索引。

index:索引全表扫描,把索引从头到尾扫一遍,常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。

all:这个就是全表扫描数据文件,然后再在server层进行过滤返回符合要求的记录。

5、possible_keys:查询可能使用到的索引。

6、key:查询真正使用到的索引。

7、key_len:用于处理查询的索引长度。

8、ref:常数等值查询显示const,连接查询则显示表的关联字段。

9、rows:执行计划中估算的扫描行数,不是精确值。

10、filtered:表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例。

11、extra:该字段信息较多,这里就不一一叙述了。

在实际的使用过程中我们需要重点去关注type、key、key_len、rows、extra这几个参数,type要努力优化到range级别,all要尽量少的出现,在查询的过程中要尽量使用索引,提高效率,在extra里面出现Using filesort, Using temporary是不太好的,要去优化提高性能。

三、优化TIPS

1、尽量少用select *

因为会增加不必要的消耗,select 后面直接加上需要的字段名。

2、IN 包含的值不应过多

IN本身这个操作消耗就比较高,如果IN里面是连续的数值,则可以用between代替,IN里面的字段如果是添加了索引,效率还是可以的,目前测试一万以内还是可以,但是超过了结果可能会有点爆炸,不要问我为什么

3、in和exists、not in 和 not exists

exists以外层表为驱动表,先被访问,适合于外表小而内表大的情况。

in则是先执行子查询,适合外表大而内表小的情况,

一般情况是不推荐使用not in,因为效率非常低,

eg:

1)select * from table_a where table_a.id not in (select table_b.id from table_b)

2)select * from table_a left join table_b on table_a.id = table_b.id where table_b.id is null

语句2的效率是要高于语句1的,SQL的结果是获取到在table_a中存在但是table_b中不存在的数据,如果直接用not in是不走索引的,而且在table_b比较大的时候效率会非常低,实际工作中我试了一下直接not in,然后数据达到一万条的时候大概需要150S左右才能查出数据(感谢DBA和运维不杀之恩),我采取的方法是,先查出两个表的交集,这样得到的表会小很多,而且是用的in,效率会高很多,然后再用not in,最终的效果也是一样,但是时间只要2.56S,然后采取语句2的关联表来处理,时间缩短到了1.42S,基本上效率是比较高的,当然理想的是在1S内。

4、尽量少用or,同时尽量用union all 代替union

or两边的字段如果有不走索引的会导致整个的查询不走索引,从而导致效率低下,这时可以使用union all或者union,而两者的区别是union是将两个结果合并之后再进行唯一性的过滤操作,效率会比union all低很多,但是union all需要两个数据集没有重复的数据。

5、分段和分页查询

在扫描行数较多的情况下可以采取分段查询,循环遍历,结果合并处理,

使用合理的分页方式,在数据表量级逐渐增加的时候,limit分页查询的效率会降低。

1)select id,col from table limit 888888,1000

2)select id,col from table where id > 888887 limit 1000

取前一页的最大行数的id,然后根据这个id来限制下一页的起点。

6、不建议使用%前缀模糊查询

like "%abc"和like "%abc%"会导致索引失效而进行全文搜索。

如果你还有什么比较好的优化tips欢迎分享!

</article>

作者:叫我老村长 链接:https://www.jianshu.com/p/f46b0f3d296b 来源:简书 著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。

本文转载自:https://www.jianshu.com/p/f46b0f3d296b

粉丝 0
博文 26
码字总数 8368
作品 0
成都
私信 提问
加载中

评论(0)

Oracle性能优化之性能调整_超越OCP精通Oracle视频教程培训38

Oracle性能优化之性能调整超越OCP精通Oracle视频教程培训38 课程介绍 风哥Oracle视频教程<>的第8/10套:Oracle性能优化之性能调整。学习Oracle性能优化,性能调整必备知识讲解 ,操作系统优化调...

风哥Oracle
2018/06/28
0
0
高级SQL优化(三)

SQL*PLUS下使用AUTOTRACE 1.AUTOTRACE简介 AUTOTRACE是SQL*Plus的一项功能,其作用是自动跟踪SQL语句,为SQL 语句生成一个 执行计划并且提供与 该语句的处理有关的统计信息。 SQL*Plus AUTOT...

Artemjor
2014/01/09
541
1
如何利用Object Browser图形化工具提高Oracle开发工作效率(七)简单的性能优化

开发中或者是正在运行的系统性能显著恶化的场合,需要进行性能优化。当听到性能优化时,有些人可能会感觉到非常困难,如果使用OB的话,通过使用索引或者内存等可以非常简单的进行性能优化。这...

海尼
2013/08/12
442
0
Oracle性能优化图文详解——利用第三方工具

开发中或者是正在运行的系统性能显著恶化的场合,需要进行性能优化。当听到性能优化时,有些人可能会感觉到非常困难,如果使用第三方工具的话,通过使用索引或者内存等可以非常简单的进行性能...

roadByroad
2013/07/17
5K
0
MySQL性能管理及架构设计:SQL查询优化、分库分表

一、SQL查询优化(重要) 1.1 获取有性能问题SQL的三种方式 通过用户反馈获取存在性能问题的SQL; 通过慢查日志获取存在性能问题的SQL; 实时获取存在性能问题的SQL; 1.1.2 慢查日志分析工具...

whs0668
2018/03/27
0
0

没有更多内容

加载失败,请刷新页面

加载更多

REST API服务为验证失败返回的适当HTTP状态代码是什么?

每当我在基于Django / Piston的REST API应用程序中遇到验证失败时,我目前正在返回401 Unauthorized。 看过HTTP状态代码注册表后我不相信这是验证失败的合适代码,你们都推荐什么? 400错误请...

javail
26分钟前
76
0
《计算机程序的构造和解释》分享下载

书籍信息 书名:《计算机程序的构造和解释》 原作名:Structure and Interpretation of Computer Programs 作者: Harold Abelson / Gerald Jay Sussman / Julie Sussman 豆瓣评分:9.5分(22...

开始以后_
32分钟前
57
0
《Linux就该这么学》第六节课while循环语句,case测试语句,计划任务及用户文件的相关命令

《Linux就该这么学》 本书是由全国多名红帽架构师(RHCA)基于最新Linux系统共同编写的高质量Linux技术自学教程,极其适合用于Linux技术入门教程或讲课辅助教材,目前是国内最值得去读的Lin...

溪风之殇
41分钟前
60
0
有没有办法让非root进程绑定到Linux上的“特权”端口?

在我的开发盒上有这个限制是非常烦人的,因为除了我之外不会有任何用户。 我知道标准的解决方法 ,但它们都没有完全符合我的要求: authbind (Debian测试中的版本,1.0,仅支持IPv4) 使用i...

技术盛宴
42分钟前
55
0
Java程序员必须要了解的类Unsafe

前言 Java是一个安全的编程语言,它能最大程度的防止程序员犯一些低级的错误(大部分是和内存管理有关的)。但凡是不是绝对的,使用Unsafe程序员就可以操作内存,因此可能带来一个安全隐患。...

Onegoleya
42分钟前
52
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部