MySQL-中间件mycat(二)(2)

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: MySQL-中间件mycat(二)

测试分库

登录mycat登录,创建四个表。

1. [root@master2 ~]# mysql -umycat -p123456 -P8066 -h 192.168.8.10
2. #省略登录信息
3. mysql> use TESTDB;
4. 
5. mysql> CREATE TABLE customer(
6.     -> id INT AUTO_INCREMENT,
7.     -> NAME VARCHAR(200),
8.     -> PRIMARY KEY(id)
9.     -> );
10. Query OK, 0 rows affected (0.02 sec)
11.  OK!
12. 
13. mysql> CREATE TABLE orders(
14.     -> id INT AUTO_INCREMENT,
15.     -> order_type INT,
16.     -> customer_id INT,
17.     -> amount DECIMAL(10,2),
18.     -> PRIMARY KEY(id)
19.     -> );
20. Query OK, 0 rows affected (0.01 sec)
21.  OK!
22. 
23. mysql> CREATE TABLE orders_detail(
24.     -> id INT AUTO_INCREMENT,
25.     -> detail VARCHAR(2000),
26.     -> order_id INT,
27.     -> PRIMARY KEY(id)
28.     -> );
29. Query OK, 0 rows affected (0.01 sec)
30.  OK!
31. 
32. mysql> CREATE TABLE dict_order_type(
33.     -> id INT AUTO_INCREMENT,
34.     -> order_type VARCHAR(200),
35.     -> PRIMARY KEY(id)
36.     -> );
37. Query OK, 0 rows affected (0.03 sec)
38.  OK!

分别登录master1、master2,查看表。

master1为:

1. mysql> show tables;
2. +------------------+
3. | Tables_in_mytest |
4. +------------------+
5. | dict_order_type  |
6. | orders           |
7. | orders_detail    |
8. | tb1              |
9. +------------------+
10. 4 rows in set (0.00 sec)

master2为:

1. mysql> show tables;
2. +------------------+
3. | Tables_in_mytest |
4. +------------------+
5. | CUSTOMER         |
6. +------------------+
7. 1 row in set (0.00 sec)
8. 
9. mysql> desc CUSTOMER;
10. +-------+--------------+------+-----+---------+----------------+
11. | Field | Type         | Null | Key | Default | Extra          |
12. +-------+--------------+------+-----+---------+----------------+
13. | ID    | int(11)      | NO   | PRI | NULL    | auto_increment |
14. | NAME  | varchar(200) | YES  |     | NULL    |                |
15. +-------+--------------+------+-----+---------+----------------+
16. 2 rows in set (0.00 sec)

总结分库

       由上可见,分库成功,简单的来讲,由mycat修改配置文件,dn1指向master1,dn2指向master2。登录mycat创建表,customer表已经指定dn2了,而其他表则创建到dn1。

水平拆分-分表

       相对于垂直拆分,水平拆分不是将表做分类,而是按照某个字段的某种规则来分散到多个库之中, 每个表中 包含一部分数据。简单来说,我们可以将数据的水平切分理解为是按照数据行的切分,就 是将表中的某些行切分到一个数据库,而另外的某些行又切分到其他的数据库中。

实现分表

       选择要拆分的表 MySQL 单表存储数据条数是有瓶颈的,单表达到 1000 万条数据就达到了瓶颈,会影响查询效率, 需要进行水平拆分(分表) 进行优化。 例如:例子中的 orders、 orders_detail 都已经达到600 万行数据,需要进行分表优化。 分表字段 以 orders 表为例,可以根据不同自字段进行分表

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

修改配置文件

       登录mycat服务器,修改schema.xml 配置文件,为 orders 表设置数据节点为 dn1、 dn2, 并指定分片规则为 mod_rule(自定义的名字)

