mysql 脚本分区 mysql分区实战

自5.1开始对分区(Partition)有支持

= 水平分区（根据列属性按行分）=
* Range（范围） – 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区，80年代（1980's）的数据，90年代（1990's）的数据以及任何在2000年（包括2000年）后的数据。 
* Hash（哈希） – 这中模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区，。例如DBA可以建立一个对表主键进行分区的表。 
* Key（键值） – 上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。 
* List（预定义列表） – 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如：DBA建立了一个横跨三个分区的表，分别根据2004年2005年和2006年值所对应的数据。 
* Composite（复合模式） - 很神秘吧，哈哈，其实是以上模式的组合使用而已，就不解释了。举例：在初始化已经进行了Range范围分区的表上，我们可以对其中一个分区再进行hash哈希分区。 

= 垂直分区（按列分）=
举个简单例子：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就要把这些不经常使用的text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。

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

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

分区管理：
删除分区及分区里的数据,会丢失数据
ALERT TABLE users DROP PARTITION p0;
不可以删除hash或者key分区.一次性删除多个分区，
alter table emp drop partition p1,p2;

分区合并,不会丢失数据
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));
hash和key分区不能用REORGANIZE

分区分解,不会丢失数据
alter table te  reorganize partition p1 into (  partition p1 values less than (100), 
partition p2 values less than (200) );

增加分区,不会丢失数据
ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19)
            DATA DIRECTORY = '/data8/data'
            INDEX DIRECTORY = '/data9/idx');
alter table emp add partition (partition p3 values less than (4000));
ALTER TABLE users ADD PARTITION PARTITIONS 8;			

给已有的表分区,不会丢失数据
alter table results partition by RANGE (year(ttime)) 
(PARTITION p0 VALUES LESS THAN (1999),
PARTITION p1 VALUES LESS THAN (2000) , PARTITION p2 VALUES LESS THAN (2001) ,
PARTITION p3 VALUES LESS THAN (2002) ,PARTITION p4 VALUES LESS THAN MAXVALUE
); 

移除表的分区,表会变成普通表,不会丢失数据
alter table users remove partitioning;

重新定义分区表,不会丢失数据
Alter table emp partition by hash(salary) partitions 7; 
Alter table emp partition by range(salary) (  
 partition p1 values less than (2000),
 partition p2 values less than (4000) );

重建分区
这和先删除保存在分区中的所有记录，然后重新插入它们，具有同样的效果。它可用于整理分区碎片
ALTER TABLE emp rebuild partition p1,p2; 

优化分区
ALTER TABLE emp optimize partition p1,p2;

分析分区
ALTER TABLE emp analyze partition p1,p2;

检查分区
ALTER TABLE emp check partition p1,p2;

修补分区
ALTER TABLE emp repair partition p1,p2;

截断分区
ALTER TABLE t5 TRUNCATE PARTITION p1;

将指定分区的记录置换到普通表中，普通表的定义应该与分区表相同，包括约束、索引等
ALTER TABLE t5 exchange PARTITION p0 WITH TABLE t15;