MySQL 逻辑架构

本文内容来自《高性能 MySQL》(第三版)

@MySQL 服务器逻辑架构图 | center

1. 最上层的服务并不是 MySQL 所独有的，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。比如连接的处理、授权认证、安全等等。
2. 第二层架构是 MySQL 比较有意思的部分。大所述 MySQL 的核心服务都在这一层，包含查询解析、分析、优化、缓存以及所有的内置函数（例如：日期、时间、数学和加密函数），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。
3. 第三层包含了存储引擎。存储引擎负责 MySQL 中数据的存储和提取。和 Linux 下的文件系统一样，每个存储引擎都有它的优势和劣势。服务器通过 API 与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。存储引擎 API 包含几十个底层函数，用于执行不同的操作。但是 存储引擎不会去解析一个 SQL 查询^[1]，不同存储引擎之间也不会相互通信，而只是简单地响应上层服务器。

连接管理与安全性

每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，该线程只能轮流在某个 CPU 核心或者 CPU 中运行。服务器会负责缓存线程，因此 不需要为每个新建的连接创建或者销毁线程^[2]。

当客户端（应用）连接到 MySQL 服务器时，服务器需要对其进行认证。认证基于用户名，原始主机信息和密码。如果使用了安全套接字（SSL）的方式连接，还可以使用 X.509 证书认证。一旦客户端连接成功，服务器会继续验证该客户端是否具有执行某个特定查询的权限。

优化与执行

MySQL 会解析查询，会创建内部数据结构（解析树），然后对其进行各种优化，包括重写查询，决定表的读取顺序，以及选择合适的索引等。用户可以通过特殊的关键字提示（hint）优化器，影响它的决策过程。也可以请求优化器解释（explain）优化过程的各个因素，使用户服务器时如何进行优化决策，并提供了一个参考基准，便于用户重构和查询和 schema、修改相关配置，使尽可能高效运行。

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

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

并发控制

无论何时，只要有多个查询需要在同一个时刻修改数据，都会产生并发控制的问题。MySQL 提供了两个层面的并发的控制：服务器层和存储引擎层层。

以 Unix 系统的 email box 为例，典型的 mbox 文件格式是非常简单的。一个 mbox 邮箱中所有的邮件都 串行^[3] 在一起，彼此首尾相连。这种格式对于读取和分析邮件信息非常友好，同时投递邮件也很容易，只要在文件尾部附加新的邮件内容即可。

但是如果两个进程在同一时刻对同一个邮箱投递邮件，会发生什么情况？显然，邮箱的数据会被破坏，两封邮件的内容会交叉地附加在文件的尾部。设计良好的邮箱投递系统会通过锁（lock）来防止数据损坏。如果客户试图投递邮件，而邮件已经被其他客户锁住，那就必须等待，直到锁释放才能进行投递。这种锁的方案虽然在实际应用中能良好的工作，但是并不支持并发处理，因为在任意一个时刻只有一个进程可以修改邮箱的数据，这在大容量的邮箱系统中是个问题。

1. 读写锁

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

• 读锁是共享的，他们之间相互不阻塞。也就是说多个客户端连接可以在同一时间访问同一个资源，他们之间相互不影响。
• 写锁则是排他的，基于安全策略，一个写锁会阻塞其他的读锁或写锁。只有这样才能确保在给定的时间内只有一个用户执行写入，并防止其他用户在同一时间访问正在写入的资源。

2. 锁粒度

一种提高共享资源并发性的策略师让锁定的对象更加具有选择性。尽量只锁定需要修改的部分。更理想的方式是：只对修改的数据片进行精确的锁定。在给定资源的条件下，锁定的数量越少，则系统的并发程度就越高。只要相互之间不发生冲突就可以。

问题是：加锁也需要消耗资源。所得各种操作，包括获取锁，检查锁是否已经解除，释放锁等都会增加系统开销。如果系统在管理锁上花费大量的资源，则在存取数据上的性能就会受到影响

锁策略就是在所锁的开销和数据的安全性之间寻求平衡，这种平衡也会影响性能。大多数数据库都会在表上使用一些复杂的方式来实现级锁（row-level lock）,以便在锁比较多的情况下尽量提供更好的服务。

MySQL 提供了多种选择，其在存储引擎中可以实现自己的锁策略和锁粒度。

