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.

Спасибо,

Марк

Ответы

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