mysql 分区类型详解

RANGE分区

基于属于一个给定连续区间的列值,把多行分配给分区。

这些区间要连续且不能相互重叠,使用VALUES LESS THAN操作符来进行定义。以下是实例。

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)

);

按照这种分区方案,在商店1到5工作的雇员相对应的所有行被保存在分区P0中,商店6到10的雇员保存在P1中,依次类推。注意,每个分区都是按顺序进行定义,从最低到最高。这是PARTITION BY RANGE 语法的要求;在这点上,它类似于C或Java中的“switch … case”语句。

对于包含数据(72, ‘Michael’, ‘Widenius’, ‘1998-06-25′, NULL, 13)的一个新行,可以很容易地确定它将插入到p2分区中,但是如果增加了一个编号为第21的商店,将会发生什么呢?在这种方案下,由于没有规则把store_id大于20的商店包含在内,服务器将不知道把该行保存在何处,将会导致错误。 要避免这种错误,可以通过在CREATE TABLE语句中使用一个“catchall” VALUES LESS THAN子句,该子句提供给所有大于明确指定的最高值的值:

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 MAXVALUE

);

MAXVALUE 表示最大的可能的整数值。现在,store_id 列值大于或等于16(定义了的最高值)的所有行都将保存在分区p3中。在将来的某个时候,当商店数已经增长到25, 30, 或更多 ,可以使用ALTER TABLE语句为商店21-25, 26-30,等等增加新的分区。

在几乎一样的结构中,你还可以基于雇员的工作代码来分割表,也就是说,基于job_code 列值的连续区间。例如——假定2位数字的工作代码用来表示普通(店内的)工人,三个数字代码表示办公室和支持人员,四个数字代码表示管理层,你可以使用下面的语句创建该分区表:

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 (job_code) (

PARTITION p0 VALUES LESS THAN (100),

PARTITION p1 VALUES LESS THAN (1000),

PARTITION p2 VALUES LESS THAN (10000)

);

在这个例子中, 店内工人相关的所有行将保存在分区p0中,办公室和支持人员相关的所有行保存在分区p1中,管理层相关的所有行保存在分区p2中。

在VALUES LESS THAN 子句中使用一个表达式也是可能的。这里最值得注意的限制是MySQL 必须能够计算表达式的返回值作为LESS THAN (<)比较的一部分;因此,表达式的值不能为NULL 。由于这个原因,雇员表的hired, separated, job_code,和store_id列已经被定义为非空(NOT NULL)。

除了可以根据商店编号分割表数据外,你还可以使用一个基于两个DATE (日期)中的一个的表达式来分割表数据。例如,假定你想基于每个雇员离开公司的年份来分割表,也就是说,YEAR(separated)的值。实现这种分区模式的CREATE TABLE 语句的一个例子如下所示:

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,

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

);

在这个方案中,在1991年前雇佣的所有雇员的记录保存在分区p0中,1991年到1995年期间雇佣的所有雇员的记录保存在分区p1中, 1996年到2000年期间雇佣的所有雇员的记录保存在分区p2中,2000年后雇佣的所有工人的信息保存在p3中。

RANGE分区在如下场合特别有用:

1)、 当需要删除一个分区上的“旧的”数据时,只删除分区即可。如果你使用上面最近的那个例子给出的分区方案,你只需简单地使用 “ALTER TABLE employees DROP PARTITION p0;”便可删除所有在1991年前就已经停止工作的雇员相对应的所有行。对于有大量行的表,这比运行一个如“DELETE FROM employees WHERE YEAR (separated) <= 1990;”这样的一个DELETE查询要有效得多。

2)、想要使用一个包含有日期或时间值,或包含有从一些其他级数开始增长的值的列。

3)、经常运行直接依赖于用于分割表的列的查询。例如,当执行一个如“SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id;”这样的查询时,MySQL可以很迅速地确定只有分区p2需要扫描,这是因为余下的分区不可能包含有符合该WHERE子句的任何记录。

注意:这种优化还没有在MySQL 5.1源程序中启用

LIST分区

类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

LIST分区通过使用“PARTITION BY LIST(expr)”来实现,其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式,然后通过“VALUES IN (value_list)”的方式来定义每个分区,其中“value_list”是一个通过逗号分隔的整数列表。

注释:在MySQL 5.1中,当使用LIST分区时,有可能只能匹配整数列表。

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,

store_id INT

);

假定有20个音像店,分布在4个有经销权的地区,如下表所示:

====================

地区 商店ID 号

————————————

北区 3, 5, 6, 9, 17

东区 1, 2, 10, 11, 19, 20

西区 4, 12, 13, 14, 18

中心区 7, 8, 15, 16

====================

要按照属于同一个地区商店的行保存在同一个分区中的方式来分割表,可以使用下面的“CREATE TABLE”语句:

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,

store_id INT

)

PARTITION BY LIST(store_id)

PARTITION pNorth VALUES IN (3,5,6,9,17),

PARTITION pEast VALUES IN (1,2,10,11,19,20),

