mysql 分区分表_mysql分库分区分表

一、分表

分表分为水平分表和垂直分表。

水平分表原理：

 分表策略通常是用户ID取模，如果不是整数，可以首先将其进行hash获取到整。

水平分表遇到的问题：

1. 跨表直接连接查询无法进行

2. 我们需要统计数据的时候

3. 如果数据持续增长，达到现有分表的瓶颈，需要增加分表，此时会出现数据重新排列的情况

解决方案建议：

1. 第1,２点可以通过增加汇总的冗余表，虽然数据量很大，但是可以用于后台统计或者查询时效性比较底的情况，而且我们可以提前算好某个时间点或者时间段的数据

2. 第３点解决建议：

1. 可以开始的时候，就分析大概的数据增长率，来大概确定未来某段时间内的数据总量，从而提前计算出未来某段时间内需要用到的分表的个数

2. 考虑表分区，在逻辑上面还是一个表名，实际物理存储在不同的物理地址上

3. 分库

垂直拆分原则：

1. 把大字段独立存储到一张表中

2. 把不常用的字段单独拿出来存储到一张表

3. 把经常在一起使用的字段可以拿出来单独存储到一张表

垂直拆分标准：

1.表的体积大于2G并且行数大于1千万

2.表中包含有text，blob，varchar(1000)以上

3.数据有时效性的，可以单独拿出来归档处理

表的体积计算

CREATE TABLE `test1` (

id bigint(20) not null auto_increment,

detail varchar(2000),

createtime  datetime,

validity int default '0',

primary key (id)

);

1000万  bigint 8字节 varchar 2000 字节 datetime  8字节 validity 4字节

(8+2000+8+4) * 10000000 = 20200000000 字节 == 18G

分表后体积：

CREATE TABLE `test1` (

id int not null auto_increment,

createtime  timestamp,

validity tinyint default 0,

primary key (id)

);

计算：(4+4+1) * 10000000 =  0.08G

表分区：

        就是将一个数据量比较大的表,用某种方法把数据从物理上分成若干个小表来存储(类似水平分表),从逻辑来看还是一个大表。分表最大分1024，一般分100左右比较适合。

使用场景：

对于这种数据库比较多，但是并发不是很多的情况下，可以采用表分区。

对于数据量比较大的，但是并发也比较高的情况下，可以采用分表和分区相结合。

二、分库

分库策略与分表策略的实现很相似，最简单的都是可以通过取模的方式进行路由。

分库也可以按照业务分库，比如订单表和库存表在两个库，要注意处理好跨库事务。

分表和分库 同时实现。

分库分表的策略相对于前边两种复杂一些，一种常见的路由策略如下：

１、中间变量　＝ user_id%(库数量*每个库的表数量);

２、库序号　＝　取整(中间变量／每个库的表数量);

３、表序号　＝　中间变量％每个库的表数量;

例如：数据库有256 个，每一个库中有1024个数据表，用户的user_id＝262145，按照上述的路由策略，可得：

１、中间变量　＝ 262145%(256*1024)= 1;

２、库序号　＝　取整(1／1024)= 0;

３、表序号　＝　1％1024 = 1;

这样的话，对于user_id＝262145，将被路由到第０个数据库的第１个表中。

range分区

create table test_range(

id int not null default 0

)engine=myisam default charset=utf8

partition by range(id)(

partition p1 values less than (3),

partition p2 values less than (5),

partition p3 values less than maxvalue

);

hash分区

create table test_hash(

id int not null default 0

)engine=innodb default charset=utf8

partition by hash(id) partitions 10;

线性hash分区

create table test_linear(

id int not null default 0

)engine=innodb default charset=utf8

partition by linear hash(id) partitions 10;

list分区

create table test_list(

id int not null

) engine=innodb default charset=utf8

partition by list(id)(

partition p0 values in (3,5),

partition p1 values in (2,6,7,9)

);

key 分区

CREATE TABLE test_key (

col1 INT NOT NULL

)

PARTITION BY  linear KEY (col1)

PARTITIONS 10;

普通的hash分区 增加风区后，需要重新计算

线性hash分区(了解)   增加分区后，还是在原来的分区

线性hash 相对于 hash分区 没有那么均匀

Key分区用的比较少，也是hash分区

三、HASH分区与线性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()函数返回一个整数。

        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';
说明:
   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，即，假设num=13，比num大的最小2的是16，即，可以通过如下公式计算：

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中，验证结果如图：