MySQL 架构与历史



前言：本篇是村民新坑的开始，村民最近在看《 高性能 MySQL 》这本书，村民在看的是第三版，仅涵盖 MySQL 5.5，虽然最新的 MySQL 已经是 8.0 版本，但后者肯定是在前者的基础上，因此学习价值还是很大的。这系列村民会基本以一章节一篇的形式记录村民对书中内容的摘抄整理及笔记，没什么新意，仅仅算是一种自娱自乐的分享，对这本书感兴趣的同学当然也可以买来看看。



本章概要地描述了 MySQL 的服务器架构、各种存储引擎之间的主要区别，以及这些区别的重要性。另外也会回顾一下 MySQL 的历史背景和基准测试，并试图通过简化细节和演示案例来讨论 MySQL 的原理。



1.1 MySQL 逻辑架构



MySQL 逻辑架构图能帮助我们清晰 MySQL 各组件之间如何协同工作，也会有助于我们深入理解 MySQL 服务器。





最上层的服务并不是 MySQL 所独有的，大多数基于网络的客户端 / 服务端的工具或者服务都有类似的架构。比如连接处理、授权认证、安全等等。



第二层架构涵盖了大多数 MySQL 的核心服务功能，包括查询解析、分析、优化、缓存以及所有的内置函数（日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。



第三层包含了存储引擎。存储引擎负责 MySQL 中数据的存储和提取。和 GNU/Linux 下的各种文件系统一样，每个存储引擎都有它的优势和劣势。服务器通过 API 与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。存储引擎 API 包含几十个底层函数，用于执行诸如 “开始一个事务” 或者 “根据主键提取一条记录” 等操作，但存储引擎不会去解析 SQL注1，不同存储引擎之间也不会进行通信，而只是简单地响应上层服务器的请求。



注1：InnoDB 存储引擎是一个例外，它会解析外键定义，因为 MySQL 服务器本身没有实现该功能。



1.1.1 连接管理与安全性



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



当客户端连接到 MySQL 服务器时，服务器需要对其进行认证。认证基于用户名、原始主机信息和密码。一旦客户端连接成功，服务器会继续验证该客户端是否具有执行某个特定查询的权限。



1.1.2 优化与执行



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





当我们使用 Navicat 工具时，在查询功能下可以点击 “ 解释 ” 按钮或者在 SQL 语句前加上 EXPLAIN 关键字请求优化器解释优化过程。



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



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



1.2 并发控制



无论何时，只要有多个进程需要在同一时刻修改同一份数据，都会产生并发控制的问题。本节的目的是讨论 MySQL 在两个层面的并发控制：服务器层与存储引擎层。



1.2.1 读写锁



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



锁的概念：读锁是共享的，或者说是相互不阻塞的。多个客户在同一时刻可以同时读取同一个资源，而互不干扰。写锁是排他的，也就是说一个写锁会阻塞其他的写锁和读锁，这样才能确保在给定的时间里只有一个用户能执行写入，并防止其他用户读取正在写入的同一资源。



1.2.2 锁粒度



一种提高共享资源并发性的方式就是让锁定对象更有选择性。在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要相互之间不发生冲突即可。随之而来的问题是加锁也需要消耗资源，因此需要通过锁策略在锁的开销和数据的安全性之间寻求平衡。最重要的两种锁策略就是表锁和行级锁。



表锁 ( table lock ) 是 MySQL 中最基本的锁策略，并且是开销最小的策略。表锁会锁定整张表，当用户对表进行写操作前，需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他读取的用户才能获得读锁，读锁之间是不相互阻塞的。



行级锁 ( row lock ) 可以最大程度地支持并发处理，同时也带来了最大的锁开销。行级锁只在存储引擎层实现，而 MySQL 服务器层没有实现。



1.3 事务



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



要了解事务，首先要知道事务的 ACID 概念。ACID 表示原子性 ( atomicity )、一致性 ( consistency )、隔离性 ( isolation ) 和持久性 (durability )。



