mysql虚拟临时表是myisam引擎吗_MyIsam 引擎是否一定要转化为INNOdb

Yes Cluster database support No

Replication support[c] Yes Foreign key support No Backup / point-in-time recovery[d] Yes

Query cache support Yes Update statistics for data dictionary Yes

[a] Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only.

Implemented in the server (via encryption functions), rather than in the storage engine.

[c] Implemented in the server, rather than in the storage engine

[d] Implemented in the server, rather than in the storage engine

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 an ENGINE table option:

CREATE TABLE t (i INT) ENGINE = MYISAM;

Normally, it is unnecessary to use ENGINE to specify the MyISAM storage engine. MyISAM is the default engine unless the default has been changed. To ensure that MyISAM is used in situations where the default might have been changed, include the ENGINE option explicitly.

You can check or repair MyISAM tables with the mysqlcheck client or myisamchk utility. You can also compress MyISAM tables with myisampack to take up much less space. See Section 4.5.3, “mysqlcheck — A Table Maintenance Program”, Section 6.4.1, “Using myisamchk for Crash Recovery”, and Section 4.6.5, “myisampack — Generate Compressed, Read-Only MyISAM 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 file systems and operating systems that support large files.

There is a limit of 232 (~4.295E+09) rows in a MyISAM table. If you build MySQL with the --with-big-tables option, the row limitation is increased to (232)2 (1.844E+19) rows. See Section 2.10.2, “Typical configure Options”. Binary distributions for Unix and Linux are built with this option.

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 invoking configure with the --with-max-indexes=N option, where N is the maximum number of indexes to permit per MyISAM table. N must be less than 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 for INSERT and UPDATE operations. This makes AUTO_INCREMENT columns faster (at least 10%). Values at the top of the sequence are not reused after being deleted. (When an AUTO_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.) The AUTO_INCREMENT value can be reset with ALTER 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.

MyISAM supports concurrent inserts: 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. 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. See Section 7.3.3, “Concurrent Inserts”.

You can put the data file and index file in different directories on different physical devices to get more speed with the DATA DIRECTORY and INDEX DIRECTORY table options to CREATE TABLE. See Section 12.1.17, “CREATE TABLE Syntax”.

BLOB and TEXT 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 Section 9.1, “Character Set Support”.

There is a flag in the MyISAM index file that indicates whether the table was closed correctly. If mysqld 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 and VARCHAR columns.

MyISAM also supports the following features:

Support for a true VARCHAR type; a VARCHAR 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 and CHAR columns in a table may be up to 64KB.

Arbitrary length UNIQUE constraints.

Additional resources

A forum dedicated to the MyISAM storage engine is available at http://forums.mysql.com/list.php?21.

Previous / Next / Up / Table of Contents

User Comments

Posted by Dirck Hecking on December 23 2006 7:23am [Delete] [Edit]

I was performing a join between a table with 1,000,000 rows and a table with 11,000. I joined on an indexed key in both tables but found that it would take 20 seconds to complete. My production database instance was returning much faster than my local instance but I couldn't figure out the difference, processors and database setup was simiilar. Finally I realized that my tables on the local box were InnoDB table. The moment I altered the table type, things got much better :-) I love MyISAM.

Posted by Matt Lyons on August 1 2007 11:14am [Delete] [Edit]

I was getting confused as to why lots of select statments were blocking behind a long select on a MyIsam table until I realised that, as soon as an update blocks behind a select, every select that follows then blocks behind the update. See

http://nothing.tmtm.com/archives/2592

For applications with relatively few update statements a developer might assume that it is OK to use MyIsam because he doesn't mind those updates waiting for a long running select statment to finish as long as all the other reads are still happening. But should any updates occur during his long select, all subsequent reads will then block.

Add your own comment.

[本帖最后由 liyihongcug 于 2009-6-9 20:44 编辑]