mysql分区表---range partition

偏执的太偏执、 2022-05-14 06:04 256阅读 0赞

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----