Mycat的简单使用（三）【分库分表】

在线渠道：每天产生 3w 笔聊天会话，假设，其中50%的会话会生成一笔离线工单，那么每天可生成 3w * 50% = 1.5w 笔工单；

热线渠道：每天产生 2.5w 通电话，假设，其中80%的电话都会产生一笔工单，那么每天可生成 2.5w * 80% = 2w 笔/天；

离线渠道：假设离线渠道每天直接生成 3w 笔；

合计共 1.5w + 2w + 3w = 6.5w 笔/天

考虑到以后可能要继续覆盖的新的业务场景，需要提前预留部分扩展空间，这里我们假设为每天产生 8w 笔问题单。

除问题单外，还有另外2张常用的业务表：用户操作日志表、用户提交的表单数据表。

其中，每笔问题单都会产生多条用户操作日志，根据历史统计数据来可以看到，平均每个问题单大约会产生8条操作日志，我们预留一部分空间，假设每个问题单平均产生约10条用户操作日志。

如果系统设计使用年限5年，那么问题单数据量大约 = 5年 365天/年 8w/天 = 1.46亿，那么估算出的表数量如下：

问题单需要：1.46亿/500w = 29.2 张表，我们就按 32 张表来切分；

操作日志需要 ：32 10 = 320 张表，我们就按 32 16 = 512 张表来切分。

如果需要分库，那么分多少库合适？

分库的时候除了要考虑平时的业务峰值读写QPS外，还要考虑到诸如双11大促期间可能达到的峰值，需要提前做好预估。

根据我们的实际业务场景，问题单的数据查询来源主要来自于阿里客服小蜜首页。因此，可以根据历史QPS、RT等数据评估，假设我们只需要3500数据库连接数，如果单库可以承担最高1000个数据库连接，那么我们就可以拆分成4个库。

如何对数据进行切分？

根据行业惯例，通常按照 水平切分、垂直切分 两种方式进行切分，当然，有些复杂业务场景也可能选择两者结合的方式。

（1）水平切分

这是一种横向按业务维度切分的方式，比如常见的按会员维度切分，根据一定的规则把不同的会员相关的数据散落在不同的库表中。由于我们的业务场景决定都是从会员视角进行数据读写，所以，我们就选择按照水平方式进行数据库切分。

（2）垂直切分

垂直切分可以简单理解为，把一张表的不同字段拆分到不同的表中。

比如：假设有个小型电商业务，把一个订单相关的商品信息、买卖家信息、支付信息都放在一张大表里。可以考虑通过垂直切分的方式，把商品信息、买家信息、卖家信息、支付信息都单独拆分成独立的表，并通过订单号跟订单基本信息关联起来。

也有一种情况，如果一张表有10个字段，其中只有3个字段需要频繁修改，那么就可以考虑把这3个字段拆分到子表。避免在修改这3个数据时，影响到其余7个字段的查询行锁定。

分库

============================================================

数据库准备

---

**此处模拟云医院管理系统当数据库连接压力过大时进行数据库的拆分，计划拆分成两个数据库。

数据库设计关系如下：**

根据上述业务表关联关系科室、用户表、挂号信息表三个表之间是有关联关系的所以应该放到一个数据节点上，另外两张表为了测试放到另外一个数据节点上，表示成如下形式：

停止之前的数据库

**分库一定要在新的数据库上准备，此处抛弃上述数据库重新创建干净的数据库

删除掉原来的数据库(容器),可以仅停止不删除容器用于测试之前的逻辑**

[root@mycat ~]# docker stop m1 && docker stop m2 && docker stop s1 && docker stop s2

m1

m2

s1

s2

[root@mycat ~]# docker rm m1 && docker rm m2 && docker rm s1 && docker rm s2

m1

m2

s1

s2

[root@mycat ~]#

安装两个数据库服务（容器）

创建两个数据库,此处暂时不配置主从数据库复制(Master-Slave模式)所以不需要在docker宿主机上单独映射配置文件，执行如下命令直接创建2个MySQL数据库容器

