记录些MySQL题集（1）_业界新闻

发布时间:2024-07-18 03:41

阅读量:3

Innodb 是如何实现事务的？

InnoDB是MySQL数据库的一个存储引擎，它支持事务处理。事务处理是数据库管理系统执行过程中的一个逻辑单位，由一个或多个SQL语句组成，这些语句要么全部执行，要么全部不执行，是一个不可分割的工作单位。InnoDB通过以下方式来实现事务：

原子性（Atomicity）：原子性确保事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。InnoDB使用日志（redo log）来确保事务的原子性。当事务提交时，相关的变更首先会被写入到redo log中，然后再异步地应用到实际的数据页上。如果事务在执行过程中由于某些原因（如系统崩溃）被中断，那么在系统恢复时，InnoDB可以通过重放redo log中的记录来确保事务的变更被正确地应用到数据上。
一致性（Consistency）：一致性确保数据库从一个一致性状态转变到另一个一致性状态。在InnoDB中，一致性是通过数据库的各种约束（如主键约束、外键约束、唯一约束等）以及事务的隔离级别来保证的。例如，在事务执行过程中，InnoDB会检查数据是否满足这些约束，如果不满足，事务会回滚，从而确保数据的一致性。
隔离性（Isolation）：隔离性确保多个并发事务执行时，一个事务的执行不应影响其他事务。InnoDB通过多版本并发控制（MVCC）来实现事务的隔离性。MVCC允许每个事务看到一个一致的数据快照，就好像它是独立运行的一样。每个事务在开始时都会获取一个版本的数据快照，并在该快照上进行操作，而不会直接修改原始数据。其他并发事务的修改对当前事务是不可见的，直到当前事务提交或回滚。
持久性（Durability）：持久性确保一旦事务提交，其所做的变更就永久地保存到数据库中。在InnoDB中，持久性是通过将事务的变更写入到磁盘上来实现的。除了前面提到的redo log之外，InnoDB还有一个undo log，用于在事务回滚时撤销所做的变更。这些日志都是持久化到磁盘上的，以确保在系统崩溃或重启后，事务的状态可以被正确地恢复。

Innodb通过Buffer Pool，LogBuffer，RedoLog，Undo Log来实现事务，以一个update语句为例：

Innodb在收到一个update语句后，会先根据条件找到数据所在的页，并将该页缓存在BufferPool中。
执行update语句，修改BufferPool中的数据，也就是内存中的数据。
针对update语句生成一个RedoLog对象，并存入LogBuffer中。
针对update语句生成undolog日志，用于事务回滚。
如果事务提交，那么则把RedoLog对象进行持久化，后还有其他机制将BufferPool中所修改的数据页持久化到磁盘中。
如果事务回滚，则利用undolog日志进行回滚。

MySQL 什么时候锁表？如何防止锁表？

MySQL 调优的重点目标：避免锁表

锁表会带来一系列问题，影响数据库的性能和系统的稳定性。主要是下面的四个问题：性能问题、死锁问题、可用性问题、一致性问题。

1. 锁表带来的性能问题

锁表会阻止其他事务对该表的并发访问，包括读操作和写操作。锁表会导致严重的性能问题：

系统吞吐量下降：多个事务需要依次等待锁的释放，导致系统整体的吞吐量下降。
用户体验差：用户的请求需要长时间等待，导致响应时间变长，影响用户体验。

2. 锁表引发死锁

在高并发环境下，锁表更容易导致死锁的发生。锁表会导致严重的事务问题：

事务回滚：部分事务被迫回滚，影响数据一致性。
系统复杂性增加：需要数据库管理系统介入来检测和解决死锁问题，增加系统复杂性。

3. 锁表降低系统可用性

长时间的表锁定会影响数据库的可用性，使得应用程序无法及时处理用户请求。锁表带来的系统可用性问题：

功能不可用：系统的部分功能无法使用，影响用户的正常操作。
业务中断：在严重情况下，可能导致整个系统不可用，影响业务连续性。

4. 锁表引发数据一致性问题

在高并发写操作场景下，锁表会导致数据一致性问题。锁表带来的数据一致性问题：

数据不一致：无法及时更新数据，影响业务逻辑的正确执行。
状态混乱：例如，订单状态更新时，如果表被锁定，其他更新操作无法及时进行，可能会导致数据状态不一致。

MySQL 锁的分类

从操作的粒度可分为表级锁、行级锁和页级锁。

1.表级锁：

每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。表级锁应用在MyISAM、InnoDB、BDB等存储引擎中。表锁的特点：

开销小，加锁快
不会出现死锁
锁定粒度大，发生锁冲突的概率最高，并发度最低

2.行级锁：

每次操作锁住一行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。行级锁应用在InnoDB 存储引擎中。MyISAM没有行级锁。行锁的特点：

开销大，加锁慢
会出现死锁
锁定粒度小，发生锁冲突的概率最低，并发度最高

3.页级锁：

每次锁定相邻的一组记录，锁定粒度界于表锁和行锁之间，加锁开销和加锁时间界于表锁和行锁之间，并发度一般。页级锁应用在BDB等存储引擎中。页锁的特点：

开销和加锁时间介于表锁和行锁之间
会出现死锁
锁定粒度介于表锁和行锁之间，并发度一般

MySQL 使用页级锁的情况相对较少，因为 MySQL 默认使用的是行级锁。但在特定的情况下，MySQL 可能会使用页级锁，主要包括以下几种情况：

表级锁定转换为页级锁定： 当表级锁无法满足需求时，MySQL 可能会将表级锁转换为页级锁。这通常发生在使用 BDB 存储引擎时，因为 BDB 存储引擎默认使用表级锁。在某些情况下，MySQL 可能会将表级锁转换为页级锁，以提高并发性能。
隐式锁定大量行数据： 在某些情况下，如果一次性锁定了大量行数据，则 MySQL 可能会使用页级锁来减少锁的数量，从而减轻锁管理的负担。例如，在使用 BDB 存储引擎时，如果一次性锁定了大量行数据，则 MySQL 可能会将表级锁转换为页级锁。
DDL 操作： 在执行对表结构进行修改的 DDL（数据定义语言）操作时，MySQL 可能会使用页级锁来锁定整个表。例如，当执行 ALTER TABLE 操作时，MySQL 可能会锁定整个表的页，以防止其他对表结构的修改和查询操作。

