这个 SQL 查询有多危险？
查询：
UPDATE
node as n
right join content_type_product as c
on n.nid = c.nid
right join uc_products as p
on p.nid = n.nid
set
c.field_product_price_eur_value = p.sell_price * 0.0961,
c.field_product_price_zar_value = p.sell_price * 1,
c.field_product_price_gbp_value = p.sell_price * 0.0844,
c.field_product_price_usd_value = p.sell_price * 0.1305,
n.changed = now()
where n.type = 'product'
对于那些还没有弄清楚的人，此查询会将 Drupal 站点上的所有节点更新为都具有最新的货币。我的问题是，如果您有以下查询，此查询有多危险：
- 500 个节点
- 50 000 个节点
- 1 000 000 个节点
如果此命令每小时执行一次？
我需要知道是否应该每隔几个小时执行一次这个查询，或者是否应该将其限制为一次仅更新 500 个等。
将执行此查询的站点将有多个节点条目，并且该查询更新了 2 行每 1 件产品。因此，如果我有大量节点，我不确定这会给服务器带来多大的压力。
The query:
UPDATE
node as n
right join content_type_product as c
on n.nid = c.nid
right join uc_products as p
on p.nid = n.nid
set
c.field_product_price_eur_value = p.sell_price * 0.0961,
c.field_product_price_zar_value = p.sell_price * 1,
c.field_product_price_gbp_value = p.sell_price * 0.0844,
c.field_product_price_usd_value = p.sell_price * 0.1305,
n.changed = now()
where n.type = 'product'
For those that haven't figured it out, this query updates all the NODES on a Drupal site to all have the latest currency. My question is, how dangerous is this query if you have:
- 500 Nodes
- 50 000 Nodes
- 1 000 000 Nodes
IF this command is executed every hour?
I need to know if i should only execute this query every few hours, or if I should limit it to only updating say 500 at a time etc.
The site where this will be executed will have several node entries, and this query updated 2 rows for every 1 product. So, I'm not sure how badly this will strain the server, if I have tons of nodes.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我建议在您的测试环境中对此进行基准测试（您确实有一个测试环境，对吧？）以估算您的服务器将经历的负载类型。如果不进一步了解您的环境，就很难猜测这会产生什么样的影响。
但是，为了改进您的应用程序，我建议将汇率存储在单独的表中，并在用户提取特定产品时计算它们。这样，当只有少数数字实际发生变化时，您不必更新数百万行。如果需要，您甚至可以每隔几分钟而不是每小时更新一次汇率。
I would suggest benchmarking this in your Test environment (you do have a test environment, right?) to approximate what sort of load your server would experience. It's very difficult to guess what sort of impact this will have without knowing more about your environment.
To improve your application, however, I would suggest storing the exchange rates in a separate table and computing them when users pull up a particular product. This way you don't have to update millions of rows when only a handful of numbers have actually changed. You could even update your exchange rates every few minutes rather than every hour, if desired.
毫无疑问，这是一个相当重大的呼吁。
我认为这是根据最新的货币汇率更新产品价格。 1,000,000 个节点已经很多了，但如果每秒有数千次点击，则可能会导致数百万次计算（如果这是动态完成的）。
我唯一的建议是设置某种过滤以仅更新“活动”产品。即公众可见的产品。如果产品从不活跃变为活跃，它应该在那时收集适当的价格。
This is, no doubt a pretty hefty call to be making.
I assume this is to update product prices according to the latest currency exchange rates. 1,000,000 nodes is a lot but if you have several thousand hits per second that can result in several million calculations if this is done on the fly.
My only recommendation would be to set up some kind of filtering to only update "active" products. That is, products that are visible to the public. If a product makes a change from inactive to active it should gather it's appropriate price at that time.
这是 InnoDB 还是 MyISAM 表？如果是MyISAM，它将锁定整个查询的整个表，这将在相当长的时间内锁定所有读取。
我认为查询本身没问题，但请使用 EXPLAIN 检查它以确保您拥有正确的索引。
您还可以考虑使用
vid，并仅更新节点的最新版本。Is this an InnoDB or MyISAM table? If MyISAM, it will lock the complete table for the entire query, this will lock out all reads for a considerable amount of time.
The query itself is OK I think, but do check it with EXPLAIN to make sure you have the proper indexes.
You could also consider using
vid, and update only the latest revision of your nodes.那么这部分就浪费资源了，价格*1=价格。事实上，由于您每次都会更新一定数量，因此我不确定查询是否正在执行您需要的操作。但总的来说，我永远不会考虑更新我计划中的所有价格，除非有变化需要他们改变。您的查询中没有任何内容表明发生了任何更改，因此无论货币值是否更改，都会发生更改（即使货币没有更改，写入方式也会更改值）。或者我没有看到你的流程的一部分？
Well this part is a waste of resources, price * 1 = price. In fact since you are updating by a set amount every time, I'm not sure the query is doing what you need anyway. In general though, I would never consider updating all the prices I have on a schedule unless there is a change requiring them to change. There is nothing in your query that indcates that any change has happened so it would happen whether or not the currency value changed (and the way it is written woudl change the values even if the currency did not change). OR am I not seeing part of your process?