【数据库】MySQL锁机制、热备、分表

川长思鸟来 2022-10-01 09:52 14阅读 0赞

> 欢迎关注公众号：【**爱编码**】 如果有需要后台回复**2019**赠送**1T的学习资料**哦！！

注：本文大都来自互联网，文字较多，基本是概念，若想深入了解，还需各位自己找文章研究。

## 表锁和行锁机制 ##

#### 表锁（MyISAM和InnoDB） ####

表锁的优势：开销小；加锁快；无死锁 表锁的劣势：锁粒度大，发生锁冲突的概率高，并发处理能力低 加锁的方式：自动加锁。查询操作（SELECT），会自动给涉及的所有表加读锁，更新操作（UPDATE、DELETE、INSERT），会自动给涉及的表加写锁。也可以显示加锁：

共享读锁：lock table tableName read;
    独占写锁：lock table tableName write;
    批量解锁：unlock tables;
    复制代码

#### 什么场景下用表锁 ####

InnoDB默认采用行锁，在未使用索引字段查询时升级为表锁。 即便你在条件中使用了索引字段，MySQL会根据自身的执行计划，考虑是否使用索引(所以explain命令中会有possible\_key 和 key)。 **如果MySQL认为全表扫描效率更高，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁**。 因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

**第一种情况：全表更新**：事务需要更新大部分或全部数据，且表又比较大。若使用行锁，会导致事务执行效率低，从而可能造成其他事务长时间锁等待和更多的锁冲突。

**第二种情况：多表查询**：事务涉及多个表，比较复杂的关联查询，很可能引起死锁，造成大量事务回滚。这种情况若能一次性锁定事务涉及的表，从而可以避免死锁、减少数据库因事务回滚带来的开销。

#### 行锁（InnoDB的行锁） ####

行锁的劣势：开销大；加锁慢；会出现死锁

行锁的优势：锁的粒度小，发生锁冲突的概率低；处理并发的能力强

加锁的方式：自动加锁。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁； 当然我们也可以显示的加锁：

共享锁：select * from tableName where … + lock in share more
    排他锁：select * from tableName where … + for update
    复制代码

#### 行锁优化 ####

1 尽可能让所有数据检索都通过索引来完成，**避免无索引行或索引失效导致行锁升级为表锁**。

2 尽可能避免间隙锁带来的性能下降，减少或使用合理的检索范围。

3 尽可能减少事务的粒度，比如控制事务大小，而从减少锁定资源量和时间长度，从而减少锁的竞争等，提供性能。

4 尽可能低级别事务隔离，隔离级别越高，并发的处理能力越低。

**InnoDB和MyISAM的最大不同点有两个：**

一，InnoDB支持**事务(transaction)**；

二，默认**采用行级锁**。加锁可以保证事务的一致性，可谓是有人(锁)的地方，就有江湖(事务)；我们先简单了解一下事务知识。

#### MySQL 事务 ####

事务是由一组SQL语句组成的逻辑处理单元，事务具有ACID属性。 **原子性**（Atomicity）：事务是一个原子操作单元。在当时原子是不可分割的最小元素，其对数据的修改，要么全部成功，要么全部都不成功。

**一致性**（Consistent）：事务开始到结束的时间段内，数据都必须保持一致状态。

**隔离性**（Isolation）：数据库[系统][Link 1]提供一定的隔离机制，保证事务在不受外部并发操作影响的”独立”环境执行。

**持久性**（Durable）：事务完成后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

#### 事务隔离级别 ####

脏读，不可重复读，幻读，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

#### 更多精彩文章 ####

*  [www.cnblogs.com/zyy1688/p/9…][www.cnblogs.com_zyy1688_p_9]
 *  [www.cnblogs.com/itdragon/p/…][www.cnblogs.com_itdragon_p]

## 双机热备 ##

#### 概念 ####

**双机热备**

