欢迎您的光临,本博所发布之文章皆为作者亲测通过,如有错误,欢迎通过各种方式指正。

文摘  MySQL优化之 表分区 讲解

MySQL 本站 1051 0评论

1.分区简介


分区是根据一定的规则,数据库把一个表分解成多个更小的、更容易管理的部分。就访问数据库应用而言,逻辑上就只有一个表或者一个索引,但实际上这个表可能有N个物理分区对象组成,每个分区都是一个独立的对象,可以独立处理,可以作为表的一部分进行处理。分区对应用来说是完全透明的,不影响应用的业务逻辑。

通俗地讲表分区是将一大表,根据条件分割成若干个小表。mysql5.1开始支持数据表分区了。 如:某用户表的记录超过了600万条,那么就可以根据入库日期将表分区,也可以根据所在地将表分区。当然也可根据其他的条件分区。


分区有利于管理非常大的表,它采用分而治之的逻辑,分区引入了分区键的概念,分区键用于根据某个区间值(或者范围值)、特定值列表或者hash函数值执行数据的聚集,让数据根据规则分布在不同的分区中,让一个大对象变成一些小对象。


MySQL分区即可以对数据进行分区也可以对索引进行分区。


2.分区优点


为了改善大型表以及具有各种访问模式的表的可伸缩性,可管理性和提高数据库效率。


分区的一些优点包括:

与单个磁盘或文件系统分区相比,可以存储更多的数据。

对于那些已经失去保存意义的数据,通常可以通过删除与那些数据有关的分区,很容易地删除那些数据。相反地,在某些情况下,添加新数据的过程又可以通过为那些新数据专门增加一个新的分区,来很方便地实现。通常和分区有关的其他优点包括下面列出的这些。MySQL分区中的这些功能目前还没有实现,但是在我们的优先级列表中,具有高的优先级;我们希望在5.1的生产版本中,能包括这些功能。

一些查询可以得到极大的优化,这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内,这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改,所以在第一次配置分区方案时还不曾这么做时,可以重新组织数据,来提高那些常用查询的效率。

涉及到例如SUM()和COUNT()这样聚合函数的查询,可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id;”。通过“并行”,这意味着该查询可以在每个分区上同时进行,最终结果只需通过总计所有分区得到的结果。

通过跨多个磁盘来分散数据查询,来获得更大的查询吞吐量。


MySQL支持大部分的存储引擎创建分区,如MyISAM、InnoDB等;不支持MERGE和CSV等来创建分区。同一个分区表中的所有分区必须是同一个存储引擎。

从MySQL5.1开始引入分区功能,可以如下方式查看是否支持:SHOW VARIABLES LIKE '%partition%'; 或者show plugins。


3.分区类型


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

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

3.HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到

表中的这些行的列值进行计算,这个函数必须产生非负整数值

4.KEY分区:类似于按HASH分区,由MySQL服务器提供其自身的哈希函数

注意:无论哪种分区,要么你分区表上没有主键/唯一键,要么分区表的主键/唯一键都必须包含分区键,也就是说不能使用主键/唯一键字段之外的其它字段分区。


MySQL分区的有限主要包括以下4个方面:

1.和单个磁盘或者文件系统分区相比,可以存储更多数据

2.优化查询。在where子句中包含分区条件时,可以只扫描必要的一个或者多个分区来提高查询效率;同时在涉及sum()和count()这类聚合函数的查询时,可以容易的在每个分区上并行处理,最终只需要汇总所有分区得到的结果

3.对于已经过期或者不需要保存的数据,可以通过删除与这些数据有关的分区来快速删除数据

4.跨多个磁盘来分散数据查询,以获得更大的查询吞吐量

分区和水平分表功能类似,将一个大表的数据分割到多张小表中去,由于查询不需要全表扫描了,只需要扫描某些分区,所以分区能提高查询速度。


水平分表需要用户预先手动显式创建出多张分表(如tbl_user0, tbl_user1, tbl_user2),在物理上实实在在的创建多张表,通过客户端代理(Sharding-JDBC等)或者中间件代理(Mycat等)来实现分表逻辑。


