Let me understand your MySQL storage engine

MySQL Architecture

adjoining course
At the top are some clients and link services , Mainly completes some similar to the connection processing 、 Authorized certification 、 And related safety programs . The server will also provide secure access for each client
The client verifies its operation permissions .
Service layer
The second layer architecture mainly completes most of the core service functions , Such as SQL Interface , And complete the cache query ,SQL Analysis and optimization of , Execution of some built-in functions . All cross storage
The function of storage engine is also realized in this layer , Such as The process 、 Functions, etc .
Engine layer
The storage engine is really responsible for MySQL The storage and extraction of data in , Server pass API Communicating with the storage engine . Different storage engines have different functions , So I
We can according to our own needs , To choose the right storage engine .
Storage layer
Mainly store the data on the file system , And complete the interaction with the storage engine .

Introduction to storage engine

The storage engine is to store data 、 Index 、 to update / Implementation of query data and other technologies . The storage engine is table based , Not library based , So the storage engine can also be
Called table type .

1. When the table is created , Specify the storage engine
   
2. View the storage engines supported by the current database

SHOW ENGINES;

Storage engine features

InnoDB

* Introduce
InnoDB It is a general storage engine with high reliability and high performance , stay MySQL 5.5 after ,InnoDB By default MySQL Storage engine .
* characteristic
DML Operation follows ACID Model , Support transactions ;
Row-level locks , Improve concurrent access performance ;
Support foreign keys FOREIGN KEY constraint , Ensure the integrity and correctness of data ;
* file
xxx.ibd：xxx Represents the table name ,innoDB Each table of the engine will correspond to such a tablespace file , The table structure that stores the table （frm、sdi）、 Data and index .
Parameters ：innodb_file_per_table

InnoDB

MyISAM

* Introduce
MyISAM yes MySQL Early default storage engine .
* characteristic
Unsupported transaction , Foreign key not supported
Support table lock , Row locks are not supported
Fast access
* file
xxx.sdi： Store table structure information
xxx.MYD: Store the data
xxx.MYI: Storage index

Memory

* Introduce
Memory The table data of the engine is stored in memory , Due to hardware problems 、 Or the impact of power failure , These tables can only be used as temporary tables or caches .
* characteristic
Memory storage
hash Indexes （ Default ）
* file
xxx.sdi： Store table structure information

difference

Storage engine selection

When choosing a storage engine , The appropriate storage engine should be selected according to the characteristics of the application system . For complex applications , You can also select a variety of storage engines to group according to the actual situation
close .
* InnoDB : yes Mysql The default storage engine for , Support transactions 、 Foreign keys . If the application has higher requirements for transaction integrity , Data consistency is required under concurrent conditions , Data operations in addition to inserts and queries , It also contains a lot of updates 、 Delete operation , that InnoDB The storage engine is a better choice .
* MyISAM ： If the application is based on read operation and insert operation , There are very few update and delete operations , And the integrity of the transaction 、 Concurrency requirements are not very high , So it's very appropriate to choose this storage engine .
* MEMORY： Keep all data in memory , Fast access , Usually used for temporary tables and caches .MEMORY The drawback is that there is a limit on the size of the table , Too large tables cannot be cached in memory , And can't guarantee the security of data .