1 MySQL 架构与历史

1 概述

2 MySQL 逻辑结构

 MySQL 逻辑架构图如下。

• 最上层主要负责连接管理与安全性，MySQL 客户端与服务端的通信连接管理以及用户名密码、对数据库及表的权限校验，都是在这一层完成的。
• 第二层包括 MySQL 核心服务功能，像查询解析、分析、优化、缓存、所有的内置函数以及所有跨存储引擎的功能（存储过程、触发器、视图等）都在这一层。
• 第三层就是存储引擎层，存储引擎负责数据的存储和提取，服务器通过一套标准的 API 可以和不同的存储引擎进行通信，这些 API 屏蔽了不同存储引擎之间的差异。

2.1 连接管理与安全性

• 每个客户端连接都对应服务器进程中的一个线程，该线程只能轮流在某个 CPU 核心或者 CPU 中运行。服务器会缓存线程，因此不需要为每一个新建的连接创建或者销毁线程。 注意 MySQL 5.5 或更新的版本提供了一个 API，支持线程池插件，可以使用池中少了的线程来服务大量的连接。
• 客户端连接到 MySQL 时，服务器需要对其进行认证。认证基于用户名、原始主机信息和密码。如果使用了安全套接字（SSL）的方式连接，还可以使用 X.509 证书认证。 一旦客户端连接成功，服务器会继续验证该客户端是否具有执行某个特定查询的权限。

2.2 优化与执行

 MySQL 会解析查询，并创建内部数据结构（解析树），然后对其进行各种优化，包括重写查询、决定表的读取顺序，以及选择合适的索引等。用户也可以通过（hint）优化器影响它的决策过程、（explain）优化器解析优化过程的各个因素。
 优化器并不关心表使用的是什么存储引擎，但存储引擎对于优化查询是有影响的。优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息等。例如，某些存储引擎的某种索引，可能对一些特定的查询有优化。
 对于 SELECT 语句，在解析查询之前，服务器会先检查查询缓存（Query Cache），如果能够在其中找到对应的查询，服务器就不必再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。

3 并发控制

 MySQL 是通过共享锁（shared lock）和排他锁（exclusive lock），也叫读锁（read lock）和写锁（write lock）来控制并发的。

• 读锁是共享锁，是互不阻塞的；多个客户端在同一时刻可以同时读取同一资源而不互相干扰。
• 写锁是排他的，一个写锁会阻塞其它的写锁和读锁，这是出于安全策略的考虑，只有这样，才能确保在给定的时间里，只有一个用户能够执行写入，并防止其他用户读取正在写入的统一资源。

 另外加锁也需要消耗资源，锁策略就是在锁的开销和数据的安全性之间寻求平衡。MySQL 存储引擎都可以实现自己的锁策略和锁粒度。MySQL 有下面几种锁策略（锁粒度）。

• 1 表锁（table lock）
  • 1.1 是 MySQL 中最基本的锁策略，并且是开销最小的策略。
  • 1.2 它会锁定整张表。
  • 1.3 写锁比读锁有更高的优先级，即写操作可能会插队到读操作前面。写操作会阻塞其他用户对该表的所有读写操作，没有写锁时，其他用户才能获得读锁。
  • 1.4 尽管存储引擎可以管理自己的锁，MySQL 本身也会使用各种有效的表锁来实现不同的目的。例如，服务器会为诸如 ALTER TABLE 之类的语句使用表锁，而忽略存储引擎的锁机制。

• 2 行级锁（row lock）
  • 2.1 行级锁可以最大程度地支持并发处理（同时也带来了最大的所开销）。
  • 2.2 行级锁只在存储引擎层实现，服务器层完全不了解存储引擎中的锁实现。InnoDB 和 XtraDB 实现了行级锁。
• 3 间隙锁（gap locking）
  • 3.1 MySQL 5.6 之后新增的功能，待补充。

4 事务

 事务就是一组原子性的 SQL 查询或者说一个独立的工作单元。
 事务的 ACID 特性如下。

• 原子性：即不可分割性，事务要么全部被执行，要么就全部不被执行。
• 一致性或可串性。事务的执行使得数据库从一种正确状态转换成另一种正确状态。
• 隔离性。在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务。
• 持久性。事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存。

