原文链接：https://www.flamingspork.com/blog/2014/09/19/mysql-architecture/
有好一阵子，我一直在四处寻找（已经尝试过很多次）面向技术精通但对MySQL钻研不太深的读者的关于MySQL体系结构和技术细节的优质资源，但一无所获。我的意思是，确实有MySQL手册（篇幅庞大且描述详尽），也确实有MySQL内部手册（已经大约过时10年了），以及各种各样的博客日志。所以我想自己应该写点东西来粗略地解释下这一切是怎样组合在一起的，以及它对你的系统做了些什么（进程、线程、IO，等等）（基本上，我在过去的几年里已经对此解释过很多次了，我的确应该把这些东西用博客写出来）。我在相关阅读材料处做了链接，有空的时候可以看看。
几年以前，有许多关于MySQL体系结构的演示。我试图在YouTube上找一些看看但以失败告终。我们可能不会静下心来在YouTube上观看，并且大部分会议都没有被记录下来。因此我只贴一条链接 Brian on NoSQL —— 因为了解下NoSQL也很重要。
下面是一个关于在MySQL服务器内部执行查询及其影响因素的简要概述。这不是一个完整的描述...只是一个（几千个字）“简要的”概述。
MySQL是一款开源的关系型数据库，其起源可追溯到1979年，MySQL 1.0诞生于1995年。It's code that has some history and sometimes this really does show. For a more complete history, see my talk from linux.conf.au 2014: Past, Present and Future of MySQL (YouTube, Download).
至少在写下这段文字时，这些对于MariaDB和Percona Server也同样适用（At least of writing, everything here applies to MariaDB and Percona Server too）。
MySQL服务器以守护进程（mysqld）的形式运行。用户通常使用TCP或者UNIX domain socket，通过MySQL网络协议（有各种协议的不同实现版本）与MySQL服务器进行交互。每一个连接都会使MySQL服务器（mysqld）产生一个新线程来处理客户端连接。
现在有几种不同的线程池插件，它们不再为每一个连接使用一个线程，而是在一组线程间进行复用。然而，这些插件并没有普遍部署，我们基本可以忽略它们。对于各种意图和目的，MySQL服务器对每一个连接产生一个线程，而这个线程独自为该连接服务。因此，MySQL服务器中的并行是指并行执行许多查询而非一次查询内的并行。
MySQL会对线程进行缓存（数目可配置），因此它不必在每一次创建新连接时都调用pthread_create()。这是由 thread_cache_size configuration option控制的。事实证明，尽管创建线程是一个相对廉价的操作，实际上在许多典型的MySQL服务器连接的规模下，它还是相当耗费时间的。
由于MySQL服务器是一组线程的集合，因此会存在线程本地数据（比如说：特定连接）和线程共享数据（比如说：磁盘数据缓存）。这意味着互斥锁和原子变量。大部分更加高级的处理并发的方法还没有引入MySQL（例如 RCU[Read-Copy-Update]还没有得到应用，而为了处理百万级的TPS[Transaction-Per-Second]是十分必要的）。因此你非常可能看到互斥锁的争夺和原子操作以及共享数据结构的超高速缓冲存储器线的争夺。
MySQL服务器内部也存在各种执行不同功能的工作线程（比如：复制）
一直到21世纪初的某个时候之前，多核心CPU还是十分罕见的，因此MySQL中的很多全局互斥锁并非一个真正的问题。这些日子里，由于我们已经拥有了更加可靠的异步网络和磁盘IO系统调用，但是MySQL拥有一段很长的历史，仍然还有全局互斥锁（ there’s global mutexes still ），并且没有硬性规定它如何操作IO。
在过去10年的MySQL发展历程中，移除全局互斥锁及其控制的数据结构以试图增加单个mysqld可以实际使用的CPU核心数已经成为一场战争。好消息是你的手机中的最大CPU核心数不再受到限制。
比如，你有一个MySQL客户端（比如，mysql client或者别的客户端）连接到了MySQL服务器。现在，你想要做一个查询。然后你执行了，比如“SELECT 1”。这条查询被发送到服务器，在服务器端它被解析，优化，执行，然后结果被返回到客户端。
现在，你期望整个过程可以无比的整洁和模块化，就像在大学里用几个接收干净的输入，产生干净的输出的黑盒子教你的那样，这些都是独立的数据结构。至少在MySQL的情形中，事实并非如此。十几年来，有许多可爱的包含干净盒子的体系结构图——代码和这个完全不同。但这只可能会在你钻研代码的时候给你带来一些困扰。（Now, you’d expect this whole process to be incredibly clean and modular, like you were taught things happened back in university with several black boxes that take clean input and produce clean output that’s all independent data structures. At least in the case of MySQL, this isn’t really the case. For over a decade there’s been lovely architecture diagrams with clean boxes – the code is not like this at all. But this probably only worries you once you’re delving into the source.）
解析器是标准的yacc(Yet Another Compiler Compiler，是一个经典的生成语法分析器的工具)——近几年来有许多替换该解析器的尝试，但都没有成功——所以我们还在用这个yaac版本（butchered one）。由于增加了诸如SQL2003存储过程等内容，MySQL 5.0的尺寸增长了很多。大家普遍认为这个版本非常臃肿，对于大部分大规模Web浏览所需的查询，4.1及更早的版本表现更好（ it is of common opinion that it’s rather bloated and was better in 4.1 and before for the majority of queries that large scale web peeps execute.）。
还有一个东西叫做查询缓存（Query Cache）——由单个全局互斥锁保护。它从2001年开始成为基线。它是对SQL语句及其返回客户端查询结果的简单哈希（It is a simple hash of the SQL statement coming over the wire to the exact result to send(2) over the socket back to a client. ）在一个单CPU系统中，如果一次又一次的重复执行同样的SQL语句而不修改传入数据时，这是一个非常好的性能优化。如果用于生产环境的安装，你就得三思而行了（think about where you went wrong in your life）。在现代系统中，启用查询缓存可能会使服务器性能下降一个数量级。单一全局锁实在是一个糟糕的主意。查询优化应该被干掉（killed with fire），至少被禁用掉。
正常情况下，SQL的执行包括解析、优化、执行、结果这一套完整的流程，但是MySQL也支持预处理语句（prepared statements）。预处理语句就像这样“Hi，服务器，请为这条语句的执行做准备，并将X，Y和Z值留空”，接下来“现在令X=foo，Y=bar，Z=42执行查询”。你可以用不同的值执行很多次查询。由于前文提到的分隔不良（not-quite-well-separated）的解析、优化、执行步骤，MySQL中的预处理语句没有别的关系型数据库那么好。在使用新参数查询时，基本上需要重新执行一次解析。More on prepared statements (from 2006) here. 除非在一次连接中对同样的查询执行很多次，否则与往返时延相比，服务器端的预定义语句得不偿失。
MySQL服务端预定义语句绝对是世界上最坏的东西。它将服务器端的内存分配转移至客户端进行。这是一种十分脑残的行为并且可以成为禁用预定义语句的理由。实际上，与其寄希望于让每一个程序员记住关闭他们的预定义查询，还不如让每一个MySQL客户端库使用假的预定义查询。（In fact, just about every MySQL client library for every programming language ever actually fakes prepared statements in the client rather than trust every $language programmer to remember to close their prepared statements. ）打开很多个MySQL服务器的客户端连接，创建许多个预定义查询，坐等OOM Killer帮你完成DoS攻击。
现在我们已经连上了服务器，解析了查询（或者完成了一个预定义查询），我们接下来进入优化器。优化器观察一个描述查询的数据结构并计算出如何执行它。记住：SQL是声明式，而非过程式的。优化器将访问变量表和索引统计信息，然后得出执行计划。它也许不是最佳的执行计划，但是在可接受的时间范围内最优的。你可以通过在SELECT语句之前添加EXPLAIN来看到这个执行计划。
MySQL优化器并不是SQL优化器的全部和结束（远远不止于此）。许多MySQL性能问题归咎于一些复杂的SQL在优化器里处理得不好，有各种各样的技巧来处理这些缺陷。如果要举出MySQL优化器做的比较好的一个地方，那就是它对于简单查询可以做出快速而优秀的决策。这就是为什么MySQL如此流行的原因——简单查询执行迅速。
为了获取表和索引统计信息，优化器不得不询问存储引擎。在MySQL中，实际的表存储（以及表里的数据航）大部分是从上层抽象出来的。与OS内核中的VFS层（VFS的作用就是采用标准的Unix系统调用读写位于不同物理介质上的不同文件系统）很相像，存在（对于某些定义）一个API将服务器的剩余部分与特定的存储想抽离。这个API不是很干净并且对于每一条规则存在一百万零一个冲突和异常（ there are a million and one layering violations and exceptions to every rule）。对不起，不是我的错。
表定义保存在磁盘的FRM文件里，完全由MySQL管理（而非存储引擎）并且为了你好，你不会想查看实际的文件格式的。表定义也会被MySQL缓存保存时必须打开并解析文件（Table definitions are also cached by MySQL to save having to open and parse a file.）
起初就有MyISAM存储引擎（好吧，在其之前还有ISAM，但是现在已经无关紧要了）。MyISAM是非事务性的但是相对更快，尤其对于以读取为主的工作负载来说。它只允许存在一个写者，但是可以支持许多个并发的读者。MyISAM仍然存在并且为许多系统表所用。现在的缺省存储引擎叫做InnoDB。它与时髦的词汇例如ACID和MVCC（Multi-Version Concurrency Control）相关。几乎所有的生产环境都会使用InnoDB。MyISAM事实上正在过时。
InnoDB起初是一个独立的项目并且（在某种程度上）还在被独立维护。不过它已经不再独立于MySQL服务器之外构建了。它也存在自己的可扩展性问题。最近的一次胜利是kernel_mutex的拆分，这个互斥锁保护了过多的InnoDB内部状态并且当NRCPUs > 4时可能成为瓶颈。
那么，回到查询执行。当优化器计算出如何执行查询时，MySQL就会开始执行它。这可能包括对某些数据库表的访问。这些可能是InnoDB的表。所以，MySQL（服务器端）会打开这些表，查询MySQL服务器表定义缓存然后创建一个MySQL服务端表共享对象，这个对象由此表的打开表实例所共享（shared amongst the open table instances for that table）。See here for scalability hints on these (from 2009). 打开表对象也会被缓存——table_open_cache。在MySQL 5.6中，存在table_open_cache_instances，它们将table_open_cache互斥锁拆分为table_open_cache_instances互斥锁来帮助减少多核心机器（>8或者>16核心，由负载而定）的锁争用。
表被打开之后，有许多种访问方法可以采用。全表扫描是最坏的一种方式（从头开始检查每一行）。还有一种方式是索引扫描（通常从索引的开始进行扫描）和键查找。如果你的查询涉及多个表，服务器（而非存储引擎）必须做一个连接。典型地，在MySQL中，这是一种嵌套循环连接。在理想世界里，对MySQL服务器做性能分析时很容易发现，但实际上，所有的东西都有滑稽的名字，比如rnd_next。（this would all be really easy to spot when profiling the MySQL server, but in reality, everything has funny names like rnd_next.）
顺便说一句，查询执行过程中的内存分配很可能是MEM_ROOT的一部分——本质上是一个池分配器，很可能是对某些古老的linux/Solaris的某些古老的libc做的优化，它们碰巧还可以正常工作。有一些关于（某些）MEM_ROOT的奇怪到的服务器配置没有指明（get exactly no airtime）它们的涵义以及改变它们能够做什么。
InnoDB拥有其自己的数据字典（独立于FRM文件），它们也可以被限制在现在的MySQL中（当你拥有成千上万个表的时候这是很重要的）——独立于MySQL服务器表定义和表定义缓存。
但不管怎样，你都拥有若干个关于表的共享数据结构以及每个打开的表的数据结构。如果真正从表中读写数据，就一定要对磁盘进行读写。
InnoDB表可以保存在一个巨大的表空间里，也可以每个表保存一个文件。（尽管现在这是可配置的），InnoDB数据库的页大小为16Kb。数据库页缓存在InnoDB缓存池中，并且buffer-pool-size通常大约应该占系统内存的80%。InnoDB使用一个（可配置的）方法来刷新。典型地，它们都是O_DIRECT（可配置），这就是为什么“只使用XFS”是IO优化的第一步 the per inode mutex in ext3/ext4 just doesn’t make IO scale.
InnoDB的IO操作一部分在执行查询的线程中完成，一部分在使用本地linux异步IO的帮助线程中完成（再次，这是可配置的）。运气好的话，你需要访问的所有数据都保存在InnoDB缓冲池中——在那里缓存了数据库页。innodb_buffer_pool_instances配置项把缓冲池切分为几个实例来帮助减少对InnoDB缓冲池互斥锁的抢夺。
所有的InnoDB表都有一个聚集索引。这种索引的顺序与行的物理顺序一致。如果你的InnoDB表中有一个INT PRIMARY KEY字段，那么主键值为1的行与主键值为2的行在物理上相邻（以此类推）。由于InnoDB页面分配的复杂性，使用主键顺序进行表扫描时可能还是需要磁盘寻道。
InnoDB的每一页都有一个校验和。有一个原始算法，后面在某些分支中出现了一些“快速”算法，现在我们统一使用crc32，主要是因为Intel的CPU指令集可以执行的很快。在写繁重的工作负载中，this used to show up pretty heavily in profiles.
InnoDB拥有REDO和UNDO日志来保证崩溃一致性（crash consistency）并提供事务中的读取一致性（consistent read views to transactions）。这些也保存在磁盘上，redo日志保存在自己的文件中（大小和数目可配置）。redo日志越大，运行崩溃恢复所需的时间越长。redo日志越小，在执行大型或者许多个并发事务时遇到麻烦的概率则越大。
如果你的查询对数据库表做了写操作，这些变更会写入REDO日志中，然后在后台写回表空间文件。有配置参数可以指定在必须执行表空间刷新写回操作之前，InnoDB缓冲池最多有多少空间可以容纳脏页面。
为了保持隔离性（ACID中的I），InnoDB需要为一个新事务分配一个一致性读视图。事务可以显式地开始（比如用BEGIN），也可以隐式地开始（比如执行SELECT语句）。最近已经有许多工作InnoDB内部创建读取视图时的可伸缩性。更早之前，有许多努力旨在突破InnoDB的1024个并发事务的限制（UNDO日志中的限制）
使得InnoDB的速度比你想象中要快的花哨的东西是自适应哈希索引（Adaptive Hash Index）和可变缓冲区。当然还有针对于此的可扩展性变更。了解它们的基础是有好处的，（当然）它们也是可配置的。
如果你要读取或者写入行（很有可能），还需有一个InnoDB行格式与MySQL服务器行格式的翻译。其中的细节不是很有趣，除非你下决心去读代码或者给我买啤酒听我的讲解。
查询的执行可能会需要从表中取出很多行，把它们连接在一起，对字段求和甚至是排序。如果排序字段做了索引，最好用这个索引。MySQL可能还需要做filesort（可能使用磁盘文件做行排序）或者创建一张临时表来执行查询。临时表要么使用MEMORY（之前是HEAP）存储引擎，要么使用MyISAM存储引擎。通常来讲，你应该避免使用临时表——做IO操作总是不好的。
当你得到查询结果的时候，你可能想这就结束了。然而，你可能也是复制分层（replication hierarchy）的一部分。如果是这样，这个事务做的任何修改都会被写回二进制日志。这是由MySQL服务器（而非存储引擎）保管的日志文件，记录所有表发生的变更。这个日志随后可以被别的MySQL服务器拉取和应用，这些服务器充当MySQL主服务器的从服务器。
我们在此忽略基于语句的复制（statement based replication）和基于行的复制（row based replication）之间的区别，这些区别在别处进行讨论。作为复制（replication）的一部分意味着你会获得一些额外的锁以及额外的一两个需要写的文件。二进制日志（binlog）是一个磁盘文件，它会被不断的追加写入直到达到某一个大小，此时会进行滚动（rotate）。写入文件的内容大小不一（与执行事务的大小有关）。对二进制文件的写入发生在事务提交时（写入二进制日志与写入存储引擎之间的崩溃安全性在别处讨论，基本上你是不会想了解这些内容的）。
如果你的MySQL服务器是一个从服务器，那么你会有一个线程负责从另外一台服务器读取二进制日志，还有另外一个线程负责应用这些变更。
如果启用了慢查询日志和通用查询日志，它们也会被写入各个点，并且现在这部分代码不是最优的（是的，你可能已经猜到了），应用了全局锁。
当查询结果返回到客户端时，MySQL服务器会做一些清理（释放一些内存），并为下一次查询做好准备。你可能会有许多同步执行的查询，这自然是一件好事。
至此，完成了对MySQL内部执行查询所涉及所有内容的概要介绍。
本文链接：http://bookshadow.com/weblog/2014/09/24/some-current-mysql-architecture-writings/
请尊重作者的劳动成果，转载请注明出处！书影博客保留对文章的所有权利。
捐赠书影博客
