ios - SQLite表需要时间来获取LIKE查询中的记录

Question

场景：数据库为sqlite（需要对数据库中的记录进行加密。因此使用了用于iOS的SQL密码API）

数据库中有一个名为partnumber的表，具有以下模式：

CREATE TABLE partnumber (
  objid varchar PRIMARY KEY,
  description varchar,
  make varchar,
  model varcha,
  partnumber varchar,
  SSOKey varchar,
  PMOKey varchar
)

该表包含大约80K记录。

UI视图中有3个文本字段，用户可以在其中输入搜索词，并在其中输入字母后立即进行搜索。

3个文本字段是：txtFieldDescription，txtFieldMake和txtFieldModel。

假设，第一个用户在txtFieldDescription中输入搜索词“ monitor”。因此，将对每个字母执行的查询为：

1。

    SELECT DISTINCT description COLLATE NOCASE
    FROM partnumber where description like ‘%m%’

2。

    SELECT DISTINCT description COLLATE NOCASE
    FROM partnumber where description like ‘%mo%’

3。

    SELECT DISTINCT description COLLATE NOCASE
    FROM partnumber where description like ‘%mon%’

4。

    SELECT DISTINCT description COLLATE NOCASE
    FROM partnumber where description like ‘%moni%’

5，

    SELECT DISTINCT description COLLATE NOCASE
    FROM partnumber where description like ‘%monit%’

6。

    SELECT DISTINCT description COLLATE NOCASE
    FROM partnumber where description like ‘%monito%’

7

    SELECT DISTINCT description COLLATE NOCASE
    FROM partnumber where description like ‘%monitor%’

到目前为止，一切都很好。现在假设用户要搜索模型（txtFieldDescription仍然包含“显示器”）。因此，用户单击txtFieldModel。用户单击模型后，将触发以下查询：

SELECT DISTINCT model COLLATE NOCASE
FROM partnumber where description like ‘%monitor%’

该查询将返回描述包含监视器（在任何位置）的记录的所有模型。

现在，如果用户要搜索所有包含单词“ sony”的模型（描述字段仍包含Monitor），则将对每个字母执行的查询为：

1。

    SELECT DISTINCT model COLLATE NOCASE
    FROM partnumber WHERE model like ‘%s%’ AND description like ‘%monitor%’

2。

    SELECT DISTINCT model COLLATE NOCASE
    FROM partnumber WHERE model like ‘%so%’ AND description like ‘%monitor%’

3。

    SELECT DISTINCT model COLLATE NOCASE
    FROM partnumber WHERE model like ‘%son%’ AND description like ‘%monitor%’

4。

    SELECT DISTINCT model COLLATE NOCASE
    FROM partnumber WHERE model like ‘%sony%’ AND description like ‘%monitor%’

现在，如果用户单击txtFieldMake并输入搜索词“ 1980”，则触发的查询为：

1。

    SELECT DISTINCT make COLLATE NOCASE
    FROM partnumber WHERE make like ‘%1%’
      AND model like ‘%sony%’ AND description like ‘%monitor%’

2。

    SELECT DISTINCT make COLLATE NOCASE
    FROM partnumber WHERE make like ‘%19%’
      AND model like ‘%sony%’ AND description like ‘%monitor%’

3。

    SELECT DISTINCT make COLLATE NOCASE
    FROM partnumber WHERE make like ‘%198%’
      AND model like ‘%sony%’ AND description like ‘%monitor%’

4。

    SELECT DISTINCT make COLLATE NOCASE
    FROM partnumber WHERE make like ‘%1980%’
      AND model like ‘%sony%’ AND description like ‘%monitor%’

在此，从txtFieldDescription过渡到txtFieldModel或txtFieldModel过渡到txtFieldMake的时间过长，并且在txtFieldModel和txtFieldMake中，输入的字母会在5或6秒（显示在处理完查询后）后显示，因此光标会停在那里。

经过分析，我发现在like关键字（如“％monitor％”）中的搜索字词之前的通配符会减慢执行速度。并且在这种情况下，可能会有多达3个类似的关键字，且关键字之间带有AND，因此，执行时间肯定会增加。同样，在like开头使用通配符会否定索引。

一些其他信息：


记录总数〜80K
SELECT查询每次在表部件号上运行（〜80K）
我执行的一些查询的结果：