PARTITION pWest VALUES IN (4,12,13,14,18),

PARTITION pCentral VALUES IN (7,8,15,16)

);

这使得在表中增加或删除指定地区的雇员记录变得容易起来。例如,假定西区的所有音像店都卖给了其他公司。那么与在西区音像店工作雇员相关的所有记录(行)可以使用“ALTER TABLE employees DROP PARTITION pWest;”来进行删除,它与具有同样作用的DELETE (删除)查询“DELETE query DELETE FROM employees WHERE store_id IN (4,12,13,14,18);”比起来,要有效得多。

【要点】如果试图插入列值(或分区表达式的返回值)不在分区值列表中的一行时,那么“INSERT”查询将失败并报错。例如,假定LIST分区的采用上面的方案,下面的查询将失败:

INSERT INTO employees VALUES(224, ‘Linus’, ‘Torvalds’, ‘2002-05-01′, ‘2004-10-12′, 42, 21);

这是因为“store_id”列值21不能在用于定义分区pNorth, pEast, pWest,或pCentral的值列表中找到。要重点注意的是,LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。

LIST分区除了能和RANGE分区结合起来生成一个复合的子分区,与HASH和KEY分区结合起来生成复合的子分区也是可能的。

HASH分区

基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

要使用HASH分区来分割一个表,要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句,其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL 整型的一列的名字。此外,你很可能需要在后面再添加一个“PARTITIONS num”子句,其中num 是一个非负的整数,它表示表将要被分割成分区的数量。

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,

store_id INT

)

PARTITION BY HASH(store_id)

PARTITIONS 4;

如果没有包括一个PARTITIONS子句,那么分区的数量将默认为1。 例外: 对于NDB Cluster(簇)表,默认的分区数量将与簇数据节点的数量相同,

这种修正可能是考虑任何MAX_ROWS 设置,以便确保所有的行都能合适地插入到分区中。

LINER HASH

MySQL还支持线性哈希功能,它与常规哈希的区别在于,线性哈希功能使用的一个线性的2的幂(powers-of-two)运算法则,而常规 哈希使用的是求哈希函数值的模数。

线性哈希分区和常规哈希分区在语法上的唯一区别在于,在“PARTITION BY” 子句中添加“LINEAR”关键字。

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,

store_id INT

)

PARTITION BY LINEAR HASH(YEAR(hired))

PARTITIONS 4;

下面的内容是详细的算法,可以只做了解

=======================================

假设一个表达式expr, 当使用线性哈希功能时,记录将要保存到的分区是num 个分区中的分区N,其中N是根据下面的算法得到:

1. 找到下一个大于num的2的幂,我们把这个值称为V ,它可以通过下面的公式得到:

2. V = POWER(2, CEILING(LOG(2, num)))

(例如,假定num是13。那么LOG(2,13)就是3.7004397181411。 CEILING(3.7004397181411)就是4,则V = POWER(2,4), 即等于16)。

3. 设置 N = F(column_list) & (V – 1).

4. 当 N >= num:

设置 V = CEIL(V / 2)

设置 N = N & (V – 1)

例如,假设表t1,使用线性哈希分区且有4个分区,是通过下面的语句创建的:

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)

PARTITION BY LINEAR HASH( YEAR(col3) )

PARTITIONS 6;

现在假设要插入两行记录到表t1中,其中一条记录col3列值为’2003-04-14’,另一条记录col3列值为’1998-10-19’。第一条记录将要保存到的分区确定如下:

V = POWER(2, CEILING(LOG(2,7))) = 8

N = YEAR(‘2003-04-14′) & (8 – 1)

= 2003 & 7

= 3

