MySQL：如何处理定期的大量插入/更新？
我有一个表格，用于跟踪有关网站使用情况的各种统计数据。每周一次，我计划使用这些统计数据和其他表格中的信息来计算用于多个报告的各种关键指标。这是为了不必在每次访问报告时都计算复杂的数据。这些指标将存储在一个单独的表中，称为“指标”。
有大约。 220 个国家的 50 个指标（即每个国家单独一组 50 个指标）。鉴于这些数字是每周计算的，我得到的数字令人难以置信：
每周 50 x 220 = 11,000 次插入/更新
我的指标表的结构如下：
id PK | type (TINYINT) | country_id (TINYINT) | year | week | value
... 我基本上会插入/更新 50 行每个国家都有不同的“类型”。
我想问的是：我这里是不是完全走错了路？将国家/地区设置为列而不是行（220 列...）可能会更好吗？或者也许指标又失去了一些灵活性？通常如何处理如此大量的插入/更新？
预先感谢您的宝贵时间。
结论：看起来这已经足够清楚了，感谢所有的贡献。
I have table where I track various statistics about site usage. Once a week, I plan to use these statistics and information from other tables to calculate various key indicators for use in multiple reports. This is in order not to have compute the complex data each time a report is accessed. These indicators will be stored in a separate table called "indicators".
There are approx. 50 indicators for 220 countries (i.e. a set of 50 for each country separately). Given that these figures are calculated weekly, I get the mind-boggling number of:
50 x 220 = 11,000 inserts/updates each week
My indicators table is structured in the following way:
id PK | type (TINYINT) | country_id (TINYINT) | year | week | value
... where I would basically insert/update 50 rows of different "type" per country.
What I'd like to ask is: am I going completely the wrong way here? Might I be better off setting the countries as columns instead of rows (220 columns...)? Or perhaps the indicators and lose some flexibility? How are such high volumes of inserts/updates generally dealt with?
Thanks in advance for your time.
CONCLUSION: Looks like this is clear enough, thanks for all the contributions.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
虽然我不知道您的确切应用程序，但这根本不应该有问题。插入和更新不应该是表锁定。我怀疑您的用户是否会注意到。
作为比较，在负载下，我的 mysql 服务器峰值为每秒近 3.5k 查询。
通常，您无论如何都会希望在低使用期间运行维护脚本。
While I don't know your exact application, this shouldn't be problematic at all. Inserts and updates shouldn't be table locking. I doubt your users would even notice.
As a comparison, under load my mysql server peaks at nearly 3.5k queries / second.
Typically you'll want to run your maintenance scripts during periods of low use anyway.r