SQLite: COUNT медленнее на больших таблицах

У меня проблема с производительностью в SQLite с помощью SELECT COUNT (*) на больших таблицах.

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

У меня есть 2 таблицы:

CREATE TABLE Table1 (
Key INTEGER NOT NULL,
... several other fields ...,
Status CHAR(1) NOT NULL,
Selection VARCHAR NULL,
CONSTRAINT PK_Table1 PRIMARY KEY (Key ASC))

CREATE Table2 (
Key INTEGER NOT NULL,
Key2 INTEGER NOT NULL,
... a few other fields ...,
CONSTRAINT PK_Table2 PRIMARY KEY (Key ASC, Key2 ASC))

В таблице 1 содержится около 8 миллионов записей, а в таблице 2 - около 51 миллиона записей, а файл базы данных - более 5 ГБ.

Таблица 1 имеет еще 2 индекса:

CREATE INDEX IDX_Table1_Status ON Table1 (Status ASC, Key ASC)
CREATE INDEX IDX_Table1_Selection ON Table1 (Selection ASC, Key ASC)

"Состояние" - обязательное поле, но имеет только 6 различных значений, "Выбор" не требуется и имеет только около 1,5 миллиона значений, отличных от нуля, и только около 600 тыс. различных значений.

Я провел несколько тестов в обеих таблицах, вы можете увидеть тайминги ниже, и я добавил "пояснить план запроса" для каждого запроса (QP). Я поместил файл базы данных на USB-запоминающее устройство, чтобы я смог удалить его после каждого теста и получить надежные результаты без помех дискового кеша. Некоторые запросы быстрее на USB (я полагаю, из-за отсутствия seektime), но некоторые из них медленнее (сканирование таблицы).

SELECT COUNT(*) FROM Table1
    Time: 105 sec
    QP: SCAN TABLE Table1 USING COVERING INDEX IDX_Table1_Selection(~1000000 rows)
SELECT COUNT(Key) FROM Table1
    Time: 153 sec
    QP: SCAN TABLE Table1 (~1000000 rows)
SELECT * FROM Table1 WHERE Key = 5123456
    Time: 5 ms
    QP: SEARCH TABLE Table1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
SELECT * FROM Table1 WHERE Status = 73 AND Key > 5123456 LIMIT 1
    Time: 16 sec
    QP: SEARCH TABLE Table1 USING INDEX IDX_Table1_Status (Status=?) (~3 rows)
SELECT * FROM Table1 WHERE Selection = 'SomeValue' AND Key > 5123456 LIMIT 1
    Time: 9 ms
    QP: SEARCH TABLE Table1 USING INDEX IDX_Table1_Selection (Selection=?) (~3 rows)

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

То же самое касается Table2:

SELECT COUNT(*) FROM Table2
    Time: 528 sec
    QP: SCAN TABLE Table2 USING COVERING INDEX sqlite_autoindex_Table2_1(~1000000 rows)
SELECT COUNT(Key) FROM Table2
    Time: 249 sec
    QP: SCAN TABLE Table2 (~1000000 rows)
SELECT * FROM Table2 WHERE Key = 5123456 AND Key2 = 0
    Time: 7 ms
    QP: SEARCH TABLE Table2 USING INDEX sqlite_autoindex_Table2_1 (Key=? AND Key2=?) (~1 rows)

Почему SQLite не использует автоматически созданный индекс для первичного ключа в таблице1? И почему, когда он использует автоиндекс на Table2, это все еще занимает много времени?

Я создал те же таблицы с одним и тем же контентом и индексами на SQL Server 2008 R2, и подсчеты почти мгновенно.

В одном из приведенных ниже комментариев предлагается выполнить ANALYZE в базе данных. Я сделал, и на это ушло 11 минут. После этого я снова запустил некоторые тесты:

SELECT COUNT(*) FROM Table1
    Time: 104 sec
    QP: SCAN TABLE Table1 USING COVERING INDEX IDX_Table1_Selection(~7848023 rows)
