分区时如何处理外键
我从事车队管理工作。我在位置表上进行了大量写入，其中包含以下列
- 日期
- 时间
- 车辆编号。
- 长
- 纬度
- 速度
- 用户ID（这是外键...）
这里这个表将每3秒进行一次写入操作。因此其中将有数百万条记录。 因此，为了更快地检索数据，我正在规划分区。 现在我的问题：-
- 如何处理外键？听说分区不支持外键
- 应该用哪一列来分区。
- 是否需要有唯一键作为分区列。
将会有数万亿条记录
@rc-谢谢伙计..性能如何...看到我每 3 秒插入一次数据，所以每次插入数据时我都必须运行一个检查程序……那性能呢？？？
2>我想将车号划分为分区......有没有其他方法......
I am working on fleet management. I am having large amount of writes on a location table with following columns
- date
- time
- vehicle no.
- long
- latitude
- speed
- userid (which is foreign key...)
Here this table is going to have write operation every 3 sec. Hence there will be millions of record in it.
So to retrieve faster data I AM PLANNING Partition.
Now my question:-
- How to handle foreign key? I heard that partition does not support foreign key
- Which column should be used for partition.
- is it necessary to have unique key as a partition column.
There will be trillions of record
@rc-Thanks man..what abt the performance ...see i am inserting data after every 3 sec so i have to run a check procedure every time i insert the data...so what about the performance???
2>I would like to go partition column as vehicle no.....is there any alternate way...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
阅读：MySQL 分区限制
1.) FK 不是支持分区表。
2.) 使用哪一列进行分区取决于您访问表的方式。如果您的查询始终基于车辆编号，那么对该列进行哈希分区可能是有意义的。如果您正在查询或报告更多有关“本月添加了哪些车辆”之类的信息，或者您希望在分区达到一定年龄时将其“滚动”，那么按日期分区可能是正确的选择。这是你必须根据你的使用情况来决定的。
3.) 请参阅上面的链接了解更多信息。
根据用户问题进行编辑：
每 3 秒插入一条记录并不是很大的吞吐量。确保用户表上有主键，以便有效地完成过程内的检查。 （即使支持 FK，情况也是如此）如果您支持 FK，数据库将在幕后为您进行此检查，因此从这个意义上说，它不会伤害您。如果检查最终成为瓶颈，您可能会觉得需要放弃它，并可能将错误的用户 ID 作为夜间批处理过程报告，但如果您的用户表相对较小且索引正确，我不认为这是一个问题。
另一种选择是对分区表或非分区表手动进行分区（即分片）。当然，对于非分区表，您可以使用本机外键。例如，您可以将车辆表拆分为多个表，例如：（假设您想使用vehicleNo作为“键”）
VehiclesNosLessThan1000
VehiclesNosLessThan2000
VehiclesNosLessThan...
VehiclesNosLessThanMAX
在这里，您可能希望再次拥有一个SP，以便应用程序/用户不会不必了解表格。 SP 将负责根据传入的vehicleNo 插入/更新正确的表。您还需要一个 SP 来选择数据，以便应用程序/用户不必知道要从中选择的表。为了轻松访问所有数据，您可以创建一个将所有表联合在一起的视图。
请注意，这样做的好处之一是，当前 MyISAM 在更新期间锁定整个分区表，而不仅仅是正在更新的分区。以这种方式对表进行分片可以缓解这种争用，因为表本身就是“分区”。
根据我所掌握的关于您正在执行的操作的有限数据，我可能会编写 2 个存储过程，1 个用于选择数据，1 个用于更新/插入数据，并让您的应用程序使用这些存储过程进行所有访问。然后我会首先尝试通过在vehicleNo 上的哈希进行常规分区，同时在过程中强制使用user_id 键。如果这成为一个问题，您可以轻松迁移到跨多个表分片数据，而无需更改应用程序，因为有关如何检索和更新数据的所有逻辑都包含在 SP 中。
Read: MySQL Partitioning Limitations
1.) FKs are not supported on partitioned tables.
2.) Which column you use for partitioning will depend on how your accessing the table. If your queries are always based on vechicle no., then it probably makes sense to do a hash partition on that column. If you're querying or reporting more on something like "what vehicles have been added this month" or you want to "roll" partitions out as they become a certain age, then partitioning on date may be the way to go. This is something you'll have to decided based on your usage.
3.) See the link above for more information.
Edit based on user question:
Inserting a record every 3 seconds is not a lot of throughput. Make sure you have a primary key on your users table in order for the check inside the procedure to be done efficiently. (This is true even if FKs were supported) The DB would be doing this check for you behind the scenes if you had support for FK's so in that sense, it's not hurting you. If the check ends up being a bottleneck, you may feel the need to drop it and possibly report errant user ids as a nightly batch process, but if you're user table is relatively small and indexed correctly I don't see this being an issue.
Another option would be to do the partitioning manually (i.e. sharding) with partitioned or non-partitioned tables. With the non-partitioned tables of course, you could use native foreign keys. For example you would split your vehicles table into multiple tables like: (assuming you want to use the vehicleNo as the "key")
VehiclesNosLessThan1000
VehiclesNosLessThan2000
VehiclesNosLessThan...
VehiclesNosLessThanMAX
Here you probably want to have an SP again so that the application/user doesn't have to know about the tables. The SP would be responsible for inserting/updating the correct table based on the vehicleNo passed in. You would also want an SP for selecting data so that the app/user doesn't have to know the table to select from either. For easy access to all the data, you can create a view that unions all the tables together.
Note that one benefit of this is that currently MyISAM locks an entire partitioned table during updates, not just the partition it is updating. Sharding a table this way alleviates that contention because the tables themselves are the "partitions".
Based on the limited data I have on what you're doing, I would probably write 2 stored procedures, 1 for selecting the data and 1 for updating/inserting the data and have your application use those for all access. Then I would try the regular partitioning via hash on vehicleNo first while enforcing the user_id key within the procedure. If this becomes an issue, you can easily migrate to sharding the data across multiple tables while not having to change the application because all the logic on how to retrieve and update the data is contained within the SPs.