MySQL分区类型

最新推荐文章于 2024-05-16 14:05:48 发布
最新推荐文章于 2024-05-16 14:05:48 发布
阅读量827 收藏 2
点赞数
分类专栏： # MySQL 分区 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_33033819/article/details/106976790
版权

文章目录

MySQL分区类型

RANGE 分区：基于一个给定连续区间范围，把数据分配到不同的分区。

LIST 分区：基于枚举出的值列表分区。

COLUMNS 分区：类似于 RANGE 和 LIST ，区别在于分区键既可以是多列，又可以是非整数。

HASH 分区：基于给定的分区个数，把数据取模分配到不同的分区。

KEY 分区：类似于 HASH 分区，但使用 MySQL 提供的哈希函数。

子分区：也叫作复合分区或者组合分区，即在主分区下再做一层分区，将数据再次分割。

注意：1、RANGE 分区、LIST 分区、HASH 分区都要求分区键必须是 INT 类型，或者通过表达式返回 INT 类型；但 KEY 和 COLUMNS 分区可以使用其他类型的列（BLOB 或 TEXT 列类型除外）作为分区键。

​ 2、无论是哪种分区类型，要么分区表上没有主键/唯一键，要么分区表的主键/唯一键都在分区键上

​ 3、MySQL表的列名、别名、分区名是不区分大小写的。

一、RANGE 分区
1、RANGE 分区

​ 按照 RANGE 分区的表时利用取值范围将数据进行分区，区间要连续并且不能互相重叠，使用 VALUES LESS THAN 操作符进行分区定义。支持表达式作为分区键。

#创建分区表
create table emp(
	id INT NOT NULL,
	ename VARCHAR(30),
	hired DATE NOT NULL DEFAULT '1970-01-01',
	separated DATE NOT NULL DEFAULT '9999-12-31',
	job VARCHAR(30) NOT NULL,
	store_id INT NOT NULL
)
PARTITION BY RANGE (store_id)(
	PARTITION p0 VALUES LESS THAN(10),
	PARTITION p1 VALUES LESS THAN(20),
	PARTITION p2 VALUES LESS THAN(30)
);

#增加表分区，MAXVALUE 表示最大的可能的整数值
alter table emp add partition(partition p3 VALUES LESS THAN MAXVALUE);

#删除表分区
alter table emp drop partition p0;

​ 注意：在 RANGE 分区中，分区键如果是 NULL 值会被当作一个最小值来处理。

2、RANGE COLUMNS 分区

​ RANGE COLUMNS 分区支持整数、日期时间、字符串数据类型

​ 整数类型：支持 tinyint、smallint、mediumint、int 和 bigint；其他数值类型不支持。

​ 日期时间类型：支持 date 和 datetime 。

​ 字符类型：支持 char、varchar、binary 和 varbinary。

​ 注意：COLUMNS 分区支持一个或者多个字段作为分区（即多列分区），但不支持表达式作为分区键。

#创建 RANGE COLUMNS 分区
create table emp_date(
	id INT NOT NULL,
	ename VARCHAR(30),
	hired DATE NOT NULL DEFAULT '1970-01-01',
	separated DATE NOT NULL DEFAULT '9999-12-31',
	job VARCHAR(30) NOT NULL,
	store_id INT NOT NULL
)
PARTITION BY RANGE COLUMNS(separated)(
	PARTITION p0 VALUES LESS THAN('1970-01-01'),
	PARTITION p1 VALUES LESS THAN('2000-01-01'),
	PARTITION p2 VALUES LESS THAN('2010-01-01')
);


#创建 RANGE COLUMNS 多列分区
create table rcol(
	a int,
	b int
)partition by range columns(a,b)(
	partition p01 values less than(0,10),
	partition p02 values less than(10,10),
	partition p03 values less than(10,maxvalue),
	partition p04 values less than(maxvalue,maxvalue)
);

​ RANGE COLUMNS 多列分区键的比较是基于元组的比较；例如写入 a=1,b=10，因为元组(1,10)<(10,10) ，所以插入到 p02。（即 a<=10 and b <10）

mysql> select (1,10)<(10,10) from dual;
+----------------+
| (1,10)<(10,10) |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

mysql> insert into rcol(a,b) values(1,10);
Query OK, 1 row affected (0.01 sec)

