主页 / user-254691

g00dds's questions

Martin Hope
g00dds
Asked: 2022-06-27 10:53:36 +0800 CST
  • 0

我有一个大型数据库和一个文件，每行都有一个 ID-Name 对，其中 ID 对应于数据库中表的主键。我需要使用相应的 ID 将文件中的所有名称写入数据库。当然，如果表中有这样的ID（如果没有该ID的用户，则不要插入新记录，只需更新现有的）。

为此，我编写了一个简单的 python 脚本。我意识到它工作得非常慢。比 UPDATE （甚至不是 INSERT）要慢得多。ID 是一个主键，所以它必须被自动索引并且它不应该减慢更新，对吧？

然后我开始试验该脚本，发现当 ID 为常数或递增时，UPDATE 的速度要快得多。实验脚本如下：

import sqlite3
import traceback
import time
import random

conn = sqlite3.connect('database.sqlite')

c = conn.cursor()
c.execute('BEGIN')

PERIOD_SEC = 3

prev_tp = 0
counter = 0

try:
    while True:
        tp = time.time()
        elapsed_sec = tp - prev_tp
        if elapsed_sec > PERIOD_SEC:
            prev_tp = tp

            speed = counter / elapsed_sec

            print('Speed: %.02f/sec' % (speed))
            counter = 0

        c.execute('UPDATE users SET username = ? WHERE id = ?', ('random', 555))
        counter += 1


    c.execute('COMMIT')
except Exception:
    c.execute('ROLLBACK')
    traceback.print_exc()

的值username不会影响性能，所以让它一直是random。在上面的示例中，ID 是恒定的并且等于555（该值无关紧要，对于较大的 ID 值，速度也相同）。对于此示例，速度为（每秒更新数）：

Speed: 376665.88/sec
Speed: 404738.08/sec
Speed: 404942.04/sec
Speed: 403681.24/sec

如果我更换

c.execute('UPDATE users SET username = ? WHERE id = ?', ('random', 555))

和

c.execute('UPDATE users SET username = ? WHERE id = ?', ('random', inc))

whereinc在每次 UPDATE 后增加 1，那么结果将是：

Speed: 233977.17/sec
Speed: 229630.93/sec
Speed: 223424.88/sec
Speed: 218353.56/sec

情况更糟，但还没有那么糟。

但是..如果我使用随机 ID

c.execute('UPDATE users SET username = ? WHERE id = ?', ('random', random.randint(0, 1000000000)))

然后结果变得令人失望：

Speed: 514.18/sec
Speed: 732.49/sec
Speed: 886.28/sec
Speed: 999.34/sec

慢了100多倍！我还注意到我选择的 ID 范围越大，它变得越慢，但该范围内的 ID 始终存在于表中。

所以我的问题是：

  1. 为什么会发生这种情况？我认为这可能与缓存有某种关系，但我不知道这是否正常。我的意思是在真实情况下，ID 几乎是随机的，而且我知道性能没有我的那么差。
  2. 如何提高性能？特别是，如果我有一个未排序的 ID-Name 对列表。除了排序然后才写入数据库之外，还有其他方法吗？

提前致谢！

performance update