MySQL（三）

最新推荐文章于 2020-07-18 09:33:01 发布

qq_36091872

最新推荐文章于 2020-07-18 09:33:01 发布

阅读量99

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/qq_36091872/article/details/90605229

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

文章目录

主从复制
ACID
数据库水平切分与垂直切分
总结：大表优化

主从复制

概念

MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

主从复制主要用途

读写分离。在开发工作中，有时候会遇见某个sql 语句需要锁表，导致暂时不能使用读的服务，这样就会影响现有业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
数据实时备份，当系统中某个节点发生故障时，可以方便的故障切换
高可用HA
架构扩展。随着系统中业务访问量的增大，如果是单机部署数据库，就会导致I/O访问频率过高。有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘I/O访问的频率，提高单个机器的I/O性能。

MySQL 主从形式

（1）一主一从
（2）一主多从，提高系统的读性能
在这里插入图片描述
一主一从和一主多从是最常见的主从架构，实施起来简单并且有效，不仅可以实现HA，而且还能读写分离，进而提升集群的并发能力。
（3）多主一从（从5.7开始支持）

多主一从可以将多个mysql数据库备份到一台存储性能比较好的服务器上。
（4）双主复制
双主复制，也就是互做主从复制，每个master既是master，又是另外一台服务器的slave。这样任何一方所做的变更，都会通过复制应用到另外一方的数据库中。
（5）级联复制
在这里插入图片描述
级联复制模式下，部分slave的数据同步不连接主节点，而是连接从节点。因为如果主节点有太多的从节点，就会损耗一部分性能用于replication，那么我们可以让3~5个从节点连接主节点，其它从节点作为二级或者三级与从节点连接，这样不仅可以缓解主节点的压力，并且对数据一致性没有负面影响。

（6）Mysql主从复制的实现原理：
MySQL主从复制涉及到三个线程，一个运行在主节点（log dump thread），其余两个(I/O thread, SQL thread)运行在从节点，如下图所示:
在这里插入图片描述
主节点二进制转储（binary log dump ）线程

当从节点连接主节点时，主节点会创建一个log dump 线程，用于发送bin-log的内容。在读取bin-log中的操作时，此线程会对主节点上的bin-log加锁，当读取完成，甚至在发送给从节点之前，锁会被释放。这个线程不会对事件进行轮询。如果该线程追赶上了主库，它将进入睡眠状态，直到主库发送信号量通知其有新的事件产生时才会被唤醒。

从节点I/O线程

当从节点上执行start slave命令之后，从节点会创建一个I/O线程用来连接主节点，请求主库中更新的bin-log（二进制日志）。I/O线程接收到主节点binary log dump 进程发来的更新之后，保存在本地relay-log（中继日志）中。

从节点SQL线程

SQL线程负责读取relay log中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。

对于每一个主从连接，都需要三个进程来完成。当主节点有多个从节点时，主节点会为每一个当前连接的从节点建一个binary log dump 进程，而每个从节点都有自己的I/O进程，SQL进程。从节点用两个线程将从主库拉取更新和执行分成独立的任务，这样在执行同步数据任务的时候，不会降低读操作的性能。比如，如果从节点没有运行，此时I/O进程可以很快从主节点获取更新，尽管SQL进程还没有执行。如果在SQL进程执行之前从节点服务停止，至少I/O进程已经从主节点拉取到了最新的变更并且保存在本地relay日志中，当服务再次起来之后，就可以完成数据的同步。

要实施复制，首先必须打开Master 端的binary log（bin-log）功能，否则无法实现。

因为整个复制过程实际上就是Slave 从Master 端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。如下图所示：
在这里插入图片描述

复制的基本过程如下：

从节点上的I/O 进程连接主节点，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容；
主节点接收到来自从节点的I/O请求后，通过负责复制的I/O进程根据请求信息读取指定日志指定位置之后的日志信息，返回给从节点。返回信息中除了日志所包含的信息之外，还包括本次返回的信息的bin-log file 的以及bin-log position；从节点的I/O进程接收到内容后，将接收到的日志内容更新到本机的relay log中，并将读取到的binary log文件名和位置保存到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log 的哪个位置开始往后的日志内容，请发给我”；
Slave 的 SQL线程检测到relay-log 中新增加了内容后，会将relay-log的内容解析成在祝节点上实际执行过的操作，并在本数据库中执行。

