MyCat入门篇-使用案例3：水平拆分

前面的文章中，我们已经完成了mycat的读写分离的实验，请参考如下文章链接：
[MyCat入门篇-使用案例1：读写分离(part1)]
[MyCat入门篇-使用案例1：读写分离(part2)]
[MyCat入门篇-使用案例1：读写分离(part3)]
[MyCat入门篇-使用案例1：读写分离(part4)]
[MyCat入门篇-使用案例2：垂直拆分]

接下来我们进行mycat和MySQL集成的水平拆分的实验部分。

---

配置水平拆分(分表)

所谓的水平拆分，就是指把某一个表中的数据，按照某种路由规则，分别放在不同的MySQL数据库服务器上。

此时每一个MySQL数据库服务器上面的表的结构都是一样的，表的数量也是一样的。

这样来实现对一个大表中的数据来说，让它们均匀的分散到各个MySQL数据库实例中，来分摊整个数据库的压力。

整体的这个概念就是把数据量特别大的某些表中的数据均匀的分布存放在不同的MySQL数据库实例中，我们有时候也称之为分表的操作，将一个大表拆开存放。

网络拓扑图

此次试验的网络拓扑图如下所示，我们基于上面的垂直拆分的基础之上在进行扩充，把之前我们准备的M3和S3也添加进来。

• M1->S1,M2->S2,M3->S3。
• M1用户库，M2订单库，M1和M2是垂直拆分关系。
• M3也是订单库，但是M3和M2是水平拆分关系。
  

---

当我们的ORDER_INFO表数据量太大之后，达到1000万条记录的时候，查询ORDER_INFO的数据有可能就搭建平静，于是我们把ORDER_INFO进行归档，生成一个ORDER_INFO_HISTORY历史订单表。以此来达到降低ORDER_INFO表中数据量，提升最近订单的查询效率。

但是随着时间的推移和订单的增加，历史订单表ORDER_INFO_HISTORY数据量也达到千万级之后，它的查询效率也达到了瓶颈。此时我们就考虑把历史订单表进行水平拆分。让不同的MySQL服务器来分担历史订单表的查询请求。

下面我们就对历史订单表ORDER_INFO_HISTORY进行水平拆分的配置。

配置mycat的schema.xml配置文件

这里对schema.xml的修改主要涉及到以下几点：

1. 增加需要进行水平拆分的表配置，把需要进行水平拆分的表以<table>标签的方式配置在<schema>标签里面。
   这里涉及到字母表的水平拆分。当我们对某一个表进行水平拆分后，那么这个表如果有子表，这些子表也应该做对应的水平拆分。这样才能保证一条数据，字母表中的数据都会水平拆分到同一个数据节点，进而保证我们在发起关联查询的时候，保证每一条数据，都能在同一个节点上找到对应子数据行和母数据行。
2. 增加数据节点dataNode的配置，对应下面新增加的dataHost的值。
3. 增加dataHost的配置，增加我们新增加的数据库节点的连接信息，也就是我们的M3和S3

<!-- 表'order_info_history'进行水平拆分，数据平均分配在 ordernode,ordernode2两个数据节点上。
			其中的'order_info_detail_history'是'order_info_history'的子表，为了关联查询的时候，能够和主表'order_info_history'在同一个数据节点可以关联上，
			它也需要拆分，并且拆分的规则和主表'order_info_history'的规则一样。通过主外键进行关联拆分。
			-->
		<table name="order_info_history" dataNode="ordernode,ordernode2" rule="mod_rule">
			<childTable name="order_info_detail_history" primaryKey="id" joinKey="order_id" parentKey="id" />
		</table>

	<dataNode name="ordernode2" dataHost="orderhost2" database="mysql_db" />
    

<dataHost name="orderhost2" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" 
														dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="hostM3" url="172.19.0.31:3306" user="root" password="root">
			<readHost host="hostS3" url="172.19.0.32:3306" user="root" password="root" />
		</writeHost>
	</dataHost>

最后的结果如下所示：

配置mycat的rule.xml配置文件

• 增加如下配置:

