MySQL——分区表

最新推荐文章于 2025-07-09 14:39:21 发布
原创 最新推荐文章于 2025-07-09 14:39:21 发布 · 320 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #数据库

分区

对一个表做分区有很多种方法：

  • RANGE：这种类型的区分根据落在给定范围内的列值，将行分配给分区
  • LIST：类似于按RANGE分区，不同的是其分区是基于一组离散值匹配的列来选择的
  • HASH：在这种类型的分区操作中，一个分区是根据用户定义的表达式返回值来选择的，该表达式对插入到表的行中的列值进行操作。HASH函数可以包含任何在MySQL中具有非负整数值的有效表达式
  • KEY：这种类型的分区类似于HASH分区，只是它仅提供一个或多个列，而且MySQL服务器提供自己的哈希函数。这些列可以包含除整数值以外的其他值，因为MySQL提供的哈希函数保证结果都未整数。

上述每一个分区类型都有一个扩展。RANGE的扩展为RANGE COLUMNS，LIST的扩展为LIST COLUMN，HASH的扩展为LINEAR HASH，KEY的扩展为LINEAR KEY。

对于[LINEAR] KEY、RANGE COLUMNS和LIST COLUMNS分区，分区表达式包含一个或多个列的列表。

在RANGE、LIST和[LINEAR] HASH分区中，分区的列的值被传递给分区函数，该函数返回一个整数值，表示该特定记录应该被存储在第几个分区。这个函数的返回值必须为既非常数也非随机数。

如何操作

可以在创建表时指定分区，也可以通过ALTER TABLE命令来指定分区。分区列应该是表中所有唯一键的一部分。

如果基于created_at列定义了分区，并且id是主键，则应该将created_at列作为PRIMARY KEY的一部分包含在内，即(id, created_at)。

RANGE分区

如果想要根据emp_no对employees表分区，并希望在一个分区中保留100000名员工，可以这样创建分区：

 -- 所有emp_no小于100000的员工划入分区p0，所有小于200000大于100000的员工划入分区p1，以此类推
create table `employees` (
	emp_no int(11) not null,
	birth_date date not null,
	first_name varchar(14) not null,
	last_name varchar(16) not null,
	gender enum('M', 'F') not null,
	hire_date date not null,
	address varchar(100) default null,
	primary key (emp_no),
	key `name` (first_name, `last_name`)
) engine = innodb default charset = utf8mb4
partition by range (emp_no)
(partition p0 values less than (100000) engine = innodb,
 partition p1 values less than (200000) engine = innodb,
 partition p2 values less than (300000) engine = innodb,
 partition p3 values less than (400000) engine = innodb,
 partition p4 values less than (500000) engine = innodb);


-- 注意：如果员工编号大于500000，因为没有为这些编号定义分区，所以这时插入操作将会失败，并报错。为了避免这种情况，必须定期检查并添加分区或创建一个MAXVALUE分区，以捕获所有类似这种情况的异常：
create table `employees` (
	emp_no int(11) not null,
	birth_date date not null,
	first_name varchar(14) not null,
	last_name varchar(16) not null,
	gender enum('M', 'F') not null,
	hire_date date not null,
	address varchar(100) default null,
	primary key (emp_no),
	key `name` (first_name, `last_name`)
) engine = innodb default charset = utf8mb4
partition by range (emp_no)
(partition p0 values less than (100000) engine = innodb,
 partition p1 values less than (200000) engine = innodb,
 partition p2 values less than (300000) engine = innodb,
 partition p3 values less than (400000) engine = innodb,
 partition p4 values less than (500000) engine = innodb,
 partition pmax values less than maxvalue engine = innodb);

如果想基于hire_date分区，可以使用YEAR(hire_date)函数作为分区表达式：

create table `employees` (
	emp_no int(11) not null,
	birth_date date not null,
	first_name varchar(14) not null,
	last_name varchar(16) not null,
	gender enum('M', 'F') not null,
	hire_date date not null,
	address varchar(100) default null,
	primary key (emp_no, hire_date),
	key `name` (first_name, `last_name`)
) engine = innodb default charset = utf8mb4
partition by range (year(hire_date))
(partition p1980 values less than (1980) engine = innodb,
 partition p1990 values less than (1990) engine = innodb,
 partition p2000 values less than (2000) engine = innodb,
 partition p2010 values less than (2010) engine = innodb,
 partition p2020 values less than (2020) engine = innodb,
 partition pmax values less than maxvalue engine = innodb);

