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

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万个值不同于null和只有大约600k不同的值。

我在两个表上做了一些测试，你可以看到下面的时间，我添加了“解释查询计划”的每个请求(QP)。我把数据库文件放在USB存储器上，所以我可以在每次测试后删除它，并获得可靠的结果，而不受磁盘缓存的干扰。一些请求在USB上更快(我想是由于缺乏查找时间)，但有些请求较慢(表扫描)。

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

如你所见，查询花费了相同的时间(除了查询计划现在显示实际行数)，只有较慢的选择现在也很快。

接下来，我在Table1的Key字段上创建dan extra索引，它应该对应于自动索引。我在原始数据库上做了这个，没有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)

如你所见，索引帮助计数(*)，但不是计数(Key)。

Finaly，我使??用列约束而不是表约束创建了表：

CREATE TABLE Table1 (
Key INTEGER PRIMARY KEY ASC 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。

谢谢，

马克

SQLite总是对count(*)进行全表扫描。它
不保存表上的元信息以加快速度
过程。

不保持元信息是一个故意设计
决定。如果每个表存储一个计数(或更好，每个
节点的btree存储一个count)然后更多更新
将必须发生在每个INSERT或DELETE。这个
会减慢INSERT和DELETE，即使在共同点
计数(*)速度不重要的情况。

如果你真的需要一个快速COUNT，那么你可以创建
INSERT和DELETE的触发器，用于更新运行
计数在一个单独的表中然后查询单独
表找到最新的计数。

当然，不值得保持FULL行计数，如果你需要依赖于WHERE子句的COUNT(即WHERE field1> 0和field2< 1000000000)。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!