mysql分区

mysql分区分表介绍

分区允许根据指定的规则，跨文件系统分配单个表的多个部分。表的不同部分在不同的位置被存储为单独的表。MySQL从5.1.3开始支持Partition。

MySQL支持RANGE，LIST，HASH，KEY分区类型，其中以RANGE最为常用：

• Range（范围）–这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区。

• Hash（哈希）–这中模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。

• Key（键值）-上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。

• List（预定义列表）–这种模式允许系统通过预定义的列表的值来对数据进行分割。

• Composite（复合模式） –以上模式的组合使用

分区例子

在这里，将用户表分成4个分区，以每300万条记录为界限，每个分区都有自己独立的数据、索引文件的存放目录，与此同时，这些目录所在的物理磁盘分区可能也都是完全独立的，可以提高磁盘IO吞吐量。

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‘
 );

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‘
 );

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‘
 );

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 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‘
 );

子分区

子分区是针对 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‘
 );

常用命令

删除分区

ALERT TABLE users DROP PARTITION p0;

重建分区

ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));

合并两个分区放到新的分区里

ALTER TABLE users REORGANIZE PARTITION p1,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));

新增分区

 ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19)
 DATA DIRECTORY = ‘/data8/data‘
 INDEX DIRECTORY = ‘/data9/idx‘);

给已有表添加分区

 alter table results partition by RANGE (month(ttime))
 (PARTITION p0 VALUES LESS THAN (1),
 PARTITION p1 VALUES LESS THAN (2) , PARTITION p2 VALUES LESS THAN (3) ,
 PARTITION p3 VALUES LESS THAN (4) , PARTITION p4 VALUES LESS THAN (5) ,
 PARTITION p5 VALUES LESS THAN (6) , PARTITION p6 VALUES LESS THAN (7) ,
 PARTITION p7 VALUES LESS THAN (8) , PARTITION p8 VALUES LESS THAN (9) ,
 PARTITION p9 VALUES LESS THAN (10) , PARTITION p10 VALUES LESS THAN (11),
 PARTITION p11 VALUES LESS THAN (12),
 PARTITION P12 VALUES LESS THAN (13) );