大纲

• 基础架构
• 日志系统
• 事务隔离
• MySQL索引
• 锁机制
• explain

1、基础架构

MySQL的逻辑架构可以从上到下分为几个主要层次：

• 客户端层
  • 包括了用户的接口，如命令行客户端和图形用户界面（GUI）
• 服务层
  • 连接处理：管理客户端和服务器之间的连接，包括身份认证。
  • SQL接口：接收SQL命令，并且返回查询结果。
  • 解析器：将SQL语句分解成可以理解和执行的部分。
  • 优化器：决定最佳的查询路径。
  • 缓存/缓冲：存储数据，以便更快地访问。
• 存储引擎层
  • 负责MySQL中数据的存储和提取。
  • MySQL的存储引擎是可插拔的，最常见的如InnoDB（支持事务和行级锁定）和MyISAM（支持全文搜索但不支持事务）。
• 存储层
  • 实际存储数据的地方
  • 包括数据文件、索引文件等

每一层在MySQL的运行中扮演着关键角色，并且它们共同工作以处理和存储数据。 客户端层接收用户输入的SQL查询，服务层对这些查询进行处理和优化，存储引擎层执行这些查询并通过存储层对数据进行物理存储和修改。

餐厅与数据库

现在，让我们用一个类比来帮助理解。想象MySQL是一家餐厅：

客户端层像是餐厅的顾客，他们点菜（发送SQL查询）。

服务层是服务员，他们接收订单，解释顾客的需求（解析SQL），并决定如何最有效地准备食物（优化查询）。 存储引擎层像是厨师，根据服务员给出的订单来准备食物（执行SQL查询）。

存储层则是冰箱和储藏室，存储着所有食材（数据）。

在这个过程中，每个员工和每个存储区都必须协调一致，以确保食物（数据）的质量和服务的效率。

默认存储引擎

现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

Server层和存储引擎层

将这个描述与上面的类比结合起来，可以这样理解：

1. Server层：
   • 这一层可以被视为餐厅的前台和服务区域。
   • 连接器：餐厅的接待台，负责迎接顾客（建立客户端连接）。
   • 查询缓存：已经准备好的菜肴，可以立即提供给顾客，无需再次等待厨师准备（缓存之前的查询结果）。
   • 分析器：服务员听顾客点菜并确认菜单上有这道菜（解析SQL查询，检查语法）。
   • 优化器：服务员决定为顾客服务的顺序，以确保效率（决定查询的最优执行计划）。
   • 执行器：服务员将顾客的订单传达给厨师并确保菜肴能够按要求出品（执行查询）。
   • 此层包含所有的内置函数，就像餐厅有各种调味品和烹饪方法可供选择。
   • 跨存储引擎的功能，如存储过程、触发器、视图，都像是餐厅提供的特殊服务，无论使用哪个厨房（存储引擎），都可以提供。
2. 存储引擎层：
   • 这一层相当于餐厅的厨房。
   • 每个存储引擎都像是一个专门的烹饪区域，有着不同的烹饪技术和设备（如InnoDB支持事务，MyISAM提供全文搜索功能）。
   • 存储引擎负责数据的物理存储和检索，就像厨师们管理食材并将其制作成菜肴一样。

对于SQL的解析和优化

• SQL解析：当服务层接收到客户端层发送的SQL语句后，它首先进行语法检查和解析，将SQL分解为解析树。
• SQL优化：然后，优化器会评估多个可能的查询执行计划，并选择一个成本最低（通常是执行时间最短）的计划

连接器

建议在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。

但如果使用长连接，服务器需要一直分配资源给这些连接（在执行过程中临时使用的内存是管理在连接对象里面），随着时间的推移，如果有很多大的操作，它占用的内存就会越来越大，最终可能会耗尽内存，导致数据库服务崩溃。

解决这个问题，有两个方法：

1. 定期断开连接：就像定期清理手机后台运行的应用，以释放内存，保持手机运行流畅。你可以在数据库中，用完连接后或者执行了一些大查询操作之后，断开连接，下次需要时再重新连接。
2. 使用mysql_reset_connection：如果你用的是MySQL 5.7或更高版本，这就像给手机做个快速重启，清除所有运行的程序但不完全关机，这样可以迅速释放内存。mysql_reset_connection命令可以重置连接的状态，但不需要像完全断开连接那样再次进行繁琐的握手和权限验证。

查询缓存

作用：作为查询缓存

1. 当你执行一个SELECT语句时，MySQL会检查查询缓存。
2. 如果之前执行过相同的查询（这里的“相同”意味着字节到字节的完全匹配），并且结果已经缓存，MySQL就会立即返回结果，不用再次执行查询。
3. 查询的文本是缓存的“key”，查询的结果是“value”。

缺点：

• 查询缓存每当表更新时就会失效。如果表经常更新，那么缓存的命中率就会非常低。
• 每次表更新，所有相关的缓存都会被清空。这意味着，即使你的查询结果被存入缓存，但如果表一更新，这些结果就会消失。

因为这些限制，对于那些数据更新频繁的系统，启用查询缓存可能会带来更多的性能开销而不是好处。只有在数据很少变动的情况下，比如某些配置表，查询缓存才可能提高性能。

如果你还是想要利用查询缓存，可以将query_cache_type设置为DEMAND。这样，只有那些你显式指定为SQL_CACHE的查询才会使用查询缓存，就像这样：

SELECT SQL_CACHE * FROM T WHERE ID=10;

但是，值得注意的是，从MySQL 8.0版本开始，查询缓存功能被完全移除了。这是因为在实践中，维护查询缓存通常会导致更多的性能问题，而现代的数据库系统有更高效的方式来提高查询性能，比如更好的索引和优化器策略。

