sqlite - SQLite 中的 INTEGER PRIMARY KEY 与 rowid

Question

我正在尝试将一些空间数据 (OSM) 导入到 SQLite 数据库中。 SQLite 引用声明 INTEGER PRIMARY KEY 成为 rowid 的别名(如果未指定 WITHOUT ROWID)。可以肯定的是，我以两种不同的方式创建了我的主表:

CREATE TABLE points (tags BLOB NOT NULL,
                     lon INTEGER NOT NULL,
                     lat INTEGER NOT NULL)

对比

CREATE TABLE points (id INTEGER PRIMARY KEY,
                     tags BLOB NOT NULL,
                     lon INTEGER NOT NULL,
                     lat INTEGER NOT NULL)

我期望得到相同的结果，但在运行应用程序两次后，我的两个数据库文件的大小明显不同:具有显式主键的版本多占用大约 100 MB 的磁盘空间(1.5 GB 与 1.4 GB)。除了一个使用“id”，另一个使用“rowid”作为点 ID 的目标列之外，我的插入语句是相同的。

有谁知道这种巨大的尺寸差异从何而来？提前致谢。

Answer 1

似乎每行有一个字节(我认为)的 rowid 的别名会产生开销，我相信这可以解释为:-

When an SQL table includes an INTEGER PRIMARY KEY column (which aliases the rowid) then that column appears in the record as a NULL value. SQLite will always use the table b-tree key rather than the NULL value when referencing the INTEGER PRIMARY KEY column. Database File Format - 2.3. Representation Of SQL Tables.

根据以下测试，每行 1 个字节似乎非常接近:-

用两个不同的表创建了两个数据库，使用以下 SQL 加载了 1,000,000 百万行:-

对于第一个:-

DROP TABLE IF EXISTS points;
CREATE TABLE IF NOT EXISTS points (tags BLOB NOT NULL, lon INTEGER NOT NULL, lat INTEGER NOT NULL);
WITH RECURSIVE counter(tags,lon,lat) AS (SELECT x'00000000', 0,0 UNION ALL SELECT tags, random() AS lon, random() AS lat FROM counter LIMIT 1000000)
INSERT INTO points (tags,lon,lat) SELECT * FROM counter;
SELECT * FROM points;
VACUUM

对于第二个(使用 rowid 的别名):-

DROP TABLE IF EXISTS points;
CREATE TABLE IF NOT EXISTS points (id INTEGER PRIMARY KEY, tags BLOB NOT NULL, lon INTEGER NOT NULL, lat INTEGER NOT NULL);
WITH RECURSIVE counter(tags,lon,lat) AS (SELECT x'00000000', 0,0 UNION ALL SELECT tags, random() AS lon, random() AS lat FROM counter LIMIT 1000000)
INSERT INTO points (tags,lon,lat) SELECT * FROM counter;
SELECT * FROM points;
VACUUM

生成的文件大小分别为 29484Kb 和 30600Kb。

差异为 30600 - 29484 = 1,116，将其乘以 1024 = 1142784(距离 1,000,000 行、页面和可用空间可能是差异的原因不远)。

• 注意 VACUUM 命令没有任何区别(因为它们是新表，所以没有期望它们会。)