MySQL锁概述

女爷i 2022-12-27 11:05 77阅读 0赞

![160f24c0732b64b7dc349cdca11b453d.png][]

> 我们在操作数据库的时候，可能会由于并发问题而引起的数据的不一致性（数据冲突），如何保证数据并发访问的一致性、有效性，是所有数据库必须解决的一个问题，锁的冲突也是影响数据库并发访问性能的一个重要因素，从这一角度来说，锁对于数据库而言就显得尤为重要。

今天就分享下MySQL相关的最全锁，希望你学习后能更好的掌握数据库锁。

# MySQL锁概述 #

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。

比如：

1.  MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；
2.  InnoDB存储引擎既支持行级锁（ row-level locking），也支持表级锁，但默认情况下是采用行级锁。

MySQL主要的两种锁的特性可大致归纳如下:

![最全MySQL锁讲解：页锁、共享锁、行锁、表锁、悲观锁、乐观锁][MySQL]

*  **表级锁**：开销小，加锁快；不会出现死锁(因为MyISAM会一次性获得SQL所需的全部锁)；锁定粒度大，发生锁冲突的概率最高,并发度最低。

*  **行级锁**：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
 *  **页锁**：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

# 行锁 和 表锁 #

**1.主要是针对锁粒度划分的，一般分为：行锁、表锁、库锁**

（1）行锁：访问数据库的时候，锁定整个行数据，防止并发错误。

（2）表锁：访问数据库的时候，锁定整个表数据，防止并发错误。

**2.行锁 和 表锁 的区别：**

*  表锁：开销小，加锁快，不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低
 *  行锁：开销大，加锁慢，会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高

# 悲观锁 和 乐观锁 #

**（1）悲观锁**：顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。

传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

**（2）乐观锁**： 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。

乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write\_condition机制的其实都是提供的乐观锁。

**（3）悲观锁 和 乐观锁的区别**：

两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果经常产生冲突，上层应用会不断的进行retry，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适。

# 共享锁 #

共享锁指的就是对于多个不同的事务，对同一个资源共享同一个锁。相当于对于同一把门，它拥有多个钥匙一样。就像这样，你家有一个大门，大门的钥匙有好几把，你有一把，你女朋友有一把，你们都可能通过这把钥匙进入你们家，这个就是所谓的共享锁。

刚刚说了，对于悲观锁，一般数据库已经实现了，共享锁也属于悲观锁的一种，那么共享锁在mysql中是通过什么命令来调用呢。通过查询资料，了解到通过在执行语句后面加上lock in share mode就代表对某些资源加上共享锁了。

# 什么时候使用表锁 #

对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中，也可以考虑使用表级锁。

*  第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
 *  第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。

当然，应用中这两种事务不能太多，否则，就应该考虑使用MyISAM表了。

# 表锁和行锁应用场景 #

*  表级锁使用与并发性不高，以查询为主，少量更新的应用，比如小型的web应用；
 *  而行级锁适用于高并发环境下，对事务完整性要求较高的系统，如在线事务处理系统。

**如果觉得有用，点赞支持。**

** 需要MySQL实战书籍、学习资料、页锁、共享锁、行锁、表锁、悲观锁、乐观锁劳烦一键三连查看下方图片获取**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTEzMjIzOA_size_16_color_FFFFFF_t_70][]

# 服务器性能剖析 #

![史上最全MySQL剖析：优化+存储+查询+索引+复制+可扩展+高可用][MySQL 1]

![史上最全MySQL剖析：优化+存储+查询+索引+复制+可扩展+高可用][MySQL 2]

在我们的技术咨询生涯中，最常碰到的三个性能相关的服务请求是:如何确认服务器是否达到了性能最佳的状态、找出某条语句为什么执行不够快，以及诊断被用户描述成“停顿"、“堆积"或者“卡死"的某些间歇性疑难故障。本章将主要针对这三个问题做出解答。我们将提供- - 些工具和技巧来优化整机的性能、优化单条语句的执行速度，以及诊断或者解决那些很难观察到的问题(这些问题用户往往很难知道其根源，有时候甚至都很难察觉到它的存在)。

# 查询性能优化 #

![史上最全MySQL剖析：优化+存储+查询+索引+复制+可扩展+高可用][MySQL 3]

![史上最全MySQL剖析：优化+存储+查询+索引+复制+可扩展+高可用][MySQL 4]

