MySQL 分布式存储

一飞要上天
1,422 阅读6分钟

1、MySQL分布式的作用

MySQL分布式存储可以将用户请求操作分发到不同服务器下解决连接数过高问题。并且也可以将庞大的数据切割到不同的数据表下

2、MySQL中的分区

2.1、分区的分类

2.1.1求余算法

根据数据表字段进行对应算法的处理之后取模，最终决定数据所存储的“数据表”。

1）key算法：按照内置的key算法运算数字

2）hash算法：按照内置的hash算法运算数字

2.1.2、范围区间算法

1）list算法：范围算法（in）

2）range算法：区间算法

2.2、分区的创建

2.2.1、 key算法创建分区

1> 语法：

create(
    字段 类型
    ......
)
partition by key(字段/表达式) 分区数目;

2> 创建key算法的分区

CREATE TABLE t_key(
	id INT NOT NULL auto_increment,
	username VARCHAR(30) NOT NULL DEFAULT '',
	PRIMARY KEY(id)
)ENGINE = myisam DEFAULT CHARSET='utf8'
PARTITION by KEY(id) PARTITIONS 2;

Mysql内置的分区功能，对于数据表永远对外只有一张数据表。

本质对数据文件进行了拆分

备注：写入数据的存储到哪一个分区是由MySQL决定

注意：分区字段必须是主键的一部分。

2.2.2、 hash算法创建分区

1> 语法：

create(
    字段 类型
    ......
)
partition by hash(字段/表达式) 分区数目;

2> 创建hash算法的分区

CREATE TABLE t_hash(
	id INT NOT NULL auto_increment,
	username VARCHAR(30) NOT NULL DEFAULT '',
	PRIMARY KEY(id)
)ENGINE = myisam DEFAULT CHARSET='utf8'
PARTITION by HASH(id) PARTITIONS 2;

2.2.3、 range算法创建分区

1> 语法：

create(
    字段 类型
    ......
)
partition by range(字段/表达式) (
    partition 名称1 values less than(常量1)，
    partition 名称2 values less than(常量2)，
    partition 名称2 values less than(常量3)，
    ...
));

说明：当字段大小比常量1小，数据会在“名称1”的分区上,当字段大小比常量2小，数据会在“名称2”的分区上，当字段大小比常量3小，数据会在“名称3”的分区上...

2> 创建range算法的分区

CREATE TABLE t_range(
	id INT NOT NULL auto_increment,
	username VARCHAR(30) NOT NULL DEFAULT '',
	year_time INT NOT NULL DEFAULT 0,
	PRIMARY KEY(id,year_time)
)ENGINE = myisam DEFAULT CHARSET='utf8'
PARTITION by RANGE(year_time)(
	PARTITION 90hou VALUES less than(2000),
	PARTITION 00hou VALUES less than(2010)
);

说明：按照range分区只能录入数据比最后一个分区的值小

2.2.4、 list算法创建分区

1> 语法：

create(
    字段 类型
    ......
)
partition by list(字段/表达式) (
    partition 名称1 values in(常量1,常量2，...)，
    partition 名称2 values in(常量5,常量6, ...)，
    ...
));

2> 创建list算法的分区

CREATE TABLE t_range(
	id INT NOT NULL auto_increment,
	username VARCHAR(30) NOT NULL DEFAULT '',
	year_time INT NOT NULL DEFAULT 0,
	month_time INT NOT NULL DEFAULT 0,
	PRIMARY KEY(id,year_time)
)ENGINE = myisam DEFAULT CHARSET='utf8'
PARTITION by RANGE(year_time)(
	PARTITION spring VALUES IN(1,2,3),
	PARTITION summer VALUES IN(4,5,6)
);

注意：分区一般创建好后不要改变，以免数据丢失

2.3、分区的追加

2.3.1 key / hash 算法追加

1> 语法格式

alter table 表名 add partition partitions 追加的数量;

