Movatterモバイル変換

18.2.1 MyISAM Startup Options

18.2.2 Space Needed for Keys

18.2.3 MyISAM Table Storage Formats

18.2.4 MyISAM Table Problems

Table 18.2 MyISAM Storage Engine Features

• 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 permit better index compression.

• Large files (up to 63-bit file length) are supported on file systems and operating systems that support large files.

• There is a limit of (2³²)² (1.844E+19) rows in aMyISAM table.

• The maximum number of indexes perMyISAM table is 64.

  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 anAUTO_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 oneAUTO_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 ormyisamchk.

• 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.

• MyISAM supports concurrent inserts: If a table has no free blocks in the middle of the data file, you canINSERT new rows into it at the same time that other threads are reading from the table. 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 10.11.3, “Concurrent Inserts”.

• You can put the data file and index file in different directories on different physical devices to get more speed with theDATA DIRECTORY andINDEX DIRECTORY table options toCREATE TABLE. SeeSection 15.1.20, “CREATE TABLE Statement”.

• BLOB andTEXT columns can be indexed.

• NULL values are permitted in indexed columns. This takes 0 to 1 bytes per key.

• Each character column can have a different character set. SeeChapter 12,Character Sets, Collations, Unicode.

• There is a flag in theMyISAM index file that indicates whether the table was closed correctly. Ifmysqld is started with themyisam_recover_options system variable set,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 packBLOB andVARCHAR columns.

• Support for a trueVARCHAR type; aVARCHAR column starts with a length stored in one or two bytes.

• Tables withVARCHAR columns may have fixed or dynamic row length.

• The sum of the lengths of theVARCHAR andCHAR columns in a table may be up to 64KB.

• Arbitrary lengthUNIQUE constraints.

• A forum dedicated to theMyISAM storage engine is available athttps://forums.mysql.com/list.php?21.