MySQL架构 | Eucaly

2020-01-24 17 min read

MySQL为什么灵活？灵活性体现在哪？

可以通过配置使它在不同的硬件上都运行得很好，也可以支持多种不同的数据类型。但是最重要、最与众不同的特性是它的存储引擎架构，这种架构的设计将查询处理及其他系统任务和数据的存储/提取相分离。

这种处理和存储分离的设计可以在使用时根据性能、特性，以及其他需求来选择数据存储的方式。

学习目标：

掌握MySQL服务器架构
掌握各种存储引擎之间的主要区别，以及这些区别的重要性
了解MySQL的历史背景和基准测试
初步掌握MySQL的原理

1.1 MySQL逻辑架构

头脑中构建出一幅MySQL各组件之间如何协同工作的架构图，有助于深入理解MySQL服务器。

第一层：连接处理、授权认证、安全

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

第三层：存储引擎。存储引擎负责MySQL中数据的存取和提取。服务器通过API与存储引擎进行通信。存储引擎API包含几十个底层函数，用于执行诸如“开始一个事务”或者“根据主键提取一行记录”等操作。但存储引擎不回去解析SQL(InnoDB例外，会解析外健定义，因为MySQL服务器本身没有实现该功能)，不同存储引擎之间也不会相互通信，而只是简单地响应上层服务器等请求。

1.1.1 连接管理与安全性

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

线程池是一个更优秀的资源管理策略

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

1.1.2 优化与执行

MySQL会解析查询，并创建内部数据结构(解析树)，然后对其进行各种优化，包括重写查询、决定表的读取顺序，以及选择合适的索引等。

用户可以通过特殊的关键字提示(hint)优化器，影响它的决策过程。

也可以请求优化器解释(explain)优化过程的各个因素，使用户可以知道服务器是如何进行优化决策的，并提供一个参考基准，便于用户重构查询和schema、修改相关配置，使应用尽可能高效运行。

第6章将讨论更多优化器细节。

优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息等。例如，某些存储引擎的某种索引，可能对一些特定的查询有优化。

关于索引与schema的优化，参见第4章和第5章。

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

第7章详细讨论了相关内容。

1.2 并发控制

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

本章的目的是讨论MySQL在两个层面的并发控制：服务器层与存储引擎层。

本章只简要地讨论MySQL如何控制并发读写。

以Unix系统的email box为例，典型的mbox文件格式是非常简单的。一个mbox邮箱中的所有邮件都串行在一起，彼此首尾相连。

这种格式对于读取和分析邮件信息非常友好，同时投递邮件也很容易，只要在文件末尾附加新的邮件内容即可。

但如果两个进程在同一时刻对同一个邮箱投递邮件，邮箱的数据会被破坏，两封邮件的内容会交叉地附加在邮箱文件的末尾。

设计良好的邮箱投递系统会通过锁(lock)来防止数据损坏。如果客户试图投递邮件，而邮箱已经被其他客户锁住，那就必须等待，直到锁释放才能进行投递。

这种锁的方案在实际应用环境中虽然工作良好，但并不支持并发处理。因为任意一个时刻，只有一个进程可以修改邮箱的数据，这在大容量的邮箱系统中是个问题。

1.2.1 读写锁

读读不会出现问题，读写、写读、写写都会有问题。需要做同步处理。

一个经典的方法是，通过实现一个由两种类型的锁组成的锁系统来解决问题。

这两种类型的锁通常被称为共享锁和排他锁，也叫读锁和写锁。

读锁是共享的，或者说是相互不阻塞的。多个客户在同一时刻可以同时读取同一个资源，而互不干扰。

写锁则是排他的，也就是说一个写锁会阻塞其他的写锁和读锁。

在实际的数据库系统中，每时每刻都在发生锁定，当某个用户修改某一部分数据时，MySQL会通过锁定防止其他用户读取同一数据。大多数时候，MySQL锁的内部管理都是透明的。

1.2.2 锁粒度

一种提高共享资源并发性的方式就是让锁定对象更有选择性。尽量只锁定需要修改的部分数据，而不是所有的资源。

更理想的方式是，只对会修改的数据片进行精确的锁定。任何时候，在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要相互之间不发生冲突即可。

问题是加锁也需要消耗资源。锁的各种操作，包括获得锁、检查锁是否已经解除、释放锁等，都会增加系统等开销。如果系统花费大量的时间来管理锁，而不是存取数据，那么系统的性能可能会因此受到影响。

所谓的锁策略，是在锁的开销和数据的安全性之间寻求平衡，这种平衡当然也会影响到性能。

大多数商业数据库系统没有提供更多的选择，一般都是在表上施加行级锁，并以各种复杂的方式来实现，以便在锁比较多的情况下尽可能地提供更好的性能。

而MySQL则提供了多种选择。每种MySQL存储引擎都可以实现自己的锁策略和锁粒度。

MySQL中，对锁的管理灵活，锁粒度可以修改，多存储引擎架构提供对多场景支持。

两种重要的锁策略

表锁

开销最小

在特定的场景中，表锁也可能有良好的性能。例如，READ_LOCAL表锁支持某些类型的并发写操作。

另外，写锁也比读锁有更高的优先级，因此一个写锁请求可能会被插入到读锁队列的前面。

尽管存储引擎可以管理自己的锁，MySQL本身还是会使用各种有效的表锁来实现不同的目的。例如，服务器会为诸如ALTER TABLE之类的语句使用表锁，而忽略存储引擎的锁机制。

行级锁

行级锁可以最大程度地支持并发处理（同时也带来了最大的锁开销）。在InnoDB和XtraDB，以及一些存储引擎中实现了行级锁。