分区是MySQL的一个插件Plugin功能,将一张大表的数据在数据库底层分成多个分区文件(如tbl_user#P#p0.ibd, tbl_user#P#p1.ibd, tbl_user#P#p2.ibd),和水平分表不同的是分区不需要显式的创建“分表”,数据库会自动创建分区文件的,用户看到的只是一张普通的表,其实是对应的是多个分区,这个是对用户是屏蔽的、透明的,在使用上和使用一张表完全一样,不需要借助任何功能来实现。分区是一种逻辑上的水平分表,在物理层面还是一张表。


分区注意事项:

1.如果表中存在primary key或者unique key时,分区的列必须是primary key或者uniquekey的一个组成部分,也就是说,分区函数的列只能从pk或者uk这些key中取子集

2.如果表中不存在任何的primary key或者unique key,则可以指定任何一个列作为分区列

3.5.5版本前的Range、List、Hash分区要求分区键必须是int;MySQL5.5及以上,支持非整型的Range和List分区,即:range columns 和 list columns


1.RANGE分区

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

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

Sql代码:

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子句,该子句提供给所有大于明确指定的最高值的值:

Sql代码:

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位数字的工作代码用来表示普通(店内的)工人,三个数字代码表示办公室和支持人员,四个数字代码表示管理层,你可以使用下面的语句创建该分区表:

Sql代码:

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 语句的一个例子如下所示:

Sql代码:

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中。


注意:

1. RANGE分区的返回值必须为整数。

2. PARTITION p3 VALUES LESS THAN MAXVALUE 是非必需的。


RANGE COLUMNS分区

RANGE COLUMNS是RANGE分区的一种特殊类型,它与RANGE分区的区别如下:

1. RANGE COLUMNS不接受表达式,只能是列名。而RANGE分区则要求分区的对象是整数。

2. RANGE COLUMNS允许多个列,在底层实现上,它比较的是元祖(多个列值组成的列表),而RANGE比较的是标量,即数值的大小。

3. RANGE COLUMNS不限于整数对象,date,datetime,string都可作为分区列。


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源程序中启用,但是,有关工作正在进行中。


2.LIST分区

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

LIST分区通过使用“PARTITION BY LIST(expr)”来实现,其中“expr”是某列值或一个基于某个列值、并返回一个整数值的表达式,然后通过“VALUES IN (value_list)”的方式来定义每个分区,其中“value_list”是一个通过逗号分隔的整数列表。 注释:在MySQL 5.1中,当使用LIST分区时,有可能只能匹配整数列表。

Sql代码:

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”语句:

Sql代码:

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分区的采用上面的方案,下面的查询将失败:

Sql代码: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分区结合起来生成复合的子分区也是可能的。


LIST COLUMNS分区

LIST COLUMNS分区同样是LIST分区的一种特殊类型,它和RANGE COLUMNS分区较为相似,同样不接受表达式,同样支持多个列支持string,date和datetime类型。


3.HASH分区

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

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

Sql代码:

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设置,以便确保所有的行都能合适地插入到分区中。


注意:

1. HASH分区可以不用指定PARTITIONS子句,如上文中的PARTITIONS 4,则默认分区数为4。

2. 不允许只写PARTITIONS,而不指定分区数。

3. 同RANGE分区和LIST分区一样,PARTITION BY HASH (expr)子句中的expr返回的必须是整数值。

4. HASH分区的底层实现其实是基于MOD函数。譬如,对于下表

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

    PARTITION BY HASH( YEAR(col3) )

    PARTITIONS 4;

如果你要插入一个col3为“2005-09-15”的记录,则分区的选择是根据以下值决定的:

MOD(YEAR('2005-09-01'),4)

=  MOD(2005,4)

=  1


LINER HASH分区

MySQL还支持线性哈希功能,它与常规哈希的区别在于,线性哈希功能使用的一个线性的2的幂(powers-of-two)运算法则,而常规哈希使用的是求哈希函数值的模数。线性哈希分区和常规哈希分区在语法上的唯一区别在于,在“PARTITION BY”子句中添加“LINEAR”关键字。

LINEAR HASH分区是HASH分区的一种特殊类型,与HASH分区是基于MOD函数不同的是,它基于的是另外一种算法。

Sql代码:

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分区得到的数据分布相比,各个分区间数据的分布不大可能均衡。


说明:

1. 它的优点是在数据量大的场景,譬如TB级,增加、删除、合并和拆分分区会更快,缺点是,相对于HASH分区,它数据分布不均匀的概率更大。

2. 具体算法,可参考MySQL的官方文档:http://dev.mysql.com/doc/refman/5.6/en/partitioning-linear-hash.html 


4.KSY分区

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

Sql代码:

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)算法得到,而不是通过模数算法。


KEY分区其实跟HASH分区差不多,不同点如下:

1. KEY分区允许多列,而HASH分区只允许一列。

2. 如果在有主键或者唯一键的情况下,key中分区列可不指定,默认为主键或者唯一键,如果没有,则必须显性指定列。

3. KEY分区对象必须为列,而不能是基于列的表达式。

4. KEY分区和HASH分区的算法不一样,PARTITION BY HASH (expr),MOD取值的对象是expr返回的值,而PARTITION BY KEY (column_list),基于的是列的MD5值。

格式如下:

CREATE TABLE k1 (

    id INT NOT NULL PRIMARY KEY,

    name VARCHAR(20)

)

PARTITION BY KEY()

PARTITIONS 2;

在没有主键或者唯一键的情况下,格式如下:

CREATE TABLE tm1 (

    s1 CHAR(32)

)

PARTITION BY KEY(s1)

PARTITIONS 10;


LINEAR KEY分区

同LINEAR HASH分区类似。


格式如下:

CREATE TABLE tk (

    col1 INT NOT NULL,

    col2 CHAR(5),

    col3 DATE

)

PARTITION BY LINEAR KEY (col1)

PARTITIONS 3;


总结:

1. MySQL分区中如果存在主键或唯一键,则分区列必须包含在其中。

2. 对于原生的RANGE分区,LIST分区,HASH分区,分区对象返回的只能是整数值。

3. RANGE COLUMNS,LIST COLUMNS,KEY,LINEAR KEY分区对象只能是列,不能是基于列的表达式。


转载请注明: ITTXX.CN--分享互联网 » MySQL优化之 表分区 讲解

最后更新:2018-11-06 18:04:54

赞 (0) or 分享 ()
游客 发表我的评论   换个身份
取消评论

表情
(0)个小伙伴在吐槽