什么网站做简历最好,东莞百度seo新网站快速排名,工作站做网站,上海企业招聘信息上一篇文章《如何编写更好的SQL查询#xff1a;终极指南-第一部分》中#xff0c;我们学习了 SQL 查询是如何执行的以及在编写 SQL 查询语句时需要注意的地方。
下面#xff0c;我进一步学习查询方法以及查询优化。
基于集合和程序的方法进行查询
反向模型中隐含的事实是…上一篇文章《如何编写更好的SQL查询终极指南-第一部分》中我们学习了 SQL 查询是如何执行的以及在编写 SQL 查询语句时需要注意的地方。
下面我进一步学习查询方法以及查询优化。
基于集合和程序的方法进行查询
反向模型中隐含的事实是建立查询时基于集合和程序的方法之间存在着不同。
查询的程序方法是一种非常类似于编程的方法你告诉系统需要做些什么以及如何做。例如上一篇文章中的示例通过执行一个函数然后调用另一个函数来查询数据库或者使用包含循环、条件和用户定义函数UDF的逻辑方式来获得最终查询结果。你会发现通过这种方式一直在请求一层一层中数据的子集。这种方法也经常被称为逐步或逐行查询。另一种是基于集合的方法只需指定需要执行的操作。使用这种方法要做的事情就是指定你想通过查询获得的结果的条件和要求。在检索数据过程中你不需要关注实现查询的内部机制数据库引擎会决定最佳的执行查询的算法和逻辑。
由于 SQL 是基于集合的所以这种方法比起程序方法更加有效这也解释了为什么在某些情况下SQL 可以比代码工作地更快。
基于集合的查询方法也是数据挖掘分析行业要求你必须掌握的技能因为你需要熟练的在这两种方法之间进行切换。如果你发现自己的查询中存在程序查询则应该考虑是否需要重写这部分。
从查询到执行计划
反向模式不是静止不变的。在你成为 SQL 开发者的过程中避免查询反向模型和重写查询可能会是一个很艰难的任务。所以时常需要使用工具以一种更加结构化的方法来优化你的查询。
对性能的思考不仅需要更结构化的方法还需要更深入的方法。
然而这种结构化和深入的方法主要是基于查询计划的。查询计划首先被解析为“解析树”并且准确定义了每个操作使用什么算法以及如何协调操作过程。 查询优化
在优化查询时很可能需要手动检查优化器生成的计划。在这种情况下将需要通过查看查询计划来再次分析你的查询。
要掌握这样的查询计划你需要使用一些数据库管理系统提供给你的工具。你可以使用以下的一些工具
一些软件包功能工具可以生成查询计划的图形表示。其它工具能够为你提供查询计划的文本描述。
请注意如果你正在使用 PostgreSQL则可以区分不同的 EXPLAIN你只需获取描述说明 planner 如何在不运行计划的情况下执行查询。同时 EXPLAIN ANALYZE 会执行查询并返回给你一个评估查询计划与实际查询计划的分析报告。一般来说实际执行计划会切实的执行这个计划而评估执行计划可以在不执行查询的情况下解决这个问题。在逻辑上实际执行计划更为有用因为它包含了执行查询时实际发生的其它细节和统计信息。
接下来你将了解 XPLAIN 和 ANALYZE 的更多信息以及如何使用这两个命令来进一步了解你的查询计划和查询性能。要做到这一点你需要开始使用两个表 one_million 和 half_million 来做一些示例。
你可以借助 EXPLAIN 来检索 one_million 表的当前信息确保已将其放在运行查询的首要位置在运行完成之后会返回到查询计划中
EXPLAINSELECT *FROM one_million;
QUERY PLAN_________________________________________________
Seq Scan on one_million
(cost0.00..18584.82 rows1025082 width36)
(1 row)
在以上示例中我们看到查询的 Cost 是0.00..18584.82 行数是1025082列宽是36。
同时也可以借助 ANALYZE 来更新统计信息 。
ANALYZE one_million;
EXPLAINSELECT *FROM one_million;
QUERY PLAN_________________________________________________
Seq Scan on one_million
(cost0.00..18334.00 rows1000000 width37)
(1 row) 除了 EXPLAIN 和 ANALYZE你也可以借助 EXPLAIN ANALYZE 来检索实际执行时间
EXPLAIN ANALYZESELECT *FROM one_million;
QUERY PLAN___________________________________________________
Seq Scan on one_million
(cost0.00..18334.00 rows1000000 width37)
(actual time0.015..1207.019 rows1000000 loops1)
Total runtime: 2320.146 ms
(2 rows) 使用 EXPLAIN ANALYZE 的缺点就是需要实际执行查询这点值得注意
到目前为止我们看到的所有算法是顺序扫描或全表扫描这是一种在数据库上进行扫描的方法扫描的表的每一行都是以顺序串行的顺序进行读取每一列都会检查是否符合条件。在性能方面顺序扫描不是最佳的执行计划因为需要扫描整个表。但是如果使用慢磁盘顺序读取也会很快。
还有一些其它算法的示例
EXPLAIN ANALYZESELECT *FROM one_million JOIN half_millionON (one_million.counterhalf_million.counter);
QUERY PLAN_____________________________________________________________
Hash Join (cost15417.00..68831.00 rows500000 width42)
(actual time1241.471..5912.553 rows500000 loops1)
Hash Cond: (one_million.counter half_million.counter) - Seq Scan on one_million(cost0.00..18334.00 rows1000000 width37)(actual time0.007..1254.027 rows1000000 loops1) - Hash (cost7213.00..7213.00 rows500000 width5)(actual time1241.251..1241.251 rows500000 loops1)Buckets: 4096 Batches: 16 Memory Usage: 770kB - Seq Scan on half_million(cost0.00..7213.00 rows500000 width5)
(actual time0.008..601.128 rows500000 loops1)
Total runtime: 6468.337 ms
我们可以看到查询优化器选择了 Hash Join。请记住这个操作因为我们需要使用这个来评估查询的时间复杂度。我们注意到了上面示例中没有 half_million.counter 索引我们可以在下面示例中添加索引
CREATE INDEX ON half_million(counter);
EXPLAIN ANALYZESELECT *FROM one_million JOIN half_millionON (one_million.counterhalf_million.counter);
QUERY PLAN______________________________________________________________
Merge Join (cost4.12..37650.65 rows500000 width42)
(actual time0.033..3272.940 rows500000 loops1)
Merge Cond: (one_million.counter half_million.counter) - Index Scan using one_million_counter_idx on one_million(cost0.00..32129.34 rows1000000 width37)(actual time0.011..694.466 rows500001 loops1) - Index Scan using half_million_counter_idx on half_million(cost0.00..14120.29 rows500000 width5)
(actual time0.010..683.674 rows500000 loops1)
Total runtime: 3833.310 ms
(5 rows)
通过创建索引查询优化器已经决定了索引扫描时如何查找 Merge join。
请注意索引扫描和全表扫描顺序扫描之间的区别后者也称为“表扫描”是通过扫描所有数据或索引所有页面来查找到适合的结果而前者只扫描表中的每一行。
教程的第二部分内容就介绍到这里。后续还会有《如何编写更好的SQL查询》系列的最后一篇文章敬请期待。
相关文章
如何编写更好的SQL查询终极指南-第一部分Entity Framework Core 执行SQL语句和存储过程SQL注入(SQL Injection)
原文地址http://www.kdnuggets.com/2017/08/write-better-sql-queries-definitive-guide-part-2.html .NET社区新闻深度好文微信中搜索dotNET跨平台或扫描二维码关注
