What is a storage engine

In mysql, we use different techniques to store data in files (or memory), each using different storage mechanisms, indexing techniques, locking levels, and ultimately providing a wide range of different capabilities and capabilities. By choosing a different storage engine, you can gain additional speed or functionality to improve the overall functionality of your application. These different technologies and associated functions are called storage engines in MySQL. For example: <1> If you want to store all your table data in memory, you need to select a memory storage engine. <2> Or you may need a transaction-enabled database to ensure that the data can be rolled back if the transaction fails. Then you need to choose a data engine that supports transaction processing, such as InnoDB.Copy the code

MySQL has eight main storage engines

MySQL comes with a number of different storage engines by default, which can be set up in advance or enabled in MySQL server. You can choose a storage engine for servers, databases, and tables. The storage engine of the MySQL database can be optionally changed and replaced. InnoDB: supports row locking and transaction processing, which is slightly slower than MyISAM. ISAM: MyISAM merges multiple MyISAM tables into a single table. Falcon: a new storage engine that supports transaction processing. ARCHIVE: compressed data is saved (only INSERT/SELECT operations are supported). CSV: saves data in CSV format (used for cross-platform data exchange).Copy the code

Viewing storage Engines

To view the storage engine used by a TABLE, run the following command: SHOW CREATE TABLE TABLE name. As shown in the following diagram, the storage ENGINE shown behind ENGINE= is InnoDB, which supports transaction processing.Copy the code

Changing the Storage Engine

If you change the storage engine, you can think of changing the data structure of the table, so you use ALTER TABLE instead of UPDATE. ALTER TABLE ALTER TABLE ENGINE= new ENGINE; ALTER TABLE customer ENGINE=MyISAM;Copy the code