• The storage engine is a MySQL component that handles SQL operations of different table types. InnoDB is the default and most common storage engine. (the CREATE TABLE statement in MySQL 8.0 creates InnoDB tables by default.)
• To determine which storage engines your server supports, use the SHOW ENGINES statement. The value in the Support column indicates whether the engine can be used.

mysql> SHOW ENGINES\G
*************************** 1. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 3. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
...

• The list of storage engines supported by MySQL 8.0 is as follows

  • InnoDB: the default storage engine in MySQL 8.0. InnoDB is a transaction secure (ACID compliant) storage engine for MySQL. It has the functions of commit, rollback and crash recovery to protect user data. InnoDB row level locking (not upgraded to coarser grained locking) and Oracle style consistent unlocked reading improve multi-user concurrency and performance. InnoDB stores user data in a clustered index to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential integrity constraints.
  • MyISAM: the table takes up very little space. Table level locking limits the performance of read/write workloads, so it is usually used for read-only or read dominated workloads in Web and data warehouse configurations.
  • Example: store all data in RAM for quick access in an environment where you need to quickly find non critical data. This engine was formerly known as the HEAP engine. Its usage scenarios are decreasing; InnoDB and its buffer pool memory area provide a common and persistent way to save most or all data in memory, while NDBCLUSTER (NDB) provides fast key value lookup for large distributed data sets.
  • CSV: is actually a text file with comma separated values. The CSV table allows you to import or dump data in CSV format to exchange data with scripts and applications that read and write in the same format. Since the CSV table has no index, the data is usually kept in the InnoDB table during normal operation, and the CSV table is used only during the import or export phase.
  • Archive: a compact, unindexed table designed to store and retrieve large amounts of rarely referenced historical, archival, or security audit information.
  • Blackhole: accepts but does not store data, similar to Unix's / dev/null device. Queries always return an empty set. These tables can be used for replication configurations where DML statements are sent to the replica server, but the source server does not keep its own copy of the data.
  • NDB (also known as NDBCLUSTER): This clustered database engine is especially suitable for applications that require the highest possible uptime and availability.
  • Merge: it is convenient for MySQL DBA s or developers to logically group a series of the same MyISAM tables and reference them as an object. Applicable to VLDB environment, such as data warehouse.
  • Federated: provides the ability to link a separate MySQL server and create a logical database from many physical servers. Ideal for distributed or data mart environments.
  • Example: it is an example in MySQL source code, which shows how to start writing a new storage engine. It is mainly of interest to developers. A storage engine is a "stub" that does nothing. You cannot create or retrieve any data in this table using this engine.