MySql分区学习总结二：RANGE分区，LIT分区

浅浅的花香味﹌ 2022-04-13 09:50 459阅读 0赞

**MySql分区学习总结二：RANGE,LIST**

**RANGE分区介绍**

在第一节中已经简单的提到过，按照RANGE分区的表示利用取值范围将数据分成区，区间是连续且不能相互叠加的。这个比较好理解，比如我按学号分区，学号在1-99的分在一个区内，在100-199的分在另一个区内，这样依次类推。分区的数值必须是连续的，并且不能有相互的重合。

**RANGE使用**  
接下来介绍RANGE分区的使用  
话不多说，直接上操作语句

CREATE TABLE A(
     ID  INT NOT NULL,
     NAME VARCHAR(30),
     JOB VARCHAR(30),
     TIME DATE NOT NULL DEFAULT '1998-01-01'
     )
     PARTITION BY RANGE(ID)(
     PARTITION P0 VALUES LESS THAN(10),
     PARTITION P1 VALUES LESS THAN(20),
     PARTITION P2 VALUES LESS THAN(30)
     );

以上就是建立一个简单包含RANGE分区的表，其中以ID键为分区键，建立了3个分区，分别用来存储id在不同区间的记录。当插入数据时，系统将按照输入的ID自动把数据存入相应的分区中。注意分区存放的数据范围，P0分区存放的范围为1-9，P1分区存放的范围为10-19。

当插入的数据大于或等于30时，数据库就会因为找不到分区而报错。这时只需再加入一个分区P3，并把分区的值设为MAXVALUE就可以解决这个问题。当插入的数据大于或等于30时，所插入的数据就会自动保存在P3分区中。加入P3分区的语句如下

ALTER TABLE A ADD PARTITION (PARTITION P3 VALUES LESS THAN MAXVALUE);

RANGE分去还支持在语句中使用表达式，最常见的就是按照年进行分区的划分。具体语句如下

CREATE TABLE B(
     ID  INT NOT NULL,
     NAME VARCHAR(30),
     JOB VARCHAR(30),
     TIME DATE NOT NULL DEFAULT '1998-01-01'
     )
     PARTITION BY RANGE(YEAR(TIME))(
     PARTITION P0 VALUES LESS THAN(1999),
     PARTITION P1 VALUES LESS THAN(2000),
     PARTITION P2 VALUES LESS THAN(2001)
     );

以上就是借助表达式将时间进行分区的例子，系统会根据插入时输入的时间自定讲记录存储到不同的分区中。出了年单位以外，mysql5.5还支持TO\_DAYS的按日分区和TO\_SECONDS的按秒分区。

在mysql5.1以前，如果不使用函数转换，就无法利用分区的特性提高查询的性能，但在mysql5.5以后改进了RANGE的分区，提供了支持非整数分区的RANGE COLUMNS,这样以来创建分区时就不需要进行表达式的转换了。关于COLUMNS在后面还有更详细的介绍，在这里先提一下。

**LIST分区介绍**

LIST分区和RANGE分区比较相似，不过LIST是以一个集合的形式建立分区的。使用LIST分区建立表的操作如下：

create table a(
        -> time date not null default '1998-04-08',
        -> category int(10) not null)
        -> partition by list(category)(
        -> partition p0 values in (3,5),
        -> partition p1 values in (1,10),
        -> partition p2 values in (4,9));

上面这段操作建立了一个表a，有time和category两个字段。以category字段为分区键进行分区。p0分区集合包含了3,5两个值，当插入的category的值为3或5时，所插入的记录就会存储到p0分区中。同理，当插入的值为1或10时就会存储到p1分区中。LIST的分区不用按照特定的顺序进行建立，只需要创建相应的集合。具体的存储情况如下图展示。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01hc3Rlcl9LaW4_size_16_color_FFFFFF_t_70]  
从上图我们可以看到，查询category为3时搜索的是p0分区。

**Columns分区**

Columns分区是mysql5.5以后加入的，用来解决mysql5.1以前的RANGE和LIST分区只支持整数分区，造成的需要进行函数换算得到的整数值。Columns分区包括RANGE Columns和LIST Columns。同时支持整数，时间，字符串三大类型。  
利用Columns分区，我们上边RANGE的按时间进行分区的表就可以不添加时间转换函数。具体操作如下：

CREATE TABLE B(
     ID  INT NOT NULL,
     NAME VARCHAR(30),
     JOB VARCHAR(30),
     TIME DATE NOT NULL DEFAULT '1998-01-01'
     )
     PARTITION BY RANGE COLUMNS(TIME)(
     PARTITION P0 VALUES LESS THAN('1999'),
     PARTITION P1 VALUES LESS THAN('2000'),
     PARTITION P2 VALUES LESS THAN('2001')
     );

同时，LIST分区中也可以将集合中的元素换为字符串类型。

create table a(
        -> time date not null default '1998-04-08',
        -> category varchar (20) not null)
        -> partition by list columns (category)(
        -> partition p0 values in ('english','japanese'),
        -> partition p1 values in ('math'),
        -> partition p2 values in ('politics')
        );

**分区管理**

删除分区：

ALTER TABLE A DROP PARTITION P2;

以上语句从表A中删除了P2分区，删除分区后，分区内的数据也会被删除。在RANGE分区中，再次加入原属于分区P2的数据时，会插入到P3分区内。但在LIST分区中，由于删除的是一个指定的集合，所以再次插入属于此集合的数据时会因找不到分区而出错。

加入分区：

ALTER TABLE A ADD PARTITION (PARTITION P3 VALUES LESS THAN (40));

以上语句在A中添加了p3分区，数值为小于40。在RANGE分区中，只能在分区列表的最大一端添加分区。但在LIST中可以添加在任意地方，不过新建的分区不能包括已有分区包含的元素

重定义分区：

重定义分区主要用来在不改变数据的情况下对分区的结构进行重新规划，重定义分区包括合并分区和拆分分区。

拆分分区：

ALTER  TABLE A REORGANIZE PARTITION P3 INTO (
       PARTITION P2 VALUES LESS THAN (25),
       PARTITION P2 VALUES LESS THAN (30)
       );

上面的语句对于LIST和RANGE分区都适用，LIST在适用时需要将LESS THAN 换为 INs。将P3分区拆分为P2,P3分区，同时改变了分区的存储值范围。

合并分区：

ALTER TABLE  A REGORANIZE PARTITION P1,P2,P3 INTO(
    PARTITION P1 VALUES LESS THAN (30)
    );

合并P1,P2,P3分区为P1分区，并重新定义值的范围。

在对分区进行重定义时，只能定义相邻的分区，不能跨分区进行操作，也不能用重定义分区修改分区的类型。

以上就是RANGE和LIST分区的简单介绍，后面会接着介绍HASH分区和KEY分区。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01hc3Rlcl9LaW4_size_16_color_FFFFFF_t_70]: /images/20220413/e82dd37372a24f918f6f9e63c80476e1.png