MySQL 分区表相关

MySQL 分区表分为RANGE、LIST、HASH、KEY四种类型，分区表的索引可以局部针对分区表建立。

一、 创建

1. 创建范围分区表

MySQL 分区表的分区键order_day必须包含在主键中，且会产生一个问题——当年份超过阈值，到了2013,2014时，需要手动创建这些分区

CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    amount DOUBLE NOT NULL,
    order_day DATETIME NOT NULL,
    PRIMARY KEY(id, order_day)
) ENGINE=Innodb PARTITION BY RANGE(YEAR(order_day)) (
    PARTITION p_01 VALUES LESS THAN (2010),
    PARTITION p_2011 VALUES LESS THAN (2011),
    PARTITION p_2012 VALUES LESS THAN (2012),
    PARTITION p_catchall VALUES LESS THAN MAXVALUE);

2. 创建hash分区表

以下语句表示每100W条数据建立一个分区，且没有阈值范围的影响

CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    amount DOUBLE NOT NULL,
    order_day DATETIME NOT NULL
) ENGINE=Innodb PARTITION BY HASH(id DIV 1000000);

 

二、 分区表的管理操作

• 查看是否支持分区

mysql> show variables like "%part%";  
+-------------------+-------+  
| Variable_name     | Value |  
+-------------------+-------+  
| have_partitioning | YES   |  
+-------------------+-------+  
1 row in set (0.00 sec)  

• 查表的各个分区

SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'history_uint';

• 删除分区

注意不可以删除hash或者key分区

alter table emp drop partition p1;
-- 一次性删除多个分区
alter table emp drop partition p1,p2;

• 增加分区

alter table emp add partition (partition p3 values less than (4000));
alter table empl add partition (partition p3 values in (40));

• 拆分分区

Reorganize partition可以对表的部分分区或全部分区进行修改，并且不会丢失数据，拆分前后分区的整体范围应该一致。

alter table te reorganize partition p1 into
(partition p1 values less than (100),
partition p3 values less than (1000)); ----不会丢失数据

• 合并分区

-- Merge分区：把2个分区合并为一个
alter table te reorganize partition p1,p3 into
(partition p1 values less than (1000)); ----不会丢失数据

• 重新定义hash分区表

Alter table emp partition by hash(salary) partitions 7;

• 重新定义range分区表

Alter table emp partition by range(salary)
(partition p1 values less than (2000),
partition p2 values less than (4000));

• 删除表的所有分区

相当于变回普通表，注意不是删数据。

Alter table emp remove partitioning;--不会丢失数据

• 重建分区

相当于先删除分区中的所有记录，然后重新插入它们，可用于整理分区碎片。

ALTER TABLE emp rebuild partitionp1,p2;

• 优化分区

如果从分区中删除了大量的行，或者对一个带有可变长度的行（VARCHAR，BLOB或TEXT类型的列）作了许多修改，可以使用“ALTER TABLE ... OPTIMIZE PARTITION”来收回没有使用的空间，并整理分区数据文件的碎片。

ALTER TABLE emp optimize partition p1,p2;

• 分析分区

读取并保存分区的键分布。

ALTER TABLE emp analyze partition p1,p2;

• 检查分区

检查分区，类似于对非分区表使用CHECK TABLE。这个命令可以告诉你表emp的分区p1,p2中的数据或索引是否已经被破坏。如果已被破坏，可使用修补命令修补该分区。

ALTER TABLE emp CHECK partition p1,p2;

• 修补分区

修补被破坏的分区

ALTER TABLE emp repair partition p1,p2;