英文翻译资料原文及译文

The Architecture of the MySQL

班级：10网工1 学号：1004031010 姓名：汪青松 指导老师签字：

Mysql’s architecture is best understood in the context of its history.

Mysql history goes back to 1979 when Monty Widenius, working for a small company called TCX, created a reporting tool written in BASIC that ran on a 4 MHZ computer with 16 KB RAM. Over time, the tool was rewritten in C and ported to run on Unix. It was still just a low-level storage engine with a reporting front end. The tool was known by the name of Uniseg.

Working under the adverse conditions of little computational resources, and perhaps building on his God-given talent, Monty developed a habit and ability to write very efficient code naturally. He also developed, or perhaps was gifted from the start, with an unusually acute vision of what needed to be done to the code to make it useful in future development—without knowing in advance much detail about what that future development would be.

In addition to the above, with TCX being a very small company and Monty being one of the owners, he had a lot of say in what happened to his code. While there are perhaps a good number of programmers out there with Monty’s talent and ability, for a number of reasons, few get to carry their code around for more than 20 years. Monty did.

Monty’s work, talents, and ownership of the code provided a foundation upon which the Miracle of MySQL could be built.

Some times in the 1990s, TCX customers began to push for an SQL interface to their data. Several possibilities were considered. One was to load it into a commercial database .Monty was not satisfied with the speed. He tried borrowing mysql code for the SQL part and integrating it with his low-level storage engine. That did not work well either. Then came the classic move of a talented driven programmer: “I’ve had enough of those tools that somebody else wrote that don’t work! I’m writing my own!”

Thus in May of 1996 MySQL version 1.0 was released to a limited group, followed by a public release in October 1996 of version 3.11.1. The initial public release provided only a binary distribution for Solaris. A month later, the source and the Linux binary were released.

In the next two years, MySQL was ported to a number of other operating systems as the feature set gradually increased. MySQL was originally released under a special license that allowed commercial use to those who were not redistributing it with their software. Special licenses were available for sale to those who wanted to bundle it with their product. Additionally, commercial support was also being sold. However, it still lacked support for transactions subquery foreign keys, stored procedures, and views. The locking happened only at a table level, which in some cases could slow it down to a grinding halt. Some programmers unable to get around its limitations still considered it a toy, while others were more than happy to dump their Oracle or SQL Server in favor of MySQL, and deal with the limitations in their code in exchange for improvement in performance and licensing cost savings.

Around 1999–2000 a separate company named MySQL AB was established. It hired

several developers and established a partnership with Sleepy cat to provide an SQL interface for the Berkeley DB data files. Since Berkeley DB had transaction capabilities, this would give MySQL support for transactions, which it previously lacked. After some changes in the code in preparation for integrating Berkeley DB, version 3.23 was released. source became equipped with hooks to add any type of storage engine, including a transactional one.

By April of 2000, with some encouragement and sponsorship from Slashdot, masterslave replication capability was added. The old no transactional storage engine, ISAM, was reworked and released as My ISAM. Among a number of improvements, full-text search capabilities were now supported. A short-lived partnership with Nu Sphere to add Gemini, a transactional engine with row-level locking, ended in a lawsuit toward the end of 2001. However, around the same time, Heikki Tuuri approached MySQL AB with a proposal to integrate his own storage engine, InnoDB, which was also capable of transactions and row-level locking.

Heikki’s contribution integrated much more smoothly with the new table handler interface already polished off by the Berkeley DB integration efforts. The MySQL/InnoDB combination became version 4.0, and was released as alpha in October of 2001. By early 2002 the MySQL/InnoDB combo was stable and instantly took MySQL to another level. Version 4.0 was finally declared production stable in March 2003.

In April 2000, with some comes from Slashdot's encouragement and sponsorship, increase the ability of master slave copy. The old the things storage engines, shameless sad men of obscene nets. Learn, nets always copy six | d, theory-the nets, we no longer provide

complete content. And In no DB group gradually stability, make MySQL arrived at another level. In March 2003 announced formally entered the stable production version 4.0.

Version of the upgrade is it but not because In no DB supplement the cause of the results. The developers have thought that MySQL In no DB is very important complement, but do not mean that is completely dependent on it and lead to success. At that time, even now, a new storage engines as a supplement would not change one version of the celebration. In fact, compared with the past version, 4.0 version not much added. Perhaps the most important supplement is increased cache lookup, this make lots of code running performances are being greatly enhanced. The code duplication and rewrite the use of two thread: one is to host to the network I/O, and the other is a deal with updated. And add some improved optimization. Client/server agreement be SSL ability.

