Mysql Storage Engine Details

Introduction to Storage Engine

Relational database tables are data structures used to store and organize information and can be understood as tables composed of rows and columns.
Because of the different types of tables, we may need a variety of tables in the actual development process. Different tables mean different types of data are stored and different data processing will also be different.

For Mysql, it provides many types of storage engines

The storage engine explains how to store data, how to index stored data, and how to update and query data.
Because data storage in relational databases is stored as tables, the storage engine can also be referred to as a table type (that is, the type of table that is stored and manipulated)

Storage Engine in Mysql

MyISAM

This engine was first provided by mysql and can be divided into static MyISAM, dynamic MyISAM and compressed MyISAM.

Static MyISAM:

If the length of each data column in the data table is fixed in advance, the server will automatically select this table type.
Because each record in the data table takes up the same amount of space, this type of table is very efficient to access and update.
Recovery is easier when data is damaged

Dynamic MyISAM:

If varchar, xxxtext, or xxxxBLOB fields appear in the data table, the server will automatically select this table type.
This table has less storage space than static MyISAM, but since the length of each record is not 1, data in the data table may be stored discretely in memory after multiple modifications, resulting in less efficient execution.
There may also be a lot of fragmentation in memory.
Therefore, this type of table is often defragmented using the optimize table command or optimization tool

Compress MyISAM:

Both types of tables mentioned above can be compressed using the myisamchk tool.
This type of table advancement reduces the storage footprint.
However, this table cannot be modified after compression.
In addition, because the data is compressed, the table is read with decompression performed first

However, no MyISAM table currently supports transaction, row-level locks, and foreign key constraints

MyISAM stands for operating system independent, which means it can be easily transferred from the windows server to the linux server.

Whenever we create a table for the MyISAM engine, three files are created on the local disk with the file name being the table name.

For example: I set up an th_for the MyISAM engineThe Demo table then produces the following three files:

1.th_demo.frm: Storage table definition;
2.th_demo.MYD: Store data;
3.th_demo.MYI: Storage index;

The MyISAM storage engine is particularly suited for the following situations:

1. By choosing dense tables, the MYISAM storage engine filters large amounts of data very quickly, which is its most prominent advantage
2. Insert dense tables, MYISAM's concurrent insertion feature allows data to be selected and inserted at the same time.For example, the MYISAM storage engine is ideal for managing mail or web server log data

InnoDB

The InnoDB table type can be seen as a step-by-step update to MyISAM, which provides transaction, row-level locking, and foreign key constraints capabilities

InnoDB is a robust transactional storage engine

Use scenarios:

1. Update dense tables.The InnoDB storage engine is particularly suited for handling multiple concurrent update requests
2. Transactions. The InnoDB storage engine is the standard mysql storage engine that supports transactions
3. Automatic disaster recovery.Unlike other storage engines, the InnoDB table automatically recovers from a disaster
4. Foreign key constraints.mysql foreign key supported storage engine InnoDB only
5. Support auto-adding column AUTO_INCREMENT attribute

1 Generally speaking, InnoDB is a good choice if you need transaction support and have a high concurrent read frequency

memory(heap)

The starting point for using the Mysql Memory storage engine is speed for the fastest response time
The logical storage medium used is system memory.
It requires that data stored in the Memory data table be in a constant-length format, which means that variable-length data types such as BLOB and Text cannot be used
VARCHAR is a variable-length type, but because it is treated as a fixed-length CHAR type inside Mysql, it can be used
Memory supports both hash and B tree indexes.B tree index is better than hash index:
You can use partial and wildcard queries, or you can use < , > and > =and other operators to facilitate data mining
Hash indexes perform an equal comparison very quickly, but range comparison is much slower
Hash index values are appropriate for use in = and < > Operator, not suitable < or > Operators are also not appropriate in the order by clause

The Memory storage engine is generally used in the following situations:

1. Target data is small and is accessed very frequently.
Stores data in memory, so memory usage can occur through the parameter max_heap_table_size controls the size of the memory table. Setting this parameter limits the maximum size of the Memory table.
2. If the data is temporary and requires immediate availability, it can be stored in a memory table
3. Sudden loss of data stored in the Memory table does not have a substantial negative impact on the application server

archive

archive means archiving. Many advanced functions are no longer supported after archiving, only the most basic insert and query functions are supported.
Prior to mysql5.5, Archive did not support indexing, but it has been supporting indexing since Mysql.
Archive has a good compression mechanism. It uses the zlib compression library, which compresses records in real time when requested, so it is often used as a repository
Commonly used for logging and aggregation analysis

MERGE

The MERGE storage engine is a combination of a set of MyISAM tables that must be structurally identical and, although not as usable as other engines, can be useful in some cases
The Merge table is an aggregator for several of the same MyISAM tables
There is no data in the Merge table. Query, update and delete operations can be performed on tables of type Merge
These operations actually operate on the internal MyISAM table

Scenarios for using the Merge storage engine:

For information such as server logs, a common storage strategy is to divide the data into tables, each name associated with a specific time-end.
Deleting the Merge table simply deletes the definition of the Merge table and has no effect on the internal table

Operation on Storage Engine in Mysql

1. View the storage engine your database can support

    show engines;

2. Several commands for viewing information such as table structure

desc tablename: View the structure of the data table
show create table tablename: Show table creation statement
show table status like'tablename'G: Displays the current state value of the table

3. Set or modify the storage engine for tables

create table tablename(
columnname (column name 1) type (data type) attri (property setting),
columnname (column name 1) type (data type) attri (property setting),
) engine= enginename: Set up storage engine when creating database tables

Alter table tablename engine = engineName: Modify the storage engine

That's all about the MySQL storage engine in this article. I hope it will be helpful for you to learn MySQL.