mysql sharding-数据库分库 分表 分区 和 设计三范式

mysql 设计三范式

第一范式:表的列的具有原子性,字段不可再分解；（原子性）

第二范式:在第一范式的基础上,要求数据库表中的每个实例或行必须可以被惟一地区分；一个表必须有唯一主键；（最小单元）

第三范式:在第二范式的基础上，要求一个数据库表中不包含已在其它表中已包含的非主键字段。表关联要使用主键关联；（不冗余）

sharding- 数据库分库 分表 分区

一、分库 分表

1、业务分库：按业务将不同表放进不同库。每个库可以放在不同数据库服务器上。

2、垂直切分：将一个表按照字段分成多表（热门字段、冷门字段），每个表存储一部分字段。表可以放在不同存储设备上。

3、水平切分：将大表按条件切分到不同表中。每个表存储一部分满足条件的行。

水平切分通常有几种常用的切分方式：

a.直接按字段条件切分（例如按省级分表）

b.取模后切分：取模是对数值或能转换为数值的字段进行取模，要切分成几片，就除几。（例如根据 学号 % 6 =1 放进stu_1库 等，进行求余分库）

c.按范围切分（例如 按月份、季度、年份切分）

*水平切分对性能提升非常大，不仅可以避开服务器资源争用，还减小了索引大小以及每个库维护的表数据量。

二、数据库分区

1、水平分区的模式：

Range（范围） – 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区，80年代（1980’s）的数据，90年代（1990’s）的数据以及任何在2000年（包括2000年）后的数据。

Hash（哈希） – 这种模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如DBA可以建立一个对表主键进行分区的表。

Key（键值） – Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。

List（预定义列表） – 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如：DBA建立了一个横跨三个分区的表，分别根据2004年2005年和2006年值所对应的数据。

Composite（复合模式） - 很神秘吧，哈哈，其实是以上模式的组合使用而已，就不解释了。举例：在初始化已经进行了Range范围分区的表上，我们可以对其中一个分区再进行hash哈希分区。

2、垂直分区（按列分）：

举个简单例子：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就要把这些不经常使用的text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。

例如：范围range ：date字段按年份分区
PARTITION BY：分区

CREATE TABLE part_tab (
 c1 int default NULL, 
 c2 varchar(30) default NULL, 
 c3 date default NULL
) engine=innodb
PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995),
PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) ,
PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) ,
PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) ,
PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) ,
PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010),
PARTITION p11 VALUES LESS THAN MAXVALUE );

*在这里注意：

mysql innodb引擎默认使用共享空间ibdata1，增删数据库的时候，ibdata1文件不会自动收缩；

在MySQL的配置文件[mysqld]部分，增加innodb_file_per_table=1参数。

可以修改InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间。

独立表空间优点：

1． 每个表都有自已独立的表空间。

2． 每个表的数据和索引都会存在自已的表空间中。

3． 可以实现单表在不同的数据库中移动。

4． 空间可以回收（除drop table操作处，表空不能自已回收）

a) Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。

b) 对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。

c) 对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。

独立表空间缺点：单表增加过大，如超过100个G。