我在SQLite中遇到了一个性能问题，在一个大表上使用SELECT COUNT（*）。
由于我还没有得到一个有用的答案，我做了一些进一步的测试，我编辑了我的问题，以纳入我的新发现。
我有两张table：

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))

字符串
Table1大约有800万条记录，Table2大约有5100万条记录，数据库文件超过5GB。
Table1还有2个索引：

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

型
“状态”是必填字段，但只有6个不同的值，“选择”不是必填字段，只有大约150万个与空值不同的值，并且只有大约60万个不同的值。
我对这两个表都做了一些测试，您可以在下面看到时间，我为每个请求（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秒）。
表2也是如此：

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不使用在Table1的主键上自动创建的索引？为什么当他在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)

型
正如您所看到的，查询花费了相同的时间（除了查询计划现在显示的是真实的的行数），只是较慢的select现在也较快。
接下来，我在Table1的Key字段上创建一个额外的索引，它应该与自动索引相对应。我是在原始数据库上做的，没有ANALYZE数据。创建这个索引花了23分钟（请记住，这是在一个U盘上）。

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)

型
如您所见，索引有助于计数（*），但对计数（Key）没有帮助。
最后，我使用列约束而不是表约束创建了表：

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来创建这些表，并使用SQLiteSpy1.9.1运行测试。
谢谢你，
马克