[双机热备][Link 2]特指[基于][Link 3]高可用系统中的两台服务器的热备（或高可用），因两机高可用在国内使用较多，故得名双机热备。 从广义上讲，就是对于重要的服务，使用两台服务器，互相备份，共同执行同一服务。当一台服务器出现故障时，可以由另一台服务器承担服务任务，从而在不需要人工干预的情况下，自动保证系统能持续提供服务。

**双机热备和备份的区别**

热备份指的是：**High Available（HA）即高可用，而备份指的是Backup,即数据备份的一种**，这是两种不同的概念，应对的产品也是两种功能上完全不同的产品。

**热备份主要保障业务的连续性**，实现的方法是故障点的转移。**而备份，主要目的是为了防止数据丢失**，而做的一份拷贝，所以备份强调的是数据恢复而不是应用的故障转移。

#### 双机热备分类 ####

按工作中的切换方式分为： **主-备方式（Active-Standby方式）和双主机方式（Active-Active方式）**。

*  **主-备方式**即指的是一台服务器处于某种业务的激活状态（即Active状态），另一台服务器处于该业务的备用状态（即Standby状态)。
 *  **双主机方式**即指两种不同业务分别在两台服务器上互为主备状态（即Active-Standby和Standby-Active状态）。

#### mysql 双机热备工作原理 ####

简单的说就是把 一个服务器上执行过的sql语句在别的服务器上也重复执行一遍， 这样只要两个数据库的初态是一样的，那么它们就能一直同步。 当然这种复制和重复都是mysql自动实现的，我们只需要配置即可。 我们进一步详细介绍原理的细节， 这有一张图：

上图中有两个服务器， 演示了从一个主服务器（master） 把数据同步到从服务器（slave）的过程。 这是一个主-从复制的例子。 主-主互相复制只是把上面的例子反过来再做一遍。 所以我们以这个例子介绍原理。 对于一个mysql服务器， 一般有两个线程来负责复制和被复制。当开启复制之后。

*  1. 作为主服务器Master，  会把自己的每一次改动都记录到 二进制日志 Binarylog 中。 （从服务器会负责来读取这个log， 然后在自己那里再执行一遍。）
 *  2. 作为从服务器Slave， 会用master上的账号登陆到 master上， 读取master的Binarylog,  写入到自己的中继日志 Relaylog， 然后自己的sql线程会负责读取这个中继日志，并执行一遍。  到这里主服务器上的更改就同步到从服务器上了。 在mysql上可以查看当前服务器的主，从状态。 其实就是当前服务器的 Binary（作为主服务器角色）状态和位置。 以及其RelayLog（作为从服务器）的复制进度。

#### mysql 双机热备实现 ####

参考下面各位大神的配置吧，他们写得太好了，太详细了。我就收藏一下。

*  [yunnick.iteye.com/blog/184530…][yunnick.iteye.com_blog_184530]
 *  [www.cnblogs.com/shuidao/p/3…][www.cnblogs.com_shuidao_p_3]
 *  [www.cnblogs.com/fnlingnzb-l…][www.cnblogs.com_fnlingnzb-l]

## 分库分表 ##

基本思想就要把**一个数据库**切分成**多个**部分放到不同的数据库(server)上，从而缓解单一数据库的性能问题。

#### 为什么要分库分表 ####

当一张表的数据达到几千万时，你查询一次所花的时间会变多，如果有联合查询的话，我想有可能会死在那儿了。分表的目的就在于此，减小数据库的负担，缩短查询时间。

#### 垂直切分和水平切分 ####

##### 垂直切分 #####

一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面

优点：

1. 拆分后业务清晰，拆分规则明确。
        2. 系统之间整合或扩展容易。
        3. 数据维护简单。
    复制代码

缺点：

1. 部分业务表无法join，只能通过接口方式解决，提高了系统复杂度。
        2. 受每种业务不同的限制存在单库性能瓶颈，不易数据扩展跟性能提高。
        3. 事务处理复杂。
    复制代码

