First, INNODDB
The InnoDB table is built on a clustered index, and the default level is Repeatable_read (repeatable read), which supports hot backup, which is not supported by other MySQL engines.
Row-level locks are supported to handle a large number of short-term transactions. INNODDB performance and automatic crash recovery features, but it is also popular in the non-transactional storage requirements. Unless there is a very special reason to use a different storage engine, the InnoDB engine should be given priority.
Second, MyISAM
MyISAM provides a number of features, including full-text indexing, compression, spatial functions (GIS), and so on, but MyISAM does not support transactional and row-level locks and there is no doubt that it is not safe to recover after a crash, but it is not useless, for read-only data, or smaller tables, can tolerate repair operations , you can still continue to use MyISAM
Third, Archive
The archive storage engine supports only insert and select operations. However, every select query needs to perform a full table scan. So the archive table is suitable for log and data collection applications, which often require a full table scan when doing data analysis.
Iv. Memory
The memory storage engine is ideal for fast access to data, and this data is not modified, and restarts are lost later.
Usage scenarios:
Used for querying or mapping tables, columns such as: City tables, data dictionary tables
Results for caching of periodic aggregated data
Used to hold intermediate data generated in the data analysis
The above content is excerpt from "High-performance mSQL"
MySQL Storage engine Introduction