mysql分區partition詳解

1. RANGE和LIST分區的管理

針對非整形字段進行RANG\LIST分區建議使用COLUMNS分區。 

RANGE COLUMNS是RANGE分區的一種特殊類型，它與RANGE分區的差別如下：

1. RANGE COLUMNS不接受表達式，隻能是列名。而RANGE分區則要求分區的對象是整數。

2. RANGE COLUMNS允許多個列，在底層實作上，它比較的是元祖（多個列值組成的清單），而RANGE比較的是标量，即數值的大小。

3. RANGE COLUMNS不限于整數對象，date，datetime，string都可作為分區列。

CREATE TABLE rcx (
    a INT,
    b INT,
    c CHAR(3),
    d INT
)
PARTITION BY RANGE COLUMNS(a,d,c) (
    PARTITION p0 VALUES LESS THAN (5,10,'ggg'),
    PARTITION p1 VALUES LESS THAN (10,20,'mmmm'),
    PARTITION p2 VALUES LESS THAN (15,30,'sss'),
    PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
);      

View Code

添加

CREATE TABLE IF NOT EXISTS `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'xxid',
  `store_id` int(10) unsigned NOT NULL,
  `customer_surname` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='xx表' AUTO_INCREMENT=1 partition by range(id)(
    partition p0 values less than(5), 
    partition p1 values less than(10), 
    partition p2 values less than(15)
);      

追加

alter table t1 add partition(
    partition p3 values less than(20)
);      

修改分區為key類型的分區      

#這和先删除這個表、然後使用“CREATE TABLE t1 PARTITION BY KEY(id) PARTITIONS 2；”重新建立這個表具有同樣的效果
ALTER TABLE t1 partition by key(id) partition 2；      

如果希望改變表的分區而又不丢失資料，使用“ALTER TABLE ... REORGANIZE PARTITION”語句      

#分解分區  将p0拆分為s0和s1
ALTER TABLE members REORGANIZE PARTITION p0 INTO (
    PARTITION s0 VALUES LESS THAN (3),
    PARTITION s1 VALUES LESS THAN (5)
)；

#合并分區  将s0和s1合并為p0
ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (
    PARTITION p0 VALUES LESS THAN (5)
)；

#合并分區  将s0和s1合并為 r0 r1
ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (
    PARTITION s0 VALUES LESS THAN (2),
    PARTITION p0 VALUES LESS THAN (5)
)；      

删除分區

#當删除了一個分區，也同時删除了該分區中所有的資料
ALTER TABLE t1 drop partition p0,p1,p2,p3;
#如果希望從所有分區删除所有的資料，但是又保留表的定義和表的分區模式，使用TRUNCATE TABLE指令      

2. HASH和KEY分區的管理

增加：

CREATE TABLE clients(
    id INT NOT NULL,
    firstname VARCHAR(30),
    lastname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',    
signed DATE
)
PARTITION BY HASH(MONTH(signed)) 
PARTITIONS 12；
//将hash類型的分區分為12個
//基于時間類型的字段的轉換函數mysql提供了"YEAR(),MONTH(),DAY(),TO_DAYS(),TO_SECONDS(),WEEKDAY(),DAYOFYEAR()      

修改：coalesce

#将12個分區改為6個
ALTER TABLE clients COALESCE PARTITION 6；      

删除

???

3. 分區維護

。對于分區表，MySQL不支援指令CHECK TABLE，OPTIMIZE TABLE，ANALYZE TABLE，或REPAIR TABLE。作為替代，可以使用ALTER TABLE 的許多擴充來在一個或多個分區上直接地執行這些操作，如下面列出的那樣      

重建分區：

ALTER TABLE t1 REBUILD PARTITION (p0, p1)；      

優化分區：如果從分區中删除了大量的行，或者對一個帶有可變長度的行（也就是說，有VARCHAR，BLOB，或TEXT類型的列）作了許多修改，可以使用“ALTER TABLE ... OPTIMIZE PARTITION”來收回沒有使用的空間，并整理分區資料檔案的碎片      

4. 擷取關于分區的資訊

#在一個給定的分區表上使用“OPTIMIZE PARTITION”等同于在那個分區上運作CHECK PARTITION，ANALYZE PARTITION，和REPAIR PARTITION。 
ALTER TABLE t1 OPTIMIZE PARTITION (p0, p1)；      

分析分區：讀取并儲存分區的鍵分布      

ALTER TABLE t1 ANALYZE PARTITION (p3)；      

修補分區： 修補被破壞的分區。      

ALTER TABLE t1 REPAIR PARTITION (p0,p1);      

檢查分區： 可以使用幾乎與對非分區表使用CHECK TABLE 相同的方式檢查分區      

#這個指令可以告訴你表t1的分區p1中的資料或索引是否已經被破壞。如果發生了這種情況，使用“ALTER TABLE ... REPAIR PARTITION”來修補該分區
ALTER TABLE trb3 CHECK PARTITION (p1)；
#還可以使用mysqlcheck或myisamchk 應用程式，在對表進行分區時所産生的、單獨的MYI檔案上進行操作，來完成這些任務。請參見8.7節，“mysqlcheck：表維護和維修程式”      

4.擷取關于分區的資訊

檢視建表結構

SHOW CREATE TABLE  表名;#目前，對于按HASH或KEY分區的表，PARTITIONS子句并不顯示      

 檢視是否支援分區

SHOW VARIABLES LIKE '%partition%';      

表的各種參數狀态

SHOW TABLE STATUS      

 檢視分區情況：

select
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
from information_schema.partitions where
table_schema = schema()
and table_name='填入你要檢視的表名';      

select * from information_schema.partitions;      

explain partitions select * from mdl_user;