前面是介绍了如何设计最优的库表结构、如何建立最好的索引，这些对于高性能来说是必不可少的。但这些还不够一还需 要合理的设计查询。如果查询写得很糟糕，即使库表结构再合理、索引再合适，也无法实现高性能。

# MySQL高级特性 #

![史上最全MySQL剖析：优化+存储+查询+索引+复制+可扩展+高可用][MySQL 5]

![史上最全MySQL剖析：优化+存储+查询+索引+复制+可扩展+高可用][MySQL 6]

MySQL从5.0和5.1版本开始引入了很多高级特性，例如分区、触发器等，这对有其他关系型数据库使用背景的用户来说可能并不陌生。这些新特性吸引了很多用户开始使用MySQL。不过，这些特性的性能到底如何，还需要用户真正使用过才能知道。这里我们将为大家介绍，在真实的世界中，这些特性表现如何，而不是只简单地介绍参考手册或者宜传材料.上的数据。

> 转发+关注后留意私信回复【架构书籍】即可免费领取史上最全MySQL实战文档

# 优化服务器设置 #

![史上最全MySQL剖析：优化+存储+查询+索引+复制+可扩展+高可用][MySQL 7]

![史上最全MySQL剖析：优化+存储+查询+索引+复制+可扩展+高可用][MySQL 8]

这里我们将解释为MySQL服务器创建一个靠谱的配置文件的过程。这是一个很绕的过程，有很多有意思的关注点和值得关注的思路。关注这些点很有必要，因为创建个好配置的最快方法不是从学习配置项开始，也不是从问哪个配置项应该怎么设置或者怎么修改开始，更不是从检查服务器行为和询问哪个配置项可以提升性能开始。

最好是从理解MySQL内核和行为开始。然后可以利用这些知识来指导配置MySQL.最后，可以将想要的配置和当前配置进行比较，然后纠正重要并且有价值的不同之处。

> 转发+关注后留意私信回复【架构书籍】即可免费领取史上最全MySQL实战文档

# 复制 #

![史上最全MySQL剖析：优化+存储+查询+索引+复制+可扩展+高可用][MySQL 9]

![史上最全MySQL剖析：优化+存储+查询+索引+复制+可扩展+高可用][MySQL 10]

![史上最全MySQL剖析：优化+存储+查询+索引+复制+可扩展+高可用][MySQL 11]

MySQL内建的复制功能是构建基于MySQL的大规模、高性能应用的基础，这类应用使用所谓的“水平扩展”的架构。我们可以通过为服务器配置一个或多个备库生1的方式来进行数据同步。复制功能不仅有利于构建高性能的应用，同时也是高可用性、可扩展性、灾难恢复、备份以及数据仓库等工作的基础。事实上，可扩展性和高可用性通常是相关联的话题，我们会在接下来的三章详细阐述。

> 转发+关注后留意私信回复【架构书籍】即可免费领取史上最全MySQL实战文档

# 可扩展的MySQL #

![史上最全MySQL剖析：优化+存储+查询+索引+复制+可扩展+高可用][MySQL 12]

![史上最全MySQL剖析：优化+存储+查询+索引+复制+可扩展+高可用][MySQL 13]

在此将展示如何构建-一个 基于MySQL的应用，并且当规模变得越来越庞大时，还能保证快速、高效并且经济。有些应用仅仅适用于--台或少数几台服务器，那么哪些可扩展性建议是和这些应用相关的呢?大多数人从不会维护超大规模的系统，井且通常也无法效仿在主流大公司所使用的策略。本章会涵盖这- - 系列的策略。我们已经建立或者协助建立了许多应用，包括从单台或少量服务器的应用到使用上千台服务器的应用。选择一个合适的策略能够大大地节约时间和金钱。MySQL经常被批评很难进行扩展，有些情况下这种看法是正确的，但如果选择正确的架构并很好地实现，就能够非常好地扩展MySQL.但是扩展性并不是-一个很好理解的主题，所以我们先来理清- -些容易混淆的地方。

> 转发+关注后留意私信回复【架构书籍】即可免费领取史上最全MySQL实战文档

# 云端的MySQL #

![史上最全MySQL剖析：优化+存储+查询+索引+复制+可扩展+高可用][MySQL 14]

![史上最全MySQL剖析：优化+存储+查询+索引+复制+可扩展+高可用][MySQL 15]

![史上最全MySQL剖析：优化+存储+查询+索引+复制+可扩展+高可用][MySQL 16]

**应用层优化**