mysql> select partition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema() and table_name='rcol';
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| p01            | `a`,`b`              | 0,10                  |          0 |
| p02            | `a`,`b`              | 10,10                 |          1 |
| p03            | `a`,`b`              | 10,20                 |          0 |
| p04            | `a`,`b`              | 10,MAXVALUE           |          0 |
| p05            | `a`,`b`              | MAXVALUE,MAXVALUE     |          0 |
+----------------+----------------------+-----------------------+------------+
5 rows in set (0.00 sec)
二、LIST 分区
1、LIST 分区

​ LIST 分区是建立离散的值列表告诉数据库特定的值属于哪个分区，是一个枚举列表的值得集合。支持表达式作为分区键。

#创建 LIST 分区
create table expenses(
	expense_date date not null,
	category int,
	amount decimal(10,3)
)partition by list(category)(
	partition p0 values in(1,3),
	partition p1 values in(2,5),
	partition p2 values in(6,7),
	partition p3 values in(4,10)
);
2、LIST COLUMNS 分区

​ LIST COLUMNS 分区支持整数、日期时间、字符串数据类型

​ 整数类型：支持 tinyint、smallint、mediumint、int 和 bigint；其他数值类型不支持。

​ 日期时间类型：支持 date 和 datetime 。

​ 字符类型：支持 char、varchar、binary 和 varbinary。

​ 注意：COLUMNS 分区支持一个或者多个字段作为分区（即多列分区），但不支持表达式作为分区键。

create table expenses_col(
	expense_date date not null,
	category varchar(30),
	amount decimal(10,3)
)partition by list columns(category)(
	partition p0 values in('li','mei'),
	partition p1 values in('lili','gu'),
	partition p2 values in('tiger','hope'),
	partition p3 values in('nan','cili')
);
三、HASH 分区

​ HASH 分区主要用来分散热点读，确保数据在预先确定个数的分区中尽可能平均分布。

​ MySQL 支持两种 HASH 分区：常规 HASH 和线性 HASH 分区

1、常规 HASH

​ 使用取模算法。优点：通过取模的方法来使数据尽可能平均分布在每个分区中，让每个分区管理的数据都减少了，提高了查询效率。缺点：当我们需要增加分区或合并分区时，分区中的数据大部分都需要通过计算重新分区。

#创建常规 HASH 分区
create table emp_hash(
	id INT NOT NULL,
	ename VARCHAR(30),
	hired DATE NOT NULL DEFAULT '1970-01-01',
	separated DATE NOT NULL DEFAULT '9999-12-31',
	job VARCHAR(30) NOT NULL,
	store_id INT NOT NULL
)PARTITION BY HASH (store_id) PARTITIONS 4;

#插入数据，因为 MOD(134,4)=2，所以 store_id= 134 存在第二分区
insert into emp_hash values(1,'lili','2010-10-01','9999-01-01','clerk','134');

#查询数据所在分区
mysql> select partition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema() and table_name='emp_hash';
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| p0             | store_id             | NULL                  |          0 |
| p1             | store_id             | NULL                  |          0 |
| p2             | store_id             | NULL                  |          1 |
| p3             | store_id             | NULL                  |          0 |
+----------------+----------------------+-----------------------+------------+
4 rows in set (0.00 sec)
2、线性 HASH 分区（LINEAR HASH 分区）

​ 使用一个线性的 2 的幂的运算法则。优点：在分区维护（增加、删除、合并、拆分分区）时，MySQL能够处理的更加快速。缺点：各个分区之间数据的分布不太均衡。

#创建 LINEAR HASH 分区
create table emp_linear(
	id INT NOT NULL,
	ename VARCHAR(30),
	hired DATE NOT NULL DEFAULT '1970-01-01',
	separated DATE NOT NULL DEFAULT '9999-12-31',
	job VARCHAR(30) NOT NULL,
	store_id INT NOT NULL
)PARTITION BY LINEAR HASH (store_id) PARTITIONS 6;

#插入数据 134，因为 V=POWER(2,CEILING(LOG(2, 6)))=8，N=mod(134,8)=6,又因为 N=6>=6,故 N=mod(N,(V/2))=mod(6,4)=2,所以在分区 p2
mysql> insert into emp_linear values(1,'lili','2010-10-01','9999-01-01','clerk','134');
Query OK, 1 row affected (0.00 sec)

mysql> select partition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema() and table_name='emp_linear';
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| p0             | store_id             | NULL                  |          0 |
| p1             | store_id             | NULL                  |          0 |
| p2             | store_id             | NULL                  |          1 |
| p3             | store_id             | NULL                  |          0 |
| p4             | store_id             | NULL                  |          0 |
| p5             | store_id             | NULL                  |          0 |
+----------------+----------------------+-----------------------+------------+
6 rows in set (0.00 sec)