总的来说，MySQL 使用页级锁的情况相对较少，因为页级锁通常会导致锁的粒度过大，影响并发性能。在设计数据库和应用程序时，通常会尽量避免使用页级锁，而是优先使用行级锁或其他更细粒度的锁。

InnoDB 存储引擎中的表锁和行锁

对于 MySQL 来说，每种存储引擎都可以实现自己的锁策略和锁粒度，比如 InnoDB 引擎支持行级锁和表级锁，不支持页级锁。下面主要聚焦介绍 InnoDB 存储引擎中的两大锁：

表级锁
行级锁

InnoDB 的表级锁

表锁，顾名思义就是对某个表加锁。表级锁可以分为：表锁、元数据锁、意向锁三种。

表级锁之一：表锁

一般情况是对应的存储引擎没有行级锁（例如：MyIASM），或者是对应的 SQL 语句没有匹配到索引。对于第一种情况而言，因为对应存储引擎不支持行锁，所以只能是使用更粗粒度的锁来实现，这也比较好理解。对于第二种情况而言，如果存储引擎支持行锁，但对应的 SQL 就没有使用索引，那么此时也是会全表扫描，那此时也是会使用表锁。

例如下面的语句没有指定查询列，或者指定了查询列但是并没有用到索引，那么也是会直接锁定整个表。

情况1：没有指定查询列
select * from user;

情况2：指定查询列，但是没有用到索引
select * from user where name = 'zhangsan';

上面说的索引，其实是判断是否会用行级锁的关键。

表级锁之二：元数据锁

元数据，指的是我们的表结构这些元数据。元数据锁（Metadata Lock）自然是执行 DDL 表结构变更语句时，我们对表加上的一个锁了。当我们对一个表做增删改查操作的时候，会加上 MDL 读锁；当我们要对表结构做变更时，就会加 MDL 写锁。

在MySQL中，当对表进行写操作（如INSERT、UPDATE、DELETE）时，需要对相关的数据行加锁以确保数据的一致性和完整性。在某些情况下，MySQL需要锁定整个表而不是部分行，这种情况下会锁定整个表，导致其他会话不能访问表。

1.使用ALTER TABLE、TRUNCATE TABLE等语句对表进行结构性修改时，MySQL需要锁定整个表以防止其他会话对表进行操作。

2.使用LOCK TABLES语句手动锁定表时，MySQL将锁定整个表以确保其他会话不能访问它。

3.在使用MyISAM存储引擎时，当执行写操作时，MySQL会对整个表进行加锁。这是因为MyISAM使用表级锁定而不是行级锁定。

项目中最常见的锁表问题，都是由于UPDATE语句或者DELETE语句的where条件没有走索引导致的。因此我们需要在条件字段上加索引，从而将表锁变为行锁。

表级锁之三：意向锁

意向锁，本质上就是空间换时间的产物，是为了提高行锁效率的一个东西。在 InnoDB 中，我们对某条记录进行锁定时，为了提高并发度，通常都只是锁定这一行记录，而不是锁定整个表。而当我们需要为整个表加 X 锁的时候，我们就需要遍历整个表的记录，如果每条记录都没有被加锁，才可以给整个表加 X 锁。而这个遍历过程就很费时间，这时候就有了意向锁。

意向锁，其实就是标记这个表有没有被锁，如果有某条记录被锁住了，那么就必须获取该表的意向锁。所以当我们需要判断这个表的记录有没有被加锁时，直接判断意向锁就可以了，减少了遍历的时间，提高了效率，是典型的用空间换时间的做法。

在对表中的行记录加锁的时候，就会用到意向锁。InnoDB 存储引擎支持 多粒度（granular）锁定，就是说允许事务在行级上的锁和表级上的锁同时存在。那么为了实现行锁和表锁并存，InnoDB 存储引擎就设计出了 意向锁（Intention Lock） 这个东西：

Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table.

很好理解：意向锁是一个表级锁，其作用就是指明接下来的事务将会用到哪种锁。有两种意向锁：

意向共享锁（IS Lock）：当事务想要获得一张表中某几行的共享锁行级锁）时，InnoDB 存储引擎会自动地先获取该表的意向共享锁（表级锁）
意向排他锁（IX Lock）：当事务想要获得一张表中某几行的排他锁（行级锁）时，InnoDB 存储引擎会自动地先获取该表的意向排他锁（表级锁）

意向锁之间是相互兼容的：

	IS 锁	IX 锁
IS 锁	兼容	兼容
IX 锁	兼容	兼容

但是与表级读写锁之间大部分都是不兼容的：

	X 锁	S 锁
IS 锁	不兼容	兼容
IX 锁	不兼容	不兼容

注意，这里强调一点：上表中的读写锁指的是表级锁，意向锁不会与行级的读写锁互斥。

来理解一下为什么说意向锁不会与行级的读写锁互斥。举个例子，事务 T1、事务 T2、事务 T3 分别想对某张表中的记录行 r1、r2、r3 进行修改，很普通的并发场景对吧，这三个事务之间并不会发生干扰，所以是可以正常执行的。

这三个事务都会先对这张表加意向写锁，因为意向锁之间是兼容的，所以这一步没有任何问题。

那如果意向锁和行级读写锁互斥的话，岂不是这三个事务都没法再执行下去了，对吧。

思考两个问题：

1）为什么没有意向锁的话，表锁和行锁不能共存？

2）意向锁是如何让表锁和行锁共存的？

首先来看第一个问题，假设行锁和表锁能共存，举个例子：事务 T1 锁住表中的某一行（行级写锁），事务 T2 锁住整个表（表级写锁）。

问题很明显，既然事务 T1 锁住了某一行，那么其他事务就不可能修改这一行。这与 ”事务 T2 锁住整个表就能修改表中的任意一行“ 形成了冲突。所以，没有意向锁的时候，行锁与表锁是无法共存的。