如果在提高MySQL的性能上花费太多时间，容易使视野局限于MySQL本身，而忽略了用户体验。回过头来看，也许可以意识到，或许MySQL已经足够优化，对于用户看到的响应时间而言，其所占的比重已经非常之小，此时应该关注下其他部分了。这是个很不错的观点，尤其是对DBA而言，这是很值得去做的正确的事。但如果不是MySQL，那又是什么导致了问题呢?使用第3章提到的技术，通过测量可以快速而准确地给出答案。如果能顺着应用的逻辑过程从头到尾来剖析，那么找到问题的源头一般来说并不困难。有时，尽管问题在MySQL.上，也很容易在系统的另一部分得到解决。

**备份和恢复**

如果没有提前做好备份规划，也许以后会发现已经错失了- -些最佳的选择。例如，在服务器已经配置好以后，才想起应该使用LVM，以便可以获取文件系统的快照一但这时已经太迟了。在为备份配置系统参数时，可能没有注意到某些系统配置对性能有着重要影响。如果没有计划做定期的恢复演练，当真的需要恢复时，就会发现并没有那么顺利。

**MySQL用户工具**

MySQL服务器发行包中并没有包含针对许多常用任务的工具，例如监控服务器或比较不同服务器间数据的工具。幸运的是，Oracle 的商业版提供了- -些扩展工具，并且MySQL活跃的开源社区和第三方公司也提供了- -系列的工具，降低了自己“重复发明轮子”的需要。

# 总目录 #

![史上最全MySQL剖析：优化+存储+查询+索引+复制+可扩展+高可用][MySQL 17]

**需要MySQL实战书籍、学习资料、页锁、共享锁、行锁、表锁、悲观锁、乐观锁劳烦一键三连查看下方图片获取**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTEzMjIzOA_size_16_color_FFFFFF_t_70][]

[160f24c0732b64b7dc349cdca11b453d.png]: /images/20221120/ce6a6b40194448028f5ccf95fcc1c679.png
[MySQL]: /images/20221120/1090efba5442484d9856ab766d3406cb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTEzMjIzOA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20201209150609665.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTEzMjIzOA==,size_16,color_FFFFFF,t_70
[MySQL 1]: https://img-blog.csdnimg.cn/img_convert/d061295ac8dd79d56e5f72c4c305f176.png
[MySQL 2]: https://img-blog.csdnimg.cn/img_convert/b6069bf826a7fb83d8383c4a0787caeb.png
[MySQL 3]: https://img-blog.csdnimg.cn/img_convert/53fd38ecc41167615484e9a36428aaf5.png
[MySQL 4]: https://img-blog.csdnimg.cn/img_convert/cf22e0003a002a555b290e9863647d55.png
[MySQL 5]: https://img-blog.csdnimg.cn/img_convert/6834e1ad372480ed1ef3e151e142bb8f.png
[MySQL 6]: https://img-blog.csdnimg.cn/img_convert/f8103552f781b1a5386a0441eb6cbb04.png
[MySQL 7]: https://img-blog.csdnimg.cn/img_convert/70fff5db3938918b7144f456d44c1e8e.png
[MySQL 8]: https://img-blog.csdnimg.cn/img_convert/3f92f18fb25130a02489bc5ad86582cf.png
[MySQL 9]: https://img-blog.csdnimg.cn/img_convert/841697ec01e67c357be11df960895449.png
[MySQL 10]: https://img-blog.csdnimg.cn/img_convert/be6ff34b360076958bf0e145584437b7.png
[MySQL 11]: https://img-blog.csdnimg.cn/img_convert/ff37cf1322409b4f3e8ade3c73be5b38.png
[MySQL 12]: https://img-blog.csdnimg.cn/img_convert/cc4c61a356822147b295c73b4b5fd5e9.png
[MySQL 13]: https://img-blog.csdnimg.cn/img_convert/11aacd09a6c7c19b85866f01cda7728b.png
[MySQL 14]: https://img-blog.csdnimg.cn/img_convert/52721bc7d2b97016c001616c1af9afd5.png
[MySQL 15]: https://img-blog.csdnimg.cn/img_convert/c50121c6678dffc6b3acd5e584f82aab.png
[MySQL 16]: https://img-blog.csdnimg.cn/img_convert/3a1b975947f6dc0be3abc5dcdd92f4bf.png
[MySQL 17]: https://img-blog.csdnimg.cn/img_convert/12b995be9e6e9f1bdccced31e37b0c89.png