和其他数据库相比，MySQL 有点与众不同，它的架构可以在不同场景中应用并发挥好的作用，但同时也会带来一点选择上的困难。MySQL 并不完美，却足够灵活。

MySQL 最重要的、最与众不同的特性是它的存储引擎结构。这篇文章基于“高性能 MySQL（第三版）”内容，主要表达 MySQL 的服务器架构、MVCC 以及不同存储引擎的区别。

1.MySQL 逻辑架构

image.png

  • 最上层服务，大多数基于网络的客户端/服务端的工具或者服务都具备类似的架构。这里主要有连接处理、授权认证、安全等功能。
  • 第二层是 server 层，大多数核心服务都在这一层，比如查询解析、分析、优化、缓存以及所有的内置函数等都在这一层实现。
  • 第三层是存储引擎。MySQL 存储引擎负责 MySQL 中数据的存储和提取。服务器通过 API 与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对于上层来说是透明的。

1.1 连接管理与安全性

  1. 每个客户端连接都拥有一个线程，这个连接的查询只会在这个单独的线程中执行。
  2. 服务器会负责缓存线程（类似于线程池的感觉），因此不需要为每一个连接新建的连接创建或销毁线程。
  3. 客户端连接到 MySQL 服务器，服务器先认证身份（主机信息、用户名、密码等），再验证其权限。这一部分就是经典的“认证授权”功能。

1.2 优化与执行

  1. MySQL 解析查询并创建内部数据机构（解析树），然后优化，比如重写查询、决定表的读取顺序、以及选择合适的索引等等。
  2. 用户可以通过特殊的关键提示优化器，影响他的决策。
  3. 用户可以使用 explain 命令，请求优化器解释它优化过程。这样用户就能基于此来重构查询或 schema。
  4. 优化器不关心下层存储引擎用的是什么，但后者对前者的优化查询是有影响的。（比如前者请求后者提供表的统计信息、某个具体操作的开销信息等）
  5. 对于 Select 语句，会先去查询缓存，能命中缓存则不进行后续操作。

2.并发控制

无论何时，多个查询需要在同一时刻修改数据，都会产生并发控制问题。
一般来说，只要给操作加锁（lock）就可以避免这种并发问题，可是这样 MySQL 性能就很会很差。

2.1 读写锁

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

基本概念：

  1. 读锁是共享的，即多个读锁之间相互不阻塞。
  2. 写锁是排他的，也就是说一个写锁会阻塞其他写锁和读锁。写锁之间排斥很好理解，至于写锁阻塞读锁是出于安全策略考虑，防止一个写入的资源被其他人读取。 |
    |
    | 读锁 | 写锁 | | —- | —- | —- | —- | | 读锁 | √ | √ | × | | 写锁 | √ | × | × |

注：√代表共享，×代表互斥（排他）。

2.2 锁粒度

尽量只锁定需要修改的部分数据，而不是所有的资源。问题是细粒度的加锁，更加消耗资源。
锁的各种操作如获得锁、检查锁、释放锁等都会增加系统开销。

所谓的锁策略，就是在锁的开销与系统安全性之间寻求平衡。MySQL 最重要的两种锁策略是表锁（table lock）和 行锁（row lock）。这两种策略顾名思义，这里就不细说。

3.事务

事务就是一组原子性的 SQL 查询，事务内的语句，要么全部执行成功，要么全部执行失败。
事务具有 ACID 这几个概念，分别是原子性（atomicity），一致性（consistency）、隔离性（isolation）、持久性（duration），这里不展开。

MySQL 中的事务默认是自动提交（autocommit）的，可以通过命令手动提交。

3.1 死锁

死锁指的是两个或多个事务占用着自己的资源，并请求锁定对方占用的资源，从而造成恶性循环的现象。
为了解决此类问题，数据库系统实现了各种死锁检测和死锁超时机制。InnoDB 存储引擎目前处理死锁的方式是，将持有最少行级排它锁的事务进行回滚（粗暴但简单实用）。

死锁发生之后，只有部分或者完全回滚其中一个事务，才能打破死锁（外力干预）。在解除死锁后，还需要考虑那些因死锁而回滚的事务该如何处理，一般来说，只需要将这些事务重新执行即可。

3.2 事务日志

  1. 事务日志可以提高事务的效率。
  2. 使用事务日志，存储引擎在修改表数据的时只需要修改其内存拷贝，再把该修改行为记录在磁盘上的事务日志中，而不用每次将修改的数据本身持久化为磁盘。
  3. 第二点中，事务日志也要写磁盘，为啥还提高效率呢？原因是写日志是追加方式，这部分操作是一小块区域上的 I/O 操作，而写数据则可能在多个地方移动磁头。
  4. 事务日志持久化之后，内存中的值在后台可以慢慢的刷回磁盘，这一般称之为预写式日志（Write-Ahead Logging），修改数据需要两次写盘。
  5. 即使内存中的数据未写盘而 MySQL 服务崩掉，也能在下一次启动后通过事务日志自动恢复这部分的数据。

4.多版本并发控制（MVCC）

MySQL 的大多数事务型存储引擎实现的都不是简单的行级锁，出于并发考虑，如果简单的行级锁会导致并发度降低，所以一般采用了行级锁的“变种”来实现即 MVCC。

MVCC 并没有一种标准，故不同的数据库系统实现机制也不尽相同。 MVCC 的实现，是通过保存数据在某个时间点的快照来实现的。 MVCC 在很多情况下可以避免加锁操作，因此开销更低。

4.1 InnoDB 的 MVCC

  1. 以每行记录后面保存两个隐藏的列的方式来实现。
  2. 两列分别是“行创建时间”、“行过期（删除）时间”，存储的值是系统的版本号（system version number）。
  3. 系统版本号类似主键，全局上的，每开始一个事务，系统版本号就会递增一个。

在 Repeatable Read 隔离级别下，MVCC 的操作：
Select：

  1. 只查找版本号早于当前事务版本的行数据（行的版本号要小于等于当前事务的版本号）。
  2. 行的删除列版本号要么不存在，要么得大于当前事务的版本号。

Insert：
为新插入的行保存当前系统版本号作为其行版本号（行创建时间）。
Delete：
为删除的每一行保存当前系统版本号作为其删除（过期）版本号。
Update：
结合Insert和 Delete 两个操作。
插入一行新纪录，并保存当前版本号作为其行版本号，同时保存当前系统版本号作为其删除（过期）版本号。

这样一来，很多读操作基本不需要加锁，提高了系统并发性。缺点就是需要额外的存储空间，以及额外的行检查工作和维护工作。

PS：MVCC 只在 Repeatable Read 和 Read Committed 两种模式下有效。（具体原因请看“高性能 MySQL”书籍）

5.MySQL 的存储引擎

  1. show table status 可查看表的信息，如 Engine 代表当前表的存储引擎类型。

6.MySQL 时间线

7.开发模式

8.总结