In this article, Vikram gives us a sneak-peek under the hood of MySQL to see what makes it tick, all the while explaining the various MySQL subsystems and how they interact with each other. This excerpt comes from Chapter two of MySQL: The Complete Reference (McGraw-Hill/Osborne, ISBN 0-07-222477-0, 2004).
The bulk of this book addresses the capabilities of the MySQL engine and illustrates the uses of MySQL through sample applications that manipulate data stored in various types of databases. Before we get into that, though, it’s important that you have a sound technical understanding of how MySQL works, so that you can make informed decisions about how best to deploy and optimize the application for your needs.
To that end, this chapter is primarily focused on giving you an overall view of the components and functionality that make up MySQL. Understanding these features will be helpful as you read further through the book and apply your knowledge to the development of MySQL-based applications.
An Overview of MySQL Architecture
MySQL is based on a tiered architecture, consisting of both primary subsystems and support components that interact with each other to read, parse, and execute queries, and to cache and return query results.
Primary Subsystems The MySQL architecture consists of five primary subsystems that work together to respond to a request made to the MySQL database server:
The Query Engine
The Storage Manager
The Buffer Manager
The Transaction Manager
The Recovery Manager
The organization of these features is shown in Figure 1.We’ll explain each one briefly to help you gain a better understanding of how the parts fit together.
FIGURE 1MySQL subsystems
The Query Engine This subsystem contains three interrelated components:
The Syntax Parser
The Query Optimizer
The Execution Component
The Syntax Parser decomposes the SQL commands it receives from calling programs into a form that can be understood by the MySQL engine. The objects that will be used are identified, along with the correctness of the syntax. The Syntax Parser also checks the objects being referenced to ensure that the privilege level of the calling program allows it to use them.
The Query Optimizer then streamlines the syntax for use by the Execution Component, which then prepares the most efficient plan of query execution. The Query Optimizer checks to see which index should be used to retrieve the data as quickly and efficiently as possible. It chooses one from among the several ways it has found to execute the query and then creates a plan of execution that can be understood by the Execution Component.
The Query Optimizer uses probability-based induction, so you may want to override it if you think that you already know the ideal way to access your query results; this will prevent the engine from using another, less optimal plan. MySQL provides you the option of giving the engine certain “hints” if you want it to use specific indexes without checking the Optimizer.
The Execution Component then interprets the execution plan and, based on the information it has received, makes requests of the other components to retrieve the records.
Adapting to the Environment
MySQL takes advantage of the separation between the Query Engine and the rest of the components to provide table handlers in addition to the native MyISAM handler, such as handlers for HEAP and MERGE table types. It also provides a new Storage Manager and a new Transaction Manager for each table handler. Since InnoDB and BDB table types have transaction features not present in the native MySQL types, these were modified by their respective third-party creators to adapt to being plugged into MySQL’s existing Query Engine.
You can read more about the various table types supported by MySQL in Chapter 8.
Remember: this is chapter two of MySQL: The Complete Reference, by Vikram Vaswani (McGraw-Hill/Osborne, ISBN 0-07-222477-0, 2004). Vikram is the founder of Melonfire, and has had numerous articles featured on Dev Shed. Buy this book now.
/ 30 
