《高性能MySQL》读书笔记 1--mysql介绍

本文深入探讨MySQL的存储引擎架构，以及并发控制中的锁机制，包括悲观锁、乐观锁、共享锁、排他锁等多种类型，解析锁的兼容性和死锁情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一边读书一边写点笔记加深印象

逻辑架构

Mysql 最重要、最与众不同的特性是它的存储引擎架构，这种架构的设计键查询处理以及其它系统任务和数据的存储/提取相分离的设计可以在使用时根据性能、特性，以及其他需求来选择数据存储的方式。

下面是mysql的服务器逻辑架构图
在这里插入图片描述
第一层：是一些服务比如：连接处理、授权认证、安全等
第二层：包含mysql的大部分核心功能包括查询解析、分析、优化、缓存以及所有的内置函数，并且所有的跨存储引擎功能都在这一层实现：存储过程、触发器、视图等。
第三层：包含存储引擎，负责mysql中的数据存储与提取。包含一些接口用于执行“开始一个事务”，“根据主键提取一行记录”。存储引擎不会去解析SQL（InnoDB例外，它会解析外键定义），不同引擎也不会互相通信。

具体些的图如下：
在这里插入图片描述

并发与各种锁的概念

在学习数据库的过程中一定会遇到一个概念——“锁”。比如：叫你好好学习的——读写锁（别名共享锁排他锁），不知道怎么锁表格的——页锁，表锁，行锁，涉及生死的——死锁，还有骚出心情的悲观锁乐观锁，还有意向锁，插入意向锁，更新锁，并发锁，间隙锁等等乱七八糟的锁。

对于这些乱七八糟的锁，就先从最基础的开始吧。“锁”出现的原因是为了应付并发情况。什么是并发呢？拿非洲草原上的狮子做个比喻：就是早先时候只有一头狮子（请求），它吃水牛（访问表）就想吃哪里就吃哪里，想什么时候吃就什么时候吃，但慢慢的发展起来后，就有一群狮子（并发请求）了。大家都想吃，都想先吃，都想多吃。怎么办呢？那就亮剑吧——当然动物是可以这么搞的，但我们高贵的计算机还是有更好的办法的，毕竟在电路里干架有辱斯文。我们可以用等级森严的封建主义区分先来后到，势力范围。比如，挥舞着《锁的语录》互相攻击：表锁——一整头牛都是我的，页锁——这个牛后腿到牛尾巴都是我的，行锁——这个牛腿是我的，意向锁——这个位置很好我预定了没预定不准站这里吃，……

有了并发自然就有并发控制：当程序中可能出现并发的情况时，我们就需要通过一定的手段来保证在并发情况下数据的准确性，通过这种手段保证了当用户和其他用户一起操作时，所得到的结果和他单独操作时的查询的结果是一样的。这种手段就叫做并发控制。并发控制的目的是保证一个用户的工作不会对另一个用户的工作产生不合理的影响。并发控制在数据库中的一大体现就是“锁”。

下面来具体谈谈各种锁：

锁的分类：

影响锁的范围属性：表级锁、行级锁、页级锁
锁机制的实现方式不同：乐观锁、悲观锁

Innodb上面共有7种基本锁分别是：
1.共享锁（读锁S）[行级锁][悲观锁] / 排他锁（写锁X）[行级锁][悲观锁]
2.意向锁（IS）[表级锁] [悲观锁] / 意向排他锁（IX）[表级锁][悲观锁]
3.记录锁(行锁)(Record Locks) [行级锁][悲观锁]
4 间隙锁(Gap Locks) [行级锁][悲观锁]
5.临键锁(Next-key Locks) [行级锁][悲观锁]
6.插入意向锁(Insert Intention Locks) [行级锁][悲观锁]
7. 自增锁(Auto-inc Locks) [表级锁][悲观锁]
临键锁是记录锁和间隙锁的组合。

锁的分类解析

