Mysql storage engine and mysql Storage

Mysql storage engine and mysql Storage
Storage engine Definition

The database storage engine is the underlying software organization of the database. The database management system (DBMS) creates, deletes, modifies, and queries data through the storage engine. Different storage engines provide different storage mechanisms, indexing techniques, locking levels, and other functions. Different storage engines can also obtain specific functions.

Supplement: The core of mysql is the storage engine.

 

View storage engine

Command:Show engines(My navicat)

 

InnoDB Storage Engine

The default storage engine after mysql5.5 is the preferred engine for transactional databases. Supports transaction security tables (ACID: atomicity, consistency, isolation, and persistence)

Features of InnoDB:

Summary: The advantage of InnoDB lies in its good transaction processing capability, crash recovery capability, and concurrency control. The disadvantage is that the read/write efficiency is poor (excluding concurrent writes) and the data space occupied is relatively large.

MyISAM storage engine

MyISAM is developed based on the ISAM engine and has a high insert and query speed, but does not support transactions.

MyISAM features:

Summary: The advantage of MyISAM is that it occupies a small amount of space and processes fast. The disadvantage is that it does not support transaction integrity and concurrency.

 

Memory storage engine

The memory storage engine Stores Table data in the memory to provide quick access to query and reference other table data.

Memory features:

Summary: MEMORY is rarely used because it stores data in the MEMORY. If the MEMORY encounters an exception, it will affect the data. If it is restarted or shut down, all data will disappear. Therefore, the lifecycle of a MEMORY-based table is short and generally one-time.

 

Function	InnoDB	MyISAN	Memory	Archive
Storage restrictions	64 TB	256 TB	RAM	None
Support transactions	YES	NO	NO	NO
Full-text index supported	YES	YES	NO	NO
Supports B-tree indexes.	YES	YES	YES	NO
Support hash Index	Adaptive hash Index	NO	YES	NO
Supports data caching	YES	NO	N/	NO
Supports Foreign keys	YES	NO	NO	NO

 

Summary: 

InnoDB is a good choice to provide ACID-compatible transaction security capabilities for submission, rollback, and crash recovery, and require concurrent control.

If a data table is mainly used to insert and query records, the MyISAM engine can provide high processing efficiency.

If you only store data temporarily, the data volume is small, and high data security is not required, you can choose to save the data in the Memory engine, MySQL uses this engine as a temporary table, store intermediate query results

If only INSERT and SELECT operations are available, you can SELECT Archive. Archive supports highly concurrent INSERT operations, but it is not transaction-safe. Archive is ideal for storing Archive data. Archive can be used to record log information.

Which engine needs flexible selection,Multiple tables in a database can use different engines to meet various performance and actual needs.Using a suitable storage engine will improve the performance of the entire database.