##### 水平切分 #####

相对于垂直拆分的区别是：垂直拆分是把不同的表拆到不同的数据库中，而水平拆分是把同一个表拆到不同的数据库中。

优点：

1. 不存在单库大数据，高并发的性能瓶颈。
        2. 对应用透明，应用端改造较少。     
        3. 按照合理拆分规则拆分，join操作基本避免跨库。
        4. 提高了系统的稳定性跟负载能力。
    复制代码

缺点：

1. 拆分规则难以抽象。
        2. 分片事务一致性难以解决。
        3. 数据多次扩展难度跟维护量极大。
        4. 跨库join性能较差。
    复制代码

##### 小结 #####

两张方式共同缺点

1. 引入分布式事务的问题。
        2. 跨节点Join 的问题。
        3. 跨节点合并排序分页问题。
    复制代码

目前主要有**两种思路：**

A. **客户端模式**，在每个应用程序模块中配置管理自己需要的一个（或者多个）数据源，直接访问各个 数据库，在模块内完成数据的整合。 
        优点：相对简单，无性能损耗。   
        缺点：不够通用，数据库连接的处理复杂，对业务不够透明，处理复杂。
       B. 通过**中间代理层**来统一管理所有的数据源，后端数据库集群对前端应用程序透明；   
        优点：通用，对应用透明，改造少。   
        缺点：实现难度大，有二次转发性能损失
    复制代码

##### Mycat分库分表 #####

Mycat的架构其实很好理解，Mycat是代理，Mycat后面就是物理数据库。和Web服务器的Nginx类似。对于使用者来说，访问的都是Mycat，不会接触到后端的数据库。

##### 常用的分库分表中间件 #####

###### 简单易用的组件： ######

*  [当当sharding-jdbc][sharding-jdbc]
 *  [蘑菇街TSharding][TSharding]

###### 强悍重量级的中间件： ######

*  [sharding][]
 *  [TDDL Smart Client的方式（淘宝）][TDDL Smart Client]
 *  [Atlas(Qihoo 360)][Atlas_Qihoo 360]
 *  [alibaba.cobar(是阿里巴巴（B2B）部门开发)][alibaba.cobar_B2B]
 *  [MyCAT（基于阿里开源的Cobar产品而研发）][MyCAT_Cobar]
 *  [Oceanus(58同城数据库中间件)][Oceanus_58]
 *  [OneProxy(支付宝首席架构师楼方鑫开发)][OneProxy]
 *  [vitess（谷歌开发的数据库中间件）][vitess]

具体实现可以参考下面这些文章：

*  [www.cnblogs.com/joylee/p/75…][www.cnblogs.com_joylee_p_75]
 *  [www.mycat.io/][www.mycat.io]
 *  [github.com/MyCATApache][github.com_MyCATApache]

##### 更多优质文章 #####

*  [www.cnblogs.com/sunny3096/p…][www.cnblogs.com_sunny3096_p]
 *  [www.cnblogs.com/mfmdaoyou/p…][www.cnblogs.com_mfmdaoyou_p]
 *  [www.cnblogs.com/jshen/p/768…][www.cnblogs.com_jshen_p_768]
 *  [www.cnblogs.com/firstdream/…][www.cnblogs.com_firstdream]

## 最后 ##

如果对 Java、大数据感兴趣请长按二维码关注一波，我会努力带给你们价值。觉得对你哪怕有一丁点帮助的请帮忙点个赞或者转发哦。 关注公众号【爱编码】 ，回复 **2019** 有相关资料哦。

转载于:https://juejin.im/post/5cc301bb6fb9a0323070d88b