再来看第二个问题，有了意向锁之后，事务 T1 在申请行级写锁之前，MySQL 会先自动给事务 T1 申请这张表的意向排他锁，当表上有意向排他锁时其他事务申请表级写锁会被阻塞，也即事务 T2 申请这张表的写锁就会失败。

InnoDB 的行级锁

行级锁是存储引擎级别的锁，需要存储引擎支持才有效。目前 MyISAM 存储引擎不支持行级锁，而 Innodb 存储引擎则支持行级锁。而表级锁，则是 MySQL 层面就支持的锁。当增删改查匹配到索引时，Innodb 会使用行级锁。如果没有匹配不到索引，那么就会直接使用表级锁。

InnoDB 存储引擎三种行级锁

InnoDB引擎行锁是通过对索引数据页上的记录加锁实现的，主要实现算法有 3 种：Record Lock、Gap Lock 和 Next-key Lock，也就是InnoDB的三种行锁模式。

Record Lock锁(行锁)：锁定单个行记录的锁。（RecordLock锁是记录锁，RC、RR隔离级别都支持）
Gap Lock锁：间隙锁，锁定索引记录间隙(不包括记录本身)，确保索引记录的间隙不变。（GapLock是范围锁，RR隔离级别支持。RC隔离级别不支持）
Next-key Lock 锁(临键锁)：记录锁和间隙锁组合，同时锁住数据，并且锁住数据前后范围。（记录锁+范围锁，RR隔离级别支持。RC隔离级别不支持）

行级锁之一：记录锁(Record Locks)

（1）记录锁, 仅仅锁住索引记录的一行，在单条索引记录上加锁。

（2）record lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。

所以说当一条sql没有走任何索引时，那么将会在每一条聚合索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。

行级锁之二：间隙锁(Gap Locks)

（1）区间锁, 仅仅锁住一个索引区间（开区间，不包括双端端点）。

（2）在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。

（3）间隙锁可用于防止幻读，保证索引间的不会被插入数据

比如在 100、10000中，间隙锁的可能值有 (∞, 100)，(100, 10000)，(10000, ∞)，

行级锁之三：临键锁(Next-Key Locks)

（1）record lock + gap lock, 左开右闭区间。

（2）默认情况下，innodb使用next-key locks来锁定记录。select … for update

（3）但当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。

（4）Next-Key Lock在不同的场景中会退化:

比如在 100、10000中，临键锁(Next-Key Locks)的可能有 (∞, 100]，(100, 10000] , 这里的关键是左开右闭。

1.使用ALTER TABLE、TRUNCATE TABLE等语句对表进行结构性修改时，MySQL需要锁定整个表以防止其他会话对表进行操作。

2.使用LOCK TABLES语句手动锁定表时，MySQL将锁定整个表以确保其他会话不能访问它。

3.在使用MyISAM存储引擎时，当执行写操作时，MySQL会对整个表进行加锁。这是因为MyISAM使用表级锁定而不是行级锁定。

InnoDB 如何加锁？

InnoDB 采用的是两阶段锁定协议（two-phase locking protocol），意思是：对于每一个事务Transaction，分为两个阶段：

第一阶段：增长阶段（Growing Phase）

在这个阶段，事务可以获得锁定，但不能释放锁定。事务可以继续请求并获得锁定，直到它达到了其锁定点（Lock Point），也就是事务获得最后一个锁定的时间点。一旦事务进入第二阶段，它就不能再获得新的锁定。

第二阶段：缩减阶段（Shrinking Phase）

在这个阶段，事务可以释放已经持有的锁定，但不能再获取新的锁定。这个阶段的目的是确保事务不会在已经进入第二阶段后再次请求锁定。

两阶段锁定协议（two-phase locking protocol），有下面的两个特点：

特点1：即在事务执行过程中，随时都可以执行加锁操作。
特点2：但是只有在事务执行 COMMIT 或者 ROLLBACK 的时候才会释放锁，并且所有的锁是在同一时刻被释放。

InnoDB 如何加锁？从下面三个维度来分开介绍：

第1个维度：InnoDB 如何加意向锁?

它比较特殊，是由 InnoDB 存储引擎自己维护的，用户无法手动操作意向锁，在为数据行加读写锁之前，InnoDB 会先获取该数据行所在在数据表的对应意向锁。

第2个维度：InnoDB如何加表级锁?：

1）隐式锁定：对于常见的 DDL 语句（如 ALTER、CREATE等），InnoDB 会自动给相应的表加表级锁。

2）显示锁定：在执行 SQL 语句时，也可以明确显示指定对某个表进行加锁（lock table user read(write)）。

lock table user read; # 加表级读锁
unlock tables; # 释放表级锁

第3个维度：InnoDB 如何加行级锁?：

1）对于常见的 DML 语句（如 UPDATE、DELETE 和 INSERT），InnoDB 会自动给相应的记录行加写锁。

2）默认情况下对于普通 SELECT 语句，InnoDB 不会加任何锁，但是在 Serializable 隔离级别下会加行级读锁。

上面两种是隐式锁定，InnoDB 也支持通过特定的语句进行显式锁定，不过这些语句并不属于 SQL 规范：

3）SELECT * FROM table_name WHERE ... FOR UPDATE，加行级写锁

4）SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE，加行级读锁

另外，需要注意的是，InnoDB 存储引擎的行级锁是基于索引的，也就是说当索引失效或者说根本没有用索引的时候，行锁就会升级成表锁。比较典型的索引失效情况 “使用 or" 。

MySQL何时锁定整个表？

1.对表进行结构性修改

当使用ALTER TABLE或TRUNCATE TABLE等语句对表进行结构性修改时，MySQL需要锁定整个表以防止其他会话对表进行操作。

2.手动锁定表

使用LOCK TABLES语句手动锁定表时，MySQL将锁定整个表以确保其他会话不能访问它。

3.MyISAM写操作

在使用MyISAM存储引擎时，执行写操作会导致MySQL对整个表加锁，这是因为MyISAM使用表级锁定而不是行级锁定。

4.两个或多个事务在同时修改一个表时

事务中包含多条对同一个表进行修改的SQL语句时，该表会被锁定。这是因为MVSQL采用行锁定机制，但当两个或多个事务在同时修改一个表时，未使用的修改请求会被放入等待队列。

