MySQL分区的概念总结

admin 发表于 [MySQL] 分类,标签: MySQL命令行 性能优化
0

一、开始

判断你的MySQL是否支持分区,使用命令:

mysql> SHOW VARIABLES LIKE '%partition%';
 
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| have_partition_engine | YES   |
+-----------------------+-------+
1 row in set (0.00 sec)

在如上列出的一个正确的SHOW VARIABLES   命令所产生的输出中,如果没有看到变量have_partition_engine 的值为YES ,那么 MySQL 的版本就不支持分区。

 

对于创建了分区的表,可以使用你的MySQL  服务器所支持的任何存储引擎;MySQL  分区引擎在一个单独的层中运行,并且可以和任何这样的层进行相互作用。在MySQL 5.1 版中,同一个分区表的所有分区必须使用同一个存储引擎;例如,不能对一个分区使用MyISAM ,而对另一个使用InnoDB 。但是,这并不妨碍在同一个  MySQL  服务器中,甚至在同一个数据库中,对于不同的分区表使用不同的存储引擎。

要为某个分区表配置一个专门的存储引擎,必须且只能使用[STORAGE] ENGINE   选项(也就是你只能使用ENGINE=存储引擎这样的格式,而不能使用 TYPE= 存储引擎 ),这如同为非分区表配置存储引擎一样。但是,必须记住CREATE TABLE 语句中的[STORAGE] ENGINE (和其他的表选项)必须放在 任何分区选项之前。下面的例子给出了怎样创建一个 使用InnoDB 存储引擎,并通过HASH 分成6 个分区的表:

CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
    ENGINE=INNODB
    PARTITION BY HASH(MONTH(tr_date))
    PARTITIONS 6;

(注释:每个PARTITION   子句可以包含一个 [STORAGE] ENGINE   选项,但是在MySQL 5.1 版本中,这没有作用)

分区会应用于一个表的所有数据和索引。

可以在 创建分区表的CREATE TABLE 语句中的 PARTITION 子句使用DATA DIRECTORY = '/disk0/data' (数据路径)INDEX DIRECTORY = '/disk0/idx' (索引路径)选项,为每个分区的数据和索引指定特定路径。此外,MAX_ROWS MIN_ROWS 选项可以用来设定最大和最小的行数,它们可以各自保存在每个分区里。

二、分区的方式

 

RANGE   分区 基于属于一个给定连续区间的列值,把多行分配给分区。采用如下PARTITON子句:

 

PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN MAXVALUE
)

 

LIST   分区 类似于按RANGE 分区,区别在于LIST 分区是基于列值匹配一个离散值集合中的某个值来进行选择。采用如下PARTITON子句:

PARTITION BY LIST(store_id)
    PARTITION p0 VALUES IN (3,5,6,9,17),
    PARTITION p1 VALUES IN (1,2,10,11,19,20),
    PARTITION p2 VALUES IN (4,12,13,14,18),
    PARTITION p3 VALUES IN (7,8,15,16)
)

HASH 分区 基于用户所定义的表达式的返回值来进行有选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL  中有效的、产生非负整数值的任何表达式。采用如下PARTITON子句:

PARTITION BY HASH(YEAR(hired))
PARTITIONS 4

KEY   分区 :类似于按HASH 分区,区别在于KEY 分区只支持计算一列或多列,且MySQL  服务器提供其自身的哈希函数。必须有一列或多列包含整数值。采用如下PARTITON子句:

PARTITION BY KEY (col1)
PARTITIONS 3

三、 RANGE和LIST分区的管理

1、更改分区类型

ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2;

这和先删除这个表、然后使用“CREATE TABLE trb3 PARTITION BY KEY( id) PARTITIONS 2 ; ”重新创建这个表具有同样的效果。

MySQL 5.1 发布前的版本中,“ALTER TABLE ... PARTITION BY ...” 还没有实现。作为替代,要么使用先删除表,然后使用想要的分区重建表,或者—— 如果需要保留已经存储在表中的数据—— 可以使用“CREATE TABLE ... SELECT ...” 来创建新的表,然后从旧表中把数据拷贝到新表中,再删除旧表,如有必要,最后重新命名新表。

2、删除分区

要删除名字为p2 的分区,执行下面的命令:

ALTER TABLE tr DROP PARTITION p2;

当删除了一个分区,也同时删除了该分区中所有的数据。

