Msql Partitioning - Key vs Hash vs List vs Range

时间:2022-09-16 11:21:38

I went through some of the documentation of mysql but cannot understand the difference in the following ways of partitioning : Key vs Hash vs List vs Range.Can someone explain in pure english?

我浏览了一些mysql的文档,但无法理解以下分区方式的区别:Key vs Hash vs List vs Range.Can有人用纯英语解释?

Also we have the following table: How do we partition by forum_id?

我们还有下表:我们如何通过forum_id进行分区?

CREATE TABLE IF NOT EXISTS `posts_content` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` int(11) NOT NULL,
  `forum_id` int(11) NOT NULL,
  `content` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=79850 ;

Thanking you

感谢您

1 个解决方案

#1


3  

key and hash means that the records will be distributed randomly, for example if you define 3 partitions for forum_id, then the records for forum_id 1,3 and 7 may go to partition 1, for 2 and 4 to partition 2, and for 5 and 6 to partition 3. In list and range you define which data goes to which partition, and they are prefered if you are partitioning based on date, in order to ensure that all recent posts will be on the same partition. Generally you have to avoid queries that will use more than one partition. For this reason, you have first to know your data access patterns, and then to choose the right partitioning type.

key和hash意味着记录将随机分发,例如,如果为forum_id定义3个分区,则forum_id 1,3和7的记录可能会转到分区1,2和4转到分区2,以及5和在列表和范围中,您可以定义哪些数据转到哪个分区,如果您是基于日期进行分区,则优先选择它们,以确保所有最近的帖子都在同一个分区上。通常,您必须避免使用多个分区的查询。因此,您首先要了解数据访问模式,然后选择正确的分区类型。

#1


3  

key and hash means that the records will be distributed randomly, for example if you define 3 partitions for forum_id, then the records for forum_id 1,3 and 7 may go to partition 1, for 2 and 4 to partition 2, and for 5 and 6 to partition 3. In list and range you define which data goes to which partition, and they are prefered if you are partitioning based on date, in order to ensure that all recent posts will be on the same partition. Generally you have to avoid queries that will use more than one partition. For this reason, you have first to know your data access patterns, and then to choose the right partitioning type.

key和hash意味着记录将随机分发,例如,如果为forum_id定义3个分区,则forum_id 1,3和7的记录可能会转到分区1,2和4转到分区2,以及5和在列表和范围中,您可以定义哪些数据转到哪个分区,如果您是基于日期进行分区,则优先选择它们,以确保所有最近的帖子都在同一个分区上。通常,您必须避免使用多个分区的查询。因此,您首先要了解数据访问模式,然后选择正确的分区类型。