5.索引操作

对一个大表进行索引操作(如新建或删除索引)时，该表会被锁定。索引操作锁定表的时间与表的大小和结构有关。

6.并发操作:

在一张表中插入大量数据的同时，尝试在同一时刻进行查询操作，会导致表被锁定。这是因为查询和插入操作都需要获取相应的锁

7 索引不可用时加锁操作

使用SELECT..FOR UPDATE语句进行行级锁定操作时，如果索引不可用，MySQL可能会升级为表锁。

8.索引选择不恰当

在某些情况下，如果查询的索引选择不恰当，MySQL可能会升级为表锁，尤其是当锁住的数据量较大时

9.更新和删除场景，where没命中索引

项目中最常见的锁表问题，都是由于UPDATE/DELETE时, where条件没有走索引导致的。当执行UPDATE或DELETE语句且where条件未使用索引时，可能会导致全表扫描并锁定整个表。因此我们需要在条件字段上加索引，从而将表锁变为行锁。

10.查询场景，索引失效的情况下，行锁升表锁

在索引失效的情况下，MySQL会把所有聚集索引记录和间隙都锁上，称之为锁表，或叫行锁升表锁。

在 MySQL 中，索引对于查询性能至关重要，但是有些情况下索引可能会失效，从而导致查询性能下降

索引失效原因及其解决方法

10.1. 使用函数或操作符

原因：在 WHERE 子句中使用函数或操作符（如计算、转换函数）会导致索引失效。
示例：SELECT * FROM table WHERE YEAR(date_column) = 2023;
解决方法：在索引列上避免使用函数或操作符。可以改为：SELECT * FROM table WHERE date_column BETWEEN '2023-01-01' AND '2023-12-31';

10.2. 模糊查询

原因：在 LIKE 子句中以通配符开头的查询（如 '%abc'）会导致索引失效。
示例：SELECT * FROM table WHERE column LIKE '%value';
解决方法：避免在 LIKE 中使用前置通配符，可以使用后置通配符：SELECT * FROM table WHERE column LIKE 'value%';

10.3. 不符合最左前缀原则

原因：复合索引必须按照最左前缀原则使用，否则会导致索引失效。
示例：对于索引 (a, b, c)，查询 WHERE b = 1 AND c = 2会导致索引失效。
解决方法：确保查询条件按照索引的顺序使用，如 WHERE a = 1 AND b = 2 AND c = 3。

10.4. 数据类型不一致

原因：查询条件中的数据类型与索引列的数据类型不一致，会导致索引失效。
示例：SELECT * FROM table WHERE varchar_column = 123;
解决方法：确保查询条件的数据类型与索引列的数据类型一致：SELECT * FROM table WHERE varchar_column = '123';

10.5. 使用 OR 条件

原因：在多个列上使用 OR 条件时，如果其中一个列没有索引，整个查询会导致索引失效。
示例：SELECT * FROM table WHERE column1 = 1 OR column2 = 2;
解决方法：可以改为 UNION 查询以使用索引：SELECT * FROM table WHERE column1 = 1 UNION SELECT * FROM table WHERE column2 = 2;

10.6. 隐式类型转换

原因：隐式类型转换会导致索引失效。
示例：SELECT * FROM table WHERE varchar_column = 123;
解决方法：显式转换查询条件的数据类型：SELECT * FROM table WHERE varchar_column = '123';

10.7. 范围条件

原因：在复合索引中，范围条件（如 <, >, BETWEEN, LIKE）之后的索引列会失效。
示例：对于索引 (a, b)，查询 WHERE a > 1 AND b = 2 会导致索引 b 失效。
解决方法：尽量避免在复合索引中使用范围条件，如果必须使用，考虑调整索引顺序。

10.8. NULL 判断

原因：在某些情况下，对 NULL 的判断会导致索引失效。
示例：SELECT * FROM table WHERE column IS NULL;
解决方法：确保列上有合适的索引，并且在设计表结构时尽量避免使用 NULL。

10.9. 更新频繁的列

原因：在频繁更新的列上建立索引，可能会导致索引的维护成本高，从而影响查询性能。
解决方法：在设计索引时，尽量避免在频繁更新的列上建立索引。

10.10. 查询优化器选择

原因：有时候查询优化器可能错误地选择了全表扫描而不是使用索引，特别是在小表或索引列的选择性不高的情况下。
解决方法：可以使用 FORCE INDEX 强制使用特定索引，或调整查询语句和索引设计以帮助优化器选择正确的索引。

通过识别和解决这些索引失效的原因，可以显著提高 MySQL 查询的性能。

MySQL会锁表的场景总结

MySQL在执行结构性修改、手动锁定、写操作、事务处理、索引操作、并发操作、加锁操作、以及在特定查询条件下索引失效，都可能需要对表进行锁定。

如何减少或避免锁表？

锁表会对系统性能、数据一致性和用户体验产生负面影响，甚至可能引发严重的业务中断和系统崩溃。因此，避免锁表是数据库优化和系统设计中的重要任务。可以通过优化索引、分解大事务、合理设计表结构、使用适当的事务隔离级别、读写分离等方法来减少锁表问题，提升系统的并发性能和稳定性。在 MySQL 中避免锁表问题对于提高数据库性能和并发性至关重要。以下是一些调优策略和最佳实践，以减少或避免锁表问题：

1. 使用合适的存储引擎

InnoDB：使用支持行级锁的存储引擎，如 InnoDB。InnoDB 是 MySQL 的默认存储引擎，支持行级锁定和事务，能够有效减少锁冲突。
避免使用 MyISAM，因为它只支持表级锁定，容易导致锁表问题。

2. 优化查询和索引

当索引包含了查询所需的所有列时，这个索引就被称为“覆盖索引”。

索引：确保查询使用适当的索引来减少扫描的行数，从而减少锁定的范围和时间。
覆盖索引：使用覆盖索引以减少表扫描，提高查询效率。
优化查询语句：避免使用复杂的查询语句，尽量简化查询条件，减少锁定时间。

3. 分解大事务

分解大事务：将大事务分解成多个小事务，以减少单个事务持有锁的时间，降低锁冲突的概率。
事务控制：在事务中，尽量减少锁定时间。尽量避免在事务中进行用户交互操作。