MySQL中的分区被广泛应用与date、datetime或timestamp列。如果想在数据库中存储一些事件，并且所有的查询都基于一个时间范围，则可以像这样使用分区。

分区函数to_days()返回自0000-01-01以来的天数，这是一个整数值：

 create table `event_history` (
	event_id int(11) not null,
	envent_name varchar(10) not null,
	created_at datetime not null,
	last_updated timestamp default current_timestamp on update current_timestamp,
	event_type varchar(10) not null,
	msg tinytext not null,
	primary key (event_id, created_at)
 ) engine = innodb default charset = utf8mb4
 partition by range (to_days(created_at))
 (partition p20170930 values less than (736967) engine = innodb,
  partition p20171001 values less than (736968) engine = innodb,
	partition p20171002 values less than (736969) engine = innodb,
	partition p20171003 values less than (736970) engine = innodb,
	partition p20171004 values less than (736971) engine = innodb,
	partition p20171005 values less than (736972) engine = innodb,
	partition p20171006 values less than (736973) engine = innodb,
	partition p20171007 values less than (736974) engine = innodb,
	partition p20171008 values less than (736975) engine = innodb,
	partition p20171009 values less than (736976) engine = innodb,
	partition p20171010 values less than (736977) engine = innodb,
	partition p20171011 values less than (736978) engine = innodb,
	partition p20171012 values less than (736979) engine = innodb,
	partition p20171013 values less than (736980) engine = innodb,
	partition p20171014 values less than (736981) engine = innodb,
	partition p20171015 values less than (736982) engine = innodb,
	partition pmax values less than maxvalue engine = innodb);

如果希望将现有的一个表转换为分区的表，如果分区键不是主键的一部分，则需要删除(drop)主键，并将分区键作为主键和所有唯一键的一部分添加进来。否则，将收到如下报错：
在这里插入图片描述

可以如下操作：

alter table employees drop primary key, add primary key(emp_no, hire_date);
	
alter table employees partition by range (year(hire_date))
(partition p1980 values less than (1980) engine = innodb,
 partition p1990 values less than (1990) engine = innodb,
 partition p2000 values less than (2000) engine = innodb,
 partition p2010 values less than (2010) engine = innodb,
 partition p2020 values less than (2020) engine = innodb,
 partition pmax values less than maxvalue engine = innodb);

RANGE COLUMNS 分区

RANGE COLUMNS分区类似于RANGE分区，但是它允许使用基于多个列值的范围来定义分区。此外，你可以使用非整数类型的列来定义范围。RANGE COLUMNS分区与RANGE分区在一下几方面有很大的区别：

  • RANGE COLUMNS不接受表达式，只接受列的名称
  • RANGE COLUMNS接受一个或多个列的列表
  • RANGE COLUMNS的分区列不限于整数列；字符串、DATE和DATETIME列也可以用作分区列
-- 在RANGE COLUMNS中可以直接使用hire_date列，而不是使用to_days()或year()函数
alter table employees partition by range columns (hire_date)
(partition p0 values less than ('1970-01-01'),
 partition p1 values less than ('1980-01-01'),
 partition p2 values less than ('1990-01-01'),
 partition p3 values less than ('2000-01-01'),
 partition p4 values less than ('2010-01-01'),
 partition p5 values less than (maxvalue));

-- 可以根据员工的姓氏(last_name)来划分员工，这么做不能保证员工在各个分区之间均匀分布

alter table employees drop primary key, add primary key(emp_no, last_name);
	
alter table employees partition by range columns (last_name)
(partition p0 values less than ('b'),
 partition p1 values less than ('f'),
 partition p2 values less than ('l'),
 partition p3 values less than ('u'),
 partition p4 values less than ('z'));

使用RANGE COLUMNS，可以在分区函数中放置多个列：

 create table range_column_example (
	a int,
	b int,
	c int,
	d int,
	e int,
	primary key(a, b, c)
 )
 partition by range columns (a, b, c) (
	partition p0 values less than (0, 25, 50),
	partition p1 values less than (10, 50, 100),
	partition p2 values less than (10, 100, 200),
	partition p3 values less than (maxvalue, maxvalue, maxvalue)
 );

如果插入值a=10,b=20,c=100,d=100,e=100，则它被分配到p1分区。在通过RANGE COLUMNS设计分区表时，可以利用mysql客户端比较所需的元组来测试连续分区的定义，如下所示：
在这里插入图片描述

LIST和LIST COLUMNS分区

LIST分区与RANGE分区类似，其每个分区都是根据一组值列表中的一个列值的成员来定义和选择的，而不是在一组连续的值范围内进行。