1. [root@mycat conf]# cat schema.xml 
2. <?xml version="1.0"?>
3. <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
4. <mycat:schema xmlns:mycat="http://io.mycat/">
5. 
6.         <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
7.                 <table name="customer" dataNode="dn2"></table>
8.                 <table name="orders" dataNode="dn1,dn2" rule="mod_rule">     #添加
9.    </table>                                  #添加
10.         </schema>
11.         <dataNode name="dn1" dataHost="localhost1" database="mytest" />
12.         <dataNode name="dn2" dataHost="localhost2" database="mytest" />
13.         <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3"
14.                           writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
15.                 <heartbeat>select user()</heartbeat>
16.                 <!-- can have multi write hosts -->
17.                 <writeHost host="hostM1" url="jdbc:mysql://192.168.8.20:3306" user="root"
18.                                    password="123">
19.                 <readHost host="hostS1" url="jdbc:mysql://192.168.8.30:3306" user="root"
20.                                    password="123"></readHost>
21.                 <readHost host="hostS2" url="jdbc:mysql://192.168.8.40:3306" user="root"
22.                                    password="123"></readHost>
23.                 </writeHost>
24. 
25.         </dataHost>
26.         <dataHost name="localhost2" maxCon="1000" minCon="10" balance="0"
27.                           writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
28.                 <heartbeat>select user()</heartbeat>
29.                 <!-- can have multi write hosts -->
30.                 <writeHost host="hostM2" url="jdbc:mysql://192.168.8.50:3306" user="root"
31.                                    password="123">
32.                 </writeHost>
33. 
34.         </dataHost>
35. </mycat:schema>

登录mycat服务器,修改配置文件 rule.xml,具体修改内容看下图红框中内容。

1. [root@mycat conf]# vim rule.xml 
2. #省略部分内容
3.  <tableRule name="mod_rule">                 #修改
4.                 <rule>
5.                         <columns>id</columns>
6.                         <algorithm>mod-long</algorithm>
7.                 </rule>
8.         </tableRule>
9. #省略部分内容
10.         <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
11.                 <!-- how many data nodes -->
12.                 <property name="count">2</property>       #修改
13.         </function>
14. #省略部分内容

#在 rule 配置文件里新增分片规则 mod_rule,并指定规则适用字段为id。

#还有选择分片算法 mod-long(对字段求模运算) , id 对两个节点求模,根据结果分片。

#配置算法 mod-long 参数 count 为 2,两个节点。

测试分表

1. 在数据节点 dn2 上建 orders 表

1. [root@master2 ~]# mysql -uroot -p123
2. #省略部分内容
3. mysql> use mytest;
4. 
5. mysql> CREATE TABLE orders(
6.     -> id INT AUTO_INCREMENT,
7.     -> order_type INT,
8.     -> customer_id INT,
9.     -> amount DECIMAL(10,2),
10.     -> PRIMARY KEY(id)
11.     -> );
12. Query OK, 0 rows affected (0.03 sec)

2. 重启 Mycat,让配置生效

[root@mycat conf]# mycat console

3. 访问 Mycat 实现分片

[root@master2 ~]# mysql -umycat -p123456 -P8066 -h192.168.8.10

4. 在 mycat 里向 orders 表插入下面六行数据, INSERT 字段不能省略

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

5. 分别在mycat、master1、master2中查看orders表数据,分表成功

1. #mycat
2. mysql> select * from orders;
3. +------+------------+-------------+-----------+
4. | id   | order_type | customer_id | amount    |
5. +------+------------+-------------+-----------+
6. |    2 |        101 |         100 | 100300.00 |
7. |    4 |        101 |         101 | 103000.00 |
8. |    6 |        102 |         100 | 100020.00 |
9. |    1 |        101 |         100 | 100100.00 |
10. |    3 |        101 |         101 | 120000.00 |
11. |    5 |        102 |         101 | 100400.00 |
12. +------+------------+-------------+-----------+
13. 6 rows in set (0.01 sec)
14. #master1
15. mysql> select * from orders;
16. +----+------------+-------------+-----------+
17. | id | order_type | customer_id | amount    |
18. +----+------------+-------------+-----------+
19. |  2 |        101 |         100 | 100300.00 |
20. |  4 |        101 |         101 | 103000.00 |
21. |  6 |        102 |         100 | 100020.00 |
22. +----+------------+-------------+-----------+
23. 3 rows in set (0.00 sec)
24. #master2
25. mysql> select * from orders;
26. +----+------------+-------------+-----------+
27. | id | order_type | customer_id | amount    |
28. +----+------------+-------------+-----------+
29. |  1 |        101 |         100 | 100100.00 |
30. |  3 |        101 |         101 | 120000.00 |
31. |  5 |        102 |         101 | 100400.00 |
32. +----+------------+-------------+-----------+
33. 3 rows in set (0.00 sec)

连接查询

1. 登录mycat服务器,修改配置文件,添加下列内容,表示可以外键连接。