所以，在最新的MySQL版本中，不需要也不能使用查询缓存了。

分析器

• 词法分析
• 语法分析

优化器: 决定查询的最优执行计划

执行器：执行查询（操作存储引擎，返回结果）

存储引擎

MySQL 的存储引擎负责MySQL中数据的存储和提取。其中 MySQL的存储引擎是可插拔的，最常见的如InnoDB（支持事务和行级锁定）和MyISAM（支持全文搜索但不支持事务）。

在 MySQL 中，我们可以使用 SHOW ENGINES 命令来查看 MySQL 支持的所有存储引擎。

我们可以使用 SELECT VERSION() 命令查看你的 MySQL 版本。不同的 MySQL 版本之间的默认存储引擎可能会有差别。

MySQL 5.5.5 之前，MyISAM 是 MySQL 的默认存储引擎。5.5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎。

MySQL 当前默认的存储引擎是 InnoDB。并且，所有的存储引擎中只有 InnoDB 是事务性存储引擎，也就是说只有 InnoDB 支持事务。

InnoDB 存储引擎详细介绍：https://dev.mysql.com/doc/refman/8.0/en/innodb-storage-engine.html 。

常见的几种 MySQL 存储引擎：

MyISAM 和 InnoDB 两种 MySQL 存储引擎在索引实现上的根本区别

1. InnoDB 的索引和数据存储方式：InnoDB 使用的是一种称为聚集索引（Clustered Index）的结构，其中数据表是按照主键顺序存储和组织的，这就意味着表数据本身就是索引的一部分。因此，InnoDB 的数据文件本身就充当了索引的角色，叶节点包含了实际的表数据。这种方式的优点是访问主键索引非常快，但缺点是次级索引（非主键索引）需要存储主键的值来引用实际的数据行。

2. MyISAM 的索引和数据存储方式：与 InnoDB 不同，MyISAM 将索引和数据存储在分开的文件中。它使用非聚集索引，索引文件仅存储数据记录的地址。这意味着无论是主键索引还是次级索引，MyISAM 都需要进行额外的查找步骤来定位实际的数据记录。这种方式的优点是简化了索引的管理，尤其是对于非主键索引的操作；但缺点是访问数据可能需要更多的磁盘I/O，尤其是对主键的查询。

简而言之，InnoDB 的数据文件就是按 B+Tree 组织的聚集索引，数据直接存储在索引的叶节点中，而 MyISAM 使用分离的文件存储数据和索引，索引文件中存储的是指向数据文件中记录的指针。这两种不同的实现方式影响了数据访问的性能和存储效率。

---

2、日志系统

MySQL的日志系统是其核心功能之一，就像黑匣子一样，记录了发生在数据库中的所有关键活动。它包括多种类型的日志，每种日志记录不同类型的信息，对于数据库的恢复、优化和故障排除都至关重要。

下面是MySQL日志系统中最重要的几种日志类型：

• 错误日志
• 查询日志（通用查询日志）
• 二进制日志（binlog）
• 慢查询日志
• 中继日志（Relay Log）
• InnoDB重做日志（Redo Log）
• InnoDB回滚日志（Undo Log）

错误日志

错误日志就像是医生的诊断记录，它记录MySQL服务器启动、运行或停止时遇到的问题。如果数据库出现问题，这是第一个要查看的地方。这里会有关于任何错误的详细信息，包括服务启动失败的原因。

查询日志（通用查询日志）

查询日志记录了所有对MySQL服务器的请求，无论这些请求是否得到了执行。可以把它看作是商店的客流记录器，记录每一个进门的顾客及其请求的商品。

由于记录了所有活动，包括每条SQL语句的文字记录，这个日志很容易变得非常大，因此在高负载系统中通常不推荐开启。

二进制日志（binlog）

二进制日志是数据库活动的详细记录，包括所有修改数据或可能修改数据的语句（如INSERT、UPDATE、DELETE）。可以将二进制日志视为飞机的黑匣子，记录飞行过程中的所有重要事件。

它对于复制和数据恢复是必不可少的，因为它可以用来在另一台服务器上重放数据更改，或者在数据丢失后恢复数据。

慢查询日志

慢查询日志记录执行时间超过特定时长的查询。

将其想象为监控摄像头，特别关注那些在商店里逗留时间过长的顾客。这对于发现那些需要优化的查询非常有帮助。

中继日志（Relay Log）

在MySQL复制设置中，从服务器上的中继日志记录了从主服务器接收到的所有二进制日志事件。

从服务器上的复制子系统会读取这些日志，并应用到从服务器的数据库中。这类似于接力赛中，接力棒在运动员之间传递的过程。

InnoDB重做日志（Redo Log）

InnoDB存储引擎专用的日志，它记录了导致数据库状态改变的所有操作，确保在系统崩溃后能够恢复数据。

可以把它看作是飞机上的飞行记录仪，记录了飞行中所有的操作，以便在事故后重建发生了什么。

InnoDB回滚日志（Undo Log）

回滚日志是InnoDB用来处理事务的，它记录了数据的旧版本，使得数据库能够“回滚”到以前的状态。

可以把它比作是时光机，能够把事情恢复到过去某个时间点的状态。

RedoLog And binlog

InnoDB重做日志（Redo Log）与 二进制日志（binlog）

我们来看一下InnoDB的重做日志（Redo Log）和二进制日志（binlog）的作用和它们在MySQL中的应用。

InnoDB重做日志（Redo Log）

Redo Log：重做日志文件大小是固定的，并组成一个循环写入的序列。（有一个擦除点和写入点，文件满的时候需要先擦除再写入）

