组织具有数据密集型行的表以优化访问时间。

Question

我正在使用一个大约70千兆字节的sqlite3数据库。这个数据库有三个表:一个有大约3000万行，另外两个有1.5亿和3亿行，每个表从6-11列运行。

行最少的表占用了大部分空间，因为它包含一个压缩BLOB的原始数据列，通常每行运行1至6千字节；数据库中的所有其他列都是数字列，压缩后的数据不可变，因此修改效率不高不成问题。

我注意到在这个表的数字列上创建索引：

[15:52:36] Query finished in 723.253 second(s).

在表上创建一个具有五倍多行的可比索引需要花费几倍的时间：

[15:56:24] Query finished in 182.009 second(s).
[16:06:40] Query finished in 201.977 second(s).

是否更好的做法是将BLOB数据存储在一个单独的表中，以便使用JOIN进行访问？每一行的额外宽度是此表的慢扫描速率最有可能的候选值。

我目前的怀疑是：

1. 这主要是由于从磁盘读取数据的方式，使得跳过中等大小的数据是不切实际的，并使得操作系统从磁盘读取的每个扇区的可用数据比率非常低，并且
2. 因此，作为关系数据库的相对新手，这可能是我所不知道的标准做法，以避免将较大、可变宽度的数据与可能需要在没有索引的情况下被扫描的其他数据放在同一个表中。

但我希望有一些反馈，有更多的知识，在这一领域。

Answer 1

在SQLite文件格式中，一行中的所有列值都简单地附加在一起，并作为行值存储。如果该行太大，无法容纳到一个数据库页中，则剩余的数据将存储在链接的溢出页列表中。

当SQLite读取一行时，它只读取所需的内容，但必须从行的开头开始。

因此，当您有blob (或较大的文本值)时，应该将其移动到列列表的末尾，这样就可以读取其他列的值，而不必遍历溢出页面列表：

CREATE TABLE t (
    id INTEGER PRIMARY KEY,
    a INTEGER,
    [...],
    i REAL,
    data BLOB NOT NULL,
);

对于单个表，blob值的第一个字节仍然存储在表的数据库页中，这减少了可以存储在一个页面中的行数。

如果经常访问其他列，那么将blob移动到单独的表(不需要单独的文件)可能是有意义的。这允许数据库在读取页面时同时遍历更多行，但会增加查找blob值所需的工作量。