时间戳

首页 > 解决方案 > SQLITE - 无法将数千个删除语句组合成单个事务

python - SQLITE - 无法将数千个删除语句组合成单个事务

问题描述

我面临一个问题，即从单个表中删除数千行需要很长时间（14k 记录超过 2 分钟），而插入相同的记录几乎是即时的（200 毫秒）。插入和删除语句的处理方式相同 - 循环生成语句并将它们附加到列表中，然后将列表传递给打开事务的单独函数，执行所有语句，然后以提交结束。至少这是我在开始使用伪代码测试之前的印象——但看起来我误解了手动打开交易的必要性。

我已经阅读了有关事务（https://www.sqlite.org/faq.html#q19）的信息，但是由于插入几乎是即时的，所以我不确定这里是否是这种情况。

我的理解是事务 == 提交，如果这是正确的，那么看起来所有删除语句都在一个事务中 - 在处理过程中我可以看到所有已删除的行，直到最终提交，之后它们实际上被删除。即下面的常见问题链接中的情况应该不同 - 因为没有提交发生。但是缓慢的速度表明它仍在做其他事情，这会减慢速度，就好像每个删除语句都是一个单独的事务一样。

运行伪代码后，似乎在发送显式提交（通过 conn.commit()）之前未提交更改，但循环前面的“开始”或“开始事务”没有任何效果。我认为这是因为 sqlite3 在后台自动发送“开始”（将SQLite 文件合并到一个 db 文件中，以及“开始/提交”问题）

用于测试的伪代码：

import sqlite3
from datetime import datetime
insert_queries = []
delete_queries = []
rows = 30000
for i in range(rows):
    insert_queries.append(f'''INSERT INTO test_table ("column1") VALUES ("{i}");''')
for i in range(rows):
    delete_queries.append(f'''DELETE from test_table where column1 ="{i}";''')

conn = sqlite3.connect('/data/test.db', check_same_thread=False)
timestamp = datetime.utcnow().strftime("%Y-%m-%dT%H:%M:%S")
print('*'*50)
print(f'Starting inserts: {timestamp}')
# conn.execute('BEGIN TRANSACTION')
for query in insert_queries:
    conn.execute(query)
conn.commit()
timestamp = datetime.utcnow().strftime("%Y-%m-%dT%H:%M:%S")
print(f'Finished inserts: {timestamp}')

print('*'*50)
timestamp = datetime.utcnow().strftime("%Y-%m-%dT%H:%M:%S")
print(f'Starting deletes: {timestamp}')
# conn.isolation_level = None
# conn.execute('BEGIN;')
# conn.execute('BEGIN TRANSACTION;')
for query in delete_queries:
    conn.execute(query)
conn.commit()
timestamp = datetime.utcnow().strftime("%Y-%m-%dT%H:%M:%S")
print(f'Finished deletes: {timestamp}')

一件奇怪的事情是，行数以指数方式增加了删除时间（2 秒删除 10k 行，7 秒删除 20k 行，43 秒删除 50k 行），而无论行数如何，插入时间都是即时的。

编辑：最初的问题是 - 为什么删除语句与插入语句相比要花费更多时间以及如何加快它的速度，以便插入和删除行的速度相似。

根据snakecharmerb的建议，一种解决方法是这样做：

rows = 100000
delete_ids = ''
for i in range(rows):
    if delete_ids:
        delete_ids += f',"{i}"'
    else:
        delete_ids += f'"{i}"'
delete_str = f'''DELETE from test_table where column1 IN ({delete_ids});'''
conn.execute(delete_str)
conn.commit()

虽然这很可能违反所有最佳实践，但它似乎确实有效 - 删除 100 万行大约需要 2 秒。

标签： pythonsqlite

解决方案

我尝试以 50 个为一组批量删除：

...
batches = []
batch = []
for i in range(rows):
    batch.append(str(i))
    if len(batch) == 50:
        batches.append(batch)
        batch = []
if batch:
    batches.append(batch)
...

base = 'DELETE FROM test_table WHERE column1 IN ({})'
for batch in batches:
    placeholders = ','.join(['?'] * len(batch))
    sql = base.format(placeholders)
    conn.execute(sql, batch)
conn.commit()
...

这将持续时间减少到 1 - 2 秒（从最初的 6 - 8 秒）。

将这种方法与executemany1 秒的持续时间相结合。

使用查询来定义删除的列几乎是即时的

DELETE FROM test_table WHERE column1 IN (SELECT column1 FROM test_table)

但 Sqlite 可能会认识到此查询与裸查询相同DELETE FROM test_table并进行了优化。

关闭secure_delete PRAGMA似乎会使性能变得更糟。

推荐阅读