在InnoDB存储引擎中，重做日志用于记录对数据库所做的每个写操作，确保在系统崩溃后可以恢复这些操作。

这个过程类似于传统账本与便笺的使用。想象一下，当店主在便笺上快速记下客户的购买记录，之后在每天结束时，他会将这些记录详细地转移到正式的账本中。便笺上的记录对应于重做日志，而正式账本就像是数据库文件。

重做日志是循环使用的，有一个固定的大小。当数据库进行写操作时，InnoDB会将操作详情写入重做日志，并标记为准备（prepare）状态。

随后，在合适的时机，这些操作会被实际应用到磁盘上的数据文件中。

这种策略允许InnoDB引擎快速响应写操作，因为它不需要每次都同步写入磁盘。

---

InnoDB的重做日志（Redo Log）包括两部分：内存中的重做日志缓冲（redo log buffer）和磁盘上的重做日志文件。

1. 重做日志缓冲（Redo Log Buffer）：这是内存中的一部分，用于暂时存放那些还没有被写入到磁盘重做日志文件的数据。当事务被提交时，事务的重做日志信息首先被写入到这个缓冲区中。

2. 重做日志文件（Redo Log Files）：这些是存放在磁盘上的物理文件，用于持久保存重做日志数据。即使在系统崩溃的情况下，由于这些信息被存储在磁盘上，InnoDB也可以在重启后使用这些日志来恢复数据。

关于重做日志文件的大小，它们是固定的，并且可以通过MySQL的配置文件进行设置。通常，重做日志文件组成一个循环写入的序列，这意味着当最后一个日志文件写满后，系统会回到第一个日志文件开始覆盖旧的日志（这取决于日志的写入位置和检查点的位置）。

在配置文件（通常是my.cnf或my.ini）中，可以通过以下参数来定义重做日志文件的大小和数量：

• innodb_log_file_size：定义单个重做日志文件的大小。
• innodb_log_files_in_group：定义重做日志文件组中文件的数量。

例如，如果你设置innodb_log_file_size为512MB，并且设置innodb_log_files_in_group为4，那么总共会有2GB的重做日志空间可用（512MB * 4 = 2048MB或2GB）。

选择适当的重做日志文件大小是很重要的，因为它会影响数据库的性能和恢复能力。如果重做日志太小，可能会导致频繁的I/O操作，因为日志空间会迅速填满并需要频繁地进行日志切换操作。如果太大，恢复过程可能会更慢，因为InnoDB可能需要处理大量的日志数据来恢复状态。通常，这个值应该根据系统的负载和I/O能力来调整。

二进制日志（binlog）

二进制日志记录了所有对数据库进行更改的SQL语句。它不是特定于InnoDB的，而是MySQL服务器层面的日志。你可以把它看作是一部摄像机，记录下了发生的所有事件，无论是好是坏。

在数据库需要恢复或者进行复制设置时，二进制日志是不可或缺的。

与重做日志不同，二进制日志是逻辑日志，记录的是发生了什么（例如“给ID=2的这一行的c字段加1”），而重做日志是物理日志，记录的是在哪个数据页上做了修改。二进制日志是按顺序追加的，并且不会覆盖旧的日志。

两者的协同工作与两阶段提交

两阶段提交：只有在重做日志和二进制日志都被成功写入后，事务才会提交。

当执行一个更新操作时，InnoDB会先写入重做日志，这个操作会标记为prepare状态。一旦这个操作被写入，即使系统崩溃，这个操作也不会丢失，确保了数据库的crash-safe特性。只有在重做日志和二进制日志都被成功写入后，事务才会提交。

这就引入了所谓的“两阶段提交”：

1. 准备阶段：事务的修改先被写入重做日志，并将事务标记为prepare状态。
2. 提交阶段：事务的修改被写入二进制日志，然后InnoDB事务会提交，重做日志的状态会变为commit。

这种机制保证了即使在事务提交过程中发生故障，重做日志和二进制日志的状态也能保持一致。这对于恢复到精确的某一点以及主从复制是必要的，确保了数据的一致性。

应用场景

在应用中，你可以利用重做日志来确保事务的持久性和原子性。

例如，如果你的系统突然断电或者崩溃，重做日志可以用来恢复未提交的事务，以及确保已提交的事务不会丢失。

二进制日志主要用于：

• 数据复制：将binlog内容复制到从服务器，从服务器重放这些操作，保持和主服务器的数据一致。
• 数据恢复：当需要将数据库恢复到过去的某个状态时，可以使用全量备份加上二进制日志来达到这个目的。

两阶段提交保证了在使用重做日志和二进制日志时，系统的状态是一致的，这对于确保数据的准确性和可恢复性非常重要。

思考题： 在什么场景下，一天一备会比一周一备更有优势呢？

日常全量备份相比周备份的优势在于减少了数据丢失的风险。如果发生灾难，你只会丢失一天的数据，而不是一周的。这直接影响了数据库系统的恢复点目标（Recovery Point Objective，RPO），即在数据丢失事件后数据恢复的能力。

每天备份的RPO明显小于每周备份，因此一天一备的策略更适合对数据准确性要求较高的系统。

3、事务隔离

事务就是要保证一组数据库操作，要么全部成功，要么全部失败。

在 MySQL 中，事务支持是在引擎层实现的，MyISAM 引擎是不支持事务的，InnoDB 支持；

下述内容是围绕 InnoDB 引擎来讲述。

隔离性与隔离级别

在MySQL中，事务隔离级别定义了一个事务可能受其他并发事务影响的程度。

这个概念对于理解并发操作时可能出现的问题（如脏读、不可重复读、幻读）至关重要。

MySQL支持以下四种标准的事务隔离级别：

