`

MySQL 分区

阅读更多

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)

 

取消分区


 

  • 大小: 10.2 KB
  • 大小: 2.5 KB
  • 大小: 17.5 KB
  • 大小: 22.1 KB
  • 大小: 18.1 KB
  • 大小: 14.5 KB
  • 大小: 12.3 KB
1
1
分享到:
评论

相关推荐

    互联网公司为啥不使用mysql分区表

    "互联网公司为啥不使用mysql分区表" 在互联网公司中,mysql分区表是一个比较少用的技术,这是因为分区表存在一些缺陷和限制,使得互联网公司更多地选择自己分库分表来水平扩展数据库。 首先,分区表的设计需要考虑...

    MySQL分区表自动创建及删除存储过程

    MySQL分区表是一种优化大型数据表查询效率的技术,它将一个大表分成多个逻辑上相连但物理上独立的部分,每个部分称为一个分区。分区可以按照不同的策略进行,如范围、哈希、列表或复合分区。这样做有助于提高数据...

    mysql分区文档

    MySQL 分区是数据库管理系统中的一种优化策略,它将大型表的数据分布在不同的物理存储上,以提高查询性能和便于管理大量数据。分区的本质是将一张大表逻辑上分成多个部分,但用户在操作时仍然将其视为单个表。MySQL ...

    MySQL分区分表方案实践手册

    ### MySQL分区分表方案实践手册知识点详述 #### 一、MySQL分区简介 数据库分区是一项重要的物理数据库设计技术,主要用于优化数据库性能并简化数据管理。MySQL的分区主要包括两种形式:水平分区和垂直分区。 - **...

    mysql分区资源整理

    ### MySQL分区资源整理 #### 一、分区概念与优势 MySQL中的分区技术是数据库优化的重要手段之一,尤其适用于处理大规模数据集。通过分区,数据库能够将一个庞大的表拆分为若干个小部分,使得管理和操作变得更加...

    mysql分区.pdf

    MySQL分区是数据库管理系统MySQL中的一种数据存储管理技术,它允许用户将数据表拆分成多个较小的、更容易管理的片段。这些片段被称为分区。通过这种技术可以提高数据库的性能,便于维护和备份,并且可以对不同分区...

    mysql如何进行分区-mysql分区有哪些方法.docx

    1. **存储能力增强**:相比于单一的磁盘或文件系统分区,MySQL 分区可以存储更多数据。 2. **数据清理便捷**:可以更简单地删除不再需要或过时的数据。 3. **查询优化**:某些查询可以直接在特定分区上执行,减少...

    创建mysql表分区的方法

    【MySQL 表分区详解】 MySQL 表分区是一种高级的数据组织技术,它允许将大型表分成较小、更易管理和处理的部分。这种技术对于处理海量数据,尤其是超过亿级别的数据集非常有效,可以显著提升查询性能和数据管理效率...

    MySQL分区管理工具

    MySQL分区管理工具是一种优化数据库性能的技术,它将大型表分解为更小、更易管理的部分,以提高查询效率和数据管理的便利性。在MySQL中,分区主要基于表的数据,根据预定义的规则将数据分布到不同的物理部分。这样...

    mysql分区

    MySQL分区是一种数据库优化技术,它将大型表分成更小、更易管理的部分,以提高查询性能和数据管理效率。在MySQL中,分区主要是为了解决大数据量时的查询速度问题,通过将数据分散到不同的物理存储块上,使得查询可以...

    zabbix_mysql分区1

    总的来说,Zabbix的MySQL分区优化是一个涉及数据库设计和管理的重要环节。通过合理地利用分区技术,可以有效应对大规模监控数据的挑战,保持Zabbix系统的高效运行。不过,务必根据你的实际情况进行调整,并考虑其他...

    MySQL分区实战

    #### 一、MySQL分区概述与应用场景 在当今互联网行业中,MySQL数据库作为最常用的关系型数据库之一,在数据管理方面扮演着极其重要的角色。随着数据量的不断增长,对数据库性能的要求也日益提高。为了提高查询效率...

    MySQL分区表:万字详解与实践指南

    ### MySQL分区表:万字详解与实践指南 #### 一、引言 在现代数据库管理中,随着数据量的不断增长,如何高效管理和查询数据成为了一个重要的课题。MySQL作为广泛使用的开源关系型数据库管理系统之一,提供了多种强大...

    MySQL分区技术在传统项目中的应用.pdf

    MySQL分区技术是一种将数据库中的表分成多个较小的、更易于管理的部分的策略,它通过将数据分布到不同的物理区域来提升数据库性能。这种技术在处理大量数据的传统项目中尤为重要,可以帮助改善数据库的扩展性、管理...

    MySQL分区分表的设计及实现-收藏备用.pdf

    ### MySQL分区分表的设计及实现 #### 一、引言 随着信息技术的飞速发展,数据量呈现爆炸式增长,海量数据的管理和处理成为了一项挑战。尤其在关系型数据库中,如MySQL这样的系统,当单个表的数据量达到一定规模后...

    MySQL分区和分表技术总结.docx

    MySQL 分区和分表技术总结 MySQL 分区和分表技术是数据库性能优化的重要手段,特别是在大型数据库系统中。以下是 MySQL 分区和分表技术的详细介绍: 什么是分表? 分表是将一个大表按照一定的规则分解成多张具有...

Global site tag (gtag.js) - Google Analytics