Previous Section  < Day Day Up >  Next Section

10.7 MySQL Storage Engines

The MySQL server uses storage engines to manage data in tables. Each storage engine handles a particular table type. These storage engines have certain similarities. For example, each database is represented by a directory under the data directory, and every table in the database has a format (.frm file in the database directory). This is true no matter the table type. On the other hand, storage engines differ in such things as how they use locking to manage query contention, which has implications for query concurrency and deadlock prevention. (Deadlock occurs when multiple queries are blocked and cannot proceed because they're waiting for each other to finish.)

The table type characteristics and features are summarized in this section as an overview. The MyISAM and InnoDB table types are examined in more detail in later chapters. See also the MySQL Reference Manual for more information about table types.

The ISAM table type, which is referenced in several places in the MySQL Reference Manual, is not covered on the exam. It is an older table format that has been made obsolete by the newer MyISAM format that replaces it.

When you create a table, you can choose its type according to which storage engine offers features that best fit the needs of your application. Section 10.7.6, "Specifying the Storage Engine for a Table," discusses how to choose a table type when creating a table.

For any table, the DESCRIBE statement provides information about the columns in a table. However, DESCRIBE doesn't show the table type. If you want to know that, you can use either the SHOW CREATE TABLE or the SHOW TABLE STATUS statement. The latter also shows other information that might be of interest. For example, it displays when a table was created or last modified.

10.7.1 MyISAM Tables

The MyISAM storage engine manages tables that have the following characteristics:

  • Each MyISAM table is represented on disk by an .frm format file, as well as an .MYD datafile and an .MYI index file. All these files are located in the database directory.

  • MyISAM has the most flexible AUTO_INCREMENT column handling of all the table types.

  • MyISAM tables can be used to set up MERGE tables.

  • MyISAM tables can be converted into fast, compressed, read-only tables.

  • MyISAM supports FULLTEXT searching.

  • MySQL manages contention between queries for MyISAM table access using table-level locking. Query performance is very fast for retrievals. Multiple queries can read the same table simultaneously. For a write query, an exclusive table-level lock is used to prevent use of the table by other read or write queries, leading to reduced performance in environments with a mix of read and write queries. Deadlock cannot occur with table-level locking.

10.7.2 InnoDB Tables

The InnoDB storage engine manages tables that have the following characteristics:

  • Each InnoDB table is represented on disk by an .frm format file in the database directory, as well as data and index storage in the InnoDB tablespace. This tablespace is a logical single storage area that is made up of one or more files or partitions on disk. The tablespace is shared by all InnoDB tables.

  • InnoDB supports transactions (commit and rollback) with full ACID compliance.

  • InnoDB provides auto-recovery after a crash of the MySQL server or the host where the server runs.

  • InnoDB supports foreign keys and referential integrity, including cascaded deletes and updates.

  • MySQL manages query contention for InnoDB tables using multi-versioning and row-level locking. Multi-versioning gives each transaction its own view of the database. This, combined with row-level locking, keeps contention to a minimum. The result is good concurrency even in an environment consisting of mixed reads and writes. However, it's possible for deadlock to occur.

10.7.3 MERGE Tables

The MERGE storage engine manages tables that have the following characteristics:

  • A MERGE table is a collection of identically structured MyISAM tables. Each MERGE table is represented on disk by an .frm format file and an .MRG file that lists the names of the constituent MyISAM files. Both files are located in the database directory.

  • Logically, a query on a MERGE table acts as a query on all the MyISAM tables of which it consists.

  • A MERGE table creates a logical entity that can exceed the maximum MyISAM table size.

10.7.4 BDB (Berkeley DB) Tables

The BDB storage engine manages tables that have the following characteristics:

  • Each BDB table is represented on disk by an .frm format file and a .db file that stores data and index information. Both files are located in the database directory.

  • BDB supports transactions (commit and rollback) with full ACID compliance.

  • BDB provides auto-recovery after a crash of the MySQL server or the host where the server runs.

  • MySQL manages query contention for BDB tables using page-level locking. This locking level provides concurrency performance that's intermediate to that of row-level and table-level locking. It's possible for deadlock to occur.

10.7.5 HEAP (MEMORY) Tables

The HEAP storage engine manages tables that have the following characteristics:

  • Each HEAP table is represented on disk by an .frm format file in the database directory. Table data and indexes are stored in memory.

  • In-memory storage results in very good performance. A memory table is never written to disk, so reads and writes are extremely fast. (A small MyISAM table that can be cached in memory and is used primarily for reading might be as fast, but there's no guarantee that its contents will stay in the cache.)

  • HEAP table contents do not survive a restart of the server. The table itself survives, but the contents are empty after a restart.

  • HEAP tables use up memory, so they should not be used for large tables.

  • MySQL manages query contention for HEAP tables using table-level locking. Deadlock cannot occur.

10.7.6 Specifying the Storage Engine for a Table

Every table must be created using one of the table types supported by the server. The set of table types available depends on which storage engines are present. This is determined both by how the server was compiled when it was built and by the options used at startup:

  • The MyISAM, MERGE, and HEAP storage engines are always available.

  • The InnoDB storage engine is included in all binary distributions. If you build MySQL from source, InnoDB is included by default unless you specify the --without-innodb configuration option. For a server that has the InnoDB storage engine included, support can be disabled at startup with the --skip-innodb option.

  • The BDB storage engine is included in MySQL-Max binary distributions. If you build MySQL from source, BDB is not included unless you specify the --with-berkeley-db configuration option. For a server that has the BDB storage engine included, support can be disabled at startup with the --skip-bdb option.

To specify a table type when creating a table, include a TYPE = type_name option in the CREATE TABLE statement. For example:







CREATE TABLE t (i INT) TYPE = InnoDB;

To change the type of an existing table, use an ALTER TABLE statement:







ALTER TABLE t TYPE = MyISAM;

If a CREATE TABLE statement includes no TYPE option, the table is created using the default table type. The built-in default table type is MyISAM. This can be changed in several ways:

  • The default table type can be specified at server startup time with the --default-table-type option.

  • For a running server, an administrator who has the SUPER privilege can change the default table type globally for all clients by setting the table_type variable:

    




    
mysql> SET GLOBAL table_type = type_name;

    Setting the table type this way affects any client that connects after the statement executes. Clients that are connected at the time of statement execution are unaffected.

  • Any client can change its own default table type by issuing this statement:

    




    
mysql> SET SESSION table_type = type_name;

If a CREATE TABLE statement includes a type_name value that is one of the legal type names but the storage engine is not active, the server creates the table as a MyISAM table. This is true even if the default table type has been changed to something other than MyISAM.

10.7.7 Using TEMPORARY Tables

Each storage engine implements tables with a particular set of characteristics. One characteristic held in common by all storage engines is that by default they create tables that exist until they are removed with DROP TABLE. This behavior may be changed by using CREATE TEMPORARY TABLE rather than CREATE TABLE. A TEMPORARY table differs from a non-TEMPORARY table in the following ways:

  • It's visible only to the client that created it and may be used only by that client. This means that different clients can create TEMPORARY tables that have the same name and no conflict occurs.

  • The server drops a TEMPORARY table automatically when the client connection ends if the client has not already dropped it.

  • A TEMPORARY table may have the same name as a non-TEMPORARY table. The non- TEMPORARY table becomes hidden to the client that created the TEMPORARY table as long as the TEMPORARY table exists.

A table created with TEMPORARY is not the same thing as a HEAP table. A HEAP table is temporary in the sense that its contents are lost if you restart the server, but the table itself continues to exist in its database. A TEMPORARY table exists only while the client that created it remains connected, and then disappears. Given that a server restart necessarily involves termination of all client connections, it also results in removal of all TEMPORARY tables.

    Previous Section  < Day Day Up >  Next Section