需要通过PARTITION BY LIST（<expr>)来定义它，其中expr是一个列值或基于列值的表达式，并返回一个整数值。

分区定义包含VALUES IN(<value_list>),其中value_list是一个用逗号分隔的整数列表，而不是VALUES LESS THAN (<value>)。

如果希望使用除整数以外的数据类型，可以使用LIST COLUMNS。

与RANGE分区的情况不同，这里没有像MAXVALUE之类的catch-alI,分区表达式期望的所有值都应该包含在PARTITION表达式中。

假设有一张带有邮政编码和城市信息的客户表。如果你想在分区中按照特定的邮政编码来划分客户，可以使用工LIST分区：

create table customer (
	customer_id int,
	zipcode int,
	city varchar(100),
	primary key (customer_id, zipcode)
)
partition by list(zipcode) (
	partition pnorth values in (560030, 560007, 560051, 560084),
	partition peast values in (560040, 560008, 560061, 560085),
	partition pwest values in (560050, 560009, 560062, 560086),
	partition pcentral values in (560060, 560010, 560063, 560087)
);

-- 如果希望 直接使用列而不是整数，则可以使用LIST COLUMNS来分区：
create table customer (
	customer_id int,
	zipcode int,
	city varchar(100),
	primary key (customer_id, city)
)
partition by list columns(city) (
	partition pnorth values in ('city1', 'city2', 'city3'),
	partition peast values in ('city4', 'city5', 'city6'),
	partition pwest values in ('city7', 'city8', 'city9'),
	partition pcentral values in ('city10', 'city11', 'city12')
);

HASH和LINEAR HASH分区

HASH分区主要是为了确保数据均匀地分布在数量预先确定的一组分区中。使用RANGE或工IST分区的话，必须明确指定应该将给定的列值或列值集合存储在哪一个分区中；而
如果使用HASH分区，这个决定将由你来做，你只需要根据要进行哈希的列值指定一个列值或表达式，以及分区表要分为多少个分区即可。

如果你希望员工在分区中均匀地分布，可以指定分区的数量，并根据YEAR(hire_date)进行HASH分区，而不是根据YEAR(hire_date)进行RANGE分区。当使用PARTITION BY HASH时，存储引擎会根据该表达式结果的模来确定要使用哪一个分区。

例如，如果hire_date是1987-11-28，YEAR(hire_date)将是1987，MOD(1987, 8)的结果是3，所以这一样将被分到第三个分区：

create table `employees` (
	emp_no int(11) not null,
	birth_date date not null,
	first_name varchar(14) not null,
	last_name varchar(16) not null,
	gender enum('M', 'F') not null,
	hire_date date not null,
	address varchar(100) default null,
	primary key (emp_no, hire_date),
	key `name` (first_name, `last_name`)
) engine = innodb default charset = utf8mb4
partition by hash (year(hire_date))
partitions 8;

效率最高的哈希函数是对单个列进行操作的函数，其值与该列值同步地增加或减少。

在LINEAR HASH分区中，可以使用相同的语法，只不过要添加LINEAR关键字。MySQL不使用MODULUS操作，而是使用2的幂算法来确定分区。

create table `employees` (
	emp_no int(11) not null,
	birth_date date not null,
	first_name varchar(14) not null,
	last_name varchar(16) not null,
	gender enum('M', 'F') not null,
	hire_date date not null,
	address varchar(100) default null,
	primary key (emp_no, hire_date),
	key `name` (first_name, `last_name`)
) engine = innodb default charset = utf8mb4
partition by linear hash (year(hire_date))
partitions 8;

KEY和LINEAR KEY分区

KEY分区与HASH分区类似，不同之处在于，HASH分区使用用户定义的表达式，KEY分区的哈希函数由MySQL服务器提供。这个内部哈希函数采用的是与PASSWORD()函数
相同的算法。

KEY仅包含零个或几个列名称的列表。如果表有主键的话，则用作KEY分区的任何列都必须是主键的一部分或全部。如果没有列名可以被指定为分区键，则使用表的主键（如果有的话)：

create table `employees` (
	emp_no int(11) not null,
	birth_date date not null,
	first_name varchar(14) not null,
	last_name varchar(16) not null,
	gender enum('M', 'F') not null,
	hire_date date not null,
	address varchar(100) default null,
	primary key (emp_no, hire_date),
	key `name` (first_name, `last_name`)
) engine = innodb default charset = utf8mb4
partition by linear key()
partitions 8;

子分区

可以将每个分区进一步划分为一个分区表，称为子分区或复合分区

