Table of Contents
MySQL supports several storage engines that act as handlers for different table types. MySQL storage engines include both those that handle transaction-safe tables and those that handle non-transaction-safe tables:
The original storage engine was ISAM, which managed non-transactional tables. This engine has been replaced by MyISAM and should no longer be used. It is deprecated in MySQL 4.1, and will be removed in MySQL 5.0.
In MySQL 3.23.0, the MyISAM and HEAP storage engines were introduced. MyISAM is an improved replacement for ISAM. The HEAP storage engine provides in-memory tables. The MERGE storage engine was added in MySQL 3.23.25. It allows a collection of identical MyISAM tables to be handled as a single table. All three of these storage engines handle non-transactional tables, and all are included in MySQL by default. Note that the HEAP storage engine now is known as the MEMORY engine.
The InnoDB and BDB storage engines that handle transaction-safe tables were introduced in later versions of MySQL 3.23. Both are available in source distributions as of MySQL 3.23.34a. BDB is included in MySQL-Max binary distributions on those operating systems that support it. InnoDB also is included in MySQL-Max binary distributions for MySQL 3.23. Beginning with MySQL 4.0, InnoDB is included by default in all MySQL binary distributions. In source distributions, you can enable or disable either engine by configuring MySQL as you like.
NDB Cluster is the storage engine used by MySQL Cluster to implement tables that are partitioned over many computers. It is available in source code distributions as of MySQL 4.1.2 and binary distributions as of MySQL-Max 4.1.3.
This chapter describes each of the MySQL storage engines except for InnoDB, which is covered in Chapter 16, The InnoDB Storage Engine and NDB Cluster which is covered in Chapter 17, MySQL Cluster.
When you create a new table, you can tell MySQL what type of table to create by adding an ENGINE or TYPE table option to the CREATE TABLE statement:
CREATE TABLE t (i INT) ENGINE = INNODB; CREATE TABLE t (i INT) TYPE = MEMORY;
ENGINE is the preferred term, but cannot be used before MySQL 4.0.18. TYPE is available beginning with MySQL 3.23.0, the first version of MySQL for which multiple storage engines were available.
If you omit the ENGINE or TYPE option, the default table type is usually MyISAM. This can be changed by setting the table_type system variable.
To convert a table from one type to another, use an ALTER TABLE statement that indicates the new type:
ALTER TABLE t ENGINE = MYISAM; ALTER TABLE t TYPE = BDB;
See CREATE TABLE and ALTER TABLE.
If you try to use a storage engine that is not compiled in or that is compiled in but deactivated, MySQL instead creates a table of type MyISAM. This behavior is convenient when you want to copy tables between MySQL servers that support different storage engines. (For example, in a replication setup, perhaps your master server supports transactional storage engines for increased safety, but the slave servers use only non-transactional storage engines for greater speed.)
This automatic automatic substitution of the MyISAM table type when an unavailable type is specified can be confusing for new MySQL users. In MySQL 4.1 and up, a warning is generated when a table type is automatically changed.
MySQL always creates an .frm file to hold the table and column definitions. The table's index and data may be stored in one or more other files, depending on the table type. The server creates the .frm file above the storage engine level. Individual storage engines create any additional files required for the tables that they manage.
A database may contain tables of different types.
Transaction-safe tables (TSTs) have several advantages over non-transaction-safe tables (NTSTs):
Safer. Even if MySQL crashes or you get hardware problems, you can get your data back, either by automatic recovery or from a backup plus the transaction log.
You can combine many statements and accept them all at the same time with the COMMIT statement (if autocommit is disabled).
You can execute ROLLBACK to ignore your changes (if autocommit is disabled).
If an update fails, all your changes will be restored. (With non-transaction-safe tables, all changes that have taken place are permanent.)
Transaction-safe storage engines can provide better concurrency for tables that get many updates concurrently with reads.
Note that to use the InnoDB storage engine in MySQL 3.23, you must configure at least the innodb_data_file_path startup option. In 4.0 and up, InnoDB uses default configuration values if you specify none. See InnoDB configuration.
Non-transaction-safe tables have several advantages of their own, all of which occur because there is no transaction overhead:
Much faster
Lower disk space requirements
Less memory required to perform updates
You can combine transaction-safe and non-transaction-safe tables in the same statements to get the best of both worlds. However, within a transaction with autocommit disabled, changes to non-transaction-safe tables still are committed immediately and cannot be rolled back.
MyISAM is the default storage engine as of MySQL 3.23. It is based on the ISAM code but has many useful extensions.
Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table definition. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension,
To specify explicitly that you want a MyISAM table, indicate that with an ENGINE or TYPE table option:
CREATE TABLE t (i INT) ENGINE = MYISAM; CREATE TABLE t (i INT) TYPE = MYISAM;
Normally, the ENGINE or TYPE option is unnecessary; MyISAM is the default storage engine unless the default has been changed.
You can check or repair MyISAM tables with the myisamchk utility. See the section called “Using myisamchk for Crash Recovery”. You can compress MyISAM tables with myisampack to take up much less space. See myisampack.
The following characteristics of the MyISAM storage engine are improvements over the older ISAM engine:
All data values are stored with the low byte first. This makes the data machine and operating system independent. The only requirement for binary portability is that the machine uses two's-complement signed integers (as every machine for the last 20 years has) and IEEE floating-point format (also totally dominant among mainstream machines). The only area of machines that may not support binary compatibility are embedded systems, which sometimes have peculiar processors.
There is no big speed penalty for storing data low byte first; the bytes in a table row normally are unaligned and it doesn't take that much more power to read an unaligned byte in order than in reverse order. Also, the code in the server that fetches column values is not time critical compared to other code.
Large files (up to 63-bit file length) are supported on filesystems and operating systems that support large files.
Dynamic-sized rows are much less fragmented when mixing deletes with updates and inserts. This is done by automatically combining adjacent deleted blocks and by extending blocks if the next block is deleted.
The maximum number of indexes per table is 64 (32 before MySQL 4.1.2). This can be changed by recompiling. The maximum number of columns per index is 16.
The maximum key length is 1000 bytes (500 before MySQL 4.1.2). This can be changed by recompiling. For the case of a key longer than 250 bytes, a larger key block size than the default of 1024 bytes is used.
BLOB and TEXT columns can be indexed.
NULL values are allowed in indexed columns. This takes 0-1 bytes per key.
All numeric key values are stored with the high byte first to allow better index compression.
Index files are usually much smaller with MyISAM than with ISAM. This means that MyISAM normally will use less system resources than ISAM, but will need more CPU time when inserting data into a compressed index.
When records are inserted in sorted order (as when you are using an AUTO_INCREMENT column), the index tree is split so that the high node only contains one key. This improves space utilization in the index tree.
Internal handling of one AUTO_INCREMENT column per table. MyISAM automatically updates this column for INSERT/UPDATE. This makes AUTO_INCREMENT columns faster (at least 10%). Values at the top of the sequence are not reused after being deleted as they are with ISAM. (When an AUTO_INCREMENT column is defined as the last column of a multiple-column index, reuse of deleted values does occur.) The AUTO_INCREMENT value can be reset with ALTER TABLE or myisamchk.
If a table doesn't have free blocks in the middle of the data file, you can INSERT new rows into it at the same time that other threads are reading from the table. (These are known as concurrent inserts.) A free block can occur as a result of deleting rows or an update of a dynamic length row with more data than its current contents. When all free blocks are used up (filled in), future inserts become concurrent again.
You can put the data file and index file on different directories to get more speed with the DATA DIRECTORY and INDEX DIRECTORY table options to CREATE TABLE. See CREATE TABLE.
As of MySQL 4.1, each character column can have a different character set.
There is a flag in the MyISAM index file that indicates whether the table was closed correctly. If mysqld is started with the --myisam-recover option, MyISAM tables are automatically checked when opened and repaired if the table wasn't closed properly.
myisamchk marks tables as checked if you run it with the --update-state option. myisamchk --fast checks only those tables that don't have this mark.
myisamchk --analyze stores statistics for key parts, not only for whole keys as in ISAM.
myisampack can pack BLOB and VARCHAR columns; pack_isam cannot.
MyISAM also supports the following features, which MySQL will be able to use in the near future:
Support for a true VARCHAR type; a VARCHAR column starts with a length stored in two bytes.
Tables with VARCHAR may have fixed or dynamic record length.
VARCHAR and CHAR columns may be up to 64KB.
A hashed computed index can be used for UNIQUE. This will allow you to have UNIQUE on any combination of columns in a table. (You can't search on a UNIQUE computed index, however.)
The following options to mysqld can be used to change the behavior of MyISAM tables:
Set the mode for automatic recovery of crashed MyISAM tables.
Don't flush key buffers between writes for any MyISAM table.
Note: If you do this, you should not use MyISAM tables from another program (such as from another MySQL server or with myisamchk) when the table is in use. Doing so will lead to index corruption.
Using --external-locking will not help for tables that use --delay-key-write.
See the section called “mysqld Command-Line Options”.
The following system variables affect the behavior of MyISAM tables:
The size of the tree cache used in bulk insert optimization. Note: This is a limit per thread!
Used to help MySQL to decide when to use the slow but safe key cache index creation method. Note: This parameter is given in megabytes before MySQL 4.0.3, and in bytes as of 4.0.3.
Don't use the fast sort index method to create an index if the temporary file would become larger than this. Note: This parameter is given in megabytes before MySQL 4.0.3, and in bytes as of 4.0.3.
Set the size of the buffer used when recovering tables.
See the section called “Server System Variables”.
Automatic recovery is activated if you start mysqld with the --myisam-recover option. In this case, when the server opens a MyISAM table, it checks whether the table is marked as crashed or whether the open count variable for the table is not 0 and you are running the server with --skip-external-locking. If either of these conditions is true, the following happens:
The table is checked for errors.
If the server finds an error, it tries to do a fast table repair (with sorting and without re-creating the data file).
If the repair fails because of an error in the data file (for example, a duplicate-key error), the server tries again, this time re-creating the data file.
If the repair still fails, the server tries once more with the old repair option method (write row by row without sorting). This method should be able to repair any type of error and has low disk space requirements.
If the recovery wouldn't be able to recover all rows from a previous completed statement and you didn't specify FORCE in the value of the --myisam-recover option, automatic repair aborts with an error message in the error log:
Error: Couldn't repair table: test.g00pages
If you specify FORCE, a warning like this is written instead:
Warning: Found 344 of 354 rows when repairing ./test/g00pages
Note that if the automatic recovery value includes BACKUP, the recovery process creates files with names of the form tbl_name-datetime.BAK. You should have a cron script that automatically moves these files from the database directories to backup media.
MyISAM tables use B-tree indexes. You can roughly calculate the size for the index file as (key_length+4)/0.67, summed over all keys. This is for the worst case when all keys are inserted in sorted order and the table doesn't have any compressed keys.
String indexes are space compressed. If the first index part is a string, it will also be prefix compressed. Space compression makes the index file smaller than the worst-case figure if the string column has a lot of trailing space or is a VARCHAR column that is not always used to the full length. Prefix compression is used on keys that start with a string. Prefix compression helps if there are many strings with an identical prefix.
In MyISAM tables, you can also prefix compress numbers by specifying PACK_KEYS=1 when you create the table. This helps when you have many integer keys that have an identical prefix when the numbers are stored high-byte first.
MyISAM supports three different storage formats. Two of them (fixed and dynamic format) are chosen automatically depending on the type of columns you are using. The third, compressed format, can be created only with the myisampack utility.
When you CREATE or ALTER a table that has no BLOB or TEXT columns, you can force the table format to FIXED or DYNAMIC with the ROW_FORMAT table option. This causes CHAR and VARCHAR columns to become CHAR for FIXED format or VARCHAR for DYNAMIC format.
In the future, you will be able to compress or decompress tables by specifying ROW_FORMAT={COMPRESSED | DEFAULT} to ALTER TABLE. See CREATE TABLE.
Static format is the default for MyISAM tables. It is used when the table contains no variable-length columns (VARCHAR, BLOB, or TEXT). Each row is stored using a fixed number of bytes.
Of the three MyISAM storage formats, static format is the simplest and most secure (least subject to corruption). It is also the fastest of the on-disk formats. The speed comes from the easy way that rows in the data file can be found on disk: When looking up a row based on a row number in the index, multiply the row number by the row length. Also, when scanning a table, it is very easy to read a constant number of records with each disk read operation.
The security is evidenced if your computer crashes while the MySQL server is writing to a fixed-format MyISAM file. In this case, myisamchk can easily determine where each row starts and ends, so it can usually reclaim all records except the partially written one. Note that MyISAM table indexes can always be reconstructed based on the data rows.
General characteristics of static format tables:
All CHAR, NUMERIC, and DECIMAL columns are space-padded to the column width.
Very quick.
Easy to cache.
Easy to reconstruct after a crash, because records are located in fixed positions.
Reorganization is unnecessary unless you delete a huge number of records and want to return free disk space to the operating system. To do this, use OPTIMIZE TABLE or myisamchk -r.
Usually require more disk space than for dynamic-format tables.
Dynamic storage format is used if a MyISAM table contains any variable-length columns (VARCHAR, BLOB, or TEXT), or if the table was created with the ROW_FORMAT=DYNAMIC option.
This format is a little more complex because each row has a header that indicates how long it is. One record can also end up at more than one location when it is made longer as a result of an update.
You can use OPTIMIZE TABLE or myisamchk to defragment a table. If you have fixed-length columns that you access or change frequently in a table that also contains some variable-length columns, it might be a good idea to move the variable-length columns to other tables just to avoid fragmentation.
General characteristics of dynamic-format tables:
All string columns are dynamic except those with a length less than four.
Each record is preceded by a bitmap that indicates which columns contain the empty string (for string columns) or zero (for numeric columns). Note that this does not include columns that contain NULL values. If a string column has a length of zero after trailing space removal, or a numeric column has a value of zero, it is marked in the bitmap and not saved to disk. Non-empty strings are saved as a length byte plus the string contents.
Much less disk space usually is required than for fixed-length tables.
Each record uses only as much space as is required. However, if a record becomes larger, it is split into as many pieces as are required, resulting in record fragmentation. For example, if you update a row with information that extends the row length, the row will be fragmented. In this case, you may have to run OPTIMIZE TABLE or myisamchk -r from time to time to get better performance. Use myisamchk -ei to obtain table statistics.
More difficult than static-format tables to reconstruct after a crash, because a record may be fragmented into many pieces and a link (fragment) may be missing.
The expected row length for dynamic-sized records is calculated using the following expression:
3 + (number of columns + 7) / 8 + (number of char columns) + (packed size of numeric columns) + (length of strings) + (number of NULL columns + 7) / 8
There is a penalty of 6 bytes for each link. A dynamic record is linked whenever an update causes an enlargement of the record. Each new link will be at least 20 bytes, so the next enlargement will probably go in the same link. If not, there will be another link. You may check how many links there are with myisamchk -ed. All links may be removed with myisamchk -r.
Compressed storage format is a read-only format that is generated with the myisampack tool.
All MySQL distributions as of version 3.23.19 include myisampack by default. (This version is when MySQL was placed under the GPL.) For earlier versions, myisampack was included only with licenses or support agreements, but the server still can read tables that were compressed with myisampack. Compressed tables can be uncompressed with myisamchk. (For the ISAM storage engine, compressed tables can be created with pack_isam and uncompressed with isamchk.)
Compressed tables have the following characteristics:
Compressed tables take very little disk space. This minimizes disk usage, which is very nice when using slow disks (such as CD-ROMs).
Each record is compressed separately, so there is very little access overhead. The header for a record is fixed (1-3 bytes) depending on the biggest record in the table. Each column is compressed differently. There is usually a different Huffman tree for each column. Some of the compression types are:
Suffix space compression.
Prefix space compression.
Numbers with a value of zero are stored using one bit.
If values in an integer column have a small range, the column is stored using the smallest possible type. For example, a BIGINT column (eight bytes) can be stored as a TINYINT column (one byte) if all its values are in the range from -128 to 127.
If a column has only a small set of possible values, the column type is converted to ENUM.
A column may use a combination of the preceding compressions.
Can handle fixed-length or dynamic-length records.
The file format that MySQL uses to store data has been extensively tested, but there are always circumstances that may cause database tables to become corrupted.
Even though the MyISAM table format is very reliable (all changes to a table made by an SQL statement are written before the statement returns), you can still get corrupted tables if some of the following things happen:
The mysqld process is killed in the middle of a write.
Unexpected computer shutdown occurs (for example, the computer is turned off).
Hardware errors.
You are using an external program (such as myisamchk) on a table that is being modified by the server at the same time.
A software bug in the MySQL or MyISAM code.
Typical symptoms for a corrupt table are:
You get the following error while selecting data from the table:
Incorrect key file for table: '...'. Try to repair it
Queries don't find rows in the table or return incomplete data.
You can check whether a MyISAM table is okay with the CHECK TABLE statement. You can repair a corrupted MyISAM table with REPAIR TABLE. When mysqld is not running, you can also check or repair a table with the myisamchk command. See CHECK TABLE, REPAIR TABLE, and the section called “myisamchk Invocation Syntax”.
If your tables become corrupted frequently, you should try to determine why this is happening. The most important thing to know is whether the table became corrupted as a result of a server crash. You can verify this easily by looking for a recent restarted mysqld message in the error log. If there is such a message, it is likely that that table corruption is a result of the server dying. Otherwise, corruption may have occurred during normal operation, which is a bug. You should try to create a reproducible test case that demonstrates the problem. See the section called “What to Do If MySQL Keeps Crashing” and the section called “Making a Test Case If You Experience Table Corruption”.
Each MyISAM index (.MYI) file has a counter in the header that can be used to check whether a table has been closed properly. If you get the following warning from CHECK TABLE or myisamchk, it means that this counter has gone out of sync:
clients are using or haven't closed the table properly
This warning doesn't necessarily mean that the table is corrupted, but you should at least check the table to verify that it's okay.
The counter works as follows:
The first time a table is updated in MySQL, a counter in the header of the index files is incremented.
The counter is not changed during further updates.
When the last instance of a table is closed (because of a FLUSH TABLES operation or because there isn't room in the table cache), the counter is decremented if the table has been updated at any point.
When you repair the table or check the table and it is found to be okay, the counter is reset to zero.
To avoid problems with interaction with other processes that might check the table, the counter is not decremented on close if it was zero.
In other words, the counter can go out of sync only under these conditions:
The MyISAM tables are copied without a preceding LOCK TABLES and FLUSH TABLES.
MySQL has crashed between an update and the final close. (Note that the table may still be okay, because MySQL always issues writes for everything between each statement.)
A table was modified by myisamchk --recover or myisamchk --update-state at the same time that it was in use by mysqld.
Many mysqld servers are using the table and one server performed a REPAIR TABLE or CHECK TABLE on the table while it was in use by another server. In this setup, it is safe to use CHECK TABLE, although you might get the warning from other servers. However, REPAIR TABLE should be avoided because when one server replaces the data file with a new one, this is not signaled to the other servers.
In general, it is a bad idea to share a data directory among multiple servers. See the section called “Running Multiple MySQL Servers on the Same Machine” for additional discussion.