SELECT COUNT(Key) FROM Table1
    Time: 151 sec
    QP: SCAN TABLE Table1 (~7848023 rows)
SELECT * FROM Table1 WHERE Status = 73 AND Key > 5123456 LIMIT 1
    Time: 5 ms
    QP: SEARCH TABLE Table1 USING INTEGER PRIMARY KEY (rowid>?) (~196200 rows)
SELECT COUNT(*) FROM Table2
    Time: 529 sec
    QP: SCAN TABLE Table2 USING COVERING INDEX sqlite_autoindex_Table2_1(~51152542 rows)
SELECT COUNT(Key) FROM Table2
    Time: 249 sec
    QP: SCAN TABLE Table2 (~51152542 rows)

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

Далее я создаю дополнительный индекс в поле Key таблицы 1, который должен соответствовать автоиндексу. Я сделал это в исходной базе данных без данных ANALYZE. Для создания этого индекса потребовалось более 23 минут (помните, что это на USB-накопителе).

CREATE INDEX IDX_Table1_Key ON Table1 (Key ASC)

Затем я снова запускал тесты:

SELECT COUNT(*) FROM Table1
    Time: 4 sec
    QP: SCAN TABLE Table1 USING COVERING INDEX IDX_Table1_Key(~1000000 rows)
SELECT COUNT(Key) FROM Table1
    Time: 167 sec
    QP: SCAN TABLE Table2 (~1000000 rows)
SELECT * FROM Table1 WHERE Status = 73 AND Key > 5123456 LIMIT 1
    Time: 17 sec
    QP: SEARCH TABLE Table1 USING INDEX IDX_Table1_Status (Status=?) (~3 rows)

Как вы можете видеть, индекс помог с count (*), но не с count (ключ).

Наконец, я создал таблицу, используя ограничение столбца вместо ограничения таблицы:

CREATE TABLE Table1 (
Key INTEGER PRIMARY KEY ASC NOT NULL,
... several other fields ...,
Status CHAR(1) NOT NULL,
Selection VARCHAR NULL)

Затем я снова запускал тесты:

SELECT COUNT(*) FROM Table1
    Time: 6 sec
    QP: SCAN TABLE Table1 USING COVERING INDEX IDX_Table1_Selection(~1000000 rows)
SELECT COUNT(Key) FROM Table1
    Time: 28 sec
    QP: SCAN TABLE Table1 (~1000000 rows)
SELECT * FROM Table1 WHERE Status = 73 AND Key > 5123456 LIMIT 1
    Time: 10 sec
    QP: SEARCH TABLE Table1 USING INDEX IDX_Table1_Status (Status=?) (~3 rows)

Хотя планы запросов совпадают, времена намного лучше. Почему это?

Проблема заключается в том, что ALTER TABLE не позволяет преобразовать существующую таблицу, и у меня есть много существующих баз данных, которые я не могу преобразовать в эту форму. Кроме того, использование табличного ограничения вместо ограничения таблицы не будет работать для Table2.

Кто-нибудь знает, что я делаю неправильно и как решить эту проблему?

Я использовал System.Data.SQLite версии 1.0.74.0 для создания таблиц и для запуска тестов я использовал SQLiteSpy 1.9.1.

Спасибо,

Марк

Ответ 1

Если у вас нет DELETE d любых записей, выполните:

SELECT MAX(_ROWID_) FROM "table" LIMIT 1;

Во избежание полноэкранного сканирования. Обратите внимание, что _ROWID_ является идентификатором SQLite.

Ответ 2

Из http://old.nabble.com/count (*) -slow-td869876.html

SQLite всегда выполняет полное сканирование таблицы для count (*). Это
не сохраняет метаинформацию о таблицах, чтобы ускорить это обрабатывать.

Не хранить метаинформацию - преднамеренный дизайн
решение. Если в каждой таблице хранится счет (или, лучше, каждый node btree хранит счет), то гораздо больше обновлений
должны возникать на каждом INSERT или DELETE. Это
замедлит INSERT и DELETE, даже в обычном режиме случай, когда скорость count (*) не имеет значения.

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

