MySQL Storage engine

The architecture of the MySQL database is as follows:

As you can see, MySQL is mainly divided into the following components:

• Connection Pool Components
• Manage services and tools components
• SQL Interface Components
• Parser component
• Optimizer components
• Buffer components
• Plug-in storage engine
• Physical files

first, the storage engine

　　Storage Engine : It is the method that specifies how the table stores the data , how to index the stored data, and how to update and query the data . Because the storage of data in a relational database is stored as a table, the storage engine can also be called a table type (that is, the type that stores and operates this table)

Understanding: There is only one storage engine in a database such as Oracle and SQL Server, and all data storage management mechanisms are the same. The MySQL database provides a variety of storage engines. Users can choose different storage engines for the data table according to different requirements, and users can write their own storage engine according to their own needs.

The following table shows the characteristics of the various storage engines:

MySQL storage engine comparison

features	MyISAM	InnoDB	Memory	Archive	NDB
Storage limits	No	64TB	Yes	No	No
Transaction		Support			Support
Lock particle size	Table	Yes	Table	Yes	Page
B-Tree Index	Support	Support	Support		Support
Hash index		Support	Support
Full-Text Indexing	Support
Cluster index		Support
Data caching		Support	Support
Index cache	Support	Support	Support
Data compression	Support			Support
BULK INSERT	High	Relatively low	High	Very high	High
Memory consumption	Low	High	In	Low	Low
FOREIGN key support		Support
Replication support	Support	Support	Support	Support	Support
Query cache	Support	Support	Support	Support	Support
Backup recovery	Support	Support	Support	Support	Support
Cluster support					Support

Two of the most common storage engines are MyISAM and InnoDB

MyISAM Storage Engine

1. MyISAM is the original default storage engine for MySQL (MySQL version 5.5).

2. MyISAM This storage engine does not support transactions, does not support row-level locks, and supports only table locks that are inserted concurrently.

3. MyISAM Type table supports three different storage structures: Static type, dynamic type, compression type.

(1) Static type: The size of the defined table column is fixed (that is, does not contain: Xblob, xtext, varchar and other variable length data types), so that MySQL will automatically use the static MyISAM format.

Tables that use a static format have a high performance because the overhead of storing data in a predetermined format during maintenance and access is low. But this high-performance is in exchange for space, because at the time of definition is fixed, so regardless of the value in the column, the maximum value will prevail, occupy the entire space.

(2) Dynamic type: If the column (even if only one column) is defined as dynamic (Xblob, xtext, varchar and other data types), then MyISAM automatically uses the dynamic type, although the dynamic table occupies less space than the static type table, but brings a performance decrease.

(3) Compression type: If you create a table in this database that is read-only throughout the life cycle, this is the case with MyISAM compressed tables to reduce space usage.

Compression method Reference Official document: Https://dev.mysql.com/doc/refman/5.6/en/myisampack.html

4, MyISAM also use B+tree Index but with InnoDB in concrete implementation of some different.

InnoDB Storage Engine

(1) MySQL default storage engine (after MySQL 5.5 version).

(2) InnoDB supports transaction, rollback and security of system crash repair capability and multi-version burst control.

(3) InnoDB support self-growing column (auto_increment), the value of the self-growth column cannot be empty, (a table allows only one self-increment and requires that the self-increment column must be indexed)

(4) InnoDB supports foreign keys (foreign key), the table in which the foreign key resides is called a child table, and the dependent table is called the parent table.

(5) The InnoDB storage engine supports row-level locks.

(6) InnoDB Storage Engine index is using B+tree

Add 3 points:

1. Large-capacity datasets tend to choose InnoDB. Because it supports transaction processing and failure recovery. InnoDB can use the data log for data recovery. The primary key query is also relatively fast in InnoDB.

2. When a large number of INSERT statements (here is an INSERT statement) are performed faster in the Myiasm engine, the UPDATE statement executes faster in InnoDB, especially when the concurrency is large.

3. What are the index data structures used by the two engines?

Answer: All the B + trees!

The Myiasm engine, the B + tree, stores content in the data structure, which is actually the address value of the actual data. That is, its index is separate from the actual data, but the index is used to point to the actual data. The pattern for this index is called a nonclustered index.

The data structure of the index of the InnoDB engine is also a B + tree, except that the data in the data structure is actually stored, and this index is called a clustered index.

Memory Storage Engine

The Memory storage engine (formerly called the heap) stores data in the table and, if the database restarts or crashes, data is lost, making it ideal for storing temporary data.

Archive Storage Engine

As its name implies, archive is ideal for storing archived data, such as log information. It supports only insert and select operations and is designed primarily to provide high-speed insertion and compression capabilities.

NDB Storage Engine

The NDB storage engine is a clustered storage engine, similar to Oracle's RAC, but it is a share nothing (unshared) architecture, thus providing a higher level of high availability and scalability. NDB is characterized by the fact that the data is all in memory, so it is very fast to find it through the primary key.

With regard to NDB, there is a problem to note that its connection (join) operation is done at the MySQL database layer, not at the storage engine level, which means that complex join operations require significant network overhead and query speed is slow.

Second,Testing the storage Engine

Create three tables, using the Innodb,myisam,memory storage engine, for insert data testing

CREATE TABLE t1 (id int) engine=innodb;create table t2 (id int) engine=myisam;create table t3 (id int) engine=memory;# Take a look at the table files created by the three storage engines t1.frm  t1.ibd   T2. MYD  T2. MYI  t2.frm   t3.frm# Careful classmate will find the last storage engine only table structure, no data #memory, after restarting MySQL or restarting the machine, the data in the table is emptied

Key [Face question]:

Differences between INNODB and MYIASM storage engines:
1.innodb is the default storage engine after the mysql5.5 version, and MyISAM is the default storage engine of the previous 5.5 version.
2.innodb supports things, and MyISAM doesn't support things
The 3.INNODB supports row-level locks. and myiasm it supports concurrent table-level locks.
4.INNODB supports foreign keys, while Myiasm does not support foreign keys
Both the 5.INNODB and Myiasm storage engines use B+tree to store data, but InnoDB's indexes and data are stored in a single file, which we call aggregate indexes.
Myiasm, in other words, creates an index file separately, that is, the data is separated from the index
6. MyISAM is higher in efficiency than InnoDB, but innodb better in terms of performance.

　

MySQL Storage engine