文档章节

如何编写更好的SQL查询:终极指南-第二部分

葡萄城技术团队
 葡萄城技术团队
发布于 2017/09/05 11:16
字数 1787
阅读 1149
收藏 60
点赞 2
评论 0

上一篇文章中,我们学习了 SQL 查询是如何执行的以及在编写 SQL 查询语句时需要注意的地方。

下面,我进一步学习查询方法以及查询优化。

 

基于集合和程序的方法进行查询

反向模型中隐含的事实是,建立查询时基于集合和程序的方法之间存在着不同。

  • 查询的程序方法是一种非常类似于编程的方法:你告诉系统需要做些什么以及如何做。例如上一篇文章中的示例,通过执行一个函数然后调用另一个函数来查询数据库,或者使用包含循环、条件和用户定义函数(UDF)的逻辑方式来获得最终查询结果。你会发现通过这种方式,一直在请求一层一层中数据的子集。这种方法也经常被称为逐步或逐行查询。
  • 另一种是基于集合的方法,只需指定需要执行的操作。使用这种方法要做的事情就是,指定你想通过查询获得的结果的条件和要求。在检索数据过程中,你不需要关注实现查询的内部机制:数据库引擎会决定最佳的执行查询的算法和逻辑。

由于 SQL 是基于集合的,所以这种方法比起程序方法更加有效,这也解释了为什么在某些情况下,SQL 可以比代码工作地更快。

基于集合的查询方法也是数据挖掘分析行业要求你必须掌握的技能!因为你需要熟练的在这两种方法之间进行切换。如果你发现自己的查询中存在程序查询,则应该考虑是否需要重写这部分。

 

从查询到执行计划 

反向模式不是静止不变的。在你成为 SQL 开发者的过程中,避免查询反向模型和重写查询可能会是一个很艰难的任务。所以时常需要使用工具以一种更加结构化的方法来优化你的查询。

对性能的思考不仅需要更结构化的方法,还需要更深入的方法。

然而,这种结构化和深入的方法主要是基于查询计划的。查询计划首先被解析为“解析树”并且准确定义了每个操作使用什么算法以及如何协调操作过程。

 

查询优化

在优化查询时,很可能需要手动检查优化器生成的计划。在这种情况下,将需要通过查看查询计划来再次分析你的查询。

要掌握这样的查询计划,你需要使用一些数据库管理系统提供给你的工具。你可以使用以下的一些工具:

  • 一些软件包功能工具可以生成查询计划的图形表示。
  • 其它工具能够为你提供查询计划的文本描述。

请注意,如果你正在使用 PostgreSQL,则可以区分不同的 EXPLAIN,你只需获取描述,说明 planner 如何在不运行计划的情况下执行查询。同时 EXPLAIN ANALYZE 会执行查询,并返回给你一个评估查询计划与实际查询计划的分析报告。一般来说,实际执行计划会切实的执行这个计划,而评估执行计划可以在不执行查询的情况下,解决这个问题。在逻辑上,实际执行计划更为有用,因为它包含了执行查询时,实际发生的其它细节和统计信息。

接下来你将了解 XPLAIN 和 ANALYZE 的更多信息,以及如何使用这两个命令来进一步了解你的查询计划和查询性能。要做到这一点,你需要开始使用两个表: one_million 和 half_million 来做一些示例。

你可以借助 EXPLAIN 来检索 one_million 表的当前信息:确保已将其放在运行查询的首要位置,在运行完成之后,会返回到查询计划中:

EXPLAIN
SELECT *
FROM one_million;
QUERY PLAN
_________________________________________________
Seq Scan on one_million
(cost=0.00..18584.82 rows=1025082 width=36)
(1 row)

在以上示例中,我们看到查询的 Cost 是0.00..18584.82 ,行数是1025082,列宽是36。

同时,也可以借助 ANALYZE 来更新统计信息  。

ANALYZE one_million;
EXPLAIN
SELECT *
FROM one_million;
QUERY PLAN
_________________________________________________
Seq Scan on one_million
(cost=0.00..18334.00 rows=1000000 width=37)
(1 row)

除了 EXPLAIN 和 ANALYZE,你也可以借助 EXPLAIN ANALYZE 来检索实际执行时间:

EXPLAIN ANALYZE
SELECT *
FROM one_million;
QUERY PLAN
___________________________________________________
Seq Scan on one_million
(cost=0.00..18334.00 rows=1000000 width=37)
(actual time=0.015..1207.019 rows=1000000 loops=1)
Total runtime: 2320.146 ms
(2 rows)

使用 EXPLAIN ANALYZE 的缺点就是需要实际执行查询,这点值得注意!

到目前为止,我们看到的所有算法是顺序扫描或全表扫描:这是一种在数据库上进行扫描的方法,扫描的表的每一行都是以顺序(串行)的顺序进行读取,每一列都会检查是否符合条件。在性能方面,顺序扫描不是最佳的执行计划,因为需要扫描整个表。但是如果使用慢磁盘,顺序读取也会很快。

