python - 为什么插入速度随着数据库的增长而减慢？

Question

我正在做一个生成大量数据的个人项目，我认为将其存储在本地数据库中是有意义的。但是，随着数据库的增长，我发现速度急剧下降，这使得它无法运行。

我做了一个简单的测试来展示我在做什么。我制作了一本字典，我在其中进行了一系列本地处理(大约 100 万个条目)，然后将其批量插入到 SQLite 数据库中，然后循环并再次执行所有操作。这是代码:

from collections import defaultdict
import sqlite3
import datetime
import random

def log(s):
    now = datetime.datetime.now()
    print(str(now) + ": " + str(s))

def create_table():
    conn = create_connection()
    with conn:
        cursor = conn.cursor()

        sql = """
            CREATE TABLE IF NOT EXISTS testing (
                test text PRIMARY KEY,
                number integer
            );"""
        cursor.execute(sql)
    conn.close()

def insert_many(local_db):
    sql = """INSERT INTO testing(test, number) VALUES(?, ?) ON CONFLICT(test) DO UPDATE SET number=number+?;"""

    inserts = []
    for key, value in local_db.items():
        inserts.append((key, value, value))

    conn = create_connection()
    with conn:
        cursor = conn.cursor()
        cursor.executemany(sql, inserts)
    conn.close()

def main():
    i = 0
    log("Starting to process records")
    for i in range(1, 21):
        local_db = defaultdict(int)
        for j in range(0, 1000000):
            s = "Testing insertion " + str(random.randrange(100000000))
            local_db[s] += 1
        log("Created local DB for " + str(1000000 * i) + " records")
        insert_many(local_db)
        log("Finished inserting " + str(1000000 * i) + " records")

def create_connection():
    conn = None
    try:
        conn = sqlite3.connect('/home/testing.db')
    except Error as e:
        print(e)

    return conn

if __name__ == '__main__':
    create_table()
    main()

这运行了一秒钟，然后像疯了似的慢了下来。这是我刚刚得到的输出:

2019-10-23 15:28:59.211036: Starting to process records
2019-10-23 15:29:01.308668: Created local DB for 1000000 records
2019-10-23 15:29:10.147762: Finished inserting 1000000 records
2019-10-23 15:29:12.258012: Created local DB for 2000000 records
2019-10-23 15:29:28.752352: Finished inserting 2000000 records
2019-10-23 15:29:30.853128: Created local DB for 3000000 records
2019-10-23 15:39:12.826357: Finished inserting 3000000 records
2019-10-23 15:39:14.932100: Created local DB for 4000000 records
2019-10-23 17:21:37.257651: Finished inserting 4000000 records
...

如您所见，前一百万次插入需要 9 秒，接下来的一百万次需要 16 秒，然后激增到 10 分钟，然后是 1 小时 40 分钟(!)。是不是我正在做的一些奇怪的事情导致了这种疯狂的减速，或者这是 sqlite 的限制？

Answer 1

(更多的是扩展评论而不是答案)

SQLite 只支持 BTree 索引。对于可能具有不同长度的字符串，树存储行 ID。读取树的复杂度为 O(log(n))，其中 n 是表的长度，但是，它会乘以从表中读取和比较字符串值的复杂度。因此，除非有充分的理由，否则最好将整数字段作为主键。

在这种情况下，使情况变得更糟的是，您要插入的字符串具有相当长的共享前缀(“测试插入”)，因此搜索第一个不匹配项需要更长的时间。

加速建议，按预期效果大小排序:

• 真正的数据库(MariaDB、Postgres)支持哈希索引，这将解决这个问题。
• 禁用自动提交(跳过不必要的磁盘写入；非常昂贵)
• 反转文本字符串(固定文本之前的数字)，甚至只保留数字部分
• 使用批量插入(一条语句中的多条记录)

@peak 的回答通过不使用索引避免了整个问题。如果根本不需要索引，这绝对是一种方法。