MySQL Storage engine

Storage Engine: Different storage engines can bring different functions and performance to the database.

View which storage engines are supported by the current database software
Mysql> show engines;

In MySQL 5.5 and later versions, the default storage engine is InnoDB, and before 5.5, the default is MyISAM engine

2. MyISAM engine
   The MyISAM storage engine focuses on speed, but does not support transactions, does not support foreign keys, and so on.
   Directory structure of the MyISAM storage engine

   . frm: The table structure is stored
   . MYD: Data stored in a table
   . MYI: The index of the table is stored
   

4. InnoDB Storage Engine: Supports transactions, supports row-level locks, supports foreign keys
   
   The storage engine will only hold the structure of the table in the database directory
   Table data is stored in the Ibdata1 file in the/data/mysql directory

   5.6 The inside index exists also in the database directory (. ibd)
   
   InnoDB support transactions: DML operations (INSERT, UPDATE, DELETE)
   Transactions meet four features: ACID
   1, a--atomicity Atomic nature
   A transaction is a whole, and all operations within it are either done or not, and cannot be split.
   2, C--consistency consistency
   The integrity constraints of the database are consistent before and after the transaction begins
   3, i--isolation isolation, Independence
   Two transactions are mutually irrelevant.
   4. D--durability Persistence
   After the transaction is complete, all changes to the database are persisted in the database and will not be rolled back.
   
   Normally: A transaction can be rolled back and committed.

   Flag for end of transaction:
   1, the explicit implementation of commit commits, indicating confirmation of changes
   2, explicitly perform rollback rollback, means to cancel all changes
   3, encountered DDL statement, automatic submission (Create, ALTER, DROP, truncate)
   4, normal exit, automatic submission
   5, abnormal exit, automatic rollback

6. Memory storage Engine: Data is stored in RAM, and after the service is restarted, data is lost and is typically used for temporary tables.
   

MySQL Storage engine