python - 在 sqlite3/Python 中的 "select"期间缓存了哪些数据，这可以从一开始就手动完成吗？

Question

假设您有一个包含数千行的 sqlite 数据库——每行都包含或引用一个相当大的、唯一的 blob——并且您想要对该集合进行稀疏采样，根据 rowid 或某个等效的主键提取行。我发现我第一次尝试在连接后(从 20k 行中)获取几个(500)个数据点时，调用需要 10 多秒才能返回；并且，随着每次连续迭代，调用变得越来越短，直到在 50-100 次此类查询后收敛到大约 100 毫秒。

显然，sqlite 或它的 python 包装器必须在缓存……某物。如果我清除不活动的内存(我在 OS X 中，但我认为 Linux 有一个类似的 if-not-identical“清除”命令？)，可以准确地复制该行为。问题是，索引没有解决的缓存是什么？此外，是否有可能从一开始就自动将加速这些查询的任何信息提取到内存中？或者还有其他我完全错过的东西吗？

一些注意事项，以防有人不能立即知道答案......

• 每个 blob 大约 40kB，是一个很大的问题源。我在下面为任何想在家一起玩的人准备了一些代码，但我更幸运地为可排序的信息和数据保留了单独的表格。这引入了一个内部联接，但它通常比将它们全部放在一起要好(尽管如果有人觉得这是错误的，我很想听听)。如果没有内部连接/数据获取，事情从 4 秒开始，然后迅速下降到 3 毫秒。

• 我觉得这可能是一个 PRAGMA 的东西，但我在网络的荒野中摆弄了其他人建议的一些设置，并没有真正看到任何好处。

• 内存数据库不是一种选择。一方面，我正在尝试跨线程共享(这对于内存中来说实际上可能不是问题......？不确定)，但更重要的是，数据库文件通常约为 17 GB。所以，这就结束了。

• 话虽如此，缓存合理数量的信息没有问题。在几十次调用之后，非事件内存无论如何都会变得有些臃肿，但我宁愿 (1) 正确地 (2) 高效地做到这一点。

好的，现在为任何想尝试复制事物的人提供一些代码。您应该能够将其复制并粘贴到独立脚本中(这基本上就是我所做的，保存格式)。

import sqlite3
import numpy as np
import time

ref_uid_index = """CREATE INDEX ref_uid_idx
                   ON data(ref_uid)"""


def populate_db_split(db_file, num_classes=10, num_points=20000, VERBOSE=False):
    def_schema_split0 = """
    CREATE TABLE main (
        uid            INTEGER PRIMARY KEY,
        name           TEXT,
        label          INTEGER,
        ignore         INTEGER default 0,
        fold           INTEGER default 0)"""

    def_schema_split1 = """
        CREATE TABLE data (
            uid            INTEGER PRIMARY KEY,
            ref_uid        INTEGER REFERENCES main(uid),
            data           BLOB)"""

    def_insert_split0 = """
        INSERT INTO main (name, label, fold)
            VALUES (?,?,?)"""

    def_insert_split1 = """
        INSERT INTO data (ref_uid, data)
            VALUES (?,?)"""

    blob_size= 5000
    k_folds = 5
    some_names = ['apple', 'banana', 'cherry', 'date']

    dbconn = sqlite3.connect(db_file)
    dbconn.execute(def_schema_split0)
    dbconn.execute(def_schema_split1)

    rng = np.random.RandomState()
    for n in range(num_points):
        if n%1000 == 0 and VERBOSE:
            print n

        # Make up some data
        data = buffer(rng.rand(blob_size).astype(float))
        fold = rng.randint(k_folds)
        label = rng.randint(num_classes)
        rng.shuffle(some_names)

        # And add it
        dbconn.execute(def_insert_split0,[some_names[0], label, fold])
        ref_uid = dbconn.execute("SELECT uid FROM main WHERE rowid=last_insert_rowid()").fetchone()[0]
        dbconn.execute(def_insert_split1,[ref_uid,data])

    dbconn.execute(ref_uid_index)
    dbconn.commit()
    return dbconn

def timeit_join(dbconn, n_times=10, num_rows=500):
    qmarks = "?,"*(num_rows-1)+"?"

    q_join = """SELECT data.data, main.uid, main.label
              FROM data INNER JOIN main ON main.uid=data.ref_uid
              WHERE main.uid IN (%s)"""%qmarks

    row_max = dbconn.execute("SELECT MAX(rowid) from main").fetchone()[0]

    tstamps = []
    for n in range(n_times):
        now = time.time()
        uids = np.random.randint(low=1,high=row_max,size=num_rows).tolist()
        res = dbconn.execute(q_join, uids).fetchall()

        tstamps += [time.time()-now]
        print tstamps[-1]

现在，如果您想复制一些东西，请执行以下操作。在我的机器上，这会创建一个 800MB 的数据库并生成如下所示的内容。

>>> db = populate_db_split('/some/file/path.db')
>>> timeit_join(db)
12.0593519211
5.56209111214
3.51154184341
2.20699000359
1.73895692825
1.18351387978
1.27329611778
0.934082984924
0.780968904495
0.834318161011

那么……知识渊博的圣人，您怎么说？

Answer 1

GB 大小的数据库文件永远不会完全加载到内存中。它们被分成所谓的页面树。这些页面缓存在内存中，默认是2000页。

您可以使用以下语句，例如将 1kB 大小的缓存页面数量翻倍。

    conn.execute("""PRAGMA cache_size = 4000""")

正如您在函数描述中所见，该连接再次拥有了最近 100 条语句的缓存:

    sqlite3.connect(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements])

cached_statements 需要整数，默认为 100。

除了设置缓存大小之外，您不太可能从应用程序启动时主动缓存语句或页面中获益。