MySQL 主从复制模式

MySQL 主从复制默认是异步的模式。MySQL增删改操作会全部记录在binary log中，当slave节点连接master时，会主动从master处获取最新的bin log文件。并把bin log中的sql relay。

（1）异步模式（mysql async-mode）
异步模式如下图所示，这种模式下，主节点不会主动push bin log到从节点，这样有可能导致failover的情况下，也许从节点没有即时地将最新的bin log同步到本地。
在这里插入图片描述
（2）半同步模式(mysql semi-sync)
一般情况下，异步复制就已经足够应付了，但由于是异步复制，备库极有可能是落后于主库，特别是极端情况下，我们无法保证主备数据是严格一致的(即使我们观察到Seconds Behind Master 这个值为0)。比如，当用户发起commit命令时，Master并不关心Slave的执行状态，执行成功后，立即返回给用户。试想下，若一个事务提交后，Master成功返回给用户后crash，这个事务的binlog还没来得及传递到Slave，那么Slave相对于Master而言就少了一个事务，此时主备就不一致了。对于要求强一致的业务是不可以接受的，半同步复制就是为了解决数据一致性而产生的。

为什么叫半同步复制？先说说同步复制，所谓同步复制就是一个事务在Master和Slave都执行后，才返回给用户执行成功。这里核心是说Master和Slave要么都执行，要么都不执行，涉及到2PC(2 Phrase Commit)。而MySQL只实现了本地redo-log和binlog的2PC，但并没有实现Master和Slave的2PC，所以不是严格意义上的同步复制。而MySQL半同步复制不要求Slave执行，而仅仅是接收到日志后，就通知Master可以返回了。

这种模式下主节点只需要接收到其中一台从节点的返回信息，就会commit；否则需要等待直到超时时间然后切换成异步模式再提交；这样做的目的可以使主从数据库的数据延迟缩小，可以提高数据安全性，确保了事务提交后，binlog至少传输到了一个从节点上，不能保证从节点将此事务更新到db中。性能上会有一定的降低，响应时间会变长。如下图所示：
在这里插入图片描述
半同步模式不是mysql内置的，从mysql 5.5开始集成，需要master 和slave 安装插件开启半同步模式。

（3）全同步模式
全同步模式是指主节点和从节点全部执行了commit并确认才会向客户端返回成功。

binlog记录格式

MySQL 主从复制有三种方式：基于SQL语句的复制（statement-based replication，SBR），基于行的复制（row-based replication，RBR)，混合模式复制（mixed-based replication,MBR)。对应的binlog文件的格式也有三种：STATEMENT,ROW,MIXED。
（1）基于SQL语句的复制

Statement-base Replication (SBR)就是记录sql语句在bin log中，Mysql 5.1.4及之前的版本都是使用的这种复制格式。
优点是只需要记录会修改数据的sql语句到binlog中，减少了binlog日质量，节约I/O，提高性能。缺点是在某些情况下，会导致主从节点中数据不一致（比如sleep(),now()等）。

（2）基于行的复制

Row-based Relication(RBR)是mysql master将SQL语句分解为基于Row更改的语句并记录在bin
log中，也就是只记录哪条数据被修改了，修改成什么样。
优点是不会出现某些特定情况下的存储过程、或者函数、或者trigger的调用或者触发无法被正确复制的问题。
缺点是会产生大量的日志，尤其是修改table的时候会让日志暴增,同时增加bin log同步时间。也不能通过bin log解析获取执行过的sql语句，只能看到发生的data变更。

（3）混合模式复制

Mixed-format Replication(MBR)，MySQL NDB cluster 7.3 和7.4
使用的MBR。是以上两种模式的混合，对于一般的复制使用STATEMENT模式保存到binlog，对于STATEMENT模式无法复制的操作则使用ROW模式来保存，MySQL会根据执行的SQL语句选择日志保存方式。

GTID复制模式