• 原子性：一个事务必须被视为一个不可分割的最小工作单位。

• 一致性：数据库总是从一个一致性的状态转换到另一个一致性的状态。如果事务最终没有提交，那么事务中所做的修改也不会保存到数据库中。

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

• 持久性：一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。



1.3.1 隔离级别



每种存储引擎实现的隔离级别不尽相同。在 SQL 标准中定义了四种隔离级别：



• READ UNCOMMITTED（未提交读）：在 READ UNCOMMITTED 级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读 ( Dirty Read )。这个级别在实际应用中一般很少使用。

• READ COMMITTED（提交读）：在 READ COMMITTED 级别中，一个事务从开始直到提交之前，所做的任何修改都对其他事务不可见的。这个级别有时候也叫做不可重复读 ( nonrepeatable read )，因为两次执行同样的查询，可能会得到不一样的结果。大多数数据库系统的默认隔离级别都是 READ COMMITTED，但 MySQL 不是。

• REAPEATABLE READ（可重复读）：REAPEATABLE READ 解决了脏读的问题，该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上，可重复读级别还是无法解决另一个问题——幻读 ( Phantom Read )。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行 ( Phantom Row )。可重复读是 MySQL 的默认事务隔离级别。

• SERIALIZABLE（可串行化）：SERIALIZABLE 是最高的隔离级别。它通过强制事务串行执行，避免了幻读的问题。简单来说，SERIALIZABLE 会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。实际应用中也很少用到这个隔离级别。





1.3.2 死锁



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



为了解决这种问题，数据库系统实现了各种死锁检测和死锁超时机制。InnoDB 目前处理死锁的方法是将持有最少行级排它锁的事务进行回滚。



锁的行为和顺序是和存储引擎相关的。以同样的顺序执行语句时，有些存储引擎会产生死锁，有些则不会。死锁的产生有双重原因：有些是因为真正的数据冲突，这种情况通常很难避免，但有些则完全是由于存储引擎的实现方式导致的。



1.3.3 事务日志



事务日志可以帮助提高事务的效率。事务日志采用的是追加的方式。



1.3.4 MySQL 中的事务



自动提交 ( AUTOCOMMIT )



MySQL 默认采用自动提交 ( AUTOCOMMIT ) 模式。也就是说，如果不是显式地开始一个事务，则每个查询都被当做一个事务执行提交操作。可以通过下述命令设置 AUTOCOMMIT 变量来启用或者禁用自动提交模式：



mysql> SET AUTOCOMMIT = 1;
mysql> SET AUTOCOMMIT = 0;



1 或者 ON 表示启用，0 或者 OFF 表示禁用。MySQL 可以通过执行 SET TRANSACTION ISOLATION LEVEL 命令来设置隔离级别。新的隔离级别会在下一个事务开始的时候生效。



mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

MySQL 能够识别所有的 4 个 ANSI 隔离级别，InnoDB 引擎也支持所有的隔离级别。



在事务中混合使用存储引擎



MySQL 服务器层不管理事务，事务是由下层的存储引擎实现的。所以在同一事务中，使用多种存储引擎是不可靠的。



隐式和显式锁定



InnoDB 采用的是两阶段锁定协议 ( two-phase locking protocol ) 。在事务执行过程中，随时都可以执行锁定，锁只有在执行 COMMIT 或者 ROLLBACK 的时候才会释放，并且所有的锁都在同一时刻被释放。前面描述的锁定都是隐式锁定，InnoDB 会根据隔离级别在需要的时候自动加锁。另外，InnoDB 也支持通过特定的语句进行显示锁定。



1.4 多版本并发控制



MySQL 的大多数事务型存储引擎实现的都不是简单的行级锁，基于提升并发性能的考虑，它们一般都实现了多版本并发控制 ( MVCC )。可以认为 MVCC 是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。MVCC 是通过保存数据在某个时间点的快照来实现的，也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。



