MySQL主从、集群模式简单介绍,UNION 和 UNION ALL 区别,分库分表

MySQL主从、集群模式简单介绍

主从模式、集群模式，都是在一个项目中使用多个mysql节点进行存储和读取数据。

当单机模式部署，不满足安全性、高可用、高并发等需求的时候，就需要考虑主从模式或者集群模式部署。

1、主从模式 Replication

主从模式，或者是叫主从架构、主从复制，有以下几种常见方案：一主一从、一主多从、多主一从、互为主备、级联复制等。
主数据库必须开启binary log（二进制）功能，因为主从同步所有的操作都是基于二进制文件来完成的。
数据同步模式有：

• 异步模式：主库将事务binlog事件写入到binlog文件中，此时主库只会通知一下dump线程发送这些新的binlog，然后主库就会继续处理提交操作，而此时不会保证这些binlog传到任何一个从库节点上。
• 半同步模式：主库只需要等待至少一个从库节点收到并且Flush binlog到relay-log文件即可，主库不需要等待所有从库给主库反馈。同时，这里只是一个收到的反馈，而不是已经完全执行并且提交的反馈。
• 全同步模式：当主库提交事务之后，所有的从库节点必须全部收到，APPLY并且提交这些事务，然后主库线程才能继续做后续操作。

1、主从复制指的是当主数据库中进行了update、insert、delete操作导致数据发生改变时，变化会实时同步到一个或者多个从数据库（slave）中。

2、默认情况下异步复制、无需维持长连接。

3、通过配置可以选择想要同步的库和表。

2、集群模式

集群最大的优点就是数据实时同步，高可用，每个节点的数据都是同步一致的，不像主从，有时会出现数据不一致，而高可用，任何一个节点宕机都不会影响业务。

集群模式有以下集中常见部署方式：

• 读写分离的集群模式：集群中有的节点只进行写入操作，有的节点只进行读取操作，每个节点的数据都是完全一致的。
• 分片（分库分表）集群模式：集群中所有的节点表结构一致，每个节点存储的数据不一样。分片算法主要有两种，一种是范围法（1-100条数据在节点A，101-200条数据在节点B），另一种是HASH法（对每条数据按照一定的算法分配到不同的存储节点）。
• 读写分离和分片模式组合应用，先进行分片模式部署，然后对每个分片进行读写分离模式部署。

3、主从模式部署注意事项

常用命令（执行命令之前stop服务，执行完再start）：

• 查看主节点状态：show master status\G;
• 查看从节点状态：show slave status\G;
• 停止同步：stop slave;
• 开启同步：start slave;
• 修改Master_Log_File：CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000026’, MASTER_LOG_POS=0;
• 修改master节点信息：CHANGE MASTER TO MASTER_HOST=‘192.168.203.141’, MASTER_PORT=33060,MASTER_USER=‘root’, MASTER_PASSWORD=‘123456’;
• 删除当前节点的 binlog 文件：PURGE BINARY LOGS TO ‘binlog.000001’;
• 数据的操作日志位置：SHOW BINARY LOGS;
• 数据的真实具体位置：SHOW GLOBAL VARIABLES LIKE “%datadir%”;

每个节点的slave_sql_running、Slave_IO_Running两个字段都是YES，集群状态才正常
主服务器查看主节点状态，显示的 File 字段，和从服务器查看从节点状态，显示的 Master_Log_File 字段，必须保持一致。
slave_sql_running为No的话，可能是主从库数据不同步，可以同步一下数据。

数据导出命令（在mysql服务器执行，不需要登录数据库）：

• mysqldump -u[用户名] -h[ip] -p[密码] -P[端口号] --databases 数据库名 --tables 表名 >导出的文件名.sql

数据库导入命令（导入的时候需要指定数据库，保证指定的数据库存在）：

• mysqldump -u[用户名] -h[ip] -p[密码] -P[端口号] < 导入的文件名.sql

UNION 和 UNION ALL 区别

一、区别1：取结果的交集

1、union: 对两个结果集进行并集操作, 不包括重复行,相当于distinct, 同时进行默认规则的排序;

2、union all: 对两个结果集进行并集操作, 包括重复行, 即所有的结果全部显示, 不管是不是重复;

二、区别2：获取结果后的操作

1、union: 会对获取的结果进行排序操作

2、union all: 不会对获取的结果进行排序操作

三、总结

union all只是合并查询结果，并不会进行去重和排序操作，在没有去重的前提下，使用union all的执行效率要比union高

分库分表