悲观锁，乐观锁
以下部分内容参考：https://www.easemob.com/news/2734
实际上并没有这两种锁，只是我们把两种不同的机制称为乐观锁和悲观锁。跟我们把牛分为小牛和大牛，但是根本么有大小牛一样。其实，更准确的说法是乐观并发控制与悲观并发控制。虽然字数多些，不过看的明白。

悲观锁：当我们要对一个数据库中的一条数据进行修改的时候，为了避免同时被其他人修改，最好的办法就是直接对该数据进行加锁以防止并发。这种借助数据库锁机制在修改数据之前先锁定，再修改的方式被称之为悲观并发控制(又名“悲观锁”，Pessimistic Concurrency Control，缩写“PCC”)。

乐观锁( Optimistic Locking ) 是相对悲观锁而言的，乐观锁假设数据一般情况下不会造成冲突。所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。

用一个场景来说明的话就是：
餐厅只剩最后一块牛排，两桌客人都同时点了这份牛排。

悲观锁：两桌的服务员同时大吼一声：“这块牛排这桌定了！”（上锁），根据谁喊得比较早比较大谁先获得这份牛排，然后去后厨（表）通知下单。

乐观锁：两桌服务员记下客户的需求，都跑道后厨去下单。谁来的快这单就是谁的，没抢到的自己和顾客解释。

正式运用的一些场景：
悲观锁实现流程：

1.在对记录进行修改前，先尝试为该记录加上排他锁(exclusive locking)。
2.如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。具体响应方式由开发者根据实际需要决定。
3.如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。
4.其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常。
示例：

// begin; 
 //查询出商品库存信息 (加锁)
 select quantity from items where id=1 for update; 
 //修改商品库存为2 
 update items set quantity=2 where id = 1; 
 //提交事务 
 commit;

for update : 仅适用于InnoDB，并且必须开启事务，在begin与commit之间才生效。
InnoDB默认是行级别的锁，当有明确指定的主键时候是行级锁，否则表级别。
其对应的主键与表级别的关系是
1.明确指定主键，并且有此记录，行级锁
2.明确指定主键/索引，若查无此记录，无锁
3.无主键/索引，表级锁
4.主键/索引不明确，表级锁
具体可以阅读这篇博客讲的非常好：https://blog.youkuaiyun.com/u011957758/article/details/75212222

乐观锁实现流程：
1、冲突检测
2、数据更新
有一种比较典型的实现方式就是 Compare and Swap(CAS)。
主题思想是：不锁定表的情况下，利用业务的控制来解决并发问题

//查询出商品库存信息
select quantity from items where id=1 
//修改商品库存为2 
update items set quantity=2 where id=1 and quantity = 3;

但是这个流程会出现问题，当两个线程同时请求的时候，一个会请求成 3->2 ,另一个也会请求成 3->2 最终结果是2，但正确的结果应当是1，这个称作ABA问题。

加个版本号或者时间戳可以解决这个问题：
例如：

//查询出商品信息
select version from items where id=1 
//修改商品库存为2 
update items set quantity=2,version = 3 where id=1 and version = 2;

每次操作都会带上版本号，版本号一致的话就能执行了。这样就能有效避开ABA问题

也可以通过减低锁粒度的方式进行解决：

//修改商品库存 
update item  set quantity=quantity - 1  where id = 1 and quantity - 1 > 0

以上 update 语句，在执行过程中，会在一次原子操作中自己查询一遍 quantity 的值，并将其扣减掉 1。

表级锁、页级锁、行级锁

什么是锁粒度呢？前面吃了这么多这回用公共厕所来做个比喻，所谓的锁粒度其实就是厕所的门，外边厕所的门一关就是表锁，里边分男女厕所，其中一个门一关就是行锁，你跑到坑里，把你蹲坑的地方的门一关那就是行锁。
表锁，开关快，资源小（在厕所外边建个门就行了）抢到就不会有人跟你争，但是你把整个厕所锁住了，外边来上厕所的不论男女就要跟你拼命了。
行锁，开销大（每个坑建个门），加锁慢（要跑一段路），但是就这一个坑被你锁了，不是太急的时候人家不会跟你拼命。
页锁，开销介于表锁行锁之间，速度介于表锁行锁之间
各引擎支持锁粒度：