#插入数据 133，因为 V=POWER(2,CEILING(LOG(2, 6)))=8，N=mod(134,8)=5,又因为 N=5<6,所以在分区 p5
mysql> insert into emp_linear values(1,'lili','2010-10-01','9999-01-01','clerk','133');
Query OK, 1 row affected (0.00 sec)

mysql> select partition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema() and table_name='emp_linear';
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| p0             | store_id             | NULL                  |          0 |
| p1             | store_id             | NULL                  |          0 |
| p2             | store_id             | NULL                  |          1 |
| p3             | store_id             | NULL                  |          0 |
| p4             | store_id             | NULL                  |          0 |
| p5             | store_id             | NULL                  |          1 |
+----------------+----------------------+-----------------------+------------+
6 rows in set (0.00 sec)

计算过程

#LOG()是计算NUM以2为底的对数，CEILING()是向上取整，POWER()是取2的次方值
#values 分区键为整数，num为分区数
V = POWER(2, CEILING(LOG(2, num)))
#N=values&(V-1)
N=mod(values,V)
if N >= num
	#N = N&(CEIL(V/2)-1)
	N=mod(N,V/2)
四、KEY 分区
1、KEY 分区

​ KEY 分区不允许使用用户自定义的表达式，需要使用 MySQL 服务器提供的 HASH 函数，支持使用除 BLOB 和 Text 外其他类型的列作为分区键，支持零个或者多个字段名作为分区键。

#创建 KEY 分区
create table emp_key(
	id INT NOT NULL,
	ename VARCHAR(30),
	hired DATE NOT NULL DEFAULT '1970-01-01',
	separated DATE NOT NULL DEFAULT '9999-12-31',
	job VARCHAR(30) NOT NULL,
	store_id INT NOT NULL
)PARTITION BY KEY (store_id) PARTITIONS 4;

#如果不指定分区键，默认首先选择使用主键作为分区键
create table emp_key_pri(
	id INT NOT NULL,
	ename VARCHAR(30),
	hired DATE NOT NULL DEFAULT '1970-01-01',
	separated DATE NOT NULL DEFAULT '9999-12-31',
	job VARCHAR(30) NOT NULL,
	store_id INT NOT NULL,
	primary key(id)
)PARTITION BY KEY () PARTITIONS 4;

#如果不指定分区键，没有主键的情况下，选择非空唯一键作为分区键。
create table emp_key_uni(
	id INT NOT NULL,
	ename VARCHAR(30),
	hired DATE NOT NULL DEFAULT '1970-01-01',
	separated DATE NOT NULL DEFAULT '9999-12-31',
	job VARCHAR(30) NOT NULL,
	store_id INT NOT NULL,
	unique key(id)
)PARTITION BY KEY () PARTITIONS 4;

​ 注意：1) 如果不指定分区键，默认首先选择使用主键作为分区键。如果不指定分区键，没有主键的情况下，选择非空唯一键作为分区键。

​ 2) 如果没有主键，也没有唯一键的情况下，必须指定分区键。作为分区键的唯一键必须是非空的。

2、LINEAR KEY 分区

​ 分区的编号通过 2 的幂算法得到。

#创建 LINEAR KEY 分区
create table emp_key_lin(
	id INT NOT NULL,
	ename VARCHAR(30),
	hired DATE NOT NULL DEFAULT '1970-01-01',
	separated DATE NOT NULL DEFAULT '9999-12-31',
	job VARCHAR(30) NOT NULL,
	store_id INT NOT NULL
)PARTITION BY LINEAR KEY (store_id) PARTITIONS 4;
五、子分区

​ 子分区（Subpartitioning）是分区表中对每个分区的再次分割，有称为复合分区（Composite Partitioning），MySQL 5.7 支持对已经通过 RANGE 或者 LIST 分区的表进行子分区。子分区既可以使用 HASH 分区，也可以使用 KEY 分区。

#创建子分区
create table emp_range_sub(
	id INT NOT NULL,
	hired date
)PARTITION BY RANGE (year(hired))
SUBPARTITION BY HASH(to_days(hired))
SUBPARTITIONS 2
(
PARTITION p0 VALUES LESS THAN(1990),
PARTITION p1 VALUES LESS THAN(2000),
PARTITION p2 VALUES LESS THAN MAXVALUE
);

