mysql分区框架_MySQL分区(mysql partition)[zz]

RANGE 类型

CREATE TABLE users (

uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(30) NOT NULL DEFAULT '',

email VARCHAR(30) NOT NULL DEFAULT ''

)

PARTITION BY RANGE (uid) (

PARTITION p0 VALUES LESS THAN (3000000)

DATA DIRECTORY = '/data0/data'

INDEX DIRECTORY = '/data1/idx',

PARTITION p1 VALUES LESS THAN (6000000)

DATA DIRECTORY = '/data2/data'

INDEX DIRECTORY = '/data3/idx',

PARTITION p2 VALUES LESS THAN (9000000)

DATA DIRECTORY = '/data4/data'

INDEX DIRECTORY = '/data5/idx',

PARTITION p3 VALUES LESS THAN MAXVALUE     DATA DIRECTORY = '/data6/data'

INDEX DIRECTORY = '/data7/idx'

);

在这里，将用户表分成4个分区，以每300万条记录为界限，每个分区都有自己独立的数据、索引文件的存放目录，与此同时，这些目录所在的>

物理磁盘分区可能也都是完全独立的，可以多大提高了磁盘IO吞吐量。

LIST 类型

CREATE TABLE category (

cid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(30) NOT NULL DEFAULT ''

)

PARTITION BY LIST (cid) (

PARTITION p0 VALUES IN (0,4,8,12)

DATA DIRECTORY = '/data0/data'

INDEX DIRECTORY = '/data1/idx',

PARTITION p1 VALUES IN (1,5,9,13)

DATA DIRECTORY = '/data2/data'

INDEX DIRECTORY = '/data3/idx',

PARTITION p2 VALUES IN (2,6,10,14)

DATA DIRECTORY = '/data4/data'

INDEX DIRECTORY = '/data5/idx',

PARTITION p3 VALUES IN (3,7,11,15)

DATA DIRECTORY = '/data6/data'

INDEX DIRECTORY = '/data7/idx'

);

分成4个区，数据文件和索引文件单独存放。

HASH 类型

CREATE TABLE users (

uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(30) NOT NULL DEFAULT '',

email VARCHAR(30) NOT NULL DEFAULT ''

)

PARTITION BY HASH (uid) PARTITIONS 4 (

PARTITION p0

DATA DIRECTORY = '/data0/data'

INDEX DIRECTORY = '/data1/idx',

PARTITION p1

DATA DIRECTORY = '/data2/data'

INDEX DIRECTORY = '/data3/idx',

PARTITION p2

DATA DIRECTORY = '/data4/data'

INDEX DIRECTORY = '/data5/idx',

PARTITION p3

DATA DIRECTORY = '/data6/data'

INDEX DIRECTORY = '/data7/idx'

);

分成4个区，数据文件和索引文件单独存放。

KEY 类型

REATE TABLE users (

uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(30) NOT NULL DEFAULT '',

email VARCHAR(30) NOT NULL DEFAULT ''

)

PARTITION BY KEY (uid) PARTITIONS 4 (

PARTITION p0

DATA DIRECTORY = '/data0/data'

INDEX DIRECTORY = '/data1/idx',

PARTITION p1

DATA DIRECTORY = '/data2/data'

INDEX DIRECTORY = '/data3/idx',

PARTITION p2

DATA DIRECTORY = '/data4/data'

INDEX DIRECTORY = '/data5/idx',

PARTITION p3

DATA DIRECTORY = '/data6/data'

INDEX DIRECTORY = '/data7/idx'

);

分成4个区，数据文件和索引文件单独存放。

子分区

子分区是针对 RANGE/LIST 类型的分区表中每个分区的再次分割。再次分割可以是 HASH/KEY 等类型。例如：

CREATE TABLE users (

uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(30) NOT NULL DEFAULT '',

email VARCHAR(30) NOT NULL DEFAULT ''

)

PARTITION BY RANGE (uid) SUBPARTITION BY HASH (uid % 4) SUBPARTITIONS 2(

PARTITION p0 VALUES LESS THAN (3000000)

DATA DIRECTORY = '/data0/data'

INDEX DIRECTORY = '/data1/idx',

PARTITION p1 VALUES LESS THAN (6000000)

DATA DIRECTORY = '/data2/data'

INDEX DIRECTORY = '/data3/idx'

);

对 RANGE 分区再次进行子分区划分，子分区采用 HASH 类型。

或者

CREATE TABLE users (

uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(30) NOT NULL DEFAULT '',

email VARCHAR(30) NOT NULL DEFAULT ''

)

PARTITION BY RANGE (uid) SUBPARTITION BY KEY(uid) SUBPARTITIONS 2(

PARTITION p0 VALUES LESS THAN (3000000)

DATA DIRECTORY = '/data0/data'

INDEX DIRECTORY = '/data1/idx',

PARTITION p1 VALUES LESS THAN (6000000)

DATA DIRECTORY = '/data2/data'

INDEX DIRECTORY = '/data3/idx'

);

对 RANGE 分区再次进行子分区划分，子分区采用 KEY 类型。