带有子分区的MySQL ALTER TABLE失败

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操作和MySQL RANGE和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. 我分区的表在自动递增id列上有一个主键，如果有不同的主键，就不能在索引上分区。

   错误1503（HY000）：PRIMARY KEY必须包括表的分区函数中的所有列另请参阅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

3. WEEK（）不允许作为分区函数。http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-functions.html

据我所知，如果您有一个UNIQUE AUTO_INCREMENT id作为主键，那么除了该值之外，不可能对任何内容进行分区。

我的查询都使用WHERE条件中的dtime列，因此，除非我能以某种方式在dtime上进行分区，否则（从性能角度来看）对该表进行分区是没有好处的。