关于mysql数据库单表分区的操作

mysql上如果一张单表过大，可以对其进行分区操作，这里测试了一张两千万数据的单表test666，做了分区，效果还不错，不过还需要进一步优化，才能提高查询的效率

仅做了小小的测试学习

对已有的大表进行分区
ALTER TABLE test666 PARTITION BY RANGE (id)  
(     
PARTITION p0 VALUES LESS THAN (2000000),  
PARTITION p1 VALUES LESS THAN (4000000), 
PARTITION p2 VALUES LESS THAN (6000000),
PARTITION p3 VALUES LESS THAN (8000000),
PARTITION p4 VALUES LESS THAN (10000000),
PARTITION p5 VALUES LESS THAN (12000000),
PARTITION p6 VALUES LESS THAN (14000000),
PARTITION p7 VALUES LESS THAN (16000000),  
PARTITION p8 VALUES LESS THAN (18000000),  
PARTITION p9 VALUES LESS THAN MAXVALUE ); 




对指定分区表查询
SELECT * FROM  test666  PARTITION (p0)




对已有分区再进行分区操作
ALTER TABLE test666  REORGANIZE PARTITION p0 INTO (
    PARTITION s0 VALUES LESS THAN (1000000),
    PARTITION s1 VALUES LESS THAN (2000000)
);


ALTER TABLE test666  REORGANIZE PARTITION p1 INTO (
    PARTITION s2 VALUES LESS THAN (3000000),
    PARTITION s3 VALUES LESS THAN (4000000)
);


ALTER TABLE test666 REORGANIZE PARTITION p2 INTO (
    PARTITION s4 VALUES LESS THAN (5000000),
    PARTITION s5 VALUES LESS THAN (6000000)
);


ALTER TABLE  test666 REORGANIZE PARTITION p3 INTO (
    PARTITION s6 VALUES LESS THAN (7000000),
    PARTITION s7 VALUES LESS THAN (8000000)
);


ALTER TABLE  test666 REORGANIZE PARTITION p4 INTO (
    PARTITION s8 VALUES LESS THAN (9000000),
    PARTITION s9 VALUES LESS THAN (10000000)
);


ALTER TABLE  test666 REORGANIZE PARTITION p5 INTO (
    PARTITION s10 VALUES LESS THAN (11000000),
    PARTITION s11 VALUES LESS THAN (12000000)
);


ALTER TABLE  test666 REORGANIZE PARTITION p6 INTO (
    PARTITION s12 VALUES LESS THAN (13000000),
    PARTITION s13 VALUES LESS THAN (14000000)
);


ALTER TABLE  test666 REORGANIZE PARTITION p7 INTO (
    PARTITION s14 VALUES LESS THAN (15000000),
    PARTITION s15 VALUES LESS THAN (16000000)
);


ALTER TABLE  test666 REORGANIZE PARTITION p8 INTO (
    PARTITION s16 VALUES LESS THAN (17000000),
    PARTITION s17 VALUES LESS THAN (18000000)
);


ALTER TABLE  test666 REORGANIZE PARTITION p9 INTO (
    PARTITION s18 VALUES LESS THAN (19000000),
    PARTITION s19 VALUES LESS THAN (MAXVALUE)
);