In March 2003, named for the alpha 4.1 release, and announced in June 2004 to the test. Different in version 4.0, it increases the many significant improvement. Perhaps the most important son inquires is, by many users a long-awaited function. The index function space directly has been increased to MySQL storage engines. The implementation of the support Unicode. Client/server agreement is getting a lot of change. It more resistant to the outside of the attack, and support to write report.

In alpha4.. 1 release at the same time, another development branch in development still work: version 5.0, it increases the storage process, the server cursor, flip-flops, trying to, XA things, query optimizer have great improvement, and there is a lot of new features. Because the developers feel if MySQL if all the latest top function to join to version 4.1 will take a long time to stability, and they have to handle storage process, and decided to set

up a separate development branch. Finally in the December 2003 alpha5.0 version released. For a while, because the alpha formed two branches caused great confusion. Having working experience when version 4.1 in October 2004 to achieve stability, chaos also solved.

A year later, in October 2005 version 5.0 to a stable.

Then in November 2005 the first alpha5.1 releases, it increased a lot of improvement, including the table data partition, based on done copy, event schedule, and standardization of the API plug-ins, this is helpful to other plug in and the integration of new storage engines.

At this point, MySQL are positive development. 5.0 is the most stable version, although 5.1 is a beta, but should quickly can achieve stability. The new function will be introduced to version 5.2.

MySQL framework, and for most, the structure of the MySQL different from formal definition and norms. In the original code, not to become part of the system, but in order to solve some very specific questions.

The core module: connection manager, thread management, thread connection, user identity authentication module, access control module, the parser, scheduling, inquires the cache module, optimization, table manager, table transformation module, table maintenance module, a status report module, led by analysis storage interface (standard procedures), storage engines achieve (My ISAM, In no DB, MEMORY, Berkeley DB), log

records module, copy the main module, copy from the module, the client/server API, low level of agreement network I/O API, core API.

The core function module, when the server in the command line next start operation, the initial module to control. It analytical configuration files and command line parameters, allocation of global memory buffer, the initial global variable and structure, load access control list, and performing other initialization tasks. Thread connection can create new, or from the thread cache and retrieval requirements of extracting old. Once the thread connection accept control, it first call user identity authentication module. In the user's identity information after verification, the client can request.

Thread connection will request the data transmission to command scheduling. Some requirements in terms of MySQL code called command, can through the command scheduling run directly, but some of the more complex the need in the other modules. A typical commands can be required server and the query, change activities database, the report states, send continuously updated copy games, close the connection or perform some other operations.

In MySQL server terms, there are two types of customer request statement: query and command. Through analysis of any enquiry is request. Command is a call of requests to perform without parser. We will use the internal range of long-term inquires MySQL. Therefore, it is not only a choice, and also have to delete and inserted in our terms just call inquires the. Sometimes we will also query called the SQL statement.

If the opening of complete log records inquires the command of electricity

management requirements log records module access or command call pure text. Thus, in the whole of the query log configuration requirements will be shameless sadness mean of the men nets. Learn, nets always copy six | d, theory-the nets, we no longer provide complete content., the executive in this short circuit, the cache results returned to the user, thread connection accept control, and prepare to another order process. If inquires the cache module report out a mistake, a inquires to analyzer relay, it will be the basis of inquires on how to transfer control to decide.

People can identify the following module, can continue to from that point: optimizer, table modify module, table maintenance module, copy module and a status report module. Choose queries to optimization, update, insert, and delete and table to create and mode, change to their table transformation inquiry module, inquires the, checking, maintenance, update the important statistical data, or ZhengLiBiao to table maintenance module; Inquires concerned copied to copy module; And the position of the request turned to a status report module. There are also a number of table transformation module: delete module, establish a module, update module.

At this point, this module will receive from the parser control each relay in inquires involves access control list module list, and then, after the success, to the table manager, open the table and obtain the necessary lock. Now watch operation module is ready to perform its task, and will release quantity requirement of abstract storage engine modules of the low level such as insert or record update operation, retrieval based on record key value, or watch on the operation of the executive level, such as repair, or update the index statistical information.

Abstract storage engine module will automatically switch to call the concrete method of storage engines, through the corresponding module object polymorphism. In other words, when a storage engine object, the caller think it in dealing with an abstract, but in fact the more concrete object types are: it storage engine object is corresponding to the given phenotype. The interface is a virtual method, it created a transparent effect. The correct method will be calls, call party of no need to know exactly the type of object storage engines object.