行级锁只在存储引擎层实现，而MySQL服务器层（如有必要，请回顾前问的逻辑架构图）没有实现。服务器层完全不了解存储引擎中的锁实现。在本章的后续内容以及全书中，所有的存储引擎都以自己的方式显现了锁机制。

1.3 事务

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

事务内的语句，要么全部执行成功，要么全部执行失败。

事务的ACID

一个运行良好的事务处理系统，必须具备这些标准特征：

原子性（atomicity）

一致性（consistency）

隔离性（isolation）

持久性（durability）

实现ACID需要考虑性能、安全等很多复杂问题。

隔离级别

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

READ UNCOMMITTED(读未提交)

READ COMMITTED(读已提交)

REPEATABLE READ(可重复度)

SERIALIZABLE(可串行化)

ANSI SQL隔离级别

隔离级别	脏读可能性	不可重复读可能性	幻读可能性	加锁读
READ UNCOMMITTED	Yes	Yes	Yes	No
READ COMMITTED	No	Yes	Yes	No
REPEATABLE READ	No	No	Yes	No
SERIALIZABLE	No	No	No	Yes

1.3.2 死锁

什么是死锁？

怎么产生死锁？

死锁检测和超时机制

1.3.3 事务日志

采用追加的方式，速度快，顺序IO，不用移动磁头。

预写式日志（Write-Ahead Logging），修改数据需要写两次磁盘，实时修改内存拷贝，持久化事务日志，事务日志持久化后，内存中被修改的数据在后台可以慢慢地刷回到磁盘。

1.3.4 MySQL中的事务

事务支持：InnoDB

自动提交

默认自动提交

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

隐式和显式锁定

1.4 多版本并发控制

可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但都实现了非阻塞的读操作，写操作也只锁定必要的行。

MVCC的实现，是通过保存数据在某个时间点的快照来实现的。

MVCC的实现，典型的有乐观并发控制和悲观并发控制。下面我们通过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两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容，因为READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁。

1.5 MySQL的存储引擎

数据库，或称schema，对应文件系统中数据目录下的一个子目录。

创建表时，MySQL会在数据库子目录下创建一个和表同名的.frm文件保存表的定义。

大小写敏感和平台相关。

可以使用show table status命令（在5.0以后的版本中，也可以查询information_schema中对应的表）显示表的相关信息。

1.5.1 InnoDB存储引擎

MySQL默认的事务引擎。被设计用来处理大量的短期事务，短期事务大部分情况时正常提交的，很少会被回滚。

InnoDB的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中也很流行。

InnoDB的历史

InnoDB概览

InnoDB的数据存储在表空间中。

InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离级别。

默认级别是REPEATABLE READ（可重复读），并且通过间隙锁（next-key locking）策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。

InnoDB表是基于聚簇索引建立的。

聚簇索引对主键查询有很高的性能。不过它的二级索引（secondary index，非主键索引）中必须包含主键列，所以如果主键列很大的话，其他的索引都会很大。

因此，若表上的索引较多的话，主键应当尽可能的小。

InnoDB的存储格式是平台独立的。数据和索引文件可以在各个平台间复制。

InnoDB内部做了很多优化，包括从磁盘读取数据时采用的可预测性预读，能够自动在内存中创建hash索引以加速读操作的自适应哈希索引（adaptive hash index），以及能够加速插入操作的插入缓冲区（insert buffer）等。后面将详细地讨论这些内容。

如果应用程序基于InnoDB引起，则应该了解一下InnoDB的MVCC架构带来的一些微妙和细节之处时非常有必要的。

存储引擎要为所有用户甚至包括修改数据的用户维持一致性的视图，是非常复杂的工作。要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入也意味着停止读取。

InnoDB支持热备份。

1.5.2 MyISAM存储引擎

特性：

支持全文索引、压缩、空间函数（GIS）等

缺点：

不支持事务和行级锁

崩溃后无法完全恢复

存储

特性

压缩表

性能

1.5.3 MySQL内建等其他存储引擎

Memory引擎

Merge引擎

1.5.4 第三方存储引擎

OLTP类引擎

TokuDB引擎，分形树（Fractal Trees）索引数据结构。缓存无关，是一种大数据存储引擎，因为其拥有很高的压缩比，可以在大的数据量上创建大量索引。

面向列的存储引擎

社区存储引擎

1.5.5 选择合适的引擎

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

事务

备份

崩溃恢复

特有的特性

常见的应用场景。

日志型应用

只读或者大部分情况下只读的表

订单处理

电子公告牌和主题讨论论坛

CD-ROM应用

大数据量

1.5.6 转换表的引擎

ALTER TABLE

导出与导入

创建于查询

先创建一个新的存储引擎的表，然后利用insert... select语法来导数据。

create table innodb_table like muisam_table;

alter table innodb_table engine=Innodb;

insert into innodb_table select * from myisam_table;

数据量大的情况，可以考虑做分批处理，针对每一段数据执行事务提交操作，避免大事务产生过多的undo。

start transaction;
insert into innodb_table select * from myisam_table where id between x and y;
commit;

1.6 MySQL时间线（Timeline）

1.7 MySQL的开发模式

新里程碑开发版

GA（Generally Available）版，通常可用版

实验室预览版，发布一些需要评估的特性

插件付费，插件开发模式

1.8 总结

MySQL拥有分层的架构。

上层是服务器层的服务和查询执行引擎，下层则是存储引擎。

如果能理解MySQL在存储引擎和服务层之间处理查询时如何通过API来回交互，就能抓住MySQL的核心基础架构的精髓。

数据字典保存在.frm文件中，对于InnoDB来说，所有的操作都是事务。

选择多并非好事。InnoDB对于95%以上的用户来说都是最佳选择。

mysql

Kim

Time waits for no one.