1.6 MySQL Storage Engines

1. What Is a Storage Engine

• The component in MySQL responsible for storage-related work.
• Used to process SQL operations and interact with the underlying file system.

2. Storage Engine Types

• View all storage engines

  show engines

  +--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+-----+------------+
  | Engine             | Support | Comment                                                                                         | Transactions | XA  | Savepoints |
  +--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+-----+------------+
  | CSV                | YES     | Stores tables as CSV files                                                                      | NO           | NO  | NO         |
  | MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                                           | NO           | NO  | NO         |
  | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                                       | NO           | NO  | NO         |
  | Aria               | YES     | Crash-safe tables with MyISAM heritage. Used for internal temporary tables and privilege tables | NO           | NO  | NO         |
  | MyISAM             | YES     | Non-transactional engine with good performance and small data footprint                         | NO           | NO  | NO         |
  | SEQUENCE           | YES     | Generated tables filled with sequential values                                                  | YES          | NO  | YES        |
  | InnoDB             | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables                | YES          | YES | YES        |
  | PERFORMANCE_SCHEMA | YES     | Performance Schema                                                                              | NO           | NO  | NO         |
  +--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+-----+------------+

• Default storage engine

  show variables like '%storage_engine%'

  | Variable_name              | Value  |
  +----------------------------+--------+
  | default_storage_engine     | InnoDB |
  | default_tmp_storage_engine |        |
  | enforce_storage_engine     |        |
  | storage_engine             | InnoDB |
  +----------------------------+--------+

2.1. InnoDB

• MySQL InnoDB.md

2.2. MyISAM

2.3. Memory

• The primary-key ID uses a Hash index and can be changed to a B+ tree index.
• Data is stored in memory and is lost after a crash.
• The lock granularity used is table-level.

3. InnoDB VS MyISAM

	MyISAM	InnoDB
Lock granularity	Table lock	Table lock + row lock
Transactions	Not supported	Supported
MVCC	Not supported	Supported
Clustered/non-clustered index (InnoDB and MyISAM Index Comparison.md)	All indexes are non-clustered indexes	The primary key is a clustered index
Primary-key index data structure	Leaf-node data stores pointers to data	Leaf-node data stores the data
Disk files (MySQL File System.md)	Table structure + data + indexes	Table structure + indexes (including data)
Record storage order	Stored in record insertion order	Inserted in order by primary-key value
Foreign keys	Not supported	Supported
Hash indexes	Not supported	Supported
Full-text indexes	Supported	Supported (MySQL 5.6+)
select count(*)	Faster because MyISAM internally maintains a counter	Relatively slower

4. References

• MySQL :: MySQL 8.0 Reference Manual :: 16 Alternative Storage Engines
• What Does “Engine” Mean in a Database Storage Engine? - Zhihu