当前位置：首页 > Mysql教程 > 列表

mysql partition 分区功能使用详解

发布:smiling　来源: PHP粉丝网 　添加日期：2014-09-29 17:20:26　浏览: 评论：0　

= 最终结论 =

* 对于大数据量,建议使用分区功能.

* 去除不必要的字段.

* 根据手册,增加myisam_max_sort_file_size 会增加分区性能.

[分区命令详解],代码如下:

  1. = 分区例子 =  
  2. * RANGE 类型 
  3.  
  4. CREATE TABLE users ( 
  5.        uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  6.        name VARCHAR(30) NOT NULL DEFAULT '', 
  7.        email VARCHAR(30) NOT NULL DEFAULT '' 
  8. ) 
  9. PARTITION BY RANGE (uid) ( 
  10.        PARTITION p0 VALUES LESS THAN (3000000) 
  11.        DATA DIRECTORY = '/data0/data' 
  12.        INDEX DIRECTORY = '/data1/idx', 
  13.  
  14.        PARTITION p1 VALUES LESS THAN (6000000) 
  15.        DATA DIRECTORY = '/data2/data' 
  16.        INDEX DIRECTORY = '/data3/idx', 
  17.  
  18.        PARTITION p2 VALUES LESS THAN (9000000) 
  19.        DATA DIRECTORY = '/data4/data' 
  20.        INDEX DIRECTORY = '/data5/idx', 
  21.  
  22.        PARTITION p3 VALUES LESS THAN MAXVALUE     DATA DIRECTORY = '/data6/data'  
  23.        INDEX DIRECTORY = '/data7/idx' 
  24. ); 

在这里,将用户表分成4个分区,以每300万条记录为界限,每个分区都有自己独立的数据、索引文件的存放目录,与此同时,这些目录所在的物理磁盘分区可能也都是完全独立的,可以提高磁盘IO吞吐量.

LIST 类型,代码如下:

  1. CREATE TABLE category ( 
  2.      cid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  3.      name VARCHAR(30) NOT NULL DEFAULT '' 
  4. ) 
  5. PARTITION BY LIST (cid) ( 
  6.      PARTITION p0 VALUES IN (0,4,8,12) 
  7.      DATA DIRECTORY = '/data0/data'  
  8.      INDEX DIRECTORY = '/data1/idx', 
  9.       
  10.      PARTITION p1 VALUES IN (1,5,9,13) 
  11.      DATA DIRECTORY = '/data2/data' 
  12.      INDEX DIRECTORY = '/data3/idx', 
  13.       
  14.      PARTITION p2 VALUES IN (2,6,10,14) 
  15.      DATA DIRECTORY = '/data4/data' 
  16.      INDEX DIRECTORY = '/data5/idx', 
  17.       
  18.      PARTITION p3 VALUES IN (3,7,11,15) 
  19.      DATA DIRECTORY = '/data6/data' 
  20.      INDEX DIRECTORY = '/data7/idx' 
  21. ); 

分成4个区,数据文件和索引文件单独存放.

HASH 类型,代码如下:

  1. CREATE TABLE users ( 
  2.      uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  3.      name VARCHAR(30) NOT NULL DEFAULT '', 
  4.      email VARCHAR(30) NOT NULL DEFAULT '' 
  5. ) 
  6. PARTITION BY HASH (uid) PARTITIONS 4 ( 
  7.      PARTITION p0 
  8.      DATA DIRECTORY = '/data0/data' 
  9.      INDEX DIRECTORY = '/data1/idx', 
  10.      --phpfensi.com 
  11.      PARTITION p1 
  12.      DATA DIRECTORY = '/data2/data' 
  13.      INDEX DIRECTORY = '/data3/idx', 
  14.  
  15.      PARTITION p2 
  16.      DATA DIRECTORY = '/data4/data' 
  17.      INDEX DIRECTORY = '/data5/idx', 
  18.  
  19.      PARTITION p3 
  20.      DATA DIRECTORY = '/data6/data' 
  21.      INDEX DIRECTORY = '/data7/idx' 
  22. ); 

分成4个区,数据文件和索引文件单独存放,例子,代码如下:

  1. CREATE TABLE ti2 (id INT, amount DECIMAL(7,2), tr_date DATE) 
  2.     ENGINE=myisam 
  3.     PARTITION BY HASH( MONTH(tr_date) ) 
  4.     PARTITIONS 6; 
  5.  
  6. CREATE PROCEDURE load_ti2() 
  7.        begin 
  8.     declare v int default 0; 
  9.     while v < 80000 
  10.     do 
  11.         insert into ti2 
  12.         values (v,'3.14',adddate('1995-01-01',(rand(v)*3652) mod 365)); 
  13.          set v = v + 1; 
  14.     end while; 
  15.     end 
  16.     // 

KEY 类型,代码如下:

  1. CREATE TABLE users ( 
  2.      uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  3.      name VARCHAR(30) NOT NULL DEFAULT '', 
  4.      email VARCHAR(30) NOT NULL DEFAULT '' 
  5. ) 
  6. PARTITION BY KEY (uid) PARTITIONS 4 ( 
  7.      PARTITION p0 
  8.      DATA DIRECTORY = '/data0/data' 
  9.      INDEX DIRECTORY = '/data1/idx', 
  10.       
  11.      PARTITION p1 
  12.      DATA DIRECTORY = '/data2/data'  
  13.      INDEX DIRECTORY = '/data3/idx', 
  14.       
  15.      PARTITION p2  
  16.      DATA DIRECTORY = '/data4/data' 
  17.      INDEX DIRECTORY = '/data5/idx', 
  18.       
  19.      PARTITION p3  
  20.      DATA DIRECTORY = '/data6/data' 
  21.      INDEX DIRECTORY = '/data7/idx' 
  22. ); 

