linux - 计算 sqlite 数据库中的行

Question

我在运行 Linux 且资源有限的 ARM 嵌入式平台上有一个 sqlite 数据库。存储设备是 microSD 卡。 SQLite 版本为 3.7.7.1。访问sqlite的应用程序是用C++编写的。

我想定期知道几个表中的行数。我目前使用

select count(*) from TABLENAME;

获取此信息。我在性能方面遇到了问题:当表大小达到某个点(~200K 行)时，每次检查表大小时我都会有很多系统和 iowait 负载。

当我写这篇文章时，我虽然查找表中的行数会很快，因为它可能存储在某个地方。但是现在我怀疑 sqlite 实际上会查看所有行，当我经过数据不再适合磁盘缓存的点时，我会得到很多 io 负载。这大致适合 db 大小和可用内存。

谁能告诉我 sqlite 的行为是否像我怀疑的那样？

有没有办法在不产生这种负载的情况下获取表行数？

编辑:plaes 询问了表格布局:

CREATE TABLE %s (timestamp INTEGER PRIMARY KEY, offset INTEGER, value NUMERIC);

Answer 1

这个表有integer索引吗？如果没有，则添加一个。否则它必须扫描整个表来计算项目数。

这是实现 COUNT() 解析和执行的 SQLite 代码的注释摘录:

    /* If isSimpleCount() returns a pointer to a Table structure, then
    ** the SQL statement is of the form:
    **
    **   SELECT count(*) FROM <tbl>
    **
    ** where the Table structure returned represents table <tbl>.
    **
    ** This statement is so common that it is optimized specially. The
    ** OP_Count instruction is executed either on the intkey table that
    ** contains the data for table <tbl> or on one of its indexes. It
    ** is better to execute the op on an index, as indexes are almost
    ** always spread across less pages than their corresponding tables.
    */
    [...]
    /* Search for the index that has the least amount of columns. If
    ** there is such an index, and it has less columns than the table
    ** does, then we can assume that it consumes less space on disk and
    ** will therefore be cheaper to scan to determine the query result.
    ** In this case set iRoot to the root page number of the index b-tree
    ** and pKeyInfo to the KeyInfo structure required to navigate the
    ** index.
    **
    ** (2011-04-15) Do not do a full scan of an unordered index.

此外，您还可以 get more information关于您使用 EXPLAIN QUERY PLAN 进行的查询。