首页 > 代码库 > Mysql表分区

Mysql表分区

参考地址:http://dev.mysql.com/doc/refman/5.6/en/partitioning.html

什么时候使用分区

  1. 海量数据
  2. 数据表索引大于服务器有效内存

 

分区的限制

  1. 大部分只能对数据表的整型列进行分区,或者数据列可以通过分区函数转化成整型列(其中COLUMNS支持 Integer / String / DATE / DATETIME类型)
  2. 最大分区数目不能超过1024
  3. 如果含有唯一索引或者主键,则分区列必须包含在所有的唯一索引或者主键内
  4. 不支持外键
  5. 不支持全文索引(fulltext)
  6. 分区支持函数限制

ABS()

CEILING()

DAY()

DAYOFMONTH()

DAYOFWEEK()

DAYOFYEAR()

DATEDIFF()

EXTRACT() 

FLOOR()

HOUR()

MICROSECOND()

MINUTE()

MOD()

MONTH()

QUARTER()

SECOND()

TIME_TO_SEC()

TO_DAYS()

TO_SECONDS()

UNIX_TIMESTAMP()

WEEKDAY()

YEAR()

 

YEARWEEK()

 

 

 

 

 

 

 

 

 

MySQL支持RANGE,LIST,HASH,KEY分区类型,其中以RANGE最为常用:

  1. Range(范围)–这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区。
  2. Hash(哈希)–这中模式允许通过对表的一个或多个列的Hash Key进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。
  3. Key(键值)-上面Hash模式的一种延伸,这里的Hash Key是MySQL系统产生的。
  4. List(预定义列表)–这种模式允许系统通过预定义的列表的值来对数据进行分割。
  5. Composite(复合模式) –以上模式的组合使用 

 

分区SQL

过滤分区表的WHERE条件必须是切分分区表的列

range分区

ALTER TABLE employees 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 (21)
);

list分区

ALTER TABLE employees 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)
);

hash分区

hash分区的目的是将数据均匀的分布到预先定义的各个分区中,保证各分区的数据量大致一致。

ALTER TABLE employees PARTITION BY HASH(store_id)
PARTITIONS 4;

hash的分区函数页需要返回一个整数值。partitions子句中的值是一个非负整数,不加的partitions子句的话,默认为分区数为1。

key分区

key分区和hash分区相似,不同在于hash分区是用户自定义函数进行分区,key分区使用mysql数据库提供的函数进行分区,NDB cluster使用MD5函数来分区,对于其他存储引擎mysql使用内部的hash函数,这些函数基于password()一样的算法。

ALTER TABLE tm1  PARTITION BY KEY(s1)
PARTITIONS 10;

columns分区

上面的RANGE、LIST、HASH、KEY四种分区中,分区的条件必须是整形,如果不是整形需要通过函数将其转换为整形。

mysql-5.5开始支持COLUMNS分区,可视为RANGE和LIST分区的进化,COLUMNS分区可以直接使用非整形数据进行分区。COLUMNS分区支持以下数据类型:

所有整形,如INT SMALLINT TINYINT BIGINT。FLOAT和DECIMAL则不支持。

日期类型,如DATE和DATETIME。其余日期类型不支持。

字符串类型,如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT类型不支持。

COLUMNS可以使用多个列进行分区。

 

子分区Subpartitioning

ALTER TABLE ts  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
);
 
 

管理分区

增加分区
ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));
删除分区
ALTER TABLE tr DROP PARTITION p2;
合并分区
ALTER TABLE members
    REORGANIZE PARTITION p0 INTO (
        PARTITION n0 VALUES LESS THAN (1960),
        PARTITION n1 VALUES LESS THAN (1970)
);

Mysql表分区