[Link 1]: https://link.juejin.im?target=https%3A%2F%2Fwww.2cto.com%2Fos%2F
[www.cnblogs.com_zyy1688_p_9]: https://link.juejin.im?target=https%3A%2F%2Fwww.cnblogs.com%2Fzyy1688%2Fp%2F9983122.html
[www.cnblogs.com_itdragon_p]: https://link.juejin.im?target=https%3A%2F%2Fwww.cnblogs.com%2Fitdragon%2Fp%2F8194622.html
[Link 2]: https://link.juejin.im?target=https%3A%2F%2Fbaike.baidu.com%2Fitem%2F%25E5%258F%258C%25E6%259C%25BA%25E7%2583%25AD%25E5%25A4%2587%2F2394182
[Link 3]: https://link.juejin.im?target=https%3A%2F%2Fbaike.baidu.com%2Fitem%2F%25E5%259F%25BA%25E4%25BA%258E
[yunnick.iteye.com_blog_184530]: https://link.juejin.im?target=https%3A%2F%2Fyunnick.iteye.com%2Fblog%2F1845301
[www.cnblogs.com_shuidao_p_3]: https://link.juejin.im?target=https%3A%2F%2Fwww.cnblogs.com%2Fshuidao%2Fp%2F3551238.html
[www.cnblogs.com_fnlingnzb-l]: https://link.juejin.im?target=https%3A%2F%2Fwww.cnblogs.com%2Ffnlingnzb-learner%2Fp%2F7000898.html
[sharding-jdbc]: https://link.juejin.im?target=https%3A%2F%2Fgithub.com%2Fdangdangdotcom%2Fsharding-jdbc
[TSharding]: https://link.juejin.im?target=https%3A%2F%2Fgithub.com%2Fbaihui212%2Ftsharding
[sharding]: https://link.juejin.im?target=https%3A%2F%2Fgithub.com%2Fgo-pg%2Fsharding
[TDDL Smart Client]: https://link.juejin.im?target=https%3A%2F%2Fgithub.com%2Falibaba%2Ftb_tddl
[Atlas_Qihoo 360]: https://link.juejin.im?target=https%3A%2F%2Fgithub.com%2FQihoo360%2FAtlas
[alibaba.cobar_B2B]: https://link.juejin.im?target=https%3A%2F%2Fgithub.com%2Falibaba%2Fcobar
[MyCAT_Cobar]: https://link.juejin.im?target=http%3A%2F%2Fwww.mycat.org.cn%2F
[Oceanus_58]: https://link.juejin.im?target=https%3A%2F%2Fgithub.com%2F58code%2FOceanus
[OneProxy]: https://link.juejin.im?target=http%3A%2F%2Fwww.cnblogs.com%2Fyouge-OneSQL%2Farticles%2F4208583.html
[vitess]: https://link.juejin.im?target=https%3A%2F%2Fgithub.com%2Fyoutube%2Fvitess
[www.cnblogs.com_joylee_p_75]: https://link.juejin.im?target=http%3A%2F%2Fwww.cnblogs.com%2Fjoylee%2Fp%2F7513038.html
[www.mycat.io]: https://link.juejin.im?target=http%3A%2F%2Fwww.mycat.io%2F
[github.com_MyCATApache]: https://link.juejin.im?target=https%3A%2F%2Fgithub.com%2FMyCATApache
[www.cnblogs.com_sunny3096_p]: https://link.juejin.im?target=https%3A%2F%2Fwww.cnblogs.com%2Fsunny3096%2Fp%2F8595058.html
[www.cnblogs.com_mfmdaoyou_p]: https://link.juejin.im?target=https%3A%2F%2Fwww.cnblogs.com%2Fmfmdaoyou%2Fp%2F7246711.html
[www.cnblogs.com_jshen_p_768]: https://link.juejin.im?target=https%3A%2F%2Fwww.cnblogs.com%2Fjshen%2Fp%2F7682502.html
[www.cnblogs.com_firstdream]: https://link.juejin.im?target=http%3A%2F%2Fwww.cnblogs.com%2Ffirstdream%2Fp%2F6728106.html