服务器之家：专注于VPS、云服务器配置技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - MySQL分区之RANGE分区详解

MySQL分区之RANGE分区详解

2022-10-19 15:24pursuer.chen Mysql

Range分区是最常用的一种分区类型,它是根据某个列的值划分为几个连续的区,行数据根据该列的值分别放入到不同的分区,这篇文章主要给大家介绍了关于MySQL分区之RANGE分区的相关资料,需要的朋友可以参考下

介绍

RANGE分区基于一个给定的连续区间范围，早期版本RANGE主要是基于整数的分区。在5.7版本中DATE、DATETIME列也可以使用RANGE分区，同时在5.5以上的版本提供了基于非整形的RANGE COLUMN分区。RANGE分区必须的连续的且不能重叠。使用

“VALUES LESS THAN ()”来定义分区区间,非整形的范围值需要使用单引号，并且可以使用MAXVALUE作为分区的最高值。

一、RANGE分区

1.创建分区

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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)
);
alter table employees add index ix_store_id(store_id) ;alter table employees add index ix_job_code(job_code) ;

p0:指分区p0,这个分区名的取值可以随便取值只要同一个表里面的每个分区名不重复即可，也不需要非得从0开始，你也可以使用a、b、c、d。

THEN():分区的范围值，这个值只能的连续不重叠的从小到大的值。

2.性能分析

插入测试数据

?
1
insert into employees(id,job_code,store_id) values(1,1001,1),(2,1002,2),(3,1003,3),(4,1004,4);

MySQL分区之RANGE分区详解

从执行计划可以看到两个查询都用到了分区的效果；如果细心估计会发现第二个查询没有走索引，并不是使用小于就不会走索引而且执行计划分析评估任务不走索引的效果会更好，事实却是如果当前查询整个分区的数据时使用索引的话还需要去查询其它的字段还不如直接扫描整个分区来的快。

3.增加分区

由于当前分区值的范围是小于21，当向分区表中插入一个超过分区范围的值时会报错。这个时候可以增加一个分区，当你不确定需要给一个多大的上限值时可以使用MAXVALUE

MySQL分区之RANGE分区详解

?
1
alter table employees add PARTITION  (PARTITION p4 VALUES LESS THAN MAXVALUE);

注意：增加分区只能在最大端增加

4.删除分区

?
1
alter table employees drop  PARTITION p4;

注意：通过这种删除分区的方式会将分区中的数据也删除，慎用！！！！。但是通过删除分区的方式删除数据会比delete快很多，因为它相当于删除一个数据库一样因为每个分区都是一个独立的数据文件。用来删除历史分区数据是非常好的办法。

5.拆分合并分区

拆分合并分区统称为重新定义分区，拆分分为不会造成数据的丢失，只将会将数据从一个分区移动到另一个分区。

例1：将P0拆分成s1,s2两个分区

?
1
2
3
4
ALTER TABLE employees REORGANIZE PARTITION p0 INTO (
    PARTITION s0 VALUES LESS THAN (3),
    PARTITION s1 VALUES LESS THAN (6)
);

注意：原来分区p0的范围是[负无穷-6)，所以新拆分的分区也必须是这范围，所以新的分区范围值最大不能超过6。

MySQL分区之RANGE分区详解

分区由原来的p0[-6)变成了so[-3),s1[3-6)，整个分区的范围还是不变。

例2：将s1,p1,p2合并为a,b两个分区

?
1
2
3
4
ALTER TABLE employees REORGANIZE PARTITION s1,p1,p2 INTO (
    PARTITION a VALUES LESS THAN (5),
    PARTITION b VALUES LESS THAN (16)
);

原本的s1,p1,p2分区范围是：[3-16)所以新的分区也必须和原本的分区相同，所以新的分区的值不能低于3不能高于16即可。

MySQL分区之RANGE分区详解

分区由原来的s1[3-6),p1[6-11),p2[11-16)变成了现在的a[3-5),b[5-16),总的范围没有发生变化

注意：无论是拆分还是合并分区都不能改变分区原本的覆盖范围，并且合并分区只能合并连续的分区不能跳过分区合并；并且不能改变分区的类型，例如不能把range分区改成key分区等。

二、日期字段分区方法

注意：RANG分区针对日期字段进行分区可以使用时间类型的函数进行转换成整形，但是如果你的查询语句需要利用分区那么查询语句也需要使用相同的时间函数进行查询。

1.使用YEAR()函数进行分区

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE employees1 (
    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
);

插入测试数据

?
1
insert into employees1(id,separated,job_code,store_id) values(1,'1990-03-04',1001,1),(2,'1995-03-04',1002,2),(3,'1998-03-04',1003,3),(4,'2016-03-04',1004,4);

MySQL分区之RANGE分区详解

对于日期字段分区，查询条件使用> 、< 、betnwen、=都会利用分区查询,如果条件使用函数转换则不会走分区，比如使用YEAR()。

2.TIMESTAMP类型的列的分区方法

