Aria Storage Formats
Contents
Lo Storage Engine Aria supporta tre tipi di formati di storage.
Questi sono FIXED, DYNAMIC e PAGE, ed è possibile impostarli con l'opzione ROW FORMAT di CREATE TABLE. PAGE è il formato predefinito, mentre FIXED e DYNAMIC sono essenzialmente uguali ai formati di MyISAM.
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 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.
Page
Page format is the default format for Aria tables, and is the only format that can be used if TRANSACTION=1.
Page tables have a number of characteristics:
- It's cached by the page cache, which gives better random performance as it uses fewer system calls.
- Does not fragment as easily easily as the DYNAMIC format during UPDATES. The maximum number of fragments are very low.
- Updates more quickly than dynamic tables.
- Has a slight storage overhead, mainly notable on very small rows
- Slower to perform a full table scan
- Slower if there are multiple duplicated keys, as Aria will first write a row, then keys, and only then check for duplicates
