Mysql分区表使用

Mysql分区表使用

一、什么是分区表

分区表就是按一定规则将一张表分割成多个部分，达到和物理分表同样的效果，但操作起来更 简单，不同于物理分表那样使用时还需要指定使用的数据表。对于使用者来说和操作普通表无差别。

二、优势与限制

1、优势

• 分区可以在一个表中存储比单个磁盘或文件系统分区上的数据更多的数据，因为我们可 以将分区表存储在不同物理磁盘上;
• 对于大表数据，可以快速删除指定分区上的数据，效率比普通表delete高很多;
• 查询的时候，如果where子句中包含分区列，则会减少IO，提升查询效率;
• 对于大表尤其是innodb引擎的表，进行count()或sum()查询时，可以分区并行查询，提高效率;

2、限制

• 如果分区字段中有主键或者唯一索引列，那么所有主键列和唯一索引列都必须包含进 来，如果表中有主键或唯一索引，那么分区键必须是主键或唯一索引;
• 分区函数限制，目前mysql仅支持以下分区函数:ABS()、CEILING()、DATEDIFF()、 DAY()、DAYOFMONTH()、DAYOFWEEK()、DAYOFYEAR()、EXTRACT()、FLOOR()、 HOUR()、MICROSECOND()、MINUTE()、MOD()、MONTH()、QUARTER()、SECOND()、 TIME_TO_SEC()、TO_DAYS()、TO_SECONDS()、UNIX_TIMESTAMP()、WEEKDAY()、YEAR()、 YEARWEEK();
• 所有分区上的数据库引擎要一致;
• 一张表可以设置8192个分区。

三、分区类型

1、RANGE分区

基于一个给定连续区间范围，把数据分配到不同的分区;

CREATE TABLE r1 ( 
    a INT,
    b INT 
)
PARTITION BY RANGE (a) (
PARTITION p0 VALUES LESS THAN (5), 
PARTITION p1 VALUES LESS THAN (MAXVALUE)
);

2、LIST分区

类似RANGE分区，区别在LIST分区是基于枚举出的值列表分区，RANGE是基于给定连续区间范围分区;

CREATE TABLE customers_1 ( 
    first_name VARCHAR(25), 
    last_name VARCHAR(25), 
    street_1 VARCHAR(30), 
    street_2 VARCHAR(30), 
    city VARCHAR(15),
    renewal DATE 
)
PARTITION BY LIST COLUMNS(city) (
PARTITION pRegion_1 VALUES IN('xian', 'chengdu', 'chongqing'), 
PARTITION pRegion_2 VALUES IN('shijiazhuang', 'tianjin', 'beijing'), 
PARTITION pRegion_3 VALUES IN('hangzhou', 'nanjing')
);

3、HASH分区

基于用户定义的表达式返回值来选择分区，该表达式对要插入到表的行中列值操作。

PS:表达式必须返回整型数据。

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 )
PARTITION BY HASH(store_id) PARTITIONS 4;

4、KEY分区

类似HASH分区，但是HASH分区允许使用用户自定义表达式，而KEY分区不允许，它需要使用 MySQL服务器提供的HASH函数，同时HASH分区只支持整数分区，而KEY分区支持除BLOB和 TEXT类型外其他列;

PS:KEY分区对列数据进行hash运算所以有时候数据分布会非常不均匀。

CREATE TABLE k1 (
    id INT NOT NULL PRIMARY KEY, 
    name VARCHAR(20)
)
PARTITION BY KEY() PARTITIONS 2;

如果不指定列，mysql会自动使用主键或者唯一健所在的列。