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)
MySQL
crashes or you get hardware problems, you
can get your data back; Either by automatic recovery or from a backup
+ the transaction log.
COMMIT
command.
ROLLBACK
to ignore your changes (if you are not
running in auto commit mode).
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.
MyISAM
は、MySQL 3.23 でのデフォルトのテーブル形式です.
これは ISAM
コードを基にし、多くの便利な拡張機能を持っています。
インデックスは .MYI
(MYindex) 拡張子のつくファイルに保存され、
データは、 .MYD
(MYData) 拡張子のつくファイルに保存されます。
myisamchk
ユーティリティを使用して、 MyISAM
テーブルの
検査・修復が可能です。 「15.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.
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 (at least 10 %) 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.
AUTO_INCREMENT
column) the key tree will be split so that the high node only contains one
key. This will improve the space utilization in the key tree.
BLOB
と TEXT
フィールドにインデックスが張れます
NULL
値をインデックスの張られたフィールドに許します. This takes 0-1
bytes/key.
myisamchk
.
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 mark tables as checked if one runs it with
--update-state
. 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
).
myisampack
は BLOB
と VARCHAR
フィールドをパックすることが可能です。
MyISAM
also supports the following things, which MySQL
will be able to use in the near future.
VARCHAR
type; A VARCHAR
column starts
with a length stored in 2 bytes.
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.
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.)
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.
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.
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
フィールドは、そのフィールド
長に足りない部分にはスペースが埋められます。
myisamchk
) unless a huge number of
records are deleted and you want to return free disk space to the operating
system.
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.
''
)なのか、
どの数値フィールドがゼロなのかを示します。
(これはフィールドの値が NULL
値とは違います)。
もし、文字型フィールドの文字列の長さ(後に続く空白は取り除かれる)が ゼロ で
あったり、あるいは、数値フィールドの値が ゼロ であった場合は、
そのフィールドはビット・マップにマークされ、値はディスクには保存されません。
空文字ではない場合は、文字列のバイト数がビット・マップに記録され、
文字列自身がフィールドに保存されます。
myisamchk
-r
from time to time to get better performance. Use myisamchk -ei
tbl_name
for some statistics.
3 + (フィールド数 + 7) / 8 + (char フィールドの数) + 数値フィールドをパックしたサイズ + 文字の長さ + (NULL フィールドの数 + 7) / 8There 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
.
これは読み込み専用の型で、オプションツールの 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.
myisampack
can read tables that
were compressed with myisampack
0
are stored using 1 bit.
BIGINT
column (8 bytes) may
be stored as a TINYINT
column (1 byte) if all values are in the range
0
to 255
.
ENUM
.
BLOB
or TEXT
columns.
myisamchk
.
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:
pack_isam
rather than with myisampack
.
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:
MAX_ROWS
in the CREATE
statement
to ensure that you accidently do not use all memory.
=
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.
HEAP
table (this isn't common for
hashed tables).
HEAP
tables are shared between all clients (just like any other
table).
ORDER BY
).
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.
DELETE FROM heap_table
or
DROP TABLE heap_table
.
MyISAM
table to a HEAP
table.
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.
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:
LOCK TABLES
doesn't yet work on BDB tables.
ALTER TABLE
doesn't yet work on BDB tables.
MyISAM
tables as one has data in BDB
tables is stored in B-trees and not in a separate data file.
FLUSH LOGS
from time to time to sync to get checkpoints
for the BDB tables.
Some things that we have to fix in the near future:
LOCK TABLES
should work as for other MySQL tables.
ALTER TABLE
doesn't yet work.
SHOW TABLE STATUS
doesn't yet provide that much information for BDB
tables.
Go to the first, previous, next, last section, table of contents.