4. 锁策略和隔离级别

锁策略：尽量使用行级锁定，而不是表级锁定。确保在需要时显式地使用合适的锁策略。
隔离级别：选择适当的事务隔离级别（如 Read Committed 或 Repeatable Read），以平衡一致性和并发性。尽量避免使用 Serializable 隔离级别，因为它会导致更多的锁定和冲突。

5. 分区和分表

表分区：对大表进行分区，以减少每次操作的行数和锁定范围，提高并发性。
分表：将数据分布到多个表中，以减少单个表的负载和锁定冲突。

6. 避免长时间的锁定操作

批量操作：将批量操作分成多个小批次，以减少每次操作的锁定时间。
在线DDL操作：使用 InnoDB 的在线 DDL 功能（例如，ALTER TABLE ... ALGORITHM=INPLACE），以减少对表的锁定时间。

7. 监控和分析

监控锁等待：使用 MySQL 的性能_schema 和相关工具（如 SHOW PROCESSLIST、INFORMATION_SCHEMA.INNODB_LOCKS、INFORMATION_SCHEMA.INNODB_LOCK_WAITS）监控锁等待情况。
分析慢查询：使用 slow query log 分析慢查询日志，找出可能导致锁表的查询，并进行优化。

8. 读写分离

主从复制：通过主从复制实现读写分离，将读操作分散到从库上，以减轻主库的负载和锁定压力。

9. 使用合适的锁

显式锁定：在需要时显式地使用合适的锁定策略（如 SELECT ... FOR UPDATE），但要谨慎使用，避免不必要的长时间锁定。

10. 业务层优化

批量提交：在业务逻辑中优化批量提交操作，减少锁冲突。
乐观锁定：在业务层使用乐观锁定机制，以减少数据库锁定冲突。

乐观锁与悲观锁

悲观锁

在MySQL中，悲观锁依赖数据库提供的锁机制来实现。在InnoDB引擎中，使用悲观锁需要先关闭MySQL数据库的自动提交属性，然后通过select ... for update来进行加锁。在数据库中，悲观锁的流程如下：

在对记录进行修改前，先尝试为该记录加上排他锁（exclusive lock）。
如果加锁失败，说明该记录正在被修改，此时当前查询可能需要等待或抛出异常，具体响应方式由开发者根据实际需求决定。
如果成功加锁，则可以对记录进行修改，事务完成后锁将被释放。
其间若有其他操作试图对该记录进行修改或加排他锁，则会等待当前锁的释放或直接抛出异常。

使用SELECT ... FOR UPDATE会将数据锁住，不过我们需要注意一些锁的级别。MySQL InnoDB默认使用行级锁。行级锁都是基于索引的，如果一条SQL语句未使用索引，优化器在选择时，若发现锁表可能性能更好，有可能会直接锁表。

乐观锁

MySQL中的乐观锁主要通过CAS（Compare and Swap）的机制来实现，通常使用版本号（version）来实现。CAS是一种乐观锁技术，当多个线程尝试使用CAS同时更新同一个变量时，只有其中一个线程能成功更新变量的值，而其他线程都失败。失败的线程并不会被挂起，而是被告知在此次竞争中失败，并可以再次尝试。

悲观锁

在对数据库中的数据进行修改时，为了避免同时被其他人修改，最好的方法是直接对该数据进行加锁以防止并发。这种在修改数据之前先锁定再修改的方式被称为悲观并发控制（又称“悲观锁”，Pessimistic Concurrency Control，缩写为“PCC”）。

悲观锁之所以被称为悲观，是因为这是一种对数据的修改抱有悲观态度的并发控制方式。一般来说，我们认为数据被并发修改的概率较大，因此在修改之前先加锁。悲观并发控制实际上是一种保守的策略，即“先取锁再访问”，它为数据处理的安全性提供了保证。

在效率方面，处理加锁机制会导致数据库产生额外的开销，增加了产生死锁的风险。此外，悲观锁还可能降低并行性，因为如果一个事务锁定了某行数据，其他事务就必须等待该事务完成才能处理该行数据。

乐观锁

乐观锁（Optimistic Locking）是相对悲观锁而言的。乐观锁假设数据在一般情况下不会发生冲突，因此在数据提交更新时才会实际检查数据是否冲突。如果发现冲突，则会向用户返回错误信息，让用户决定如何处理。与悲观锁相比，乐观锁在处理数据库时并不会使用数据库提供的锁机制。一般来说，乐观锁的实现方式是通过记录数据的版本信息。

乐观并发控制相信事务之间的数据竞争（data race）的概率较小，因此尽可能直接进行操作，直到提交时才对数据进行检查和锁定。这样做不会产生任何锁或死锁。

如何选择

在乐观锁与悲观锁的选择上面，主要看下两者的区别以及适用场景就可以了。

乐观锁并未真正加锁，效率高。适用于读操作频繁，写操作相对较少的场景。一旦锁的粒度掌握不好，更新失败的概率就会比较高，容易发生业务失败。
悲观锁依赖数据库锁，效率低。更新失败的概率比较低。适用于写操作较为频繁，且并发写入的概率较高的场景。

MySQL 执行SQL查询时发生了什么？

各组件的功能主要是：

1、连接器

管理客户端的连接，并负责权限验证。当用户通过客户端发送一条SQL查询语句时，首先，MySQL服务器会验证客户端的连接信息（如用户名、密码），建立连接。连接成功后，服务器才会SQL查询语句。MySQL也提供了最大连接数、连接超时等一系列参数设置来控制客户端连接行为。

2、查询缓存

如果一个查询请求被执行过，MySQL会把执行结果存入查询缓存，如果后面又查询了同样的语句，则会直接返回结果。但是，查询缓存会因为更新频繁失效，因为只要一个表有更新操作，那么这个表的查询缓存就会被清空。这样就显得很鸡肋，我们可以通过将参数 query_cache_type 设置成 DEMAND，来关闭查询缓存。需要指出，MySQL 8.0版本直接将查询缓存的整块功能删掉了，8.0开始彻底没有这个功能了。

3、SQL解析器

