mysql表分區

標籤：mysql   分區   partition   

定界分割：

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;

本文出自 “7727197” 部落格，請務必保留此出處http://7737197.blog.51cto.com/7727197/1663098

mysql表分區