分成4个区,数据文件和索引文件单独存放.

子分区:子分区是针对 RANGE/LIST 类型的分区表中每个分区的再次分割,再次分割可以是 HASH/KEY 等类型,例如,代码如下:

  1. CREATE TABLE users ( 
  2.      uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  3.      name VARCHAR(30) NOT NULL DEFAULT '', 
  4.      email VARCHAR(30) NOT NULL DEFAULT '' 
  5. ) 
  6. PARTITION BY RANGE (uid) SUBPARTITION BY HASH (uid % 4) SUBPARTITIONS 2( 
  7.      PARTITION p0 VALUES LESS THAN (3000000) 
  8.      DATA DIRECTORY = '/data0/data' 
  9.      INDEX DIRECTORY = '/data1/idx', 
  10.  
  11.      PARTITION p1 VALUES LESS THAN (6000000) 
  12.      DATA DIRECTORY = '/data2/data' 
  13.      INDEX DIRECTORY = '/data3/idx' 
  14. ); 

对 RANGE 分区再次进行子分区划分,子分区采用 HASH 类型,或者如下代码:

  1. CREATE TABLE users ( 
  2.      uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  3.      name VARCHAR(30) NOT NULL DEFAULT '', 
  4.      email VARCHAR(30) NOT NULL DEFAULT '' 
  5. ) 
  6. PARTITION BY RANGE (uid) SUBPARTITION BY KEY(uid) SUBPARTITIONS 2( 
  7.      PARTITION p0 VALUES LESS THAN (3000000) 
  8.      DATA DIRECTORY = '/data0/data' 
  9.      INDEX DIRECTORY = '/data1/idx', 
  10.  
  11.      PARTITION p1 VALUES LESS THAN (6000000) 
  12.      DATA DIRECTORY = '/data2/data' 
  13.      INDEX DIRECTORY = '/data3/idx' 
  14. ); 

对 RANGE 分区再次进行子分区划分,子分区采用 KEY 类型.

分区管理:

删除分区:ALERT TABLE users DROP PARTITION p0;,删除分区 p0。

重建分区,o RANGE 分区重建:ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));将原来的 p0,p1 分区合并起来,放到新的 p0 分区中.

o LIST 分区重建:ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));

将原来的 p0,p1 分区合并起来,放到新的 p0 分区中,o HASH/KEY 分区重建,ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2;

用 REORGANIZE 方式重建分区的数量变成2，在这里数量只能减少不能增加。想要增加可以用 ADD PARTITION 方法。

新增分区,o 新增 RANGE 分区:ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19)

DATA DIRECTORY = '/data8/data'

INDEX DIRECTORY = '/data9/idx');

新增一个RANGE分区,o 新增 HASH/KEY 分区:ALTER TABLE users ADD PARTITION PARTITIONS 8;将分区总数扩展到8个.

给已有的表加上分区,代码如下:

  1. alter table results partition by RANGE (month(ttime))  
  2. (PARTITION p0 VALUES LESS THAN (1), 
  3. PARTITION p1 VALUES LESS THAN (2) , PARTITION p2 VALUES LESS THAN (3) , 
  4. PARTITION p3 VALUES LESS THAN (4) , PARTITION p4 VALUES LESS THAN (5) , 
  5. PARTITION p5 VALUES LESS THAN (6) , PARTITION p6 VALUES LESS THAN (7) , 
  6. PARTITION p7 VALUES LESS THAN (8) , PARTITION p8 VALUES LESS THAN (9) , 
  7. PARTITION p9 VALUES LESS THAN (10) , PARTITION p10 VALUES LESS THAN (11), 
  8. PARTITION p11 VALUES LESS THAN (12), 
  9. PARTITION P12 VALUES LESS THAN (13) ); 

默认分区限制分区字段必须是主键,PRIMARY KEY)的一部分,为了去除此限制.

方法1,使用ID,代码如下:

  1. mysql> ALTER TABLE np_pk 
  2.     ->     PARTITION BY HASH( TO_DAYS(added) ) 
  3.     ->     PARTITIONS 4; 
  4. ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function 
  5.  
  6. However, this statement using the id column for the partitioning column is valid, as shown here: 
  7.  
  8. mysql> ALTER TABLE np_pk 
  9.     ->     PARTITION BY HASH(id) 
  10.     ->     PARTITIONS 4; 
  11. Query OK, 0 rows affected (0.11 sec) 
  12. Records: 0 Duplicates: 0 Warnings: 0 

方法2,将原有PK去掉生成新PK,代码如下:

  1. mysql> alter table results drop PRIMARY KEY; 
  2. Query OK, 5374850 rows affected (7 min 4.05 sec) 
  3. Records: 5374850 Duplicates: 0 Warnings: 0 
  4.  
  5. mysql> alter table results add PRIMARY KEY(id, ttime); 
  6. Query OK, 5374850 rows affected (6 min 14.86 sec) 
  7. Records: 5374850 Duplicates: 0 Warnings: 0

上一页 1 2 下一页

Tags: partition mysql分区功能

分享到：

相关文章

推荐文章

热门文章

最新评论文章