Как я могу получить более быстрые результаты запроса FTS4, упорядоченные по полю в другой таблице?

Question

Как я могу получить более быстрые результаты запроса FTS4, упорядоченные по полю в другой таблице?

Фон

Я реализую полнотекстовый поиск по телу сообщений электронной почты, хранящемуся в SQLite, используя его фантастический встроенный механизм FTS4. Я получаю довольно низкую производительность запросов, хотя и не совсем там, где я ожидал. Давайте взглянем.

Репрезентативная схема

Я приведу несколько упрощенных примеров рассматриваемого кода со ссылками на полный код, где это применимо.

У нас есть MessageTable который хранит данные о почтовом сообщении (полная версия распределена по нескольким файлам здесь, здесь и здесь):

CREATE TABLE MessageTable (
    id INTEGER PRIMARY KEY,
    internaldate_time_t INTEGER
);
CREATE INDEX MessageTableInternalDateTimeTIndex
    ON MessageTable(internaldate_time_t);

Текст с возможностью поиска добавляется в таблицу FTS4 с именем MessageSearchTable (полная версия здесь):

CREATE VIRTUAL TABLE MessageSearchTable USING fts4(
    id INTEGER PRIMARY KEY,
    body
);

id в таблице поиска действует как внешний ключ к таблице сообщений.

Я оставлю читателю в качестве упражнения вставку данных в эти таблицы (я, конечно, не могу выдать свою личную электронную почту). У меня чуть меньше 26 тысяч записей в каждой таблице.

Проблемный запрос

Когда мы получаем результаты поиска, нам нужно, чтобы они были упорядочены по убыванию internaldate_time_t так что мы можем получить только самые последние результаты. Вот пример поискового запроса (полная версия здесь):

SELECT id
FROM MessageSearchTable
JOIN MessageTable USING (id)
WHERE MessageSearchTable MATCH 'a'
ORDER BY internaldate_time_t DESC
LIMIT 10 OFFSET 0

На моей машине, с моей электронной почтой, это занимает около 150 миллисекунд, что измеряется с помощью:

time sqlite3 test.db <<<"..." > /dev/null

150 миллисекунд - не зверь запроса, но для простого поиска FTS и индексированного порядка это вяло. Если я опущу ORDER BY, это завершается за 10 миллисекунд, например. Также имейте в виду, что у фактического запроса есть еще один суб-выбор, так что в общем немного больше работы: полная версия запроса выполняется примерно за 600 миллисекунд, что находится на территории зверя, и опускается ORDER BY в этом случае время сбрасывается на 500 миллисекунд.

Если я включу статистику внутри sqlite3 и запустив запрос, я замечаю строку:

Sort Operations:                     1

Если моя интерпретация документации об этой статистике верна, похоже, что запрос полностью пропущен с использованием MessageTableInternalDateTimeTIndex, Полная версия запроса также имеет строку:

Fullscan Steps:                      25824

Похоже, он куда-то ходит по столу, но давайте пока проигнорируем это.

Что я обнаружил

Итак, давайте немного поработаем над оптимизацией. Я могу переставить запрос в подвыбор и заставить SQLite использовать наш индекс с INDEXED BY расширение:

SELECT id
FROM MessageTable
INDEXED BY MessageTableInternalDateTimeTIndex
WHERE id IN (
    SELECT id
    FROM MessageSearchTable
    WHERE MessageSearchTable MATCH 'a'
)
ORDER BY internaldate_time_t DESC
LIMIT 10 OFFSET 0

И вот, время выполнения сократилось примерно до 100 миллисекунд (300 миллисекунд в полной версии запроса, время выполнения сократилось на 50%), и о операциях сортировки не сообщалось. Обратите внимание, что просто реорганизуя запрос, как этот, но не форсируя индекс с помощью INDEXED BY есть еще операция сортировки (хотя мы все еще достаточно странно сбрили несколько миллисекунд), поэтому кажется, что SQLite действительно игнорирует наш индекс, если мы не форсируем его.

Я также попробовал некоторые другие вещи, чтобы увидеть, будут ли они иметь значение, но они этого не сделали:

Явно делая индекс DESC как описано здесь, с и без INDEXED BY
Явно добавляя id столбец в индексе, с и без internaldate_time_t приказал DESC с и без INDEXED BY
Вероятно, несколько других вещей, которые я не могу вспомнить в данный момент