• 读未提交
• 读已提交
• 可重复读
• 串行化

1. READ UNCOMMITTED（读未提交）

这是最低的隔离级别，在这个级别，事务可以读取到其他事务还未提交的更改。

想象一个图书馆，所有书籍即使正在被编辑（数据被修改）也可以被任何人阅读。

这可能导致“脏读”，即一个事务可能读取到另一个事务修改但尚未提交的数据，如果那个事务回滚，读取到的数据就是无效的。

2. READ COMMITTED（读已提交）

在这个级别，一个事务只能读取到其他事务已经提交的更改。

回到图书馆的例子，这就相当于只有当一本书完成编辑并返回到书架上时，其他人才能阅读它。

这解决了脏读的问题，但仍然可能出现“不可重复读”，因为在同一个事务中，同样的查询可能会返回不同的结果，如果其他事务在两次查询之间提交了更改。

3. REPEATABLE READ（可重复读）

MySQL的默认隔离级别。

在这个级别，事务在开始时创建一个数据快照，确保在整个事务期间可以重复读取相同的数据，即使其他事务提交了更改。

在图书馆中，即使书籍被编辑，你也会一直读到你最初找到的版本。虽然解决了不可重复读的问题，但它仍然面临“幻读”的问题，当一个事务中的两个相同的查询可能因为另一个事务的插入操作而返回不同的行数。

4. SERIALIZABLE（串行化）

这是最高的隔离级别，它通过强制事务顺序执行，避免了幻读的问题。在图书馆中，这相当于当你阅读一本书时，其他任何人都不能编辑或检查这本书。这会导致明显的性能下降，因为它阻止了可能的并发事务执行。

不同隔离级别对性能和并发性的影响是一个权衡：

• 更低的隔离级别（如READ UNCOMMITTED和READ COMMITTED）提高了并发性，但牺牲了数据的准确性。
• 更高的隔离级别（如REPEATABLE READ和SERIALIZABLE）提供了更准确的数据和事务一致性，但可能会降低并发性。

选择哪个隔离级别取决于应用程序的特定需求和它可以容忍的数据不一致程度。

例如，银行系统可能会倾向于使用更高的隔离级别以保证事务的准确性，而一个只显示近似数据的报告系统可能会选择一个更低的隔离级别以获得更好的性能。

隔离级别REPEATABLE READ（可重复读）的实现

在MySQL中，REPEATABLE READ（可重复读）隔离级别的实现是通过结合使用多版本并发控制（MVCC）和回滚日志来完成的。

这两种机制共同工作，以确保事务可以在其执行期间多次读取同一数据的一致状态，同时避免由其他并发事务引起的数据不一致。

多版本并发控制（MVCC）

MVCC是可重复读隔离级别的核心，它允许在不加锁的情况下进行读操作，同时仍然保持事务间的隔离。

• 数据版本化：在MVCC中，每当数据被更新时，原始数据不会立即被覆盖。相反，会创建一个新的数据版本。这样，不同的事务可以看到同一数据的不同版本。
• 读视图（Read-View）：当事务开始时，它创建一个读视图，这个视图代表数据库在那一时刻的状态。在整个事务期间，即使数据在其他事务中被更改，该事务也能看到其读视图中的数据版本。

回滚日志

回滚日志是实现MVCC的关键组成部分，它用于记录每个数据版本的历史信息。

• 版本链：每当数据项被更新，回滚日志就会记录一个操作，这些操作形成了一条版本链。例如，一个值从1改为2，再改为3，最后变为4，回滚日志会记录这一系列变化。
• 保留历史数据：这些回滚操作允许数据库“回滚”到早期的状态。因此，即使当前数据值是4，不同的事务依据它们的读视图可能看到的值是1、2或4。

回滚日志什么时候删除？

• 在不需要的时候才删除。也就是说，系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。
• 什么时候才不需要了呢？就是当系统里没有比这个回滚日志更早的 read-view 的时候。

实现原理

结合MVCC和回滚日志，可重复读隔离级别在MySQL中的实现可以这样理解：

1. 事务开始时创建读视图：事务开始时捕捉数据库的瞬时快照，这个快照定义了事务能看到的数据版本。
2. 读取操作时使用回滚日志：当事务中的操作需要读取数据时，系统会利用回滚日志找到该事务读视图对应的数据版本。
3. 隔离性的保证：即使在事务执行过程中，其他事务已经提交了对相同数据的更改，该事务仍然只能看到其开始时的数据版本。
4. 回滚日志的维护：回滚日志会一直保留，直到没有事务需要早期的数据版本为止。这确保了即使在有长事务存在的情况下，数据的早期版本仍然可用。

长事务的影响

由于长事务可能会访问旧版本的数据，MySQL必须保留长事务开始之前的所有回滚信息。这不仅占用了大量存储空间，而且可能影响系统性能。

例如，在MySQL 5.5及以前的版本中，大量的回滚日志可能导致ibdata文件过大，即使长事务最终提交，这些空间也不会自动释放，有时需要重建整个数据库来清理这些空间。

因此，虽然REPEATABLE READ提供了较强的隔离性，但也需要合理管理事务的生命周期，避免长事务对系统性能和存储空间的负面影响。

---

刚刚看到一个概念，叫 Next-Key Locking。他可以保持在可重复隔离级别下，同时避免脏读的一种锁机制。

可串行化的化就完全是强制事务顺序执行，而 Next-Key Locking 的话是结合了行锁和间隙锁，避免了一定范围内出现脏读。

Next-Key Locking

Next-Key Locking 是 InnoDB 存储引擎特有的锁机制，它结合了行锁和间隙锁（gap lock）的特性。Next-Key Lock 实际上锁定了一个范围，不仅锁定了行记录本身，还锁定了记录之间的间隙，从而防止其他事务在这个范围内插入新的记录。

