我有一个巨大的表,其中充满了来自其他几个表(有点像“物化视图”)的信息,这些表可以包含超过200万行,具体取决于用户的设置方式.当用户想要搜索某个项目时,该应用程序会对这个具有索引列的大型表以及用作过滤器和/或元数据的其他列执行查询.我将在下面发布查询和基本想法.无论如何,这个查询通常在iPad第4代上返回2~3秒,不超过这个,这很好.每次用户点击按钮以将其数据与我们的服务器同步时,都会删除,重新创建和填充此表.
但是,最近同一个查询在同一个表中(根本没有相关变化),随机开始需要40~50秒.如果你以后在相同的设备上使用相同的过滤器(甚至更改过滤器！)执行相同的操作,同一个表上的相同查询将再次花费2~3秒.我没有发现导致这种减速的任何特定情况,该应用程序是当时唯一运行的应用程序.设备不是问题,我们已经看到这种情况发生在至少5个不同的iPad上,一个是iPad 3,另一个是iPad第四代.
我认为这不是某种缓存,因为应用程序不会缓存任何内容,而且这些时间相当随机.有时它们连续10次需要40秒,然后突然再次开始只需2秒,而另一方面也是如此.对我来说唯一清楚的是,这种减速只发生在密集使用后(使用应用程序工作1-2天),所以我也遇到麻烦导致我在iPad上调试时出现这种情况.
我尝试过的：
>将仪器连接到过程并检查减速期间正在使用的资源.该应用程序在整个过程中都会使用iPad的“磁盘”(闪存).我现在没有再分析它的例子,但我认为cpu使用率约为30％. RAM使用率稳定在90~100MB,这对我们的应用来说是正常的.
>在db上运行VACCUM; – 在我所拥有的数据库上减少了大约50MB.从大约600MB到大约550MB.
>在db上运行ANALYZE; – 没有看到任何改进
>在db上运行REINDEX; – 似乎有点帮助,但它没有解决问题.
>杀死过程并重新开始 – 没有任何变化
巨大的表构造如下,并且没有任何外键或其他任何约束：
CREATE TABLE FMV_CATALOG(
UNIQUE_ID TEXT,
PRODUCT_ID INTEGER,
<bunch of Metadata/filtered columns – total of 20 columns>
);
并且查找产品的查询是：
SELECT
PRODUCT_ID
,UNIQUE_ID
<all other required columns,~20 columns>
FROM
FMV_CATALOG
WHERE
UNIQUE_ID = ‘<some id>_<other id>’
AND PRODUCT_NAME LIKE ‘%iPhone%’
<and other optional,rarely used,filters.>
我完全没有想法,所以任何帮助都将受到赞赏.
谢谢！
更新(更多信息)：
我忘了提到的重要信息,Rob让我想起了它.我的数据库连接始终处于打开状态,仅在用户注销时才会关闭.当我们保持连接打开时,我们注意到应用程序的所有部分都有巨大的性能,因为我们有数百个小查询在其他情况下执行(但在浏览/搜索产品目录时却没有).
用于创建索引的查询如下：
CREATE INDEX IDX_MV_CATALOG ON MV_CATALOG(UNIQUE_ID);
此外,即使该列名为UNIQUE_ID,它也不是唯一的.它应该是最初的,但现在重复了N次.我知道这是错的,我们会尽快改变.
通过连接两个其他表的ID来填充此“UNIQUE_ID”(这不是唯一的).这样,当用户搜索我们的目录时,我们的“物化视图”消除了至少三个连接的需要,这将查询时间从大约20秒提高到大约2秒.
我们不直接在查询上调用sqlite3 API,我们已经开发了一个包装类,我们已经使用它至少2年了.这是我们第一次遇到这种情况,但这是我们第一次处理这么多数据.
>您没有向我们展示FMV_CATALOG上任何索引的创建.如果没有别的,如果名称UNIQUE_ID是唯一的,那么我倾向于使用PRIMARY KEY定义表：
CREATE TABLE FMV_CATALOG(
UNIQUE_ID TEXT PRIMARY KEY,PRODUCT_ID INTEGER,<bunch of Metadata/filtered columns - total of 20 columns>
);
>您应该尝试使用sqlite EXPLAIN QUERY PLAN命令来查看查询并查看其计划并确保它使用您的索引.按原样执行此操作,然后再使用PRIMARY KEY(也许如果仍然不执行此操作,WHERE子句中的字段的索引),并确保最终查询肯定使用您的索引.
>我不确定为什么,如果你有独特的id,为什么你也在看其他领域.如果添加主键(可能还有其他索引)无法解决问题,我可能会尝试根据唯一ID检索记录,然后检查代码中是否符合其他参数.我不相信你需要这样做,但这是最糟糕的情况.
就其减速的原因而言,如果没有看到代码(我确信在一个简单的S.O.问题中分享太复杂了),就更难猜测是怎么回事.例如,如果您在sqlite3_prepare_v2语句之后未能使用sqlite3_finalize,或者您无意中关闭数据库然后在其他地方再次打开它,我可能会想到奇怪的行为.如果sqlite3调用的顺序不正确,我可以想象可能出现的性能问题.使用像FMDB这样的东西可以最大限度地减少发生这类问题的机会(以及简化sqlite代码).或者,如果这是一个过于激进的步骤,尝试编写自己的调用sqlite调用的宏,还要记录您调用sqlite3函数的事实,然后倒过该日志并仔细检查sqlite调用的顺序.
我唯一可以建议的是你是否可以构建一个可以重现异常行为的简化项目.追踪Heisenbug可能会令人愤怒：除非您能够始终如一地重现该错误,否则很难追踪.
