MYSQL5.1表分区初探

1、mysql5.1 分区功能初探编写人：胡家惠日期： 2008-3-14 1.1.1.1.1.mysql 分区的概念分区就是根据某种规则把一个大表划分为多个物理存放的部分(也叫子分区 )，从而提高表的查询速度和方便管理的一种方法。mysql5.1 开始全面支持表分区，但是现阶段mysql官方还没有推荐把mysql5.1应用到生产环境，应该过不了多久，就会推出正式版的mysql5.1 。可以对mysql 所支持的所有存储引擎表进行分区，但是一个分区表中只能选择一种存储引擎。测试你的mysql 是否支持分区方法：mysql show v ariables like %partition%;+-+-+

2、 | variable_name | value | +-+-+ | have_partition_engine | yes | +-+-+ 1 row in set (0.00 sec) 如果看到变量have_partition_engine 的值为 yes，说明你的mysql 支持分区。1.1.1.1.2.分区的优点1、 与单个磁盘或文件系统分区相比，可以存储更多的数据。2、 简化管理。3、 缩小查询范围，提高查询性能4、 提高查询的并行性5、 提高磁盘吞吐量1.1.1.1.3.分区的创建方法1.1.1.1.3.1.range 分区假定你创建了一个如下的一个表，该表保存有20 家音像店的职

3、员记录，这20 家音像店的编号从1 到 20（store_id字段） 。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 ) 可以根据 store_id字段进行范围分区: create table employees_range0

