前言:
此时大家对“mysql 树结构分表”大约比较重视,小伙伴们都想要学习一些“mysql 树结构分表”的相关文章。那么小编同时在网上网罗了一些有关“mysql 树结构分表””的相关资讯,希望大家能喜欢,各位老铁们快快来学习一下吧!1、本章面试题
为什么要分库分表,分库的类型,有什么好处
2、知识点
2.1、课程回顾
mysql 主从配置
原理:
1,master 开启二进制日志记录,所有写操作记入日志
2,slave上有io线程负责读master二进制文件写入到中继文件(relay log)
3,slave 上有sql线程,执行中继文件中的写操作,持久化到slave上,达到主从数据一致的目的
mycat 双主双从集群 (m1-s1,m2-s2): 读 selelct 写(DDL(create alter drop)DML(insert update delete))分离好处
1,读写分离(mycat datahost balance 1 m1写操作,m2,s1,s2都是读)
2,负载均衡(读操作不再一台机子执行而是m2,s1,s2一同负载压力)
3,自动故障切换(m1出现故障,自动切换到m2 datahost switchType=2 第一台writeHost如果挂掉,自动切换到还存活第一台writeHost,如m1修复,m2写,m1,s1和s2负责读)
2.2、本章重点
为什么要分库分表
分库有哪些分类,分别有什么优缺点
mycat实现分库分表
3、具体内容
3.1 为什么要分库分表(水平/横向切分)
简单来说,就是指通过某种特定的条件,将我们存放在同一个数据库中的数据分散存放到多个数据库(主机) 上面,以达到分散单台设备负载的效果。
关系型数据库本身比较容易成为系统瓶颈,单机存储容量、连接数、处理能力都有限。当单表的数据量达到1000W或100G以后,由于查询维度较多,即使添加从库、优化索引(btree B树,位图索引,基数select distinct empno from emp; select distinct job from emp; select distinct sex from emp; ),做很多操作时性能仍下降严重。此时就要考虑对其进行切分了,切分的目的就在于减少数据库的负担,缩短查询时间。
3,2 切分模式
3.2.1 垂直(纵向)切分
概念:
按照不同的表(或者 Schema)来切分到不同的数据库(主机)之上,尤其适合各业务之间的耦合度非常低,相互影响很小, 业务逻辑非常清晰的系统。
优点:
拆分后业务清晰,拆分规则明确
系统之间整合或扩展容易
数据维护简单。
缺点:
部分业务表无法 join,只能通过接口方式解决,提高了系统复杂度
受每种业务不同的限制存在单库性能瓶颈,不易数据扩展跟性能提高;
事务处理复杂(分布式事务 XA )
3.2.2 水平(横向)切分
概念:
相对于垂直拆分,水平拆分不是将表做分类,而是按照某个字段的某种规则来分散到多个库之中,每个表中 包含一部分数据。简单来说,我们可以将数据的水平切分理解为是按照数据行的切分,就是将表中的某些行切分 到一个数据库,而另外的某些行又切分到其他的数据库中
优点:
拆分规则抽象好,join 操作基本可以数据库做;
不存在单库大数据,高并发的性能瓶颈;
应用端改造较少;
提高了系统的稳定性跟负载能力。
缺点:
拆分规则难以抽象;
分片事务一致性难以解决;
数据多次扩展难度跟维护量极大;
跨库 join 性能较差
3.3 mycat 分库分表的实现过程
emp 表 数据量非常大,分库分表
准备工作:
本来克隆新的主机,使用前面课程的主机,把主从关系去除
使用xshell 在all session中操作:
servcie my sqld status; 查看mysql服务器是否启动
mysql -uroot -ptiger 登录mysql
mysql>show slave satus \G;
mysql>stop slave; 停止从服务
mysql>show slave satus \G; 再查看 已经io和sql都是no
使用navicat 创建库,创建表:
分别在m1,m2,s1,s2创建库 test_shard_db1,test_shard_db2,test_shard_db3,test_shard_db4,test_shard_db5
其中4和5在s2上
创建表emp:
create table emp(
empno int,
ename varchar(20),
sal double,
hiredate date
);
分别在test_shard_db1,2,3,4,5上执行
mkdir /usr/mycat_shard 创建新目录
解压mycat:
tar -xzvf /root/soft/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/mycat_shard/
cd /usr/mycat_shard/mycat/ 进入目录
server.xml 配置
系统全局配置,用户名,密码,权限和逻辑数据库名称等等
和前面配置一样,不发生变化
32: <property name="serverPort">8066</property>
<user name="root">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
</user>
schema.xml配置
作为 MyCat 中重要的配置文件之一,管理着 MyCat 的逻辑库、表、分片规则、DataNode 以 及 DataSource。
rule.xml
rule.xml 里面就定义了我们对表进行拆分所涉及到的规则定义。我们可以灵活的对表使用不同的分片算法,或者对表使用相同的算法但具体的参数不同。这个文件里面主要有 tableRule 和 function 这两个标签。
测试:
在mycat执行 任意数字%5=[0-4]
-- 1%5=1 test_shard_db2 2%5=2 db3 ... 5%5=0 db1 15%5=0 db1 ....
insert into emp(empno,ename,sal,hiredate) values(1,'scott1',10000,'2020-01-10');
insert into emp(empno,ename,sal,hiredate) values(2,'scott2',10000,'2020-01-10');
insert into emp(empno,ename,sal,hiredate) values(3,'scott3',10000,'2020-01-10');
insert into emp(empno,ename,sal,hiredate) values(4,'scott4',10000,'2020-01-10');
insert into emp(empno,ename,sal,hiredate) values(5,'scott5',10000,'2020-01-10');
insert into emp(empno,ename,sal,hiredate) values(15,'scott15',10000,'2020-01-10');
insert into emp(empno,ename,sal,hiredate) values(16,'scott16',10000,'2020-01-10');
insert into emp(empno,ename,sal,hiredate) values(17,'scott17',10000,'2020-01-10');
insert into emp(empno,ename,sal,hiredate) values(18,'scott18',10000,'2020-01-10');
insert into emp(empno,ename,sal,hiredate) values(19,'scott19',10000,'2020-01-10');
查看数据分布情况和原来分析是对应 db1 ( 5,15) db2(1,16) db3(2,17)......验证了按照rule1的规则,按照emp的empno列的值,对count=5取余分片
select * from emp;
按时间分片
rule.xml
schema.xml
重启mycat
测试:
-- 2020-01 db1 2020-02 db2 .... 2020-05 db5 2020-06 插入不了
-- db1
insert into emp(empno,ename,sal,hiredate) values(6,'scott6',10000,'2020-01-10');
insert into emp(empno,ename,sal,hiredate) values(7,'scott7',10000,'2020-01-11');
-- db2
insert into emp(empno,ename,sal,hiredate) values(8,'scott8',10000,'2020-02-10');
insert into emp(empno,ename,sal,hiredate) values(9,'scott9',10000,'2020-02-11');
-- db3
insert into emp(empno,ename,sal,hiredate) values(10,'scott10',10000,'2020-03-11');
-- db4
insert into emp(empno,ename,sal,hiredate) values(11,'scott11',10000,'2020-04-11');
-- db5
insert into emp(empno,ename,sal,hiredate) values(12,'scott12',10000,'2020-05-11');
-- db1
insert into emp(empno,ename,sal,hiredate) values(13,'scott13',10000,'2020-01-21');
-- 不能插入
insert into emp(empno,ename,sal,hiredate) values(14,'scott13',10000,'2020-06-21');
插入时一句一句执行 或者去掉 -- 注释一起执行也可以
。。。
参考官网第10章:10.5 Mycat 分片规则
全局序列号(参考官网第9章)等于mysql的自增功能
在实现分库分表的情况下,数据库自增主键已无法保证自增主键的全局唯一。为此,MyCat 提供了全局 sequence,并且提供了包含本地配置和数据库配置等多种实现方式
本地文件方式:
cd /usr/mycat_shard/mycat/
vim conf/sequence_conf.properties
修改 server.xml
vim conf/server.xml
测试:
按照id分片:
重启服务
./bin/mycat restart
在mycat的navicat链接中执行:
新版本,使用序列式需要加上单引号
insert into emp(empno,ename,sal,hiredate) values(next value for MYCATSEQ_GLOBAL,'scott1001',10000,'2020-01-21');
insert into emp(empno,ename,sal,hiredate) values(next value for MYCATSEQ_GLOBAL,'scott1002',10000,'2020-01-21');
insert into emp(empno,ename,sal,hiredate) values(next value for MYCATSEQ_GLOBAL,'scott1003',10000,'2020-01-21');
insert into emp(empno,ename,sal,hiredate) values(next value for MYCATSEQ_GLOBAL,'scott1004',10000,'2020-01-21');
insert into emp(empno,ename,sal,hiredate) values(next value for MYCATSEQ_GLOBAL,'scott1005',10000,'2020-01-21');
insert into emp(empno,ename,sal,hiredate) values(next value for MYCATSEQ_GLOBAL,'scott1006',10000,'2020-01-21');
.....
可以看到不同数据库,分别存放了id为1001-1006的数据,全局自增,类似mysql单表的id自增
4、本章总结
4.1 总结本章知识点
错误解决:
vim /usr/mycat_shard/mycat/conf/wrapper.conf
加入:
wrapper.startup.timeout=300
重启查看错误,就看到了真正的错误
4.2 面试题答案
4.3 预习下一章重点
标签: #mysql 树结构分表