What is the storage engine and the characteristics of different storage engines?

What is the storage engine and the characteristics of different storage engines?

I used to play with Oracle databases all day round the business needs and execution plans. I just got in touch with Mysql and saw that the storage engine was not understanding it, so there would be people like me, so I wrote a text to remember it.

　　

First of all, I simply want to deal with the disk. The actual situation is also true. A database system can switch between different storage engines at will. That is to say, the method of writing a disk or operating a disk is quite cool. So let's take a look at the Mysql architecture.

The MySQL server adopts a multi-layer design and independent module. The plug-in Storage engine architecture allows the Storage engine to be loaded into the new MySQL server, as shown in the Pluggable Storage Engines section. The MySQL server architecture is used. Because Pluggable Storage Engines provides consistent and simple application models and APIs at the Storage level, application programmers and DBAs can no longer consider all the underlying implementation details. Therefore, although different storage engines have different capabilities, applications are separated from each other. The storage engine is responsible for dealing with file systems.

The positioning and functions of the storage engine should be basically understood here. The following question is: is there any need. It is necessary because the content listed below is handled by the storage engine:

• Concurrency: Some applications have a lot of granular locking requirements (such as row-level locking) than other applications ).
• Transaction support: not all applications require transactions, but for applications that do need transactions, they have well-defined requirements, such as ACID compatibility.
• Integrity of reference: the server must maintain the integrity of the reference of the associated database through the foreign key defined by DDL.
• Physical storage: it includes a variety of items, from the total page size of tables and indexes, to the format required to store data, to the physical disk.
• Indexing support: different applications tend to adopt different indexing policies. Each storage engine usually has its own indexing method, but some indexing methods (such as B-tree indexes) it is common for almost all storage engines.
• Memory high-speed buffer: compared with other applications, different applications provide better responses to some memory high-speed buffer policies. Therefore, although some memory high-speed buffering is common to all storage engines (such as high-speed buffering for user connection and high-speed query caching for MySQL ), other high-speed buffer policies are only defined when special storage engines are used.
• Performance help: it includes multiple I/O threads for parallel operations, thread concurrency, database checkpoints, and batch insert processing.
• Other target features: may include support for geospatial operations and security restrictions on specific data processing operations.

The above requirements will be reflected in different requirements. It is impossible to implement it through a single system. Some of the above features are self-contradictory, and the problem of fish and bear's paw. Make some choices for the above content. The storage engine is a plug-in engine, which can be used for certain specific requirements. For example, some existing storage engines and basic features:

 

So far, we should have an intuitive impression on the storage engine. By the way, note that different storage engines can be specified for different tables in Mysql, that is, a Mysql server can use N different storage engines at the same time, or even write one by yourself.