龙空技术网

mysql分库分表

晨光架构技术 4896

前言:

此时大家对“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 树结构分表