1. [root@mycat conf]# cat schema.xml 
2. <?xml version="1.0"?>
3. <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
4. <mycat:schema xmlns:mycat="http://io.mycat/">
5. 
6.         <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
7.                 <table name="customer" dataNode="dn2"></table>
8.                 <table name="orders" dataNode="dn1,dn2" rule="mod_rule">
9.                         <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />    #添加
10.     </table>
11. #省略部分内容

2. 登录master2上创建 orders_detail表。

1. [root@master2 ~]# mysql -uroot -p123
2. 
3. mysql> use mytest;
4. 
5. mysql> CREATE TABLE orders_detail(
6.     -> id INT AUTO_INCREMENT,
7.     -> detail VARCHAR(2000),
8.     -> order_id INT,
9.     -> PRIMARY KEY(id)
10.     -> );
11. Query OK, 0 rows affected (0.01 sec)

3. 重启 Mycat 访问,并登录Mycat 向 orders_detail 表插入数据

[root@mycat conf]# mycat console

重启后登录mycat

1. [root@master2 ~]# mysql -umycat -p123456 -P8066 -h192.168.8.10
2. #省略部分内容
3. mysql> use TESTDB;
4. 
5. mysql> insert into orders_detail(id,detail,order_id) values (1,'detail',1);
6. Query OK, 1 row affected (0.25 sec)
7.  OK!
8. 
9. mysql>  insert into orders_detail(id,detail,order_id) values (2,'detail',2);
10. Query OK, 1 row affected (0.02 sec)
11.  OK!
12. 
13. mysql> insert into orders_detail(id,detail,order_id) values (3,'detail',3);
14. Query OK, 1 row affected (0.01 sec)
15.  OK!
16. 
17. mysql>  insert into orders_detail(id,detail,order_id) values (4,'detail',4);
18. Query OK, 1 row affected (0.01 sec)
19.  OK!
20. 
21. mysql> insert into orders_detail(id,detail,order_id) values (5,'detail',5);
22. Query OK, 1 row affected (0.01 sec)
23.  OK!
24. 
25. mysql> insert into orders_detail(id,detail,order_id) values (6,'detail',6);
26. Query OK, 1 row affected (0.01 sec)
27.  OK!
28. 
29. mysql> select o.*,od.detail from orders as o inner join orders_detail as od on o.id=od.order_id;
30. +------+------------+-------------+-----------+--------+
31. | id   | order_type | customer_id | amount    | detail |
32. +------+------------+-------------+-----------+--------+
33. |    2 |        101 |         100 | 100300.00 | detail |
34. |    4 |        101 |         101 | 103000.00 | detail |
35. |    6 |        102 |         100 | 100020.00 | detail |
36. |    1 |        101 |         100 | 100100.00 | detail |
37. |    3 |        101 |         101 | 120000.00 | detail |
38. |    5 |        102 |         101 | 100400.00 | detail |
39. +------+------------+-------------+-----------+--------+
40. 6 rows in set (0.07 sec)

全局表

       在分片的情况下,当业务表因为规模而进行分片以后,业务表与这些附属的字典表之间的关联, 就成了比较 棘手的问题,考虑到字典表具有以下几个特性:

1、 变动不频繁

2、 数据量总体变化不大

3、数据规模不大,很少有超过数十万条记录 鉴于此, Mycat 定义了一种特殊的表,称之为“全局表”,全局表具有以下特性:

  1. 全局表的插入、更新操作会实时在所有节点上执行,保持各个分片的数据一致性
  2. 全局表的查询操作,只从一个节点获取
  3. 全局表可以跟任何一个表进行 JOIN 操作 将字典表或者符合字典表特性的一些表定义为全局表,则从另外一个方面,很好的解决了数据 JOIN 的难题。 通过全局表+基于 E-R 关系的分片策略, Mycat 可以满足 80%以上的企业应用开发

修改配置文件

登录mycat修改 schema.xml 配置文件

1. [root@mycat conf]# cat schema.xml 
2. <?xml version="1.0"?>
3. <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
4. <mycat:schema xmlns:mycat="http://io.mycat/">
5. 
6.         <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
7.                 <table name="customer" dataNode="dn2"></table>
8.                 <table name="orders" dataNode="dn1,dn2" rule="mod_rule">
9.                         <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
10.     </table>
11.     <table name="dict_order_type" dataNode="dn1,dn2" type="global">
12.   </table>
13.         </schema>
14. #省略部分内容

测试全局表

1. 在master2创建 dict_order_type 表

