详解sqlite中的查询规划器.docVIP

详解sqlite中的查询规划器 这个查询不是特别复杂，不过，即便这样，它仍然可以替代上百行，也许是上千行处理过程代码。这个查询的要点是：向下扫描event表，查找满足下列三个条件中任何一个的最新的200条提交记录： ??? 此提交含有trunk标签。??? 此提交有个子提交含有“trunk标签。??? 此提交有个父提交含有“trunk标签。第一个条件将显示所有主干分支上的提交，第二个和第三个条件包含合并到主干分支，或者由主干分支产生的提交。这三个条件是通过在此查询的where子句中用or连接三个exists语句实现的。使用下一代查询规划器引起的性能下降是由第二个和第三个条件产生的。两个条件里存在的问题是相同的，因此我们只看第二个条件。第二个条件的子查询可以重写为如下语句（把次要的和不重要的进行了简化）： plink表保存着各个提交之间的父子关系。tagxref表把标签映射到提交上。作为参考，对这两个表进行查询的模式的相关部分显示如下： 实现这样的查询只有两个方法值得考虑。（当然可能还有许多其他算法，不过它们中的任何一个都不是“最佳”算法的竞争者。 ??? 查找提交$ckid的所有子提交，然后对每一个进行测试，看看是否有子提交包含$trunk标签??? 查找所有包含$trunk标签的提交，然后对每个这样的提交进行测试，看看是否有$ckid提交的子提交。仅凭直觉，我们人类认为第一个算法是最佳选择。每个提交可能有几个子提交（其中有一个提交是我们最常用到的。），然后对每个子提交进行测试，用对数运算计算出查找到$trunk标签的时间。实际上，算法1确实较快。然而下一代查询规划器却没有使用人们直觉上的最佳选择。下一代查询规划器一定是选择了很难得算法，算法2在数学上相对稍微难些。这是因为：在没有其他信息的情况下下一代查询规划器一定假设plink_i1和tagxref_i1索引具有同等的质量和同等的可选择性。算法2使用了tagxref_i1索引的一个字段，plink_i1索引的两个字段，而算法1只是使用了这两个索引的第一个字段。正是由于算法2使用了多个字段的索引，所以下一代查询规划器才会以自己的标准正确地确定它作为两种算法中性能较好的算法。两个算法所花费的时间非常接近，算法2 只是勉强稍稍领先算法1。不过，这种情况下，选择算法2确实是正确的。 很不幸，在实际的应用中算法2比算法1要慢些。 出现这样的问题是因为索引并不是具有同等质量。一个提交有可能只有一个子提交。这样plink_i1索引的第一个字段通常缩减值对一行进行搜索。不过由于成千上万的提交都包含有trunk标签，所以tagxref_i1的第一个字段对缩减搜索不会有多大帮助。 下一代查询规划器是没有办法知道tagxref_i1在这样的查询中几乎没有什么用处，除非在数据库上运行analyze。analyze命令 收集了各个索引的质量统计信息，并把 这些统计信息存储到sqlite_stat1表里。如果下一代查询规划器能够访问这些统计信息 ，那么在很大程度上它就会非常容易地选择算法1作为最佳算法。 难道旧查询规划器没有选择算法2？很简单：因为nn算法甚至从来都没有考虑到算法2。这类规划问题的图示如下： 在如左图那样“没有运行analyze“的情况下，nn算法选择循环p9plink)作为外循环，因为比要小，结果就是选择p-t路径，即算法1。nn算法只是在每一步查找一个最佳选择路径，因此它完全忽略了这样一个事实：+是比+性能稍稍有些好的规划。然而n3算法对着两个连接追踪了5个最佳路径，因此它最终选择了t-p路径，因为这条路径的总体资源消耗要少一些。路径t-p就是算法2。 注意： 如果运行了analyze，那么对资源消耗的评估就更加接近于现实，这样nn和n3都选择算法1。 （附注：最新的两图中对资源消耗的评估是下一代查询规划器使用以2为底的对数算法计算得出来的，而且与旧查询规划器相比假设的资源消耗稍微有些不同。因此，最后两个图中的资源消耗评估不能与tpc-h q8图里的资源消耗评估进行比较。） 问题修正 对资源仓库数据库运行analyze可立即修复这类性能问题。然而，无论是否对资源仓库是否进行分析，我们都要求fossil十分强壮，而且总是能够快速地运行。基于这个原因，我们修改查询使用cross join操作符而不使用常用的join操作符。sqlite将不会对cross join连接的表重新排序。这个功能是sqlite中长期都有的一个功能，做这么特别的设计就是允许具有丰富经验的开发人员能够强制sqlite执行特定的嵌套循环顺序。一旦某个连接更改为（增加了一个关键字的）cross join这样的连接，下一代查询规划器就不管是否使用analyze收集统计统计信息都强制选择稍稍快一点的算法1。 我们说算法