Rechercher dans le manuel MySQL
16.2 The MyISAM Storage Engine
[+/-]
MyISAM
is based on the older (and no longer
available) ISAM
storage engine but has many
useful extensions.
Table 16.2 MyISAM Storage Engine Features
Feature | Support |
---|---|
B-tree indexes | Yes |
Backup/point-in-time recovery (Implemented in the server, rather than in the storage engine.) | Yes |
Cluster database support | No |
Clustered indexes | No |
Compressed data | Yes (Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only.) |
Data caches | No |
Encrypted data (Implemented in the server via encryption functions. Data-at-rest tablespace encryption is available in MySQL 5.7 and later.) | Yes |
Foreign key support | No |
Full-text search indexes | Yes |
Geospatial data type support | Yes |
Geospatial indexing support | Yes |
Hash indexes | No |
Index caches | Yes |
Locking granularity | Table |
MVCC | No |
Replication support (Implemented in the server, rather than in the storage engine.) | Yes |
Storage limits | 256TB |
T-tree indexes | No |
Transactions | No |
Update statistics for data dictionary | Yes |
Each MyISAM
table is stored on disk in two files.
The files have names that begin with the table name and have an
extension to indicate the file type. The data file has an
.MYD
(MYData
) extension. The
index file has an .MYI
(MYIndex
) extension. The table definition is
stored in the MySQL data dictionary.
To specify explicitly that you want a MyISAM
table, indicate that with an ENGINE
table option:
In MySQL 8.0, it is normally necessary to use
ENGINE
to specify the MyISAM
storage engine because InnoDB
is the default
engine.
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 4.6.4, “myisamchk — MyISAM Table-Maintenance Utility”, and
Section 4.6.6, “myisampack — Generate Compressed, Read-Only MyISAM Tables”.
In MySQL 8.0, the MyISAM
storage
engine provides no partitioning support. Partitioned
MyISAM
tables created in previous versions of
MySQL cannot be used in MySQL 8.0. For more
information, see
Section 23.6.2, “Partitioning Limitations Relating to Storage Engines”. For help
with upgrading such tables so that they can be used in MySQL
8.0, see
Section 2.11.1.3, “Changes in MySQL 8.0”.
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 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 (232)2 (1.844E+19) rows in a
MyISAM
table.The maximum number of indexes per
MyISAM
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 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.
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. See Section 8.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 the
DATA DIRECTORY
andINDEX DIRECTORY
table options toCREATE TABLE
. See Section 13.1.20, “CREATE TABLE Syntax”.NULL
values are permitted in indexed columns. This takes 0 to 1 bytes per key.Each character column can have a different character set. See Chapter 10, Character Sets, Collations, Unicode.
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-options
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:
Additional Resources
A forum dedicated to the
MyISAM
storage engine is available at https://forums.mysql.com/list.php?21.
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-myisam-storage-engine.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.