Mysql按照范围区间创建分区表

威赞

于 2024-07-31 16:59:43 发布

阅读量544

点赞数 4

分类专栏： mysql 文章标签： mysql android 数据库

本文链接：https://blog.csdn.net/wilsonzane/article/details/140827377

版权

mysql 专栏收录该内容

2 篇文章

订阅专栏

定义

每一个分区仅包含在指定范围内的数据列。这样的分区方式就是范围分区。在Mysql的范围分区表定义中，分区范围需要连续并且不会有覆盖。定义范围分区表时，使用VALUES LESS THAN操作符。在PARTITION BY RANGE语法中，建立分区表指定分区时，每一个分区都是按顺序定义。使用时类似C语言和java中的if...elseif...表达式。

应用

本文的几个举例，都假设用户为一家具有20个门店的音响公司创建员工记录表。这20家门店的编号store_id是1到20.

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列进行分区

使用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
) 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)
);

在这个新建的分区表中，门店1-5的员工数据，保存在第一个分区p0中；门店6-10的员工数据，保存在第二个分区p1中....

按照上面的分区表定义，Mysql很容易将数据(72, 'Mitchell', 'Wilson', '1998-06-25', DEFAULT, 7, 13) 插入第三个分区p2当中。但这家公司开了一家新的门店，第21个门店时，会出现什么样的问题。在这个分区表的定义中，并未指定门店编码超过20的处理方式，因此插入第21个门店的员工数据时，Mysql会报错。Mysql给出来一个数值MAXVALUE来解决这个问题。MAXVALUE是Mysql中最大的一个数值，所有数字都会比MAXVALUE小。则构建该分区表的语句变成下面的形式

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
) 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
);

现在，所有门店编号大于等于16的数据，都会插入到第四个分区p3当中。而当后续门店增加时，数据库管理员和开发人员，可以为表建立新的分区。如为编号21-25门店添加新的分区，为26-30的门店添加另一个分区。

使用员工编号分区

使用员工编号分区的方式，是一种更加时髦的分区方法。在上面的例子中，数据库管理员和开发人员可以使用字段job_code进行分区。如，两位编码用于保存店长的信息。三位编码分配给办公室和后勤人员。而为销售经理分配四位编码。则可以按照下面的方法来创建分区表

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
) partition by range (job_code) (
    partition p0 values less than (100),
    partition p1 values less than (1000),
    partition p2 values less than (10000)
);

使用日期时间字段建立分区

除了使用门店编号，和员工代码分区外，还可以使用时间列来建立分区表。假设现在需要使用员工的离职年份建立分区。即使用YEAR(seperated)表达式返回的值来确定分区范围。则使用下面的语句建立分区表

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
) partition by range (YEAR(separated)) (
    partition p0 values less than (1991),
    partition p1 values less than (1996),
    partition p2 values less than (2001),
    partition p4 values less than MAXVALUE
);

在上面的分区表中，1991年前离职的员工在分区p0中，而在1991-1995年离职的员工，在分区p1中...

当然，也可以基于TIMESTAMP字段，按照时间范围来建立分区表，使用UNIX_TIMESTAMP()方法，建立分区表如下。

CREATE TABLE quarterly_report_status (
  report_id INT NOT NULL,
  report_status VARCHAR(20) NOT NULL,
  report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
  PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
  PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
  PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
  PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
  PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
  PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
  PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
  PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
  PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
  PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

使用时间段分区使用下面的场景

当开发人员或数据库管理员想要删除旧数据的时候，如前面的employee表，用户可以简单的使用ALTER TABLE employees DROP PARTITION p0,来删除1991年前离职员工的所有数据。当有很多数据需要删除时，使用这种删除方法比使用DELETE方法删除数据效率会高很多。
用户想要使用时间或其他序列的字段来操作数据表
用户经常使用表分区字段来进行查询。如当执行下面这条语句.Mysql能够快速的使用where查询条件找到第三个分区p2, 而不需要扫描其他分区的数据。

EXPLAIN SELECT COUNT(*) FROM employees WHERE seperated BETWEEN '2000-01-01' AND '2000-12-31' GROUP BY store_id

范围分区列

与范围分区相似的用法就是分区列。使用RANGE COLUMNS方法构建分区表，允许用户使用多个列来建立分区表。这些列可以共同用于查询时对扫描数据的修剪和过滤。

使用范围分区列来创建分区表。

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(joined) (
    PARTITION p0 VALUES LESS THAN ('1960-01-01'),
    PARTITION p1 VALUES LESS THAN ('1970-01-01'),
    PARTITION p2 VALUES LESS THAN ('1980-01-01'),
    PARTITION p3 VALUES LESS THAN ('1990-01-01'),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);