- MySQL storage engine is a separate layer from MySQL server.
- Each table can use different storage engine.
- However, MySQL server manages table definition, and its name is dependent on platform: Windows allows case-insensitive name, and Linux is case-sensitive.
We’ll look through a few popular engines:
- One of the oldest storage engine of MySQL
- Has separate data and index file. The max size is the max file size allowed by OS.
- Supports advance index features: GIS, full-text index
- Is non-transactional
- Has auto/manual repair
- Has fast insert/read queries; however, modify queries lock entire table, blocks all read queries.
- Has delay key-write to improve performance when writing index
- Has compress mode for CD-ROM and DVD-ROM: has small size and fast read due to low disk I/O
MyISAM Merge Engine
- Merges multiple MyISAM tables to a virtual table
- Usually uses in datawharehousing
- One of the most popular engines
- Is built for short-live transactions
- Is transactional, and implements 4 MySQL isolation levels.
- Uses MVCC for high concurrency
- Default isolation level is Repeatable Read, which uses next-key locking to prevent phantom read.
- Has multiple data files, which separate table data and indexes.
- Has clustered indexes
- Fast primary key lookup
- Secondary indexes also has primary key, so indexes for table with a lot of records can be very big and slow.
- Has slower table rebuild time compared to MyISAM
- Implements Foreign key constraint, which MySQL does not implement.
- Isn’t persisted to disk
- Is faster than MyISAM
- Supports Hash index
- Supports only fix-size row
- Uses table-lock for each read/write, so it has low concurrency
- Is usually used for cache, lookup query and intermediate result
- Supports only INSERT and SELECT queries
- Has much less disk I/O than MyISAM because it buffers data writes and compresses row with zlib when inserting
- Each SELECT query requires full table scan
- Is usually used for logging and data acquisition
- Supports row-level locking and special buffer system for high-concurrency insert
- Is non-transactional
- Can treat csv file as tables
- Lets user copy file in/out when server is running
- Is usually used for data-interchange
- Does not store data locally, but uses a table on a remote MySQL server instead
- Has very slow aggregate queries, joins or other basic operations
- Is only useful for single-row lookup or INSERT queries on a remote server
- Has no storage mechanism
- Discards all INSERT queries, but keep the logs as usual
- Is useful for replication setups and audit logging
NDB Cluster Engine
- Is designed for high performance with redundancy and load-balancing capabilities
- Keeps all data in memory and is optimized for primary key lookups
- Has share-nothing infrastructure, consists of data nodes, management nodes and SQL nodes (MySQL instances)
- Each data nodes is a shard of the cluster’s data, which is replicated to multiple nodes
- Performs joins at MySQL server level, and can be extremely slow due to network latency. On another hand, single-table lookups can be very fast.
- Is very large and complex
- Is usually unsuitable for traditional applications
- Is transactional
- Uses MVCC and supports both pessimistic and optimistic concurrency control
- Has full foreign key supports
- Uses transaction logs and data files to avoid write-ahead logging, which reduces overhead
- Uses MVCC
- Supports foreign key constraint
- Does not use clustered index
- Has BLOB streaming: when combining with BLOB engine, it can stream binary and media file directly in and out of the database
- Now is called Aria in MariaDB
- Replaces MyISAM as default engine of MariaDB
- Uses Aria table format instead of MyISAM, which speeds up some GROUP BY and DISTINCT queries
Selecting right Engine
You should take into account a few factors:
- Crash recovery
- Special features: foreign keys, joins, indexes