29.1. MySQL Storage Engines

All tables managed by MySQL Server have certain similarities. For example, every table in a database has a format (.frm) file in the database directory. This file, which stores the definition of the table's structure, is created by the server. Tables have differences as well, which are tied to the storage engines that the server uses to manage table contents. Each storage engine has a particular set of operational characteristics. For example, engines may create additional disk files to accompany the .frm files, but the types of files that they create to manage data and index storage vary per engine. Storage engines differ in other ways as well, such as in the way that they use locking to manage query contention, or in whether the tables that they provide are transactional or non-transactional. These engine properties have implications for query processing performance, concurrency, and deadlock prevention. (Deadlock occurs when multiple queries are blocked and cannot proceed because they are waiting for each other to finish.)

When you create a table, you can choose what storage engine to use. Typically, this choice is made according to which storage engine offers features that best fit the needs of your application. For example, ask yourself what types of queries you'll use the table for. Then choose a storage engine that uses a locking level appropriate for the anticipated query mix. MyISAM table-level locking works best for a query mix that is heavily skewed toward retrievals and includes few updates. Use InnoDB if you must process a query mix containing many updates. InnoDB's use of row-level locking and multi-versioning provides good concurrency for a mix of retrievals and updates. One query can update rows while other queries read or update different rows of the table.

To specify a storage engine explicitly in a CREATE TABLE statement, use an ENGINE option. The following statement creates t as an InnoDB table:

 CREATE TABLE t (i INT) ENGINE = InnoDB; 

If you create a table without using an ENGINE option to specify a storage engine explicitly, the MySQL server creates the table using the default engine, which is given by the value of the storage_engine system variable. Section 8.2.2, "Specifying the Storage Engine for a Table," further discusses how to specify storage engines when creating tables and how to change the default storage engine.

To determine which storage engine is used for a given table, you can use the SHOW CREATE TABLE or the SHOW TABLE STATUS statement:

 mysql> SHOW CREATE TABLE City\G *************************** 1. row ***************************        Table: City Create Table: CREATE TABLE `City` (   `ID` int(11) NOT NULL auto_increment,   `Name` char(35) NOT NULL default '',   `CountryCode` char(3) NOT NULL default '',   `District` char(20) NOT NULL default '',   `Population` int(11) NOT NULL default '0',   PRIMARY KEY  (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> SHOW TABLE STATUS LIKE 'CountryLanguage'\G *************************** 1. row ***************************            Name: CountryLanguage          Engine: MyISAM         Version: 10      Row_format: Fixed            Rows: 984  Avg_row_length: 39     Data_length: 38376 Max_data_length: 167503724543    Index_length: 22528       Data_free: 0  Auto_increment: NULL     Create_time: 2005-04-26 22:15:35     Update_time: 2005-04-26 22:15:43      Check_time: NULL       Collation: latin1_swedish_ci        Checksum: NULL  Create_options:         Comment: 1 row in set (0.00 sec) 

The INFORMATION_SCHEMA TABLES table contains storage engine information as well:

 mysql> SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES     -> WHERE TABLE_SCHEMA = 'world'; +-----------------+--------+ | TABLE_NAME      | ENGINE | +-----------------+--------+ | City            | MyISAM | | Country         | MyISAM | | CountryLanguage | MyISAM | +-----------------+--------+ 

Although you can choose which storage engine to use for a table, in most respects the way that you use the table after creating it is engine independent. Operations on tables of all types are performed using the SQL interface and MySQL manages engine-dependent details for you at a lower level in its architecture. That is, your interface to tables is at the higher SQL tier, and table-management details are at the lower storage-engine tier. There are, nonetheless, times when knowing which storage engine manages a table can enable you to use the table more efficiently. Engine-specific optimizations that you can take advantage of are covered in Chapter 38, "Optimizing Databases."

Before you can use a given storage engine, it must be compiled into the server and enabled. MySQL Server uses a modular architecture: Each storage engine is a software module that is compiled into the server. The use of this modular approach allows storage engines to be easily selected for inclusion in the server at configuration time.

Some storage engines are always available, such as MyISAM, MERGE, and MEMORY. Other engines are optional. Support for optional engines typically can be selected when MySQL is configured and built. Compiled-in optional engines also typically can be enabled or disabled with a server startup option. For example, 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 may be disabled at startup with the --skip-innodb option.

To reduce memory use, don't configure unneeded storage engines into the server. This requires that you compile MySQL from source rather than using a precompiled binary distribution. If you are using a binary distribution that includes compiled-in optional engines that you don't need, disable them at runtime.

To see what storage engines are compiled into your server and whether they are available at runtime, use the SHOW ENGINES statement

 mysql> SHOW ENGINES\G *************************** 1. row ***************************  Engine: MyISAM Support: DEFAULT Comment: Default engine as of MySQL 3.23 with great performance *************************** 2. row ***************************  Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tables *************************** 3. row ***************************  Engine: HEAP Support: YES Comment: Alias for MEMORY ... 

The following sections examine several of MySQL's storage engines in more detail. Much of the discussion involves locking concepts, so you should be familiar with the material covered in Chapter 28, "Locking." Note: When this chapter says that deadlock cannot occur with table locking, that is subject to the exception described in section 28.1 "Locking Concepts."