基于集合和过程的查询方法逆向模型中隐含的事实是，基于集合的方法和过程构建查询的方法之间存在差异。查询的过程化方法与编程非常相似：您告诉系统需要做什么以及如何做。比如上一篇文章的例子，通过执行一个函数然后调用另一个函数来查询数据库，或者使用包括循环、条件、用户自定义函数（UDF）在内的逻辑来得到最终的查询结果。你会发现，通过这种方式，你总是在请求一层中的数据子集。这种方法通常也称为逐步或逐行查询。另一种是基于集合的方法，您只需要指定需要执行的操作。使用此方法所要做的就是为要从查询中获得的结果指定条件和要求。在检索数据的过程中，你不需要关注实现查询的内部机制：数据库引擎会决定最好的算法和逻辑来执行查询。由于SQL是基于集合的，因此这种方法比过程方法更有效，这解释了为什么在某些情况下，SQL可以比代码更快地工作。基于集合的查询方式也是数据挖掘分析行业需要你掌握的技能！因为你需要熟练地在这两种方式之间切换。如果你发现你的查询中有过程查询，你应该考虑是否需要重写这部分。从查询到执行计划的反向模式不是静态的。作为SQL开发人员，避免查询反向模型和重写查询可能是一项艰巨的任务。因此，通常需要使用工具以更结构化的方式优化您的查询。思考性能不仅需要一种更结构化的方法，还需要更深入的方法。然而，这种结构化和深入的方法主要基于查询计划。查询计划首先被解析成“解析树”，并准确定义每个操作使用什么算法以及如何协调操作。查询优化优化查询时，您可能需要手动检查优化器生成的计划。在这种情况下，您将需要通过查看查询计划再次分析您的查询。要掌握这样的查询计划，您需要使用数据库管理系统提供给您的一些工具。您可以使用的一些工具是：一些包功能工具可以生成查询计划的图形表示。其他工具可以为您提供查询计划的文本描述。请注意，如果您使用的是PostgreSQL，则可以区分不同的EXPLAIN，您只是获得规划器如何执行查询的描述，而无需运行计划。同时，EXPLAINANALYZE会执行查询，返回给你预估查询计划和实际查询计划的分析报告。一般来说，实际执行计划会实际执行计划，而评估执行计划可以在不执行查询的情况下解决这个问题。从逻辑上讲，实际执行计划更有用，因为它包含有关执行查询时实际发生的情况的其他详细信息和统计信息。接下来，您将了解有关XPLAIN和ANALYZE的更多信息，以及如何使用它们来了解有关您的查询计划和查询性能的更多信息。为此，您需要从两个表开始：one_million和half_million来做一些示例。您可以借助EXPLAIN检索one_million表的当前信息：确保将其放在第一个运行查询的位置，运行完成后，它将返回查询计划：EXPLAINSELECT*FROMone_million;QUERYPLAN1025082width=36)(1row)在上面的例子中，我们看到查询的代价是0.00..18584.82，行数是1025082，列宽是36，同时统计信息也可以用分析的帮助。ANALYZEone_million;EXPLAINSELECT*FROMone_million;QUERYPLAN_________________________________________________SeqScanonone_million(cost=0.00..18334.00rows=1000000width=37)(1row)除了EXPLAIN和ANALYZE，你也可以借助EXPLAINANALYZE来检索实际执行时间：EXPLAINANALYZESELECT*FROMone_million;QUERYPLAN___________________________________________________SeqScanonone_million(cost=0.00..18334.00rows=1000000width=37)(actualtime=0.015..1207.019rows=1000000loops=1)Totalruntime:2320.146ms(2rows)使用EXPLAINANALYZE的缺点是需要实际执行查询，这一点值得注意！到目前为止，我们看到的所有算法都是顺序扫描或者全表扫描：这是一种对数据库进行扫描的方法，扫描表的每一行都是按顺序（串行）顺序读取的，并且检查每一列是否合规有条件的。从性能上来说，顺序扫描并不是最好的执行计划，因为需要扫描整张表。但是对于慢速磁盘，顺序读取也很快。还有一些其它算法的示例：EXPLAINANALYZESELECT*FROMone_millionJOINhalf_millionON(one_million.counter=half_million.counter);QUERYPLAN_____________________________________________________________HashJoin(cost=15417.00..68831.00rows=500000width=42)(actualtime=1241.471..5912.553rows=500000loops=1)HashCond:(one_million.counter=half_million.counter)->SeqScanonone_million(cost=0.00..18334.00rows=1000000width=37)(actualtime=0.007..1254.027rows=1000000loops=1)->Hash(cost=7213.00..7213.0500000width=5)(actualtime=1241.251..1241.251rows=500000loops=1)Buckets:4096Batches:16MemoryUsage:770kB->SeqScanonhalf_million(cost=0.00..7213.00rows=500000width=5)(actualtime=0.008..600rows501s=.1280)Totalruntime:6468.337ms我们可以看到查询优化器选择了HashJoin。记住这个操作，因为我们需要用它来评估查询的时间复杂度。我们注意到了上面示例中没有half_million.counter索引，我们可以在下面示例中添加索引：CREATEINDEXONhalf_million(counter);EXPLAINANALYZESELECT*FROMone_millionJOINhalf_millionON(one_million.counter=half_million.counter);QUERYPLAN______________________________________________________________MergeJoin(cost=4.12..37650.65rows=500000width=42)(actualtime=0.033..3272.940rows=500000loops=1)MergeCond:(one_million.counter=half_million.counter)->IndexScanusingone_million_counter_idxonone_million(cost=0.00..32129.34rows=1000000width.1=4.60.60)(6rows=500001loops=1)->IndexScanusinghalf_million_counter_idxonhalf_million(cost=0.00..14120.29rows=500000width=5)(actualtime=0.010..683.674rows=500000loops=1)Totalruntime:3833.310mshasbeencreatedbythequeryoptimizer)(DetermineshowtofindMergejoinsduringindexscans.Notethedifferencebetweenindexscansandfulltablescans(sequentialscans):thelatter(alsoknownas"tablescans")findsuitableresultsbyscanningalldataorindexingallpages,whiletheformeronlyscantablesforeachrowinthe.