<!-- 新增加的水平拆分的拆分规则 -->
	<tableRule name="mod_rule">
		<rule>
			<columns>customer_code</columns><!-- 根据哪一个字段的值进行水平拆分 -->
			<algorithm>mod-long</algorithm><!-- 拆分的具体实现算法是什么，这里的值，指向该文件中下面的function标签中的具体算法 -->
		</rule>
	</tableRule>

• 修改如下内容为2

<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
		<!-- how many data nodes -->
		<property name="count">2</property><!-- 水平拆分数据到几个数据节点这里就配置几。 -->
	</function>

最后配置如下两图所示：

---

初始化M3节点数据库环境

我们前面的读写分离、垂直拆分的实验都是基于M1、M2节点进行的，还没有M3的参与。现在我们把M3也加入进来进行水平拆分的实验。那么需要把我们使用的数据库在M3上创建一下。

使用如下命令登录M3的命令行终端，然后执行创建mysql_db的数据库，之所以是要创建名为mysql_db的数据库，是因为我们在mycat的schema.xml配置文件中配置的真实的数据库名称就是mysql_db。

mysql -uroot -proot -P3331 -h127.0.0.1

create database mysql_db default charset utf8mb4;

重启mycat服务

当所有的配置文件配置好之后，我们需要重启mycat服务，使我们的修改生效。
使用如下命令重启mycat的容器即可完成mycat服务的重启

docker restart mycat-mycat1

执行实验前的核查工作

在真正开始实验之前，我们下看下目前的mycat和M1、M2、M3、S1、S2、S3各个数据库节点的情况是什么样子的。

• 具体验证如下图所示：

创建表和初始化表中数据

如下SQL需要通过mycat命令行终端进行执行，可以验证一下，执行完成下面的SQL语句之后，是不是会在M2和M3两个节点上面创建了历史订单相关的表。
当然建表语句也可以在M2和M3节点上，分别执行下面的建表的SQL语句，在两个节点上都分别创建历史订单相关的表。但是插入测试数据的时候，一定要通过mycat数据库中间件来插入，这样才能实现水平拆分插入数据到M2和M3的效果。

• 创建表

-- 创建需要信息水平拆分的历史订单相关的表
create table order_info_history(id int primary key auto_increment, customer_code int, status_code varchar(16), hostname varchar(16));
create table order_info_detail_history(id int primary key auto_increment, order_id int, detail_desc varchar(16), hostname varchar(16));

• 插入测试数据

-- 插入历史订单的测试数据，检查是否可以在M2和M3上平均分配下面的插入数据
insert into ORDER_INFO_HISTORY(customer_code,status_code,hostname) values(10,'A',@@hostname);
insert into ORDER_INFO_HISTORY(customer_code,status_code,hostname) values(11,'B',@@hostname);
insert into ORDER_INFO_HISTORY(customer_code,status_code,hostname) values(12,'C',@@hostname);
insert into ORDER_INFO_HISTORY(customer_code,status_code,hostname) values(13,'D',@@hostname);
insert into ORDER_INFO_HISTORY(customer_code,status_code,hostname) values(14,'A',@@hostname);
insert into ORDER_INFO_HISTORY(customer_code,status_code,hostname) values(15,'B',@@hostname);

insert into ORDER_INFO_DETAIL_HISTORY(order_id,detail_desc,hostname) values(1,'order_id=1',@@hostname);
insert into ORDER_INFO_DETAIL_HISTORY(order_id,detail_desc,hostname) values(2,'order_id=2',@@hostname);
insert into ORDER_INFO_DETAIL_HISTORY(order_id,detail_desc,hostname) values(3,'order_id=3',@@hostname);
insert into ORDER_INFO_DETAIL_HISTORY(order_id,detail_desc,hostname) values(4,'order_id=4',@@hostname);
insert into ORDER_INFO_DETAIL_HISTORY(order_id,detail_desc,hostname) values(5,'order_id=5',@@hostname);
insert into ORDER_INFO_DETAIL_HISTORY(order_id,detail_desc,hostname) values(6,'order_id=6',@@hostname);

---

验证水平拆分的结果

创建完成表并插入数据之后，我们看下最后是不是达到我们期望的水平拆分的效果，现在分别去mycat和M1、M2、M3、S1、S2、S3上看表和数据是什么样子的。

