学习MySQL Partition分区方法

来源：互联网发布：学电脑编程好不好编辑：程序博客网时间：2024/05/17 19:20

参考文档：http://dev.mysql.com/doc/refman/5.1/en/partitioning-types.html

Range分区，List分区，Hash分区，Key分区，各自区别见下面的create table语句就能一目了然。

CREATE TABLE employees (    id INT NOT NULL,    fname VARCHAR(30),    lname VARCHAR(30),    hired DATE NOT NULL DEFAULT '1970-01-01',    separated DATE NOT NULL DEFAULT '9999-12-31',    job_code INT NOT NULL,    store_id INT NOT NULL)PARTITION BY RANGE (store_id) (    PARTITION p0 VALUES LESS THAN (6),    PARTITION p1 VALUES LESS THAN (11),    PARTITION p2 VALUES LESS THAN (16),    PARTITION p3 VALUES LESS THAN (21));

RegionStore ID NumbersNorth3, 5, 6, 9, 17East1, 2, 10, 11, 19, 20West4, 12, 13, 14, 18Central7, 8, 15, 16

CREATE TABLE employees (    id INT NOT NULL,    fname VARCHAR(30),    lname VARCHAR(30),    hired DATE NOT NULL DEFAULT '1970-01-01',    separated DATE NOT NULL DEFAULT '9999-12-31',    job_code INT,    store_id INT)PARTITION BY LIST(store_id) (    PARTITION pNorth VALUES IN (3,5,6,9,17),    PARTITION pEast VALUES IN (1,2,10,11,19,20),    PARTITION pWest VALUES IN (4,12,13,14,18),    PARTITION pCentral VALUES IN (7,8,15,16));

CREATE TABLE employees (    id INT NOT NULL,    fname VARCHAR(30),    lname VARCHAR(30),    hired DATE NOT NULL DEFAULT '1970-01-01',    separated DATE NOT NULL DEFAULT '9999-12-31',    job_code INT,    store_id INT)PARTITION BY HASH( YEAR(hired) )PARTITIONS 4;

CREATE TABLE k1 (    id INT NOT NULL,    name VARCHAR(20),    UNIQUE KEY (id))PARTITION BY KEY()PARTITIONS 2;

0 0