由于不存在row-size的区别,char与varchar之间对效率影响小
但是,过多的列会长生更为巨大的负面作用 ”
VARCHAR
,VARBINARY
, BLOB
, or TEXT
) 就是动态长度的表
MyISAM
is the default storage engine. It is based on the older
ISAM
code but has many useful extensions. (Note that MySQL 5.1 does
not support
ISAM
.)
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 format. 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 anENGINE
table option:
CREATE TABLE t (i INT) ENGINE = MYISAM;
Normally, it is unnecesary to use ENGINE
to specify theMyISAM
storage engine. MyISAM
is the default engine unless the default has been changed. To ensure thatMyISAM
is used in situations where the default might have been changed, include theENGINE
option explicitly.
You can check or repair MyISAM
tables with the mysqlcheck client or myisamchk utility. You can also compressMyISAM
tables with myisampack to take up much less space. SeeSection 8.9, “mysqlcheck — A Table Maintenance and Repair Program”,Section 5.9.4.1, “Usingmyisamchk for Crash Recovery”, andSection 8.4, “myisampack — Generate Compressed, Read-OnlyMyISAM
Tables”.
MyISAM
tables have the following characteristics:
-
All data values are stored with the low byte first. This makes the data machine and operating system independent. The only requirements for binary portability are that the machine uses two's-complement signed integers and IEEE floating-point format. These requirements are widely used among mainstream machines. Binary compatibility might not be applicable to embedded systems, which sometimes have peculiar processors.
There is no significant speed penalty for storing data low byte first; the bytes in a table row normally are unaligned and it takes little more processing 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.
-
All numeric key values are stored with the high byte first to allow better index compression.
-
Large files (up to 63-bit file length) are supported on filesystems and operating systems that support large files.
-
The maximum number of indexes per
MyISAM
table is 64. This can be changed by recompiling. Beginning with MySQL 5.1.4, you can configure the build by invokingconfigure with the--with-max-indexes=
option, whereN
N
is the maximum number of indexes to permit perMyISAM
table.N
must be less thann or equal to 128. Before MySQL 5.1.4, you must change the source.The maximum number of columns per index is 16.
-
The maximum key length is 1000 bytes. This can also be changed by changing the source and recompiling. For the case of a key longer than 250 bytes, a larger key block size than the default of 1024 bytes is used.
-
When rows 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 is supported.MyISAM
automatically updates this column forINSERT
andUPDATE
operations. This makesAUTO_INCREMENT
columns faster (at least 10%). Values at the top of the sequence are not reused after being deleted. (When anAUTO_INCREMENT
column is defined as the last column of a multiple-column index, reuse of values deleted from the top of a sequence does occur.) TheAUTO_INCREMENT
value can be reset withALTER TABLE
or myisamchk. -
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.
-
If a table has no 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. SeeSection 7.3.3, “Concurrent Inserts”. -
You can put the data file and index file on different directories to get more speed with the
DATA DIRECTORY
andINDEX DIRECTORY
table options toCREATE TABLE
. See Section 13.1.7, “CREATE TABLE
Syntax”. -
BLOB
andTEXT
columns can be indexed. -
NULL
values are allowed in indexed columns. This takes 0–1 bytes per key. -
Each character column can have a different character set. See Chapter 10, Character Set Support.
-
There is a flag in the
MyISAM
index file that indicates whether the table was closed correctly. Ifmysqld is started with the--myisam-recover
option,MyISAM
tables are automatically checked when opened, and are 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 portions of keys, as well as for entire keys.
-
myisampack can pack
BLOB
andVARCHAR
columns.
MyISAM
also supports the following features:
-
Support for a true
VARCHAR
type; aVARCHAR
column starts with a length stored in one or two bytes. -
Tables with
VARCHAR
columns may have fixed or dynamic row length. -
The sum of the lengths of the
VARCHAR
andCHAR
columns in a table may be up to 64KB. -
A hashed computed index can be used for
UNIQUE
. This allows you to haveUNIQUE
on any combination of columns in a table. (However, you cannot search on aUNIQUE
computed index.)
Additional resources
-
A forum dedicated to the
MyISAM
storage engine is available athttp://forums.mysql.com/list.php?21.
The following options to mysqld can be used to change the behavior ofMyISAM
tables. For additional information, see Section 5.2.1, “mysqld Command Options”.
-
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 access
MyISAM
tables from another program (such as from another MySQL server or withmyisamchk) when the tables are in use. Doing so risks index corruption. Using--external-locking
does not eliminate this risk.
The following system variables affect the behavior of MyISAM
tables. For additional information, seeSection 5.2.2, “Server System Variables”.
-
bulk_insert_buffer_size
The size of the tree cache used in bulk insert optimization. Note: This is a limit per thread!
-
myisam_max_sort_file_size
The maximum size of the temporary file that MySQL is allowed to use while re-creating a
MyISAM
index (duringREPAIR TABLE
,ALTER TABLE
, orLOAD DATA INFILE
). If the file size would be larger than this value, the index is created using the key cache instead, which is slower. The value is given in bytes. -
myisam_sort_buffer_size
Set the size of the buffer used when recovering tables.
Automatic recovery is activated if you start mysqld with the--myisam-recover
option. In this case, when the server opens aMyISAM
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 external locking disabled. If either of these conditions is true, the following happens:
-
The server checks the table 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 previously completed statementas and you didn't specifyFORCE
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
. You should have acron script that automatically moves these files from the database directories to backup media.tbl_name-datetime
.BAK
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 is also prefix compressed. Space compression makes the index file smaller than the worst-case figure if a string column has a lot of trailing space or is aVARCHAR
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 thePACK_KEYS=1
table option when you create the table. Numbers are stored with the high byte first, so this helps when you have many integer keys that have an identical prefix.
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 themyisampack utility.
When you use CREATE TABLE
or ALTER TABLE
for a table that has no BLOB
or TEXT
columns, you can force the table format to FIXED
orDYNAMIC
with the ROW_FORMAT
table option.
You can decompress tables by specifying ROW_FORMAT=DEFAULT
withALTER TABLE
.
See Section 13.1.7, “CREATE TABLE
Syntax”, for information aboutROW_FORMAT
.
Static format is the default for MyISAM
tables. It is used when the table contains no variable-length columns (VARCHAR
,VARBINARY
, 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 due to the ease with which rows in the data file can be found on disk: To look up a row based on a row number in the index, multiply the row number by the row length to calculate the row position. Also, when scanning a table, it is very easy to read a constant number of rows with each disk read operation.
The security is evidenced if your computer crashes while the MySQL server is writing to a fixed-formatMyISAM
file. In this case, myisamchk can easily determine where each row starts and ends, so it can usually reclaim all rows except the partially written one. Note thatMyISAM
table indexes can always be reconstructed based on the data rows.
Static-format tables have these characteristics:
-
CHAR
columns are space-padded to the column width.BINARY
columns are padded with0x00
bytes to the column width. -
Very quick.
-
Easy to cache.
-
Easy to reconstruct after a crash, because rows are located in fixed positions.
-
Reorganization is unnecessary unless you delete a huge number of rows 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 dynamic-format tables.
Dynamic storage format is used if a MyISAM
table contains any variable-length columns (VARCHAR
,VARBINARY
, BLOB
, or TEXT
), or if the table was created with the ROW_FORMAT=DYNAMIC
table option.
Dynamic format is a little more complex than static format because each row has a header that indicates how long it is. A row can become fragmented (stored in non-contiguous pieces) when it is made longer as a result of an update.
You can use OPTIMIZE TABLE
or myisamchk -r 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.
Dynamic-format tables have these characteristics:
-
All string columns are dynamic except those with a length less than four.
-
Each row 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 row uses only as much space as is required. However, if a row becomes larger, it is split into as many pieces as are required, resulting in row fragmentation. For example, if you update a row with information that extends the row length, the row becomes fragmented. In this case, you may have to run
OPTIMIZE TABLE
ormyisamchk -r from time to time to improve performance. Usemyisamchk -ei to obtain table statistics. -
More difficult than static-format tables to reconstruct after a crash, because rows may be fragmented into many pieces and links (fragments) may be missing.
-
The expected row length for dynamic-sized rows 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) / 8There is a penalty of 6 bytes for each link. A dynamic row is linked whenever an update causes an enlargement of the row. Each new link is at least 20 bytes, so the next enlargement probably goes in the same link. If not, another link is created. You can find the number of links using myisamchk -ed. All links may be removed with
OPTIMIZE TABLE
or myisamchk -r.
Compressed storage format is a read-only format that is generated with the myisampack tool. Compressed tables can be uncompressed withmyisamchk.
Compressed tables have the following characteristics:
-
Compressed tables take very little disk space. This minimizes disk usage, which is helpful when using slow disks (such as CD-ROMs).
-
Each row is compressed separately, so there is very little access overhead. The header for a row takes up one to three bytes depending on the biggest row 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 aTINYINT
column (one byte) if all its values are in the range from-128
to127
. -
If a column has only a small set of possible values, the data type is converted to
ENUM
. -
A column may use any combination of the preceding compression types.
-
-
Can be used for fixed-length or dynamic-length rows.