When inquires is processed or order, after the corresponding module will be a part of the result set to the statement because they are available. It can also send a warning or false information. If the error message has been made, the client and server know, inquires the or command failed, and take appropriate measures. The client will not accept any more is given query result set, warning or false information data, the server will always control transfer after connected to thread sent a mistake. Note, for not using MySQL exception implementation stability and portability, in all levels of all the requirements of reason, must check and control fault is under the condition of appropriate transmission error.

If low levels of module in some way more data have been modified, and if the binary renew log is enabled, module will be responsible for record recorded update module will be required of the events of the binary renew log, sometimes called copy logs, or, in the main users and developers MySQL, bin log. Once after completion, executive process returns to the links, it carries out the necessary cleaning and to the other inquires from client or waiting for the order. The meeting continue until the client from a command.

In addition to the regular customer interaction, a server may receive a command from

copying slaves of continuous read binary renew log. This command will copy stompers processing module.

If the server as a copy from the configuration, the initial module will call copy from module, which will launch two thread, the so-called SQL thread and the I/O thread. They use the publicity occurred in the main module to update from module. It is in the same server can be configured to the main module and from module.

Network communication with the customers through the client/server protocol modules, the packing in the appropriate format data is responsible, and depending on the connection Settings, compress it. Client/server protocol modules in turn use low-level network I/O modules, and this is responsible for sending and receiving in the data of the cross-platform about a socket level of portable way. It is also used to encrypt data using Open SSL library is responsible for call options if the connection is set correctly.

When they perform their respective tasks, the core component of a lot depends on the server in the core API. Core API provides a rich set of functions, including file of I/O, memory management, string operation, the implementation of the data structure and algorithms, and many other useful functions. MySQL developers encourage to avoid a direct lib of calls, and use core API for citizens to travel to new port platform and the future of code optimization.

MySQL的架构

班级：10网工1 学号：1004031010 姓名：汪青松 指导老师签字：

MySQL的架构的最好的理解是从他的历史背景中去发现。

MySQL的历史可以追溯到1979年，当时蒙蒂维德纽斯，在一家名为TcX的小公司工作，他用BASIC语言创造了一个报告工具，在这个报告工具的基础上他在16KB的RAM中写了4跑道的Mhzcomputer。随着时间的推移，该工具被用C语言重新编写并且移植到Unix系统上运行。但它任然只是一个有前端报告的低级别存储引擎。该工具被称为Unireg。

除了上面所述，在TCX这个小公司，蒙蒂作为拥有者之一，他对自己编写的代码有着很多发言权。虽然有可能在那也有许多很蒙蒂一样拥有天赋和能力的程序员，但由于各种各样的原因，他们中很少能有可以执行代码超过20年的，但蒙蒂是这样。蒙蒂的努力工作，天赋，和代码所有权为以后MySQL的创造打下了坚实的基础。

在20世纪90年代那段时间，TCX的顾客们开始把他们的数据按进SQL的接口中。考虑了几种可能性。一，加载到一个商业数据库。但蒙蒂并不满意运行的速度。于是他试图借用SQL中的部分mSQL代码并结合自己的低级别存储引擎。但并不成功，接着作为一个有天赋的驱动程序员的经典去举动出现了：“我已经有足够的工具那些别人写出来并不能运行的，但我在写我自己的东西。”

因此，在1996年5月MySQL的1.0版本被试用到一个有限制的群体中，随后在1996年10月公开发布了MySQL的3.11.1版本。首次公开发行只提供了对Solaris的二进制分发。一个月后，源和Linux二进制被释放。

在未来两年中，随着功能集逐渐的增加，MySQL被移植到许多别的操作系统中。MySQL最初在一个特殊许可证下发布，允许商业利用那些谁也没分配的软件。特殊许可证也可以与商品一起绑定出售给那些需要的人。此外，商业产权也在被出售。尽管他们独创的目的已经达到，但TcX从中获得的收入已经证明了MySQL未来的发展肯能。