粒度	InnoDB	MyISAM	MEMORY	BDB
表级锁	Y	Y	Y	Y
页级锁	N	N	N	Y
行级锁	Y	N	N	N

表级锁：表示对当前操作的整张表加锁，实现简单，资源消耗较少，被大部分MySQL引擎支持
特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低
注： mysql显式加表锁语句： lock tables tbl_name MyISAM在执行查询语句（seelct）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 lock tables 命令给MyISAM表显式加锁

页级锁：锁定粒度介于行级锁和表级锁中间的一种锁
特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

行级锁：锁定粒度最细的一种锁，只针对当前操作的行进行加锁
特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

Innodb中的行锁与表锁
InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！
1、行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行的记录,但是如果是使用相同的索引键,是会出现锁冲突的
2、多个索引的时候,不同的事务可以使用不同的索引锁定不同的行
3、在条件中使用了索引字段,但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的,如果 MySQL 认为全表扫效率更高,比如对一些很小的表,它就不会使用索引,这种情况下 InnoDB 将使用表锁,而不是行锁

共享锁（读锁S）、排他锁（写锁X），意向共享锁（IS），和意向排他锁（IX）：
共享锁与排他锁均用于事务当中，随事务的结束而解除。

共享锁[行级锁]（读锁S share lock ）：读取操作创建的锁。
一旦上锁，任何事务（包括当前事务）无法对其修改，其他事务可以并发读取数据，也可在对此数据再加共享锁

SELECT ... LOCK IN SHARE MODE;

排他锁[行级锁]（写锁X exclusive lock）:如果事务对数据A加上排他锁后，则其他事务不可并发读取数据，也不能再对A加任何类型的锁。获准排他锁的事务既能读数据，又能修改数据。

SELECT ... FOR UPDATE

update,insert,delete语句会自动加排它锁

意向锁:InnoDB所用的表级锁，其设计目的主要是为了在一个事务中揭示下一步将要被请求的锁的类型。

InnoDB中的两个表锁：

意向共享锁（IS）[表级锁]：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁

意向排他锁（IX）[表级锁]：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

意向锁是InnoDB自动加的，不需要用户干预。

简单来说就是：
共享锁——你在看书，可以和别人一起看（并发读取）
排它锁——你在写书，你自己一个人看草稿（不可并发读取），一个人写（单独修改）
意向共享锁——你要先把这本书买了
意向排他锁——你要先买本自己的草稿纸，不能用其他人买的草稿纸

它们之间的兼容关系：

	X	IX	S	IS
X	N	N	N	N
IX	N	Y	N	Y
S	N	N	Y	Y
IS	N	Y	Y	Y

注：排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。 mysql InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select ...for update语句，加共享锁可以使用select ... lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select ...from...查询数据，因为普通查询没有任何锁机制。来自：https://www.cnblogs.com/boblogsbo/p/5602122.html
附代码小实验：

1、验证排它锁排它效用
	a.navicate新建一个查询：
	随便找张表加上：
 	begin;
	select * from ims_ewei_share_log where id=1 for update;
	
	b.navicate再新建一个查询：
	查询刚才那张表（直接通过select ...from...查询数据，因为普通查询没有任何锁机制）
	select * from ims_log where id=1 for update;
	select * from ims__log where id=1 lock in share mode;
	得出结论 lock超时
	
2、验证共享锁效用
	a.navicate新建一个查询：
	begin;
	select * from ims_ewei_share_log where id=1 lock in share mode;
	
	b.navicate再新建一个查询：
	select * from ims_log where id=1 for update;（不能用，排斥）
	select * from ims__log where id=1 lock in share mode;（能用）
	