SQL解析器会分析SQL语句的语法结构，做词法和语法分析，检查其是否符合SQL语法规范。如果不符合，将返回错误信息。之后构建出语法树

4、预处理器

预处理器进一步检查SQL语句中的表名、列名是否存在，把 select * 替换为具体的列，以及用户是否有相应的访问权限。

5、优化器

优化器根据表的数据分布、索引等信息，选择最优的执行计划。这一步骤对于提高查询效率至关重要。优化器可能会重写查询语句，比如转换JOIN顺序，选择最合适的索引等。

6、执行器

查询执行引擎根据优化后的执行计划，调用存储引擎的API 执行实际的数据读取或修改操作，包括主键索引查询，索引下推等。如果查询涉及多个表，MySQL会使用相应的算法（如嵌套循环、哈希连接）进行表的连接操作。之后，查询结果被收集并格式化，然后通过网络返回给客户端。如果查询结果太大，可能会采用分批次的方式返回。

MySQL 事务两阶段提交原理简析

Redo log 与 Binlog

MySQL中的日志非常重要，包括实例内的事务以及实例间的主从复制均基于日志实现。MySQL 中最重要的两份日志是 redo log 与 binlog。为什么会有两份日志，原因是使用场景不同。其中：

redo log 用于实现事务的持久性，具体是通过 crash-safe 能力；
binlog 用于实现主从复制与数据恢复。

两份日志主要有以下三点不同；

redo log 是 InnoDB 存储引擎层实现的特有的日志，binlog 是 Server 层实现的通用的日志；
redo log 是物理日志，binlog 是逻辑日志；
redo log 是循环写入，binlog 是追加写入。

重做日志是循环写入的。这意味着数据库系统会预先分配一定数量的重做日志文件，当当前文件写满后，系统会自动切换到下一个文件继续写入。一旦所有文件都被写过一遍，系统会从第一个文件开始覆盖旧的记录。这个过程通常被称为“日志切换”或“日志循环”。

两阶段提交

为了保证两份日志之间的逻辑一致，也就是数据与备份的一致性，引入两阶段提交（two-phase commit protocol，2PC）。由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。假设执行 update，将值从 1 改为 2：

先写 redo log 后写 binlog，如果 redo log 写完后 MySQL 进程异常重启，redo log 崩溃恢复后值为 2，但是基于 binlog 备份恢复值为 1，并导致备份恢复少了一个事务；
先写 binlog 后写 redo log，如果 binlog 写完后 MySQL 进程异常重启，基于 binlog 备份恢复值为 2，但是 redo log 还没写因此崩溃恢复后事务无效，值为 1，并导致备份恢复多了一个事务。

显然，如果没有两阶段提交，无法保证数据与日志的一致性。那么，有两阶段提交时会怎么样呢？

首先，介绍下两阶段提交的过程，其中将 redo log 的提交拆分为两个步骤，包括 prepare 与 commit，期间写入 binlog。

因此，如果在两阶段提交的不同时刻，MySQL 异常重启会发生什么呢？

如果在时刻 A 重启，也就是 redo log prepare 之后，写入 binlog 之前，崩溃恢复时发现 redo log 没有 commit，因此回滚。binlog 还没写，因此不会传到备库，数据与日志保持一致；
如果在时刻 B 重启，也就是写入 binlog 之后，redo log commit 之前，崩溃恢复时发现 redo log 虽然没有 commit，但是 redo log 有完整的 prepare，且对应的事务 binlog 完整，因此提交事务。binlog 写入，因此会传到备库，数据与日志保持一致。

崩溃恢复

崩溃恢复时根据两阶段提交的进度进行处理。崩溃恢复（crash-recovery）时的完整判断逻辑为：

如果 redo log 里面的事务完整，也就是已经有了 commit 标识，直接提交；
如果 redo log 里面的事务只有完整的 prepare，进一步判断对应的事务 binlog 是否存在且完整：
- 如果是，提交事务；
- 否则，回滚事务。

因此，redo log prepare 后 commit 前崩溃恢复时可能发生回滚或提交，具体与 binlog 的完整性有关。

显然，时刻 B 发生 crash 的情况对应 redo log prepare 完整，且 binlog 完整的场景，因此事务提交。这里可以提出以下两个问题：

1）如何判断 binlog 完整

2）如何根据 redo log 定位对应的 binlog

接下来分别回答这两个问题。

1）如何判断 binlog 完整

判断 binlog 的完整性有以下两种方式：

在事务提交时记录 XID event 到 binlog 中以标记事务的结束。这个机制确保了事务的完整性和一致性，无论使用哪种复制格式；
在 MySQL 5.6.2 版本以后，还引入了 binlog-checksum 参数，用于验证 binlog 内容的正确性。通过为 binlog 中的每个事件添加校验和（checksum），MySQL 能够检测到写入 binlog 时由于磁盘错误等原因导致的数据损坏。

如下所示，测试显示 row 与 statement 两种 binlog 格式中事务的最后一个 event 都是 XID event。

2）如何根据 redo log 定位对应的 binlog

redo log 与 binlog 有一个共同的数据字段，称为 XID。崩溃恢复的时候，会按顺序扫描 redo log：

如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。

其中：

redo log 扫描的起点是 InnoDB 最后一次 checkpoint 操作的 lsn（last_checkpoint_lsn）。
XID 与分布式事务有关。

这里可以提出另一个问题，根据事务的持久性，到什么进度后事务将无法回滚？

理论上 MySQL 中通过 redo log 实现事务的持久性，因此 redo log 刷盘后就可以保证对数据库的修改是永久性的，即使发生崩溃也不会丢失，当然也不会回滚。不过根据事务的两阶段提交协议，binlog 写入代表事务提交，同样不可能发生回滚。

因此，事务无法回滚的关键点是事务的提交，而不是单纯的 redo log 或 binlog 的写入。在事务提交的过程中，两阶段提交机制确保了 redo log 和 binlog 的一致性，这个提交过程标志着事务从可回滚转变为不可回滚。

XA 事务

