Young87

SmartCat's Blog

So happy to code my life!

游戏开发交流QQ群号60398951

当前位置：首页 >跨站数据测试

三分钟了解mysql范围分区--就三分多一分钟都不要

日期： 2020-12-14    分类： 跨站数据测试 476次阅读

范围分区

范围分区表的分区方式是：每个分区都包含行数据且分区的表达式在给定的范围内，分区的范围应该是连续的且不能重叠，可以使用values less than运算符来定义。

说白了就是就是表数据本来存一个地方的现在按一定条件分开存，方便管理。就这么简单。

接下来写几个例子，大家看一遍就都懂了

1、创建普通表

mysql> DROP TABLE IF EXISTS `employees`;
Query OK, 0 rows affected (0.49 sec)

mysql> 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
    -> );
Query OK, 0 rows affected (0.34 sec)

查看存储文件就一个

2、创建带分区的表，下面建表的语句是按照store_id来进行分区的，指定了4个分区

mysql> DROP TABLE IF EXISTS `employees`;
Query OK, 0 rows affected (0.57 sec)

mysql> 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)
    -> );
Query OK, 0 rows affected (0.81 sec)

存储文件变成了四个

在当前的建表语句中可以看到，store_id的值在1-5的在p0分区，6-10的在p1分区，11-15的在p3分区，16-20的在p4分区，但是如果插入超过20的值就会报错，因为mysql不知道将数据放在哪个分区

测试一下

mysql> insert into employees(id,fname,lname,hired,separated,job_code,store_id)  values (1,'zhangsan','lisi',now(),now(),1,1);
Query OK, 1 row affected, 2 warnings (0.06 sec)

mysql> insert into employees(id,fname,lname,hired,separated,job_code,store_id)  values (1,'zhangsan','lisi',now(),now(),1,21);
ERROR 1526 (HY000): Table has no partition for value 21

可以使用less than maxvalue来避免此种情况（maxvalue：表示始终大于等于最大可能整数值的整数值），通过这种方式store_id只要不超过整数范围都没问题

mysql> DROP TABLE IF EXISTS `employees`;
Query OK, 0 rows affected (0.65 sec)

mysql> 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
    -> );
Query OK, 0 rows affected (1.41 sec)
mysql> insert into employees(id,fname,lname,hired,separated,job_code,store_id)  values (1,'zhangsan','lisi',now(),now(),1,100000000);
Query OK, 1 row affected, 2 warnings (0.16 sec)

可以使用相同的方式根据员工的职务代码对表进行分区

mysql> DROP TABLE IF EXISTS `employees`;
Query OK, 0 rows affected (0.59 sec)

mysql> 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)
    -> );
Query OK, 0 rows affected (0.62 sec)

3、可以使用date类型进行分区：如虚妄根据每个员工离开公司的年份进行划分，如year(separated)，最常见的也就是根据时间来进行分区

mysql> DROP TABLE IF EXISTS `employees`;
Query OK, 0 rows affected (0.81 sec)

mysql> 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 RANGE ( YEAR(separated) ) (
    ->     PARTITION p0 VALUES LESS THAN (1991),
    ->     PARTITION p1 VALUES LESS THAN (1996),
    ->     PARTITION p2 VALUES LESS THAN (2001),
    ->     PARTITION p3 VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (1.21 sec)

4、使用函数根据range的值来对表进行分区，如timestampunix_timestamp()

-timestamp不允许使用任何其他涉及值的表达式

mysql> DROP TABLE IF EXISTS quarterly_report_status;
Query OK, 0 rows affected (0.23 sec)

mysql> 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)
    -> );
Query OK, 0 rows affected (2.06 sec)

5、基于时间间隔的分区方案，在mysql5.7中，可以基于范围或事件间隔实现分区方案，有两种选择

1）基于范围的分区，对于分区表达式，可以使用操作函数基于date、time、或者datatime列来返回一个整数值

mysql> 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( YEAR(joined) ) (
    ->     PARTITION p0 VALUES LESS THAN (1960),
    ->     PARTITION p1 VALUES LESS THAN (1970),
    ->     PARTITION p2 VALUES LESS THAN (1980),
    ->     PARTITION p3 VALUES LESS THAN (1990),
    ->     PARTITION p4 VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (0.98 sec)
mysql> DROP TABLE IF EXISTS quarterly_report_status;
Query OK, 0 rows affected (1.07 sec)

mysql> 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)
    -> );
Query OK, 0 rows affected (2.54 sec)

2）基于范围列的分区，使用date或者datatime列作为分区列

mysql> DROP TABLE IF EXISTS members;
Query OK, 0 rows affected (0.68 sec)

mysql> 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
    -> );
Query OK, 0 rows affected (1.29 sec)

三分钟，看一遍就都懂了~多一分都不要

除特别声明，本站所有文章均为原创，如需转载请以超级链接形式注明出处：SmartCat's Blog

标签：mysql mysql

上一篇： GaussDB(DWS)应用实践丨负载管理与作业排队处理方法

下一篇： 做项目就是要专注、聚焦

精华推荐