MYSQL5.1表分区初探.doc

MySQL5.1分区功能初探 编写人：胡家惠 日期：2008-3-141.1.1.1.1. MySQL分区的概念分区就是根据某种规则把一个大表划分为多个物理存放的部分(也叫子分区)，从而提高表的查询速度和方便管理的一种方法。MySQL5.1开始全面支持表分区，但是现阶段MySQL官方还没有推荐把MySQL5.1应用到生产环境，应该过不了多久，就会推出正式版的MySQL5.1。可以对MySQL所支持的所有存储引擎表进行分区，但是一个分区表中只能选择一种存储引擎。测试你的MySQL是否支持分区方法：mysql SHOW VARIABLES LIKE %partition%;+-+-+| 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家音像店的职员记录，这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 ( 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) 按照这种分区方案，在商店1到5工作的雇员相对应的所有行被保存在分区P0中，商店6到10的雇员保存在P1中，商店11到15的雇员保存在P2中，商店号大于或等于16而小于MAXVALUE的雇员记录将保存在p3中，这里MAXVALUE 表示最大的可能的整数值。也可以基于每个雇员进入公司的年份（YEAR(hired)）来分割表，比如：CREATE TABLE employees_range1 ( 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 RANGE (YEAR(hired) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1996), PARTITION p2 VALUES LESS THAN (2001), PARTITION 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分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值，而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, 11, 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_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”查询将失败并报错。例如，假定LIST分区的采用上面的方案，下面的插入语句将失败：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 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。下面的语句创建了一个使用基于“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)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 INT, store_id INT)PARTITION BY HASH(YEAR(hired)PARTITIONS 41.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 31.1.1.1.3.5. 子分区子分区是对分区表中每个分区进行再次分割。如果子分区采用不同于父分区的分区类型，也称作混合分区。比如可以对已经通过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 THAN (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(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 ) )可以通过分区字句（DATA DIRECTORY 、INDEX DIRECTORY）把子分区的数据和索引分布到各个磁盘上，比如： 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 = /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 ) )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(purchased) ( 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 新增表分区语句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 DEFAULT 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年前成员的所有行移入到一个分开的分区中，可以使用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中包含那些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 IN (5, 10,