Sqlite虽然几乎不占用机器资源，但速度较慢。

Question

我有一个500MB的SQLite数据库，大约有500万行数据，其架构如下：

CREATE TABLE my_table (
        id1 VARCHAR(12) NOT NULL,
        id2 VARCHAR(3) NOT NULL,
        date DATE NOT NULL,
        val1 NUMERIC,
        val2 NUMERIC,
        val2 NUMERIC,
        val4 NUMERIC,
        val5 INTEGER,
        PRIMARY KEY (id1, id2, date)
);

我正在尝试运行：

SELECT count(ROWID) FROM my_table

查询现在已经运行了几分钟，这对我来说似乎太长了。我知道sqlite并不是为count(*)类型的查询进行优化的。

如果我的计算机表现出色，我可以接受这一点。然而，我的CPU负载大约为0-1%。Process Explorer中的“磁盘增量总字节数”约为500,000。

有什么办法可以加快速度吗？

- ARF

---

什么操作系统？ 什么文件系统？ 有杀毒软件吗？ - CL.

SQLite不支持并发访问。它在事务开始时锁定表格。您确定没有另一个进程在同一数据库上处于空闲事务中吗？ - aruisdante

@CL Windows Server 2003 R2，Symantec Endpoint Protection - ARF

@aruisdante 这难道不意味着查询永远无法完成？在我的情况下，它确实完成了，但很慢。 - ARF

什么文件系统？这是在网络上吗？ - CL.

@CL 抱歉，文件系统是本地 NTFS。 - ARF

Answer 1

你应该为任何查询的字段建立索引，例如这样：create index tags_index on tags(tag);。然后，我确信查询一定会更快。其次，尝试规范化表格并进行测试（不建立索引）。比较结果。

- subham soni

---

我的主键有什么问题？这已经是一个索引了。据我所知，表已经在第三范式中了。 - ARF

“索引无处不在”的策略并不能加速所有操作，我怀疑在这样的表上没有where子句的计数能否被加速。” - AsTeR

Answer 2

在大多数情况下，count(*)比count(rowid)更快。

如果你有一个（非部分）索引，使用该索引计算行数会更快，因为需要从磁盘加载的数据较少。在这种情况下，主键约束已经创建了这样的索引。

- CL.

---

count(*)也有同样的问题：机器资源利用率低。我理解的没错吧，你是说我的带有主键的模式原则上是可以的？ - ARF

Answer 3

如果我是你，我会尝试查看我的磁盘IO。考虑到你的数据库的大小，一些数据必须在磁盘上，这使得磁盘成为瓶颈。

从我对SQLite的基础知识中得出两个想法。

想法1：如果内存不是问题，并且你的应用程序只启动一次并运行多个查询，我会尝试增加使用的缓存量（有一个可用的cache_size pragma）。经过一些谷歌搜索，我找到了这个关于SQLite调优的链接：http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html。

想法2：我建议使用自增的主键（在单个列上）并尝试使用SELECT COUNT(DISTINCT row_id) FROM my_table;来调整请求。这可能会强制计数仅在索引所包含的内容上运行。

- AsTeR

---

我之前的磁盘IO速度是0.5MB/s，虽然不算慢但还有很大的提升空间... 重启系统后，我的磁盘IO速度现在达到了3-4 MB/s：这已经好多了，但仍然不够理想。我不知道是什么导致了这种速度提升，也不知道为什么速度最高只能达到3-4 MB/s。我会尝试你们的建议，但必须重新启动系统以避免系统缓存造成结果失真。 - ARF