当前位置:首页>

文章详细页

MySQL分区分表

分类:MySQL



========== 水平分区(根据列属性按行分)
举个简单例子:一个包含十年发票记录的表可以被分区为十个不同的分区,每个分区包含的是其中一年的记录。


==== 水平分区的几种模式

- Range(范围) – 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区,80年代(1980's)的数据,90年代(1990's)的数据以及任何在2000年(包括2000年)后的数据。

- Hash(哈希) – 这中模式允许DBA通过对表的一个或多个列的Hash Key进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区,。例如DBA可以建立一个对表主键进行分区的表。

- Key(键值) – 上面Hash模式的一种延伸,这里的Hash Key是MySQL系统产生的。

- List(预定义列表) – 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如:DBA建立了一个横跨三个分区的表,分别根据2004年2005年和2006年值所对应的数据。

- Composite(复合模式) - 很神秘吧,哈哈,其实是以上模式的组合使用而已,就不解释了。举例:在初始化已经进行了Range范围分区的表上,我们可以对其中一个分区再进行hash哈希分区。


========== 垂直分区(按列分)
举个简单例子:一个包含了大text和BLOB列的表,这些text和BLOB列又不经常被访问,这时候就要把这些不经常使用的text和BLOB了划分到另一个分区,在保证它们数据相关性的同时还能提高访问速度。


========== 结论

    对于大数据量,建议使用分区功能。
    去除不必要的字段
    根据手册, 增加myisam_max_sort_file_size 会增加分区性能



========== 分区实例

==== RANGE 类型

    CREATE TABLE users (  
           uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  
           name VARCHAR(30) NOT NULL DEFAULT '',  
           email VARCHAR(30) NOT NULL DEFAULT ''  
    )  
    PARTITION BY RANGE (uid) (  
           PARTITION p0 VALUES LESS THAN (3000000)  
           DATA DIRECTORY = '/data0/data'  
           INDEX DIRECTORY = '/data1/idx',  
      
           PARTITION p1 VALUES LESS THAN (6000000)  
           DATA DIRECTORY = '/data2/data'  
           INDEX DIRECTORY = '/data3/idx',  
      
           PARTITION p2 VALUES LESS THAN (9000000)  
           DATA DIRECTORY = '/data4/data'  
           INDEX DIRECTORY = '/data5/idx',  
      
           PARTITION p3 VALUES LESS THAN MAXVALUE     DATA DIRECTORY = '/data6/data'   
           INDEX DIRECTORY = '/data7/idx'  
    );  

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


==== LIST 类型

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

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


==== HASH 类型   

    CREATE TABLE users (  
         uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  
         name VARCHAR(30) NOT NULL DEFAULT '',  
         email VARCHAR(30) NOT NULL DEFAULT ''  
    )  
    PARTITION BY HASH (uid) PARTITIONS 4 (  
         PARTITION p0  
         DATA DIRECTORY = '/data0/data'  
         INDEX DIRECTORY = '/data1/idx',  
      
         PARTITION p1  
         DATA DIRECTORY = '/data2/data'  
         INDEX DIRECTORY = '/data3/idx',  
      
         PARTITION p2  
         DATA DIRECTORY = '/data4/data'  
         INDEX DIRECTORY = '/data5/idx',  
      
         PARTITION p3  
         DATA DIRECTORY = '/data6/data'  
         INDEX DIRECTORY = '/data7/idx'  
    );  

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


==== KEY 类型

    CREATE TABLE users (  
         uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  
         name VARCHAR(30) NOT NULL DEFAULT '',  
         email VARCHAR(30) NOT NULL DEFAULT ''  
    )  
    PARTITION BY KEY (uid) PARTITIONS 4 (  
         PARTITION p0  
         DATA DIRECTORY = '/data0/data'  
         INDEX DIRECTORY = '/data1/idx',  
           
         PARTITION p1  
         DATA DIRECTORY = '/data2/data'   
         INDEX DIRECTORY = '/data3/idx',  
           
         PARTITION p2   
         DATA DIRECTORY = '/data4/data'  
         INDEX DIRECTORY = '/data5/idx',  
           
         PARTITION p3   
         DATA DIRECTORY = '/data6/data'  
         INDEX DIRECTORY = '/data7/idx'  
    );     

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


==== 子分区

    CREATE TABLE users (  
         uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  
         name VARCHAR(30) NOT NULL DEFAULT '',  
         email VARCHAR(30) NOT NULL DEFAULT ''  
    )  
    PARTITION BY RANGE (uid) SUBPARTITION BY HASH (uid % 4) SUBPARTITIONS 2(  
         PARTITION p0 VALUES LESS THAN (3000000)  
         DATA DIRECTORY = '/data0/data'  
         INDEX DIRECTORY = '/data1/idx',  
      
         PARTITION p1 VALUES LESS THAN (6000000)  
         DATA DIRECTORY = '/data2/data'  
         INDEX DIRECTORY = '/data3/idx'  
    );  



========== 分区管理

==== 删除分区
    ALERT TABLE users DROP PARTITION p0;  

==== 重建分区

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

- LIST 分区重建
    ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));  
    将原来的 p0,p1 分区合并起来,放到新的 p0 分区中。

- HASH/KEY 分区重建
    ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2;  
    用 REORGANIZE 方式重建分区的数量变成2,在这里数量只能减少不能增加。想要增加可以用 ADD PARTITION 方法。


==== 新增分区

- 新增 RANGE 分区   
    ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19)  
               DATA DIRECTORY = '/data8/data'  
               INDEX DIRECTORY = '/data9/idx');  
    新增一个RANGE分区。

- 新增 HASH/KEY 分区
    ALTER TABLE users ADD PARTITION PARTITIONS 8;  
    将分区总数扩展到8个。




==== 给已有的表加上分区

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

==== 默认分区限制分区字段必须是主键(PRIMARY KEY)的一部分,为了去除此限制:
[方法1] 使用ID

    mysql> ALTER TABLE np_pk  
        ->     PARTITION BY HASH( TO_DAYS(added) )  
        ->     PARTITIONS 4;  

    mysql> ALTER TABLE np_pk  
        ->     PARTITION BY HASH(id)  
        ->     PARTITIONS 4;  


[方法2] 将原有PK去掉生成新PK

    mysql> alter table results drop PRIMARY KEY;  
    mysql> alter table results add PRIMARY KEY(id, ttime);  



参考:http://blog.csdn.net/tjcyjd/article/details/11194489