作用与效果

• 作用：通过 Next-Key Locking，MySQL 能够确保在可重复读隔离级别下，一个事务在其执行期间看到的数据范围保持不变，即使有其他事务试图在这个范围内插入新的数据，也会因为锁的存在而被阻塞，直到原事务完成。这样就有效地避免了幻读问题。
• 效果：因此，在可重复读隔离级别下，MySQL 能够有效解决幻读问题，确保事务的一致性和隔离性。

简而言之，通过 Next-Key Locking 机制，MySQL 在可重复读隔离级别下通过锁定数据范围和间隙来防止幻读，从而保证了数据的一致性和事务的隔离性。

和串行化的区别

Next-Key Locking 不是串行化（Serializable）隔离级别，而是一种锁机制，主要用于 InnoDB 存储引擎在可重复读（Repeatable Read）隔离级别下防止幻读问题的一种实现方法。串行化隔离级别和可重复读隔离级别是事务隔离级别的两种不同标准，它们在处理并发事务时有不同的特性和行为。

Next-Key Locking

• Next-Key Locking 是 InnoDB 特有的一种锁机制，结合了行锁和间隙锁（Gap Lock）。
• 它锁定一个范围而不仅仅是单个行记录，包括记录本身和记录之间的间隙。
• 主要用在可重复读（Repeatable Read）隔离级别下，用于防止幻读问题。

串行化隔离级别（Serializable）

• 串行化是最高的事务隔离级别，它通过强制事务顺序执行来避免并发事务带来的问题，包括脏读、不可重复读和幻读。
• 在串行化隔离级别下，读操作会加锁，使得其他事务不能同时进行写操作。
• 串行化通过对所有读取的行加锁达到与单线程顺序执行相同的效果，从而保证了数据的绝对一致性。

区别

• 隔离级别：Next-Key Locking 是可重复读隔离级别下防止幻读的一种锁机制，而串行化是一个事务隔离的标准级别，它通过加锁来序列化所有的事务。
• 性能和并发：由于 Next-Key Locking 只锁定需要的数据范围，所以可重复读级别下的并发性能比串行化级别更好。串行化隔离级别通过加锁限制了并发，虽然数据一致性最强，但性能开销也最大。
• 应用场景：Next-Key Locking 在需要防止幻读同时又要保持较高并发性的场景下使用较多。串行化适用于对数据一致性要求极高的场景，但并发要求不高。

总结来说，Next-Key Locking 是一种锁机制，用于可重复读隔离级别下的 InnoDB 存储引擎，而串行化是最高等级的事务隔离级别，两者在并发控制和性能开销上有明显的差异。

事务的启动方式

事务的启动方式：显示启动事务；自动提交模式

在MySQL中，事务的启动方式对于事务管理和性能优化至关重要。理解和正确使用不同的事务启动方式可以避免长事务带来的风险。下面是MySQL中常见的几种事务启动方式：

1. 显式启动事务

• 使用语句启动：最直接的启动事务的方式是使用BEGIN或START TRANSACTION语句。这种方式下，事务会持续到执行COMMIT或ROLLBACK语句为止。COMMIT用于提交事务中的所有更改，而ROLLBACK用于撤销所有更改。
• 显式控制：这种方法的优点是开发者对事务的开始和结束有明确的控制，这有助于避免不必要的长事务。

2. 自动提交模式

• 设置自动提交：通过执行SET autocommit=0，可以关闭当前线程的自动提交。这意味着即使执行了一个简单的SELECT查询，也会启动一个事务，并且事务不会自动提交。
• 潜在风险：这种模式下，如果开发者忘记了手动提交或回滚事务，或者在长连接中使用此模式，可能会无意中创建长事务，这会占用大量资源并影响数据库性能。

3. 推荐的实践

• 默认自动提交：建议总是使用SET autocommit=1。通过显式的BEGIN、COMMIT和ROLLBACK语句来控制事务，这样可以清晰地管理事务的生命周期。
• 减少交互：对于需要频繁启动事务的场景，可以使用COMMIT WORK AND CHAIN语法。在autocommit=1的情况下，这个命令不仅提交当前事务，还会自动开始一个新事务。这减少了每个事务开始时执行BEGIN的需求，同时保持了事务的显式控制。

4. 监控长事务

• 检测长事务：可以通过查询information_schema.innodb_trx表来监控长时间运行的事务。例如，要查找运行时间超过60秒的事务，可以使用类似以下的查询语句：

SELECT * FROM information_schema.innodb_trx 
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;

这对于识别可能影响数据库性能的长事务非常有用。

通过以上方法，开发者可以更有效地管理MySQL事务，避免因误用或不当的事务管理导致的性能问题。

明确的事务界定，结合对长事务的监控，有助于维持数据库的健康状态和高效性能。

---

问题：作为业务开发负责人兼数据库负责人，如何避免长事务的出现并妥善处理这种情况。

长事务可能导致多种性能问题，如锁争用、事务日志膨胀、主从延迟等。

以下是一些有效的策略和最佳实践：

• 1. 教育和培训
• 2. 代码审查
• 3. 监控和报警
• 4. 性能分析
• 5. 优化事务管理
• 6. 应用架构设计
• 7. 测试和仿真
• 8. 环境管理

3. 监控和报警

• 事务持续时间监控：监控事务的持续时间，使用如information_schema.innodb_trx的查询来识别长事务。
• 实时报警系统：实现实时报警系统，当事务超过预设阈值时发出警告。

4. 性能分析