还有一些其它算法的示例:

EXPLAIN ANALYZE
SELECT *
FROM one_million JOIN half_million
ON (one_million.counter=half_million.counter);
QUERY PLAN
_____________________________________________________________
Hash Join (cost=15417.00..68831.00 rows=500000 width=42)
(actual time=1241.471..5912.553 rows=500000 loops=1)
Hash Cond: (one_million.counter = half_million.counter)
    -> Seq Scan on one_million
    (cost=0.00..18334.00 rows=1000000 width=37)
    (actual time=0.007..1254.027 rows=1000000 loops=1)
    -> Hash (cost=7213.00..7213.00 rows=500000 width=5)
    (actual time=1241.251..1241.251 rows=500000 loops=1)
    Buckets: 4096 Batches: 16 Memory Usage: 770kB
    -> Seq Scan on half_million
    (cost=0.00..7213.00 rows=500000 width=5)
(actual time=0.008..601.128 rows=500000 loops=1)
Total runtime: 6468.337 ms

我们可以看到查询优化器选择了 Hash Join。请记住这个操作,因为我们需要使用这个来评估查询的时间复杂度。我们注意到了上面示例中没有 half_million.counter 索引,我们可以在下面示例中添加索引  :

CREATE INDEX ON half_million(counter);
EXPLAIN ANALYZE
SELECT *
FROM one_million JOIN half_million
ON (one_million.counter=half_million.counter);
QUERY PLAN
______________________________________________________________
Merge Join (cost=4.12..37650.65 rows=500000 width=42)
(actual time=0.033..3272.940 rows=500000 loops=1)
Merge Cond: (one_million.counter = half_million.counter)
    -> Index Scan using one_million_counter_idx on one_million
    (cost=0.00..32129.34 rows=1000000 width=37)
    (actual time=0.011..694.466 rows=500001 loops=1)
    -> Index Scan using half_million_counter_idx on half_million
    (cost=0.00..14120.29 rows=500000 width=5)
(actual time=0.010..683.674 rows=500000 loops=1)
Total runtime: 3833.310 ms
(5 rows)

通过创建索引,查询优化器已经决定了索引扫描时,如何查找 Merge join。

请注意,索引扫描和全表扫描(顺序扫描)之间的区别:后者(也称为“表扫描”)是通过扫描所有数据或索引所有页面来查找到适合的结果,而前者只扫描表中的每一行。

 

教程的第二部分内容,就介绍到这里。后续还会有《如何编写更好的SQL查询》系列的最后一篇文章,敬请期待。

原文链接:http://www.kdnuggets.com/2017/08/write-better-sql-queries-definitive-guide-part-2.html

转载请注明出自:葡萄城控件

© 著作权归作者所有

共有 人打赏支持
葡萄城技术团队

葡萄城技术团队

粉丝 320
博文 494
码字总数 706491
作品 13
西安
高级程序员
如何编写更好的SQL查询:终极指南-第二部分

上一篇文章中,我们学习了 SQL 查询是如何执行的以及在编写 SQL 查询语句时需要注意的地方。 下面,我进一步学习查询方法以及查询优化。 基于集合和程序的方法进行查询 反向模型中隐含的事实...

powertoolsteam
2017/09/05
0
0
如何编写更好的SQL查询:终极指南-第二部分

上一篇文章中,我们学习了 SQL 查询是如何执行的以及在编写 SQL 查询语句时需要注意的地方。 下面,我进一步学习查询方法以及查询优化。 基于集合和程序的方法进行查询 反向模型中隐含的事实...

powertoolsteam
2017/09/05
0
0
如何编写更好的SQL查询:终极指南-第一部分

结构化查询语言(SQL)是数据挖掘分析行业不可或缺的一项技能,总的来说,学习这个技能是比较容易的。对于SQL来说,编写查询语句只是第一步,确保查询语句高效并且适合于你的数据库操作工作,...

powertoolsteam
2017/08/29
0
0
调查报告:现在的开发人员对数据库的使用方式

根据Evans数据公司提供的数据,全世界大约有1900万开发人员。而2015年StackOverflow开发者调查显示,36%的开发人员最近使用过SQL。也就是说,现如今大约有700万开发人员使用SQL。在过去四年里...

oschina
2016/01/01
3.2K
4
如何编写更好的SQL查询:终极指南-第三部分

本次我们学习《如何编写更好的SQL查询》系列的最后一篇文章。 时间复杂度和大O符号 通过前两篇文章,我们已经对查询计划有了一定了解。接下来,我们还可以借助计算复杂度理论,来进一步深入地...

powertoolsteam
2017/09/11
0
0
如何编写更好的SQL查询:终极指南-第三部分