2> 追加 key / hash 算法的分区

alter table t_key add partition partitions 2

或

alter table t_hash add partition partitions 2

2.3.2 range 算法追加

1> 语法格式

alter table 表名 add partition(
    partition 名称 values less than(常量)
)

2> 追加 range 算法的分区

alter table t_range add partition(
    partition 10hou value less than(2020)
)

注意：range分区追加只能向后追加，常量只能越来越大

2.3.3 list 算法追加

1> 语法格式

alter table 表名 add partition(
    partition 名称 values in(常量1,常量2,...)
)

2> 追加 list 算法的分区

alter table t_list add partition(
    partition winter value in(10,11,12)
)

注意：range分区追加只能向后追加，常量只能越来越大

2.4、分区的删除

2.4.1 key / hash 算法删除

1> 语法格式

alter table 表名 coalesce partition 删除的数量;

2> 删除 key / hash 算法的分区

alter table t_key coalesce partition 2

或

alter table t_hash coalesce partition 2

2.4.2 range / list 算法删除

1> 语法格式

alter table 表名 drop partition 分区名称;

2> 删除 range 算法的分区

alter table t_range drop partition 10hou;

特定注意： 对于key / hash求余算法来说，删除分区不会导致数据丢失，数据仍然通过算法分配到其他具体的分区保留下来。对于range和list范围区间算法，删除分区可能会导致数据丢失。比如range算法，删除中间的分区，数据还可以保留在其他分区，但是如果删除最后的分区则会导致数据也一同删除掉。

MySQL没有提供修改分区的功能，可以先删除分区再追加分区以达到修改分区的目的。

3、分表

3.1、 分表的分类

3.2、 垂直分表

垂直分表即将数据表按照字段进行拆分。拆分之后每张表中的字段不一致（将数据表按照垂直的方向切割）

垂直分表常用的两种拆分方式：

1> 根据字段的使用频率，将经常使用的字段存储到一张表。将并不常使用的存储到另外一张表

2> 根据字段是否为固定长度。将固定长度的字段存储一张表，可变长度的字段存储另外一张表。

3.3、 水平分表

水平分表即将数据进行拆分。拆分之后每张表的字段一模一样。但是存储的数据不一致（可以理解为将数据表水平切割）。MySQL内置的分区功能也属于水平分表中的一种。

3.4、 实现物理水平分表

例如：如果要分成两张表，则余数不是0就是1，所以：创建一个article-0表，将主键求余为0的数据存储到这张表中，如下；并复制为article-1表，将主键求余为1的数据存储到这张中。

CREATE TABLE `article-0` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT '',
  `body` varchar(255) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

如果以主键唯一id来判断存储到哪张表中，需要考虑生成两种表达的全局唯一性主键id。

1> 方案1：使用两台MySQL服务器，利用步长来生成唯一的主键id，如第一台服务器起始值为1，另一条服务器起始值为2，步长均为2，每次增长2.这样主键id就不会重复。

2> 方案2：创建一张表，表中仅有一个字段id，专门用于生成主键id，由于该数据表只作写入操作，插入并获取相应主键id，不作查询操作，故不必担心因数据量增大带来的性能问题。

1> 创建生成文章标识的数据表：

CREATE TABLE `createaid` (
  `aid` int(11) NOT NULL DEFAULT '0' COMMENT '这个才是真的文章ID',
  PRIMARY KEY (`aid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

该表的作用用于生成文章的唯一标识。每次发布文章时先向该表中写入数据并获取到写入的id。再以文章的标识取模运算，决定写入的数据是哪一个。

因此，两个文章的数据表结构如下（只是表名不一样）：

CREATE TABLE `article-0` (z
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `aid` int(11) NOT NULL DEFAULT '0' COMMENT '文章ID标识',
  `title` varchar(255) DEFAULT '',
  `body` varchar(255) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
avatar
文章
阅读
粉丝