A Technical Tour of MySQL

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 1 MySQL 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.

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.