在此期间MySQL发展到3..22版本。它支持SQL语言的完整的一个子集，比我们预期可能有一个人写的优化了很多更复杂的，非常快，非常稳定。APIs的成员都做出了贡献，使任何一个人几乎能在任意一个客户端编写程序语言。但它任然缺少交易，子查询，外键，存储过程和视图的支持。锁定只能发生在一个表级别，在某些情况下可能慢下来然后突然停止运行。一些程序员无法解它的局限性任然认为这只是个玩具，而另、其他人更倾向于MySQL更乐意抛售Oracle和SQL Server，并在处理性能和节约成本的改善换取其代码中的局限。

在1999年~2000年这个时间里，一个命名为MySQL AB的独立公司成立。它公司雇佣了一些开发商并和提供Sleepycat的Berkeley DB的数据文件的SQL接口的建立了伙伴关系。由于Berkeley DB有交易功能。这件给MySQL以前完全被锁定的交易功能带来支持。为了准备整合Berkeley DB在修改一些代码之后，版本3.23被推出了。

虽然MySQL的开发人员不肯做到Berkeley DB所有苛刻的要求，Berkeley DB表也从来没有稳定运行，但努力没有白费。因此，MySQL源代码成为配有挂钩添加任何类型存储的发动机，包括一个事物之一。

到2000年4月，凭借一些来自于Slashdot的鼓励和赞助，增加了masterslave的复制能力。老的非事物存储引擎与InnoDB的组逐步稳定，使得MySQL到达了另一个层面。在2003年3月宣布4.0版本正式进入稳定生产。

版本的升级是值得一提的但绝不是因为InnoDB的补充而导致的结果。MySQL的开发人员一直认为InnoDB是非常重要的的补充，但不意味着是完全依赖它而导致成功的。那时，即使是现在，一个新存储引擎的补充不肯能像一个版本的改变一样庆祝。事实上，相比过去的版本，4.0版本没有多大的补充。也许最重要的补充就是增加了缓存查询，这个使大量代码运行的表现得到了大大的提高。代码的复制与重写使用两个线程：一是来之主机对网络I/O,另一个是处理更新。又增加了一些改进的优化。客户机/服务器协议成为SSL能力。

在2003年3月，命名为alpha的4.1版本发布，并在2004年6月宣布进行测试。不同于4.0版本，它增加了许多重大的改进。也许最重要的是子查询，一个被许多使用者期待已久的功能。空间索引功能的直接被增加到MySQL的存储引擎中。实施了Unicode的支持。客户机/服务器协议得到了许多改变。它更能抵抗外界的攻击，并支持编写报表。

在alpha4..1版本发布的同时，另一个发展分支任然在进行开发工作：版本5.0，它增加了存储过程，服务器端游标，触发器，试图，XA事物，查询优化器有了巨大的改进，并且有了许多新功能。因为MySQL的开发者感觉到如果如果把所有最新的顶级功能加入到4.1版本中将会花费很长的时间去稳定，而且他们不得不对存储过程进行处理，于是决定设立一个单独的开发分支。终于在2003年12月alpha5.0版本发布了。有一阵子，由于alpha形成了两个分支机构造成了很大的混乱。总后当4.1版本在2004年10月达到稳定时，混乱也同时解决了。

一年之后，在2005年10月5.0版本也达到了稳定。

随后在2005年11月第一个alpha5.1版本发布，它增加了许多改进，其中有表中的数据分区，基于行的复制，事件调度，和标准化的API插件，这有利于其它的插件与新存储引擎的整合。

在这一点上，MySQL正在积极发展。5.0是目前最稳定的版本，虽然5.1是测试版，但应该

很快能达到稳定。新功能会被引进到5.2版本中。

MySQL的架构，对于大部分，MySQL的架构不同于正式定义和规范。在最初代码编写时，不是为了在未来成为大系统中的一部分，而是为了解决一些非常具体的问题。

核心模块： 连接管理器、线程管理、线程连接、用户身份验证模块、访问控制模块、分析器、指挥调度、查询缓存模块、优化、表管理器、表改造模块、表维护模块、状态报告模块、引分析存储引起接口（标处理程序）、存储引擎实现（MyISAM, InnoDB, MEMORY, Berkeley DB）、日志记录模块、复制主模块、复制从模块、客户端/服务器协议的API、低级别网络I/O的API、核心的API。

模块的核心作用， 当服务器在命令行下启动运行，初始化模块对它进行控制。它解析配置文件和命令行参数，分配全局内存缓冲区，初始化的全局变量和结构，加载访问控制表，并执行其它初始化任务。线程连接可以创造新的，或者从线程缓存和检索要求中提取旧的。一旦线程连接接受控制，它首先调用用户身份验证模块。在对用户的身份信息进行验证后，客户端可以发出请求。

