1. MySQL架构逻辑

image.png
上述为MySQL服务器的逻辑架构图

  • 第一次层服务并不是MySQL独有的，他们大多数是基于网络的客户端/服务器工具；
  • 第二层架构是大多数MySQL的核心服务功能所在的一层，包括查询解析、分析、优化、缓存以及所有的内置函数（例如：日期、时间、数学和加密函数），所有跨存存储引擎功能都在这一层实现：存储过程、触发器、视图等
  • 第三层包含了存储引擎。存储引擎负责MySQL中数据的储存和提取，每个存储引擎都有它的优势和劣势，服务器通过API与存储引擎进行通信，接口屏蔽了不同储存引擎之间的差异，使得不同差异对上层的查询过程透明。存储引擎API包含几十个底层函数，用于执行类似“开始一个事务”或者“根据主键提取一行记录”等操作。但存储引擎不会解析SQL（InnoDB是一个例外，它会解析外键定义，因为MySQL服务器本身没有实现该功能）

1.1 连接管理与安全性

每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，该线程只能轮流在某个CPU核心或者CPU中运行，服务器会负责缓存线程。

1.2 优化与执行

MySQL会解析查询，并创建内部数据结构（解析树），然后对其进行优化，包括重写查询、决定表的顺序读取，以及选择合适的索引等。用可以用通过特殊的关键字提示（hint）优化器，影响它的决策过程。也可以使用请求优化解释（explain）查看服务器的优化决策。

传送门 -> MySQL 查询性能优化

优化器不关心表用的存储引擎，但存储引擎对于优化查询是有影响的。优化器会查询储存引擎提供的容量或某个具体操作的开销信息，以及表数据的统计信息等。

传送门 -> 关于索引与 schema 的优化

对于SELECT语句， 在解析查询之前，服务器会先检查 查询缓存（Query Cache）能够在其中找到对应的查询，服务器就不必再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。

2. 并发控制

只要有多个查询需要在同一时刻修改数据，就会产生控制并发的问题。

1.1 读写锁

在处理并发读或者写时，可以通过实现一个由两种类型的锁组成的锁系统来解决问题。这两种类型的锁通常被称为共享锁（shared lock）和排他锁（exclusive lock），也称作读锁（read lock）和写锁（write lock）。

  • 读锁是共享的，相互不阻塞，多个客户可以在同一时刻同事读取同一资源互不干扰；

  • 写锁是排他的，一个写锁会阻塞其他的写锁和读锁，同一时刻只能有一个用户执行写入，并防止其他用户读取正在写入的资源

    1.2 锁粒度

    提高共享资源并发性的其中一种方式就是，尽量只锁定需要修改的部分数据，而不是所有资源。锁定的数量越少，则系统的并发成都越高。加锁也是需要消耗资源的。锁的操作包括获得锁、检查锁是否已经解除、释放锁等，都会增加系统的开销。所以合适的锁策略，就是在锁的开销和数据的安全性之间寻求一种平衡。大多数商业数据库系统没有提供更多的选择，一般都是在表上施加行级锁（row-level lock）。

    表锁（table lock）

    MySQL 中基本锁策略且是开销最小的策略，会锁定整张表。一个用户在对表进行写操作（插入、删除、更新等）前，需要先获得写锁，这会阻塞其他用户对该表的所有读写操作；没有写锁时，其他读取的用户可以获得写锁，且读锁之间是不相互阻塞的。某些特定场景中，表锁也可能有良好的性能。例如，READ LOCAL 表锁支持某些类型的并发写操作；写锁比读锁有更高的优先级，写锁请求可能会被插入到读锁队列的前面，反之则不能。服务器执行 ALERT TABLE 之类的语句会使用表锁，而忽略存储引擎的锁机制。

    行级锁（row lock）

    行级锁可以最大程度地支持并发处理（同时也带来了最大的锁开销）。在InnoDB和XtraDB，以及其他的一些存储引擎中实现了行级锁。行级锁只能在存储引擎层实现，而MySQL服务器层没有实现。

    1.3 事务

    事务就是一组原子性的 SQL 查询，或者说是一个独立单元；该组下的 SQL 要么全部都执行成功，要么就全部不成功。一个运行良好的事务处理系统，必须满足ACID标准特征。

    ACID

  • 原子性 atomicity

一个事务必须视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败 回滚。

  • 一致性 consistency

数据库总是从一个一致性的状态转换到另一个一致性的状态。

  • 隔离性 isolation

通常来说，一个事务所做的修改在最终提交之前，对其他事务是不可见的。

  • 持久性 durability

一旦事务提交，则其他所做的修改就会永久保存到数据库中。

隔离级别

关于隔离性，在SQL标准中定义了四种隔离级别。

  • READ UNCOMMITTED （未提交读）

在 READ UNCOMMITTED 级别中，事务中的修改，即使没有提交，对其他的事务也都是可见的。事务可以读取 未提交的数据，这也被称为脏读（Dirty Read）。这个级别会导致很多问题，从性能来说，READ COMMITTED 不会比其他的级别好太多，但缺乏其他级别的很多好处，不推荐。

  • READ COMMITTED （提交读）

一个事务开始时，能看“看见”已经提交的事务所做的修改。或是说，一个事务从开始直到提交前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫不可重复读（nonrepeatable read）。大多数数据库默认隔离级别都是 READ COMMITTED（MySQL不是）