#或
create table emp_range_sub1(
	id INT NOT NULL,
	hired date
)PARTITION BY RANGE (year(hired))
SUBPARTITION BY HASH(to_days(hired))
(
PARTITION p0 VALUES LESS THAN(1990)(
	SUBPARTITION s0,
	SUBPARTITION s1
),
PARTITION p1 VALUES LESS THAN(2000)(
	SUBPARTITION s2,
	SUBPARTITION s3
),
PARTITION p2 VALUES LESS THAN MAXVALUE(
	SUBPARTITION s4,
	SUBPARTITION s5
));

注意：1、每个分区必须具有相同数量的子分区。

​ 2、如果要显示指定子分区，则每个分区都要显示指定。

​ 3、子分区的名称在整个表中时唯一的。

六、MySQL 分区处理 NULL 值得方式

1、RANGE 分区中， NULL 值会被当作最小值来处理。

2、LIST 分区中，NULL 值必须出现在枚举列表中，否则不被接受。

3、HASH/KEY 分区中，NULL 值会被当作零值来处理。

sunset_ggz
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
简述MySQL分区表类型
qf2019的博客
08-20 586
分区是将一个表的数据按照某种方式，比如按照时间上的月份，分成多个较小的，更容易管理的部分，但是逻辑上仍是一个表。我们在此之前已经讲过MySQL分区表的原理，分区有利于管理非常大的表，它采用分而治之的逻辑，便于对数据的管理。本文我们就来进一步了解MySQL分区表，详细看一下MySQL分区表类型究竟有几个？ MySQL支持多种分区表，我们看到最多的是根据范围进行分区，每个分区存储落在某个范围的记录，分区表达式可以是列，也可以是包含列的表达式。分区的一个主要目的是将数据按照一个较粗的粒度分在不同的表中，这样做可以
MySQL 分区表
Quiet_boy
12-26 320
今天统计数据的时候发现一张表使用了表分区，借此机会记录一下。 1. 什么是表分区？ 表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。 2. 表分区与分表的区别 分表：指的是通过一定规则，将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。 分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分
mysql分区--类型
daicooper的博客
04-24 2199
分区类型 mysql主要的分区类型 RANGE分区：基于一个给定连续区间范围，把数据分配到不同的分区。 LIST分区：类似于RANGE分区，区别在于 LIST分区是基于枚举出的值列表分区。RANGE是基于给定的连续区间范围分区。 HASH分区：基于给定的分区个数，把数据分配到不同的分区。 KEY分区：类似于HASH分区。 无论是哪种 MySQL分区类型，要么分区表上没有主键/唯一键，要么分...
spark的转换算子2
changshupx的博客
09-08 1682
1）coalesce def coalesce(numPartitions: Int, shuffle: Boolean = false)(implicit ord: Ordering[T] = null): RDD[T] 该函数用于将RDD进行重分区，使用HashPartitioner。 第一个参数为重分区的数目，第二个为是否进行shuffle，默认为false; 作用 缩减分区数，用于大数据集过滤后，提高小数据集的执行效率。 val ListRDD: RDD[Int] = context.makeRDD
【mysql 详解】mysql分区详解
最新发布
snakelis的博客
05-16 382
mysql 详解 分区 range 分区 hash分区 key分区 子分区
MySQL之分区表(分区表的特点、分区键、分区类型、RANGE分区、HASH分区、LIST分区、如何选择合适的分区方式、修改分区-添加分区、分区删除、使用分区表的注意事项)
yanghao8866的博客
08-07 935
分区表的特点 在逻辑上为一个表,在物理上存储在多个文件中 create table `login_log`( login_id int(10) unsigned not null comment '登录用户id', login_time timestamp not null default current_timestamp, login_ip int(10) unsigned not null comment '登录类型' )engine=innodb default charse
MYSQL分区表测试
热门推荐
08-20 2万+
一、       mysql分区简介 数据库分区 数据库分区是一种物理数据库设计技术。虽然分区技术可以实现很多效果，但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减sql语句的响应时间，同时对于应用来说分区完全是透明的。 MYSQL的分区主要有两种形式：水平分区和垂直分区   水平分区（HorizontalPartitioning） 这种形式的分区是对根据表的行进行分区，
mysql 表分区的类型介绍与分区类型演示和分区的原理
小洪帽i的博客
08-10 875
表分区的类型介绍 MySQL 对于表分区有四种方式分别是：range、list、hash、key 的方式分区 range 分区：基于属于一个给定连续区间的列值，把多行分配给分区。 list 分区：类似于按 range 分区，区别在于 list 分区是基于列值匹配一个离散值集合中的某个值来进行选择。 hash 分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含 MySQL 中有效的、产生非负整数值的任何表达式。 key 分区：类似于按 ha
『Mysql』Mysql四种分区方式及组合分区落地实现
老陈聊架构
11-02 8233
📣读完这篇文章里你能收获到， Mysql分区的概念， Mysql分区四种分区方式的落地及案例， Mysql分区的管理
详解MySQL分区表
12-14
【MySQL分区表详解】 分区表是数据库管理中一种高级的数据组织策略，主要目的是为了提高大表的查询效率和数据管理的便利性。在MySQL中，分区表是通过`PARTITION BY`子句来实现的，它允许我们将一个大的表根据特定的...
mysql分区文档
07-11
MySQL 支持多种分区类型，包括范围（range）、列表（list）、哈希（hash）和键（key）分区。 **1. 范围（Range）分区** 范围分区基于某个字段值的范围进行划分。例如，可以将`teacher`表按`birthdate`字段的年份...
mysql分区表LIST
12-14
MySQL分区表是数据库管理系统中的一种优化策略，它将大型表分成多个较小的部分，每个部分（或称为分区）存储在不同的物理位置，以提高查询性能和管理效率。在MySQL中，分区通常基于范围、列表、哈希或键值的某种组合...
创建mysql表分区的方法
09-10
MySQL支持多种分区类型，其中包括RANGE分区，如标题和描述中提到的。RANGE分区是根据列值的范围来划分数据的。例如，`PARTITION BY RANGE (id)`会依据`id`列的值将数据分配到不同的分区。在提供的代码示例中，`p_...
Mysql分区 - Key分区
秃头的博客
11-25 3135
Key（键值）** – Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。
mysql 分区键_mysql分区
weixin_39940770的博客
01-27 1573
分区就访问数据库的应用而言，逻辑上只有一个表或一个索引，但是实际上这个表可能由数10个物理分区对象组成，每个分区都是一个独立的对象，可以独自处理，可以作为表的一部分进行处理。分区对应用来说是完全透明的，不影响应用的业务逻辑创建分区，是对数据表，和索引表，同时创建分区的。不能分别创建分区；同时也不能对部分数据进行分区；分区在创建完成之后，管理员也无法动态更改；mysql分区的优点主要包括：和单个磁盘...
MySQL表(五)-分区表(RANGE分区、LIST分区、HASH分区、KEY分区、COLUMNS分区、子分区)
星光之子的博客
08-14 1603
分区表分区概述 分区概述 分区功能 并不是 在存储引擎层 完成的，因此不是只有InnoDB存储引擎支持分区，常见的存储引擎MyISAM、NDB等都支持。但也并不是所有的存储引擎都支持，如CSV、FEDORATED、MERGE等就不支持 MySQL数据库在5.1版本时添加了对分区的支持 分区的过程 是将 一个表或索引 分解为 多个更小、更可管理的部分 就访问数据库的应用而言，从逻辑上讲，只有一个表或一个索引，但是在物理上 这个表或索引 可能由 数十个物理分区组成。每个分区都是独立的对象，可以独自处理，也可以作
MySQL数据库表分区功能详解
deng1228的博客
07-24 2525
1、什么是表分区？ mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过my.cnf中的datadir来查看），一张表主要对应着三个文件，一个是frm存放表结构的，一个是myd存放表数据的，一个是myi存表索引的。如果一张表的数据量太大的话，那么myd,myi就会变的很大，查找数据就会变的很慢，这个时候我们可以利用mysql的分区功能，在物理...
MySQL表分区
atwdy的博客
06-13 1万+
主要介绍mysql几种表分区的基本使用，包括range分区、list分区、hash分区、key分区、子分区和columns分区。
mysql 分区-list分区（三）
yinjinshui的博客
10-09 345
list分区 list分区与range分区有类似的地方： demo1： 假定你需要将超市员工划分地区，该表有40家超市职员记录。这40家超市的编号从1到40。而这40家超市分布在5个有经销权的地区，店铺分布如下表示 地区 店铺id号 东区 1,2,3,4,5,6,7,8 南区 17,18,19,20,21,22,23,24 西区 33,34,35,36,37,38,39,4...
mysql 分区 分表
04-29
常见的MySQL分区类型有：按范围分区、按列表分区、按哈希分区和按键值分区。通过分区可以减小单个表的数据量，在大数据量下提高查询效率。 分表是将一张表按照规则拆分为多个表。通过分表可以减小单个表的数据量和...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值