• 创建完成表之后，mycat和M1、M2、M3、S1、S2、S3上的表分布情况如下：

• 插入ORDER_INFO_HISTORY水平拆分测试的数据之后，mycat和M1、M2、M3、S1、S2、S3上的表分布情况如下：

• 插入子表ORDER_INFO_DETAIL_HISTORY测试数据之后，mycat和M1、M2、M3、S1、S2、S3上的表分布情况如下：

此时当我尝试去向子表ORDER_INFO_DETAIL_HISTORY中插入数据的时候，出现如下错误：

mysql> insert into ORDER_INFO_DETAIL_HISTORY(order_id,detail_desc,hostname) values(1,'order_id=1',@@hostname);
ERROR 1064 (HY000): can't find (root) parent sharding node for sql:insert into ORDER_INFO_DETAIL_HISTORY(order_id,detail_desc,hostname) values(1,'order_id=1',@@hostname)
mysql>

---

然后我怀疑是不是我没有把ORDER_INFO_DETAIL_HISTORY表的自增主键名称写上，所以我尝试把所有字段都写上，最后还是出现如下错误：

mysql> insert into ORDER_INFO_DETAIL_HISTORY(id,order_id,detail_desc,hostname) values(1,1,'order_id=1',@@hostname);
ERROR 1064 (HY000): can't find (root) parent sharding node for sql:insert into ORDER_INFO_DETAIL_HISTORY(id,order_id,detail_desc,hostname) values(1,1,'order_id=1',@@hostname)
mysql>

---

然后我又怀疑是不是因为我在schema.xml配置文件中，配置子表和主表的关联字段的的时候都是小写字母导致的，把schema.xml中childTable标签中的字段名称都改为大写，然后重启mycat容器，再次执行插入语句，仍然不可以执行成功，还是出现如上的错误提示。

---

没有办法，我最后还是妥协了，我去修改了我的M2、M3、S2、S3的my.cnf配置文件，在这4个文件最后增加了如下的一行代码。
该代码的目的是让MySQL对表名称忽略大小写，不挂你用大写还是小写字母来定义表名称，最后创建后的表名称都是小写。

lower_case_table_names=1

增加完成如下的配置之后，我又通过如下命令重启了我的M2、M3、S2、S3四个MySQL容器，以便是我的修改生效。

docker stop mysql-slave2
docker stop mysql-slave3
docker stop mysql-master2
docker stop mysql-master3

docker start mysql-master2
docker start mysql-master3
docker start mysql-slave2
docker start mysql-slave3

重启完成M2、M3、S2、S3容器之后，我分别登录了这四个MySQL数据库的命令行界面，查看了mysql_db下面的表有哪些，虽然此时可以看到有重启MySQL容器之前创建的名称都是大写字母的表，但是此时你执行任何操作这些大写字母表的SQL语句的时候，都会提示你这些大写字母命名的表不存在。
也就是说此时的MySQL已经不识别在增加lower_case_table_names=1参数之前创建的表了。所以，真正的生产环境中切记慎用这个参数的修改。

---

最后又登录到mycat命令行窗口，重新创建了ORDER_INFO_HISTORY和ORDER_INFO_DETAIL_HISTORY两张表。然后重新执行ORDER_INFO_HISTORY表的insert语句，最后再去执行ORDER_INFO_DETAIL_HISTORY的插入语句才成功了。
看来mycat对于这个表名称大小写的问题确实一个很大的坑。
最后子表ORDER_INFO_DETAIL_HISTORY插入数据后的验证结果如下：

通过上面的验证结果可以看出mycat的水平拆分是正确的。ORDER_INFO_HISTORY和ORDER_INFO_DETAIL_HISTORY都水平的拆分到M2和M3上，并且他们的直接存在关联关系的数据行也都被拆分到了同一个

---

到这里，mycat和MySQL集成配置水平拆分的使用案例就完成了。接下来我会继续分享mycat和MySQL水平拆分的使用案例之全局表的水平拆分，
敬请期待…

---

微信搜索“coder-home”或扫一扫下面的二维码，
关注公众号，第一时间了解更多干货分享，还有各类视频教程资源。扫描它，带走我

---