• 表锁（table lock）：表锁是 MySQL 中最基本的锁策略，并且是开销最小的策略。它会锁定整张表，一个用户在对表进行操作得时候会先获得锁，这个锁会阻塞其他用户对该表的操作。只有在没有写锁的条件下，其他用户才能获得读锁。读锁之间是不相互阻塞的。另外：写锁比读锁拥有更高等级的优先级，因此一个写锁请求可能会被插入到读锁队列的前面，反之则不行。
• 行级锁（row lock）：行级锁在最大程度的支持并发请求的同时带来了最大的锁开销。行级索只有的存储引擎层面实现，而 MySQL 服务器层没有实现。
• 尽管 MySQL 的存储引擎自己实现了锁策略，但是在进行一些操作得时候，MySQL 服务器会忽略存储引擎的锁策略，而使用自身各种有效的表锁来实现目的。例如 ALTER TABLE 之类的语句。

事务

事务就是一组原子性的 SQL 查询或者说是一个独立的工作单元。如果存储引擎能够成功地对数据库应用所有的全部语句，那么就执行该组查询。如果其中有一条语句因为崩溃或者其他原因无法执行，那么所有的语句都不会执行。也就是说在一个事务内部的所有操作语句，要么全部执行成功要么全部执行失败。

ACID 表示原子性（atomicity）、一致性（consistency）、隔离性（isolation）和持久性（durability）。一个运行良好的事务处理系统必须具有这些标准特征。

• 原子性（atomicity）：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚。对于一个事务来讲，不可能只执行其中的一部分操作；
• 一致性（consistency）：数据库总是从一个一致性状态转到另一个一致性状态。
• 隔离性（isolation）：通常来说，一个事务修改在最终提交修改之前，对其他事务是不可见的。
• 持久性（durability）：一旦事务提交，则其所做的修改就会永久保存在数据库中。此时即使系统崩溃，修改的数据也不会丢失

隔离级别

在 SQL 标准中定义了四种隔离级别，一种级别都规定了一个事务所能做的修改，哪些在事务之间是可见的，哪些在事务之间是不可见的。较低级别的隔离通产更可以执行更高的并发，系统的开销也更低。

• READ UNCOMMITTED（未提交读）：在 READ UNCOMMITTED 中，对于事务中的修改，即使没有提交，对于其他事务也是可见的。事务可以读取无提交的数据，这也被称为是脏读（Dirty Read）。这个级别会导致很多其他的问题，从性能上来说，READ UNCOMMITTED 不会比其他的级别好太多，但是却缺乏其他级别的很多好处，除非真的有非常必要的理由，在实际应用中一般很少使用。

• READ COMMITTED（提交读）：大多数数据库默认的隔离级别是 READ COMMITTED，但是 MySQL 并不是。其满足了一个事务开始时，只能看到已经提交的事务所做的修改。换句话说：一个事务从开始直到提交之前，所做的任何修改对于其他事务都是不可见的。这个级别有时也被称为不可重复读（nonrepeatable read），因为两次执行同样的查询，可能会得到不一样的结果。

• REPEATABLE READ（可重复度）：其解决了脏读的问题，该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上来讲，可重复读隔离级别还是无法解决另外一个 幻读^[4] 的问题。其是 MySQL 默认的事务隔离级别。

• SERIALIZABLE（可串行化）：SERIALIZABLE 是最高级别的事务隔离等级，它通过强制事务串行执行，避免了前面的幻读问题。简单来说：SERIALIZABLE 会在读取的每一行数据上面加上锁，所以可能导致大量的超时和锁争用的问题。实际引用中很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才会考虑该级别。

  隔离级别	脏读可能性	不可重复读可能性	幻读可能性	加锁读
  READ UNCOMMMITTED	yes	yes	yes	no
  READ COMMITTED	no	yes	yes	no
  REPEATABLE COMMITTED	no	no	yes	no
  SERIALIZABLE	no	no	no	yes

死锁

死锁是指两个事务或者多个事务在同一个资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务企图用不同的顺序锁定资源时就会造成死锁。多个事务同时锁定同一个资源时也会造成死锁。

假设现在有两个事务如下：

• 事务 1:

  START TRANSACTION;
  update tablename set name = 'heroic' where id = 5;
  update tablename set name = 'sophia' where id = 4;
  COMMIT;
  

• 事务 2:

  START TRANSACATION;
  update tablename set name = 'root' where id = 4;
  update tablename set name = 'staff' where id = 5;
  COMMIT;
  