分布式事务是一种跨多个独立的数据库、系统或网络区域的事务处理方法。XA 事务是一种遵循 XA 规范的分布式事务，因此 XA 事务是分布式事务的一种实现。XA 事务依赖两阶段提交（2PC）协议实现分布式事务的一致性和原子性。两阶段提交是最常见的分布式事务协议，用于保证分布式事务的原子性，显然并不是 MySQL 独有的。根据 XA 规范，两阶段提交的实现过程中包括两个角色：

资源管理器（Resource Manager），可以称为执行器，用于管理分布式数据库的一个本地事务；
事务管理器（Transaction Manager），可以称为协调器，用于协调事务的提交、回滚、崩溃恢复。

两阶段提交中将提交操作分为两个阶段：

prepare 阶段，协调器询问所有执行器，是否可以提交事务，如果任何一个执行器的本地事务无法提交时，分布式事务都需要通知所有执行器进行回滚操作；
commit 阶段，协调器在收到每一个执行器的提交确认后，通知执行器各自提交自己的本地事务。

MySQL 中的 XA 事务分为外部 XA 与内部 XA。其中：

外部 XA，MySQL 服务器作为执行器，连接服务器的客户端程序作为协调器，对应多个支持分布式事务的数据库实例，比如多套 MySQL（使用分库分表中间件）、Oracle + MySQL；
内部 XA，对应单个 MySQL 实例，分为以下两种场景：
- 没有开启 binlog，SQL 语句涉及一个或多个支持事务的存储引擎；
- 开启 binlog，SQL 语句涉及一个或多个支持事务的存储引擎。

其中，由于 binlog 与存储引擎是独立单元，可以将 binlog 也看作一个存储引擎，因此需要通过 XA 事务实现 binlog 与存储引擎的数据一致性和原子性，从而保证全部操作要么全部提交，要么全部回滚。

在分布式事务中，XID作为全局事务的唯一标识符，用于跟踪和协调不同数据库实例中的事务部分。这个标识符在事务的所有参与者之间是共享的，以确保事务的一致性和完整性。因此在 XA 事务中，XID用于在多个数据库实例之间协调事务。在 MySQL 中，XID（Transaction Identifier）是事务的唯一标识符，用于标记事务的提交。

Binlog 中一个事务由一系列事件（event）组成，这个序列由 BEGIN 事件开始，以 XID 事件结束（对于提交的事务）。因此如果事务被回滚，不会记录 XID 事件，而是记录一个 ROLLBACK 事件。

参考 ChatGPT，XID 与 GTID 的主要区别包括：

XID：是事务的标识符，用于标记事务的结束，主要用于事务的恢复和复制过程中确定事务边界。对于分布式事务，所有 MySQL 实例使用相同的 XID 来提交事务；
GTID（全局事务标识符）：是 MySQL 5.6 及更高版本中引入的，用于唯一标识每个事务。每个 GTID 都是全局唯一的，即使在不同的 MySQL 实例中也是如此。GTID 使得跟踪和复制事务变得更加简单和可靠。

prepare

MySQL 事务二阶段提交与 MySQL 核心模块揭秘 | 07 期 | 二阶段提交 (1) prepare 阶段，prepare 阶段做的事情分为两类：

binlog prepare，对应 binlog_prepare 函数，什么都不做；
InnoDB prepare，对应 innobase_xa_prepare 函数，具体做五件事情：
- 把分配给事务的所有 Undo segment 的状态 TRX_UNDO_STATE 从 TRX_UNDO_ACTIVE 修改为 TRX_UNDO_PREPARED；
- 把事务 XID 写入所有 Undo segment 中当前提交事务的 Undo Log Segment Header；
- 把内存中的事务对象状态从 TRX_STATE_ACTIVE 修改为 TRX_STATE_PREPARED，标识事务已经进入二阶段提交的 prepare 阶段；
- 如果当前提交事务的隔离级别是读未提交（READ-UNCOMMITTED）或读已提交（READ-COMMITTED)，InnoDB 会释放事务给记录加的共享、排他 GAP 锁；
- 调用 trx_flush_logs()，处理 redo log 刷盘的相关逻辑，其中实际上并不会将 redo log 刷盘，也就是同样什么都不做。

其中 undo log 非常重要，原因是：

TRX_UNDO_STATE 用于崩溃恢复过程中，标记哪些事务需要恢复，哪些事务不用恢复。
XID 用于崩溃恢复过程中，决定数据库崩溃时处于 prepared 阶段的事务，是要回滚还是要提交。

参考文章 XA事务与两阶段提交。

Undo页面链表的第一个页面的结构见下图，其中记录了一些关于这个事务的一些属性。

其中 Undo Log Segment Header 结构见下图，其中 TRX_UNDO_STATE 字段表示事务所处的状态。

其中 Undo Log Header 结构见下图。

其中：

TRX_UNDO_XID_EXISTS：表示有没有 XID 信息；
XID信息：表示具体的 XID 是什么。

TRX_UNDO_STATE 的取值包括：
TRX_UNDO_ACTIVE：活跃状态，也就是一个活跃的事务正在往这个段里边写入 undo log；
TRX_UNDO_CACHED：被缓存的状态。处在该状态的 Undo 页面链表等待着之后被其他事务重用；
TRX_UNDO_TO_FREE：对于 insert undo 链表来说，如果在它对应的事务提交之后，该链表不能被重用，那么就会处于这种状态。Undo 页面链表可以被马上清理；
TRX_UNDO_TO_PURGE：对于 update undo 链表来说，如果在它对应的事务提交之后，该链表不能被重用，那么就会处于这种状态。Undo 页面链表不可以被马上清理，而是加入 History 链表用于 MVCC，等待 purge 线程清理；
TRX_UNDO_PREPARED：包含处于 prepare 阶段（这个阶段是在分布式事务中会出现）的事务产生的 undo log。

commit

commit 阶段做的事情同样分为两类：

binlog 刷盘，对应 flush 函数，将事务执行过程中产生的 binlog 写入硬盘；
InnoDB commit，对应 innobase_commit 函数，完成存储引擎层面的事务提交。

具体 commit 阶段的实现与组提交有关。因此，在客户端执行 commit 语句或自动 commit 时，MySQL 开启内部 XA 事务，分两阶段完成 XA 事务的提交。

崩溃恢复

