从01开始 从01开始
首页
  • 计算机科学导论
  • 数字电路
  • 计算机组成原理

    • 计算机组成原理-北大网课
  • 操作系统
  • Linux
  • Docker
  • 计算机网络
  • 计算机常识
  • Git
  • JavaSE
  • Java高级
  • JavaEE

    • Ant
    • Maven
    • Log4j
    • Junit
    • JDBC
    • XML-JSON
  • JavaWeb

    • 服务器软件
    • Servlet
  • Spring
  • 主流框架

    • Redis
    • Mybatis
    • Lucene
    • Elasticsearch
    • RabbitMQ
    • MyCat
    • Lombok
  • SpringMVC
  • SpringBoot
  • 学习网课的心得
  • 输入法
  • 节假日TodoList
  • 其他
  • 关于本站
  • 网站日记
  • 友人帐
  • 如何搭建一个博客
GitHub (opens new window)

peterjxl

人生如逆旅,我亦是行人
首页
  • 计算机科学导论
  • 数字电路
  • 计算机组成原理

    • 计算机组成原理-北大网课
  • 操作系统
  • Linux
  • Docker
  • 计算机网络
  • 计算机常识
  • Git
  • JavaSE
  • Java高级
  • JavaEE

    • Ant
    • Maven
    • Log4j
    • Junit
    • JDBC
    • XML-JSON
  • JavaWeb

    • 服务器软件
    • Servlet
  • Spring
  • 主流框架

    • Redis
    • Mybatis
    • Lucene
    • Elasticsearch
    • RabbitMQ
    • MyCat
    • Lombok
  • SpringMVC
  • SpringBoot
  • 学习网课的心得
  • 输入法
  • 节假日TodoList
  • 其他
  • 关于本站
  • 网站日记
  • 友人帐
  • 如何搭建一个博客
GitHub (opens new window)
  • JavaSE

  • JavaSenior

  • JavaEE

  • JavaWeb

  • Spring

  • 主流框架

    • Redis

    • Mybatis

    • Lucene

    • Elasticsearch

    • MQ

    • MyCat

      • MyCat2-尚硅谷
      • MyCat是什么,为什么要用
      • 搭建环境
      • MyCat概念
      • MyCat的配置文件
      • 一主一从复制
      • 一主一从读写分离
      • 双主双从复制方案
      • 分库分表
        • 如何分库
        • 如何分表
        • 环境准备
        • MyCat实现分库分表
        • 常用分片规则
        • 全局序列
      • MyCat安全设置
      • Mycat2-UI
      • MyCat
    • Lombok

    • 主流框架
  • SpringMVC

  • SpringBoot

  • Java并发

  • Java源码

  • JVM

  • 韩顺平

  • Java
  • Java
  • 主流框架
  • MyCat
2023-06-28
目录

分库分表

# 80.分库分表

一个数据库由很多表的构成,每个表对应着不同的业务,垂直切分是指按照业务将表进行分类,分布到不同的数据库上面,这样也就将数据或者说压力分担到不同的库上面,如下图:   ​​

系统被切分成了,用户,订单交易,支付几个模块。

‍

‍

# 如何分库

一个问题:在两台主机上的两个数据库中的表,能否关联查询?

答案:不可以关联查询。

分库的原则:有紧密关联关系的表应该在一个库里,相互没有关联关系的表可以分到不同的库里。举例:

--客户表 rows:20万
CREATE TABLE customer(
    id INT AUTO_INCREMENT,
    NAME VARCHAR(200),
    PRIMARY KEY(id)
);

-- 订单表 rows:600万
    CREATE TABLE orders(
    id INT AUTO_INCREMENT,
    order_type INT,
    customer_id INT,
    amount DECIMAL(10,2),
    PRIMARY KEY(id)
);


-- 订单详细表 rows:600万
CREATE TABLE orders_detail(
    id INT AUTO_INCREMENT,
    detail VARCHAR(2000),
    order_id INT,
    PRIMARY KEY(id)
);