• 分析慢查询日志：定期检查慢查询日志，找出可能导致长事务的慢SQL语句。
• 使用性能分析工具：使用如MySQL Workbench等工具分析数据库性能，寻找优化点。

5. 优化事务管理

• 短事务原则：鼓励使用短事务原则，尽量减少事务的作用范围和持续时间。
• 避免不必要的事务：有些读取操作可能不需要事务，特别是在只读操作中。

4、数据库索引 🐱

在MySQL中，数据库索引是一种特殊的数据结构，它们帮助数据库快速高效地定位和检索数据。

你可以将索引类比为图书的目录：就像目录使你能够快速找到你想要阅读的章节，数据库索引允许数据库快速找到存储在数据表中的特定数据，而不必扫描整个表。

索引的类型

按数据结构分类可分为：B+tree索引、Hash索引、Full-text索引。
按物理存储分类可分为：聚簇索引、二级索引（辅助索引）。
按字段特性分类可分为：主键索引、普通索引、前缀索引。
按字段个数分类可分为：单列索引、联合索引（复合索引、组合索引）。

索引(B+树)

B+ Tree 原理

参考：

• https://www.pdai.tech/md/db/sql-mysql/sql-mysql-b-tree.html
• https://www.yuque.com/snailclimb/mf2z3k/wghadf

索引为什么使用 B+ 树

引入各种树解决的问题以及面临的新问题

• 二叉查找树（BST)
  • 不平衡
  • 二叉查找树的平均时间复杂度是 O(lgn)；但是在极端情况下，BST会退化为链表，此时时间复杂度为 O(n)
• 平衡二叉树（AVL)
  • 旋转耗时
  • 删除的一个耗时效率较低，比起使用的好处而言，综合使用并不广泛
• 红黑树
  • 树太高
  • 红黑树在内存的表现优异；但是在真正存储在磁盘的时候，考虑的偏重是需要减少IO次数
• B树
  • 为磁盘而生
  • 将二叉树改为了多路平衡查找树
• B+ 树
  • 在 B 树的基础上，进一步降低了树的高度，部分改造，使得范围查询更加高效。

5、锁机制

MySQL的锁机制是一种用来管理多个事务对同一数据进行访问的机制，旨在保持数据的一致性和完整性。

锁机制允许多个事务同时读写数据库，同时防止数据冲突和不一致。（锁是一种常见的并发事务的控制方式）

MySQL中的锁主要可以分为以下几种类型：

• 表级锁（Table-level Locks）
• 行级锁（Row-level Locks）
• 页面锁（Page-level Locks）
• 共享锁和排他锁
• 意向锁（Intention Locks）
• 死锁（Deadlocks）

1. 表级锁（Table-level Locks）

• 简单但开销小：锁定整个表，是最简单的锁策略，开销最小。
• 并发性低：不适合高并发操作，因为它阻止了对同一表的其他访问。
• 应用：MyISAM存储引擎主要使用表级锁。

2. 行级锁（Row-level Locks）

• 高并发处理：只锁定需要访问的数据行。是最细粒度的锁，允许高度的并发。
• 开销大：比表级锁更多的内存和CPU资源。
• 死锁：可能会引起死锁，需要额外的逻辑来处理。
• 应用：InnoDB存储引擎使用行级锁。

3. 页面锁（Page-level Locks）

• 中间粒度：锁定内存中的页面，介于表级锁和行级锁之间。
• 并发和资源：平衡了并发性和资源开销。
• 应用：一些存储引擎如BerkeleyDB使用页面锁。

4. 共享锁和排他锁

• 共享锁（Shared Locks）：又称读锁，允许事务读一行数据。
  • 读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
• 排他锁（Exclusive Locks）：又称写锁，允许事务排他地写一行数据。
  • 又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。
  • 排他锁与任何的锁都不兼容，共享锁仅和共享锁兼容。

5. 意向锁（Intention Locks）

• 层级锁系统：用于支持表级锁和行级锁的兼容。
• 类型：包括意向共享锁和意向排他锁。

6. 死锁（Deadlocks）

• 问题：当多个事务相互等待对方释放锁时发生。
• 解决：MySQL会自动检测并解决死锁，通常是通过回滚一个事务来解锁。

锁策略的选择

• 不同的存储引擎支持不同的锁策略。例如，InnoDB支持行级锁和表级锁，而MyISAM主要使用表级锁。
• 锁的选择取决于多种因素，包括事务的类型、并发级别、数据表的大小等。

锁机制的实际应用

• 在高并发的数据库系统中，适当的锁策略对于维护数据的完整性和提高系统性能至关重要。
• 理解不同锁的行为和影响可以帮助数据库管理员和开发人员优化查询，减少死锁，并提高数据库的整体性能。

参考 https://javaguide.cn/database/mysql/mysql-questions-01.html#mysql-锁

表级锁和行级锁

概念：

1. 表级锁（Table-level Locks）

• 简单但开销小：锁定整个表，是最简单的锁策略，开销最小。
• 并发性低：不适合高并发操作，因为它阻止了对同一表的其他访问。
• 应用：MyISAM存储引擎主要使用表级锁。

2. 行级锁（Row-level Locks）

• 高并发处理：只锁定需要访问的数据行。是最细粒度的锁，允许高度的并发。
• 开销大：比表级锁更多的内存和CPU资源。
• 死锁：可能会引起死锁，需要额外的逻辑来处理。
• 应用：InnoDB存储引擎使用行级锁。

在实际的使用中，MySQL 目前默认的存储引擎 InnoDB 中，默认为行级锁，同时 InnoDB 也支持表级锁。

与之对应的 MyISAM 存储引擎，它只支持表级锁（table-level locking），一锁就锁整张表。