4.1 隔离级别

 为了达到事务的四大特性，数据库定义了 4 种不同的事务隔离级别，由低到高依次为 Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

• 脏读（Drity Read）：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个 RollBack 了操作，则后一个事务所读取的数据就会是不正确的。
• 不可重复读（Non-repeatable read）:在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
• 幻读（Phantom Read）:在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列（Row）数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

 SQL 标准定义了四个隔离级别：

• READ-UNCOMMITTED(读未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。实际应用中一般很少使用。
• READ-COMMITTED(读已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。实际应用中因为性能及数据一致性问题，也很少用到这个隔离级别。

 MySQL 默认采用的 REPEATABLE_READ 隔离级别，Oracle 默认采用的 READ_COMMITTED 隔离级别。
 InnoDB 和 XtraDB 存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）解决了幻读的问题，后续会进行了解。

4.2 死锁

 死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。
 数据库系统实现了各种死锁检测和死锁超时机制。越复杂的系统，比如 InnoDB 存储引擎，越能检测到死锁的循环依赖，并立即返回一个错误。目前 InnoDB 处理死锁的方法是，将持有最少行级排他锁的事务进行回滚（这是相对毕竟简单的死锁回滚算法）。
 锁的行为和顺序是和存储引擎相关的。以同样的顺序执行语句，有些存储引擎会产生死锁，有些则不会。死锁的产生有双重原因：有些是因为真正的数据冲突，有些是由于存储引擎的实现方式导致的。

4.3 事务日志

 事务日志可以提高事务的效率。

• 存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久化在硬盘上的事务日志中，而不用每次都将修改的数据本身持久化到磁盘。
• 事务日志采用的是追加的方式，即使用的是磁盘上一小块区域的顺序 I/O，而不像随机 I/O 需要在磁盘的多个地方移动磁头，因此会快些。
• 事务日志持久化后，内存中被修改的数据在后台可以慢慢地刷回到磁盘。目前大多数存储引擎都是这样实现的，这种机制通常也称为预写式日志（Write-Ahead Logging），修改数据需要写两次磁盘。

 如果数据的修改以及记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。具体的恢复方式则视存储引擎而定。

4.4 MySQL 中的事务

 MySQL 提供了两种事务型的存储引擎：InnoDB 和 NDB Cluster。另外还有一些第三方存储引擎也支持事务，比较知名的包括 XtraDB 和 PBXT。

• 1 自动提交
  • 1.1 MySQL 默认采用自动提交（AUTOCOMMIT）模式。可以设置 AUTOCOMMIT 变量来启用或禁用自动提交模式：SET AUTOCOMMIT = 1; 或查看 SHOW VARIABLES LIKE 'AUTOCOMMIT';。
  • 1.2 修改 AUTOCOMMIT 对非事务型的表，不会有任何影响。
  • 1.3 另外有些命令，在执行之前会强制执行 COMMIT 提交当前的活动事务。如 ALTER TABLE、LOCK TABLES。具体需看官方文档确认所有可能导致自动提交的语句列表。
• 2 隔离级别
  • 2.1 MySQL 可以通过执行 SET TRANSACTION ISOLATION LEVEL 命令来设置隔离级别（当前会话生效，全局生效得改配置文件）。新的隔离级别会在下一个事务开始的时候生效。
• 3 在事务中混合使用存储引擎
  • 3.1 事务中混合使用了是事务型和非事务型的表，正常提交不会有问题，但是事务回滚时，非事务型表上的变更就无法撤销。因此需注意存储引擎的选用。
• 4 隐式和显示锁定
  • 4.1 隐式锁定：InnoDB 采用的是两阶段锁定协议，会根据隔离级别在需要的时候自动加锁。在事务执行过程中，随时都可以执行锁定，锁只有在执行 COMMIT 或者 ROLLBACK 的时候才会释放，并且所有的锁是在同一时刻被释放。
  • 4.2 显示锁定：如 SELECT ... LOCK IN SHARE MODE;SELECT ... FOR UPDATE，这些语句不属于 SQL 规范，实际上应当尽量避免使用。

5 多版本并发控制（MVCC）

 大多数事务型存储引擎一般都实现了 MVCC，但各自的实现机制不尽相同，因为 MVCC 没有一个统一的实现标准。
 可以认为 MVCC 是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。
 MVCC 的实现，是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。
 典型的 MVCC 实现有乐观并发控制和悲观并发控制两种。

5.1 InnoDB 的 MVCC

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

• 1 SELECT
  • 1.1 InnoDB 会根据以下两个条件检查每行记录，只有符合下面两个条件的记录，才能返回作为查询结果：
    • 1.1.1 InnoDB 只查找版本早于当前事务版本的数据行，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。
    • 1.1.2 行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。
• 2 INSERT
  • 2.1 InnoDB 为新插入的每一行保存当前系统版本号作为行版本号。
• 3 DELETE
  • 3.1 InnoDB 为删除的每一行保存当前系统版本号作为行删除标识。
• 4 UPDATE
  • 4.1 InnoDB 为插入一行新纪录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

 保存这两个额外系统版本号，使大多数读操作都可以不用加锁。这样设计的好处使得读操作很简单，性能很好，并且也能保证只会读取到符合标准的行，不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。
 MVCC 只在 REPEATABLE READ 和 READ COMMITTED 两个隔离级别下工作，其他两个隔离级别和 MVCC 不兼容。因为 READ UNCOMMITTED 总是读取最新的数据行，而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁。
 可以参考博文来理解 MySQL 中的 MVCCopen in new window。

6 MySQL 的存储引擎

 这里简单了解下，不过多深入存储引擎。
 在文件系统中，MySQL 将每个数据库保存为数据目录下的一个子目录。创建表时，MySQL 会在数据库子目录下创建一个和表同名的 .frm 文件保存表的定义（还有 MYD、MYI、ibd、par 文件之类的，这里不展开讨论）。因为 MySQL 使用文件系统的目录和文件来保存数据库和表的定义，大小写敏感性和具体的平台密切相关。在 Windows 中，大小写是不敏感的；而在类 Unix 中则是敏感的。不同的存储引擎保存数据和索引的方式是不同的，但表的定义则是在 MySQL 服务层统一处理的。
 可以使用 SHOW TABLE STATUS 命令（在 MySQL 5.0 以后的版本中，也可以查询 INFORMATION_SCHEMA 中对应的表）显示表的相关信息。如下示例，具体列含义不一一介绍。

mysql> show table status like 'city';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| city | InnoDB |      10 | Dynamic    |    8 |           2048 |       16384 |               0 |            0 |         0 |             10 | 2022-03-22 00:50:00 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.01 sec)

6.1 InnoDB 存储引擎

 它是 MySQL 的默认事务型引擎，也是最重要、使用最广泛的存储引擎。它被设计用来处理大量的短期（short-lived）事务，短期事务大部分情况是正常提交的，很少会回滚。InnoDB 的性能和自动崩溃恢复特性，是的它在非事务型的需求中也很流行。如果没有非常特别的原因，应该优先考虑 InnoDB 引擎。

• InnoDB 的数据存储在表空间（tablespace）中，表空间是由 InnoDB 管理的一个黑盒子，由一系列的数据文件组成。在 MySQL 4.1 以后的版本中，InnoDB 可以将每个表的数据和索引存放在单独的文件中。InnoDB 也可以使用裸设备作为表空间的存储介质，但现代的文件系统使得裸设备不再是必要的选择。
• InnoDB 采用 MVCC 来支持高并发，并且实现了四个标准的隔离级别。其默认级别是 REPEATABLE READ （可重复读），并且通过间隙锁（next-key locking）策略防止幻读的出现。间隙锁使得 InnoDB 不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。
• InnoDB 表是基于聚簇索引建立的，后面在索引中会详细介绍聚簇索引。
• InnoDB 内部做了很多优化，包括从磁盘读取数据时采用的可预测预读，能够自动在内存中创建 hash 索引以加速读操作的自适应哈希索引（adaptive hash index），以及能够加速插入操作的插入缓冲区（insert buffer）等。
• InnoDB 的行为是非常复杂的，不容易理解。可以阅读官方手册中的 “InnoDB 事务模型与锁” 一节，加深了解。
• 作为事务型的存储引擎，InnoDB 通过一些机制和工具支持真正的热备份。MySQL 的其他存储引擎不支持热备份，要获取一致性视图需要对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

6.2 MyISAM 存储引擎

 在 MySQL 5.1 及之前的版本，它是默认的存储引擎。MyISAM 提供了大量的特性，包括全文索引、压缩、空间函数（GIS）等，但 MyISAM 不支持事务和行级锁，而且有一个缺陷就是崩溃后无法安全恢复。

• 1 存储
  • 1.1 MyISAM 会将表存储在两个文件中：数据文件和索引文件，分别以 .MYD 和 .MYI 为扩展名。MyISAM 表可以包含动态或者静态（长度固定）行。MySQL 会根据表的定义来决定采用何种行格式。MyISAM 表可以存储的行记录数，一般受限于可用的磁盘空间，或者操作系统中单个文件的最大尺寸。
  • 1.2 MyISAM 在不同的 MySQL 版本中，指针长度不一致，因此默认支持的配置数据大小不一致，可以修改表的 MAX_ROWS 和 AVG_ROW_LENGTH 选项来调高指针的长度，来改变支持的存储大小。
• 2 特性
  • 2.1 加锁和并发。对整张表加锁，而不是针对行；有读取操作时，写操作会插队（并发写入）。
  • 2.2 修复。MySQL 可以手工或者自动执行检查和修复操作，但这里说的修复是另外的概念，即可以通过 CHECK TABLE mytable 检查表的错误，如果有错误可以通过 REPAIR TABLE mytable 进行修复。另外，如果 MySQL 服务器已经关闭，也可以通过 myisamchk 命令行工具进行检查和修复操作。
  • 2.3 索引特性。MyISAM 对于 BLOB 和 TEXT 等长字段，也可以基于其前 500 个字符创建索引。MyISAM 也支持全文索引。
  • 2.4 延迟更新索引键（Delayed Key Write）。如字面意思。
• 3 压缩表
  • 3.1 如果表在创建并导入数据以后，不会再进行修改操作，那么这样的表或许适合采用 MyISAM 压缩表。
  • 3.2 压缩表是不能修改的（除非先将表解除压缩，修改数据，然后再次压缩），压缩表可以极大地减少磁盘空间占用，因此也可以减少磁盘 I/O，从而提高查询性能。压缩表也可以支持索引，但索引也是只读的。
  • 3.3 目前的硬件能力，对大多数应用场景，读取压缩表数据时的解压带来的开销影响并不大，而减少 I/O 带来的好处则要大得多。压缩时比表中的记录是独立压缩的，所以读取单行的时候不需要去解压整个表（甚至也不解压行所在的整个页面）。
• 4 性能
  • 4.1 数据以紧密格式存储，在某些场景下的性能很好。
  • 4.2 有一些扩展限制，如对索引键缓冲区的 Mutex 锁，MariaDB 基于段（segment）的索引键缓冲区机制来避免实现该问题。但 MyISAM 最典型的性能问题还是表锁的问题（如所有的查询都长期处于 “Locked” 的状态）。

6.3 MySQL 内建的其他存储引擎。

 这里简单的例举下。

• Archive 引擎。一个针对高速插入和压缩做了优化的简单引擎。
• Blackhole 引擎。没有任何的存储机制，即不保存任何数据，但是服务器会记录 Blackhole 表的日志，在某些特殊的复制架构和日志审核时发挥作用。这个使用坑比较多，一般不使用。
• CSV 引擎。它可以将普通的 CSV 文件作为 MySQL 的表来处理，但这种表不支持索引。
• Federated 引擎。是访问其他 MySQL 服务器的一个代理。使用也经常带来问题，因此默认是禁用的。MariaDB 使用了它的一个后续改进版本，叫做 FederatedX。
• Memory 引擎。数据保存在内存中，不需要进行磁盘 I/O。
• Merge 引擎。MyISAM 引擎的一个变种。是由多个 MyISAM 表合并而来的虚拟表。引入分区功能后，该引擎已经被放弃。
• NDB 集群引擎。

6.4 第三方存储引擎

 这里简单的例举下。

• 1 OLTP 类引擎。
  • 1.1 XtraDB。是基于 InnoDB 引擎的一个改进版本。
  • 1.2 和 InnoDB 类似的存储引擎，如 PBXT 等。
  • 1.3 TokuDB。大数据存储引擎。
  • 1.4 RethinkDB。
  • 1.5 Falcon。被取消很久了。
• 2 面向列的存储引擎。
  • 2.1 Infobright。
  • 2.2 InfiniDB。
• 3 社区存储引擎
  • 3.1 Aria。计划替代 MyISAM 的一款引擎，后面取消了。
  • 3.2 Groonga。全文索引引擎。
  • 3.3 OQGraph。支持图操作的引擎。
  • 3.4 Q4M。支持队列的引擎。
  • 3.5 SphinxSE。为 Sphinx 全文索引搜索服务器提供了 SQL 接口。
  • 3.6 Spider。提供了分区功能，可以针对分片执行并行查询。
  • 3.7 VPForMySQL。支持垂直分区。

6.5 选择合适的引擎

 主要综合考虑以下因素。

• 事务。
• 备份。
• 崩溃修复。
• 特有的特性。

 大部分情况下，InnoDB 都是正确的选择。

6.5.1 推荐示例

1. 日志型应用。  MyISAM 或者 Archive 存储引擎对这类应用比较合适，因为它们开销低，而且插入速度非常快。
    如果需要对记录的日志做分析报表，可以考虑下面两种方案。
   （1）主从复制数据，主库只用于高效的插入工作，从库执行比较消耗时间和 CPU 的查询。 （2）在日志记录表的名字中包含年和月的信息，这样可以在已经没有插入操作的历史表上做频繁的查询操作，而不会干扰到最新的当前表上的插入操作。

2. 只读或者大部分情况下只读的表。  建议使用 InnoDB，虽然 MyISAM 适用与读多写少的场景，但其崩溃恢复问题很麻烦。

3. 订单处理。  需要事务，InnoDB 是最佳选择。

4. 电子公告牌和主题讨论论坛。  这种应用场景核心表的读写压力可能非常大，不同存储引擎对某些特殊处理逻辑不一样，比如 mysql > select count(*) from table; 不是所有的存储引擎运行该查询都很快。

5. CD-ROM 应用。  可以考虑使用 MyISAM 表或者 MyISAM 压缩表。

6. 大数据量。  数据量过大的话，某些数据引擎不适合，比如 TB 级数据量时，使用 MyISAM，崩溃后的数据恢复就是一个噩梦。

6.6 转换表的引擎

 有三种方法。

1. ALTER TABLE。  示例 ALTER TABLE mytable ENGINE = INNODB;，该语法适用于任何存储引擎。但是它需要执行很长时间。MySQL 会按行将数据从原表复制到一张新的表中，在复制期间可能会消耗系统所有的 I/O 能力，同时原表上会加上读锁。如果转换表的存储引擎，将会失去和原引擎相关的所有特性。比如 InnoDB 表转换为 MyISAM，然后再转换回 InnoDB，原 InnoDB 表上所有外键将丢失。

2. 导出和导入。  使用 mysqldump 工具导入导出，同时注意修改对应的存储引擎即可。

3. 创建与查询。  使用类似下面语法。

mysql > CREATE TABLE innodb_table LIKE myisam_table;
mysql > ALTER TABLE innodb_table ENGINE=InnoDB;
mysql > INSERT INTO innodb_table SELECTT * FROM myisam_table;

 这种数据量不大的话，可以直接上述处理。如果数据量过大，以避免大事务产生过多的 undo，需要做分批处理。
 有个 pt-online-schema-change 的工具（基于 facebook 的在线 schema 变更技术），可以比较简单、方便地执行上述过程，避免手工操作可能导致的失误和繁琐。

7 其他

 还可以关注下 MySQL 的时间线、各个版本的性能测试、MySQL 的开发模式等。

• 低并发场景下 MySQL 性能测试open in new window

参考文献

• [高性能 MySQL]