mysql基于Mycat实现分库分表

最新推荐文章于 2023-10-19 19:35:12 发布

w4xj

最新推荐文章于 2023-10-19 19:35:12 发布

阅读量337

点赞数

分类专栏： mycat 文章标签： mycat 分库分表水平拆分垂直拆分

本文链接：https://blog.csdn.net/sltylzx/article/details/103947660

版权

mycat 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1垂直拆分–分库

一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，
分布到不同 的数据库上面，这样也就将数据或者说压力分担到不同的库上面

在这里插入图片描述

[1].如何划分表

一个问题：在两台主机上的两个数据库中的表，能否关联查询？   
答案：不可以关联查询。同一台主机上的两个库可以通过【库.表名】的方式关联查询

[2].分库的原则：有紧密关联关系的表应该在一个库里，相互没有关联关系的表可以分到不同的库里。

#客户表 rows:20万
CREATE TABLE customer(
id INT AUTO_INCREMENT,
NAME VARCHAR(200),
PRIMARY KEY(id)
);
#订单表 rows:600万
CREATE TABLE orders(
id INT AUTO_INCREMENT,
order_type INT,
customer_id INT,
amount DECIMAL(10,2),
PRIMARY KEY(id)
);
#订单详细表 rows:600万
CREATE TABLE orders_detail(
id INT AUTO_INCREMENT,
detail VARCHAR(2000),
order_id INT,
PRIMARY KEY(id)
);
#订单状态字典表 rows:20
CREATE TABLE dict_order_type(
id INT AUTO_INCREMENT,
order_type VARCHAR(200),
PRIMARY KEY(id)
);

以上四个表如何分库？客户表分在一个数据库，另外三张都需要关联查询，分在另外一个数据库。

[3].修改schema.xml（为了测试效果明显，先将之前做的读写分离还原）

①.在schema节点中新增table标签，设置dataNode为dn2

②.新增dn2的dataNode标签，指定dataHost

③.新增dataHost标签

④.设置负载策略为0

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
                <table name="customer" dataNode="dn2" ></table>
        </schema>
        <dataNode name="dn1" dataHost="host1" database="orders" />
        <dataNode name="dn2" dataHost="host2" database="orders" />
        <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.26.162:3306" user="root"
                                   password="123456">
                </writeHost>
        </dataHost>
        <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM2" url="192.168.26.163:3306" user="root"
                                   password="123456">
                </writeHost>
        </dataHost>
</mycat:schema>

[4].分别在数据节点 dn1、dn2 上分别创建数据库 orders

CREATE DATABASE orders;

[5].启动mycat

./mycat console

[6].登陆mycat

mysql -umycat -p123456 -h 192.168.26.161 -P 8066

[7].切换到TESTDB

mysql> use TESTDB

[8].创建4张表

