python-3.x - SQLITE3 上的缓慢更新，python3 上带有大 blob（60MB）

Question

我正在制作一个程序来管理组件列表，我正在添加的功能之一是能够将文件添加为附件（使用 blob）。在我的测试中，我添加了一个大约 60MB 的文件，我注意到该行的 BOOL 字段的 SQlite UPDATE 比其他（大约 130k）慢得多。

做一些测试我注意到数据库文件大小是更新后的两倍，并且硬盘 LED 在更新时亮起，所以看起来 sqlite 正在用新数据复制整行然后删除旧数据，而不是只更新场。

为了测试pourposes，我创建了这个简单的测试脚本：

# -*- coding: utf-8 -*-

import sqlite3
import os
from datetime import datetime

# Connect to database
conn = sqlite3.connect(
    "test.sqlite3"
)

# Create table
with conn:
    conn.execute(
        """CREATE TABLE IF NOT EXISTS Files (
          ID INTEGER PRIMARY KEY AUTOINCREMENT,
          state BOOLEAN DEFAULT 0,
          Filedata BLOB NOT NULL
        );
        """
    )

print("{}: Adding 60MB file to database".format(datetime.now()))
try:
    with open("60MB.pdf", 'rb') as fIn:
        _blob = fIn.read()
        with conn:
            conn.execute("INSERT INTO Files VALUES (?, ?, ?);", (None, False, sqlite3.Binary(_blob)))
    print("{}: Finished".format(datetime.now()))
except Exception as e:
    print("Error: {}".format(e))


print("{}: Adding 130k file to database".format(datetime.now()))
try:
    with open("130k.pdf", 'rb') as fIn:
        _blob = fIn.read()
        with conn:
            conn.execute("INSERT INTO Files VALUES (?, ?, ?);", (None, False, sqlite3.Binary(_blob)))
    print("{}: Finished".format(datetime.now()))
except Exception as e:
    print("Error: {}".format(e))

print("{}: Updating the state column of 60MB file".format(datetime.now()))
try:
    with conn:
        conn.execute("UPDATE Files SET state = 1 WHERE ID = 1;")
        #conn.execute("UPDATE Files SET state = 0;")
    print("{}: Finished".format(datetime.now()))
except Exception as e:
    print("Error: {}".format(e))

print("{}: Same with 130k file".format(datetime.now()))
try:
    with conn:
        conn.execute("UPDATE Files SET state = 1 WHERE ID = 2;")
        #conn.execute("UPDATE Files SET state = 0;")
    print("{}: Finished".format(datetime.now()))
except Exception as e:
    print("Error: {}".format(e))

conn.close()
#os.remove("test.sqlite3")

有了这个脚本，我就有了这个运行时间：

更新 130k 行大约需要 0.01s，而 60MB 行大约需要 0.5s（类似于 INSERT 时间）。

我认为我理解这个问题（每次更新都会复制整个数据），所以我的想法是创建一个只有 ID 和 BLOB 的单独表，并使用外键链接两个表，但在此之前，我想知道我是否做错了什么以及有更好的方法。

有人知道存储大 blob 数据的更好方法吗？

谢谢，问候。

标签： python-3.xsqlite

Answer 1

Blob 的单独表是有意义的，外键与基表有关系。您看到的行为对于不是列存储的 RDBMS 来说是典型的。

看起来 sqlite 正在使用新数据复制整行，然后删除旧数据

嗯，不完全是。大多数关系数据库都会为此类 UPDATE 带来一些 ACID 事务开销，sqlite 也不例外。默认情况下它使用journal_mode=WAL，提前写日志。Sqlite 允许您打开到数据库的一对连接，并且连接 2 上的 SELECT 不应该看到来自连接 1 的修改值，直到它提交事务。这是在行级别处理的，您有非常宽的行。旧行内容被复制到日志中，稍后会发生检查点，并且该行中的布尔值接收其新值。按照您的建议，将大型 BLOB 放在单独的表中将消除对此类大量复制的需要。