本次我们学习《如何编写更好的SQL查询》系列的最后一篇文章。 时间复杂度和大O符号 通过前两篇文章,我们已经对查询计划有了一定了解。接下来,我们还可以借助计算复杂度理论,来进一步深入地...

powertoolsteam
2017/09/11
0
0
如何编写更好的SQL查询:终极指南-第三部分

本次我们学习《如何编写更好的SQL查询》系列的最后一篇文章。 时间复杂度和大O符号 通过前两篇文章,我们已经对查询计划有了一定了解。接下来,我们还可以借助计算复杂度理论,来进一步深入地...

powertoolsteam
2017/09/11
0
0
如果你在找 CSS 学习资源,那就收藏这篇文章

本文由伯乐在线 -伯小乐 翻译。未经许可,禁止转载! 英文出处:Grace Smith。欢迎加入翻译组。 如果你在寻找一些 CSS 基础和高级的指南或资源,那这篇文章应该有你要的 CSS 初级和高级指南:...

伯乐在线
2014/11/07
0
0
数据模型与查询语言 ------《Designing Data-Intensive Applications》读书笔记2

数据模型是开发软件的最重要的部分,因为它们对应用程序有着深远的影响:不仅是软件的编写方式,而且也影响我们如何解决的问题的方式。第二篇读书笔记,我们聊一聊数据模型的设计。 1.数据模...

LeeHappen
2017/12/20
0
0
10 件在 PHP 7 中不要做的事情

1. 不要使用函数 这一天终于来了,从此你不仅仅“不应该”使用函数。PHP 7 已经把它们从核心中全部移除了,也就是说你需要迁移到好得多的函数,或者更灵活的 PDO 实现。 2. 不要编写垃圾代码...

两味真火
2016/09/18
12.1K
52

没有更多内容

加载失败,请刷新页面

加载更多

下一页

如何把你的Linux系统变的更加安全

做为一个小白,以为自己懂了点Linux知识,会搭建Linux各种服务就觉得自己牛的不要要的。在我们团队里面,我将使用了一台破电脑搭建Linux服务器,上面跑着Ftp服务存放着资源,ssh服务可以远程...

问题终结者
1分钟前
0
0
lombok的使用和原理

一、项目背景 在写Java程序的时候经常会遇到如下情形: 新建了一个Class类,然后在其中设置了几个字段,最后还需要花费很多时间来建立getter和setter方法 lombok项目的产生就是为了省去我们手...

颖辉小居
1分钟前
0
0
rsync至服务同步-系统日志-screen

rsync: 服务同步;配置文件:/etc/rsyncd.conf 默认端口:873 服务启动:rsync --daemon rsync -av /root/1.txt 192.168.1.2::test/2.txt (test为模块名称) /etc/rsync.conf配置样例: #指定...

ZHENG-JY
3分钟前
0
0
读取文件中内容转换成字符串

package com.lieni.ruyu.api.xmlTool; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.Unsuppo......

newdeng
3分钟前
0
0
《PHP和MySQL Web 开发》 第8章 设计Web数据库

LCL WARNING 这是我学习《PHP和MySQL Web 开发》的读书笔记,一些重要的知识点我会记录下来,当然只会写我觉得重要的。 如果有幸有人看到这个学习笔记了,你要结合着书看,不要光看这个笔记。...

十万猛虎下画山
10分钟前
0
0
Spring+jpaNo transactional EntityManager available

TransactionRequiredException: No transactional EntityManager availableEntityManager执行以下方法(refresh, persist, flush, joinTransaction, remove, merge) 都需要需要事务i......

wpfc
11分钟前
0
0
八幅漫画理解使用JSON Web Token设计单点登录系统

八幅漫画理解使用JSON Web Token设计单点登录系统 Sep 07, 2015 in Engineering 上次在《JSON Web Token - 在Web应用间安全地传递信息》中我提到了JSON Web Token可以用来设计单点登录系统。...

祖冲之
13分钟前
0
0
Spring框架中的设计模式(三)

Spring框架中的设计模式(三) 原创: 瑞查德-Jack 在之前的两篇文章中,我们看到了一些在Spring框架中实现的设计模式。这一次我们会发现这个流行框架使用的3种新模式。 本文将从描述两个创意...

瑞查德-Jack
16分钟前
1
0
[MicroPython]TPYBoard智能小车“飞奔的TPYBoard装甲一号”

智能小车作为现代的新发明,是以后的发展方向,他可以按照预先设定的模式在一个环境里自动的运作,不需要人为的管理,可应用于科学勘探等等的用途。智能小车能够实时显示时间、速度、里程,具...

bodasisiter
18分钟前
0
0
桌面虚拟化VDI(Virtual Desktop Infrastructure)

为了保证员工(客户)不把公司的资料复制、传输给别人。可以把员工平时办公放在服务器上做。所以使用桌面虚拟化。就是把一个服务器虚拟出很多桌面系统(如:windows)。 桌面虚拟化最大的优势...

王坤charlie
25分钟前
3
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部