mysql – ON UPPLICATE KEY UPDATE比UPDATE更快
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql – ON UPPLICATE KEY UPDATE比UPDATE更快，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5132字，纯文字阅读大概需要8分钟。
内容图文
我有一个大约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)
由于严格的性质只接触那些表示对通讯感兴趣的人,以及从未联系过要求不联系的人的严格性质,在邮寄之前我添加了一个字段expressions_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上.
解决方法:
我知道从M??ySQL获取更新的执行计划并不容易,因为它只提供SELECT语句的更新.但是线索可能是记录更新的顺序,WHERE的评估,其中包含带有大量静态数据的IN,以及与之相关的连接读写,中间缓存的数量.
该声明
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子句中的大量静态值会在具有许多匹配记录的查询上几乎指数地增加执行时间,因为它们基本上不使用任何索引或优化并且通常最终在全表扫描中,其中IMHO执行将通过将其与IN()列表中的每个项目逐个进行比较,检查表中的每个记录/行.
声明如
INSERT INTO humans_we_respect (id) VALUES (1),...,(10000) ON DUPLICATE KEY UPDATE expressed_interest=1;
然而,使用索引来定位记录,然后更新它,即使这样不打算用于此用途,它将运行得更好,因为如果有一个ID的索引,并且只会对ID执行索引查找记录而不是成千上万的比较！
如果ID列表是从同一服务器上的另一个表派生的,那么直接使用两个表可能会更快,可以使用更多的优化,而且您不必将数据传输到mysql服务器进程中.
就像一些额外的信息：
UPDATE humans_we_respect SET expressed_interest=1 WHERE id='1' OR id='2' OR ...
是一种很好的技术来优化具有非常低元素数量的IN()查询,因为它将为每个元素创建一个并行索引查询,这对于第一个元素很有用,但是会在更多元素的情况下降低性能,并且在某些时候达到优化解析器的极限(恕我直言,它可能是一个查询中的255个元素),此时它将再次研磨到蜗牛的速度……
内容总结
以上是互联网集市为您收集整理的mysql – ON UPPLICATE KEY UPDATE比UPDATE更快全部内容，希望文章能够帮你解决mysql – ON UPPLICATE KEY UPDATE比UPDATE更快所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。