MySQL分区表实践

MySQL分区表实践

MySQL分区概述： 

允许根据可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。用户所选择的、实现数据分割的规则被称为分区函数，这在MySQL中它可以是模数，或者是简单的匹配一个连续的数值区间或数值列表，或者是一个内部HASH函数，或一个线性HASH函数。函数根据用户指定的分区类型来选择，把用户提供的表达式的值作为参数。该表达式可以是一个整数列值，或一个作用在一个或多个列值上并返回一个整数的函数。[z1] 。这个表达式的值传递给分区函数，分区函数返回一个表示那个特定记录应该保存在哪个分区的序号。这个函数不能是常数，也不能是任意数。它不能包含任何查询，但是实际上可以使用MySQL 中任何可用的SQL表达式，只要该表达式返回一个小于MAXVALUE（最大可能的正整数）的正数值。

由于MySQL无全局索引的概念，只有本地分区索引，基于此种原因，一个表中如果有2个或2个以上的唯一索引，此表无法分区。分区函数应用的数据库的列必须是MySQL的主键，否则不能分区。[z2] 

对于创建了分区的表，可以使用你的MySQL 服务器所支持的任何存储引擎。在MySQL 5.1版中，同一个分区表的所有分区必须使用同一个存储引擎；例如，不能对一个分区使用MyISAM，而对另一个使用InnoDB。但是，这并不妨碍在同一个 MySQL 服务器中，甚至在同一个数据库中，对于不同的分区表使用不同的存储引擎。 

MySQL分区的建立

MySQL可以建立四种分区类型的分区：  

          RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。详情参见18.2.1节，“RANGE分区”。

·         LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。详情参见18.2.2节，“LIST分区”。

·         HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。详情参见18.2.3节，“HASH分区”。

·         KEY 分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。详情参照：18.2.4. KEY分区。

        子分区：子分区是分区表中每个分区的再次分割。书写格式参照：18.2.5. 子分区

        （1）关于子分区应注意的地方： 每个分区必须有相同数量的子分区。

·       （2）如果在一个分区表上的任何分区上使用SUBPARTITION 来明确定义任何子分区，那么就必须定义所有的子分区。

在建立分区的时候可以指定分区的数据存储位置和索引位置，这样可以跨磁盘或者文件系统保存不同的数据。数据分磁盘存储可以一定程度上增加数据读取速度，因为采用多磁盘后，每个磁盘的I/O操作会降低。而且采用指定分区存储位置能够增大存储量。 

无论使用何种类型的分区，分区总是在创建时就自动的顺序编号，且从0开始记录，记住这一点非常重要。当有一新行插入到一个分区表中时，就是使用这些分区编号来识别正确的分区。例如，如果你的表使用4个分区，那么这些分区就编号为0, 1, 2, 和3。对于RANGE和LIST分区类型，确认每个分区编号都定义了一个分区，很有必要。对HASH分区，使用的用户函数必须返回一个大于0的整数值。对于KEY分区，这个问题通过MySQL服务器内部使用的 哈希函数自动进行处理。注意：分区的名字是不区分大小写的，且对于RANGE分区和LIST分区，分区的名称是不能重复的。这几种可根据不同的需求来选择，比较常用的是RANGE分区。 

常用的MySQL的分区管理：

 RANGE 和LIST分区管理

分区对于程序来说是透明的，而且只有删除能在分区层面上操作，其他如查询、修改、增加都不能指定分区。 

ALTER TABLE …DROPPARTITION ….(删除分区)

ALTER TABLE … ADD PARTITION (PARTITION p3 VALUESLESS THAN (…));[z3] 增加分区

ALTER TABLE ... REORGANIZE PARTITION …,… INTO (

   PARTITION p0 VALUES LESS THAN (…)

)；[z4] 合并拆分分区。 

HASH和KEY分区管理

添加分区和RANGE、LIST分区方式相同，对于修改分区，不能使用与从按照RANGE或LIST分区的表中删除分区相同的方式，来从HASH或KEY分区的表中删除分区。但是，可以使用“ALTERTABLE ... COALESCE PARTITION”命令来合并HASH或KEY分区。

如果要查看分区的信息，可以通过sql语句来查询

SELECT * FROM INFORMATION_SCHEMA.partitions WHERETABLE_SCHEMA = schema() AND TABLE_NAME='xxx’

1 2 3 4 下一页