假如事务A、事务B同时开始事务，属性 a 初始值为 10， 事务A读取了 a 为10，此时事务B将 a 设置为了20，这时事务A查询 a 的值仍为10，此时事务B提交了事务，事务A再次查询 a 的值就变为 20 了。虽然事务A并未提交，但多次查询同一结果值不一致。这也就是为什么叫不可重复了，因为A在未提交事务前，多次查询的值有可能不一致。

  • REPEATABLE READ （可重复读）

REPEATABLE READ 解决了脏读的问题。该级别保证了同一个事务中无法多次读取同样记录的结果是一致的。该隔离界别下无法解决另一个问题幻读（Phantom Read）。幻读是指当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当前的事务再次读取该范围内的记录时，会产生幻行（Phantom Row）。

InnoDB 和 XtraDB 存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）解决了幻读的问题。 RR 的隔离级别下， 默认采用 Next-Key Locks，就是 Record Lock 和 Gap Lock 的结合，即除了锁住记录本身，还要锁住索引之间的间隙，Gap Lock 机制默认打开，并不会产生幻行。 MVCC 可以解决快照读的幻读问题，但当前读的幻读问题是通过 Next-Key Lock 解决的。

  • SERIALIZABLE （串行化）

SERIALIZABLE 是最高的隔离级别。它通过强制事务串行执行，避免了前面说的幻读情况的问题。SERIALIZABLE 会在读取的每一行上都加锁，所以可能导致大量的超时和锁征用的问题。实际应用中很少用到。

死锁

死锁是指两个及以上的事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致死锁。
InnoDB存储引擎将持有最少行级排它锁的事务进行回滚来解决死锁问题

事务日志

使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用的是追加方式，写日志的操作是磁盘上一小块区域内的顺序I/O， 而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。事务日志持久化以后，内存中被修改的数据在后台可以慢慢地刷回到磁盘。目前大多数存储引擎都是这样实现的，我们通常称之为预写式日志（Write-Ahead Logging），修改数据需要写两次盘。
如果修改的数据已经记录到事务日志并持久化，但本身数据还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动回复这部分修改的数据。

1.4 多版本并发控制

MySQL 的大多事务性存储引擎实现都不是简单的行级锁。基于提升并发性能的考虑，它们一般都同时实现了多版本并发控制（MVCC）。不仅是MySQL，包括 Oracle、PostgreSQL 等其他数据库系统也都实现了MVCC。
MVCC可以视为行级锁的变种，在大多数情况下避免了加锁操作，因此开销更低。大多数的读操作都是非阻塞的，写操作也是只是锁定必要的行。
MVCC是通过保存在某个时间的快照实现的，每个事物看到的数据都是一致的。有基于乐观（optimistic）并发控制和悲观（pessimistic）并发控制。

InnoDB 的 MVCC

InnoDB 的 MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。存储的并不是实际的时间值，而是系统版本号（system version number）。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

REPEATABLE READ 隔离级别下，MVCC具体操作

SELECT
InnoDB 会根据以下两个条件检查每行记录，只有符合以下两个条件才能作为结果返回：
a. InnoDB 只查找版本早于当前事务版本的数据行。保证读取到的行，要么在事务开始前已经存在，要么是事 务资深插入或修改过的。
b. 行的删除版本要么未定义， 要么大于当前事务版本号。保证读取到的行，在事务之前未被删除。
INSERT
InnoDB 为新插入的每一行保存当前系统版本号作为版本号
DELETE
InnoDB 为删除的每一行保存当前系统版本号作为行删除表示
UPDATE
InnoDB 为插入一行新纪录，保存当前版本号作为行版本号，同时保存当前系统版本号到原来行作为行删除表示。

*MVCC 只在 REPEATABLE READ 和 READ COMMITTED 两个隔离级别下工作。

3.总结

  1. MySQL 的逻辑架构 | 第一层 | 网络客户端/服务器工具 | | —- | —- | | 第二层 | 查询解析、分析、优化、缓存以及所有的内置函数等跨存储引擎功能的实现 | | 第三层 | 存储引擎、负责MySQL中数据的储存和提取 |

  2. 引入出并发控制问题

简单介绍锁的种类，共享锁、排他锁或是读锁、写锁。共享锁或读锁通常是不会相互阻塞的，排他锁或写锁会阻塞会阻塞其他的锁。
根据锁的粒度可分为，表级锁和行级锁，表级锁是 MySQL 锁策略开销最小的一种， 写锁的优先级是高于读锁的。
事务的ACID属性，原子性、一致性、隔离性、持久性。
介绍了四种隔离级别，未提交、已提交读、可重复读、串行化 四种

脏读 幻读 幻行
未提交读 √ √ √
已提交读 X √ √
可重复读 X X √
串行化 X X X

其中已提交读和可重复读，可通过MVCC 多版本并发控制来避免幻行的情况。

  1. 事务日志

开启事务日志数据会先对内存里数据拷贝进行操作，之后把行为追加记录到硬盘上一块顺序I/O区域，事务日志持久化后，内存中被修改的数据再写回磁盘上，一共需要进行两次操作。

  1. MVCC 多版本并发控制实现

InnoDB存储引擎为每个数据航创建了两个隐藏字段，分别是数据行创建时的事务版本号，数据行删除时的事务版本号。在做 CUID 的时候用版本号来控制是否是当前事务内或当前事务之前就产生的数据。