主页 / dba / 问题 / 68548
Accepted
dotancohen
Asked: 2014-06-19 23:38:10 +0800 CST

ON DUPLICATE KEY UPDATE 比 UPDATE 快

  • 772

我有一个大约 1700 万行的表：

mysql> describe humans_we_respect;
+---------------------+-------------------------------------------------------------------------+------+-----+---------+-------+
| Field               | Type                                                                    | Null | Key | Default | Extra |
+---------------------+-------------------------------------------------------------------------+------+-----+---------+-------+
| id                  | bigint(20)                                                              | NO   | PRI | NULL    |       |
| name                | varchar(63)                                                             | YES  |     | NULL    |       |
| address             | varchar(127)                                                            | YES  |     | NULL    |       |
| city                | varchar(63)                                                             | YES  |     | NULL    |       |
| state               | varchar(3)                                                              | YES  | MUL | NULL    |       |
| zip                 | varchar(15)                                                             | YES  |     | NULL    |       |
| country             | varchar(15)                                                             | YES  |     | NULL    |       |
| email               | varchar(127)                                                            | YES  |     | NULL    |       |
| website             | varchar(127)                                                            | YES  |     | NULL    |       |
| area_code_state     | varchar(3)                                                              | YES  | MUL | NULL    |       |
| timezone            | set('other','pacific','mountain','central','eastern','alaska','hawaii') | YES  |     | other   |       |
+---------------------+-------------------------------------------------------------------------+------+-----+---------+-------+
12 rows in set (0.01 sec)

由于只联系那些对时事通讯表示兴趣的人的严格性质，以及从不联系要求不联系的人的严格性质，在邮寄之前，我添加了一个字段，我为那些表示有兴趣的人expressed_interest (tinyint) deafult null切换到，然后1切换到null那些要求不被联系的人。

以下查询（每个查询更新 10000 行）需要很长时间才能运行（半小时后终止）：

UPDATE humans_we_respect SET expressed_interest=1 WHERE id IN (1,...,10000);

但是，以下查询在几秒钟内完成：

INSERT INTO humans_we_respect (id) VALUES (1),...,(10000) ON DUPLICATE KEY UPDATE expressed_interest=1;

在什么条件下会ON DUPLICATE KEY UPDATE比 快UPDATE？我想知道这一点，以备将来与这样的大表一起使用。

这是在Amazon RDS中运行的 MySQL 5.5.33 上。

mysql update

2 个回答

  1. Best Answer

    我知道从 MySQL 获取更新的执行计划并不容易，因为它只提供那些 on SELECTstatements。但线索可能在于记录更新的顺序，对WHERE包含IN大量静态数据的 a 的评估，以及与之相关的连接读写、中间缓存的数量。

    该声明

    UPDATE humans_we_respect SET expressed_interest=1 WHERE id IN (1,...,10000);

    是我们在更新较大的数据库时尽量避免的一种语句，因为解析器似乎不时对它们进行疯狂处理。IN ( a,b,c,...,ZZZZ )对我来说已经成为一种只适用于IN数据中非常小的项目编号的编码风格。我正在做一个开源项目，我经常遇到我所谓的“远程加入”，后半部分通常看起来和你的问题完全一样。

    SELECT id FROM all_our_customers WHERE happytospam=1 AND LENGTH(email) > 6;
...
Storing result on client side as string like 
LOOP over results
$all_ids += ",$next_result";
END_LOOP
$all_ids = SUBSTRING($all_ids,1); 
ending up with a string like 
"1,2,3,4,5,8,10,100,1000,...,100000" in $all_ids
...
UPDATE humans_we_respect SET expressed_interest=1 WHERE id IN ( $all_ids )

    虽然第一部分通常执行得很快，但第二部分需要很长时间，这也是您所描述的。这些查询通常可以通过将它们重写为：

    UPDATE humans_we_respect,all_our_customers 
SET humans_we_respect.expressed_interest=1 
WHERE all_our_customers.id = humans_we_respect.id 
AND all_our_customers.happytospam=1 
AND LENGTH(all_our_customers.email) > 6

    我们还用

    UPDATE humans_we_respect 
SET expressed_interest=1 
WHERE id IN ( 
SELECT id 
FROM all_our_customers 
WHERE happytospam=1 
AND LENGTH(email) > 6 
)

    它的性能比原始版本好，但不如我建议的版本好。

    这一切都假设您使用适当的索引和主要的 ID 和组合的多列索引，其中多列经常一起使用或一起具有很好的意义并且通常存在于您的查询中。

    线索是子句中的大量静态值IN几乎成倍地增加了具有许多匹配记录的查询的执行时间，因为它们基本上不使用任何索引或优化，并且通常以全表扫描结束，恕我直言，执行将在其中检查通过将表中的每条记录/行与IN()列表中的每个项目逐个进行比较。

    像这样的声明

    INSERT INTO humans_we_respect (id) VALUES (1),...,(10000) ON DUPLICATE KEY UPDATE expressed_interest=1;

    然而，使用索引来定位记录然后更新它，即使不打算用于此用途，由于在 ID 上使用索引（如果有）并且只会对索引进行索引查找，它会运行得更好记录而不是成千上万的比较！但是，如果 ID 列表是从同一服务器上的另一个表派生的，则直接使用两个表可能会更快，可以使用更多优化，而且您不必将数据从 mysql 服务器进程传出和传入 mysql 服务器进程。

    就像一些额外的信息：

    UPDATE humans_we_respect SET expressed_interest=1 WHERE id='1' OR id='2' OR ...

    是一种优化元素数量非常少的查询的好技术，IN()因为它会为每个元素创建一个并行索引查询，这对于第一个元素非常有用，但会随着更多元素而降低性能，并且在某些时候会达到极限用于优化的解析器（恕我直言，它可能是一个查询中的 255 个元素），此时它将再次磨成蜗牛的步伐......

    • 3

  2. 只是一个猜测：

    • 也许 mysql 在第一个查询中检查 mysql 检查 1700 万行，如果其中一个适合该IN子句。
    • 在第二个查询中，您仅插入 10000 行，按索引检查键并更新行。

    如果是这种情况，您可以尝试将第一个查询重写为 UPDATE... WHERE id <= 10000，但这仅在您确实必须使用连续 id 更新 10000 行时才有效。也许你也可以试试UPDATE ... WHERE id = 1 OR id = 2 OR ...。但这只会很快，如果 mysql 可以在内部优化它。

    • 0

相关问题