行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁），所以对于并发写入操作来说， InnoDB 的性能更高。

表级锁和行级锁对比：

• 表级锁： MySQL 中锁定粒度最大的一种锁（全局锁除外），是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。不过，触发锁冲突的概率最高，高并发下效率极低。表级锁和存储引擎无关，MyISAM 和 InnoDB 引擎都支持表级锁。
• 行级锁： MySQL 中锁定粒度最小的一种锁，是 针对索引字段加的锁 ，只针对当前操作的行记录进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。行级锁和存储引擎有关，是在存储引擎层面实现的。

行级锁的使用有什么注意事项？

InnoDB 的行锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁。当我们执行 UPDATE、DELETE 语句时，如果 WHERE条件中字段没有命中唯一索引或者索引失效的话，就会导致扫描全表对表中的所有行记录进行加锁。这个在我们日常工作开发中经常会遇到，一定要多多注意！！！

不过，很多时候即使用了索引也有可能会走全表扫描，这是因为 MySQL 优化器的原因

行锁分类

InnoDB 有哪几类行锁

InnoDB 行锁是通过对索引数据页上的记录加锁实现的，MySQL InnoDB 支持三种行锁定方式：

• 记录锁（Record Lock）：也被称为记录锁，属于单个行记录上的锁。
• 间隙锁（Gap Lock）：锁定一个范围，不包括记录本身。
• 临键锁（Next-Key Lock）：Record Lock+Gap Lock，锁定一个范围，包含记录本身，主要目的是为了解决幻读问题（MySQL 事务部分提到过）。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

在 InnoDB 默认的隔离级别 REPEATABLE-READ 下，行锁默认使用的是 Next-Key Lock。但是，如果操作的索引是唯一索引或主键，InnoDB 会对 Next-Key Lock 进行优化，将其降级为 Record Lock，即仅锁住索引本身，而不是范围。

一些大厂面试中可能会问到 Next-Key Lock 的加锁范围： https://segmentfault.com/a/1190000040129107

---

意向锁

作用： 通过意向锁来快速判断是否可以对某个表使用表锁。

意向锁是表级锁，共有两种：

• 意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
• 意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

意向锁是由数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。

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

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

意向锁和共享锁和排它锁互斥（这里指的是表级别的共享锁和排他锁，意向锁不会与行级的共享锁和排他锁互斥）。

	IS 锁	IX 锁
S 锁	兼容	互斥
X 锁	互斥	互斥

---

再看一下下面的这个解释：

意向锁是 InnoDB 引擎用来优化行锁和表锁之间操作的一种锁机制。其主要目的是为了在数据库中实现多粒度锁定（即同时使用行锁和表锁），并且能够提供一种机制来判断是否可以安全地对整个表加锁。意向锁是表级锁，但它是用来表明事务在接下来的操作中，对表中行记录所计划使用的锁类型（共享或排他）。

意向锁的作用

• 快速判断表锁兼容性：意向锁让数据库快速知道是否可以不检查每一行而对整个表加锁。例如，如果一个事务已经对某行加了排他锁，那么此时不能对整个表加共享锁，因为存在不兼容的行锁。
• 避免死锁：通过先获取意向锁再获取具体的行锁，可以减少死锁的可能性。

意向锁的类型

• 意向共享锁（IS锁）：表明事务打算对表中的某些行加共享锁。在加共享锁之前，必须先获得表的IS锁。
• 意向排他锁（IX锁）：表明事务打算对表中的某些行加排他锁。在加排他锁之前，必须先获得表的IX锁。

工作原理

当事务想要对某个数据行进行操作（读取或修改）时，它会根据操作类型（是否会修改数据）尝试在该行上加共享锁或排他锁。但在此之前，事务必须先在整个表上获取对应的意向锁（IS或IX）。这样，当另一个事务尝试对整个表加锁时，它可以通过检查表上的意向锁来快速确定是否存在行级锁冲突，而不必检查表中每一行的锁状态。

用户透明

对于数据库的使用者而言，意向锁是完全透明的，即用户无需手动管理意向锁。InnoDB 存储引擎会自动管理意向锁的获取和释放，以确保数据库操作的一致性和并发性。

总结来说，意向锁是一种表级锁，用于声明事务对表中行记录的加锁意图，它使得数据库能够在保持高并发的同时，有效地管理和调和行级锁和表级锁之间的关系。

6、explain的使用

在MySQL中，EXPLAIN是一个非常有用的工具，它用于分析MySQL如何执行SQL查询，特别是用于优化查询性能。

使用EXPLAIN可以帮助你理解MySQL如何处理你的SQL语句，包括选择哪些索引，如何连接表，以及估计的行数等。

如何使用EXPLAIN

要使用EXPLAIN，只需在你的SELECT语句之前加上关键字EXPLAIN。例如：

EXPLAIN SELECT * FROM your_table WHERE your_column = 'some_value';

这条命令会返回一个结果集，展示了MySQL执行该查询的计划。

EXPLAIN输出的关键列

EXPLAIN的输出包含了许多列，以下是一些最重要的列：

1. id：表示SELECT的序列号，用于区分查询中不同部分的执行顺序。
2. select_type：查询的类型，如SIMPLE（简单表，即不使用表连接或子查询），PRIMARY（主查询），SUBQUERY（子查询）等。
3. table：显示这一行的数据是关于哪个表的。
4. type：表示连接类型，是优化中非常重要的一个指标，从最好到最差依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。
5. possible_keys：显示可能应用在这张表上的索引。
6. key：实际使用的索引。如果为NULL，则没有使用索引。
7. key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好。
8. ref：显示索引的哪一列被使用了。
9. rows：MySQL认为必须检查的用来返回请求数据的行数。
10. Extra：包含MySQL解决查询的详细信息。比如，“Using index”表示相应的SELECT操作使用了覆盖索引，避免了访问表的数据行。