链接： MySQL-如何分库分表？一看就懂_mysql分库分表怎么实现-CSDN博客

一、为什么要分库分表
如果一个网站业务快速发展，那这个网站流量也会增加，数据的压力也会随之而来，比如电商系统来说双十一大促对订单数据压力很大，Tps十几万并发量，如果传统的架构（一主多从），主库容量肯定无法满足这么高的Tps，业务越来越大，单表数据超出了数据库支持的容量，持久化磁盘IO,传统的数据库性能瓶颈，产品经理业务·必须做，改变程序，数据库刀子切分优化。数据库连接数不够需要分库，表的数据量大，优化后查询性能还是很低，需要分。

二、什么是分库分表
分库分表方案是对关系型数据库数据存储和访问机制的一种补充。
分库：将一个库的数据拆分到多个相同的库中，访问的时候访问一个库
分表：把一个表的数据放到多个表中，操作对应的某个表就行

三、分库分表的几种方式

1.垂直拆分

(1) 数据库垂直拆分

根据业务拆分，如图，电商系统，拆分成订单库，会员库，商品库

(2)表垂直拆分

根据业务去拆分表，如图，把user表拆分成user_base表和user_info表，use_base负责存储登录，user_info负责存储基本用户信息

垂直拆分特点
1.每个库（表）的结构都不一样
2.每个库（表）的数据至少一列一样
3.每个库（表）的并集是全量数据

垂直拆分优缺点

优点：
1.拆分后业务清晰（专库专用按业务拆分）
2.数据维护简单，按业务不同，业务放到不同机器上

缺点:
1.如果单表的数据量，写读压力大
2.受某种业务决定，或者被限制，也就是说一个业务往往会影响到数据库的瓶颈（性能问题，如双十一抢购）
3.部分业务无法关联join，只能通过java程序接口去调用，提高了开发复杂度

2、水平拆分

(1) 数据库水平拆分

如图，按会员库拆分，拆分成会员1库，会员2库，以userId拆分，userId尾号0-5为1库
6-9为2库，还有其他方式，进行取模，偶数放到1库，奇数放到2库

(2) 表水平拆分

如图把users表拆分成users1表和users2表，以userId拆分，进行取模，偶数放到users1表，奇数放到users2表

水平拆分的其他方式

range来分,每个库一段连续的数据，这个一般是按比如时间范围来的，但是这种一般较少用，因为很容易产生热点问题，大量的流量都打在最新的数据上了,优点：扩容的时候，就很容易，因为你只要预备好，给每个月都准备一个库就可以了，到了一个新的月份的时候，自然而然，就会写新的库了 缺点：大部分的 请求，都是访问最新的数据。实际生产用range，要看场景，你的用户不是仅仅访问最新的数据，而是均匀的访问现在的数据以及历史的数据
hash分发,优点：可以平均分配每个库的数据量和请求压力 缺点：扩容起来比较麻烦，会有一个数据迁移的这么一个过程
水平拆分特点
1.每个库（表）的结构都一样
2.每个库（表）的数据都不一样
3.每个库（表）的并集是全量数据

水平拆分优缺点

优点：
1.单库/单表的数据保持在一定量（减少），有助于性能提高
2.提高了系统的稳定性和负载能力
3.拆分表的结构相同，程序改造较少。

缺点:
1.数据的扩容很有难度维护量大
2.拆分规则很难抽象出来
3.分片事务的一致性问题部分业务无法关联join，只能通过java程序接口去调用

四、分库分表带来的问题
分布式事务
跨库join查询
分布式全局唯一id
开发成本 对程序员要求高

五、分库分表技术如何选型
分库分表的开源框架
jdbc 直连层：shardingsphere、tddl
proxy 代理层：mycat，mysql-proxy（360）

jdbc直连层

jdbc直连层又叫jdbc应用层,是因为所有分片规则，所有分片逻辑，包括处理分布式事务
所有这些问题它都是在应用层，所有项目都是由war包构成的，所有分片都写成了jar包，放到了war包里面，java需要虚拟机去运行的，虚拟机运行的时候就会把war包里面的字节文件进行classLoder加载到jvm内存中，所有分片逻辑都是基于内存方进行操作的

proxy代理层

如图,proxy代理层，所有分片规则，所有分片逻辑，包括处理分布式事务都在mycat写好了,所有分片逻辑都是基于mycat方进行操作

jdbc直连层和proxy代理层优缺点

jdbc直连层性能高，只支持java语言，支持跨数据库
proxy代理层开发成本低，支持跨语言，不支持跨数据库