针对TIMESTAMP的日期类型的字段需要使用专门的UNIX_TIMESTAMP（）函数进行转换

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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)
);

三、null值处理

当往分区列中插入null值RANG 分区会将其当作最小值来处理即插入最小的分区中

?
1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE test (
    id INT NOT NULL,
    store_id INT
)
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)
);
insert into test(id,store_id) values(1,null);
?
1
2
SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='test';

MySQL分区之RANGE分区详解

备注：文章中的示例摘自mysql官方参考手册

四、移除表的分区

?
1
2
ALTER TABLE tablename
REMOVE PARTITIONING ;

注意：使用remove移除分区是仅仅移除分区的定义，并不会删除数据和drop PARTITION不一样，后者会连同数据一起删除

分区系列文章：

LIST分区：http://www.tuohang.net/article/248216.html

RANGE分区：http://www.tuohang.net/article/248217.html

HASH分区：https://www.tuohang.net/article/248218.html

KEY分区：https://www.tuohang.net/article/248219.html

子分区：https://www.tuohang.net/article/248220.html

指定各分区路径：https://www.tuohang.net/article/248221.html

分区索引以及分区介绍总结：https://www.tuohang.net/article/248222.html

总结

有两点非常重要需要注意，第一删除分区时要慎重因为会连同分区里的数据一并删除，拆分合并分区新的分区一定要和原来的分区的范围一致。RANGE COLUMN分区单独用章节进行讲解，。

到此这篇关于MySQL分区之RANGE分区的文章就介绍到这了,更多相关MySQL RANGE分区内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://www.cnblogs.com/chenmh/p/5627912.html

  • mysql
  • 分区
  • range

    • 延伸 · 阅读

    精彩推荐
    • MysqlMYSQL必知必会读书笔记第二章之版本更改

      MYSQL必知必会读书笔记第二章之版本更改

      本文是小编日常收集整理些有关mysql必知必会笔记整理第二章，小编感觉非常实用，特此分享到脚本之家平台，供大家参考 ...

      自然鸟神5592020-06-07
    • Mysql聊聊幂等性如何保证的

      聊聊幂等性如何保证的

      幂等概念来自数学，表示对数据源做N次变换和1次变换的结果是相同的。在工程中幂等性用来表示用户对于同一操作发起的一次请求或者多次请求的结果是...

      sowhat141212352021-04-15
    • MysqlMysql数据库表中为什么有索引却没有提高查询速度

      Mysql数据库表中为什么有索引却没有提高查询速度

      你有没有想起过为什么明明再数据库中有索引，但是查询速度却并没有希望的那样快？本篇文章将带给你答案，跟小编一起看看吧...

      慕枫技术笔记12052022-02-24
    • Mysqllinux忘记mysql密码处理方法

      linux忘记mysql密码处理方法

      这篇文章主要为大家介绍下linux忘记mysql密码处理方法，需要的朋友可以参考下。 ...

      mdxy-dxy2842019-11-21
    • Mysqlmysql 协议的ping命令包及解析详解及实例

      mysql 协议的ping命令包及解析详解及实例

      这篇文章主要介绍了mysql 协议的ping命令包及解析详解及实例的相关资料,这里附有简单实例代码并附下载源码，需要的朋友可以参考下...

      mysql教程网5012020-07-10
    • Mysql最新MySql8.27主从复制及SpringBoot项目中的读写分离实战教程

      最新MySql8.27主从复制及SpringBoot项目中的读写分离实战教程

      MySQL主从复制是一个异步的复制过程，底层是基于MySQL1数据库自带的二进制日志功能，这篇文章主要介绍了最新MySql8.27主从复制及SpringBoot项目中的读写分离...

      小钟要学习！！！9002022-08-08
    • Mysql详细聊聊MySQL中慢SQL优化的方向

      详细聊聊MySQL中慢SQL优化的方向

      由于在MySQL日常查询中，查询类型的语句占慢sql的大部分,所以下面这篇文章主要给大家介绍了关于MySQL中慢SQL优化方向的相关资料,文中通过示例代码介绍的...

      Ijiran6562021-09-26
    • MysqlMySQL的Query Cache图文详解

      MySQL的Query Cache图文详解

      Query Cache就是把查询返回的结果缓存起来，但是，仅仅只是缓存查询返回的结果，其实是不准确的，Query Cache还会缓存查询语句，在内存中将查询语句和查询...

      水晶命匣5872021-08-26
    最近更新
    编辑推荐
    阅读排行
    热门标签
    150 　 1044 　 1062 　 1053 　 1449 　 1045 　 1290 　 1819 　 2013 　 1406 　 1005 　 1864 　 1862 　 1146 　 1054 　 2006 　 1615 　 1215 　 1175 　 10048 　 2003 　 1366 　 28000 　 1577 　 1135 　 1786 　 1093 　 1067 　 1698 　 2002 　
    © 2019-2022 服务器之家(www.tuohang.net) 版权所有关于我们联系我们版权申明网站地图