分区时如何处理外键 [英] How to handle foreign key while partitioning

问题描述

我正在从事车队管理工作。我在一个位置表上​​写下了大量的以下列：

1. date


2. time


3. 车辆号码


4. long


5. 纬度


6. / li>
   
7. userid（这是外键...）

这个表将会有每3秒写一次操作因此，将有数百万的记录。
所以要检索更快的数据我AM计划分区。
现在我的问题： -

1. 如何处理外键？我听说分区不支持外键


2. 应该使用哪个列进行分区。


3. 是否需要将唯一的键作为分区列。

将有数万亿的记录

@ rc-thanks man..what abt的性能...看到我每3秒插入一次数据，所以我必须运行一个检查程序，每次我插入数据...所以性能如何？ / p>

2>我想去分区列作为车辆没有.....有任何替代方式...

解决方案

阅读： MySQL分区限制

1。）分区表不支持FK。

• 一个选项是创建插入/更新记录的存储过程，并在插入发生之前在过程中验证用户标识已传递到用户表中。您应该设置表上的权限，以便只允许SP更新和插入以允许应用程序和/或用户后门检查。您还需要在从用户表中删除用户时采取预防措施。

2）您用于分区的列将取决于你如何访问表。如果您的查询总是基于vechicle编号，那么在该列上执行哈希分区可能是有意义的。如果您正在查询或报告更多的内容，例如本月添加了哪些车辆，或者您想要在分段成为一定年龄时将其分段，那么日期分区可能就是这样。这是您根据使用情况决定的。

3。）有关更多信息，请参阅上述链接。

根据用户问题编辑

每3秒插入一条记录不是很多的吞吐量。确保您的用户表中有一个主键，以便程序中的检查能够高效地完成。 （即使支持FK也是如此）如果您在这个意义上支持FK，那么DB将在幕后进行此检查，这不会伤害您。如果支票成为瓶颈，您可能会觉得需要将其删除，并可能会将每个批次过程中的错误用户ID报告，但如果您的用户表相对较小并且正确索引，则不会看到这是一个问题。

另一个选择是使用分区或非分区表手动（即分片）进行分区。使用非分区表当然可以使用本机外键。例如，您可以将您的车辆表分成多个表，如：（假设您要使用车辆不作为钥匙）

VehiclesNosLessThan1000

VehiclesNosLessThan2000

VehiclesNosLessThan ...

VehiclesNosLessThanMAX

在这里，您可能想要再次使用SP，以便应用程序/用户不必了解表。 SP将负责基于未通过的车辆插入/更新正确的表。您还需要一个SP来选择数据，以便应用/用户不必知道要从中选择的表。为了方便所有数据的访问，您可以创建一个将所有表联合在一起的视图。

请注意，其中一个好处是当前的MyISAM锁定整个分区表在更新期间，不仅仅是它正在更新的分区。以这种方式分割表可以减轻这种争用，因为这些表本身就是分区。

根据有限的数据，我在做什么，我可能会写2个存储过程，1个用于选择数据，1个用于更新/插入数据，并让您的应用程序使用这些进行所有访问。然后我将首先尝试通过hash上的常规分区，同时在该过程中强制执行user_id键。如果这成为一个问题，您可以轻松地迁移到跨多个表分割数据，而无需更改应用程序，因为所有关于如何检索和更新数据的逻辑都包含在SP中。

I am working on fleet management. I am having large amount of writes on a location table with following columns

1. date
2. time
3. vehicle no.
4. long
5. latitude
6. speed
7. 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:-

1. How to handle foreign key? I heard that partition does not support foreign key
2. Which column should be used for partition.
3. 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...

解决方案

Read: MySQL Partitioning Limitations

1.) FKs are not supported on partitioned tables.

• One option is to create a stored procedure that inserts/updates the record and to verify inside the procedure that the user id passed is present in your users table before the insert takes place. You should set up the permissions on the table so that only the SP is allowed to update and insert to allow applications and/or users from backdooring the check. You will also need to take precautions when removing users from the users table.

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.

这篇关于分区时如何处理外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！