MySQL Table partitioning

Range Partitioning:

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

) ;

Note: Be sure to have a primary key and the primary key to include the partition key.

However, if you have to partition, and the partition key in the partition does not want to use (business does not allow) the primary key, you can take a two-step approach.

1, establish the table, with the primary key.

2, delete the primary key, establish independent index.

This allows you to step into the respective partition table when inserting data.

Adding partitions to existing tables

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 partition:

Create TABLE K1 (v1 int, v2 int, v3 int, d datetime) partition by hash (To_days (d)) partitions 3660;

This article is from the "7727197" blog, please be sure to keep this source http://7737197.blog.51cto.com/7727197/1663098

MySQL Table partitioning