Part of the system I'm working on at the moment involves a log in mysql, with counts being frequently updated.
我正在研究的系统的一部分涉及登录mysql,计数经常更新。
The data being inserted is of the format:
插入的数据格式如下:
date | name | count |
-----------+------+-------+
2009-01-12 | alan | 5 |
2009-01-12 | dave | 2 |
2009-01-12 | mary | 1 |
This data is parsed regularly from a flat file, summarised as above in preparation for a db insert/update - the unique key on the database is the (date, name) pair.
此数据定期从平面文件中解析,如上所述,为数据库插入/更新做准备 - 数据库上的唯一键是(日期,名称)对。
Previously, this system would check the existing table for any records for a given date and name pair before deciding on an update or insert.
以前,在决定更新或插入之前,此系统将检查现有表以查找给定日期和名称对的任何记录。
The problem we're having is, as this table grows, the response time isn't getting any better, and we want to reduce the number of queries as much as possible.
我们遇到的问题是,随着此表的增长,响应时间没有变得更好,我们希望尽可能减少查询数量。
The system was recently updated to run a INSERT ... ON DUPLICATE KEY UPDATE query, which has reduced the number of selects marginally, but our common case by some distance is the update.
最近更新了系统以运行INSERT ... ON DUPLICATE KEY UPDATE查询,该查询略微减少了选择的数量,但我们常见的情况是一些距离是更新。
I'm wondering if anyone knows of a mysql function that's essentially INSERT ... ON DUPLICATE KEY UPDATE in reverse, i.e. will try to update a row, if none match then perform the insert?
我想知道是否有人知道一个mysql函数本质上是INSERT ...在DUPLICATE KEY UPDATE反向,即将尝试更新一行,如果没有匹配则执行插入?
Edit
I didn't make it too clear above, what I would like to do when I have the record ('2009-01-12','alan','5') for example, is:
我上面没有说清楚,例如,当我有记录('2009-01-12','alan','5')时,我想做的是:
UPDATE table SET count = count+5 WHERE date = '2009-01-12' and name = 'alan';
and if the above fails, insert the above data. The need to increment a counter is why REPLACE won't work. Replace performs a delete & insert, and doesn't let you refer to the row being deleted, so count = count + 5 wouldn't increment the previous count value for by 5.
如果上述操作失败,请插入以上数据。增加计数器的需要是REPLACE不起作用的原因。替换执行删除和插入,并且不允许您引用要删除的行,因此count = count + 5不会将先前的计数值增加5。
@jasoncohen - the INSERT ... ON DUPLICATE KEY UPDATE does do the job, but I'm asking if there's a more optimal way to do this.
@jasoncohen - INSERT ... ON DUPLICATE KEY UPDATE确实完成了这项工作,但我问的是否有更优化的方法来做到这一点。
Sorry for any confusion resulting from the poor original phrasing!
抱歉由于原始措辞不佳导致的任何混乱!
4 个解决方案
#1
3
It's just the same. With "UPDATE ... ON NO KEY INSERT", the database engine will still have to check first if there is something to update. Hence no need for a separate construct even if update is most common
它也是一样的。使用“UPDATE ... ON NO KEY INSERT”,数据库引擎仍然必须先检查是否有更新内容。因此,即使更新最常见,也不需要单独的构造
#3
1
I have been trying to figure out what exatcly it is that you want, and as I see it, you don't want to do anything if the data match? I dont see a solution to that, if the "count" somehow will change and need updating, you're stuck with the INSERT INTO ON DUPLICATE KEY UPDATE (which I don't really see the issue with).
我一直试图弄清楚你想要的是什么,而且正如我所看到的,如果数据匹配,你不想做任何事情?我没有看到解决方案,如果“计数”会以某种方式改变并需要更新,你就会坚持使用INSERT INTO ON DUPLICATE KEY UPDATE(我实际上没有看到问题)。
However, if the count never will be updated, you might wanna look into INSERT IGNORE INTO which will ignore the insert if the unique key (date + name) already exists.
但是,如果计数永远不会更新,您可能需要查看INSERT IGNORE INTO,如果唯一键(日期+名称)已经存在,它将忽略插入。
You haven't considered "flushing/rotating" your flat file therefor only checking for added material? Or isn't that possible?
您没有考虑“冲洗/旋转”您的平面文件,仅检查添加的材料?或者不可能吗?
Edit:
The INSERT will fail instantly because of the duplicate key violation, and trigger the UPDATE in that case. Shouldn't be any performance concern at all. I do this all the time on pretty large database, and I haven't noticed any huge performance difference when starting from a empty database as opposed to an already populated database.
由于重复密钥冲突,INSERT将立即失败,并在这种情况下触发UPDATE。根本不应该是任何性能问题。我一直在相当大的数据库上执行此操作,并且我没有注意到从空数据库开始而不是已经填充的数据库时有任何巨大的性能差异。
However, it's probably a good thing to run ANALYZE TABLE/OPTIMIZE TABLE from time to time in order to keep the index in good shape.
但是,为了使索引保持良好状态,不时运行ANALYZE TABLE / OPTIMIZE TABLE可能是件好事。
#4
0
Why isn't the INSERT sufficient? Even if most of the time it's a duplicate key and therefore an update (instead of the other way around), it's still the correct operation right?
为什么INSERT不够用?即使大部分时间它都是重复键并因此更新(而不是相反),它仍然是正确的操作吗?
Are you just asking for performance concerns?
您只是在询问性能问题吗?
#1
3
It's just the same. With "UPDATE ... ON NO KEY INSERT", the database engine will still have to check first if there is something to update. Hence no need for a separate construct even if update is most common
它也是一样的。使用“UPDATE ... ON NO KEY INSERT”,数据库引擎仍然必须先检查是否有更新内容。因此,即使更新最常见,也不需要单独的构造
#2
#3
1
I have been trying to figure out what exatcly it is that you want, and as I see it, you don't want to do anything if the data match? I dont see a solution to that, if the "count" somehow will change and need updating, you're stuck with the INSERT INTO ON DUPLICATE KEY UPDATE (which I don't really see the issue with).
我一直试图弄清楚你想要的是什么,而且正如我所看到的,如果数据匹配,你不想做任何事情?我没有看到解决方案,如果“计数”会以某种方式改变并需要更新,你就会坚持使用INSERT INTO ON DUPLICATE KEY UPDATE(我实际上没有看到问题)。
However, if the count never will be updated, you might wanna look into INSERT IGNORE INTO which will ignore the insert if the unique key (date + name) already exists.
但是,如果计数永远不会更新,您可能需要查看INSERT IGNORE INTO,如果唯一键(日期+名称)已经存在,它将忽略插入。
You haven't considered "flushing/rotating" your flat file therefor only checking for added material? Or isn't that possible?
您没有考虑“冲洗/旋转”您的平面文件,仅检查添加的材料?或者不可能吗?
Edit:
The INSERT will fail instantly because of the duplicate key violation, and trigger the UPDATE in that case. Shouldn't be any performance concern at all. I do this all the time on pretty large database, and I haven't noticed any huge performance difference when starting from a empty database as opposed to an already populated database.
由于重复密钥冲突,INSERT将立即失败,并在这种情况下触发UPDATE。根本不应该是任何性能问题。我一直在相当大的数据库上执行此操作,并且我没有注意到从空数据库开始而不是已经填充的数据库时有任何巨大的性能差异。
However, it's probably a good thing to run ANALYZE TABLE/OPTIMIZE TABLE from time to time in order to keep the index in good shape.
但是,为了使索引保持良好状态,不时运行ANALYZE TABLE / OPTIMIZE TABLE可能是件好事。
#4
0
Why isn't the INSERT sufficient? Even if most of the time it's a duplicate key and therefore an update (instead of the other way around), it's still the correct operation right?
为什么INSERT不够用?即使大部分时间它都是重复键并因此更新(而不是相反),它仍然是正确的操作吗?
Are you just asking for performance concerns?
您只是在询问性能问题吗?