我该如何执行这个MySQL分区？

I have a table with an integer column ranging from 1 to 32 (this column identify the type of record stored).

我有一个表,其整数列范围从1到32(此列标识存储的记录类型)。

The types 5 and 12 represents 70% of the total number of rows, and this number is greater than 1M rows, so it seems to makes sense to partition the table.

类型5和12表示总行数的70%,并且此数字大于1M行,因此对表进行分区似乎是有意义的。

Question is: how can I create a set of 3 partitions, one containing the type 5 records, the second containing the type 12 records, and the third one with the remaining records?

问题是:如何创建一组3个分区,一个包含类型5记录,第二个包含类型12记录,第三个包含剩余记录?

3 个解决方案

#1

http://dev.mysql.com/doc/refman/5.1/en/partitioning-list.html

create table some_table (
    id INT NOT NULL,
    some_id INT NOT NULL
)
PARTITION BY LIST(some_id) (
    PARTITION fives VALUES IN (5),
    PARTITION twelves VALUES IN (12),
    PARTITION rest VALUES IN (1,2,3,4,6,7,8,9,10,11,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32)
);

#2

Use Partition by list

按列表使用分区

#3

Provided that type is an index, then MySQL has already logically partitioned the table for you. Unless you really need physical partitioning, it seems to me you are only making trouble for yourself.

如果type是索引,那么MySQL已经为你逻辑地为表分区。除非你真的需要物理分区,否则在我看来你只是为自己制造麻烦。

#1