create table `employees` (
	emp_no int(11) not null,
	birth_date date not null,
	first_name varchar(14) not null,
	last_name varchar(16) not null,
	gender enum('M', 'F') not null,
	hire_date date not null,
	address varchar(100) default null,
	primary key (emp_no, hire_date),
	key `name` (first_name, `last_name`)
) engine = innodb default charset = utf8mb4
partition by range (year(hire_date))
subpartition by hash (emp_no)
	subpartitions 4 (
		partition p0 values less than (1990),
		partition p1 values less than (2000),
		partition p2 values less than (2010),
		partition p3 values less than (2020),
		partition p4 values less than maxvalue
	);

删除分区

如果希望删除分区，可以执行REMOVE PARTITIONING语句：

alter table employees remove partitioning;
MySQL的分区表
九师兄
08-22 3837
概述 Hash分区 CREATE TABLE employees ( id INT NOT NULL, name VARCHAR(30), store_id INT ) PARTITION BY HASH(store_id) PARTITIONS 4; 新建一个employees表，这个表以对store_id进行hash后的值确定存储在哪个分区，PARTITION...
MYSQL——临时表的相关原理
庄小焱
01-08 1397
摘要 优化 join 查询的时候使用到了临时表。当时，我们是这么用的： create temporary table temp_t like t1; alter table temp_t add index(b); insert into temp_t select * from t2 where b>=1 and b<=2000; select * from t1 join temp_t on (t1.b=temp_t.b); 你可能会有疑问，为什么要用临时表呢？直接用普通表是不是也可
MySQL分区表
weixin_33769125的博客
10-16 218
  分区的作用分区是将一个表的数据按照某种方式，比如按照时间上的月份，分成多个较小的，更容易管理的部分，但是逻辑上仍是一个表。个人理解起来，分区跟性能没有必然关系，分区更多的是从管理的角度出发的。 MySQL分区表对分区字段的限制分区的字段，必须是表上所有的唯一索引（或者主键索引）包含的字段的子集换句话说就是：（所有的）字段必须出现在（所有的）唯一索引或者主键索引的字段中，或者更通俗讲就是，一...