如果希望从所有分区删除所有的数据,但是又保留表的定义和表的分区模式,使用TRUNCATE TABLE (语法: TRUNCATE [TABLE] tbl_name 命令。

3、移动分区

如果希望改变表的分区而又不丢失数据,使用“ALTER TABLE ... REORGANIZE PARTITION 语句

“REORGANIZE PARTITION” 的基本语法是:

ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO (partition_definitions)



其中,
tbl_name   是分区表的名称;
partition_list   是通过逗号分开的、一个或多个将要被改变的现有分区的列表;
partition_definitions   是一个是通过逗号分开的、新分区定义的列表,它遵循与用在“CREATE TABLE” 中的partition_definitions   相同的规则。

例如,把members表中的分区p0根据条件移动(分拆)到s0、s1:

ALTER TABLE members REORGANIZE PARTITION p0 INTO (
    PARTITION s0 VALUES LESS THAN (1960),
    PARTITION s1 VALUES LESS THAN (1970)
);

或者,把相邻的两个分区s0,s1移动(合并)到p0:

ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (
    PARTITION p0 VALUES LESS THAN (1970)
);

 

应当注意到,在把多少个分区合并到一个分区或把一个分区拆分成多少个分区方面,没有限制。例如,可以重新组织成员表的四个分区成两个分区,具体实现如下:

ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (
    PARTITION m0 VALUES LESS THAN (1980),
    PARTITION m1 VALUES LESS THAN (2000)
);

同样,对于按LIST 分区的表,也可以使用REORGANIZE PARTITION 。让我们回到那个问题,即增加一个新的分区到已经按照LIST 分区的表tt 中,但是因为该新分区有一个值已经存在于现有分区的值列表中,添加新的分区失败。我们可以通过先 添加只包含非冲突值的分区,然后重新组织该新分区和现有的那个分区,以便保存在现有的那个分区中的值现在移到了新的分区中,来处理这个问题:

ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
    PARTITION p1 VALUES IN (6, 18),
    PARTITION np VALUES in (4, 8, 12)
);
 

3、添加分区:

要增加一个新的RANGELIST 分区到一个前面已经分区了的表,使用“ALTER TABLE ... ADD PARTITION 语句。对于使用RANGE 分区的表,可以用这个语句添加新的区间到已有分区的序列的前面或后面。

例如,添加RANGE分区:

ALTER TABLE mytable ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));

给已存在的表加上分区:

ALTER TABLE no_part_table PARTITION BY RANGE (month(c3))(
PARTITION p0 VALUES LESS THAN (1000000),
PARTITION p1 VALUES LESS THAN (2000000) , 
PARTITION p2 VALUES LESS THAN (3000000) ,
PARTITION P12 VALUES LESS THAN MAXVALUE 
);

对于通过RANGE 分区的表,只可以使用 ADD PARTITION 添加新的分区到分区列表的高端。

添加LIST分区:

ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));

注意:不能添加 一个 包含有已经包含在现有分区值列表中的任意值de 分区。如果试图这样做,将会导致错误。

 

四、HASH和KEY分区的管理

http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#alter-table


五、分区维护

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

示例:

ALTER TABLE t1 REBUILD PARTITION (p0, p1)

;



·          优化分区: 如果从分区中删除了大量的行,或者对一个带有可变长度的行(也就是说,有VARCHARBLOB ,或TEXT 类型的列 )作了许多修改,可以使用“ALTER TABLE ... OPTIMIZE PARTITION” 来收回没有使用的空间,并整理分区数据文件的 碎片。

示例: ALTER TABLE t1 OPTIMIZE PARTITION (p0, p1) ;

在一个给定的分区表上使用“OPTIMIZE PARTITION” 等同于在那个分区上运行CHECK PARTITIONANALYZE PARTITION ,和REPAIR PARTITION

·          分析分区: 读取并保存分区的键分布。

示例: ALTER TABLE t1 ANALYZE PARTITION (p3);

·          修补分区:  修补被破坏的分区。

示例: ALTER TABLE t1 REPAIR PARTITION (p0,p1);

·          检查分区:  可以使用几乎与对非分区表使用CHECK TABLE   相同的方式检查分区。

示例: ALTER TABLE trb3 CHECK PARTITION (p1) ;


这个命令可以告诉你表t1 的分区p1 中的数据或索引是否已经被破坏。如果发生了这种情况,使用“ALTER TABLE ... REPAIR PARTITION” 来修补该分区。

还可以使用mysqlcheck myisamchk   应用程序,在对表进行分区时所产生的、单独的MYI 文件上 进行操作,来完成这些任务。


发表我的评论