(3 >= 6 为假(FALSE): 记录将被保存到#3号分区中)

第二条记录将要保存到的分区序号计算如下:

V = 8

N = YEAR(‘1998-10-19′) & (8-1)

= 1998 & 7

= 6

(6 >= 4 为真(TRUE): 还需要附加的步骤)

N = 6 & CEILING(5 / 2)

= 6 & 3

= 2

(2 >= 4 为假(FALSE): 记录将被保存到#2分区中)

按照线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷,有利于处理含有极其大量(1000吉)数据的表。它的缺点在于,与使用

常规HASH分区得到的数据分布相比,各个分区间数据的分布不大可能均衡。

KEY分区

类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

CREATE TABLE tk (

col1 INT NOT NULL,

col2 CHAR(5),

col3 DATE

)

PARTITION BY LINEAR KEY (col1)

PARTITIONS 3;

在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用,分区的编号是通过2的幂(powers-of-two)算法得到,而不是通过模数算法。

时间: 2025-01-30 00:31:13

mysql 分区类型详解的相关文章

MySQL表的四种分区类型详解

一.什么是表分区 通俗地讲表分区是将一大表,根据条件分割成若干个小表.mysql5.1开始支持数据表分区了. 如:某用户表的记录超过了600万条,那么就可以根据入库日期将表分区,也可以根据所在地将表分区.当然也可根据其他的条件分区. 二.为什么要对表进行分区 为了改善大型表以及具有各种访问模式的表的可伸缩性,可管理性和提高数据库效率. 分区的一些优点包括: 与单个磁盘或文件系统分区相比,可以存储更多的数据. 对于那些已经失去保存意义的数据,通常可以通过删除与那些数据有关的分区,很容易地删除那些数

MySQL字段类型详解

MySQL支持大量的列类型,它可以被分为3类:数字类型.日期和时间类型以及字符串(字符)类型.本节首先给出可用类型的一个概述,并且总结每个列类型的存储需求,然后提供每个类中的类型性质的更详细的描述. 概述有意简化,更详细的说明应该考虑到有关特定列类型的附加信息,例如你能为其指定值的允许格式.由MySQL支持的列类型列在下面.下列代码字母用于描述中:M指出最大的显示尺寸.最大的合法的显示尺寸是 255 .D适用于浮点类型并且指出跟随在十进制小数点后的数码的数量.最大可能的值是30,但是应该不大于M

MySQL表类型详解

MySQL为我们提供了很多表类型供选择,有MyISAM.ISAM.HEAP.BerkeleyDB.InnoDB,MERGE表类型,萝卜白菜各有所爱是不假,可是真正选择何种表类型还是要看业务需要啊,每一种表类型都有其自己的属性和优点.下面我们来简单的讨论一下. MyISAM表类型: (1)MyISAM表(TYPE=MYISAM)是ISAM类型的一种延伸,具有很多优化和增强的特性. (2)是MySQL的默认表类型. (3)MyISAM优化了压缩比例和速度,并且可以很方便的在不同的操作系统和平台之间进

MySQL binlog中的事件类型详解_Mysql

MySQL binlog记录的所有操作实际上都有对应的事件类型的,譬如STATEMENT格式中的DML操作对应的是QUERY_EVENT类型,ROW格式下的DML操作对应的是ROWS_EVENT类型. 首先,看看源码中定义的事件类型 源码位置:mysql-5.7.14/libbinlogevents/include/binlog_event.h enum Log_event_type { /** Every time you update this enum (when you add a ty

MySQL EXPLAIN命令详解学习(执行计划)

MySQL EXPLAIN命令详解学习(执行计划) MySQL EXPLAIN 命令详解 MySQL的EXPLAIN命令用于SQL语句的查询执行计划(QEP).这条命令的输出结果能够让我们了解MySQL 优化器是如何执行 SQL 语句的.这条命令并没有提供任何调整建议,但它能够提供重要的信息帮助你做出调优决策. 1 语法 MySQL 的EXPLAIN 语法可以运行在SELECT 语句或者特定表上.如果作用在表上,那么此命令等同于DESC 表命令.UPDATE 和DELETE 命令也需要进行性能改

TIMESTAMP列类型详解(怎样设列的默认值为Now())

详解 TIMESTAMP列类型详解(怎样设列的默认值为Now()) MySQL目前不支持列的Default 为函数的形式,如达到你某列的默认值为当前更新日期与时间的功能,你可以使用TIMESTAMP列类型下面就详细说明TIMESTAMP列类型 TIMESTAMP列类型TIMESTAMP值可以从1970的某时的开始一直到2037年,精度为一秒,其值作为数字显示.TIMESTAMP值显示尺寸的格式如下表所示::+---------------+----------------+| 列类型      

MySQL日志文件详解

  这篇文章主要介绍了MySQL日志文件详解,本文分别讲解了错误日志.二进制日志.通用查询日志.慢查询日志.Innodb的在线redo日志.更新日志等日志类型和作用介绍,需要的朋友可以参考下 概述 日志文件是MySQL数据库的重要组成部分.MySQL有几种不同的日志文件,通常包括错误日志文件,二进制日志,通用日志,慢查询日志,等等.这些日志可以帮助我们定位mysqld内部发生的事件,数据库性能故障,记录数据的变更历史,用户恢复数据库等等.本文主要描述MySQL的各种日志文件. MySQL日志文件

磁盘分区对齐详解与配置 – Linux篇

磁盘分区对齐详解与配置 – Linux篇 介绍 许多系统管理员可能不曾听过"磁盘分区对齐"之说,甚至一些有经验的存储管理员对分区对齐也不甚了解.磁盘分区不对齐现象是什么,为什么会造成比较严重的性能下降?相反,配置正确的分区起始位置(Offset)设置会使存储系统发挥更大的性能潜力.文章就磁盘分区对齐进行的介绍,并且给出了在Windows平台上如何配置的方法. 什么是磁盘分区对齐(Disk Alignment.Partition Alignment) Windows的磁盘有一种结构叫做M

MySQL字符串函数详解(推荐)_Mysql

一.ASCII ASCII(str) 返回字符串str的最左面字符的ASCII代码值.如果str是空字符串,返回0.如果str是NULL,返回NULL. 二.ORD ORD(str) 如果字符串str最左面字符是一个多字节字符,通过以格式((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code...]返回字符的ASCII代码值来返回多字节字符代码.如果最左面的字符不是一个多字节字符.返回与A