一、环境
机器准备:
上面使我们之前搭建了双主双从mysql架构,下面我们基于上面的配置进行分库分表操作
编号 角色 IP地址 1 Master1 192.168.56.20 2 Slave1 192.168.56.21 3 Master2 192.168.56.22 4 Slave2 192.168.56.23
二、上面的是基于一个库的,下面我们再添加一个库
1、修改Master1和Master2的my.cnf文件
2、重新启动Master1和Master2,再新建一下spdb_portal_wechat库
三、分库
1、简介
一个数据库由很多表的构成,每个表对应着不同的业务,垂直切分是指按照业务将表进行分类,分布到不同 的数据库上面,这样也就将数据或者说压力分担到不同的库上面,如下图:
系统被切分成了,用户,订单交易,支付几个模块
2、如何划分表一个问题:在两台主机上的两个数据库中的表,能否关联查询?
答案:不可以关联查询。
分库的原则:有紧密关联关系的表应该在一个库里,相互没有关联关系的表可以分到不同的库里。
3、案例:有如下的几个表,四个表如何分库?客户表分在一个数据库,另外三张都 需要关联查询,分在另外一个数据库。
#客户表 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) );
4、修改Mycat的 schema 配置文件
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="M_SPDB_PZEX" checkSQLschema="false" sqlMaxLimit="100" randomDataNode="dn1" dataNode="dn1"> <!--将customer表放到dn1数据节点--> <table name="customer" dataNode="dn2" ></table> </schema> <dataNode name="dn1" dataHost="host1" database="spdb_pzex" /> <dataNode name="dn2" dataHost="host2" database="spdb_portal_wechat" /> <dataHost name="host1" maxCon="1000" minCon="10" balance="1" 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.56.20:3306" user="dhapp" password="xxxxxx"> <!-- can have multi read hosts --> <readHost host="hostS1" url="192.168.56.21:3306" user="dhapp" password="xxxxxx"></readHost> </writeHost> <!-- can have multi write hosts --> <writeHost host="hostM2" url="192.168.56.22:3306" user="dhapp" password="xxxxxx"> <!-- can have multi read hosts --> <readHost host="hostS2" url="192.168.56.23:3306" user="dhapp" password="xxxxxx"></readHost> </writeHost> </dataHost> <!--创建host2数据节点--> <dataHost name="host2" maxCon="1000" minCon="10" balance="1" 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.56.20:3306" user="dhapp" password="xxxxxx"> <!-- can have multi read hosts --> <readHost host="hostS1" url="192.168.56.21:3306" user="dhapp" password="xxxxxx"></readHost> </writeHost> <!-- can have multi write hosts --> <writeHost host="hostM2" url="192.168.56.22:3306" user="dhapp" password="xxxxxx"> <!-- can have multi read hosts --> <readHost host="hostS2" url="192.168.56.23:3306" user="dhapp" password="xxxxxx"></readHost> </writeHost> </dataHost> </mycat:schema>
5、启动mycat
启动成功!
6、使用mycat开始建表
分别查询spdb_pzex和spdb_portal_wechat库:
spdb_pzex:
![]()
spdb_portal_wechat:
说明我们通过mycat建表后,mycat会自动为我们分到指定的库中
四、分表
1、简介
相对于垂直拆分,水平拆分不是将表做分类,而是按照某个字段的某种规则来分散到多个库之中,每个表中 包含一部分数据。简单来说,我们可以将数据的水平切分理解为是按照数据行的切分,就是将表中的某些行切分 到一个数据库,而另外的某些行又切分到其他的数据库中,如图:
2、选择要拆分的表
MySQL 单表存储数据条数是有瓶颈的,单表达到 1000 万条数据就达到了瓶颈,会影响查询效率,需要进行水平拆分(分表)进行优化。例如:例子中的 orders、orders_detail 都已经达到 600 万行数据,需要进行分表优化。
分表字段,以 orders 表为例,可以根据不同自字段进行分表
![]()
3、下面以orders表为例开始进行分表
修改schema.xml文件,为 orders 表设置数据节点为 dn1、dn2,并指定分片规则为 mod_rule(自定义的名字):
修改rule.xml文件,定义mod_rule规则:
注意:上面我们进行了分库,现在只有spdb_pzex存在orders表,所以我们现在需要在
spdb_portal_wechat库中建立orders表
下面我们重新启动mycat,让配置生效:
在 mycat 里向 orders 表插入数据,INSERT 字段不能省略:
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、dn1、dn2中查看orders表数据,分表成功:
spdb_pzex:
spdb_portal_wechat:
4、Mycat 的分片 “join”
Orders 订单表已经进行分表操作了,和它关联的 orders_detail 订 单详情表如何进行 join 查询,我们要对 orders_detail 也要进行分片操作。Join 的原理如下图:
使用ER表解决上面的字表关联查询的问题,Mycat 借鉴了 NewSQL 领域的新秀 Foundation DB 的设计思路,Foundation DB 创新性的提出了 Table Group 的概念,其将子表的存储位置依赖于主表,并且物理上紧邻存放,因此彻底解决了JION 的效率和性能问 题,根据这一思路,提出了基于E-R 关系的数据分片策略,子表的记录与所关联的父表记录存放在同一个数据分片上。
修改schema.xml文件:
在spdb_portal_wechat库中创建 orders_detail 表,之前分库时,只有spdb_pzex库中有这张表。
重启 Mycat,使配置生效:
访问 Mycat 向 orders_detail 表插入数据:
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);
在mycat、dn1、dn2中运行两个表join语句:
select o.*,od.detail from orders o inner join orders_detail od on o.id=od.order_id;
五、全局表
1、简介
在分片的情况下,当业务表因为规模而进行分片以后,业务与这些附属的字典表之间的关联,就成了比较 棘手的问题,考虑到字典表具有以下几个特性:
- 变动不频繁
- 数据量总体变化不大
- 数据规模不大,很少有超过数十万条记录
鉴于此,Mycat 定义了一种特殊的表,称之为“全局表”,全局表具有以下特性:
- 全局表的插入、更新操作会实时在所有节点上执行,保持各个分片的数据一致性
- 全局表的查询操作,只从一个节点获取
- 全局表可以跟任何一个表进行 JOIN 操作将字典表或者符合字典表特性的一些表定义为全局表,则从另外一个方面,很好的解决了数据JOIN 的难题。通过全局表+基于 E-R 关系的分片策略,Mycat 可以满足 80%以上的企业应用开发
2、修改 schema.xml 配置文件
在spdb_portal_wechat库中创建 dict_order_type表,之前分库时,只有spdb_pzex库中有这张表。
重启 Mycat,促使配置生效:
访问 Mycat 向 dict_order_type 表插入数据
INSERT INTO dict_order_type(id,order_type) VALUES(101,'type1'); INSERT INTO dict_order_type(id,order_type) VALUES(102,'type2');
在以下两个库中查询数据:
spdb_pzex:
![]()
spdb_portal_wechat:
六、常用分片规则
1、取模
此规则为对分片字段求摸运算。也是水平分表最常用规则
2、分片枚举
通过在配置文件中配置可能的枚举 id,自己配置分片,本规则适用于特定的场景,比如有些业务需要按照省份或区县来做保存,而全国省份区县固定的,这类业务使用本条规则。
实现方式如下:
2.1、修改schema.xml配置文件
测试表为orders_ware_info,配置在dn1和dn2节点,规则是新增一个sharding_by_intfile
2.2、修改rule.xml配置文件
2.3、修改partition-hash-int.txt配置文件
表示areacode为110就存到第一个数据节点,为120就存到第二个数据节点
2.4、重启 Mycat使配置生效
使用mycat创建订单归属区域信息表:
CREATE TABLE orders_ware_info ( `id` INT AUTO_INCREMENT comment '编号', `order_id` INT comment '订单编号', `address` VARCHAR(200) comment '地址', `areacode` VARCHAR(20) comment '区域编号', 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');
通过数据库直接查询:
spdb_pzex:
spdb_portal_wechat:
成功将数据分到两个数据节点中。
3、范围约定
此分片适用于,提前规划好分片字段某个范围属于哪个分片。
3.1、修改schema.xml配置文件
上面定义表以及所存储的数据节点和分片规则等
3.2、修改rule.xml配置文件
![]()
3.3、修改autopartition-long.txt配置文件
3.4、重启 Mycat使配置生效
在mycat中创建支付信息表payment_info:
CREATE TABLE payment_info ( `id` INT AUTO_INCREMENT comment '编号', `order_id` INT comment '订单编号', `payment_status` INT comment '支付状态', 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);
分别查看两个host数据节点的数据:
spdb_pzex:
![]()
spdb_portal_wechat:
成功将数据分到两个数据节点中。
4、按日期(天)分片
此规则为按天分片。设定时间格式、范围
4.1、修改schema.xml配置文件
4.2、修改rule.xml配置文件
需要自定义一个分片函数shardingByDate:
4.3、重启 Mycat使配置生效重启
在mycat中创建用户信息表login_info:
CREATE TABLE login_info ( `id` INT AUTO_INCREMENT comment '编号', `user_id` INT comment '用户编号', `login_date` date comment '登录日期', PRIMARY KEY(id) );
插入数据:
INSERT INTO LOGIN_INFO(id,user_id,login_date) VALUES (1,101,'2019-01-01'); INSERT INTO LOGIN_INFO(id,user_id,login_date) VALUES (2,102,'2019-01-02'); INSERT INTO LOGIN_INFO(id,user_id,login_date) VALUES (3,103,'2019-01-03'); INSERT INTO LOGIN_INFO(id,user_id,login_date) VALUES (4,104,'2019-01-04'); INSERT INTO LOGIN_INFO(id,user_id,login_date) VALUES (5,103,'2019-01-05'); INSERT INTO LOGIN_INFO(id,user_id,login_date) VALUES (6,104,'2019-01-06');
分别查看两个host数据节点的数据:
spdb_pzex:
spdb_portal_wechat:
![]()
七、全局序列
1、简介
在实现分库分表的情况下,数据库自增主键已无法保证自增主键的全局唯一。为此,Mycat 提供 了全局 sequence,并且提供了包含本地配置和数据库配置等多种实现方式
1.1、本地文件
此方式 Mycat 将 sequence 配置到文件中,当使用到 sequence 中的配置后,Mycat 会更下 classpath 中的 sequence_conf.properties 文件中 sequence 当前的值。
① 优点:本地加载,读取速度较快
② 缺点:抗风险能力差,Mycat 所在主机宕机后,无法读取本地文件
1.2、数据库方式
利用数据库一个表 来进行计数累加。但是并不是每次生成序列都读写数据库,这样效率太低。Mycat 会预加载一部分号段到 Mycat 的内存中,这样大部分读写序列都是在内存中完成的。如果内存中的号段用完了 Mycat 会再向数据库要一次。
问:那如果 Mycat 崩溃了 ,那内存中的序列岂不是都没了?
是的。如果是这样,那么 Mycat 启动后会向数据库申请新的号段,原有号段会弃用。 也就是说如果 Mycat 重启,那么损失是当前的号段没用完的号码,但是不会因此出现主键重复。
2、下面使用数据库方式测试全局序列
2.1、建库序列脚本
#在 dn1 上创建全局序列表 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; #创建全局序列所需函数 DELIMITER $$ CREATE FUNCTION mycat_seq_currval(seq_name VARCHAR(50)) RETURNS VARCHAR(64) 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 ; DELIMITER $$ CREATE FUNCTION mycat_seq_setval(seq_name VARCHAR(50),VALUE INTEGER) RETURNS VARCHAR(64) DETERMINISTIC BEGIN UPDATE MYCAT_SEQUENCE SET current_value = VALUE WHERE NAME = seq_name; RETURN mycat_seq_currval(seq_name); END $$ DELIMITER ; DELIMITER $$ CREATE FUNCTION mycat_seq_nextval(seq_name VARCHAR(50)) RETURNS VARCHAR(64) DETERMINISTIC BEGIN UPDATE MYCAT_SEQUENCE SET current_value = current_value + increment WHERE NAME = seq_name; RETURN mycat_seq_currval(seq_name); END $$ DELIMITER ; #初始化序列表记录 INSERT INTO MYCAT_SEQUENCE(NAME,current_value,increment) VALUES ('ORDERS', 400000, 100);
![]()
2.2、修改 Mycat 的sequence_db_conf.properties配置
意思是 ORDERS这个序列在dn1这个节点上,具 体dn1节点是哪台机子
2.3、修改server.xml
全局序列类型:0-本地文件,1-数据库方式,2-时间戳方式。此处应该修改成1。
2.4、重启 Mycat使配置生效重启
验证全局序列,登录 Mycat,插入数据:
insert into orders(id,amount,customer_id,order_type) values(next value for MYCATSEQ_ORDERS,1000,101,102);