Home arrow MySQL arrow A Technical Tour of MySQL

A Technical Tour of MySQL

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).

  1. A Technical Tour of MySQL
  2. An Overview of MySQL Architecture (cont'd)
  3. Support Components
  4. The MySQL Engine
  5. Characteristics in Detail
  6. Extensibility
  7. Symmetric Multiprocessing with MySQL
  8. Security
  9. Replication
  10. Application Programming Interfaces and Add-On Tools
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 30
April 20, 2004

print this article



MySQL: The Complete ReferenceThe 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.

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.

>>> More MySQL Articles          >>> More By McGraw-Hill/Osborne

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: