MyISAM Storage Formats
Contents
Lo Storage Engine MyISAM supporta tre differenti formati di storage.
Questi sono FIXED, DYNAMIC e COMPRESSED, e possono essere impostati tramite l'opzione ROW FORMAT nell'istruzione CREATE TABLE, o possono essere scelti automaticamente a seconda dei tipi delle colonne.
Fixed-length
Fixed-length (or static) tables contain records of a fixed-length. Each column is the same length for all records, regardless of the actual contents. It is the default format if a table has no BLOB, TEXT, VARCHAR or VARBINARY fields, and no ROW FORMAT is provided. You can also specify a fixed table with ROW_FORMAT=FIXED in the table definition.
Tables containing BLOB or TEXT fields cannot be FIXED, as by design these are both dynamic fields.
Fixed-length tables have a number of characteristics
- fast, since MariaDB will always know where a record begins
- easy to cache
- take up more space than dynamic or compressed tables, as the maximum amount of storage space will be allocated to each record.
- reconstructing after a crash is uncomplicated due to the fixed positions
- no fragmentation or need to re-organize, unless records have been deleted and you want to free the space up.
Dynamic
Dynamic tables contain records of a variable length. It is the default format if a table has any BLOB, TEXT, VARCHAR or VARBINARY fields, and no ROW FORMAT is provided. You can also specify a DYNAMIC table with ROW_FORMAT=DYNAMIC in the table definition.
Dynamic tables have a number of characteristics
- Each row contains a header indicating the length of the row.
- Rows tend to become fragmented easily. UPDATING a record to be longer will likely ensure it is stored in different places on the disk.
- All string columns with a length of four or more are dynamic.
- They require much less space than fixed-length tables.
- Restoring after a crash is more complicated than with FIXED tables.
Compressed
Compressed tables are a read-only format, created with the myisampack tool. myisamchk is used to uncompress them.
Compressed tables have a number of characteristics:
- while the data is read-only, DDL statements such as DROP TABLE and TRUNCATE TABLE will still function.
- take much less space than fixed or dynamic tables.
- rows are compressed separately, reducing access overhead.
- row headers will be from one to three bytes.
- rows can be compressed with different compression types, including
- prefix space compression
- suffix space compression
- columns with small sets of values are converted to ENUM
- numeric zeros are stored with only one bit
- integer columns will be reduced to the smallest int type that can hold the contents