详解SQLite中的查询规划器(2)-木庄网络博客

4.1范例分析：升级Fossil使用下一代查询规器

Fossil DVCS是用来追踪全部SQLite源代码的版本控制系统。Fossil软件仓库就是一个SQLite数据库文件。（作为单独的练习，我们邀请读者对这种递归式的应用查询规划器进行深入思考。）Fossil既是SQLite的版本控制系统，也是SQLite的测试平台。无论什么时候对SQLite进行强化，Fossil都是对这些强化进行测试和评估的首批应用之一。所以Fossil最早采用下一代查询规划器。

很不幸，下一代查询规划器引起了Fossil性能下降。

Fossil用到许多报表，其中之一就是单个分支上所做更改的时间表，它显示了这个分支的所有合并和删除。查看 http://www.sqlite.org/src/timeline?nd&n=200&r=trunk就可以看到时间报表的典型例子。通常生成这样的报表只需要几毫秒。然而，升级到下一代查询规划器之后，我们发现对仓库的主干分支生成这样的报表竟然需要近10秒的时间。

用来生成分支时间表的核心查询如下。（我们不期望读者理解这个查询的细节。下面将给出说明。）

SELECT
   blob.rid AS blobRid,
   uuid AS uuid,
   datetime(event.mtime,'localtime') AS timestamp,
   coalesce(ecomment, comment) AS comment,
   coalesce(euser, user) AS user,
   blob.rid IN leaf AS leaf,
   bgcolor AS bgColor,
   event.type AS eventType,
   (SELECT group_concat(substr(tagname,5), ', ')
    FROM tag, tagxref
    WHERE tagname GLOB 'sym-*'
     AND tag.tagid=tagxref.tagid
     AND tagxref.rid=blob.rid
     AND tagxref.tagtype>0) AS tags,
   tagid AS tagid,
   brief AS brief,
   event.mtime AS mtime
 FROM event CROSS JOIN blob
 WHERE blob.rid=event.objid
  AND (EXISTS(SELECT 1 FROM tagxref
        WHERE tagid=11 AND tagtype>0 AND rid=blob.rid)
    OR EXISTS(SELECT 1 FROM plink JOIN tagxref ON rid=cid
          WHERE tagid=11 AND tagtype>0 AND pid=blob.rid)
    OR EXISTS(SELECT 1 FROM plink JOIN tagxref ON rid=pid
          WHERE tagid=11 AND tagtype>0 AND cid=blob.rid))
 ORDER BY event.mtime DESC
 LIMIT 200;

这个查询不是特别复杂，不过，即便这样，它仍然可以替代上百行，也许是上千行处理过程代码。这个查询的要点是：向下扫描EVENT表，查找满足下列三个条件中任何一个的最新的200条提交记录：

此提交含有"trunk"标签。
此提交有个子提交含有“trunk"标签。
此提交有个父提交含有“trunk"标签。

第一个条件将显示所有主干分支上的提交，第二个和第三个条件包含合并到主干分支，或者由主干分支产生的提交。这三个条件是通过在此查询的WHERE子句中用OR连接三个EXISTS语句实现的。使用下一代查询规划器引起的性能下降是由第二个和第三个条件产生的。两个条件里存在的问题是相同的，因此我们只看第二个条件。第二个条件的子查询可以重写为如下语句（把次要的和不重要的进行了简化）：

SELECT 1
 FROM plink JOIN tagxref ON tagxref.rid=plink.cid
 WHERE tagxref.tagid=$trunk
  AND plink.pid=$ckid;

PLINK表保存着各个提交之间的父子关系。TAGXREF表把标签映射到提交上。作为参考，对这两个表进行查询的模式的相关部分显示如下：

CREATE TABLE plink(
 pid INTEGER REFERENCES blob,
 cid INTEGER REFERENCES blob
);
CREATE UNIQUE INDEX plink_i1 ON plink(pid,cid);
 
CREATE TABLE tagxref(
 tagid INTEGER REFERENCES tag,
 mtime TIMESTAMP,
 rid INTEGER REFERENCE blob,
 UNIQUE(rid, tagid)
);
CREATE INDEX tagxref_i1 ON tagxref(tagid, mtime);

实现这样的查询只有两个方法值得考虑。（当然可能还有许多其他算法，不过它们中的任何一个都不是“最佳”算法的竞争者。

查找提交$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)作为外循环，因为4.9比5.2要小，结果就是选择P-T路径，即算法1。NN算法只是在每一步查找一个最佳选择路径，因此它完全忽略了这样一个事实：5.2+4.4是比4.9+4.8性能稍稍有些好的规划。然而N3算法对着两个连接追踪了5个最佳路径，因此它最终选择了T-P路径，因为这条路径的总体资源消耗要少一些。路径T-P就是算法2。

注意：如果运行了ANALYZE，那么对资源消耗的评估就更加接近于现实，这样NN和N3都选择算法1。

（附注：最新的两图中对资源消耗的评估是下一代查询规划器使用以2为底的对数算法计算得出来的，而且与旧查询规划器相比假设的资源消耗稍微有些不同。因此，最后两个图中的资源消耗评估不能与TPC-H Q8图里的资源消耗评估进行比较。）

4.2 问题修正

