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

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.

The default table type in MySQL is MyISAM. If you are trying to use a table type that is not incompiled or activated, MySQL will instead create a table of type MyISAM.

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

Note that MySQL supports two different kind of tables. Transactions safe tables (BDB) and not transaction safe tables (ISAM,MyISAM and HEAP.

Advantages of transaction safe tables (TST)

Advantages of not transaction safe tables (NTST):

You can combine TST and NTST tables in the same statements to get the best of both worlds.

8.1 MyISAM tables

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

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

The following is new in MyISAM:

MyISAM also supports the following things, which MySQL will be able to use in the near future.

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. 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 では、常に、全てのインデックスが再構築できることに注意してください。 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 tablemyisamchk を使用して、テーブルの フラグメンテーションを修正することが可能です。 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. 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.

8.2 ISAM tables

You can also use the deprecated ISAM table type. This will disappear rather soon because 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. 「15.6 Using myisamchk for crash recovery」節参照.

ISAM has the following features/properties:

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:

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:

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.

8.4 BDB or Berkeley_db tables

Berkeley DB (http://www.sleepycat.com has provided MySQL with a transaction safe table handler. This will survive crashes and also provides commit and rollaback on transactions. In order to build MySQL 3.23.16 with support for BDB tables, you will need Berkeley DB 3.1.5 which can be downloaded from ftp://ftp.mysql.com/pub/mysql/Downloads/db/db-3.1.5.tar.gz.

Even if Berkeley DB is in itself very tested and reliably, the MySQL interface is still very alpha, but we are actively improving and optimizing it to get it this stable real soon.

If you are running with AUTOCOMMIT=0 then your changes in BDB tables will not be updated until you execute COMMIT. Instead of commit you can execute ROLLBACK to forget your changes. 「7.26 COMMIT/ROLLBACK 構文」節参照.

The following options to mysqld can be used to change the behavour of BDB tables:

--bdb-home= directory Berkeley home direcory item --bdb-lock-detect=# Berkeley lock detect. One of (DEFAULT, OLDEST, RANDOM or YOUNGEST)
--bdb-logdir=directory Berkeley DB log file directory
--bdb-nosync Don't synchronously flush logs
--bdb-recover Start Berkeley DB in recover mode
--bdb-tmpdir=directory Berkeley DB tempfile name
--skip-bdb Don't use berkeley db.

If you use --skip-bdb, MySQL will not initialize the Berkeley DB library and this will save a lot of memory. You can of course not use BDB tables if you are using this option.

Some characteristic of BDB tables:

Some things that we have to fix in the near future:

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