MySQL数据库分区的一些应用

[mrl...@sina.com.cn]

MySQL数据库分区的一些应用
http://freddy.cc/article/101

分区的一些优点：

1)与单个磁盘或文件系统分区相比，可以存储更多的数据。

2)对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通
过为那些新数据专门增加一个新的分区来很方便地实现。

3)一些查询可以得到极大的优化，这主要是借助于满足一个给定where 语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的
分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。

4)涉及到例如sum() 和 count()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “select
salesperson_id, count(orders) as order_total from sales group by
salesperson_id；”。通过“并行”， 这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。

5)通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

mysql支持的分区类型

range 分区：基于属于一个给定连续区间的列值进行分配

create table employees (
id int not null,
fname varchar(30),
lname varchar(30),
hired date not null default '1970-01-01',
separated date not null default '9999-12-31',
job_code int not null,
store_id int not null
)
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分区中每个分区的定义和选择是基于某列的值从属于一个集合，而range分区是从属
于一个连续区间值的集合

create table employees (
id int not null,
fname varchar(30),
lname varchar(30),
hired date not null default '1970-01-01',
separated date not null default '9999-12-31',
job_code int,
store_id int
)
partition by list(store_id)
partition pnorth values in (3,5,6,9,17),
partition peast values in (1,2,10,11,19,20),
partition pwest values in (4,12,13,14,18),
partition pcentral values in (7,8,15,16)
)
如果试图插入列值（或分区表达式的返回值）不在分区值列表中的一行时，那么“insert”查询将失败并报错

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

hash分区主要用来确保数据在预先确定数目的分区中平均分布。在range和list分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分
区中；而在hash分区中，mysql 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成
的分区数量。

要使用hash分区来分割一个表，要在create table 语句上添加一个“partition by hash (expr)”子句，其
中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为mysql 整型的一列的名字。此外，你很可能需要在后面再添加一
个“partitions num”子句，其中num 是一个非负的整数，它表示表将要被分割成分区的数量。

create table employees (
id int not null,
fname varchar(30),
lname varchar(30),
hired date not null default '1970-01-01',
separated date not null default '9999-12-31',
job_code int,
store_id int
)
partition by hash(year(hired))
partitions 4；
mysql还支持线性哈希功能，它与常规哈希的区别在于，线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则，而常规 哈希使
用的是求哈希函数值的模数。

线性哈希分区和常规哈希分区在语法上的唯一区别在于，在“partition by” 子句中添加“linear”关键字，如下面所示：

create table employees (
id int not null,
fname varchar(30),
lname varchar(30),
hired date not null default '1970-01-01',
separated date not null default '9999-12-31',
job_code int,
store_id int
)
partition by linear hash(year(hired))
partitions 4；
按照线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷，有利于处理含有极其大量（1000g）数据的表。它的缺点在于，与使用常规
hash分区得到的数据分布相比，各个分区间数据的分布不大可能均衡。

key 分区：按照key进行分区类似于按照hash分区，除了hash分区使用的用户定义的表达式，而key分区的 哈希函数是由mysql 服务器
提供。mysql 簇（cluster）使用函数md5()来实现key分区

对于使用其他存储引擎的表，服务器使用其自己内部的 哈希函数，这些函数是基于与password()一样的运算法则

通过线性key分割一个表也是可能的。下面是一个简单的例子：

create table tk (
col1 int not null,
col2 char(5),
col3 date
)
partition by linear key (col1)
partitions 3;
子分区:子分区是分区表中每个分区的再次分割

create table ts (id int, purchased date)
partition by range(year(purchased))
subpartition by hash(to_days(purchased))
(
partition p0 values less than (1990)
(
subpartition s0,
subpartition s1
),
partition p1 values less than (2000)
(
subpartition s2,
subpartition s3
),
partition p2 values less than maxvalue
(
subpartition s4,
subpartition s5
)
);
子分区可以用于特别大的表，在多个磁盘间分配数据和索引。假设有6个磁盘，分别为/disk0， /disk1， /disk2等。现在考虑下面的例
子：

create table ts (id int, purchased date)
partition by range(year(purchased))
subpartition by hash(to_days(purchased))
(
partition p0 values less than (1990)
(
subpartition s0
data directory = '/disk0/data'
index directory = '/disk0/idx',
subpartition s1
data directory = '/disk1/data'
index directory = '/disk1/idx'
),
partition p1 values less than (2000)
(
subpartition s0
data directory = '/disk2/data'
index directory = '/disk2/idx',
subpartition s1
data directory = '/disk3/data'
index directory = '/disk3/idx'
),
partition p2 values less than maxvalue
(
subpartition s0
data directory = '/disk4/data'
index directory = '/disk4/idx',
subpartition s1
data directory = '/disk5/data'
index directory = '/disk5/idx'
)
)
分区管理

从一个按照range或list分区的表中删除一个分区，可以使用带一个drop partition子句的alter table命令来实现

alter table tr drop partition p2;
由“alter table … drop partition”语句引起的、从表中删除的行数并没有被服务器报告出来

如果希望改变表的分区而又不丢失数据，使用“alter table … reorganize partition”语句

一个reorganize partition语句也可以用来合并相邻的分区。可以使用如下的语句恢复成员表到它以前的分区：

alter table members reorganize partition s0,s1 into (
partition p0 values less than (1970)
);
使用“reorganize partition”拆分或合并分区，没有数据丢失。在执行上面的语句中，mysql 把保存在分区s0和s1中的所有数
据都移到分区p0中。

“reorganize partition”的基本语法是：

alter table tbl_name reorganize partition partition_list into
(partition_definitions)；
不能使用与从按照range或list分区的表中删除分区相同的方式来从hash或key分区的表中删除分区。但是，可以使用“alter
table … coalesce partition”命令来合并hash或key分区。

coalesce不能用来增加分区的数量,要增加顾客表的分区数量从12到18，使用“alter table … add partition”,具
体如下：

alter table clients add partition partitions 6;