Вопросы

100 миллисекунд здесь все еще кажутся ужасно медленными, потому что кажется, что это должен быть простой поиск в FTS и индексированный порядок.

Что тут происходит? Почему он игнорирует очевидный индекс, если вы не форсируете его руку?
У меня есть некоторые ограничения при объединении данных из виртуальных и обычных таблиц?
Почему это все еще относительно медленно, и есть ли что-то еще, что я могу сделать, чтобы упорядочить совпадения FTS по полю в другой таблице?

Спасибо!

16

sqlite sqlite3 fts3 fts4

Источник

user30497 24 авг '13 в 00:21

1 ответ

Решение

Другие вопросы по тегам sqlite sqlite3 fts3 fts4

user11654 24 авг '13 в 12:25 2013-08-24 12:25 · Accepted Answer · 2013-08-24 12:25

Индекс полезен для поиска строки таблицы на основе значения индексированного столбца. После того как строка таблицы найдена, индексы перестают быть полезными, поскольку поиск какой-либо строки таблицы в индексе неэффективен по любому другому критерию.

Следствием этого является то, что невозможно использовать более одного индекса для каждой таблицы, к которой осуществляется доступ в запросе.

Также см. Документацию: Планирование запросов, Оптимизатор запросов.

Ваш первый запрос имеет следующий вывод EXPLAIN QUERY PLAN:

0 0 0 SCAN TABLE MessageSearchTable VIRTUAL TABLE INDEX 4: (~0 rows)
0 1 1 SEARCH TABLE MessageTable USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0 0 0 USE TEMP B-TREE FOR ORDER BY

Что происходит то

индекс FTS используется, чтобы найти все соответствующие MessageSearchTable строки;
для каждой строки, найденной в 1., MessageTable индекс первичного ключа используется для поиска подходящей строки;
все строки, найденные в 2., отсортированы по временной таблице;
первые 10 строк возвращаются.

Ваш второй запрос имеет следующий вывод EXPLAIN QUERY PLAN:

0 0 0 SCAN TABLE MessageTable USING COVERING INDEX MessageTableInternalDateTimeTIndex (~100000 rows)
0 0 0 EXECUTE LIST SUBQUERY 1
1 0 0 SCAN TABLE MessageSearchTable VIRTUAL TABLE INDEX 4: (~0 rows)

Что происходит то

индекс FTS используется, чтобы найти все соответствующие MessageSearchTable строки;
SQLite просматривает все записи в MessageTableInternalDateTimeTIndex в порядке индекса и возвращает строку, когда id значение - это одно из значений, найденных на шаге 1. SQLite останавливается после десятой такой строки.

В этом запросе можно использовать индекс для (подразумеваемой) сортировки, но только потому, что никакой другой индекс не используется для поиска строк в этой таблице. Использование индекса таким способом подразумевает, что SQLite должен пройти через все записи, а не искать несколько строк, соответствующих какому-либо другому условию.

Когда вы опускаете INDEXED BY предложение из вашего второго запроса, вы получите следующий вывод EXPLAIN QUERY PLAN:

0 0 0 SEARCH TABLE MessageTable USING INTEGER PRIMARY KEY (rowid=?) (~25 rows)
0 0 0 EXECUTE LIST SUBQUERY 1
1 0 0 SCAN TABLE MessageSearchTable VIRTUAL TABLE INDEX 4: (~0 rows)
0 0 0 USE TEMP B-TREE FOR ORDER BY

который по сути совпадает с вашим первым запросом, за исключением того, что объединения и подзапросы обрабатываются немного по-другому.

С вашей структурой таблицы, на самом деле невозможно получить быстрее. Вы делаете три операции:

просматривая ряды в MessageSearchTable;
просматривая соответствующие строки в MessageTable;
сортировка строк по MessageTable значение.

Что касается индексов, шаги 2 и 3 конфликтуют друг с другом. База данных должна выбрать, использовать ли индекс для шага 2 (в этом случае сортировка должна выполняться явно) или для шага 3 (в этом случае она должна пройти все MessageTable записей).

Вы можете попытаться вернуть меньше записей из поиска FTS, сделав время сообщения частью таблицы FTS и выполнив поиск только в течение последних нескольких дней (и увеличив или уменьшив время, если вы не получите достаточно результатов).