分区或单独的表
我正在为车队管理系统设计数据库。
我将每 3 秒获取 n 条记录。显然，我的表中将有数百万条记录，我将在 current_location 表中存储车辆的当前信息。在这里，性能是一个大问题。
为了解决这个问题，我收到了以下建议：
为每辆车创建一个单独的表。 一旦我单击“创建新表”，就会在运行时创建一个表。与特定表相关的所有数据都将插入该特定表并从该特定表中检索。
进行分区。
请回答以下有关这些解决方案的问题。
两者有什么区别？
哪个最好，为什么？
表中的行数何时会导致性能问题？
还有其他解决方案吗？
现在 ---如果我在 sql server 2008 中进行范围分区，我应该做什么，
使用 varchar(20) 分区。
我打算根据车号进行分区。例如 MH30 q 1234。 这里是车号。假设 MH30 q 1234——只有 30 & q 将会改变......所以我的问题是我应该怎么去。意思是分区函数应该怎么写。
***第一个这个问题是针对我的 sql 提出的。现在针对 sql server ******抱歉，现在我从 sql 转移到 sql server***** 有同样的问题
I am designing database for fleet management system.
I will be getting n number of records every 3 seconds. Obviously, there will be millions of record in my table where I am going to store current Information of vehicle in the current_location table. Here performance is an BIG issue.
To solve this, I received the following suggestions:
Create a separate table for each vehicle.
Here a table will be created at a run time as as soon as I click on create new table.And all the data related to particular table will be inserted and retrieve from that particular table.Go for partition.
Please answer the following questions about these solutions.
What is difference between the two?
Which is best and why?
At what point will the number of rows in the tables cause performance issues?
Are there any other solutions?
Now ---if I go for range partition in sql server 2008 what should i do to,
partition using varchar(20).
i am planning to do partition based on vehicle no. eg MH30 q 1234.
Here In vehicle no. lets say mh30 q 1234--only 30 & q going to change....so my question is HOW SHOULD I GO. means how should write the partition function.
***1st this question was asked for my sql..now for sql server
********sorry guys now I shifted from my sql to sql server*****With The same question
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
肯定使用分区。当 mysql 会为你做这件事时，为什么要费尽心思去弄清楚使用哪个表来回答问题呢？如果您不使用分区，祝您好运找到所有卡车的当前位置！
分区为您提供了多个表的性能优势，但具有自动修剪（仅选择回答查询所需的表）。
没有什么是“最好的”。问题是：什么最适合您的问题？
这个问题无法回答。您只需监视系统的性能问题并根据需要调整服务器设置或规模。
至少就mysql而言，没有比分区更好的了！
definitely use partitioning. why go to all of the hassle to figure out which table to use to answer a question when mysql will do it for you? and good luck find the current location of all of your trucks if you're not using partitioning!
partitioning gives you the performance benefits of multiple tables, but with automatic pruning (selection of just the tables needed to answer the query).
nothing is ever "best". the question is: what is best for your problem?
this is impossible to answer. you will just have to monitor your system for performance issues and adjust server settings or scale as necessary.
at least as far as mysql is concerned, none as good as partitioning!
不必为每天 28,800 行的分区而烦恼。
我们（还）每天的处理量还没有超过 500 万。 （“尚未”意味着我们还没有就他们想要的数据保留策略提供任何业务意见）
Don't bother with partitioning for 28,800 rows per day.
We don't (yet) with over 5 million per day. (The "yet" means we have no business input on what data retention policy they want)
为每辆车创建一个单独的表和将车辆 ID 作为主键中的第一个字段之间的性能差异应该很小。无论哪种方式，您都会在磁盘上获得相同的分组，并且 mysql 应该不会遇到表中数百万行的问题。
仅当您的计算机上有多个磁盘并且想要跨磁盘分散负载时，分区才有用。
所以我想我的答案是两者都不做。先验地设计这个似乎有点矫枉过正。
There should be very little performance difference between making a separate table for each vehicle, and making the vehicle ID the first field in the primary key. You get the same grouping on disk either way, and mysql should have no trouble with millions of rows in a table.
Partitions are only useful if you have multiple disks on your machine and want to spread the load across disks.
So I guess my answer is do neither. Designing this in a priori seems overkill.
我想指出的一件事是，拥有一张表（您可以在需要时对其进行分区）在数据库中和查询数据方面都更容易维护。
One thing I want to point out is that having one table (which you can partition later when you need to) will be much easier to maintain both in the database and in terms of querying the data.