mysql分区表---range partition

author：skate
time：2012/11/28

 

mysql分区表---range partition

range partition

创建范围分区：
CREATE TABLE `employees1` (
  `id` int(11) NOT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job_code` int(11) NOT NULL,
  `store_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (store_id)
(PARTITION p0 VALUES LESS THAN (6) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (11) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (16) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (21) ENGINE = InnoDB) */

 

使用函数表达式创建范围分区
 CREATE TABLE `employees2` (
  `id` int(11) NOT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job_code` int(11) DEFAULT NULL,
  `store_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( YEAR(separated))
(PARTITION pm1 VALUES LESS THAN (2006) ENGINE = InnoDB,
 PARTITION pm2 VALUES LESS THAN (2011) ENGINE = InnoDB) */
 
 
维护命令：

添加分区
alter table employees2 add partition (partition p0 values less than(1991));  //只能添加大于分区键的分区

删除分区
alter table employees2 drop partition p0; //可以删除任意分区

删除分区数据
alter table employees2 truncate partition p1,p2;
alter table employees2 truncate partition all;
或
delete from employees2 where separated < '2006-01-01' or (separated >= '2006-01-01' and separated<'2011-01-01');

重定义分区（包括重命名分区，伴随移动数据；合并分区)
alter table employees2 reorganize partition p1,p3,p4 into (partition pm1 values less than(2006),
partition pm2 values less than(2011));
 
rebuild重建分区
alter  table employees2 rebuild partition pm1/all; //相当于drop所有记录，然后再reinsert；可以解决磁盘碎片

优化表
alter  table tt2 optimize partition pm1; //在大量delete表数据后，可以回收空间和碎片整理。但在5.5.30后支持。在5.5.30之前可以通过recreate+analyze来替代，如果用rebuild+analyze速度慢

analzye表
alter  table employees2 analyze partition pm1/all;

check表
alter  table employees2 check partition pm1/all;
 

show create table employees2;  //查看分区表的定义
show table status like 'employees2'\G;    //查看表时候是分区表 如“Create_options: partitioned”
select * from information_schema.KEY_COLUMN_USAGE where table_name='employees2';   //查看索引
SELECT * FROM information_schema.partitions WHERE table_name='employees2'   //查看分区表
explain partitions select * from employees2 where separated < '1990-01-01' or separated > '2016-01-01';   //查看分区是否被select使用
 
 
 
注意：
1. primary key和unique key必须包含在分区key的一部分，否则在创建primary key和unique index时会报”ERROR 1503 (HY000)“

mysql> create unique index idx_employees1_job_code on employees1(job_code);
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function
或
mysql> ALTER TABLE `skate`.`employees1` ADD PRIMARY KEY (`id`) ;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

2. 范围分区添加分区只能在最大值后面追加分区
3. 所有分区的engine必须一样
4. 范围分区分区字段：integer、数值表达式、日期列，日期函数表达式(如year(),to_days(),to_seconds(),unix_timestamp())

 

 

 

------end----