8 MySQL table types

With MySQL you can currently (version 3.23.6) choose between three basic table formats. When you create a new table, you can tell MySQL which table type it should use for the table. MySQL will always create a .frm file to hold the table and column definitions. Depending on the table type the index and data will be stored in other files.

ALTER TABLE 文を使用すれば、テーブルを違う形式に変更できます。 「7.8 ALTER TABLE構文」節参照.

8.1 MyISAM tables

MyISAM は、MySQL 3.23 でのデフォルトのテーブル形式です． これは ISAM コードを基にし、多くの便利な拡張機能を持っています。

インデックスは .MYI (MYindex) 拡張子のつくファイルに保存され、 データは、 .MYD (MYData) 拡張子のつくファイルに保存されます。 myisamchk ユーティリティを使用して、 MyISAM テーブルの 検査・修復が可能です。 「14.6 Using myisamchk for crash recovery」節参照.

The following is new in MyISAM:

• あるスレッドが読み込み中のテーブルに対し、違うスレッドが 同じテーブルに、新しい行を INSERT できます。 削除はなしで You can INSERT new rows in a table without deleted rows, while other threads are reading from the table.
• 大ファイル (63 bit) のサポート。 ただし、filesystems/operating systems が巨大ファイルをサポートしている場合。
• 全データは、下位バイトが先にかかれます。 これは、データを、マシン・OS 非依存にしました。 The only requirement is that the machine uses twos-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 (since they sometimes have peculiar processors).
• 全数値キーは高位バイトが先に書かれます。 これはインデックスの圧縮率を 良くします。
• Internal handling of one AUTO_INCREMENT column. MyISAM will automatically update this on INSERT/UPDATE. The AUTO_INCREMENT value can be reset with myisamchk. This will make AUTO_INCREMENT columns faster and old numbers will not be reused as with the old ISAM. Note that when a AUTO_INCREMENT is defined on the end of a multi-part-key the old behavior is still present.
• BLOB と TEXT フィールドにインデックスが張れます
• NULL 値をインデックスの張られたフィールドに許します． This takes 0-1 bytes/key.
• 現在、キーの最大長はデフォルトで 500 です。 In cases of keys longer than 250 bytes, a bigger key block size than the default of 1024 bytes is used for this key.
• Maximum number of keys/table enlarged to 32 as default. This can be enlarged to 64 without having to recompile myisamchk.
• There is a flag in the MyISAM file that indicates whether or not the table was closed correctly. This will soon be used for automatic repair in the MySQL server.
• myisamchk will now mark tables as checked. myisamchk --fast will only check those tables that don't have this mark.
• myisamchk -a stores statistics for key parts (and not only for whole keys as in ISAM).
• Dynamic size rows will now be 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.
• myisampack は BLOB と VARCHAR フィールドをパックすることが可能です。

MyISAM also supports the following things, 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 2 bytes.
• Tables with VARCHAR may have fixed or dynamic record length.
• VARCHAR and CHAR may be up to 64K. All key segments have their own language definition. This will enable MySQL to have different language definitions per column.
• 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.)

8.1.1 Space needed for keys

MySQL can support different index types, but the normal type is ISAM or MyISAM. These uses B-tree index and 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 we don'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 above figures 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 which have an identical prefix when the numbers are stored high-byte first.

8.1.2 MyISAM table formats

MyISAM supports 3 different table types. 2 of them are chosen automatically depending on the type of columns you are using. The third, compressed tables, can only be created with the myisampack tool.

8.1.2.1 Static (Fixed-length) table characteristics

This is the default format. It's used when the table contains no VARCHAR, BLOB or TEXT columns.

このフォーマットは、最も単純、かつ、安全なフォーマットです。 これは, Disk 上に作られるテーブルの中で、最も速いフォーマットでもあります。 これはディスク上のデータを見つけやすいからです。 When looking up something with a index and static format it very simple, just multiply the row number with the row length.

Also when scanning a table it is very easy to read a constant number of records with each disk read.

安全、というのは、次の様なことです。 もし仮に、静的(固定長) MyISAM ファイルに書き込み中に、 あなたのコンピュータがクラッシュした場合、 myisamchk は、それぞれのレコードの開始点と終了点を安易に見つけることが出 来ます。 So it can usually reclaim all records except the partially written one. MySQL では、常に、全てのインデックスが再構築できることに注意してください。

• 全ての CHAR, NUMERIC, DECIMAL フィールドは、そのフィールド 長に足りない部分にはスペースが埋められます。
• とても速い
• キャッシュしやすい。
• クラッシュの後再構築しやすい。なぜならレコードが固定された位置に割り当てられてい るから。
• Doesn't have to be reorganized (with myisamchk) unless a huge number of records are deleted and you want to return free disk space to the operating system.
• 通常、動的テーブルよりも多くのディスク容量が必要。

8.1.2.2 Dynamic table characteristics

This format is used if the table contains any VARCHAR, BLOB or TEXT columns or if the table was created with ROW_FORMAT=dynamic.

この形式は少し複雑です。 なぜならそれぞれのレコードが、レコードがどのぐらいの 長さを持っているかを記録するヘッダーを持っているからです。 One record can also end up at more than one location when it is made longer at an update.

OPTIMIZE table か myisamchk を使用して、テーブルの フラグメンテーションを修正することが可能です。 If you have static data that you acess/change a lot in the same table as some VARCHAR or BLOB columns, it might be a good idea to move the dynamic columns to other tables just to avoid fragmentation.