2、验证update,delete,insert自动加锁
	a.navicate新建一个查询：
	随便找张表加上：
 	begin;
	update ims_ewei_share_log set uniacid=2 where id=1;
	
	b.navicate再新建一个查询：
	查询刚才那张表（直接通过select ...from...查询数据，因为普通查询没有任何锁机制，但查出的是来老的数据即 uniacid还是为1）
	select * from ims_log where id=1 for update;
	select * from ims__log where id=1 lock in share mode;

记录锁（Record Locks），间隙锁（Gap Locks），临键锁（Next-Key Locks）
这几个锁都是RR级别使用
记录锁（Record Locks）:记录锁锁定索引中一条记录。

 select * from ims_logwhere   id =  1  for update

id列上有唯一索引，并且查询条件可以唯一确定一条记录，这时候innodb使用记录锁，只会锁住查出来的这一行记录

间隙锁（Gap Locks）:间隙锁要么锁住索引记录中间的值，要么锁住第一个索引记录前面的值或者最后一个索引记录后面的值。
where后面的字段有索引，但不是唯一索引，或者使用了>, < 等范围的查询条件时，查询条件范围内的索引值之间的间隙会被加锁，结果就是被加锁的间隙之间不能插入索引值
查看间隙锁状态：

show variables like 'innodb_locks_unsafe_for_binlog';

关于间隙锁可以看看这篇文章：https://www.jianshu.com/p/bf862c37c4c9

临键锁（Next-Key Locks）:Next-Key锁是索引记录上的记录锁和在索引记录之前的间隙锁的组合。

当InnoDB扫描索引记录的时候，会首先对选中的索引记录加上行锁（Record Lock），再对索引记录两边的间隙（向左扫描扫到第一个比给定参数小的值，向右扫描扫描到第一个比给定参数大的值，然后以此为界，构建一个区间）加上间隙锁（Gap Lock）

插入意向锁（Intention Lock ）
插入意向锁本质上可以看成是一个间隙锁，也是实施在索引上。
官网定义是：插入意图锁定表示插入的意图，即插入到同一索引间隙中的多个事务如果不插入间隙内的相同位置则不需要彼此等待。

//例如以下示例
id | name
10 | 北京
20 | 上海

//执行操作
//一个事务
insert into ims_table value(11,'深圳');
//另一个事务
insert into ims_table value(12,'广东');
//并发插入并不会冲突

自增锁(Auto-inc Locks)
官网定义：插入意图锁定是在行插入之前由INSERT操作设置的一种间隙锁定。该锁定表示以这样的方式插入的意图：如果插入到相同索引间隙中的多个事务不插入间隙内的相同位置，则不需要等待彼此。假设存在值为4和7的索引记录。分别尝试插入值5和6的单独事务，在获取插入行上的排它锁之前，每个锁定4和7之间的间隙和插入意图锁，但是不要互相阻塞因为行是非冲突的。
特性：在有自增字段时才存在的锁，采用一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的SQL语句后立即释放。
在插入是使用

select max(auto_inc_col) from t for update;

来得到最新的值。
InnoDB提供了一个参数 innodb_autoinc_lock_mode 来控制自增长的模式。

INSERT-like 声明
在表中生成新行的所有语句，包括INSERT，INSERT … SELECT，REPLACE，REPLACE … SELECT和LOAD DATA. 包括 “simple-inserts”, “bulk-inserts”, 和“mixed-mode” 增加.。

Simple insert
可以预先确定要插入的行数的语句（最初处理语句时）。这包括单行和多行INSERT和REPLACE语句，它们没有嵌套子查询，但没有INSERT … ON DUPLICATE KEY UPDATE。

Bulk inserts
预先不知道要插入的行数（以及所需的自动增量值的数量）的语句。这包括INSERT … SELECT，REPLACE … SELECT和LOAD DATA语句，但不包括普通INSERT。在处理每一行时，InnoDB一次为AUTO_INCREMENT列分配一个新值。

Mixed-mode inserts
些是“Simple insert”语句，它指定一些（但不是全部）新行的自动增量值。