线程连接将请求的数据传输到指挥调度。一些要求在MySQL代码术语中被称为命令，可以通过指挥调度直接运行，但一些更复杂的需要传载的别的模块中。一个典型的命令可以要求服务器进行查询，更改活动数据库，报告状态，发送不断更新的复制场，关闭连接或执行一些其他操作。

在MySQL服务器术语中，有两种类型的客户请求语句：查询和命令。查询是任何经过分析器的请求。命令是一个无需调用分析器执行的请求。我们将使用MySQL内部范围的长期查询。因此，不仅只有一个选择，并且也有删除和插入在我们的术语中才称为查询。有时我们也将查询

语句称为SQL语句。

如果启用了完整的日志记录查询，指挥调度会要求日志记录模块登陆查询或者命令调用纯文本。因此，在整个日志配置中的查询要求的情况下，执行在这点短暂短路，缓存的结果返回给用户，线程连接接受控制，并准备另一个命令的过程。如果查询缓存模块报告出一个错误，这个查询将转发到分析器去，它将在查询类的基础上对如何进行传输控制做出决定。

人们可以识别以下模块，可以继续从该点：优化器，表修改模块，表维护模块，复制模块和状态报告模块。选择查询转发到优化，更新，插入，删除和表创造和模式，改变查询到各自的表改造模块，查询，检查，维修，更新重要统计数据，或整理表到表维护模块;查询有关复制到复制模块;和地位的请求转到状态报告模块。也存在一表改造模块的数量：删除模块，建立模块，更新模块。

在这一点上，该模块将接收从分析器控制每个传递在查询清单涉及到访问控制模块表，然后，成功后，到表管理器，打开表并获得必要的锁。现在表操作模块已准备好进行其具体任务，并会发行数量要求的抽象存储引擎模块的低级别如插入或更新记录操作，检索基础上的纪录关键值，或表级别上执行的操作，如修理，或更新索引统计信息。

抽象存储引擎模块将自动转换到调用存储引擎的具体方法，通过相应的模块对象的多态性。换句话说，当一个存储引擎对象时，调用者认为它在处理一个抽象的，而实际上更具体的对象类型是：它存储引擎对象是对应于给定的表型。该接口方法是虚拟的，它创造了透明效果。正确的方法将被调用，调用方没有需要的确切知道对象的类型存储引擎对象。

当查询或命令被处理过后，相应的模块将把一部分结果设置成语句因为它们是可用的。它可以同时发送警告或错误信息。如果错误讯息已发出，客户端和服务器都了解，查询或命令失败，

并采取适当的措施。客户端将不接受任何更多的是给定的查询结果集，警告或错误信息数据，而服务器将始终控制转移到线程连接后发出了一个错误。注意，因为MySQL不使用例外实施稳定和可移植性，在所有层次的所有要求的理由，必须是检查与控制的故障的情况下适当的传输错误。

如果低层次的模块以某种方法多数据进行了修改，并且如果二进制更新日志被激活，模块将负责要求记录模块将记录更新事件的二进制更新日志，有时也被称为复制日志，或者，在MySQL的开发者和主要用户，binlog。一旦任务完成后，执行流程返回到连接线，它执行必要的清理和对另一个查询或等待命令从客户端。这次会议继续进行，直到客户端发出的退出命令。

除了与固定客户的互动，一个服务器可能会收到一个命令从复制奴隶连续读取的二进制更新日志。此命令将复制母版处理模块。

如果该服务器作为复制从配置，初始化模块将调用复制从模块，从而将启动两个线程，所谓的SQL线程和I / O线程。他们利用宣传更新发生在主模块到从模块。它是在同一台服务器可以被配置成主模块和从模块。

网络与客户沟通贯穿于客户机/服务器协议模块，这对包装在适当的格式的数据负责，并视连接设置上，压缩它。客户机/服务器协议模块反过来使用低级别的网络I / O模块，这是负责发送和收到关于在跨平台的数据套接字级别可移植的方式。这也是用于加密数据使用OpenSSL库负责调用如果连接选项设置正确。

当他们履行各自的任务，服务器的核心部件大量依赖在核心的API。核心API提供了一个丰富的功能设置，包括文件的I / O，内存管理，字符串操作，实现各种数据结构和算法，以及其他许多有用的功能。 MySQL的开发者鼓励以避免直接的libc调用，并使用核心API以方便市民前往新口岸平台和代码优化的未来。