mysql分区技术和理论

作者:吴泽鑫 分类: Mysql 发布于:2016-4-6 22:01 ė3916次浏览 60条评论

当一个表的数据很庞大,成百上千万时,很多人采用水平分区的方式。但是水平分区技术是将一个表拆分成多个表,比较常用的方式是将表中的记录按照某种hash算法进行拆分,简单的拆分方法如取模算法方式。同样,这种拆分方法也必须对前端的应用程序中的sql语句进行修改方可使用。而且对于一个sql,它可能会修改2个或者多个表,那么你必须写成多个sql语句才能完成一个业务的事务,使得程序的判断逻辑越来越复杂,这样会导致程序的维护代价高,也就失去了采用数据库的优势。因此,分区技术可以有力的避免如上的弊端,成为解决海量数据存储的有力方案。

mysql的分区技术不同于之前的分表技术,它于水平分表有点类似,但是它是在逻辑层进行水平分表,对于应用程序而言,它还是一张表,Mysql5.1有五种分区类型,常用如下4种:

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

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

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

  • KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且mysql服务器提供其自身的HASH函数。


RANGE分区

例1: 假定你创建了一个如下的表, 该表保存有20家音像店的职员记录, 这20家音像店的编号从1到20。如果你想将其分成4个小分区,那么你可以采用RANGE分区,创建的数据库表如下:

CREATE TABLE EMPLOYESS(
    ID INT NOT NULL,
    FNAME VARCHAR(30),
    LNAME VARCHAR(30),
    HIRED DATE NOT NULL DEFAULT '1970-01-01',
    SEPARATED DATE NOT NULL DEFAULT '9999012-31',
    JOB_CODE INT NOT NULL,
    STORE_ID INT NOT NULL
)
PARTITION BY RANGE (STORE_ID)(
    PARTITION P0 VALUES LESS THAN(6), #当STORE_ID小与6,数据在p0分区
    PARTITION P1 VALUES LESS THAN(11), #当STORE_ID小与6,数据在p1分区
    PARTITION P2 VALUES LESS THAN(16), #当STORE_ID小与6,数据在p2分区
    PARTITION P3 VALUES LESS THAN(21) #当STORE_ID小与6,数据在p3分区
)

例2:假定你创建了一个如下的表,该表保存有20家音像店的职员记录,这20家音像店的编号从1到20。你想把不同时期离职的员工进行分别存储,那么你可以将日期字段separated(即离职时间)作为一个key,创建的sql语句如下:

CREATE TABLE EMPLOYESS(
    ID INT NOT NULL,
    FNAME VARCHAR(30),
    LNAME VARCHAR(30),
    HIRED DATE NOT NULL DEFAULT '1970-01-01',
    SEPARATED DATE NOT NULL DEFAULT '9999012-31',
    JOB_CODE INT NOT NULL,
    STORE_ID INT NOT NULL
)
PARTITION BY RANGE (year(SEPARATED))(
    PARTITION P0 VALUES LESS THAN(1991), #当年份小与1991,数据在p0分区
    PARTITION P1 VALUES LESS THAN(1996), #当年份小与1996,数据在p1分区
    PARTITION P2 VALUES LESS THAN(2001), #当年份小与2001,数据在p2分区
    PARTITION P3 VALUES LESS THAN MAXVALUE #所有都不小于的时候,数据在p3分区,类似于default
)

这样你就可以对一个日期类型的字段调用mysql的日期函数year()转化为一种整型类型,从而可以作为range分区的key。这时候你可以看到,分区后的无力文件是相对独立的

blob.png


分区之后,会有10个文件产生,employess.frm是表结构,employess.par代表它是一个分区表,employess#p#p0.MYD到employess#P#P3.MYI,是分区文件,MYD格式是表的数据,MYI是表的索引。

LIST分区

list分区与range分区有类似的地方,可以理解成是range的一个特例,举个与例1类似的例子如下:

例3:假定你创建了一个如下的一个表,该表保存有20家音像店的职员记录,这20家音像店的编号从1到20。而这20个音像店,分布在4个有经销权的地区,如下表所示:

blob.png

那么你可以采用如下的list分区语句创建数据表:

CREATE TABLE EMPLOYESS(
    ID INT NOT NULL,
    FNAME VARCHAR(30),
    LNAME VARCHAR(30),
    HIRED DATE NOT NULL DEFAULT '1970-01-01',
    SEPARATED DATE NOT NULL DEFAULT '9999012-31',
    JOB_CODE INT NOT NULL,
    STORE_ID INT NOT NULL
)
PARTITION BY LIST(STORE_ID)(
    PARTITION pNorth VALUES IN(3,5,6,9,17), #当store_id在3,5,6,9,17时,数据在pNorth分区
    PARTITION PEast VALUES IN(1,2,10,11,19,20), #当store_id在1,2,10,11,19,20,数据在PEast分区
    PARTITION PWest VALUES IN(4,12,13,14,18), #当store_id在4,12,13,14,18,数据在PWest分区
    PARTITION PCentral VALUES IN(7,8,15,16) #当store_id在7,8,15,16,数据在PCentral分区
)

同样,它在物理文件上也会标识不同的分区:

blob.png

HASH分区

hash分区主要用来确保数据在预先确定数目的分区种平均分布。它可以基于用户定义的表达式返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。

例4:假定你创建了一个如下的一个表,该表保存有20家音像店的职员记录,这20家音像店的编号从1到20。你想把不同时期加入的员工进行分别存储,那么你可以将日期字段hired(即入职时间)作为一个key,创建的sql语句如下:

CREATE TABLE EMPLOYESS(
    ID INT NOT NULL,
    FNAME VARCHAR(30),
    LNAME VARCHAR(30),
    HIRED DATE NOT NULL DEFAULT '1970-01-01',
    SEPARATED DATE NOT NULL DEFAULT '9999012-31',
    JOB_CODE INT NOT NULL,
    STORE_ID INT NOT NULL
)
PARTITION BY HASH(YEAR(HIRED))
PARTITIONS 4 #将数据平均随机分配到4个分区中

KEY分区

与hash分区类似,但它的key可以不是整型类型,如字符串等类型的字段。mysql使用md5函数来实现key分区;对于使用其他存储引擎的表,服务器使用其自己内部的哈希函数,这些函数是基于与PASSWORD()一样的运算法则。

几种分区技术的对比

blob.png

本文出自 Jensen-吴泽鑫的博客,转载时请注明出处及相应链接:http://www.wuzexin.cn/post-61.html

发表评论

电子邮件地址不会被公开。必填项已用*标注


Ɣ回顶部