mySql分区操纵记录

最新推荐文章于 2022-09-28 10:57:19 发布

唯心之妙

最新推荐文章于 2022-09-28 10:57:19 发布

阅读量98

点赞数

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

本文链接：https://blog.csdn.net/qq_33441262/article/details/121067472

版权

mySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

mysql分区（四种分区）：

一、RANGE（范围分区）：RANGE COLUMNS分区

根据区间来判断位于哪个分区。

如下例中，如果store_id小于6，则新增或修改的记录会被分配到p0分区，如果大于6小于11，则会被分配到p1分区，以此类推，类似编程语言中的if…elseif…语句

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

注意：

1. RANGE分区的返回值必须为整数。

2. PARTITION p3 VALUES LESS THAN MAXVALUE 是非必需的。

RANGE COLUMNS分区（RANGE分区的一种特殊类型），与RANGE分区的区别如下：

1. RANGE COLUMNS不接受表达式，只能是列名。而RANGE分区则要求分区的对象是整数。
2. RANGE COLUMNS允许多个列，在底层实现上，它比较的是元祖（多个列值组成的列表），而RANGE比较的是标量，即数值的大小。
3. RANGE COLUMNS不限于整数对象，date，datetime，string都可作为分区列。
    CREATE TABLE rcx (
      a INT,
      b INT,
      c CHAR(3),
      d INT
    )
    PARTITION BY RANGE COLUMNS(a,d,c) (
      PARTITION p0 VALUES LESS THAN (5,10,'ggg'),
      PARTITION p1 VALUES LESS THAN (10,20,'mmmm'),
      PARTITION p2 VALUES LESS THAN (15,30,'sss'),
      PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
    );
同RANGE分区类似，它的区间范围必须是递增的，有时候，列涉及的太多，不好判断区间的大小，可采用下面的方式进行判断：
  mysql> SELECT (5,10) < (5,12), (5,11) < (5,12), (5,12) < (5,12);
  +-----------------+-----------------+-----------------+
  | (5,10) < (5,12) | (5,11) < (5,12) | (5,12) < (5,12) |
  +-----------------+-----------------+-----------------+
  | 1 | 1 | 0 |
  +-----------------+-----------------+-----------------+
  1 row in set (0.07 sec)

二、LIST（列表分区）：LIST COLUMNS分区

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

LIST COLUMNS分区（LIST分区的一种特殊类型），和RANGE COLUMNS分区较为相似，同样不接受表达式，同样支持多个列支持string,date和datetime类型。

    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(renewal) (
      PARTITION pWeek_1 VALUES IN('2010-02-01', '2010-02-02', '2010-02-03',
        '2010-02-04', '2010-02-05', '2010-02-06', '2010-02-07'),
      PARTITION pWeek_2 VALUES IN('2010-02-08', '2010-02-09', '2010-02-10',
        '2010-02-11', '2010-02-12', '2010-02-13', '2010-02-14'),
      PARTITION pWeek_3 VALUES IN('2010-02-15', '2010-02-16', '2010-02-17',
        '2010-02-18', '2010-02-19', '2010-02-20', '2010-02-21'),
      PARTITION pWeek_4 VALUES IN('2010-02-22', '2010-02-23', '2010-02-24',
        '2010-02-25', '2010-02-26', '2010-02-27', '2010-02-28')
    );
多列格式：
    CREATE TABLE customers_2 (
      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,last_name,first_name) (
      PARTITION pRegion_1 VALUES IN (('Oskarshamn', 'H?gsby', 'M?nster?s'),('N?ssj?', 'Eksj?', 'Vetlanda')),
      PARTITION pRegion_2 VALUES IN(('Vimmerby', 'Hultsfred', 'V?stervik'),('Uppvidinge', 'Alvesta', 'V?xjo'))
    );

三、HASH：LINEAR HASH分区

和RANGE，LIST分区不同的是，HASH分区无需定义分区的条件。只需要指明分区数即可。

    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;

注意：

1. HASH分区可以不用指定PARTITIONS子句，如上文中的PARTITIONS 4，则默认分区数为4。

2. 不允许只写PARTITIONS，而不指定分区数。

3. 同RANGE分区和LIST分区一样，PARTITION BY HASH (expr)子句中的expr返回的必须是整数值。

4. HASH分区的底层实现其实是基于MOD函数。

譬如，对于下表

    CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
      PARTITION BY HASH( YEAR(col3) )
      PARTITIONS 4;

如果你要插入一个col3为“2005-09-15”的记录，则分区的选择是根据以下值决定的：

MOD(YEAR('2005-09-01'),4)
  = MOD(2005,4)
  = 1

LINEAR HASH分区（HASH分区的一种特殊类型），与HASH分区是基于MOD函数不同的是，它基于的是另外一种算法。

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 LINEAR HASH( YEAR(hired) )
PARTITIONS 4;

说明：

1. 它的优点是在数据量大的场景，譬如TB级，增加、删除、合并和拆分分区会更快，缺点是，相对于HASH分区，它数据分布不均匀的概率更大。

2. 具体算法，可参考MySQL的官方文档

四、KEY：LINEAR KEY分区

其实跟HASH分区差不多，不同点如下：

1. KEY分区允许多列，而HASH分区只允许一列。

2. 如果在有主键或者唯一键的情况下，key中分区列可不指定，默认为主键或者唯一键，如果没有，则必须显性指定列。

3. KEY分区对象必须为列，而不能是基于列的表达式。

4. KEY分区和HASH分区的算法不一样，PARTITION BY HASH (expr)，MOD取值的对象是expr返回的值，而PARTITION BY KEY (column_list)，基于的是列的MD5值。

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

在没有主键或者唯一键的情况下，格式如下：
CREATE TABLE tm1 (
  s1 CHAR(32)
)
PARTITION BY KEY(s1)
PARTITIONS 10;

LINEAR KEY分区（同LINEAR HASH分区类似。）

CREATE TABLE tk (
  col1 INT NOT NULL,
  col2 CHAR(5),
  col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

总结：

1. MySQL分区中如果存在主键或唯一键，则分区列必须包含在其中。
1. 对于原生的RANGE分区，LIST分区，HASH分区，分区对象返回的只能是整数值。
1. RANGE COLUMNS，LIST COLUMNS，KEY，LINEAR KEY分区对象只能是列，不能是基于列的表达式。

唯心之妙

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mySql分区操纵记录

mysql分区（四种分区）：一、RANGE（范围分区）：RANGE COLUMNS分区根据区间来判断位于哪个分区。如下例中，如果store_id小于6，则新增或修改的记录会被分配到p0分区，如果大于6小于11，则会被分配到p1分区，以此类推，类似编程语言中的if…elseif…语句 CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hir
复制链接

扫一扫