InnoDB 的 MVCC 是通过在每行记录后面保存两个隐藏的列来实现的，一个列保存了行的创建时间，另一个列保存行的过期时间或删除时间。当然存储的并不是实际的时间值，而是系统版本号 ( system version number )。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。保存这两个额外系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。



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



1.5 MySQL 的存储引擎



1.5.1 InnoDB 存储引擎



InnoDB 是 MySQL 的默认事务型引擎，也是最重要、使用最广泛的存储引擎。它被设计用来处理大量的短期 ( short-lived ) 事务，短期事务大部分情况是正常提交的，很少会被回滚。InnoDB 的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中也很流行。



InnoDB 概览



InnoDB 采用 MVCC 来支持高并发，并且实现了四个标准的隔离级别。其默认隔离级别是 REPEATABLE READ（可重复读），并且通过间隙锁 ( next-key locking ) 策略防止幻读的出现。间隙锁使得 InnoDB 不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。



InnoDB 的表是基于聚簇索引建立的。聚簇索引对主键查询有很高的性能，不过它的二级索引 ( secondary index，非主键索引 ) 中必须包含主键列，所以如果主键列很大的话，其他的索引都会很大。因此，若表上的索引较多的话，主键应当尽可能的小。InnoDB 的存储格式是平台独立的，也就是说可以将数据和索引文件复制出来转移到另一个平台。



1.5.2 MyISAM 存储引擎



MyISAM 提供了大量的特性，包括全文索引、压缩、空间函数 ( GIS ) 等，但 MyISAM 不支持事务和行级锁，而且有一个毫无疑问的缺陷就是崩溃后无法安全恢复。



存储



MyISAM 会将表存储在两个文件中：数据文件和索引文件，分别以 .MYD 和 .MYI 为扩展名。MyISAM 表可以包含动态或者静态（长度固定）行。MySQL 会根据表的定义来决定采用何种格式。MyISAM 表可以存储的行记录数，一般受限于可用的磁盘空间，或者操作系统中单个文件的最大尺寸。



MyISAM 特性



• 加锁与并发：MyISAM 对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁，写入时则对表加排他锁，但是在表有读取查询的同时，也可以往表中插入新的记录（这也被称为并发插入，CONCURRENT INSERT ）。

• 修复：对于MyISAM 表，MySQL 可以手工或者自动执行检查和修复操作，但这里说的修复和事务恢复以及崩溃恢复时不同的概念。执行表的修复可能导致一些数据丢失，而且修复操作是非常慢的。可以通过 CHECK TABLE mytable 检查表的错误，如果有错误可以通过执行 REPAIR TABLE mytable 进行修复。另外，如果 MySQL 服务器已经关闭，也可以通过 myisamchk 命令行工具进行检查和修复操作。

• 索引特性：对于 MyISAM 表，即使是 BLOB 和 TEXT 等长字段，也可以基于其前 500 个字符创建索引。MyISAM 也支持全文索引，这是一种基于分词创建的索引，可以支持复杂的查询。

• 延迟更新索引键 ( Delayed Key Write )：创建 MyISAM 表的时候，如果指定了 DELAY_kEY_wRITE 选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区 ( in-memory key buffer )，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入到磁盘。这种方式可以极大地提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。延迟更新索引键的特性，可以在全局设置，也可以为单个表设置。



MyISAM 压缩表



如果表在创建并导入数据以后，不会在进行修改操作，那么这样的表或许适合采用 MyISAM 压缩表。可以使用 myisampack 对 MyISAM 表进行压缩（也叫打包 pack ）。压缩表是不能修改的（除非先将表解除压缩，修改数据，然后再次压缩）。压缩表可以极大地减少磁盘空间占用，因此也可以减少磁盘 I/O，从而提升查询性能。压缩表也支持索引，但索引也是只读的。



1.5.5 选择合适的引擎