崩溃恢复全过程分为多个阶段，其中与事务两阶段提交有关的阶段包括：

恢复数据页，通过 double write buffer 修复部分页写入（partial page write）导致的数据页损坏；
读取 redo log，从 last_checkpoint_lsn 开始读取 redo log；
应用 redo log 到数据页，将没有写入数据页的日志重做一遍，从而保证事务的持久性；
初始化事务子系统，从 undo 表空间文件读取未完成的事务；
处理未完成事务，其中：
- 如果事务 XID 对应 binlog 已写入文件，事务提交；
- 如果事务 XID 对应 binlog 未写入文件，事务回滚。
- 清理已提交事务，对应 TRX_STATE_COMMITTED_IN_MEMORY，包括 DDL 与 DML；
- 回滚未提交事务，对应 TRX_STATE_ACTIVE，包括 DDL 与 DML；
- 处理 prepare 事务，对应 TRX_STATE_PREPARED，其中：

未完成事务的状态可能是以下三种之一：

TRX_STATE_ACTIVE，表示事务还没有进入提交阶段。
TRX_STATE_PREPARED，表示事务已经提交了，但是只完成了二阶段提交的 PREPARE 阶段，还没有完成 COMMIT 阶段。
TRX_STATE_COMMITTED_IN_MEMORY，表示事务已经完成了二阶段提交的 2 个阶段，还剩一些收尾工作没做，这种状态的事务修改的数据已经可以被其它事务看见了。

其中未提交事务 TRX_STATE_ACTIVE 对应 redo log 已经刷盘的未提交事务，包括以下三种场景：

后台线程定时将 redo log buffer 中的日志刷盘时将事务执行中间过程的 redo log 持久化到磁盘；
redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半时，后台线程会主动写盘，即使事务并没有提交；
并行的事务提交时，顺带将这个事务的 redo log buffer 持久化到磁盘。假设一个事务 A 执行到一半，已经写了一些 redo log 到 buffer 中，这时候有另外一个线程的事务 B 提交，如果 innodb_flush_log_at_trx_commit 设置的是 1，那么按照这个参数的逻辑，事务 B 要把 redo log buffer 里的日志全部持久化到磁盘。这时候，就会带上事务 A 在 redo log buffer 里的日志一起持久化到磁盘。

因此，为了保证事务的原子性，需要在崩溃恢复时将这些未提交事务回滚，而找到这些未提交事务依赖 undo log。

结论

MySQL 通过事务的两阶段提交实现数据与日志的一致性。其中数据指 redo log，日志指 binlog，可以认为是两个不同的存储引擎，因此基于分布式事务的 XID 协议实现一致性。具体实现中将 redo log 的提交拆分为两个步骤，包括 prepare 与 commit，期间写入 binlog。因此，写入的不同阶段异常重启时：

redo log commit crash，binlog 完整，因此事务提交；
binlog crash，redo log 没有 commit，且没有写入 binlog，因此事务回滚。

具体是在崩溃恢复过程中基于两阶段提交保证事务的一致性。其中：

redo log application 阶段用于将没有写入数据页的日志重做一遍，把系统恢复到崩溃前的状态，其中都是提交，没有回滚；
初始化事务子系统阶段从表空间中找到各个 Undo 页面链表的首个页面的页号，然后根据事务的状态处理未完成事务。其中：
- TRX_STATE_ACTIVE，表明是未提交事务，因此回滚事务；
- TRX_STATE_PREPARED，进一步判断 XID 对应 binlog 是否存在，如果有，提交事务，否则回滚事务；
- TRX_STATE_COMMITTED_IN_MEMORY，表明是已提交事务，因此提交事务，具体是清理已提交事务。

因此，可以将崩溃恢复过程中使用的日志的顺序理解为 redo log、undo log、binlog。

支持

资讯

记录些MySQL题集（1）

Innodb 是如何实现事务的？

MySQL 什么时候锁表？如何防止锁表？

MySQL 调优的重点目标：避免锁表

1. 锁表带来的性能问题

2. 锁表引发死锁

3. 锁表降低系统可用性

4. 锁表引发数据一致性问题

MySQL 锁的分类

1.表级锁：

2.行级锁：

3.页级锁：

InnoDB 存储引擎中的表锁和行锁

InnoDB 的表级锁

表级锁之一：表锁

表级锁之二：元数据锁

表级锁之三：意向锁

InnoDB 的行级锁

InnoDB 存储引擎三种行级锁

行级锁之一：记录锁(Record Locks)

行级锁之二：间隙锁(Gap Locks)

行级锁之三：临键锁(Next-Key Locks)

InnoDB 如何加锁？

第1个维度：InnoDB 如何加意向锁?

第2个维度：InnoDB如何加表级锁?：

第3个维度：InnoDB 如何加行级锁?：

MySQL何时锁定整个表？

1.对表进行结构性修改

2.手动锁定表

3.MyISAM写操作

4.两个或多个事务在同时修改一个表时

5.索引操作

6.并发操作:

7 索引不可用时加锁操作

8.索引选择不恰当

9.更新和删除场景，where没命中索引

10.查询场景，索引失效的情况下，行锁升表锁

索引失效原因及其解决方法

10.1. 使用函数或操作符

10.2. 模糊查询

10.3. 不符合最左前缀原则

10.4. 数据类型不一致

10.5. 使用 OR 条件

10.6. 隐式类型转换

10.7. 范围条件

10.8. NULL 判断

10.9. 更新频繁的列

10.10. 查询优化器选择

MySQL会锁表的场景总结

如何减少或避免锁表？

1. 使用合适的存储引擎

2. 优化查询和索引

3. 分解大事务

4. 锁策略和隔离级别

5. 分区和分表

6. 避免长时间的锁定操作

7. 监控和分析

8. 读写分离

9. 使用合适的锁

10. 业务层优化

乐观锁与悲观锁

悲观锁

乐观锁

如何选择

MySQL 执行SQL查询时发生了什么？

1、连接器

2、查询缓存

3、SQL解析器

4、预处理器

5、优化器

6、执行器

MySQL 事务两阶段提交原理简析

Redo log 与 Binlog

两阶段提交

崩溃恢复

XA 事务

prepare

commit