请选择 进入手机版 | 继续访问电脑版

葡萄城产品技术社区

    注册

QQ登录

SpreadJS

纯前端控件,类似Excel,问题响应通道

ComponentOne WPF

前沿科技,针对WPF桌面,问题响应通道

C1FlexGrid视频教程

WinForm平台,功能强大的表格控件,初学者必看视频

ActiveReport视频教程

轻松搞定报表,从头开始学AR,视频教程

搜索
查看: 781|回复: 0

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

[复制链接]

258

主题

259

帖子

2902

积分

超级版主

Rank: 8Rank: 8

积分
2902

微信认证勋章

发表于 2017-9-5 11:43:42 | 显示全部楼层 |阅读模式
本帖最后由 断天涯大虾 于 2017-9-6 15:45 编辑


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

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

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

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


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

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


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

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

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

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

要掌握这样的查询计划,你需要使用一些数据库管理系统提供给你的工具。你可以使用以下的一些工具:
  • 一些软件包功能工具可以生成查询计划的图形表示。
  • 其它工具能够为你提供查询计划的文本描述。

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

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

你可以借助 EXPLAIN 来检索 one_million 表的当前信息:确保已将其放在运行查询的首要位置,在运行完成之后,会返回到查询计划中:
  1. EXPLAIN
  2. SELECT *
  3. FROM one_million;
  4. QUERY PLAN
  5. _________________________________________________
  6. Seq Scan on one_million
  7. (cost=0.00..18584.82 rows=1025082 width=36)
  8. (1 row)
复制代码
在以上示例中,我们看到查询的 Cost 是0.00..18584.82 ,行数是1025082,列宽是36。
同时,也可以借助 ANALYZE 来更新统计信息  。
  1. ANALYZE one_million;
  2. EXPLAIN
  3. SELECT *
  4. FROM one_million;
  5. QUERY PLAN
  6. _________________________________________________
  7. Seq Scan on one_million
  8. (cost=0.00..18334.00 rows=1000000 width=37)
  9. (1 row)
复制代码
除了 EXPLAIN 和 ANALYZE,你也可以借助 EXPLAIN ANALYZE 来检索实际执行时间:
  1. EXPLAIN ANALYZE
  2. SELECT *
  3. FROM one_million;
  4. QUERY PLAN
  5. ___________________________________________________
  6. Seq Scan on one_million
  7. (cost=0.00..18334.00 rows=1000000 width=37)
  8. (actual time=0.015..1207.019 rows=1000000 loops=1)
  9. Total runtime: 2320.146 ms
  10. (2 rows)
复制代码
使用 EXPLAIN ANALYZE 的缺点就是需要实际执行查询,这点值得注意!

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

还有一些其它算法的示例:
  1. EXPLAIN ANALYZE
  2. SELECT *
  3. FROM one_million JOIN half_million
  4. ON (one_million.counter=half_million.counter);
  5. QUERY PLAN
  6. _____________________________________________________________
  7. Hash Join (cost=15417.00..68831.00 rows=500000 width=42)
  8. (actual time=1241.471..5912.553 rows=500000 loops=1)
  9. Hash Cond: (one_million.counter = half_million.counter)
  10.     -> Seq Scan on one_million
  11.     (cost=0.00..18334.00 rows=1000000 width=37)
  12.     (actual time=0.007..1254.027 rows=1000000 loops=1)
  13.     -> Hash (cost=7213.00..7213.00 rows=500000 width=5)
  14.     (actual time=1241.251..1241.251 rows=500000 loops=1)
  15.     Buckets: 4096 Batches: 16 Memory Usage: 770kB
  16.     -> Seq Scan on half_million
  17.     (cost=0.00..7213.00 rows=500000 width=5)
  18. (actual time=0.008..601.128 rows=500000 loops=1)
  19. Total runtime: 6468.337 ms
复制代码
我们可以看到查询优化器选择了 Hash Join。请记住这个操作,因为我们需要使用这个来评估查询的时间复杂度。我们注意到了上面示例中没有 half_million.counter 索引,我们可以在下面示例中添加索引  :
  1. CREATE INDEX ON half_million(counter);
  2. EXPLAIN ANALYZE
  3. SELECT *
  4. FROM one_million JOIN half_million
  5. ON (one_million.counter=half_million.counter);
  6. QUERY PLAN
  7. ______________________________________________________________
  8. Merge Join (cost=4.12..37650.65 rows=500000 width=42)
  9. (actual time=0.033..3272.940 rows=500000 loops=1)
  10. Merge Cond: (one_million.counter = half_million.counter)
  11.     -> Index Scan using one_million_counter_idx on one_million
  12.     (cost=0.00..32129.34 rows=1000000 width=37)
  13.     (actual time=0.011..694.466 rows=500001 loops=1)
  14.     -> Index Scan using half_million_counter_idx on half_million
  15.     (cost=0.00..14120.29 rows=500000 width=5)
  16. (actual time=0.010..683.674 rows=500000 loops=1)
  17. Total runtime: 3833.310 ms
  18. (5 rows)
复制代码
通过创建索引,查询优化器已经决定了索引扫描时,如何查找 Merge join。

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

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

原文链接:http://www.kdnuggets.com/2017/08/write-better-sql-queries-definitive-guide-part-2.html
转载请注明出自:葡萄城控件
   
关于葡萄城:全球最大的控件提供商,世界领先的企业应用定制工具、企业报表和商业智能解决方案提供商,为超过75%的全球财富500强企业提供服务。
回复

使用道具 举报

*滑动验证:
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

欢迎关注葡萄城控件

小黑屋|手机版|Archiver|葡萄城控件官网 ( 沪ICP备05020274号

GMT+8, 2017-11-18 14:31 , Processed in 1.129926 second(s), 34 queries .

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表