在传统的复制里面，当发生故障，需要主从切换，需要找到binlog和pos点，然后将主节点指向新的主节点，相对来说比较麻烦，也容易出错。在MySQL 5.6里面，不用再找binlog和pos点，我们只需要知道主节点的ip，端口，以及账号密码就行，因为复制是自动的，MySQL会通过内部机制GTID自动找点同步。

多线程复制（基于库），在MySQL 5.6以前的版本，slave的复制是单线程的。一个事件一个事件的读取应用。而master是并发写入的，所以延时是避免不了的。唯一有效的方法是把多个库放在多台slave，这样又有点浪费服务器。在MySQL 5.6里面，我们可以把多个表放在多个库，这样就可以使用多线程复制。

基于GTID复制实现的工作原理：

主节点更新数据时，会在事务前产生GTID，一起记录到binlog日志中。
从节点的I/O线程将变更的bin log，写入到本地的relay log中。
SQL线程从relay log中获取GTID，然后对比本地binlog是否有记录（所以MySQL从节点必须要开启binary log）。
如果有记录，说明该GTID的事务已经执行，从节点会忽略。
如果没有记录，从节点就会从relay log中执行该GTID的事务，并记录到bin log。
在解析过程中会判断是否有主键，如果没有就用二级索引，如果有就用全部扫描。

ACID

详细介绍可参考：https://www.cnblogs.com/codelifing/articles/5584709.html

原子性

原子性是指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。

一致性

一致性是指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。这是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。

数据库机制层面：数据库层面的一致性是，在一个事务执行之前和之后，数据会符合你设置的约束（唯一约束，外键约束,Check约束等)和触发器设置。这一点是由SQL
SERVER进行保证的。比如转账，则可以使用CHECK约束两个账户之和等于2000来达到一致性目的
对于业务层面来说，一致性是保持业务的一致性。这个业务一致性需要由开发人员进行保证。当然，很多业务方面的一致性，也可以通过转移到数据库机制层面进行保证。

隔离性

多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其它事务运行效果。

事务隔离级别

详细介绍可参考：https://www.cnblogs.com/heyboom/p/9167394.html

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

实现原理
未提交读：

事务在读数据的时候并未对数据加锁。
事务在修改数据的时候只对数据增加行级共享锁。

提交读：

事务对当前被读取的数据加行级共享锁（当读到时才加锁），一旦读完该行，立即释放该行级共享锁；
事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级排他锁，直到事务结束才释放。

可重复读：

事务在读取某数据的瞬间（就是开始读取的瞬间），必须先对其加行级共享锁，直到事务结束才释放；
事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级排他锁，直到事务结束才释放。

可序列化：

事务在读取数据时，必须先对其加表级共享锁，直到事务结束才释放；
事务在更新数据时，必须先对其加表级排他锁，直到事务结束才释放。

MySQL的默认隔离级别就是Repeatable,Oracle默认Read committed

持久性

意味着在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

事务完成后所做的改动都会被持久化，即使发生灾难性的失败。通过日志和同步备份可以在故障发生后重建数据。

数据库水平切分与垂直切分

参考：https://www.cnblogs.com/zr520/p/5449748.html

垂直拆分

就是要把表按模块划分到不同数据库表中（当然原则还是不破坏第三范式），这种拆分在大型网站的演变过程中是很常见的。当一个网站还在很小的时候，只有小量的人来开发和维护，各模块和表都在一起，当网站不断丰富和壮大的时候，也会变成多个子系统来支撑，这时就有按模块和功能把表划分出来的需求。其实，相对于垂直切分更进一步的是服务化改造，说得简单就是要把原来强耦合的系统拆分成多个弱耦合的服务，通过服务间的调用来满足业务需求看，因此表拆出来后要通过服务的形式暴露出去，而不是直接调用不同模块的表，淘宝在架构不断演变过程，最重要的一环就是服务化改造，把用户、交易、店铺、宝贝这些核心的概念抽取成独立的服务，也非常有利于进行局部的优化和治理，保障核心模块的稳定性。

常见的是把一个多字段的大表按常用字段和非常用字段进行拆分，每个表里面的数据记录数一般情况下是相同的，只是字段不一样，使用主键关联

