一、分表
分表分为水平分表和垂直分表。
水平分表原理:
分表策略通常是用户ID取模,如果不是整数,可以首先将其进行hash获取到整。
水平分表遇到的问题:
1. 跨表直接连接查询无法进行
2. 我们需要统计数据的时候
3. 如果数据持续增长,达到现有分表的瓶颈,需要增加分表,此时会出现数据重新排列的情况
解决方案建议:
1. 第1,2点可以通过增加汇总的冗余表,虽然数据量很大,但是可以用于后台统计或者查询时效性比较底的情况,而且我们可以提前算好某个时间点或者时间段的数据
2. 第3点解决建议:
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左右比较适合。
使用场景:
对于这种数据库比较多,但是并发不是很多的情况下,可以采用表分区。
对于数据量比较大的,但是并发也比较高的情况下,可以采用分表和分区相结合。
二、分库
分库策略与分表策略的实现很相似,最简单的都是可以通过取模的方式进行路由。
分库也可以按照业务分库,比如订单表和库存表在两个库,要注意处理好跨库事务。
分表和分库 同时实现。
分库分表的策略相对于前边两种复杂一些,一种常见的路由策略如下:
1、中间变量 = user_id%(库数量*每个库的表数量);
2、库序号 = 取整(中间变量/每个库的表数量);
3、表序号 = 中间变量%每个库的表数量;
例如:数据库有256 个,每一个库中有1024个数据表,用户的user_id=262145,按照上述的路由策略,可得:
1、中间变量 = 262145%(256*1024)= 1;
2、库序号 = 取整(1/1024)= 0;
3、表序号 = 1%1024 = 1;
这样的话,对于user_id=262145,将被路由到第0个数据库的第1个表中。
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中,验证结果如图: