SQLite ANALYZE разбивает индексы

У меня есть таблица, содержащая около 500 тыс. Строк. Таблица имеет индекс в столбце «статус». Поэтому я запускаю следующую команду объяснения:

EXPLAIN QUERY PLAN SELECT * FROM my_table WHERE status = 'ACTIVE' 

Результаты в предсказуемом «объяснении» …

 SEARCH TABLE my_table USING INDEX IDX_my_table_status (status=?) (~10 rows) 

После добавления в таблицу многих дополнительных строк я вызываю «ANALYZE». Впоследствии запросы казались намного медленнее, поэтому я снова запустил объяснение и теперь вижу следующее:

 SCAN TABLE my_table (~6033 rows) 

Первое, что я замечаю, это то, что BOTH оценки строк не работают. Самая большая проблема заключается в том, что индекс, кажется, пропущен после того, как ANALYZE запущен. Я попробовал REINDEX – безрезультатно. Единственный способ вернуть индексы – сбросить их, а затем восстановить их. Кто-нибудь видел это? Это ошибка? Любые идеи, что я делаю неправильно? Я пробовал это по нескольким базам данных, и я вижу те же результаты. Это на моем ПК, а на MAC и на iPhone / iPad – все те же результаты.

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

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

Информация, собранная ANALYZE , хранится в sqlite_stat1 и, возможно, sqlite_stat3 . Пожалуйста, покажите, что такое информация о вашей таблице.
Если эта информация не отражает истинное распределение ваших данных, вы можете попробовать снова запустить ANALYZE или просто удалить эту информацию из таблиц sqlite_stat* .

Вы можете принудительно пройти через индекс, если вы используете ORDER BY в индексированном поле. ( INDEXED BY , как говорится в документации, не предназначен для настройки производительности запроса).

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

Это не редкость для плана выполнения запросов, чтобы избежать использования существующего индекса в столбце с низкой мощностью, например «статус», который, вероятно, имеет только несколько отдельных значений. Часто поиск выполняется быстрее, сканируя таблицу db. (Некоторые администраторы баз данных рекомендуют никогда не индексировать столбцы с низкой мощностью).

Однако, основываясь на невероятно разнообразных подсчетах строк в плане объяснений, я предполагаю, что «анализ» SQLite аналогичен анализу MySQL при использовании механизма хранения InnoDB. «Анализ» MySQL выполняет случайный набор погружений в данные таблицы, чтобы определить количество строк, мощность индекса и т. Д. Из-за случайных погружений статистика может варьироваться после запуска каждого анализа, что приводит к разным планам выполнения запросов. Столбцы с малой мощностью еще более восприимчивы к неправильной статистике, так как, например, случайные погружения могут указывать на то, что большинство строк в вашей таблице имеют «активный» статус, что делает его более эффективным для сканирования таблицы, а не для использования индекса , (Я не эксперт по SQLite, поэтому кто-то, пожалуйста, звонит, если моя догадка о «анализе» поведения неверна).

Вы можете попробовать протестировать использование индекса в запросе, используя «проиндексированный» (см. http://www.sqlite.org/lang_indexedby.html ), хотя принудительное использование индексов обычно является последним средством. Различные РСУБД имеют разные решения проблемы с низким уровнем мощности, такие как разбиение на разделы, использование растровых индексов и т. Д. Я бы рекомендовал исследовать конкретные решения SQLite для запросов / индексирования на столбцах с низкой мощностью).