MySQL-分库分表详解（四）

♥️作者：小刘在C站

♥️个人主页： 小刘主页

♥️努力不一定有回报，但一定会有收获加油！一起努力，共赴美好人生！

♥️学习两年总结出的运维经验，以及思科模拟器全套网络实验教程。专栏：云计算技术

♥️小刘私信可以随便问，只要会绝不吝啬，感谢CSDN让你我相遇！

前言上章讲到MySQL-分库分表（三）本章继续

MySQL

5 MyCat分片

5.1 垂直拆分

5.1.1 场景

5.1.2 准备

5.1.3 配置

1). schema.xml

2). server.xml

5.1.4 测试

5.1.5 全局表

配置完毕后，重新启动MyCat。

MySQL

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型和大型网站的开发都选择 MySQL 作为网站数据库。

5 MyCat分片

5.1 垂直拆分

5.1.1 场景

在业务系统中 , 涉及以下表结构 , 但是由于用户与订单每天都会产生大量的数据 , 单台服务器的数据

存储及处理能力是有限的 , 可以对数据库表进行拆分 , 原有的数据库表如下。

现在考虑将其进行垂直分库操作，将商品相关的表拆分到一个数据库服务器，订单表拆分的一个数据库服务器，用户及省市区表拆分到一个服务器。最终结构如下：

5.1.2 准备

准备三台服务器， IP 地址如图所示：

并且在 192.168.200.210 ， 192.168.200.213, 192.168.200.214 上面创建数据库

shopping 。

5.1.3 配置

1). schema.xml

<schema name="SHOPPING" checkSQLschema="true" sqlMaxLimit="100">
<table name="tb_goods_base" dataNode="dn1" primaryKey="id" />
<table name="tb_goods_brand" dataNode="dn1" primaryKey="id" />
<table name="tb_goods_cat" dataNode="dn1" primaryKey="id" />
<table name="tb_goods_desc" dataNode="dn1" primaryKey="goods_id" />
<table name="tb_goods_item" dataNode="dn1" primaryKey="id" />
<table name="tb_order_item" dataNode="dn2" primaryKey="id" />
<table name="tb_order_master" dataNode="dn2" primaryKey="order_id" />
<table name="tb_order_pay_log" dataNode="dn2" primaryKey="out_trade_no" />
<table name="tb_user" dataNode="dn3" primaryKey="id" />
<table name="tb_user_address" dataNode="dn3" primaryKey="id" />
<table name="tb_areas_provinces" dataNode="dn3" primaryKey="id"/>
<table name="tb_areas_city" dataNode="dn3" primaryKey="id"/>
<table name="tb_areas_region" dataNode="dn3" primaryKey="id"/>
</schema>
<dataNode name="dn1" dataHost="dhost1" database="shopping" />
<dataNode name="dn2" dataHost="dhost2" database="shopping" />
<dataNode name="dn3" dataHost="dhost3" database="shopping" />
<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"
slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="master" url="jdbc:mysql://192.168.200.210:3306?
useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8"
user="root" password="1234" />
</dataHost>
<dataHost name="dhost2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"
slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="master" url="jdbc:mysql://192.168.200.213:3306?
useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8"
user="root" password="1234" />
</dataHost>
<dataHost name="dhost3" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"
slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="master" url="jdbc:mysql://192.168.200.214:3306?
useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8"
user="root" password="1234" />
</dataHost>

2). server.xml

<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">SHOPPING</property>
<!-- 表级 DML 权限设置 -->
<!--
<privileges check="true">
<schema name="DB01" dml="0110" >
<table name="TB_ORDER" dml="1110"></table>
</schema>
</privileges>
-->
</user>
<user name="user">
<property name="password">123456</property>
<property name="schemas">SHOPPING</property>
<property name="readOnly">true</property>
</user>

5.1.4 测试

1). 上传测试 SQL 脚本到服务器的 /root 目录

2). 执行指令导入测试数据

重新启动 MyCat 后，在 mycat 的命令行中，通过 source 指令导入表结构，以及对应的数据，查看数据分布情况。

source /root/shopping-table.sql
source /root/shopping-insert.sql

将表结构及对应的测试数据导入之后，可以检查一下各个数据库服务器中的表结构分布情况。检查是否和我们准备工作中规划的服务器一致。

3). 查询用户的收件人及收件人地址信息(包含省、市、区)。

在 MyCat 的命令行中，当我们执行以下多表联查的 SQL 语句时，可以正常查询出数据。


select ua.user_id, ua.contact, p.province, c.city, r.area , ua.address from
tb_user_address ua ,tb_areas_city c , tb_areas_provinces p ,tb_areas_region r
where ua.province_id = p.provinceid and ua.city_id = c.cityid and ua.town_id =
r.areaid ;

4). 查询每一笔订单及订单的收件地址信息(包含省、市、区)。

实现该需求对应的 SQL 语句如下：

SELECT order_id , payment ,receiver, province , city , area FROM tb_order_master o
, tb_areas_provinces p , tb_areas_city c , tb_areas_region r WHERE
o.receiver_province = p.provinceid AND o.receiver_city = c.cityid AND
o.receiver_region = r.areaid ;

但是现在存在一个问题，订单相关的表结构是在 192.168.200.213 数据库服务器中，而省市区的数

据库表是在 192.168.200.214 数据库服务器中。那么在 MyCat 中执行是否可以成功呢？

经过测试，我们看到， SQL 语句执行报错。原因就是因为 MyCat 在执行该 SQL 语句时，需要往具体的数据库服务器中路由，而当前没有一个数据库服务器完全包含了订单以及省市区的表结构，造成 SQL 语句失败，报错。

对于上述的这种现象，我们如何来解决呢？下面我们介绍的全局表，就可以轻松解决这个问题。

5.1.5 全局表

对于省、市、区 / 县表 tb_areas_provinces , tb_areas_city , tb_areas_region ，是属于

数据字典表，在多个业务模块中都可能会遇到，可以将其设置为全局表，利于业务操作。

修改 schema.xml 中的逻辑表的配置，修改 tb_areas_provinces 、 tb_areas_city 、

tb_areas_region 三个逻辑表，增加 type 属性，配置为 global ，就代表该表是全局表，就会在

所涉及到的 dataNode 中创建给表。对于当前配置来说，也就意味着所有的节点中都有该表了。


<table name="tb_areas_provinces" dataNode="dn1,dn2,dn3" primaryKey="id"
type="global"/>
<table name="tb_areas_city" dataNode="dn1,dn2,dn3" primaryKey="id"
type="global"/>
<table name="tb_areas_region" dataNode="dn1,dn2,dn3" primaryKey="id"
type="global"/>

配置完毕后，重新启动MyCat。

1). 删除原来每一个数据库服务器中的所有表结构

2). 通过 source 指令，导入表及数据

source /root/shopping-table.sql
source /root/shopping-insert.sql

3). 检查每一个数据库服务器中的表及数据分布，看到三个节点中都有这三张全局表

4). 然后再次执行上面的多表联查的 SQL 语句

SELECT order_id , payment ,receiver, province , city , area FROM tb_order_master o
, tb_areas_provinces p , tb_areas_city c , tb_areas_region r WHERE
o.receiver_province = p.provinceid AND o.receiver_city = c.cityid AND
o.receiver_region = r.areaid ;