1: MySQL storage engine

1. What is a storage engine?

Simply understood as:The storage engine is the underlying logic for processing data. The underlying processing methods of different engines are different

Modern database can be divided into three layers. The top layer is used for connection, thread processing, etc; The middle tier provides the core functions of the database, including SQL Analysis, analysis, optimization, view, etc; The bottom layer is the storage engine of the database, which is responsible for data storage and extraction.

In short, the storage engine is a software module that provides the creation, query, update and storage of data for the database. The main difference between different storage engines is the storage mode of data. In addition, there are differences in functions, characteristics and speed.

The storage engine masks the details of the underlying storage for the database. Now many database management systems support multiple storage engines, which are configured through plug-in. Different storage engines can be selected according to specific scenarios.

2. View storage engine information

show engines;

2: MySQL supported storage engine

1. Storage engine

stay MySQL Medium execution SHOW ENGINES;，You can see the list of supported storage engines, which may vary in different environments

MySQL The storage engines of are mainly divided into two categories: transactional and non transactional“ Transactions"One column can be seen, only InnoDB The engine supports transactions.

stay MySQL 5.5 And later versions, the default storage engine is InnoDB，And before that MyISAM. 

3: innoDB storage engine

1. Characteristics

characteristic	Support	characteristic	Support
Multi version concurrency control (MVCC)	Yes	Locking granularity	Row level lock
Transactions	Yes	Foreign key support	Yes
Indexes	Yes	Hot backup / disaster recovery	Yes

2. Storage structure

In a table space, all data records are**Logically**Stored in a tablespace. The table space is further divided into segments( segment),District( extent),Page( page). Page is InnoDB Minimum disk unit for managing data, 16 each KB The size of the page can hold 2-200 Record of rows.

3. Advantages, disadvantages and applicable scenarios

1. Support transaction, row lock, foreign key, MVCC and other features
2. It has good concurrency and is suitable for scenes with intensive updates. This is because when updating data, InnoDB uses row locks with small granularity and less competition, which increases concurrent processing (table locks are used when inserting data)
3. Poor reading and writing efficiency. The main reason is that after index query, you need to perform secondary search according to the primary key
4. Large occupied space

InnoDB engine can be selected in most scenarios. InnoDB is also the default storage engine of MySQL.

4: MyISAM storage engine

MyISAM is based on the old ISAM storage engine and adds many useful extensions.

1. Characteristics

characteristic	Support	characteristic	Support
Indexes	Yes	Locking granularity	Table lock

2. Storage structure

differ InnoDB，MyISAM The data is**Sequential storage**of Indexed B+ Leaf node storage**Address of data record**，It can locate the data directly, so the search speed is very fast

3. Advantages, disadvantages and applicable scenarios

1. Small footprint
2. Fast processing speed, suitable for scenes with dense select ion. This is because the data can be directly located through the index node without secondary search
3. Support full-text indexing (InnoDB also supports)
4. Transaction not supported
5. Only table level locks are supported (for concurrent insertion)

It is suitable for scenarios that contain a large number of read-heavy operations and do not need transaction support. It is very efficient, such as data warehouse and Web application.

5: Memory storage engine

1. Introduction

characteristic	Support	characteristic	Support
Indexes	Yes	Locking granularity	Table lock

Memory The storage engine, as its name implies, stores data in the system memory, so it has fast reading and writing speed and high performance. however**Low security**，Data may be lost due to process crash or hardware restart.

Memory Each table in corresponds to one on disk `.frm` File, which only stores the table structure, and the data is stored in memory.

2. Applicable scenarios

• It needs fast reading and writing speed
• Database tables are relatively small (limited memory space)
• Low requirements for data security

The above is the relationship of "and". However, the Memory storage engine is rarely used.

6: Storage engine function summary

1.MyISAM

yes MySQL5.5 Default storage engine before version
 The engine accesses data very fast
 However, the security is low and does not support many additional functions

2.InnoDB

yes MySQL5.5 Default storage engine after version
 The engine supports transactions, row level locks and foreign keys
 There is no difference in the speed of accessing data MyISAM Fast but more functional and secure

affair : Ensure that multiple sql statements of data are executed at the same time
Row level lock : It is equivalent to a mutex lock, which can only be operated by one person at a time
Foreign key : Used to create the relationship between tables

3.memory

All data is stored in memory, which is fast, but it is lost immediately after power failure(Memory)

4.blackhole

Any data put into a black hole will disappear(Similar to garbage disposal station)

6: Create a table to specify the storage engine

1. Use the table to create the specified storage engine

Format:
create table t1(id int) engine = storage engine;

create table t2(id int) engine=MyISAM;
create table t3(id int) engine=InnoDB;
create table t4(id int) engine=memory;
create table t5(id int) engine=blackhole;

2. Compare the differences between storage engines

• Storage engine suffix

MyISAM
	Three documents
        .frm	Table structure
        .MYD	Table data
        .MYI	Table index
InnoDB
	Two files
        .frm	Table structure
        .ibd    Data and index
memory
	.frm	Table structure
blackhole
	.frm	Table structure

3. Demonstration of inserting data into storage engine

insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);
insert into t5 values(1);

4. Check whether memory is stored in memory. If shutdown data is lost, test again.

4.1 run open cmd as Administrator

4.2 restart mysql

net stop mysql
net start mysql

4.3 re open cmd and log in to MySQL (check whether memory data exists)

see memory Is the data memory
select * from t4

7: Comprehensive comparison

8: How to select a storage engine

1. Suggestions on storage engine selection

Each storage engine has different functions and limitations. In some cases, only a specific storage engine can be selected, such as transaction support, hot backup, crash recovery, foreign key support, cache, etc., or there can be no storage restrictions. In other cases, you can flexibly choose different storage engines:

• Different storage engines can be used for different tables in the same database. For example, MyISAM can be used for query intensive tables, and Memory can be used for query temporary tables
• Different storage engines can also be used for databases on different servers. For example, in the scenario of separation of reading and writing, MyISAM can be used from the database to improve the query speed