MySQL之HASH分区和LINEAR HASH分区初识

  • Post author:
  • Post category:mysql


上一篇:

MySQL之LIST和LIST COLUMNS分区初探

HASH分区

对于HASH分区,需要关注两点,第一:用于HASH计算的一个或者多个列值或者基于一个或者多个列值的表达式expr,第二:表的分区数,也就是表应该被分成几个分区。对于第一点MySQL使用PARTITION BY HASH (expr)语句来定义,HASH用于计算expr的哈希值,expr是一个或者多个整数列或者是一个返回整数的表达式,对于第二点使用PARTITIONS num语句来指定分区数,num表示分区数,是一个正整数。例如,对于employees表,按照整数类型的字段store_id的值分成4个分区,可如下创建HASH分区表:

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子句,employees表默认只有1个分区,即如下两种方式是等价的:

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)
;
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 1;

如果使用了PARTITIONS,而没有指定分区数,是存在语法错误的。

可以在非整数类型字段,但返回整数的表达式上使用HASH分区,例如:

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( YEAR(hired) )
PARTITIONS 4;

hired是DATE类型,但YEAR()函数返回一个整数。

什么样的expr表达式好呢?最有效的函数是对表的单个列进行操作,函数计算后的值与列值一致地增加或减少,就是说当列的值增加时,expr的值也增加,或者当列的值增加值,expr的值一直再减小,这样的好处是,允许对分区范围进行“剪枝”。也就是说,表达式随着它所基于的列的值变化得越紧密,MySQL就能越有效地使用表达式进行散列分区。例如,假设date_col是一个表的DATE类型的列,


TO_DAYS(date_col)


表达式就比


YEAR(date_col)


表达式有较好的散列分区。

从数学角度来说,如果将列的值看成是坐标轴的x,表达式的值看成是坐标轴的y,它们如果形成一条直线(

y=


c


x,c不为0

),这样的表达式适合用于hash。

理论上来说,对于包含多个列值的表达式也可以进行修剪,但是确定哪一个这样的表达式是合适的是相当困难和耗时的。因此,不特别推荐使用涉及多个列的散列表达式 。

PARTITION BY HASH (expr)是如何计算每条记录的分区的呢?对于给定的一条数据行,先计算数据行的列对应的表达式expr的值,然后使用表达式的值对分区数num进行取模运行,即n=MOD(expr, num),得到的值n就是数据行的分区号,最后该数据行就存储到分区号为n的分区中。下面举个例子,假设表t1有4个分区,HASH分区建表语句如下:

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY HASH( YEAR(col3) )
PARTITIONS 4;

向HASH分区表t1插入如下一条记录:

insert into t1 values(1,'2','2005-09-15');

如下方式可计算该条记录的分区号:

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

可以执行如下语句进行验证:

select PARTITION_NAME,TABLE_ROWS 
from information_schema.`PARTITIONS` where `TABLE_NAME`='t1';

如图:

该数据行存储在分区p1中。

LINEAR HASH分区

与HASH分区不同,LINEAR HASH分区使用了线性2的幂(linear powers-of-two)算法。但LINEAR HASH分区创建的语法与HASH分区确十分相似,如下:

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;

仅仅是在HASH关键词前添加LINEAR关键词。那么对于LINEAR HASH分区,数据行是如何存储到对应分区的呢?下面我们详细解释线性2的幂算法。假设给定表达式expr和分区数num,

(1)找到比num大的最小2的幂,假设num=1,那么比1大的最小2的幂是2,即
2^1
,假设num=13,比num大的最小2的是16,即
2^4
,可以通过如下公式计算:

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

如图:

LOG是对数函数,CEILING(x)是取比x大的最小整数。

(2)


N


=

expr

& (


V


– 1)

列值对应的表达式expr的值与(V-1)求“与”运算,得到分区号N;

(3)如果上一步计算得到的分区号小于num,数据行存储到分区号N的分区中,如果分区号大于或者等于num,继续计算

  • 设置


    V


    =


    V


    / 2


  • 设置

    N


    =


    N


    & (


    V


    – 1)

循环执行(3)。

下面举例说明该算法:

创建一个分区数为6的LINEAR HASH分区表t1,如下:

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY LINEAR HASH( YEAR(col3) )
PARTITIONS 6;

1、向分区表中插入如下数据:

insert into t1 VALUES(1,'1','2003-04-14');

下面使用线性2的幂算法,计算该数据的分区号

(1)分区数num=6,计算出V值:

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

(2) 计算出分区号N

N = YEAR('2003-04-14') & (8 - 1)
   = 2003 & 7
   = 3

(3) 因为N<6,所以数据行存储到分区号3中,即p3分区(分区号从0开始计算),可以执行如下sql进行验证

select PARTITION_NAME,TABLE_ROWS 
from information_schema.`PARTITIONS` where `TABLE_NAME`='t1';

如图:

2、向分区中再插入如下一条数据:

insert into t1 VALUES(2,'2','1998-10-19');

(1)计算V值

V = 8

(2)计算分区号N

N = YEAR('1998-10-19') & (8 - 1)
  = 1998 & 7
  = 6

(3)N≥num,重新设置V,计算分区号N

V = 8/2=4
N = N & (V - 1)
  = 6 & (4 - 1)
  = 6 & 3
  = 2

所以数据行存储到分区号2中,验证结果如图:

下一篇:

MySQL之KEY分区和LINEAR KEY分区