Конечно, это не стоит держать FULL количество строк, если вы нужны COUNT, зависящие от предложений WHERE (то есть WHERE field1 > 0 и field2 < 1000000000).

Ответ 3

Не считайте звезды, считайте записи! Или на другом языке, никогда не выпускайте

SELECT COUNT (*) FROM tablename;

использовать

SELECT COUNT (ROWID) FROM tablename;

Вызвать EXPLAIN QUERY PLAN для обоих, чтобы увидеть разницу. Убедитесь, что у вас есть индекс, содержащий все столбцы, упомянутые в предложении WHERE.

Ответ 4

Это может не сильно помочь, но вы можете запустить команду ANALYZE для восстановления статистики о вашей базе данных. Попробуйте запустить "ANALYZE;", чтобы перестроить статистику всей базы данных, затем снова запустите запрос и посмотрите, не работает ли он быстрее.

Ответ 5

В связи с ограничением столбца SQLite отображает столбцы, которые объявлены как INTEGER PRIMARY KEY для внутреннего идентификатора строки (что, в свою очередь, допускает ряд внутренних оптимизаций). Теоретически, он мог бы сделать то же самое для отдельно объявленного ограничения первичного ключа, но, похоже, это не делается на практике, по крайней мере, с использованием используемой версии SQLite. (System.Data.SQLite 1.0.74.0 соответствует базовому SQLite 3.7.7.1. Возможно, вам захочется повторить проверку ваших данных с помощью 1.0.79.0, вам не нужно менять свою базу данных, чтобы сделать это, только в библиотеке.)

Ответ 6

Вывод для быстрых запросов начинается с текста "QP: SEARCH". В то время как те, для медленных запросов, начинаются с текста "QP: SCAN", который предполагает, что sqlite выполняет проверку всей таблицы, чтобы сгенерировать счет.

Googling для "подсчета сканирования sqlite table" находит следующее, что говорит о том, что использование полного сканирования таблицы для извлечения счета - это просто путь sqlite работает, и поэтому, вероятно, неизбежно.

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

выберите 1, где status = 1 союз выберите 1, где status = 2 ...

затем подсчитайте строки в результате. Это явно некрасиво, но может работать, если он убеждает sqlite запускать запрос как поиск, а не сканирование. Идея возвращения "1" каждый раз заключается в том, чтобы избежать накладных расходов на возврат реальных данных.

Ответ 7

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

Предполагая, что у вас есть столбец date_created (или может добавить один), запустите запрос в фоновом режиме каждый день в полночь (скажем, в 00:05) и сохраняйте значение где-то вместе с последней обновленной датой, которую он подсчитал (я буду вернемся к этому немного).

Затем, работая с вашим столбцом date_created (с индексом), вы можете избежать полного сканирования таблицы, выполнив такой запрос, как SELECT COUNT (*) FROM TABLE WHERE date_updated > "[TODAY] 00:00:05".

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

Единственное уловка заключается в том, что с 12:05 до 12:07 (продолжительность, в течение которой выполняется ваш общий запрос количества) у вас есть условие гонки, которое вы можете проверить last_updated значение вашего полного счета сканирования таблицы(). Если это > 24 часа, то ваш инкрементный запрос счета должен вытащить полный дневной счет плюс время, прошедшее сегодня. Если это & lt; 24-часовое, то ваш инкрементный запрос счета должен вытащить частичный счет дня (только время, прошедшее сегодня).

Ответ 8

У меня была такая же проблема, в моей ситуации помогла команда VACUUM. После его выполнения в базе данных скорость COUNT (*) увеличилась почти в 100 раз. Однако сама команда нуждается в нескольких минутах в моей базе данных (20 миллионов записей). Я решил эту проблему, выполнив VACUUM, когда мое программное обеспечение выходит после разрушения главного окна, поэтому задержка не создает проблем для пользователя.