1. [root@master2 ~]# mysql -uroot -p123
2. #省略部分内容
3. mysql> use mytest
4. 
5. mysql> CREATE TABLE dict_order_type(
6.     -> id INT AUTO_INCREMENT,
7.     -> order_type VARCHAR(200),
8.     -> PRIMARY KEY(id)
9.     -> );
10. Query OK, 0 rows affected (0.01 sec)

2. 重启 Mycat

[root@mycat conf]# mycat console

3. 访问 Mycat 向 dict_order_type 表插入数据

1. [root@master2 ~]# mysql -umycat -p123456 -P8066 -h192.168.8.10
2. #省略部分内容
3. mysql> use TESTDB
4. 
5. mysql> insert into dict_order_type (id,order_type) values (101,'type1');
6. Query OK, 1 row affected (0.22 sec)
7.  OK!
8. 
9. mysql> insert into dict_order_type (id,order_type) values (102,'type2');
10. Query OK, 1 row affected (0.03 sec)
11.  OK!

4. 分别登录mycat、master1、master2,查看表,最终都可以看到id为101,102的数据内容。

1. mysql> select * from dict_order_type;
2. +-----+------------+
3. | id  | order_type |
4. +-----+------------+
5. | 101 | type1      |
6. | 102 | type2      |
7. +-----+------------+
8. 2 rows in set (0.00 sec)

常用分片规则

1、 取模 此规则为对分片字段求摸运算。 也是水平分表最常用规则。 5.1 配置分表中, orders 表采用了此规则。

2、 分片枚举 通过在配置文件中配置可能的枚举 id,自己配置分片,本规则适用于特定的场景,比如有些业务 需要按照省份或区县来做保存,而全国省份区县固定的,这类业务使用本条规则。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
3月前
|
关系型数据库 MySQL Java
MySQL单表膨胀优化之MyCat分库分表
MySQL单表膨胀优化之MyCat分库分表
38 0
|
3月前
|
SQL 关系型数据库 MySQL
②⑩① 【MySQL】什么是分库分表?拆分策略有什么?什么是MyCat?
②⑩① 【MySQL】什么是分库分表?拆分策略有什么?什么是MyCat?
50 0
|
5月前
|
关系型数据库 MySQL 中间件
企业实战(10)基于Maxscale中间件实现Mysql读写分离实战
企业实战(10)基于Maxscale中间件实现Mysql读写分离实战
|
5月前
|
消息中间件 NoSQL 关系型数据库
【Kubernetes部署Shardingsphere、Mycat、Mysql、Redis、中间件Rocketmq、Rabbitmq、Nacos】
【Kubernetes部署Shardingsphere、Mycat、Mysql、Redis、中间件Rocketmq、Rabbitmq、Nacos】
103 0
|
5月前
|
关系型数据库 MySQL Java
为了把MySQL分库分表:MyCAT学明白,我花了近一个月的时间!
MySQL分库分表:MyCAT-问题描述 随着数据库存储的内容越来越多,MySQL主从复制也开始无法存储更多的数据,此时就需要切割表,把一张过大的表切割后分别存储在不同的MySQL中,以便存储更多的内容,承载更多的用户。此阶段出现的典型问题如下: (1)随着互联网的发展,数据的量级也呈指数级增长,从GB到TB再到PB。对数据的各种操作也愈加困难,传统的关系数据库已经无法满足快速查询与插入数据的需求。如何使单表数据量存储更大?甚至期望单表数据量可以“无限扩大”。 (2)MySQL本身是不支持读写分离的,MySQL只支持主从数据复制,读写功能需要重新开发,。有没有一种办法可以不用一次次重写这部
|
5月前
|
关系型数据库 MySQL 中间件
MySQL实现分库分表代码实战(Mango中间件)
MySQL实现分库分表代码实战(Mango中间件)
|
6月前
|
监控 中间件 关系型数据库
MyCAT、ShardingSphere和Mocc这三个中间件的优缺点对比
MyCAT、ShardingSphere和Mocc这三个中间件的优缺点对比
|
8月前
|
SQL 网络协议 关系型数据库
mysql主从和mycat读写分离的安装及验证
最近需要将公司的d、t、p环境的mysql集群做梳理工作,所以就促使了自己对于mysql主从以及mycat读写分离的安装做了如下总结
|
4天前
|
关系型数据库 MySQL Linux
centos7.0环境下安装MySql_8.0.12
centos7.0环境下安装MySql_8.0.12
|
12天前
|
关系型数据库 MySQL 数据库
MYSQL解压版安装笔记
MYSQL解压版安装笔记
13 0

热门文章

最新文章