Overview of MySQL table engines

The purpose of this article is to give a brief, very concise overview of the engines, so that the article can be used when choosing the engine at the design stage / creating / optimizing the table. It is assumed that the reader knows the essence of the question at least superficially and is able to find all the additional information himself (questions in the comments can always be asked :))

MyISAM

• no transaction
• Max. Disk: 256TB
• lock table
• full text search
• work in a cluster: no
• maintaining integrity, foreign keys: no
• replication: yes
• Max. indices: 64
• Max. records: 2 ^ 32
• Max. key length: 1000 bytes
• keys take up disk space up to (max.): (key_length + 4) /0.67
• sensitive to the "fall" of the server, it is difficult to recover
• in the absence of “holes” (gaps) - inserts are not competitive (no locks occur)
• it is possible to store data and index files on different devices
• each column can have its own encoding
• Max. the sum of the lengths of VARCHAR and CHAR: 64k

Static (Fixed-length) table format

• automatically if there are no VARCHAR, VARBINARY, BLOB, TEXT columns
• faster, safer (more stable), better cached, requires more disk space
• if forced, VARCHAR and CHAR are filled with spaces, VARBINARY is filled with zeros

Dynamic length table format

• all lines up to 4 long - VARCHAR
• empty lines and zero (0) do not occupy disk space (NULL is not zero)
• Record (string) is fragmented automatically upon updates (OPTIMIZE TABLE needs to be run to defragment)
• more difficult to recover from failures

Compressed

• created by myisampack
• read-only
• recommended for very slow media
• it can be fixed-length and dynamic-length
• look towards the Archive table engine

Tips:

• they say that MyISAM tables are sure to “break” sooner or later, so be prepared;)
• do not kill the server while recording
• do not change tables by multiple servers at the same time
• do not change the tables by the utility and the server at the same time

Recommendations: directories

InnoDB

• Max. disk: 64tb
• full transaction support (4 isolation levels)
• record lock (not table), two types of locks (SHARED, EXCLUSIVE)
• full text index: no
• transaction safe
• indexes are clustered for “typical queries”
• integrity support (foreign keys)
• can be used on OS with limited file size
• many settings for optimization
• allows you to use Raw Disk for tables to bypass the file system
• AUTOCOMMIT is enabled by default (SET autocommit = 1)
• automatically detects deadlocks

The engine was designed specifically for large tables. The developers claim that InnoDB is the fastest of all known disk-based database engines (multiple tests confirm this)

Tips:

• SELECT (*) FROM table is much slower than MyISAM - create triggers if needed
• backup simple file copying is impossible
• mysqldump is slow, use InnodDb Hot Backup for backups
• watch indexes, InnoDB gain is lost if there are no indexes for queries

Recommendations: high-load sites, financial transactions
')

Merge

• Used to join identical tables into one
• tables must have identical structure
• column order must match
• DROP does not delete source tables.
• tables may be in another database
• can be used for aliases (for one table)
• cannot use FULLTEXT search
• do not mix temporary and non-temporary tables
• slow reading by key
• REPLACE not working
• changes in the structure of the source tables are not tracked (the table will be broken)

Recommendations: “convenient” (re) table organization

HEAP (MEMORY)

• no transaction
• table lock
• replication: yes
• Max. key length: 500 bytes
• all data is lost when the server is stopped (the table itself remains)
• storage format: always fixed-length row
• memory is not released when deleting a record (used to insert new ones)

Tips:

large tables "swap" to disk and the gain is lost

Recommendations: Local calculations, time data

ARCHIVE

• Max. disk: no limit
• write lock
• does not work DELETE, REPLACE, UPDATE, ORDER BY, type BLOB
• INSERT is buffered and merged with a long delay.
• very slow select

Recommendations: logging operations (audit, statistical data, counters)

CSV

• stores tables in CSV format
• Allows you to edit tables with external applications.
• badly documented, there are open bugs

FEDERATED

It is a “transparent” connection to another server (not replication). There are many limitations, it is planned to be able to connect to servers other than MySQL.

BLACKHOLE

• the data goes "nowhere"
• binary logs are written

Recommendations: replication optimization (master server does not write data to disk)

Trends, MySQL 6.0

• Maria - Improved MyISAM
• Falcon - "improved InnoDb", improved performance for Web servers

References:

MySQL Documentation, Chapter 13. Storage Engines
MySQL Storage Engines by Mike Peters