Home arrow MySQL arrow Storage Engine (Table Types)

Storage Engine (Table Types)

This chapter covers the MySQL architecture, locking and concurrency, and transactions. It also discusses how to select the right engine and looks at each of MySQL's storage engines in detail. (From the book High Performance MYSQL: Optimization, Backups, Replication and Load Balancing, by Jeremy Zawodny and Derek Balling, ISBN: 0596-003064, O'Reilly Media, 2004.)

  1. Storage Engine (Table Types)
  2. Locking
  3. Multi-Version Concurrency Control
  4. Transactions
  5. Bene
  6. Deadlocks
  7. Transactions in MySQL
  8. Selecting the Right Engine
  9. Practical Examples
  10. Table Conversions
  11. The Storage Engines
  12. MyISAM Tables
  13. Compressed MyISAM Tables
  14. InnoDB Tables
  15. Heap (In-Memory) Tables
By: O'Reilly Media
Rating: starstarstarstarstar / 55
August 02, 2004

print this article



High Performance MySQLOne powerful aspect of MySQL that sets it apart from nearly every other database server is that it offers users many choices and options depending upon the user’s environment. From the server point of view, its default configuration can be changed to run well on a wide range of hardware. At the application development level, you have a variety of data types to choose from when creating tables to store records. But what’s even more unusual is that you can choose the type of table in which the records will be stored. You can even mix and match tables of different types in the same database!

Storage engines used to be called table types. From time to time we refer to them as table types when it’s less awkward to do so. In this chapter, we’ll show the major differences between the storage engines and why those differences are important. We’ll begin with a look at locking and concurrency as well as transactions—two concepts that are critical to understanding some of the major differences between the various engines. Then we’ll discuss the process of selecting the right one for your applications. Finally, we’ll look deeper into each of the storage engines and get a feel for their features, storage formats, strengths and weaknesses, limitations, and so on.

Before drilling down into the details, there are a few general concepts we need to cover because they apply across all the storage engines. Some aren’t even specific to MySQL at all; they’re classic computer science problems that just happen to occur frequently in the world of multiuser database servers.

MySQL Architecture

It will greatly aid your thinking about storage engines and the capabilities they bring to MySQL if you have a good mental picture of where they fit. Figure 2-1 provides a logical view of MySQL. It doesn’t necessarily reflect the low-level implementation, which is bound to be more complicated and less clear cut. However, it does serve as a guide that will help you understand how storage engines fit in to MySQL. (The NDB storage engine was added to MySQL just before this book was printed. Watch for it in the second edition.)

High Performance MySQL
Figure 2-1. A logical view of MySQL's architecture.

The topmost layer is composed of the services that aren’t unique to MySQL. They’re services most network-based client/server tools or servers need: connection handling, authentication, security, etc.

The second layer is where things get interesting. Much of the brains inside MySQL live here, including query parsing, analysis, optimization, caching, and all the built-in functions (dates, times, math, encryption, etc.). Any functionality provided across storage engines lives at this level. Stored procedures, which will arrive in MySQL 5.0, also reside in this layer.

The third layer is made up of storage engines. They’re responsible for the storage and retrieval of all data stored “in” MySQL. Like the various filesystems available for Linux, each storage engine has its own benefits and drawbacks. The good news is that many of the differences are transparent at the query layer.

The interface between the second and third layers is a single API not specific to any given storage engine. This API is made up of roughly 20 low-level functions that perform operations such as “begin a transaction” or “fetch the row that has this primary key” and so on. The storage engines don’t deal with SQL or communicate with each other; they simply respond to requests from the higher levels within MySQL. 

Buy the book!If you've enjoyed what you've seen here, or to get more information, click on the "Buy the book!" graphic. Pick up a copy today!

Visit the O'Reilly Network http://www.oreillynet.com for more online content.

>>> More MySQL Articles          >>> More By O'Reilly Media

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: