mysql表分区

321312

范围分区：

CREATE TABLE BIGTABLE
(
ID INT,
SNPTIME DATETIME NOT NULL,
VALUE VARCHAR(20),
PRIMARY KEY (SNPTIME, ID)
) ENGINE=InnoDB
partition by range (TO_DAYS(SNPTIME))
(
PARTITION p1 VALUES LESS THAN (to_days('2009-1-31')),
PARTITION p2 VALUES LESS THAN (to_days('2009-2-28')),
PARTITION p3 VALUES LESS THAN (to_days('2008-3-31')),
PARTITION p4 VALUES LESS THAN (to_days('2008-4-30')),
PARTITION p5 VALUES LESS THAN (to_days('2008-5-31')),
PARTITION p6 VALUES LESS THAN (to_days('2008-6-30')),
PARTITION p7 VALUES LESS THAN (to_days('2008-7-31')),
PARTITION p8 VALUES LESS THAN (to_days('2008-8-31')),
PARTITION p9 VALUES LESS THAN (to_days('2008-9-30')),
PARTITION p10 VALUES LESS THAN (to_days('2008-10-31')),
PARTITION p11 VALUES LESS THAN (to_days('2008-11-30')),
PARTITION p12 VALUES LESS THAN (to_days('2008-12-31')),
PARTITION p13VALUES LESS THAN MAXVALUE
) ;
注意一点：一定要有主键，并且主键要包括分区键。
但是，如果必须要分区，而分区中的分区键不想使用（业务不允许）主键的时候，可以采用两步走的办法。
1、建立表，带有主键。
2、删除主键，建立独立索引。
这样在插入数据的时候还是能够按部就班地进入各自所属的分区表。

给已存在的表加分区
ALTER TABLE SNP_SWITCH
partition by RANGE (TO_DAYS(RPTTIME))
(PARTITION P1210 VALUES LESS THAN (735172) ENGINE = MYISAM,
PARTITION P1211 VALUES LESS THAN (735202) ENGINE = MYISAM,
PARTITION P1212 VALUES LESS THAN (735233) ENGINE = MYISAM,
PARTITION P1301 VALUES LESS THAN (735264) ENGINE = MYISAM,
PARTITION P1302 VALUES LESS THAN (735292) ENGINE = MYISAM,
PARTITION P1303 VALUES LESS THAN (735323) ENGINE = MYISAM,
PARTITION P1304 VALUES LESS THAN (735353) ENGINE = MYISAM,
PARTITION P1305 VALUES LESS THAN (735384) ENGINE = MYISAM,
PARTITION P1306 VALUES LESS THAN (735414) ENGINE = MYISAM,
PARTITION P1307 VALUES LESS THAN (735445) ENGINE = MYISAM,
PARTITION P1308 VALUES LESS THAN (735476) ENGINE = MYISAM,
PARTITION P1309 VALUES LESS THAN (735506) ENGINE = MYISAM,
PARTITION P1310 VALUES LESS THAN (735537) ENGINE = MYISAM,
PARTITION P1311 VALUES LESS THAN (735567) ENGINE = MYISAM,
PARTITION P1312 VALUES LESS THAN (735598) ENGINE = MYISAM,
PARTITION P1401 VALUES LESS THAN (735629) ENGINE = MYISAM,
PARTITION P1402 VALUES LESS THAN (735657) ENGINE = MYISAM,
PARTITION P1403 VALUES LESS THAN (735688) ENGINE = MYISAM,
PARTITION P1404 VALUES LESS THAN (735718) ENGINE = MYISAM,
PARTITION P1405 VALUES LESS THAN (735749) ENGINE = MYISAM,
PARTITION P1406 VALUES LESS THAN (735779) ENGINE = MYISAM,
PARTITION P1407 VALUES LESS THAN (735810) ENGINE = MYISAM,
PARTITION P1408 VALUES LESS THAN (735841) ENGINE = MYISAM,
PARTITION P1409 VALUES LESS THAN (735871) ENGINE = MYISAM,
PARTITION P1410 VALUES LESS THAN (735902) ENGINE = MYISAM,
PARTITION P1411 VALUES LESS THAN (735932) ENGINE = MYISAM,
PARTITION P1412 VALUES LESS THAN (735963) ENGINE = MYISAM,
PARTITION P1501 VALUES LESS THAN (735994) ENGINE = MYISAM,
PARTITION P1502 VALUES LESS THAN (736022) ENGINE = MYISAM,
PARTITION P1503 VALUES LESS THAN (736053) ENGINE = MYISAM,
PARTITION P1504 VALUES LESS THAN (736083) ENGINE = MYISAM,
PARTITION P1505 VALUES LESS THAN (736114) ENGINE = MYISAM,
PARTITION P1506 VALUES LESS THAN (736144) ENGINE = MYISAM,
PARTITION P1507 VALUES LESS THAN (736175) ENGINE = MYISAM,
PARTITION P1508 VALUES LESS THAN (736206) ENGINE = MYISAM,
PARTITION P1509 VALUES LESS THAN (736236) ENGINE = MYISAM,
PARTITION P1510 VALUES LESS THAN (736267) ENGINE = MYISAM,
PARTITION P1511 VALUES LESS THAN (736297) ENGINE = MYISAM,
PARTITION P1512 VALUES LESS THAN (736328) ENGINE = MYISAM,
PARTITION P1600 VALUES LESS THAN (741807) ENGINE = MYISAM);

HASH分区：
create table k1(v1 int, v2 int, v3 int, d datetime) partition by hash (to_days(d)) partitions 3660;