python - 加快 MySQL 更新/插入语句

Question

如何从速度的角度改进以下功能？理想情况下，我希望将 executemany 用作此向上过程的一部分。

虽然该功能工作正常，但我确信有更有效的方法来做到这一点；检查值是否存在并根据需要更新/插入。

我需要每天对数百万数据执行此操作。

def insert_or_update(self, profile_id, landing_page, keyword, position, impressions, clicks, ctr):
    ''' checks if a entry exists, if not it's inserted. If it is, the metrics
    are updated.
    '''
    try:
        self.cursor.execute('select id, position, impressions, clicks, ctr from temp where profile_id={} and keyword="{}" and landing_page="{}"'.format(profile_id, keyword, landing_page))
        data = self.cursor.fetchone()
        if data:
            row_id = data[0]
            sql_impressions = data[2] + impressions
            sql_clicks = data[3] + clicks
            sql_ctr = sum([data[4], ctr]) / len([data[4], ctr])
            # if the keyword/landing_page exists
            self.cursor.execute("update temp set position={}, impressions={}, clicks={}, ctr={} where id={}".format(position, sql_impressions, sql_clicks, sql_ctr, row_id))
            # Commit your changes in the database
            self.db.commit()
            return self.cursor.lastrowid
        else:
            # if the keyword/landing_page doesn't exist
            self.cursor.execute("insert into temp (profile_id, landing_page, keyword, position, impressions, clicks, ctr) values (%s, %s, %s, %s, %s, %s, %s)", (profile_id, landing_page, keyword, position, impressions, clicks, ctr))
            # Commit your changes in the database
            self.db.commit()
            return self.cursor.lastrowid
    except Exception as e:
        return e
        # Rollback in case there is any error
        self.db.rollback()
    finally:
        self.db.close()

Answer 1

如果您需要执行此操作数百万次，则会出现大量性能问题。

• 您正在一遍又一遍地准备相同的 SQL 语句，数百万次。准备一次并执行数百万次会更好。

• 您在单个查询后的每个函数调用中都断开了与数据库的连接。这意味着您每次都需要重新连接，并且所有缓存的信息都会被丢弃。不要那样做，保持连接状态。

• 您在每一行之后都做出了 promise 。这会减慢速度。相反，在完成一批后提交。

• 选择 + 更新或插入可能可以作为单个更新插入来完成。

• 您向临时表中插入这么多内容可能是性能问题。

• 如果表中的索引太多会减慢插入速度。有时最好删除索引，进行大批量更新，然后重新创建它们。

• 因为您将值直接放入 SQL 中，所以您的 SQL 对 SQL injection attack 开放.

---

相反...

• 使用预处理语句和绑定(bind)参数
• 保持数据库连接
• 批量更新
• 仅在更新运行结束时提交
• 在 UPDATE 而不是 SELECT + math + UPDATE 中完成所有数学运算。
• 使用“UPSERT”代替SELECT，然后使用UPDATE 或INSERT

首先，准备语句。这些让 MySQL 编译语句一次，然后重新使用它。这个想法是你写一个带有值占位符的语句。

select id, position, impressions, clicks, ctr
from temp
where profile_id=%s and
      keyword=%s and 
      landing_page=%s

然后将值作为参数而不是字符串的一部分执行。

self.cursor.execute(
   'select id, position, impressions, clicks, ctr from temp where profile_id=%s and keyword=%s and landing_page=%s',
   (profile_id, keyword, landing_page)
)

这允许数据库缓存准备好的语句，而不必每次都重新编译它。它还避免了 SQL 注入(inject)攻击，在这种情况下，聪明的攻击者可以制作一个实际上更像 SQL 的值，如 "MORE SQL HERE "。这是一个非常、非常、非常普遍的安全漏洞。

请注意，您可能需要使用 MySQL's own Python database library to get true prepared statements .不要太担心，使用准备好的语句不是你最大的性能问题。

---

接下来，您基本上要做的是添加到现有行，或者如果没有现有行，则插入一个新行。使用 UPSERT、INSERT 和 UPDATE 的组合，可以在单个语句中更有效地完成此操作。 MySQL has it as INSERT ... ON DUPLICATE KEY UPDATE .

要查看这是如何完成的，我们可以将您的 SELECT then UPDATE 编写为单个 UPDATE。计算在 SQL 中完成。

    update temp
    set impressions = impressions + %s,
        clicks = clicks + %s,
        ctr = (ctr + %s / 2)
    where profile_id=%s and
          keyword=%s and
          landing_page=%s

您的 INSERT 保持不变...

    insert into temp
        (profile_id, landing_page, keyword, position, impressions, clicks, ctr)
        values (%s, %s, %s, %s, %s, %s, %s)

将它们组合成一个 INSERT ON DUPLICATE KEY UPDATE。

    insert into temp
        (profile_id, landing_page, keyword, position, impressions, clicks, ctr)
        values (%s, %s, %s, %s, %s, %s, %s)
    on duplicate key update
    update temp
    set impressions = impressions + %s,
        clicks = clicks + %s,
        ctr = (ctr + %s / 2)

这取决于表的键被定义为什么。如果您有 unique( profile_id, landing_page, keyword ) 那么它应该与您的代码一样工作。

即使您不能执行 upsert，您也可以通过尝试 UPDATE 来消除 SELECT，检查它是否更新了任何内容，如果它没有执行一个 INSERT。

---

批量更新。与其调用执行一次更新和提交的子例程，不如将一大堆要更新的内容传递给它并在循环中处理它们。您甚至可以利用 executemany运行具有多个值的相同语句。然后提交。

您可以批量执行 UPSERT。 INSERT 可以一次获取多行。例如，这将插入三行。

insert into whatever
    (foo, bar, baz)
values (1, 2, 3),
       (4, 5, 6), 
       (7, 8, 9)

您可以对 INSERT ON DUPLICATE KEY UPDATE 执行相同的操作，从而减少与数据库对话的开销。参见 this post for an example (在 PHP 中，但您应该能够适应)。

这牺牲了返回最后插入行的 ID，但它们是中断。