docker run --name dn1 -p 3316:3306 -e MYSQL_ROOT_PASSWORD=root -d mysql:5.7

docker run --name dn2 -p 3326:3306 -e MYSQL_ROOT_PASSWORD=root -d mysql:5.7

具体执行过程如下：

[root@mycat ~]# docker run --name dn1 -p 3316:3306 -e MYSQL_ROOT_PASSWORD=root -d mysql:5.7 &&\

docker run --name dn2 -p 3326:3306 -e MYSQL_ROOT_PASSWORD=root -d mysql:5.7

b9b3297b30ae82cd25889414e76e16c0fa20c186535a1c6b0d2d15469163b40e

e61487a76ef284927f031897ac38d7c6d00c88c191265f946fcf6bb237f04f54

[root@mycat ~]#

查看两个数据库机器（容器）的ip

[root@mycat ~]# docker ps

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

fd2bea0b1f01 mysql:5.7 “docker-entrypoint.s…” 5 seconds ago Up 3 seconds 33060/tcp, 0.0.0.0:3326->3306/tcp, :::3326->3306/tcp dn2

6ae712c5f59c mysql:5.7 “docker-entrypoint.s…” 6 seconds ago Up 5 seconds 33060/tcp, 0.0.0.0:3316->3306/tcp, :::3316->3306/tcp dn1

[root@mycat ~]# docker inspect --format ‘{ { .NetworkSettings.IPAddress }}’ dn1 &&\

docker inspect --format ‘{ { .NetworkSettings.IPAddress }}’ dn2

172.17.0.2

172.17.0.3

[root@mycat ~]#

ip地址分别是：

| 容器 | IP |

| — | — |

| dn1 | 172.17.0.2 |

| dn2 | 172.17.0.3 |

创建数据库

在两个空白数据库机器（容器）上创建数据库语句如下：

CREATE DATABASE his_mycat DEFAULT CHARACTER SET utf8mb4;

在dn1上创建数据库

[root@mycat ~]# mysql -u root -proot -h 172.17.0.2 -P3306

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MySQL connection id is 5

Server version: 5.7.34 MySQL Community Server (GPL)

Copyright © 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MySQL [(none)]> CREATE DATABASE his_mycat DEFAULT CHARACTER SET utf8mb4;

Query OK, 1 row affected (0.01 sec)

MySQL [(none)]> show databases;

±-------------------+

| Database |

±-------------------+

| information_schema |

| his_mycat |

| mysql |

| performance_schema |

| sys |

±-------------------+

5 rows in set (0.00 sec)

MySQL [(none)]> exit

Bye

[root@mycat ~]#

在dn2上创建数据库

[root@mycat ~]# mysql -u root -proot -h 172.17.0.3 -P3306

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.7.34 MySQL Community Server (GPL)

Copyright © 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MySQL [(none)]> CREATE DATABASE his_mycat DEFAULT CHARACTER SET utf8mb4;

Query OK, 1 row affected (0.00 sec)

MySQL [(none)]>

MySQL [(none)]> show databases;

±-------------------+

| Database |

±-------------------+

| information_schema |

| his_mycat |

| mysql |

| performance_schema |

| sys |

±-------------------+

5 rows in set (0.00 sec)

MySQL [(none)]> exit

Bye

[root@mycat ~]#

配置mycat

---

分库规则：

• dn1：department部门表、user用户表、register患者挂号表

• dn2：drugs 药品表、disease 疾病表

修改mycat的schema.xml重新配置分库规则：

<?xml version="1.0"?>

<mycat:schema xmlns:mycat=“http://io.mycat/”>

<dataHost name=“host1” maxCon=“1000” minCon=“10” balance=“0”

writeType=“0” dbType=“mysql” dbDriver=“native” switchType=“1”

slaveThreshold=“100”>

select user()

<dataHost name=“host2” maxCon=“1000” minCon=“10” balance=“0”

writeType=“0” dbType=“mysql” dbDriver=“native” switchType=“1”

slaveThreshold=“100”>

select user()

<