对资源仓库数据库运行ANALYZE可立即修复这类性能问题。然而，无论是否对资源仓库是否进行分析，我们都要求Fossil十分强壮，而且总是能够快速地运行。基于这个原因，我们修改查询使用CROSS JOIN操作符而不使用常用的JOIN操作符。SQLite将不会对CROSS JOIN连接的表重新排序。这个功能是SQLite中长期都有的一个功能，做这么特别的设计就是允许具有丰富经验的开发人员能够强制SQLite执行特定的嵌套循环顺序。一旦某个连接更改为（增加了一个关键字的）CROSS JOIN这样的连接，下一代查询规划器就不管是否使用ANALYZE收集统计统计信息都强制选择稍稍快一点的算法1。

我们说算法1"快一些“，不过，严格来说这么说不准确。对一个常见的存储仓库运行算法1是快一些，不过，可能构建这样一种资源仓库：对资源仓库的每一次提交都是提交给不同的名字唯一的分支上，而且所有的提交都是根提交的子提交。这种情况下，TAGXREF_I1与PLINK_I1相比就具有更多的可选项了，此时算法2才真正快一些。然而实际中这样的资源仓库极不可能出现，所以使用CROSS JOIN语法硬编码嵌套循环的顺序是解决这种情形下存在问题的适合方案。

5.0 避免或者修正查询规划器问题的方法一览表

不要惊慌！查询规划器选择差的规划这种情况实际上是非常罕见的。你未必会在应用中碰到这样的问题。如果你没有性能方面问题，那么你就不必为此而担心。

创建正确的索引。大多数SQL性能问题不是因为查询规划器问题而引起的，而是因为缺少合适的索引。确保索引可以促进所有大型的查询。大多数性能问题都可以使用一个或者两个CREATE INDEX命令来解决，而不需要对应用代码进行修改。

避免创建低质量的索引。（用于解决查询规划器问题而创建的）低质量索引是这样的索引：表里的索引最左一个字段具有相同值的行超过10行或者20行。特别注意，避免使用布尔字段或或者“枚举类型”字段作为索引的最左一字段。

这篇文章的前一段所说的Fossil性能问题是因为TAGXREF表的TAGXREF_I1索引的最左一子段(TAGID字段）具有相同值得项超过1万。

如果你一定要使用低质量的索引，那么请一定要运行ANALYZE。只要查询规划器知道那个索引时低质量的，那么低质量的索引就不会让它迷惑。查询规划器知晓低质量索引的方法是通过SQLITE_STAT1表的内容来实现的，这个表示有ANALYZE命令计算得来的。

当然，ANALYZE只有在数据库一开始就拥有非常大量的内容的情况下才能够高效地运行。当你希望创建一个数据库并累积了大量数据的时候，你可以运行命令"ANALYZE sqlite_master"创建SQLITE_STAT1表，然后（使用常用的INSERT语句）向SQLITE_STAT1表中填入用来说明这样的数据库正适合你的应用的内容-也许这样的内容是你在对实验室的某个填写的非常完美的模板数据库运行ANALYZE命令后所获得的。

编写你自己的代码。增加可以让你快速且非常容易就能知道哪些查询需要很多时间，这样就只运行哪些特别不需要花太长时间的查询。

如果查询可能使用没有运行分析的数据库上的低质量索引，那么请使用CROSS JOIN语法，强制使用特定的嵌套循环顺序。SQLite对CROSS JOIN操作符进行特殊的处理，它强制左表为右表的外部循环。

如果有其他方法实现，那么就避免这么做，因为它与任何一个SQL语言理念里的强大的优点相抵触，特别是应用开发人不需要了解查询规划。如果你使用了CROSS JOIN，那么直到开发周期的后期你也要这么做，而且要在注释里仔细地说明CROSS JOIN是如何使用的，这样以后才有可能把它去掉。在开发周期的早期就避免使用CROSS JOIN,因为这么做是不成熟的优化措施，也就是众所周知的万恶之源。

使用单目运算符"+",取消WHERE子句某些限制条件。当对某个具体的查询有更高质量的索引可以使用的时候，如果查询规划器仍然坚持选择差质量的索引，那么请在WHERE子句中谨慎地使用单目运算符"+",这样做就可以强制查询规划器不使用差质量的索引。如果可能的话，就尽量小心地添加这个这个运算符，而且尤其避免在应用开发的周期的早期就使用。特别要注意：给一个与类型密切相关的等号表达式增加单目运算符"+"可能更改这个表达式的结果。

使用INDEXED BY语法，强制有问题的查询选择特定的索引。同前两个标题一样，如果可能的话，尽量避免使用这个方法，尤其避免在开发的早期这么做，因为很清楚，它是一个不成熟的优化措施。

6.0 结论

SQLite的查询规划器做这样的工作做得非常好：为正在运行的SQL语句选择快速算法。对旧查询规划器来说，这是事实，对新的下一代查询规划器来说更是这样。也许偶然会出现这样的情况：由于信息不完整，查询规划器选择了稍差的查询规划。与使用旧查询规划器相比，使用下一代查询规划器这种情形就会更少出现了，不过仍然有可能出现。即便出现了这种极少出现的情况，应用开发人员需要做的是了解和帮助查询规划器做正确的事情。通常情况下，下一代查询规划器只是对SQLite做了一个新的增强，这种增强可以让应用运行的更快些，而且不需要开发人员做更多的思考或者动作。

更多SQL内容来自木庄网络博客

标签：SQL

返回前面的内容