参数	解释
0	这是MySQL 5.1.22版本之前自增长的实现方式，即通过表锁的AUTO-INC Locking方式，因为有了新的自增长实现方式，0这个选项不应该是新版用户的首选了
1	这是该参数的默认值，对于”simple inserts”（预先知道行数），该值会用 mutex 去对内存中的计数器进行累加的操作后对增加的量进行锁定。对于”bulk inserts”，还是使用传统表锁的AUTO-INC Locking方式。在这种配置下，如果不考虑回滚操作，对于自增值列的增长还是连续的。并且在这种方式下，statement-based方式的replication还是能很好地工作。简而言之，这种锁定模式显着提高了可伸缩性，同时可以安全地使用基于语句的复制。此外，与“传统”锁定模式一样，由任何给定语句分配的自动递增数字是连续的。对于任何使用自动增量的语句，与“传统”模式相比，语义没有变化，但有一个重要的例外。“Mixed-mode inserts”的例外情况是，用户为多行“simple inserts”中的某些行（但不是所有行）提供AUTO_INCREMENT列的显式值。对于此类插入，InnoDB会分配比要插入的行数更多的自动增量值。但是，自动分配的所有值都是连续生成（因此高于）最近执行的先前语句生成的自动增量值。 “超额”号码丢失了。
2	在这个模式下，对于所有”INSERT-LIKE”自增长值的产生都是通过 mutex ，而不是AUTO-INC Locking的方式。显然，这是性能最高的方式。然而，这会带来一定的问题，因为并发插入的存在，在每次插入时，自增长的值可能不是连续的。此外，最重要的是，基于Statement-Base Replication会出现问题。因此，使用这个模式，任何时候都应该使用row-base replication。这样才能保证最大的并发性能及replication主从数据的一致。

详见：https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html#innodb-auto-increment-lock-modes

总结：
我想以生活中的一些比喻来总结下这些表，让它们更好记忆更加形象。而不是保留在电脑的硬盘中，在一段时间不用之后就被遗忘。以我们坐飞机的过程中的现象为例来打个比方：
在买完机票后，你可以通过值机来选定你在飞机上的座位。在值机的过程中，你会发现你可以看到已经被选中的座位但是你不能对这些座位进行操控了，这种效果就是共享锁。当你选中一个座位时，你可以执行值机操作但是其他人不能操作这个，这种效果就是排他锁。（意向锁是自动添加的这里不表）。
在上飞机之前要排队检票，一次只锁定一个人进行检查，这效果就是记录锁。你们排队的时候别人不能插队，这种效果就是间隙锁，排队只能一个个的往队伍后边排不能随便乱排，这种就是自增锁。
在安检严格的时候还会抽检爆炸物扫描。随机抽上你和你前后一定范围的小伙伴，来进行检查。这种效果就是临键锁。
你按照你值机选的号码坐上飞机对应的座位，别人不能坐这个座位，这就叫插入意向锁。

死锁
以下摘录自：https://www.cnblogs.com/sivkun/p/7518540.html

是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。由于资源占用是互斥的，当某个进程提出申请资源后，使得有关进程在无外力协助下，永远分配不到必需的资源而无法继续运行，这就产生了一种特殊现象死锁。

死锁的第一种情况
一个用户A 访问表A(锁住了表A),然后又访问表B；另一个用户B 访问表B(锁住了表B)，然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。
死锁的第二种情况
用户A查询一条纪录，然后修改该条纪录；这时用户B修改该条纪录，这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户B里的独占锁由于A 有共享锁存在所以必须等A释放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。这种死锁比较隐蔽，但在稍大点的项目中经常发生。如在某项目中，页面上的按钮点击后，没有使按钮立刻失效，使得用户会多次快速点击同一按钮，这样同一段代码对数据库同一条记录进行多次操作，很容易就出现这种死锁的情况。
死锁的第三种情况
如果在事务中执行了一条不满足条件的update语句，则执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。类似的情况还有当表中的数据量非常庞大而索引建的过少或不合适的时候，使得经常发生全表扫描，最终应用系统会越来越慢，最终发生阻塞或死锁。

以上，是一些对mysql结构与锁的介绍。