一、分表分库
1.1 分表分库概念
简单来说,就是指通过某种特定的条件,将我们存放在同一个数据库中的数据分散存放到多个数据库(主机) 上面,以达到分散单台设备负载的效果。
1.2 分表分库的分类
1.2.1 垂直(纵向)切分概念:
按照不同的表(或者 Schema)来切分到不同的数据库(主机)之上,这种切可以称之为数据的垂直(纵向)切分;
具体实现:
一个数据库由很多表的构成,每个表对应着不同的业务,垂直切分是指按照业务将表进行分类,分布到不同 的数据库上面,这样也就将数据或者说压力分担到不同的库上面,如下图:
优点:
• 拆分后业务清晰,拆分规则明确;
• 系统之间整合或扩展容易;
• 数据维护简单。
缺点:
• 部分业务表无法 join,只能通过接口方式解决,提高了系统复杂度;
• 受每种业务不同的限制存在单库性能瓶颈,不易数据扩展跟性能提高;
• 事务处理复杂。
1.2.2 水平(横向)切分概念:
根据 表中的数据的逻辑关系,将同一个表中的数据按照某种条件拆分到多台数据库(主机)上面,这种切分称之为数 据的水平(横向)切分
具体实现:
相对于垂直拆分,水平拆分不是将表做分类,而是按照某个字段的某种规则来分散到多个库之中,每个表中包含一部分数据。简单来说,我们可以将数据的水平切分理解为是按照数据行的切分,就是将表中的某些行切分到一个数据库,而另外的某些行又切分到其他的数据库中,如图:
user1,user2,user3不是表名称,而是表中数据的3部分
优点:
• 拆分规则抽象好,join 操作基本可以数据库做;
• 不存在单库大数据,高并发的性能瓶颈;
• 应用端改造较少 (select * from order limit 100);
• 提高了系统的稳定性跟负载能力。
缺点:
• 拆分规则难以抽象;
• 分片事务一致性难以解决;
• 数据多次扩展难度跟维护量极大;
• 跨库 join 性能较差。
二、mycat实现水平切分
2.1 水平切分规划图
2.2 准备工作
直接使用前面的数据库,根据规划图,要创建不同的数据库,就需要把主从关系去掉。然后创建库,创建表。
1、去掉主从关系
启动mysql主机 ,使用xshell连接,登录mysql,在allsession中执行:
service mysqld status
mysql -uroot -proot
show slave status \G;
stop slave; //注意,不要重启服务器,一旦重启就又会有主从关系
show slave status \G; //看到都是 no no
2、创建数据库
分别在mysqltest 1-3上分别创建:
create database db_sharding1 default char set 'utf8';
create database db_sharding2 default char set 'utf8';
create database db_sharding3 default char set 'utf8';
在mysqltest 4
create database db_sharding4 default char set 'utf8';
create database db_sharding5 default char set 'utf8';
3、分别在不同库创建表
-- 选择库
use db_sharding1;
-- 创建表
create table tb_order(
order_id int primary key auto_increment ,
name varchar(50) comment '订单名称',
client_id int comment '客户编号',
goods_id int comment '商品编号',
add_time datetime comment '添加时间'
);
2.3 mycat具体配置
为了不去破坏前面的读写分离,自动容错配置,重新解压mycat到/usr其他目录,重新配置。
mkdir /usr/sharding
tar -xzvf /usr/localclear/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/sharding/
1、server.xml配置:
vim /usr/sharding/mycat/conf/server.xml
:32 <property name="serverPort">8077</property>
:83
:98
2、schema.xml配置:
vim /usr/sharding/mycat/conf/schema.xml
:8 25dd 删掉多余部分
:12 2yy :13 p 复制两行
:26 2dd :27 3dd 去除多余的
:20 8yy :27 3p 将上图内容复制三份
2.4 分片规则配置
2.4.1 概述:
rule.xml 里面就定义了我们对表进行拆分所涉及到的规则定义。我们可以灵活的对表使用不同的分片算法, 或者对表使用相同的算法但具体的参数不同。这个文件里面主要有 tableRule 和 function 这两个标签。在具体使 用过程中可以按照需求添加 tableRule 和 function。
tableRule 标签 这个标签定义表规则。
function标签:
配置了很多种分片规则,详细分片规则参考官网文档:
2.4.2 按照ID取模配置
配置:
vim /usr/sharding/mycat/conf/rule.xml
:40
:107
vim /usr/sharding/mycat/conf/schema.xml
:7
测试:
先要保证mysql1-4 启动 库db_sharding1-5和表tb_order 都可以正常访问。
启动mycat :
/usr/sharding/mycat/bin/mycat start
使用navicat连接:
-- 向tb_order插入数据
select * from tb_order;
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(1,'order1',11,1001,now());
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(2,'order1',11,1001,now());
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(3,'order1',11,1001,now());
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(4,'order1',11,1001,now());
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(5,'order1',11,1001,now());
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(6,'order1',11,1001,now());
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(7,'order1',11,1001,now());
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(8,'order1',11,1001,now());
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(9,'order1',11,1001,now());
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(10,'order1',11,1001,now());
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(116,'order1',11,1001,now());
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(117,'order1',11,1001,now());
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(118,'order1',11,1001,now());
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(119,'order1',11,1001,now());
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(120,'order1',11,1001,now());
-- 执行查询
select * from tb_order order by order_id;
2.4.3 测试按照自然月分片
vim /usr/sharding/mycat/conf/rule.xml
:58
:121
vim /usr/sharding/mycat/conf/schema.xml
测试
重新启动mycat:
/usr/sharding/mycat/bin/mycat restart
-- 向tb_order插入数据
select * from tb_order;
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(121,'order1',11,1001,'2022-02-11');
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(122,'order1',11,1001,'2022-04-11');
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(123,'order1',11,1001,'2022-05-11');
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(124,'order1',11,1001,'2022-01-11');
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(125,'order1',11,1001,'2022-03-11');
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(126,'order1',11,1001,'2022-05-21');
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(127,'order1',11,1001,'2022-03-21');
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(128,'order1',11,1001,'2022-04-21');
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(129,'order1',11,1001,'2022-1-21');
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(130,'order1',11,1001,'2022-2-21');
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(133,'order1',11,1001,'2022-3-22');
-- 6月和7月数据都会插入失败 因为一共就5个库,
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(134,'order1',11,1001,'2022-06-21');
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(134,'order1',11,1001,'2022-07-21');
2.4.3 全局序列号
1、简介:
在实现分库分表的情况下,数据库自增主键已无法保证自增主键的全局唯一。为此,MyCat 提供了全局sequence,并且提供了包含本地配置和数据库配置等多种实现方式。
2、配置(本地文件方式)
原理:
此方式 MyCAT 将 sequence 配置到文件中,当使用到 sequence 中的配置后,MyCAT 会更新 classpath 中的 sequence_conf.properties 文件中 sequence 当前的值。
实现:
vim /usr/sharding/mycat/conf/sequence_conf.properties
vim /usr/sharding/mycat/conf/server.xml
:16
vim /usr/sharding/mycat/conf/schema.xml
重启mycat:
/usr/sharding/mycat/bin/mycat restart
-- 向tb_order插入数据
select * from tb_order;
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(next value for MYCATSEQ_GLOBAL,'order1',11,1001,now());
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(next value for MYCATSEQ_GLOBAL,'order1',11,1001,now());
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(next value for MYCATSEQ_GLOBAL,'order1',11,1001,now());
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(next value for MYCATSEQ_GLOBAL,'order1',11,1001,now());
insert into tb_order(order_id,name,client_id,goods_id,add_time) values(next value for MYCATSEQ_GLOBAL,'order1',11,1001,now());
选中执行2-3次