比如原始的用户表是：
在这里插入图片描述
垂直拆分后是：

垂直拆分的优点：可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。

垂直拆分的缺点：主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；

垂直拆分：单表大数据量依然存在性能瓶颈

水平切分

水平拆分，上面谈到垂直切分只是把表按模块划分到不同数据库，但没有解决单表大数据量的问题，而水平切分就是要把一个表按照某种规则把数据划分到不同表或数据库里。例如像计费系统，通过按时间来划分表就比较合适，因为系统都是处理某一时间段的数据。而像SaaS应用，通过按用户维度来划分数据比较合适，因为用户与用户之间的隔离的，一般不存在处理多个用户数据的情况，简单的按user_id范围来水平切分。

通俗理解：水平拆分行，行数据拆分到不同表中，垂直拆分列，表数据拆分到不同表中。

案例

以mysql为例，简单购物系统暂设涉及如下表：
　　1.产品表（数据量10w，稳定）
　　2.订单表（数据量200w，且有增长趋势）
　　3.用户表（数据量100w，且有增长趋势）
以mysql为例讲述下水平拆分和垂直拆分，mysql能容忍的数量级在百万静态数据可以到千万
垂直拆分：
　　解决问题：表与表之间的io竞争
　　不解决问题：单表中数据量增长出现的压力
　　方案：把产品表和用户表放到一个server上
　　　　　订单表单独放到一个server上　　
　　优点：
　　　　　1. 拆分后业务清晰，拆分规则明确。
　　　　　2. 系统之间整合或扩展容易。
　　　　　3. 数据维护简单。
　　缺点：
　　　　　1. 部分业务表无法join，只能通过接口方式解决，提高了系统复杂度。
　　　　　2. 受每种业务不同的限制存在单库性能瓶颈，不易数据扩展跟性能提高。
　　　　　3. 事务处理复杂。
　　　　　
由于垂直切分是按照业务的分类将表分散到不同的库，所以有些业务表会过于庞大，存在单库读写与存储瓶颈，所以就需要水平拆分来做解决。

水平拆分：
　　解决问题：单表中数据量增长出现的压力
　　不解决问题：表与表之间的io争夺
　　方案：用户表通过性别拆分为男用户表和女用户表
　　　　　订单表通过已完成和完成中拆分为已完成订单和未完成订单
　　　　　产品表未完成订单放一个server上
　　　　　已完成订单表盒男用户表放一个server上
　　　　　女用户表放一个server上(女的爱购物)
　　优点：
　　　　　1. 拆分规则抽象好，join操作基本可以数据库做。
　　　　　2. 不存在单库大数据，高并发的性能瓶颈。
　　　　　3. 应用端改造较少。
　　　　　4. 提高了系统的稳定性跟负载能力。
　　缺点：
　　　　　1. 拆分规则难以抽象。
　　　　　2. 分片事务一致性难以解决。
　　　　　3. 数据多次扩展难度跟维护量极大。
　　　　　4. 跨库join性能较差。

垂直切分和水平切分共同的特点和缺点有：
　　1. 引入分布式事务的问题。
　　2. 跨节点Join的问题。
　　3. 跨节点合并排序分页问题。
　　4. 多数据源管理问题。
拆分原则
　　1. 尽量不拆分，架构是进化而来，不是一蹴而就。(SOA)
　　2. 最大可能的找到最合适的切分维度。
　　3. 由于数据库中间件对数据Join 实现的优劣难以把握，而且实现高性能难度极大，业务读取尽量　　　少使用多表Join -尽量通过数据冗余，分组避免数据垮库多表join。
　　4. 尽量避免分布式事务。
　　5. 单表拆分到数据1000万以内。
　　
切分方案：范围、枚举、时间、取模、哈希、指定等

总结：大表优化

https://segmentfault.com/a/1190000006158186

qq_36091872

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL（三）

文章目录主从复制概念主从复制主要用途MySQL 主从形式MySQL 主从复制模式binlog记录格式GTID复制模式主从复制概念 MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者...
复制链接

扫一扫