4、( 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

5、 p2 values less than (16), partition p3 values less than maxvalue ) 按照这种分区方案，在商店 1 到 5 工作的雇员相对应的所有行被保存在分区p0中，商店 6 到 10 的雇员保存在p1中，商店11 到 15 的雇员保存在p2 中，商店号大于或等于16而小于 maxvalue 的雇员记录将保存在p3 中，这里maxvalue 表示最大的可能的整数值。也可以基于每个雇员进入公司的年份（year(hired) ）来分割表，比如：create table employees_range1 ( id int not null, fna

6、me 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 range (year(hired) ( partition p0 values less than (1991), partition p1 values less than (1996), partition p2 values less than (2001), part

7、ition p3 values less than maxvalue ) 在这个方案中，在1991 年前雇佣的所有雇员的记录保存在分区p0 中， 1991 年到 1995年期间雇佣的所有雇员的记录保存在分区p1 中， 1996 年到 2000 年期间雇佣的所有雇员的记录保存在分区p2 中， 2000 年后雇佣的所有雇员的信息保存在p3 中。1.1.1.1.3.2.list 分区mysql 中的 list 分区在很多方面类似于range 分区。和按照range 分区一样，每个分区必须明确定义。它们的主要区别在于，list 分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值，而

8、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 ) 假定有 20 个音像店，分布在4 个有经销权的地区，如下表所示：地区商店 id 号北区3, 5, 6, 9, 17 东区1, 2, 10, 1

9、1, 19, 20 西区4, 12, 13, 14, 18 中心区7, 8, 15, 16 要按照属于同一个地区商店的行保存在同一个分区中的方式来分割表，可以使用下面的“create table”语句进行list 分区：create table employees_list ( 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_i

10、d 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 ”查询将失败并报错。例如，假定list 分区的采用上面的方案，下面的插入语句将失

11、败：insert into employees values (224, linus, torvalds, 2002-05-01, 2004-10-12, 42, 21) 这是因为“ store_id ”列值21 不能在用于定义分区pnorth, peast, pwest,或pcentral的值列表中找到。1.1.1.1.3.3.hash 分区hash 分区主要用来确保数据在预先确定数目的分区中平均分布。在 range 和 list分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在 hash分区中， mysql 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列

12、值或表达式，以及指定被分区的表将要被分割成的分区数量。下面的语句创建了一个使用基于“store_id ”列进行哈希处理的表，该表被分成了 4 个分区：create table employees_hash0 ( 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(store_id

13、) partitions 4 如果没有包括一个partitions子句，那么分区的数量将默认为1，如果在关键字“partitions ”后面没有加上分区的数量，将会出现语法错误。也可以基于雇用雇员的年份来进行分区。这可以通过下面的语句来实现：create table employees_hash1 ( 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

14、int, store_id int ) partition by hash(year(hired) partitions 4 1.1.1.1.3.4.key分区按照 key进行分区类似于按照hash 分区，除了hash 分区使用的用户定义的表达式，而 key分区的哈希函数是由mysql 服务器提供。创建 key分区的例子：create table test_key ( col1 int not null, col2 char(5), col3 date ) partition by linear key (col1) partitions 3 1.1.1.1.3.5.子分区子分区是对分区表中每

15、个分区进行再次分割。如果子分区采用不同于父分区的分区类型，也称作混合分区。比如可以对已经通过range 或 list 分区了的表再进行子分区，子分区既可以使用hash 希分区，也可以使用key 分区。create table ts (id int, purchased date) partition by range(year(purchased) subpartition by hash(to_days(purchased) subpartitions 2 ( partition p0 values less than (1990), partition p1 values less tha

16、n (2000), partition p2 values less than maxvalue ) 表 ts 有 3 个 range 分区。这3 个分区中的每一个分区(p0, p1, p2) 又被进一步分成了 2 个子分区。实际上，整个表被分成了3 * 2 = 6个分区。可以使用 subpartition 子句来明确定义子分区的名称和个数，但是每个分区必须有相同数量的子分区：create table ts_1 (id int, purchased date) partition by range(year(purchased) subpartition by hash(to_days(pur

17、chased) ( 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 ) ) 可以通过分区字句（data directory 、index directory）把子分区的数据和索引分布到各个磁盘上，比如

18、：create table ts_2 (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 = /d

19、isk1/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 direc

20、tory = /disk4/idx, subpartition s1 data directory = /disk5/data index directory = /disk5/idx ) ) 1.1.1.1.4.分区的管理分区表建立以后，可以通过命令添加、删除、重新定义、合并或拆分已经存在的分区，进行分区的管理。各种类型分区管理操作命令大同小异。1.1.1.1.4.1.range 和 list 分区的管理对于以下的范围分区表：create table tr (id int, name varchar(50), purchased date) partition by range(year(p

21、urchased) ( partition p0 values less than (1990), partition p1 values less than (1995), partition p2 values less than (2000), partition p3 values less than (2005) ) 删除表分区语句删除 tr 表的 p1 分区：alter table tr drop partition p2; 在删除一个分区之后，该分区的数据也随之被删除。可以用show 命令查看删除分区 p1 之后的表结构：show create table trg 新增表分区语句

22、alter table members add partition (partition p3 values less than (2000); 对于通过range 分区的表， 只可以使用add partition添加新的分区到分区列表的高端不删除数据，修改表分区语句对于以下的range 分区： create table members ( id int(11) default null, fname varchar(25) default null, lname varchar(25) default null, dob date default null ) engine=myisam d

23、efault charset=latin1 partition by range (year(dob) ( partition p0 values less than (1970) engine = myisam, partition p1 values less than (1980) engine = myisam, partition p2 values less than (1990) engine = myisam, partition p3 values less than (2000) engine = myisam ) 假定想要把表示出生在1960 年前成员的所有行移入到一个分

24、开的分区中，可以使用 reorganize partition分区子句： alter table members reorganize partition p0 into ( partition s0 values less than (1960), partition s1 values less than (1970) ) 实际上，这个命令把分区p0 分成了两个新的分区s0 和 s1。同时，它还根据包含在两个“partition . values . ” 子句中的规则，把保存在分区p0 中的数据移入到两个新的分区中，所以分区s0 中只包含year(dob) 小于 1960 的那些行， s1

25、 中包含那些year(dob) 大于或等于1960 但是小于1970 的行。可以用 reorganize partition语句来合并相连的分区：alter table members reorganize partition s0,s1 into ( partition p0 values less than (1970) ) 执行上述语句， mysql 把保存在分区 s0 和 s1 中的所有数据都移到分区p0 中。重新组织成员表（members）的四个分区成两个分区语句：alter table members reorganize partition p0,p1,p2,p3 into ( partition m0 values less than (1980), partition m1 values less than (2000) ) 对于以下的list 分区表： create table tt ( id int, data int ) partition by list(data) ( partition p0 values i