MySQL ALTER TABLE with subpartition失败

Question

我正在尝试修改一个现有的表，以添加年份和周子分区，如下所示：

CREATE TABLE test_table(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    dtime DATETIME NOT NULL);

ALTER TABLE test_table
PARTITION BY RANGE ( YEAR(dtime) )
SUBPARTITION BY RANGE( WEEK(dtime) ) ( 
    PARTITION y0 VALUES LESS THAN (2013) (
        SUBPARTITION w0 VALUES LESS THAN (2),
        ...
        SUBPARTITION w52 VALUES LESS THAN (54)
    ),
    PARTITION y1 VALUES LESS THAN (2014) (
        SUBPARTITION w0 VALUES LESS THAN (2),
        ...
        SUBPARTITION w52 VALUES LESS THAN (54)
    ),
    PARTITION y2 VALUES LESS THAN (2015) (
        SUBPARTITION w0 VALUES LESS THAN (2),
        ...
        SUBPARTITION w52 VALUES LESS THAN (54)
    ),
    PARTITION y3 VALUES LESS THAN (2016) (
        SUBPARTITION w0 VALUES LESS THAN (2),
        ...
        SUBPARTITION w52 VALUES LESS THAN (54)
    )
);

然而，这给了我一个含糊和无用的回答：

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that     corresponds to your MySQL server version for the right syntax to use near 'RANGE(     WEEK(DTIME) ) (
            PARTITION y0 VALUES LESS THAN (2013) (
                    SUBPARTITION ' at line 3

我已经检查了文档：MySQL ALTER TABLE Partition operations和MySQL RANGE and LIST Partitions。但是，这两种方法都没有描述如何更改表以创建子分区。

我的问题的第二部分是关于这个分区方案的反馈。其中包含的数据是每分钟记录的传感器读数，最常见的查询操作是最近一周的数据。我认为这应该会大大加快我的查询速度，因为"WHERE dtime > date“非常常见，而不必定期手动将数据从表中移到存档表中。

Answer 1

如果要将分区BY LIST添加到已存在的表中，请删除主键并创建复合主键：

alter table test_table drop primary key, add primary key (id,<some other key>);
alter table orders partition by list(<some other key>) (
    partition p0 values IN (1),
    partition p1 values IN (2),
    partition p2 values IN (3),
    partition p3 values IN (4),
    partition p4 values IN (5),
    partition p5 values IN (6),
    partition p6 values IN (7),
    partition p7 values IN (8),
    partition p8 values IN (9),
    partition p9 values IN (10)
);

Answer 2

经过进一步的调查，我发现了这种方法的几个问题。

1. 不可能对DateTime值(本例中的dtime是)进行范围划分。我正在分区的http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-functions.html
2. The表在自动增量id列上有一个主键，如果有不同的主键，则不能在索引上分区。

错误1503 (HY000)：主键必须包括表的分区函数中的所有列参见http://blog.mclaughlinsoftware.com/2011/05/09/mysqls-real-partition-key/ http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-partitioning-keys-unique-keys.html

• WEEK() is not allowed as a HY000 function。http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-functions.html

据我所知，如果您有一个唯一的AUTO_INCREMENT id作为主键，那么除了这个值之外，不可能对任何东西进行分区。

我的查询都使用WHERE条件中的dtime列，所以似乎除非我仍然可以在dtime上进行分区，否则对这个表进行分区没有任何好处(从性能的角度来看)。