MySQL中的分区表
最新发布
Eucalyptus's blog
07-09 370
MySQL分区技术摘要 MySQL分区技术将大表数据按规则划分为多个物理分区，提升管理效率和查询性能。主要分区类型包括： 范围分区(Range)：按连续值范围划分，适合时间序列数据 列表分区(List)：按离散值列表划分 哈希分区(Hash)：均匀分布数据 键值分区(Key)：基于主键/唯一键自动分区 每种分区类型支持变种(如Range Columns/List Columns)和复合分区(子分区)。分区管理操作包括： 新增/删除分区 重组分区数据 分区维护(重建/检查/优化) 分区优势： 跨磁盘存储大规模
MySQL分区分表（1）
lvjianzhaoa的博客
01-08 4153
博文大纲： 1、为什么要分表？ 2、MySQL分表 3、利用merge存储引擎实现分表 4、MySQL分区 1、为什么要分表？ 数据库数据越来越大，随之而来的是单个表中数据太多。以至于查询速度变慢，而且由于表的锁机制导致应用操作也搜到严重影响，出现了数据库性能瓶颈。mysql中有一种机制是表锁定和行锁定，是为了保证数据的完整性。表锁定表示你们都不能对这张表进行操作，必须等我对表操作完才行。...
mysql 分区表
热门推荐
许诗宇的博客
01-06 1万+
对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成。实现分区的代码实际上是对一组底层表的句柄对象(Handler Object)的封装。对分区表的请求，都会通过句柄对象转化成对存储引擎的接口调用。所以分区对于SQL层来说是一个完全封装底层实现的黑盒子，对应用是透明的，但是从底层的文件系统来看就很容易发现，每一个分区表都有一个使用＃分隔命名的表文件。MySQL实现分区表的方式一对底层表的封装一一意味着索引也是按照分区的子表定义的，而没有全局索引。...
mysql分区表
qq1929892209的博客
04-07 1808
分区表的核心在于合理选择分区键和分区类型。通过分区表，可以显著减少查询时的数据扫描范围，从而提升性能。如果你的数据量持续增长且查询条件固定，分区表是一个简单且有效的解决方案。
MySQL——数据库表.rar
08-04
MySQL是一种广泛使用的开源关系型...以上是对MySQL数据库表的一些基础知识点的概述，实际应用中还有更多高级特性和策略，如复制、主从同步、分区策略、存储引擎选择等，都需要根据具体应用场景进行深入学习和实践。
MySQL大数据表优化——表分区
未来，在于当下的学习。
06-07 820
MySQL大数据表优化表分区
MySQL——RDS下的分区表实践
chongyi7826的博客
07-24 1084
实践背景 项目中有的表空间太大，且行数太多，故决定对一些表进行分库分表。再研究选型方案的时候发现常用的一些分库分表的解决方案对业务代码修改较多，故决定采用MySQL的分区方案。 其实在我个人看来，分区表就是MySQL帮我们实现了底层的分库分表，不需要涉及业务代码的...
Hive——分区表基本操作
d_l_w_d_l_w的博客
09-29 993
创建分区表之前的数据准备： 先创建好dept1.txt dept2.txt dept3.txt 然后存入数据： dept1.txt 10 ACCOUNTING 1700 20 RESEARCH 1800 dept2.txt 30 SALES 1900 40 OPERATIONS 1700 dept3.txt 50 TEST 2000 60 DEV 1900 创建分区表： > create table dept_partition( > deptno int, dname strin
MySQL分区表详解
egegerhn的博客
08-02 3544
通常情况下，同一张表的数据在物理层面都是存放在一起的。随着业务增长，当同一张表的数据量过大时，会带来管理上的不便。而分区特性可以将一张表从物理层面根据一定的规则将数据划分为多个分区，多个分区可以单独管理，甚至存放在不同的磁盘/文件系统上，提升效率。对于应用来说，表依然是一个逻辑整体，但数据库可以针对不同的数据分区独立执行管理操作，不影响其他分区的运行。而数据划分的规则即称为分区函数，数据写入表时，会根据运算结果决定写入哪个分区。...
MySQL分区表（一）
小何的博客
09-22 2837
MySQL分区表
MySQL 分区表
blue0bird的专栏
10-25 298
MySQL 分区表转载自：http://my.oschina.net/jasonultimate/blog/548745?fromerr=GbwoDMxI 以作备忘1 什么是表分区 2 表分区与分表的区别 3 表分区有什么好处 4 分区表的限制因素 5 如何判断当前MySQL是否支持分区 6 MySQL支持的分区类型有哪些 7 Range分区 8 LIST分区 9 Columns分区 10
MySQL（十三）：分区表（ Partitioning Table）
码客
05-27 5492
文章目录1、MySQL 分区简介1.1、什么是分区表1.2、分区表优点1.3、分区表缺点2、MySQL 分区类型2.1、范围分区（RANGE）2.1.1、基于时间间隔的分区方案。2.1.1.1、按 RANGE 对表进行分区2.1.1.2、按 RANGE COLUMNS 对表分区2.2、列表分区（LIST）2.3、HASH分区（HASH）2.3.1、LINEAR HASH 分区2.4、KEY 分区（KEY）2.5、子分区（Subpartitioning）3、MySQL分区如何处理NULL4、MySQL 分区管
MySQL表分区
anhe0516的博客
03-06 234
一、表分区partition 指根据一定的规则，数据库把一个表分解成更多个小的、更容易管理的部分。逻辑上只有一个表或者一个索引，但实际上这个表可能由数个10个物理分区对象组成，每个分区都是一个独立的对象，可以独自处理，可以作为表的一部分进行处理。 注意： 同一个分区表的所有分区必须使用同一个存储引擎，但是对于不同的分区表可以使用不同的存储引擎。 不能只对表数据分区而不对索引分区，...
Mysql分区表------范围分区表（新增、删除、重组、查看）
睡不醒的每天
07-14 5656
当我们删除p1分区以后，p1分区原来的分区区间定义将由他后面的分区定义区间所继承。而且数据将会被删除。– 查询p0和p1分区。根据以上命令可以查询分区的相关数据库信息。表的分区结构中新建的4个分区已新建。由此可见4个分区文件生成。
【MySQL】MySQL分区表详解
BookSea的博客
10-10 562
在我们日常处理海量数据的过程中，如何有效管理和优化数据库一直是一个既重要又具有挑战性的问题。分区表技术就为此提供了一种解决方案，尤其是在使用MySQL这类关系型数据库时。该技术将大型表的数据切割成更易于管理和查询的小块，从而提高了整体数据库操作的性能。
mysql表分区
05-09
### MySQL 表分区的使用方法及示例 #### 1. 范围分区（Range Partitioning） 范围分区是一种基于列值区间划分数据的方法。通过 `PARTITION BY RANGE` 子句定义分区条件。 以下是创建一个按范围分区的表的例子： `...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值