mysql> #客户表 rows:20万
mysql> CREATE TABLE customer(
    -> id INT AUTO_INCREMENT,
    -> NAME VARCHAR(200),
    -> PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> #订单表 rows:600万
mysql> CREATE TABLE orders(
    -> id INT AUTO_INCREMENT,
    -> order_type INT,
    -> customer_id INT,
    -> amount DECIMAL(10,2),
    -> PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> #订单详细表 rows:600万
mysql> CREATE TABLE orders_detail(
    -> id INT AUTO_INCREMENT,
    -> detail VARCHAR(2000),
    -> order_id INT,
    -> PRIMARY KEY(id)
    -> );
#订单状态字典表 rows:20
Query OK, 0 rows affected (0.03 sec)

mysql> #订单状态字典表 rows:20
mysql> CREATE TABLE dict_order_type(
    -> id INT AUTO_INCREMENT,
    -> order_type VARCHAR(200),
    -> PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.00 sec)

[9].查看表信息，可以看到成功分库

在这里插入图片描述

2. 水平拆分–分表

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

在这里插入图片描述

[1].选择要拆分的表

MySQL 单表存储数据条数是有瓶颈的，单表达到 1000 万条数据就达到了瓶颈，会影响查询效率，需要进行水平拆分（分表）进行优化。例如：例子中的 orders、orders_detail 都已经达到 600 万行数据，需要进行分表优化

[2]. 分表字段

以 orders 表为例，可以根据不同自字段进行分表

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

[3].修改配置件文件 schema.xml

配置table节点orders 表设置数据节点为 dn1、dn2，并指定分片规则为 mod_rule（自定义的名字）

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
                <table name="customer" dataNode="dn2" ></table>
                <table name="orders" dataNode="dn1,dn2" rule="mod_rule" ></table>
        </schema>
        <dataNode name="dn1" dataHost="host1" database="orders" />
        <dataNode name="dn2" dataHost="host2" database="orders" />
        <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.26.162:3306" user="root"
                                   password="123456">
                </writeHost>
        </dataHost>
        <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM2" url="192.168.26.163:3306" user="root"
                                   password="123456">
                </writeHost>
        </dataHost>
</mycat:schema>

[4].修改配置文件 rule.xml

在 rule 配置文件里新增分片规则 mod_rule，并指定规则适用字段为 customer_id，
还有选择分片算法 mod-long（对字段求模运算），customer_id 对两个节点求模，根据结果分片
配置算法 mod-long 参数 count 为 2，两个节点

<tableRule name="mod_rule">
<rule>
<columns>customer_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>

<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">2</property>
</function>

[5].由于我们之前演示分库的时候只在dn1上建了 orders、dict_order_type和orders_detail，所以我们需要在dn2手动建这三张表

#订单表 rows:600万
CREATE TABLE orders(
id INT AUTO_INCREMENT,
order_type INT,
customer_id INT,
amount DECIMAL(10,2),
PRIMARY KEY(id)
);
#订单详细表 rows:600万
CREATE TABLE orders_detail(
id INT AUTO_INCREMENT,
detail VARCHAR(2000),
order_id INT,
PRIMARY KEY(id)
);
#订单状态字典表 rows:20
CREATE TABLE dict_order_type(
id INT AUTO_INCREMENT,
order_type VARCHAR(200),
PRIMARY KEY(id)
);

[6].重启mycat

[7].登陆mycat插入数据测试

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

在这里插入图片描述

[8].Mycat 的分片 “join ”

Orders 订单表已经进行分表操作了，和它关联的 orders_detail 订单详情表如何进行 join 查询。

ER表

Mycat 借鉴了 NewSQL 领域的新秀 Foundation DB 的设计思路，Foundation DB 创新性的提出了 Table Group 的概念，其将子表的存储位置依赖于主表，并且物理上紧邻存放，因此彻底解决了JION 的效率和性能问 题，根据这一思路，提出了基于 E-R 关系的数据分片策略，子表的记录与所关联的父表记录存放在同一个数据分片上。

①.修改schema.xml 配置文件

<childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />

②.重启mycat

③.登陆mycat插入数据

INSERT INTO orders_detail(id,detail,order_id) VALUES(1,'detail1',1);
INSERT INTO orders_detail(id,detail,order_id) VALUES(2,'detail1',2);
INSERT INTO orders_detail(id,detail,order_id) VALUES(3,'detail1',3);
INSERT INTO orders_detail(id,detail,order_id) VALUES(4,'detail1',4);
INSERT INTO orders_detail(id,detail,order_id) VALUES(5,'detail1',5);
INSERT INTO orders_detail(id,detail,order_id) VALUES(6,'detail1',6);

在这里插入图片描述

[9].全局表

在分片的情况下，当业务表因为规模而进行分片以后，业务表与这些附属的字典表之间的关联，
就成了比较 棘手的问题，考虑到字典表具有以下几个特性：
① 变动不频繁
② 数据量总体变化不大
③ 数据规模不大，很少有超过数十万条记录
鉴于此，Mycat 定义了一种特殊的表，称之为“全局表”，全局表具有以下特性：
① 全局表的插入、更新操作会实时在所有节点上执行，保持各个分片的数据一致性
② 全局表的查询操作，只从一个节点获取
③ 全局表可以跟任何一个表进行 JOIN 操作

①.修改 schema.xml 配置文件

配置全局表table

<table name="dict_order_type" dataNode="dn1,dn2" type="global" ></table>

②.重启mycat

③.登陆mycat插入数据

INSERT INTO dict_order_type(id,order_type) VALUES(101,'type1');
INSERT INTO dict_order_type(id,order_type) VALUES(102,'type2');

在这里插入图片描述

w4xj

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql基于Mycat实现分库分表

1垂直拆分–分库一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面[1].如何划分表一个问题：在两台主机上的两个数据库中的表，能否关联查询？答案：不可以关联查询。同一台主机上的两个库可以通过【库.表名】的方式关联查询[2].分库的原则：有紧密关联关系的表应该在一个库里，相互...
复制链接

扫一扫