如果凑巧两个事务都执行了第一条 update 语句，更新了一行数据，同时也锁定了该行数据。接着每个事务又都去执行第二条 update 语句，却发现资源已经被对方锁定，然后两个事务都等待对方释放锁，同时又持有对方需啊锁，这样就会容易陷入死循环，造成死锁。
InnoDB 目前处理死锁的办法是将持有最少行级排他锁的事务进行回滚。

事务日志

事务日志可以帮助提高事务的执行效率。使用事务日志，存储引擎在修改表的数据的时候只需要修改其内存拷贝。再把该修改行为记录到事务日志中，而不用每次都将修改数据的操作本身持久化到磁盘。事务日志采用追加的方式，因此事务日志的操作是磁盘上一小块位置的顺序 I/O，而不像随机 I/O 那样在在磁盘的多个位置移动磁头。所以使用事务日志的方式相对而言要快得多。事务日志持久以后，内存中被修改的数据可以在后台慢慢地刷回到磁盘。这种方式通常被称作预写式日志（Write-Ahead Logging）。修改数据需要写两次磁盘。

如果数据的修改已经写入到事务日志并持久化，但数据本身还没有会写磁盘，这个时候系统崩溃，这种情况下存储引擎能够在重启时恢复这部分数据。

MySQL 中的事务

Mysql 提供两种事务型操作引擎，INnoDB 和 NDB Cluster，另外还有一些第三方的存储引擎支持事务，比如 XtraDB 等。

• 自动提交：MySQL 默认采用自动提交模式（AUTOCOMMIT）。也就是说如果不显式的开始一个事务，则每个查询都会被当做是一个事务并自动提交。可以通过 SET AUTOCOMMIT 来修改设置启用或者禁用自动提交模式（0 或 OFF 表示禁用，1 或 ON 表示启用）。还有一些操作会强制提交当前事务，比较典型的有在 DDL 中如果导致大量的数据改变的操作，比如 ALTER TABLE 等。
• MySQL 可以通过 SET TRANSACTION LEVEL 的命令来设置事务隔离等级，新的事务隔离等级会在下次事务操作的时候生效。 如：mysql> SET SESSION TRANSACTION LEVEL READ COMMITTED。
• 由于 MySQL 服务器层不管理事务，事务是由下层得存储引擎实现的。所以在同一个事务中，使用多种存储引擎是不可靠的。

InnoDB 存储引擎概览

• InnoDB 的数据在存储在表空间（tablespace）中，表的空间是由 InnoDB 管理的一个黑盒子，有一系列数据文件组成。
• InnoDB 采用 MVCC 来支持高并发。并实现了四个标准的隔离等级。其默认级别是 REPEATABLE READ（可重复读），并通过间隙锁（next-key locking）策略来实现防止幻读。间隙锁使得 InnoDB 不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定。以防幻行的插入。
• InnoDB 是基于聚簇索引的，聚簇索引对于查询有很高的性能。不过它的二级索引（secondary index，非主键索引）中必须包含主键列，所以如果主键列很大的话，其他的所有引擎都很大。因此，若表上的索引比较多的话，主键应该尽可能的小。

---

脏读：是指一个事务读到了另一个事务中修改过但没有提交的数据。假如一个事务失败回滚，那么它做的修改统统被撤销，这就使得前面的一个事务读到了后面事务撤销的垃圾数据。
不可重复度：在一个事务中，再次读取数据的结果和前面一次读取数据的结果不一样。
幻读：事务1读取指定的where子句所返回的一些行。然后，事务2插入一个新行，这个新行也满足事务1使用的查询 where 子句。然后事务1再次使用相同的查询读取行，但是现在它看到了事务2刚插入的行。这个行被称为幻象，因为对事务1来说，这一行的出现是不可思议的。
幻读和不可重复读的区别：幻读的重点在于新增或者删除，导致两次读取的数据行数不一样，而不可重复度的重点在于修改，导致两次查询得到的结果不一样。

---

1. InnoDB是一个例外，他会解析外键定义，因为MySQL服务器本身没有实现该功能。 ↩

2. MySQL5.5或者更新的版本提供了一个API，支持线程池（Thread-Pooling）插件，可以使用少量的线程来服务大量的连接。 ↩

3. 串行通信是指使用一条数据线，将数据按位一位一位地依次传输，每一个数据占据一个固定的时间长度。其只需要少数的几根线就可以在系统间交换信息，特别适合计算机之间或者计算机和外设之间的远距离通信。【百度百科】 ↩

4. 所谓幻读，指的是当某个事务在读取某个范围内记录的时候，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。InnoDB和XtraDB通过多版本并发控制机制解决了幻读问题。 ↩