-- 订单状态字典表 rows:20
CREATE TABLE dict_order_type(
    id INT AUTO_INCREMENT,
    order_type VARCHAR(200),
    PRIMARY KEY(id)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

以上四个表如何分库?客户表分在一个数据库,另外三张都需要关联查询,分在另外一个数据库

‍

‍

# 如何分表

1、选择要拆分的表

MySQL 单表存储数据条数是有瓶颈的,单表达到 1000 万条数据就达到了瓶颈,会影响查询效率,需要进行水平拆分(分表)进行优化。

例如:例子中的 orders、orders_detail 都已经达到 600 万行数据,需要进行分表优化。

‍

‍

2、分表字段

以 orders 表为例,可以根据不同自字段进行分表:

编号 分表字段 效果
1 id(主键、或创建时间) 查询订单注重时效,历史订单被查询的次数少,
如此分片会造成一个节点访问多,一个访问少,不平均。
2 customer_id(客户 id) 根据客户 id 去分,两个节点访问平均,
一个客户的所有订单都在同一个节点

‍

‍

# 环境准备

为了方便,我们不需要启动这么多MySQL服务器,这里只保留2个即可,也就是192.168.56.101/103

为此,我们可以修改下集群的配置vim /usr/local/mycat/conf/clusters/prototype.cluster.json​,修改第11行和第17行,只保留一个Master和slave:

{
	"clusterType":"MASTER_SLAVE",
	"heartbeat":{
		"heartbeatTimeout":1000,
		"maxRetryCount":3,
		"minSwitchTimeInterval":300,
		"showLog":false,
		"slaveThreshold":0.0
	},
	"masters":[
		"rwSepw"
	],
	"maxCon":2000,
	"name":"prototype",
	"readBalanceType":"BALANCE_ALL",
	"replicas":[
		"rwSepr"
	],
	"switchType":"SWITCH"
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

‍

同理,数据源的配置,我们也去掉(可以通过修改文件名的方式):

cd /usr/local/mycat/conf/datasources/
mv rwSepr2.datasource.json rwSepr2.datasource.json_backup
mv rwSepw2.datasource.json rwSepw2.datasource.json_backup
1
2
3

‍

然后重启MyCat:

/usr/local/mycat/bin
./mycat restart
1
2

‍

查看数据源,可以看到只有一个Master:

/*+ mycat:showClusters{} */;
1

‍

‍

至此,104和105两台MySQL可以关闭了。

‍

# MyCat实现分库分表

Mycat2 一大优势就是可以在终端直接创建数据源、集群、库表,并在创建时指定分库、分表。与 1.6 版本比大大简化了分库分表的操作

# 1、添加数据源

/*+ mycat:createDataSource{
    "name":"dw0",
    "url":"jdbc:mysql://192.168.56.101:3306",
    "user":"root",
    "password":"Mycat0520@"
} */;

/*+ mycat:createDataSource{
    "name":"dr0",
    "url":"jdbc:mysql://192.168.56.101:3306",
    "user":"root",
    "password":"Mycat0520@"
} */;

/*+ mycat:createDataSource{
    "name":"dw1",
    "url":"jdbc:mysql://192.168.56.103:3306",
    "user":"root",
    "password":"Mycat0520@"
} */;

/*+ mycat:createDataSource{
    "name":"dr1",
    "url":"jdbc:mysql://192.168.56.103:3306",
    "user":"root",
    "password":"Mycat0520@"
} */;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

‍

‍

通过注释命名添加数据源后,在对应目录会生成相关配置文件

ll /usr/local/mycat/conf/datasources
-rw-r--r--  1 root root 518 6月  26 20:24 dr0.datasource.json
-rw-r--r--  1 root root 518 6月  26 20:24 dr1.datasource.json
-rw-r--r--  1 root root 518 6月  26 20:24 dw0.datasource.json
-rw-r--r--  1 root root 518 6月  26 20:24 dw1.datasource.json
1
2
3
4
5

‍

‍

# 2、添加集群配置

把新添加的数据源配置成集群

/*!mycat:createCluster{"name":"c0","masters":["dw0"],"replicas":["dr0"]}*/;
/*!mycat:createCluster{"name":"c1","masters":["dw1"],"replicas":["dr1"]}*/;
1
2

‍

可以查看集群配置信息,c0主要是192.168.56.101,c1主要是192.168.56.103

ll /usr/local/mycat/conf/clusters
总用量 12
-rw-r--r-- 1 root root 312 6月  26 20:43 c0.cluster.json
-rw-r--r-- 1 root root 312 6月  26 20:43 c1.cluster.json
1
2
3
4

其实这4个数据源应该是在不同的服务器上的,这里是为了简化才只使用了2个数据库

‍

# 3、创建全局表

在本文一开的案例中,有个字典表:dict_order_type,这个表是每个数据库都要用到的,在MyCat中,这叫全局表(也叫广播表)。

我们来创建一个全局表,在MyCat中执行:

--添加数据库db1
CREATE DATABASE db1;

--在建表语句中加上关键字 BROADCAST(广播,即为全局表)
CREATE TABLE db1.`travelrecord` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `user_id` varchar(100) DEFAULT NULL,
    `traveldate` date DEFAULT NULL,
    `fee` decimal(10,0) DEFAULT NULL,
    `days` int DEFAULT NULL,
    `blob` longblob,
    PRIMARY KEY (`id`),
    KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 BROADCAST;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

‍

‍

进入相关目录查看 schema 配置,可以看到自动生成的全局表配置信息

cat /usr/local/mycat/conf/schemas/db1.schema.json
1

‍

我们打开101和103的数据库,也能看到这个数据库和表。

‍

# 4、创建分片表(分库分表)

在 Mycat 里运行建表语句进行数据分片:

CREATE TABLE db1.orders(
    id BIGINT NOT NULL AUTO_INCREMENT,
    order_type INT,
    customer_id INT,
    amount DECIMAL(10,2),
    PRIMARY KEY(id),
    KEY `id` (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
dbpartition BY mod_hash(customer_id) tbpartition BY mod_hash(customer_id)
dbpartitions 2 tbpartitions 1;
1
2
3
4
5
6
7
8
9
10

关键是建表语句后面的几句,讲了数据库分片规则,表分片规则,以及各分多少片。mod_hash,就是一个hash算法,可以均匀的划分。

‍

‍

同样可以查看生成的配置信息,进入相关目录查看 schema 配置

cat /usr/local/mycat/conf/schemas/db1.schema.json
1

‍

‍

接下来我们造几个数:

INSERT INTO orders(id,order_type,customer_id,amount) VALUES(1,101,100,100100);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(2,101,100,100300);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(3,101,101,120000);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(4,101,101,103000);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(5,102,101,100400);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(6,102,100,100020);


1
2
3
4
5
6
7
8

‍

我们连接MySQL,可以看到有多了数据库,并且数据库里只存储了部分数据:

​​​​

另一个数据库同理,只存储了部分数据

‍

‍

但我们如果通过MyCat查询数据,是可以看到有全部数据的:

SELECT * FROM orders;
1

‍

# 5、创建 ER 表

有时候,我们需要和分片表进行关联查询,这种表就叫ER表。

例如订单表只记录了订单基本信息,订单详情表则记录了具体订单的信息,那么订单详情表如何创建?举例:

CREATE TABLE orders_detail(
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    detail VARCHAR(2000),
    order_id INT,
    PRIMARY KEY(id)
) ENGINE=INNODB DEFAULT CHARSET=utf8
dbpartition BY mod_hash(order_id) tbpartition BY mod_hash(order_id)
tbpartitions 1 dbpartitions 2;
1
2
3
4
5
6
7
8

‍

‍

‍

‍

然后我们造数:

INSERT INTO orders_detail(id,detail,order_id) VALUES(1,'detail1',1);
INSERT INTO orders_detail(id,detail,order_id) VALUES(2,'detail1',2);
INSERT INTO orders_detail(id,detail,order_id) VALUES(3,'detail1',3);
INSERT INTO orders_detail(id,detail,order_id) VALUES(4,'detail1',4);
INSERT INTO orders_detail(id,detail,order_id) VALUES(5,'detail1',5);
INSERT INTO orders_detail(id,detail,order_id) VALUES(6,'detail1',6);
1
2
3
4
5
6

‍

同理,某个数据库只存储了部分数据:

​

‍

然后我们试试能不能关联查询:

SELECT * FROM orders o INNER JOIN orders_detail od ON od.order_id=o.id;
1

不出意外,是可以的。

‍

‍

我们在建表的时候,好像没有指定外键关系,为什么也能查询呢?这是因为 Mycat2 无需指定 ER 表,能自动识别,如果想要查看配置的表是否具有 ER 关系,使用

/*+ mycat:showErGroup{}*/
1

‍

查询结果:

groupId schemaName tableName
0.00 db1 orders
0.00 db1 orders_detail

group_id 表示相同的组,该组中的表具有相同的存储分布,查询的时候MyCat会自动合并结果,示意图:

​​

‍

# 常用分片规则

# 1、分片算法简介

Mycat2 支持常用的(自动)HASH 型分片算法,也兼容 1.6 的内置的(cobar)分片算法.

HASH 型分片算法默认要求集群名字以 c 为前缀,数字为后缀,c0 就是分片表第一个节点,c1 就是第二个节点。该命名规则允许用户手动改变

‍

# 2、Mycat2 与 1.x 版本区别

Mycat2 Hash 型分片算法多数基于 MOD_HASH(MOD 对应 Java 的 % 运算),实际上是取余运算。

Mycat2 Hash 型分片算法对于值的处理,总是把分片值转换到列属性的数据类型再运算。

而 1.x 系列的分片算法统一转换到字符串类型再运算,且只能根据一个分片字段计算出存储节点下标。

Mycat2 Hash 型分片算法适用于等价条件查询。

而 1.x 系列由于含有用户经验的路由规则,1.x 系列的分片规则总是先转换成字符串再运算。

‍

# 3、分片规则与适用性

分片算法 描述 分库 分表 数值类型
MOD_HASH 取模哈希 是 是 数值,字符串
UNI_HASH 取模哈希 是 是 数值,字符串
RIGHT_SHIFT 右移哈希 是 是 数值
RANGE_HASH 两字段其一取模 是 是 数值,字符串
YYYYMM 按年月哈希 是 是 DATE,DATETIME
YYYYDD 按年日哈希 是 是 DATE,DATETIME
YYYYWEEK 按年周哈希 是 是 DATE,DATETIME
MM 按月哈希 否 是 DATE,DATETIME
DD 按日期哈希 否 是 DATE,DATETIME
MMDD 按月日哈希 是 是 DATE,DATETIME
WEEK 按周哈希 否 是 DATE,DATETIME
STR_HASH 字符串哈希 是 是 字符串

‍

# 4、常用分片规则简介

(1)MOD_HASH

【数据分片】HASH 型分片算法-MOD_HASH

如果分片值是字符串则先对字符串进行 hash 转换为数值类型

分库键和分表键是同键:

  • 分表下标 = 分片值 % (分库数量 * 分表数量)
  • 分库下标 = 分表下标 / 分表数量

分库键和分表键是不同键:

  • 分表下标= 分片值 % 分表数量
  • 分库下标= 分片值 % 分库数量

‍

(2)RIGHT_SHIFT

【数据分片】HASH 型分片算法 - RIGHT_SHIFT

RIGHT_SHIFT(字段名,位移数)

仅支持数值类型,分片值右移二进制位数,然后按分片数量取余

‍

(3)YYYYMM

【数据分片】HASH 型分片算法-YYYYMM

仅用于分库

(YYYY*12+MM) % 分库数.MM 是 1-12

‍

(4)MMDD

仅用于分表,仅 DATE/DATETIME,一年之中第几天%分表数,tbpartitions 不超过 366

‍

# 全局序列

Mycat2 在 1.x 版本上简化全局序列,自动默认使用雪花算法生成全局序列号,如不需要 Mycat 默认的全局序列,可以通过配置关闭自动全局序列

‍

# 1、建表语句方式关闭全局序列

如果不需要使用 mycat 的自增序列,而使用 mysql 本身的自增主键的功能,需要在配置中更改对应的建表 sql,不设置 AUTO_INCREMENT 关键字,这样MyCat就不认为这个表有自增主键的功能,就不会使用 mycat 的全局序列号。

这样,对应的插入 sql 在 mysql处理,由 mysql 的自增主键功能补全自增值.

雪花算法:引入了时间戳和 ID 保持自增的分布式 ID 生成算法:

​​

‍

建表 sql 可以自动在原型库对应的逻辑表的物理表获取,如果逻辑表的建表 SQL 与物理表的建表 SQL 不对应,则需要在配置文件中配置建表 SQL。例如:

带 AUTO_INCREMENT 关键字使用默认全局序列

CREATE TABLE db1.`travelrecord` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `user_id` varchar(100) DEFAULT NULL,
    `traveldate` date DEFAULT NULL,
    `fee` decimal(10,0) DEFAULT NULL,
    `days` int DEFAULT NULL,
    `blob` longblob,
    PRIMARY KEY (`id`),
    KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 BROADCAST;
1
2
3
4
5
6
7
8
9
10

‍

‍

去掉关键字AUTO_INCREMENT,不使用默认全局序列

CREATE TABLE db1.`travelrecord` (
    `id` bigint NOT NULL,
    `user_id` varchar(100) DEFAULT NULL,
    `traveldate` date DEFAULT NULL,
    `fee` decimal(10,0) DEFAULT NULL,
    `days` int DEFAULT NULL,
    `blob` longblob,
    PRIMARY KEY (`id`),
    KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 BROADCAST;
1
2
3
4
5
6
7
8
9
10

‍

‍

# 2、设置 Mycat 数据库方式获取全局序列

1、在prototype服务器的db1库导入dbseq.sql文件

Mycat2已经为用户提供了相关sql脚本,需要在对应数据库下运行脚本,不能通过Mycat客户端执行

脚本所在目录:mycat/conf/dbseq.sql

脚本内容较长,这里不列出。其实就是创建了一个表,然后创建了一些规则

‍

‍

‍

2、添加全局序列配置文件

进入/mycat/conf/sequences目录,添加配置文件,{数据库名字}_{表名字}.sequence.json​,配置内容:

{
    "clazz":"io.mycat.plug.sequence.SequenceMySQLGenerator",
    "name":"db1_travelrecord",
    "targetName": "prototype",
    "schemaName":"db1"//指定物理库名
}
1
2
3
4
5
6

可选参数targetName 更改序列号服务器

"targetName": "prototype" 是执行自增序列的节点,也是dbseq.sql导入的节点

dbseq.sql导入的当前库的库名与逻辑表的逻辑库名一致。导入后检查库下有没有mycat_sequence表。

其中increment是序列号自增的步伐,为1的时候严格按1递增,当1000的时候,mycat会每次批量递增1000取序列号,此时在多个mycat访问此序列号表的情况下,不能严格自增NAME列中的值是对应的 库名_表名 该值需要用户设置,即插入一条逻辑表相关的记录,用于记录序列号

‍

‍

‍

3、切换为数据库方式全局序列号

使用注释前要导入dbseq.sql,以及设置mycat_sequence表内的逻辑表记录。通过注释设置为数据库方式全局序列号

/*+ mycat:setSequence{
    "name":"db1_travelrecord",
    "clazz":"io.mycat.plug.sequence.SequenceMySQLGenerator",
    "name":"db1_travelrecord",
    "targetName": "prototype",
    "schemaName":"db2"
} */;
1
2
3
4
5
6
7

‍

‍

4、如果想切换为雪花算法方式全局序列号:

/*+ mycat:setSequence{"name":"db1_travelrecord","time":true} */;
1
在GitHub上编辑此页 (opens new window)
上次更新: 2023/6/28 11:11:51
双主双从复制方案
MyCat安全设置

← 双主双从复制方案 MyCat安全设置→

Theme by Vdoing | Copyright © 2022-2023 粤ICP备2022067627号-1 粤公网安备 44011302003646号
  • 跟随系统
  • 浅色模式
  • 深色模式
  • 阅读模式