Таблица SQLite требует времени для извлечения записей в запросе LIKE

Question

Таблица SQLite требует времени для извлечения записей в запросе LIKE

Сценарий: база данных sqlite (необходимо зашифровать записи в базе данных. Следовательно, используется API шифрования SQL для iOS)

В базе данных есть таблица с именем partnumber со следующей схемой:

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

Эта таблица содержит приблизительно 80 тысяч записей.

В представлении пользовательского интерфейса есть 3 текстовых поля, в которых пользователь может вводить условия поиска, и поиск выполняется, как только пользователь вводит туда буквы.

3 текстовых поля: txtFieldDescription, txtFieldMake и txtFieldModel.

Предположим, первый пользователь вводит поисковый термин как "монитор" в txtFieldDescription. Итак, запросы, которые будут выполняться с каждой буквой:

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 по-прежнему содержит "monitor"). Таким образом, пользователь нажимает на txtFieldModel. Как только пользователь нажимает на модель, запрос запускается как:

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

Этот запрос вернет все модели для записей, описание которых содержит монитор (в любой позиции).

Теперь, если пользователь хочет найти все модели, содержащие слово "sony" (поле описания по-прежнему содержит монитор), то запросы, которые будут выполняться с каждой буквой:

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 между ними, и, следовательно, время выполнения обязательно увеличится. Кроме того, использование подстановочного знака в начале подобно отменяет индексы.

НЕСКОЛЬКО ДОПОЛНИТЕЛЬНОЙ ИНФОРМАЦИИ:

Общее количество записей ~80К
Запрос 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, создает COLLATE NOCASE (всего 7129 строк). Что касается модели, то создание другой таблицы для нее не поможет, так как число строк ~64644 почти равно общему количеству записей ~82135. Но проблема с этим подходом состоит в том, что я не знаю, как я буду выполнять поиск в этих таблицах, какие столбцы должны быть в каждой из них и сколько таблиц должно быть создано. Что, если пользователь вводит некоторое описание, затем вводит модель, а затем снова вводит новое описание.
Так как результат этого запроса выбора отображается в UITableView, и пользователь видит максимум 5 строк одновременно. Таким образом, мы можем ограничить количество возвращаемых строк до 500, а когда пользователь прокручивает, можно выбрать следующие 500 и так далее до последней найденной записи.

Но проблема здесь в том, что мне нужно только 500 записей, но мне придется искать всю таблицу (SCAN ~80K записей). Итак, мне нужен запрос, который сначала будет искать только верхние 10% таблицы и возвращать верхние 500 строк из этого, затем искать следующие 500 и 10% записей, затем 10%, затем следующие 10% и 80000 записей. ведется поиск (нужно искать порциями по 10-10% записей).

Если таблицу из 80 тыс. Записей можно разбить на 4 таблицы по 20 тыс. Записей в каждой, а затем поиск выполняется по всем 4 таблицам одновременно (в разных фоновых потоках), чтобы получить набор результатов. Но здесь я не знаю, как выполнять запросы в 4 разных потоках (своего рода пакетное выполнение), когда объединять результаты и как узнать, что все потоки завершили выполнение.
Если я могу заменить как% monitor% 'другой функцией, которая возвращает тот же результат, но выполнение которого происходит быстрее, и использование этой функции не влияет на использование индекса (то есть не игнорирует использование индекса), тогда исполнение может стать быстрее. Если кто-нибудь может предложить мне такую функцию в sqlite, то я могу продолжить этот подход.

Если вы можете помочь мне реализовать любую из этих альтернатив или предложить другое решение, я смогу увеличить скорость выполнения моего запроса. И, пожалуйста, не говорите мне, чтобы включить FTS (полнотекстовый поиск) в sqlite, потому что я уже пытался сделать это, но я не знаю точных шагов. Большое спасибо за терпеливое чтение этого вопроса......

РЕДАКТИРОВАТЬ:

Привет всем, я получил некоторый успех. Я изменил свой запрос select, чтобы он выглядел так:

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-Tree для разных, которые я не хочу использовать.

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 для поиска) занимает больше времени на устройстве, чем исходный. Я пытался удалить отчетливый и упорядоченный по ключевым словам, но это было бесполезно. Все еще занимает ~8-10 секунд на iPhone. Пожалуйста, помогите мне.

1

ios sqlite query-optimization sqlcipher

Источник

user1023146 07 мар '12 в 05:01

1 ответ

Другие вопросы по тегам ios sqlite query-optimization sqlcipher

user235261 07 мар '12 в 20:41 2012-03-07 20:41 · Answer 1 · 2012-03-07 20:41

Anshul,

Я знаю, что вы сказали: "Пожалуйста, не говорите мне включить FTS (полнотекстовый поиск) в sqlite, потому что я уже пытался сделать это, но я не знаю точных шагов", однако FTS - единственный способ заставить это выполнить Что ж. Там нет магии, которая сделает полное сканирование таблицы работать хорошо. Я предлагаю почитать в FTS, потратить время на его изучение, а затем использовать его: http://sqlite.org/fts3.html.