备案控制台
开发者社区 数据库 文章 正文

MySQL-中间件mycat（二）（2）

2023-06-30 84
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL Serverless，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL Serverless，价值2615元额度，1个月
简介： 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，自己配置分片，本规则适用于特定的场景，比如有些业务 需要按照省份或区县来做保存，而全国省份区县固定的，这类业务使用本条规则。

文章标签：
云数据库 RDS MySQL 版
关系型数据库
MySQL
中间件
算法
数据库
存储
关键词：
中间件mycat
云数据库 RDS MySQL 版mycat
中间件mysql
云数据库 RDS MySQL 版中间件
云数据库 RDS MySQL 版中间件mycat
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
大虾好吃吗
目录
相关文章
流烟默
|
5月前
|
关系型数据库 MySQL Java
MySQL单表膨胀优化之MyCat分库分表
MySQL单表膨胀优化之MyCat分库分表
流烟默
72 0
.29.
|
5月前
|
SQL 关系型数据库 MySQL
②⑩① 【MySQL】什么是分库分表？拆分策略有什么？什么是MyCat？
②⑩① 【MySQL】什么是分库分表？拆分策略有什么？什么是MyCat？
.29.
62 0
非著名运维
|
7月前
|
关系型数据库 MySQL 中间件
企业实战（10）基于Maxscale中间件实现Mysql读写分离实战
企业实战（10）基于Maxscale中间件实现Mysql读写分离实战
非著名运维
60 0
渐暖
|
17天前
|
中间件 关系型数据库 MySQL
【中间件】mycat读写分离分析
【中间件】mycat读写分离分析
渐暖
9 0
我是廖志伟
|
7月前
|
消息中间件 NoSQL 关系型数据库
【Kubernetes部署Shardingsphere、Mycat、Mysql、Redis、中间件Rocketmq、Rabbitmq、Nacos】
【Kubernetes部署Shardingsphere、Mycat、Mysql、Redis、中间件Rocketmq、Rabbitmq、Nacos】
我是廖志伟
141 0
宁在春
|
4月前
|
中间件 关系型数据库 MySQL
史上最详细Docker安装Mycat中间件 | 实现主从的读写分离
史上最详细Docker安装Mycat中间件 | 实现主从的读写分离
宁在春
108 2
游客io6musbi47krs
|
7月前
|
关系型数据库 MySQL Java
为了把MySQL分库分表：MyCAT学明白，我花了近一个月的时间！
MySQL分库分表：MyCAT-问题描述 随着数据库存储的内容越来越多，MySQL主从复制也开始无法存储更多的数据，此时就需要切割表，把一张过大的表切割后分别存储在不同的MySQL中，以便存储更多的内容，承载更多的用户。此阶段出现的典型问题如下： （1）随着互联网的发展，数据的量级也呈指数级增长，从GB到TB再到PB。对数据的各种操作也愈加困难，传统的关系数据库已经无法满足快速查询与插入数据的需求。如何使单表数据量存储更大？甚至期望单表数据量可以“无限扩大”。 （2）MySQL本身是不支持读写分离的，MySQL只支持主从数据复制，读写功能需要重新开发，。有没有一种办法可以不用一次次重写这部
游客io6musbi47krs
136 0
流楚丶格念
|
7月前
|
关系型数据库 MySQL 中间件
MySQL实现分库分表代码实战（Mango中间件）
MySQL实现分库分表代码实战（Mango中间件）
流楚丶格念
109 1
宁波阿成.
|
2天前
|
关系型数据库 MySQL 数据库
docker MySQL删除数据库时的错误（errno: 39）
docker MySQL删除数据库时的错误（errno: 39）
宁波阿成.
10 0
宁波阿成.
|
2天前
|
关系型数据库 MySQL 数据库连接
用Navicat备份Mysql演示系统数据库的时候出：Too Many Connections
用Navicat备份Mysql演示系统数据库的时候出：Too Many Connections
宁波阿成.
9 0

热门文章

最新文章

  • 1
    egg.js 24.17中间件配置
  • 2
    PostgreSQL 聚合函数讲解 - 3 总体|样本 方差, 标准方差
  • 3
    一文读懂AnalyticDB MySQL过滤条件智能下推原理
  • 4
    谈谈MYSQL索引是如何提高查询效率的
  • 5
    mysql主从配置一些注意
  • 6
    源码安装MySQL 5.1 GA
  • 7
    mysql (双主，互主)
  • 8
    Mysql binlog 日志的三种模式
  • 9
    从MySQL 5.5迁移到Mariadb 10.1.14
  • 10
    mysql主从监控脚本：
  • 1
    egg.js 24.17中间件配置
    20
  • 2
    如何解读消息中间件的演变
    1075
  • 3
    38、中间件漏洞解析-IIS6.0
    15
  • 4
    中间件简析
    42
  • 5
    新一代消息中间件—Apache Pulsar
    112
  • 6
    Rancher 系列文章 -K3s Traefik MiddleWare 报错 -Failed to create middleware keys
    36
  • 7
    SAP UI5 Tooling 实战：动手创建自己的 Custom UI5 Server Middleware 试读版
    20
  • 8
    Flutter笔记：getX库中的GetView中间件
    102
  • 9
    Scrapy框架 -- 中间件介绍
    20
  • 10
    常用的消息队列和中间件都有哪些
    210

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • 测试场景（RDS无优惠）
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用Python完成RDS数据开发及分析
  • Excel文件转存到RDS数据库
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • mongodb
  • mariadb
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）