MySQL 分区
分区是指根据一定的规则,把一个表分解成多个更小更易管理的部分,逻辑上只有一个表或一个索引,
但是实际上该表可能由数个物理分区对象组成,每个分区都是一个独立的对象,每个分区可以独自处理,也可以作为表的一部分处理。分区对应用是完全透明的。
分区的优点体现在以下方面:
- 和单个磁盘或者文件系统相比,可以存储更多数据
- 优化查询,在Where子句中包含分区条件时,可以只扫描必要的一个或者多个分区来提高查询效率;像sum和count这类聚集函数的查询时,可以很容易地在每个分区上并行地处理,最终汇总所有分区的结果
- 对于已经过期的数据,可以通过删除与这些数据有关的分区来快速删除数据
- 跨多个磁盘来分散数据查询,以获得更大的查询吞吐量
MySQL中分区有如下几种类型:
- Range分区:指定区间的范围,把数据分配到不同的分区
- List分区:类似于Range分区,但是List是基于枚举出的值列表分区
- Hash分区:基于给定的分区个数,把数据分配到不同的分区
- Key分区:类似于Hash分区
在MySQL5.1版本中,Range、List、Hash分区都要求分区键必须是INT类型,或者通过表达式返回一个INT类型的数值,也就是说5.1只支持整数分区,唯一的例外就是分区类型为Key分区时,可以使用其它类型的列(BLOB和TEXT除外)作为分区键。
如果一个表有主键/非空唯一键,那么分区键必须使用该主键/非空唯一键;如果没有主键/非空唯一键,那只要该列类型被分区类型允许,就可以使用。
Range分区
Range分区利用取值范围将数据分区,区间连续且区间之间不得有重叠,使用VALUES LESS THAN操作符进行定义:
create table emp ( -> id int not null primary key, -> name varchar(100) , -> hired date ) -> partition by range (id) ( -> partition p0 values less than (5), -> partition p1 values less than (10), -> partition p2 values less than MAXVALUE);
emp表中id在0-4之间的记录将保存在 p0分区,5-9的在p1分区,10 - int的最大值之间的在p2分区。
注意,按上面的语法paitition by range (id)中,括号内的字段必须为整数类型,但是如果是时间类型的怎么办?
1.可以用函数YEAR()和TO_DAYS()来转换,例如:
create table emp ( -> id int(11), -> name varchar(100), -> hired date) -> partition by range (YEAR(hired)) ( -> partition p0 values less than (2017), -> partition p1 values less than (2018), -> partition p2 values less than MAXVALUE);
2.如果MySQL版本是5.5及之后的版本,那么可以用partition by range columns :
create table emp ( -> id int, -> name varchar(100), -> hired date -> ) -> partition by range columns (hired) ( -> partition p0 values less than ('2017-01-01'), -> partition p1 values less than ('2018-01-01'), -> partition p2 values less than MAXVALUE -> ) -> ;
当你要删除一个分区的数据时,只需
ALTER TABLE table_name DROP PARTITION partition_name
即可删除该分区的数据,比delete高效
查询时,MySQL会根据条件判断需要扫描的分区,而不是全表扫描:
List分区
List分区时根据离散的值列表告诉数据库特定的值属于哪个分区,SQL定义语法:
create table good ( -> id int, -> name varchar(255), -> category int -> ) -> partition by list (category) ( -> partition p0 values in (1,3,5), -> partition p1 values in (2,4,6) -> );
按上述定义后,若插入category=7的记录,那么是会报错的,你必须把所有可能值都枚举完,不像range分区有MAXVALUE可以用。
MySQL5.5及其之后的版本中List分区也支持非整数列
Columns分区
Columns分区是MySQL5.5引入的新的分区类型,解决了Range分区和List分区只支持整数列的问题,Columns分区支持的分区列类型为:
- 整数:tinyint、smallint、mediumint、int和bigint
- 日期时间:date和datetime
- 字符串类型:char、varchar、binary和varbinary
Columns分区仅支持一个或多个字段名作为分区键,不再支持表达式作为分区键。
Columns分区支持多列分区:
create table aa ( -> a int, -> b int -> ) -> partition by range columns(a,b) ( -> partition p0 values less than (0,10), -> partition p1 values less than (10,10), -> partition p2 values less than (10,20), -> partition p3 values less than (10,35), -> partition p4 values less than (10,MAXVALUE), -> partition p5 values less than (MAXVALUE,MAXVALUE) -> );
分区键包含多列时,其比较是基于元组的比较,也就是基于字段组的比较,假设有两个元组(A1, B1)和(A2, B2),那么其比较规则为:
(A1 < A2) OR ((A1 == A2) && (B1 < B2))
例如:
Hash分区
Hash分区主要用来分散热点读,确保数据在预先确定个数的分区中尽可能平均分布。对一个表执行Hash分区时,MySQL会对分区键应用一个散列函数,依=以此确定数据应当放在分区中的哪个分区中。
MySQL支持两种Hash分区:
- 常规Hash分区:使用取模算法
- 线性Hash分区(Linear Hash):使用一个线性的2 的幂的运算规则
常规Hash分区语法:
create table emp ( -> id int, -> name varchar(100), -> hired date -> ) engine = innodb -> partition by hash(id) partitions 3;
id为0的将在p0分区,id为1的将在p1分区....以此类推:
常规Hash不适合需要灵活变动分区的需求,因为分区数增加或减少后都得重新计算分区,否则数据就错位了。
为此我们可以用线性Hash分区:
create table emp ( -> id int, -> name varchar(100), -> hired date -> ) engine = innodb -> partition by linear hash(id) partitions 3;
看一下数据分布:
可以看到,数据往哪个分区的计算方法稍有区别:假如有N个分区,要插入的数据的id为Q,那么找到下一个大于等于N的2的幂,记为M,那么分区下标X = Q & (M - 1)。其实说简单点,原来常规Hash是直接对分区数取模,但是线性Hash就不是这样,而是找出最小的大于等于分区数的2的幂,对该数进行取模,比如分区数为6,那么模数为8,要插入的记录的d为10的话,那么分区下标就为 10 % 8 = 2。这样的好处就是,分区数增加或减少不一定要重新计算分区,因为改变的分区数可能并没有使模数发生变化,因此一条记录原来往哪个分区存,还是往哪个分区存,但是如果分区数变化使得模数变化(比如分区数从7变成9,模数从8变为16),那么是依然需要重新计算分区的。
线性hash分区的优点是,在分区维护(增加、删除、合并和拆分分区)时,MySQL能够处理得更加迅速;缺点是,对比常规Hash分区,线性Hash分区各个分区数据分布并不是很均匀。
Key分区
Key分区类似于Hash分区,只不过Hash分区允许使用用户自定义得表达式,而Key分区不允许,需要使用MySQL服务器提供的Hash函数;同时Hash分区只支持整数分区,而Key分区支持除了Blob和Text类型外的所有类型作为分区键。在有主键或者非空唯一键的情况下,创建Key分区时可以不指定分区键,MySQL会默认使用主键作为分区键,若没有主键则使用非空唯一键作为分区键。
create table emp ( -> id int, -> name varchar(100), -> hired date -> ) engine = innodb -> partition by key (id) partitions 4;
对于所有分区,MySQL都允许使用Null值,再Range分区中,Null值被当作最小值来处理;再List分区中,Null值必须被枚举出来,否则就不允许使用插入Null值;再Hash/Key分区中,Null值被当作零值来处理。
分区管理命令
MySQL提供了添加、删除、重定义、合并和拆分分区的命令,这些操作都可以用ALTER TABLE命令来实现
Range分区
create table emp ( -> id int, -> name varchar(100), -> hired date -> ) -> partition by range columns (hired) ( -> partition p0 values less than ('2017-01-01'), -> partition p1 values less than ('2018-01-01'), -> partition p2 values less than ('2019-01-01') -> );
添加分区:
mysql> alter table emp add partition (partition p3 values less than ('2020-01-01'));
添加分区时不能从中间添加,只能从最大的那个分区后面添加:
mysql> alter table emp add partition (partition p4 values less than ('2019-07-01')); ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition
拆分p0为p00和p01:
mysql> alter table emp reorganize partition p0 into ( -> partition p00 values less than ('2016-07-01'), -> partition p01 values less than ('2017-01-01') -> );
合并p00和p01为p0:
mysql> alter table emp reorganize partition p00,p01 into ( -> partition p0 values less than ('2017-01-01') -> );
删除p2分区:
mysql> alter table emp drop partition p2;
List分区
create table good ( id int, name varchar(255), category int ) partition by list (category) ( partition p0 values in (1,2), partition p1 values in (3,4) );
添加分区:
mysql> alter table good add partition (partition p2 values in (5,6));
拆分分区p0为p00和p01:
mysql> alter table good reorganize partition p0 into ( -> partition p00 values in (1), -> partition p01 values in (2) -> );
合并分区:
mysql> alter table good reorganize partition p00,p01 into ( -> partition p0 values in (1,2) -> );
删除分区:
mysql> alter table good drop partition p2;
Hash分区
mysql> create table emp ( -> id int, -> name varchar(100), -> hired date -> ) engine = innodb -> partition by hash (id) partitions 3;
减少分区的数量:
mysql> alter table emp coalesce partition 2;
增加分区的数量
mysql> alter table emp add partition partitions 4; # 该数字为增加的数量而不是增加后的数量
Key分区
key分区和Hash一致.
无论是什么分区,都可以通过information_schema.partitions表来查看其详细信息:
mysql> desc partitions; +-------------------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------------------+---------------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(512) | NO | | | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | PARTITION_NAME | varchar(64) | YES | | NULL | | | SUBPARTITION_NAME | varchar(64) | YES | | NULL | | | PARTITION_ORDINAL_POSITION | bigint(21) unsigned | YES | | NULL | | | SUBPARTITION_ORDINAL_POSITION | bigint(21) unsigned | YES | | NULL | | | PARTITION_METHOD | varchar(18) | YES | | NULL | | | SUBPARTITION_METHOD | varchar(12) | YES | | NULL | | | PARTITION_EXPRESSION | longtext | YES | | NULL | | | SUBPARTITION_EXPRESSION | longtext | YES | | NULL | | | PARTITION_DESCRIPTION | longtext | YES | | NULL | | | TABLE_ROWS | bigint(21) unsigned | NO | | 0 | | | AVG_ROW_LENGTH | bigint(21) unsigned | NO | | 0 | | | DATA_LENGTH | bigint(21) unsigned | NO | | 0 | | | MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | | INDEX_LENGTH | bigint(21) unsigned | NO | | 0 | | | DATA_FREE | bigint(21) unsigned | NO | | 0 | | | CREATE_TIME | datetime | YES | | NULL | | | UPDATE_TIME | datetime | YES | | NULL | | | CHECK_TIME | datetime | YES | | NULL | | | CHECKSUM | bigint(21) unsigned | YES | | NULL | | | PARTITION_COMMENT | varchar(80) | NO | | | | | NODEGROUP | varchar(12) | NO | | | | | TABLESPACE_NAME | varchar(64) | YES | | NULL | | +-------------------------------+---------------------+------+-----+---------+-------+ 25 rows in set (0.03 sec)
取消分区
相关推荐
"互联网公司为啥不使用mysql分区表" 在互联网公司中,mysql分区表是一个比较少用的技术,这是因为分区表存在一些缺陷和限制,使得互联网公司更多地选择自己分库分表来水平扩展数据库。 首先,分区表的设计需要考虑...
MySQL分区表是一种优化大型数据表查询效率的技术,它将一个大表分成多个逻辑上相连但物理上独立的部分,每个部分称为一个分区。分区可以按照不同的策略进行,如范围、哈希、列表或复合分区。这样做有助于提高数据...
MySQL 分区是数据库管理系统中的一种优化策略,它将大型表的数据分布在不同的物理存储上,以提高查询性能和便于管理大量数据。分区的本质是将一张大表逻辑上分成多个部分,但用户在操作时仍然将其视为单个表。MySQL ...
### MySQL分区分表方案实践手册知识点详述 #### 一、MySQL分区简介 数据库分区是一项重要的物理数据库设计技术,主要用于优化数据库性能并简化数据管理。MySQL的分区主要包括两种形式:水平分区和垂直分区。 - **...
### MySQL分区资源整理 #### 一、分区概念与优势 MySQL中的分区技术是数据库优化的重要手段之一,尤其适用于处理大规模数据集。通过分区,数据库能够将一个庞大的表拆分为若干个小部分,使得管理和操作变得更加...
MySQL分区是数据库管理系统MySQL中的一种数据存储管理技术,它允许用户将数据表拆分成多个较小的、更容易管理的片段。这些片段被称为分区。通过这种技术可以提高数据库的性能,便于维护和备份,并且可以对不同分区...
1. **存储能力增强**:相比于单一的磁盘或文件系统分区,MySQL 分区可以存储更多数据。 2. **数据清理便捷**:可以更简单地删除不再需要或过时的数据。 3. **查询优化**:某些查询可以直接在特定分区上执行,减少...
【MySQL 表分区详解】 MySQL 表分区是一种高级的数据组织技术,它允许将大型表分成较小、更易管理和处理的部分。这种技术对于处理海量数据,尤其是超过亿级别的数据集非常有效,可以显著提升查询性能和数据管理效率...
MySQL分区管理工具是一种优化数据库性能的技术,它将大型表分解为更小、更易管理的部分,以提高查询效率和数据管理的便利性。在MySQL中,分区主要基于表的数据,根据预定义的规则将数据分布到不同的物理部分。这样...
MySQL分区是一种数据库优化技术,它将大型表分成更小、更易管理的部分,以提高查询性能和数据管理效率。在MySQL中,分区主要是为了解决大数据量时的查询速度问题,通过将数据分散到不同的物理存储块上,使得查询可以...
总的来说,Zabbix的MySQL分区优化是一个涉及数据库设计和管理的重要环节。通过合理地利用分区技术,可以有效应对大规模监控数据的挑战,保持Zabbix系统的高效运行。不过,务必根据你的实际情况进行调整,并考虑其他...
#### 一、MySQL分区概述与应用场景 在当今互联网行业中,MySQL数据库作为最常用的关系型数据库之一,在数据管理方面扮演着极其重要的角色。随着数据量的不断增长,对数据库性能的要求也日益提高。为了提高查询效率...
### MySQL分区表:万字详解与实践指南 #### 一、引言 在现代数据库管理中,随着数据量的不断增长,如何高效管理和查询数据成为了一个重要的课题。MySQL作为广泛使用的开源关系型数据库管理系统之一,提供了多种强大...
MySQL分区技术是一种将数据库中的表分成多个较小的、更易于管理的部分的策略,它通过将数据分布到不同的物理区域来提升数据库性能。这种技术在处理大量数据的传统项目中尤为重要,可以帮助改善数据库的扩展性、管理...
### MySQL分区分表的设计及实现 #### 一、引言 随着信息技术的飞速发展,数据量呈现爆炸式增长,海量数据的管理和处理成为了一项挑战。尤其在关系型数据库中,如MySQL这样的系统,当单个表的数据量达到一定规模后...
MySQL 分区和分表技术总结 MySQL 分区和分表技术是数据库性能优化的重要手段,特别是在大型数据库系统中。以下是 MySQL 分区和分表技术的详细介绍: 什么是分表? 分表是将一个大表按照一定的规则分解成多张具有...