拆分表以每秒处理 100 次插入？
我们将拥有一个每秒 100 次读取的 MS SQL Server 数据库，在一小时内均匀分布 2,400 次插入，在一小时内均匀分布 14,400 次更新。环境每月可能会增长 170 万行。列数少于 20 个。
我们是一个集装箱码头，拥有 60 台起重机，每台起重机在给定时刻搬运 1 个集装箱。这些表的属性是 a/o ：crane_ID（3 个字符）、container_ID（11 个字符）、container_unique_number（双精度）、container_status（1 个字符）和一堆其他详细信息。
在任何给定时刻，最多 60 条记录（一台起重机搬运一个集装箱）将处于“活动”状态；其余的是历史数据。
活动记录的更新将使用 3 列进行读取，其中一个字段 (container_unique_number) 是唯一的。 100 个 I/O（来自不同客户端的所有不同进程）扫描所有活动记录或活动的特定 container_id。 一半的流程基本上专用于一台特定的起重机。
我们面临的设计问题是：
- 如果我们构建一个通用表来保存所有起重机的所有记录，并且每秒对活动记录执行 100 个（不同进程的）查询，我们会遇到性能问题吗？
- 或者我们应该为每个起重机构建一个专用（但相同）表并将进程专用于特定表，例如与起重机1相关的进程仅读取表CRANE1_blablabla？
- 如果我们确实构建了一个公用表，我们是否应该将“已完成”记录移至存档表中？在归档之前，您允许在此“活动”表中保存的已完成记录的最大数量是多少？
谢谢， 西蒙
We will have a MS SQL Server database with 100 reads per second, 2,400 inserts evenly spread over one hour, and 14,400 updates evenly spread over one hour. Per month the environment could grow with 1,7M rows. The # of columns is under 20.
We are a container terminal with 60 cranes that are each handling 1 container at a given moment. Attributes of these tables are a/o : crane_ID (3 char), container_ID (11 char), container_unique_number (double), container_status (1 char), and a bunch of other details.
At any given moment, max 60 records (one crane handles one container) will be "active"; the rest is historic data.
Updates on active records will be done reading using 3 columns with one field (container_unique_number) being unique. The 100 I/Os (all different processes from various clients) are scans for all records that are active or for a specific container_id that is active.
Half of the process is basically dedicated to one specific crane.
The design question that we are facing is :
- if we build one common table that holds all records for all cranes, with 100 queries (of distinct processes) on active records per second, will we have a performance issue?
- or should we build a dedicated (yet identical) table per crane and dedicate processes to specific tables eg the processes associated with crane 1 only read table CRANE1_blablabla?
- if we do build one common table, should we move "completed" records out to an archive table? What would the max nr of completed records be that you would allow to sit in this "active" table before you archive it?
Thanks,
Simon
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
一张桌子。这更多地与硬件有关。放入 SSD 来存储日志和数据，一切都很好。
拆分到多个表会让你在大多数公司中获得第一，尽管这会带来“所谓的”性能提升。有充分的理由。
如今，您可以在桌面硬件上每秒执行 100 次操作。
One table. This is a LOT more about the hardware than anything. Put in a SSD for logs and data and you are more than fine.
Splitting to multiple tables woudl get you fird in most companies, and that is despite the "supposed" performance gain. With good reason.
100 operations per second you can do on desktop hardware these days.
我不会担心那种程度的读取或写入。从绝对值来看，它相当低
为所有起重机构建一个表：不要将信息编码到表名称中。
如果您想删除存档记录并且需要在实时表中保留一些记录，那么第 8 天后通过一些批处理即可完成。如果您不需要实时表中的存档数据，请立即将其移动，否则只需使用一张表并查看 缓慢改变维度，将所有内容集中在一处
I wouldn't worry about that level of reads or writes. It's quite low in absolute terms
Build a table for all cranes: don't encode information into table names.
If you want to remove archive records and if you need to have some in the live table, then after day 8 days via some batch process will do it. If you don't need the archive data in the live table, then move it immediately, Otherwise just use one table and look at Slowly changing dimension to keep it all in one place