大部分情况下，InnoDB 都是正确的选择，这也是 InnoDB 被选择作为 MySQL 默认存储引擎的原因。简单来说，除非需要用到某些 InnoDB 不具备的特性，并且没有其他办法可以替代，否则都应该优先选择 InnoDB 引擎。当然，如果不需要用到 InnoDB 的特性，同时其他引擎的特性能够更好地满足需求，也可以考虑一下其他存储引擎。



除非万不得已，否则不建议混合使用多种存储引擎，否则可能会带来一系列复杂的问题，以及一些潜在的 bug 和边界问题。混合存储对一致性备份和服务器参数配置都带来了一些困难。



如果应用需要不同的存储引擎，请先考虑一下几个因素：



• 事务：如果应用需要事务支持，那么 InnoDB 是目前最稳定并且经过验证的选择。如果不需要事务，并且主要是 SELECT 和 INSERT 操作，那么 MyISAM 是不错的选择，一般日志型的应用比较符合这一特性。

• 备份：备份的需求也会影响存储引擎的选择。如果可以定期地关闭服务器来执行备份的，那么备份的因素可以忽略。反之，如果需要在线热备份，那么选择 InnoDB 就是基本的要求。

• 崩溃恢复：数据量比较大的时候，系统崩溃后如何快速地恢复是一个需要考虑的问题。相对而言，MyISAM 崩溃后发生损坏的概率比 InnoDB 要高很多，而且恢复速度也要慢。因此，即使不需要事务支持，很多人也选择 InnoDB 引擎，这是一个非常重要的因素。

• 特有的特性：有些应用可能依赖一些存储引擎所独有的特性或者优化。比如很多应用依赖聚簇索引的优化，另外，MySQL 中也只有 MyISAM 支持地理空间搜索。如果一个存储引擎拥有一些关键的特性，同时又缺乏一些必要的特性，那么有时候不得不做折中的考虑，或者在架构设计上做一些取舍。某些存储引擎无法直接支持的特性，有时候通过变通也可以满足需求。



1.5.6 转换表的引擎



如果转换表的存储引擎，将会失去和原引擎相关的所有特性。有很多种方法可以将表的存储引擎转换成另外一种引擎。每种方法都有其优点和缺点，这里介绍三种方法。



ALTER TABLE



将表从一个引擎修改为另一个引擎最简单的方法是使用 ALTER TABLE 语句。下面的语句将 mytable 的引擎修改为 InnoDB：



mysql> ALTER TABLE mytable ENGINE = InnoDB;

上述语法可以适用任何存储引擎，但存在需要执行很长时间的问题。MySQL 会按行将数据从原表复制到一张新的表中，在复制期间可能会消耗系统所有的 I/O 能力，同事原表上会加上读锁。



导出与导入



为了更好地控制转换的过程，可以使用 mysqldump 工具将数据导出到文件，然后修改文件中 CREATE TABLE 语句的存储引擎选项，注意同时修改表名，因为同一数据库中不能存在相同的表名，即使它们使用的是不同的存储引擎。同时要注意 mysqldump 默认会自动在 CREAT TABLE 语句前加上 DROP TABLE 语句，不注意这一点可能会导致数据丢失。



创建与查询



第三种转换的技术综合了第一种方法的高效和第二种方法的安全，不需要导出整个表的数据，而是先创建一个新的存储引擎的表，然后利用 INSERT ··· SELECT 语法来导数据：



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

数据量不大的话，这样做工作得很好。如果数据量很大，则可以考虑做分批处理，针对每一段数据执行事务提交操作，以避免大事务产生过多的 undo。假设有主键字段 id，重复运行一下语句（最小值 x 和最大值 y 进行相应的替换）将数据导入到新表：



mysql> START TRANSACTION;
mysql> INSERT INTO innodb_tabel SELECT * FROM myisam_table WHERE id BETWEEN x AND y;
mysql> COMMIT;



这样操作完成以后，新表是原表的一个全量复制，原表还在，如果需要可以删除原表。如果有必要，可以在执行的过程中对原表加锁，以确保新表和原表的数据一致。