使用EXPLAIN进行性能优化

通过分析EXPLAIN的输出，你可以获得如何优化查询的线索：

• 确保适当的索引被使用：如果key列是NULL，或者type列显示为ALL或index，可能需要优化索引。
• 检查连接顺序：在id列中，较大的值表示优先级较高的表，你可以通过调整查询结构来改变连接顺序。
• 优化查询结构：根据Extra列的信息，你可能需要调整查询结构，如更改JOIN类型或重新编写子查询。

注意事项

• EXPLAIN不执行查询：它只显示MySQL如何执行查询，实际上并不执行该查询。
• 估计的行数可能不准确：rows列显示的是估计的行数，这个估计可能基于表统计数据，而不是实际的行数。

平时用这个进行分析语句的时候，看的最多的三个字段是：select_type、key、rows

• select_type : 查询类型，有简单查询、联合查询、子查询等
• key : 使用的索引
• rows : 扫描的行数

性能优化注意的点比较多，建议看一下这个：

• https://www.pdai.tech/md/db/sql-mysql/sql-mysql-performance.html

性能优化策略

• 优化数据访问
  • 1. 减少请求的数据量
  • 2. 减少服务器端扫描的行数
• 重构查询方式
  • 1.切分大查询
  • 2.分解大连接查询

大纲

7、分库分表

MySQL的分表分库是一种数据库架构设计方法，用于处理大规模数据和高并发访问。

随着数据量的增长和访问量的提升，单一数据库或表可能难以承受压力，这时通过分表分库可以有效地提高性能和可扩展性。

分表（Sharding）

分表是指将一个大表拆分成多个小表的过程。

这些小表可以分布在同一个数据库或多个数据库中。分表通常基于某些关键字段进行，比如时间、用户ID等。

• 垂直分表：将表中不同的列分到不同的表中。例如，一个用户表可以被分成用户基础信息表和用户详细信息表。
• 水平分表：根据行数据将表分成多个表，每个表包含相同的列，但只包含部分行。例如，基于用户ID范围或创建时间进行切分。

分库（Database Sharding）

分库涉及将数据分布到多个数据库实例上。每个数据库实例可以托管在不同的服务器上，从而分散负载和提高容错能力。

• 分布式数据库：数据被分散存储在多个物理位置，每个数据库实例可以独立处理查询和事务。
• 读写分离：常与分库结合使用，读操作和写操作分别在不同的数据库实例上进行。

在实现MySQL的分表分库时，可能需要使用一些工具和中间件，如MyCAT、Shard-Query等，它们帮助管理复杂的分片逻辑，并对应用层透明。

8、主从复制和读写分离

在MySQL中，主从复制和读写分离是两种常用的架构策略，用于提高数据库的可用性、扩展性和性能。

主从复制（Master-Slave Replication）

主从复制是一种数据复制的方法，其中数据从一个主服务器（Master）自动复制到一个或多个从服务器（Slave）。

工作原理

1. 日志复制：主服务器的更改（如INSERT、UPDATE、DELETE操作）被记录到二进制日志中。
2. 日志传输：这些更改（日志条目）被传输到从服务器。
3. 应用更改：从服务器读取这些日志条目，并在自己的数据副本上应用这些更改。

目的和优势：

• 数据备份：提供了数据备份，有助于灾难恢复。
• 读取负载分散：从服务器可以用于读取操作，减轻主服务器的负载。
• 高可用性：如果主服务器出现故障，从服务器可以被提升为新的主服务器。

主从辅助操作主要涉及三个线程: binlog 线程、I/O 线程和 SQL 线程。

• binlog 线程 : 负责将主服务器上的数据更改写入二进制日志中。
• I/O 线程 : 负责从主服务器上读取二进制日志，并写入从服务器的中继日志中。
• SQL 线程 : 负责读取中继日志并重放其中的 SQL 语句。

读写分离（Read-Write Splitting）

读写分离涉及将数据库的读取操作（SELECT查询）和写入操作（INSERT、UPDATE、DELETE）分离到不同的服务器上。

实现方法

• 主服务器处理写操作：所有更改数据的操作都在主服务器上执行。
• 从服务器处理读操作：所有的读取请求都被重定向到从服务器。

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。

小结

读写分离和主从复制可以通过MySQL的内置功能实现，也可以使用第三方工具如ProxySQL、MaxScale等来帮助管理。

主从复制和读写分离是提高MySQL数据库性能和可靠性的重要策略。

它们在处理大量数据和高并发访问的情况下尤其有用，但也需要考虑其带来的额外管理和配置复杂性。

参考

• https://www.bilibili.com/video/BV1ve411F794
• https://gitee.com/moxi159753/LearningNotes/tree/master/MySQL/MySQL45讲
• https://javaguide.cn/database/mysql/mysql-questions-01.html#mysql-存储引擎
• https://gitee.com/moxi159753/LearningNotes/tree/master/MySQL/MySQL45讲
• https://developer.huawei.com/consumer/cn/forum/topic/0204405591412170236
• https://javaguide.cn/database/mysql/mysql-questions-01.html
• https://www.pdai.tech/md/db/sql-mysql/sql-mysql-overview.html
• https://www.yuque.com/snailclimb/mf2z3k/wghadf
• https://blog.csdn.net/qq_44766883/article/details/105879308
• https://javaguide.cn/database/mysql/mysql-questions-01.html
• https://www.pdai.tech/md/db/sql-mysql/sql-mysql-devide.html
• https://www.bilibili.com/video/BV1yT411H7YK