点赞多大胆,就有多大产!开源促使进步,献给每一位技术使用者和爱好者!
干货满满,摆好姿势,点赞发车
路漫漫其修远兮,吾将上下而求索
前言
数据库拆分的理论知识有一篇不错的文章,没有必要再复制一遍,不过还是建议大家先看看这篇文章,再动手实现,我们这篇文章主要是基于Mycat去实现一下数据库拆分
https://www.cnblogs.com/butterfly100/p/9034281.html
垂直拆分-分库
配置mycat的schema配置文件
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!--dataNode配置数据库地址,对应下方dataNode标签name属性值-->
<schema name="MYCATDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<!--新增table标签,配置customer表在dn2上-->
<table name="customer" dataNode="dn2"></table>
</schema>
<!--两个dataNode,database都是orders-->
<dataNode name="dn1" dataHost="host1" database="orders" />
<dataNode name="dn2" dataHost="host2" database="orders" />
<!--库1,修改balance=0,url-->
<dataHost name="host1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.11.201:3306" user="root"
password="123456">
</writeHost>
</dataHost>
<!--库1,修改balance=0,url-->
<dataHost name="host2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM2" url="192.168.11.202:3306" user="root"
password="123456">
</writeHost>
</dataHost>
</mycat:schema>
在两个mysql实例中分别创建orders数据库
CREATE DATABASE orders;
登陆Mycat创建四张表
-- 用户表,假如有20W用户
CREATE TABLE customer(
id INT AUTO_INCREMENT,
NAME VARCHAR(20),
PRIMARY KEY (id)
);
-- 订单表,假如有2000W个订单
CREATE TABLE orders(
id INT AUTO_INCREMENT,
order_type INT,
customer_id INT,
amount DECIMAL(10,2),
PRIMARY KEY (id)
);
-- 订单详情表,数据量和订单表一样
CREATE TABLE order_detail(
id INT AUTO_INCREMENT,
detail VARCHAR(20),
order_id INT,
PRIMARY KEY (id)
);
-- 字典表,数据量假如有20条,对应订单的类型字典,类型说明数字对应字符串,订单表中只需要存储数字即可
CREATE TABLE dict_order_type(
id INT AUTO_INCREMENT,
order_type VARCHAR(20),
PRIMARY KEY (id)
);
查看表
如下图,在Mycat上创建完之后Mycat窗口可以查询出四张表,stt202上有一张customer表,stt203上有三张表,和我们理想效果一样
水平拆分-分库分表
我们发现order和order_detail两张表中数据量非常多,如果存储在同一个节点上的同一个库中性能会受到影响,我们考虑将order表和order_detail表进行拆分,分布式存储全量数据,平均存储在两台节点上。
切片规则
- 我们切分表中数据需要按照一定的规则切分,比如按照时间,id,用户id等
- 如果按照时间切分,老的数据存储在一起,新的数据存储在一起,用户一般查询的是新的数据,所以会导致新数据所在节点的负载要高于旧数据节点
- 如果按照id分区与日期效果类似,一样会导致节点负载不均匀
- 在本例中我们可以按照
customer_id
分配,具体的项目需求大家在具体考虑,尽可能让数据平均分配,节点负载均衡
配置mycat的schema.xml配置文件
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!--dataNode配置数据库地址,对应下方dataNode标签name属性值-->
<schema name="MYCATDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<!--新增table标签,配置customer表在dn2上-->
<table name="customer" dataNode="dn2"></table>
<!--配置orders表分布在dn1和dn2上,分片规则为mod_rule(自定义的)与rule.xml文件一致-->
<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>
<writeHost host="hostM1" url="192.168.11.201: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>
<writeHost host="hostM2" url="192.168.11.202:3306" user="root"
password="123456">
</writeHost>
</dataHost>
</mycat:schema>
配置rule.xml配置文件
<!--新增分片规则-->
<tableRule name="mod_rule">
<rule>
<!--根据表中的customer_id列分配-->
<columns>customer_id</columns>
<!--分片算法,mod-long取模算法是Mycat已经定义好的-->
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<!--Mycat提供的分片算法-->
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes ,配置有几个节点我们有两个改为2-->
<property name="count">2</property>
</function>
在dn2上创建orders表,重启mycat,登陆mycat新增数据到orders表中
-- 我们以前添加,sql语法表名后的字段名可以省略,但是mycat分库分表添加数据不可省略,因为需要指明哪一列数据是customer_id
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);
通过上图可以看出我们在mycat中添加6条数据,在mycat端可以全量查出,但是顺序并不是按照id排序的,如果想要飘絮可以使用order by语句,在stt201和stt202上分别查出3条数据,这样就实现了数据的水平拆分
水平拆分的join关联查询
看上图可以发现我们使用join内关联查询时会提示说order_detail表找不到,我们对orders表进行了切分也需要对orders的子表order_detail也进行切分配置
schema.xml文件
<!--配置orders表分布在dn1和dn2上,分片规则为mod_rule(自定义的)-->
<table name="orders" dataNode="dn1,dn2" rule="mod_rule">
<!--添加childTable 标签
name:子表表名
primaryKey:主键字段
joinKey:两表连接字段
parentKey:父表主键
-->
<childTable name="order_detail" primaryKey="id" joinKey="order_id" parentKey="id"/>
</table>
在dn2上创建order_detail表,重启mycat插入数据再做查询
-- 插入数据和查询都是在mycat端操作
-- 插入数据
INSERT INTO order_detail(id,detail,order_id)VALUES(1,'detail',1);
INSERT INTO order_detail(id,detail,order_id)VALUES(2,'detail',2);
INSERT INTO order_detail(id,detail,order_id)VALUES(3,'detail',3);
INSERT INTO order_detail(id,detail,order_id)VALUES(4,'detail',4);
INSERT INTO order_detail(id,detail,order_id)VALUES(5,'detail',5);
INSERT INTO order_detail(id,detail,order_id)VALUES(6,'detail',6);
-- 连接查询
SELECT * FROM orders o inner join order_detail od on o.id = od.order_id;
到此我们的垂直拆分和水平拆分就告一段落,当然还没有结束,真是XXXX了,咋还没完心态炸裂,不慌大家老规矩喝杯茶继续搞。
全局表
我们的业务表比如orders、order_detail表数据量很多时就需要切分,但是还一些附属表,比如我们这里的dict_order_type(字典表),他们之间也要关联,字典表数据并不多,数据变动不频繁进行切片就没有必要,这种表Mycat中定义为全局表
特点
- 全局表的插、更新操作会实时在所有节点上执行,保持各个分片的一致性
- 全局表的查询操作,只从一个节点获取
- 全局表可以跟任意一个表进行JOIN操作
修改schema.xml配置文件
<table name="orders" dataNode="dn1,dn2" rule="mod_rule">
<childTable name="order_detail" primaryKey="id" joinKey="order_id" parentKey="id"/>
</table>
<!--新建table 标签,type为global-->
<table name="dict_order_type" dataNode="dn1,dn2" type="global"></table>
保存在dn2上创建字典表,重启mycat
INSERT INTO dict_order_type(id,order_type) VALUES(101,'type1');
INSERT INTO dict_order_type(id,order_type) VALUES(102,'type2');
我们查询数据在dn1和dn2都有完整的两条数据,虽然存在数据冗余,但是好在这些表中的数据并不多,不用切分实现JOIN查询
常用分片规则
我们在上边的例子中切分数据时使用的是取模切分
,这里我们说一说其他开发中经常用到的数据切分方式
枚举分片
在配置文件中配置可能用到的枚举ID,自己设置分片,比如按照省份或者区县来做保存,而全国的省份区县是固定的,可以使用在这些场景下
修改schema.xml配置文件
<!--订单地址表-->
<table name="orders_ware_info" dataNode="dn1,dn2" rule="sharding_by_intfile"></table>
修改rule.xml配置文件
<tableRule name="sharding_by_intfile">
<rule>
<columns>areacode</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
......
<function name="hash-int"
class="io.mycat.route.function.PartitionByFileMap">
<!--标识配置文件名称,-->
<property name="mapFile">partition-hash-int.txt</property>
<!--type:0位int型,非0位String型,我们的areacode字段是varchar类型-->
<property name="type">1</property>
<!--defaultNode
默认节点:小于0表示不设置默认节点,大于等于0表示设置默认节点
设置默认节点如果碰到不是别的枚举值,就由它路由到默认节点,如果不设置就报错
-->
<property name="defaultNode">0</property>
</function>
修改partition-hash-int.txt配置文件
110=0
120=1
重启mycat,创建表插入数据
-- 创建表
CREATE TABLE orders_ware_info(
id INT AUTO_INCREMENT,
order_id INT,
address VARCHAR(20),
areacode VARCHAR,
PRIMARY KEY(id)
);
-- 插入数据
INSERT INTO orders_ware_info(id,order_id,address,areacode) VALUES (1,1,'北京','110');
INSERT INTO orders_ware_info(id,order_id,address,areacode) VALUES (2,2,'天津','120');
根据查询结果在mycat上查询是两条数据,在stt201上是北京,在stt202上是天津
范围约定分片
比如我们的用户id,将0-100000、100001-200000等这些按照范围存储,适用于范围提前规定好的场景,我们这里使用一张支付信息表为例
配置schema.xml文件
<!--支付表-->
<table name="payment_info" dataNode="dn1,dn2" rule="auto_sharding_long"></table>
配置rule.xml配置文件
<tableRule name="auto_sharding_long">
<rule>
<columns>order_id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
......
<function name="rang-long"
class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
<property name="defaultNode">0</property>
</function>
修改autopartition-long.txt文件
注意:将原本有的配置删除
0-102 = 0
103-200=1
重启mycat,创建表,插入数据
CREATE TABLE payment_info(
id INT AUTO_INCREMENT,
order_id INT,
payment_status INT,
PRIMARY KEY (id)
);
INSERT INTO payment_info(id,order_id,payment_status) VALUES (1,101,0);
INSERT INTO payment_info(id,order_id,payment_status) VALUES (2,102,1);
INSERT INTO payment_info(id,order_id,payment_status) VALUES (3,103,0);
INSERT INTO payment_info(id,order_id,payment_status) VALUES (4,104,1);
我们可以看到在mycat上查询全量数据,在stt201上展示两条,在stt202上展示两条,并且数据分布也正确
按照日期分片
我们按照天进行划分,设定时间格式、范围
修改schema.xml配置文件
<!--登陆信息表-->
<table name="login_info" dataNode="dn1,dn2" rule="sharding_by_date"></table>
修改rule.xml配置文件
<tableRule name="sharding_by_date">
<rule>
<columns>login_date</columns>
<algorithm>shardingByDate</algorithm>
</rule>
</tableRule>
......
<function name="shardingByDate"
class="io.mycat.route.function.PartitionByDate">
<!--日期格式-->
<property name="dateFormat">yyyy-MM-dd</property>
<!--开始日期-->
<property name="sBeginDate">2020-04-01</property>
<!--结束日期,代表数据到达这个日期的分片后循环从开始分片插入
5号6号又要分片到新的节点上,但是只有2个节点,这时如果配置了结束时间会从开始时间重新分
不会报错
-->
<property name="sEndDate">2020-04-04</property>
<!--分区天数,2天分到一个区-->
<property name="sPartionDay">2</property>
</function>
重启Mycat,创建表插入数据
CREATE TABLE login_info(
id INT AUTO_INCREMENT,
user_id INT,
login_date date,
PRIMARY KEY (id)
);
INSERT INTO login_info(id,user_id,login_date) VALUES (1,101,'2020-04-01');
INSERT INTO login_info(id,user_id,login_date) VALUES (2,102,'2020-04-02');
INSERT INTO login_info(id,user_id,login_date) VALUES (3,103,'2020-04-03');
INSERT INTO login_info(id,user_id,login_date) VALUES (4,104,'2020-04-04');
INSERT INTO login_info(id,user_id,login_date) VALUES (5,103,'2020-04-05');
INSERT INTO login_info(id,user_id,login_date) VALUES (6,104,'2020-04-06');
看到效果,stt201上四条数据因为超过结束日期重新开始分区,stt202上两条数据,大家可以按照自己的想法去操作,看看是否和自己预想的效果一样,好好体会体会!到此我们完成了基于Mycat的数据库切分操作以及常用的切分方式作为参考
全局序列
在分库分表的情况下,数据库自增主键已无法保证自增主键的唯一性,为此Mycat提供了全局序列,提供了本地配置和数据库配置多种实现方式
本地文件
此方式Mycat将sequence配置到文件中,当使用到sequence中的配置后,Mycat会更新该值
- 优势:本地加载,读取速度较快
- 弊端:抗风险性差,mycat宕机无法读取配置文件,重启之后序列会重新开始,造成重复
数据库方式(推荐使用)
利用数据库的一个表来进行累加,并不是每次生成序列都读写数据库,这样太慢,Mycat会预先加载一部分到Mycat内存中,这样大部分读写都在内存中完成,如果内存中号段用完Mycat再向数据库要一次
在dn1上创建MYCAT_SEQUENCE序列表
CREATE TABLE MYCAT_SEQUENCE (
name VARCHAR(50) NOT NULL,
current_value INT NOT NULL,
increment INT NOT NULL DEFAULT 100,
PRIMARY KEY(name)
)ENGINE=InnoDB;
创建函数获取当前sequence的值
DELIMITER $
CREATE FUNCTION mycat_seq_currval(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET utf8
DETERMINISTIC
BEGIN
DECLARE retval VARCHAR(64);
SET retval="-999999999,null";
SELECT concat(CAST(current_value AS CHAR),",",CAST(increment AS CHAR)) INTO retval FROM MYCAT_SEQUENCE WHERE name = seq_name;
RETURN retval;
END $
DELIMITER ;
创建函数设置sequence的值
DELIMITER $
CREATE FUNCTION mycat_seq_setval(seq_name VARCHAR(50),value INTEGER) RETURNS varchar(64) CHARSET utf8
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = value
WHERE name = seq_name;
RETURN mycat_seq_currval(seq_name);
END $
DELIMITER ;
创建函数获取下一个sequence的值
DELIMITER $
CREATE FUNCTION mycat_seq_nextval(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET utf8
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = current_value + increment WHERE name = seq_name;
RETURN mycat_seq_currval(seq_name);
END $
DELIMITER ;
初始化序列表
-- 新增一条数据,序列名为ORDERS,初始值为400000,increment100,这个设置的是Mycat重启之后的值递增100,这个大家根据业务自己设置
INSERT INTO MYCAT_SEQUENCE(NAME,current_value,increment) VALUES('ORDERS',400000,100);
修改schmea.xml文件
<table name="mycat_sequence" primaryKey="name" dataNode="dn1"/>
修改Mycat的sequence_db_conf.properties文件
前边为序列名后边为所在节点,我们序列名为ORDERS就是在dn1上创建的,如果你是在dn2上创建的序列表,则改为dn2
#sequence stored in datanode
GLOBAL=dn1
COMPANY=dn1
CUSTOMER=dn1
ORDERS=dn1
修改server.xml文件
把
<property name="sequnceHandlerType">
改为1,配置使用序列的哪种方式,Mycat提供了三种方式,0为本地文件,1为数据库方式,2为时间戳方式
添加数据
语法就是将ID的值改为next value for MYCATSEQ_SeqName
咱么这里的序列名为ORDERS。
INSERT INTO orders(id,order_type,customer_id,amount) VALUES (next value for MYCATSEQ_ORDERS,101,102,1000);
查询数据
SELECT * FROM orders;
时间戳方式
全局序列ID=64位二进制(42(毫秒)+5(机器ID)+5(业务编码)+12(重复累加))换算成十进制为18位的long类型,每毫秒可以并发12位二进制累加
- 优势:配置简单
- 弊端:太长
自主生成
可以在项目中自己编写生成序列的代码,或者使用redis的incr生成序列,这种方式也行但是需要在程序中进行编码,我们还是推荐使用Mycat自带的全局序列,也就是第二种方式
总结
- 实现制定好切分方式或者说切分计划
- 准备好物理Mysql,这些Mysql应该都是白白的很干净的
- 安装好Mycat,配置Mycat的配置文件
- 启动Mycat创建表插入数据等操作,通过Mycat会将表和数据创建并且插入到真正的物理MySQL中维护
- Mycat提供三种全局序列,解决分布式数据库主键ID唯一问题,我们使用数据库方式
本文若有任何看不懂,或者有错误的地方欢迎大家评论区留言,我时时关注哦