• 全ての文字フィールドが動的になります（ただし4byteより短い物は除きます）
• それぞれのレコードの先頭には、フィールドの状態を表すビット・マップがきます。 このビットは、どの文字型フィールドが空文字('')なのか、 どの数値フィールドがゼロなのかを示します。 (これはフィールドの値が NULL 値とは違います)。 もし、文字型フィールドの文字列の長さ(後に続く空白は取り除かれる)が ゼロ で あったり、あるいは、数値フィールドの値が ゼロ であった場合は、 そのフィールドはビット・マップにマークされ、値はディスクには保存されません。 空文字ではない場合は、文字列のバイト数がビット・マップに記録され、 文字列自身がフィールドに保存されます。
• 通常、固定長のテーブルよりも少ないディスク容量ですみます
• それぞれのレコードは、要求されただけのスペースを使用します。 もしあるレコードが大きくなると、要求された分、それを多くの断片に分けます。 この結果、レコードのフラグメンテーションが発生します。
• 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 myisamchk -r from time to time to get better performance. Use myisamchk -ei tbl_name for some statistics.
• Not as easy to reconstruct after a crash, because a record may be fragmented into many pieces and a link (fragment) may be missing.
• 期待されるレコードの長さは：

  3
  + (フィールド数 + 7) / 8
  + (char フィールドの数)
  + 数値フィールドをパックしたサイズ
  + 文字の長さ
  + (NULL フィールドの数 + 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.

8.1.2.3 Compressed table characteristics

これは読み込み専用の型で、オプションツールの myisampack で作成されます。 (pack_isam for ISAM tables).

myisampack and pack_isam are available to all customers that have bought a MySQL license or MySQL support for their internal use.

• The uncompress code exists in all MySQL distributions so that even customers who don't have myisampack can read tables that were compressed with myisampack
• Compressed tables takes very little disk space. This minimizes disk usage which is very nice when using slow disks (like CD-ROMs).
• Each record is compressed separately (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. Some of the compression types are:
  • There is usually a different Huffman table for each column.
  • Suffix space compression.
  • Prefix space compression.
  • Numbers with value 0 are stored using 1 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 (8 bytes) may be stored as a TINYINT column (1 byte) if all values are in the range 0 to 255.
  • 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 above compressions.
• Can handle fixed or dynamic length records, but not BLOB or TEXT columns.
• Can be uncompressed with myisamchk.

8.2 ISAM tables

You can also use the deprecated ISAM table type. This will disappear rather soon since MyISAM is a better implementation of the same thing. ISAM uses a B-tree index. The index is stored in a file with the .ISM extension and the data is stored in file with the .ISD extension. You can check/repair ISAM tables with the isamchk utility. 「14.6 Using myisamchk for crash recovery」節参照.

ISAM has the following features/properties:

• Compressed and fixed length keys
• Fixed and dynamic record length
• 16 keys with 16 key parts / key
• Max key length 256 (default)
• Data is stored in machine format; this is fast, but is machine/OS dependent.

Most of the things for MyISAM tables are also true for ISAM tables. 「8.1 MyISAM tables」節参照. The major differences compared to MyISAM tables are:

• ISAM tables are not binary portable across OS/Platforms.
• Can't handle tables > 4G.
• Only support prefix compression on strings
• Smaller key limits.
• Dynamic tables gets more fragmented.
• Tables are compressed with pack_isam rather than with myisampack.

8.3 HEAP tables

HEAP tables use a hashed index and are stored in memory. This makes them very fast, but if MySQL crashes you will lose all data stored in them. HEAP is very useful as temporary tables!

MySQL 内部 HEAP テーブルは、100% ダイナッミック・ハッシングを 使用しておいます（オーバーフローエリア無しに）。 There is no extra space needed for free lists. HEAP tables also don't have problems with delete + inserts, which normally is common with hashed tables..

mysql> CREATE TABLE test TYPE=HEAP SELECT ip,SUM(downloads) as down
        FROM log_table GROUP BY ip;
mysql> SELECT COUNT(ip),AVG(down) FROM test;
mysql> DROP TABLE test;

Here are some things you should consider when you use HEAP tables:

• You should always use specify MAX_ROWS in the CREATE statement to ensure that you accidently do not use all memory.
• Indexes will only be used with = and <=> (but are VERY fast).
• HEAP tables can only use whole keys to search for a row; compare this to MyISAM tables where any prefix of the key can be used to find rows.
• HEAP tables use a fixed record length format.
• HEAP doesn't support BLOB/TEXT columns.
• HEAP doesn't support AUTO_INCREMENT columns.
• HEAP doesn't support an index on a NULL column.
• You can have non-unique keys in a HEAP table (this isn't common for hashed tables).
• HEAP tables are shared between all clients (just like any other table).
• You can't search for the next entry in order (that is to use the index to do a ORDER BY).
• Data for HEAP tables are allocated in small blocks. The tables are 100% dynamic (on inserting). No overflow areas and no extra key space is needed. Deleted rows are put in a linked list and are reused when you insert new data into the table.
• You need enough extra memory for all HEAP tables that you want to use at the same time.
• To free memory, you should execute DELETE FROM heap_table or DROP TABLE heap_table.
• MySQL cannot find out how approximately many rows there are between two values (this is used by the range optimizer to decide which index to use). This may affect some queries if you change a MyISAM table to a HEAP table.
• To ensure that you accidentally don't do anything stupid, you can't create HEAP tables bigger than max_heap_table_size.

Memory needed for one row in a HEAP table is:

SUM_OVER_ALL_KEYS(max_length_of_key + sizeof(char*)*2) + ALIGN(length_of_row+1,sizeof(char*))

sizeof(char*) is 4 on 32 bit machines and 8 on 64 bit machines.

Go to the first, previous, next, last section, table of contents.