主页 / dba / 问题 / 313797
Accepted
g00dds
Asked: 2022-06-27 10:53:36 +0800 CST

为什么在 SQLite 中对 WHERE 键进行排序时更新更快？如何提高性能？

  • 772

我有一个大型数据库和一个文件，每行都有一个 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

1 个回答

  1. Best Answer

    行存储在数据库中的页面中，sqlite 将始终读取或写入整个页面。页面的默认大小是 4096 字节，因此如果每行是 40 字节的数据，那么一个页面最多可以包含 100 行。

    此外，如果 ID 是整数主键，则 ID 用作 rowid。这意味着该表按 ID 顺序存储在磁盘上的页面中，并且值相似的 ID 存储在同一页面中。

    为什么会发生这种情况？我认为这可能与缓存有某种关系，但我不知道这是否正常。我的意思是在真实情况下，ID 几乎是随机的，而且我知道性能没有我的那么差。

    即使它们包含在单个事务中，每个 UPDATE 也是单独执行的。这意味着对于每个 UPDATE sqlite 都必须找到包含具有指定 ID 的行的页面，读取它，修改它，然后将其写入磁盘。如果重复更新同一行，则该页面已经缓存在内存中，直到事务结束才会刷新到磁盘，因此无需读取或写入任何内容到磁盘。

    如果您以递增的 ID 顺序更新表，由于单个页面包含多行，您访问同一页面进行多达 100 次更新（在每页 100 行的示例中），然后每 100 次更新您切换到另一个页面并对其进行修改以进行其他数量的更新。即使您更新了表的所有行，每个页面也只会被读取和写入磁盘一次。

    如果您以随机 ID 顺序更新表，则每次更新很可能需要更新存储在与前一个不同页面上的行，因此 sqlite 每次都需要读取和写入不同的页面。这意味着如果您更新整个表格，您可能需要读取表格的所有页面 100 次。

    如何提高性能？特别是，如果我有一个未排序的 ID-Name 对列表。除了排序然后才写入数据库之外，还有其他方法吗？

    您可以从文件中导入数据并将其插入临时表中。临时表可以存储在内存中以获得最佳性能，并且可以按顺序完成导入，而无需检查记录是否存在或是否为新记录。然后，您可以使用单个查询来更新主表：

    PRAGMA temp_store = MEMORY;    
CREATE TEMPORARY TABLE myfile (ID INTEGER PRIMARY KEY, Name);

... import your file in table myfile

INSERT INTO users (ID, username) 
SELECT ID, Name from myfile WHERE true  -- WHERE true needed to avoid confusion with following ON
ON CONFLICT (ID) DO UPDATE SET username = excluded.username;

    INSERT 会通过一个命令将 myfile 合并到用户中，因此 sqlite 可以优化操作。当 ID 上存在重复键冲突时，INSERT 的 ON CONFLICT 子句将更新行，而不是添加新行（更多信息：http ://www.sqlite.org/draft/lang_upsert.html ）

    另请注意，如果 users 表具有与 myfile 相同的列（没有您要保留的其他列），则 INSERT 查询也可以这样编写：

    INSERT OR REPLACE INTO users (ID, username) 
SELECT ID, Name from myfile

    不同之处在于，如果表 users 中已经存在 ID，则该行将被删除并插入新行（可能会删除 myfile 中不存在的其他列）。

    • 1

相关问题