Sqlite> SELECT count(DISTINCT description COLLATE NOCASE) from partnumber;
    Result is: 2599

Sqlite> SELECT count(DISTINCT make COLLATE NOCASE) from partnumber;
    Result is: 7129

Sqlite> SELECT count(DISTINCT model COLLATE NOCASE) from partnumber;
    Result is: 64644

Sqlite> SELECT count(objid) from partnumber;
    Result is: 82135

索引创建如下：

CREATE INDEX index_description
ON partnumber (description collate nocase)

CREATE INDEX index_make
ON partnumber (make collate nocase)

CREATE INDEX index_model
ON partnumber (model collate nocase)

一些提高性能的替代方法：


由于不重复描述的计数只有2599个，而make的计数只有7129个，因此该表可以拆分为不同的表，其中一个包含DISTINCT描述COLLATE NOCASE输出（总共2599行），另一个包含DISTINCT make COLLATE NOCASE（总计7129行）。就模型而言，为其创建不同的表将无济于事，因为〜64644的行数几乎等于〜82135的总记录数。
但是这种方法的问题在于，我不知道如何在这些表中进行搜索，每个表中必须有哪些列以及必须创建多少个表。如果用户输入一些描述，然后输入模型，然后再次输入新描述，该怎么办？
由于此选择查询的结果显示在UITableView中，因此用户一次最多只能看到5行。因此，我们可以将返回的行数限制为500，并且当用户滚动时，可以提取下一个500，依此类推，直到最后搜索的记录为止。


但是这里的问题是尽管我只需要500条记录，但是我将不得不搜索整个表（SCAN〜80K条记录）。因此，我需要一个查询，该查询将首先仅搜索表的前10％并从中返回前500行，然后全部搜索下500个直到前10％的记录，然后搜索下10％，然后再搜索下10％直到80000条记录被搜索（需要搜索10-10％的记录块）。


如果一个80K记录的表可以分为4个20K记录的表，然后同时对所有4个表（在不同的后台线程中）执行搜索以获取结果集。但是这里我不知道如何在4个不同的线程中运行查询（某种批处理执行），何时组合结果以及如何知道所有线程已完成执行。
如果我可以用另一个返回相同结果但执行速度更快的函数代替％monitor％'，并且该函数的使用不影响索引的使用，（即不绕过索引的使用） ，则执行速度可能会更快。如果有人可以在sqlite中向我建议这样的功能，那么我可以继续使用这种方法。


如果您可以帮助我实现这些替代方法中的任何一个，或者可以为我提供任何其他解决方案，那么我将能够提高查询的执行速度。并且请不要告诉我在sqlite中启用FTS（全文搜索），因为我已经尝试过执行此操作，但是我不知道确切的步骤。非常感谢您耐心阅读此问题...

编辑：

嘿，我取得了一些成功。我修改了选择查询，使其看起来像这样：

select distinct description collate nocase as description from partnumber where rowid BETWEEN 1 AND (select max(rowid) from partnumber) AND description like '%a%' order by description;

而且，宾果游戏的搜索时间前所未有。但是现在的问题是，当我像这样执行命令EXPLAIN QUERY PLAN时，它向我展示了使用B树来区分我不想使用的东西。

explain query plan select distinct description collate nocase as description from partnumber where rowid BETWEEN 1 AND (select max(rowid) from partnumber) AND description like '%a%' order by description;

输出：

0|0|0|SEARCH TABLE partnumber USING INTEGER PRIMARY KEY (rowid>? AND rowid<?) (~15625 rows)
0|0|0|EXECUTE SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE partnumber USING INTEGER PRIMARY KEY (~1 rows)
0|0|0|USE TEMP B-TREE FOR DISTINCT

编辑：

对不起大家。上述方法（使用rowid进行搜索）在设备上花费的时间比原始方法花费的时间更多。我曾尝试通过关键字删除distinct和order，但这没有用。在iPhone上仍需要约8-10秒的时间。请帮我。

Answer 1

安舒尔

我知道您说过“请不要告诉我在sqlite中启用FTS（全文搜索），因为我已经尝试过执行此操作，但是我不知道确切的步骤”，但是FTS是使此功能执行的唯一方法好。没有任何魔术可以使全表扫描性能良好。我建议阅读FTS，花点时间学习它，然后使用它：http://sqlite.org/fts3.html。