Mysql storage engine details

Mysql storage engine details

Introduction to the storage engine

A relational database table is a data structure used to store and organize information. It can be understood as a table 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 that different types of data are stored and data processing will also be different.

Mysql provides many types of storage engines.

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

Storage engine in Mysql

MyISAM

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

Static MyISAM:

If the length of each data column in the data table is pre-fixed, the server automatically selects this table type.
Because each record in a data table occupies the same space, the efficiency of table access and update is very high.
When data is damaged, recovery is easier.

Dynamic MyISAM:

If the varchar, xxxtext, or xxxxBLOB fields appear in the data table, the server automatically selects this table type.
Compared with static MyISAM, this table has a small storage space, but because the length of each record is different, after data is modified multiple times, the data in the data table may be stored discretely in the memory, this leads to a decrease in execution efficiency.
At the same time, many fragments may occur in the memory.
Therefore, this type of table often uses the optimize table command or optimization tool for fragment.

Compress MyISAM:

The two types of tables mentioned above can be compressed using myisamchk.
This type of table further reduces the storage used.
However, this type of table cannot be modified after compression.
In addition, because the data is compressed, such tables must be decompressed before reading.

However, no matter which MyISAM table is used, transactions, row-level locks, and foreign key constraints are not currently supported.

MyISAM indicates that it is independent from the operating system, which means it can be easily transferred from a windows server to a linux server;

Every time we create a MyISAM engine table, three files will be created on the local disk. The file name is the table name.

For example, if I create a th_Demo TABLE OF THE MyISAM engine, the following three files will be generated:

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 suitable for the following scenarios:

1. Select-intensive tables. The MYISAM storage engine is very fast in filtering large amounts of data, which is the most prominent advantage.
2. Insert-intensive tables. The concurrent insert feature of MYISAM allows you to select and insert data at the same time. For example, MYISAM storage engine is suitable for managing mail or web server log data.

InnoDB

The InnoDB table type can be seen as a product for further updating MyISAM. It provides the functions of transactions, row-level locks, and foreign key constraints.

InnoDB is a robust transactional storage engine.

Use Cases:

1. Update intensive tables. The InnoDB Storage engine is particularly suitable for processing multiple concurrent update requests.
2. transactions. The InnoDB Storage engine is a standard mysql storage engine that supports transactions.
3. Automatic disaster recovery. Different from other storage engines, InnoDB tables can be automatically recovered from disasters.
4. Foreign key constraints. Mysql only supports the InnoDB Storage engine.
5. The AUTO_INCREMENT attribute can be automatically added.

In general, InnoDB is a good choice if you need transaction support and a high concurrent read frequency.

Memory (heap)

The starting point of using the Mysql Memory storage engine is speed, in order to get the fastest response time
The logical storage medium used is the system memory.
It requires that the data stored in the Memory data table be in the same length format, which means that the variable-length data types such as BLOB and Text cannot be used.
VARCHAR is a variable-length type, but because it is used as a CHAR type with Fixed Length in Mysql, you can use
Memory supports both hash indexes and B-tree indexes. B-tree indexes are better than hash indexes:
Some queries and configuration queries can be used, or operators such as <,> and> = can be used to facilitate data mining.
It is very fast to perform "equal comparison" for hash indexes, but the speed for "range comparison" is much slower.
Hash index values are suitable for operators of = and <>. They are not suitable for <or> operators, and are also not suitable for order by clauses.

Memory storage engine is generally used in the following situations:

1. The target data is small and frequently accessed.
Data is stored in the memory, which may cause Memory usage. You can use the max_heap_table_size parameter to control the size of the memory table and set this parameter to limit the maximum size of the Memory table.
2. if the data is temporary and must be available immediately, it can be stored in the memory table.
3. If the data stored in the Memory table is suddenly lost, it will not have a substantial negative impact on the application server.

Archive

Archive means archiving. After archiving, many advanced functions are no longer supported. It only supports the most basic insert and query functions.
Before Mysql 5.5, Archive does not support indexes, but later Mysql versions start to support indexes.
Archive has a good compression mechanism. It uses zlib to compress records in real time when they are requested. Therefore, Archive is often used as a repository.
It is often used in log recording and aggregation analysis.

MERGE

The MERGE storage engine is a combination of MyISAM tables. These MYISAM tables must have the same structure. Although they are not as prominent as other engines, they are useful in some cases.
The Merge table is the aggregation of several identical MyISAM tables.
There is no data in the Merge table. You can query, update, and delete tables of the Merge type.
These operations are actually performed on the internal MyISAM table.

Use Cases of the Merge storage engine:

For server logs, a common storage policy is to divide data into many tables. Each name is related to a specific time end.
Deleting a Merge table only deletes the definition of a Merge table and has no impact on the internal table.

Operations on the storage engine in Mysql

1. View storage engines supported by the database

    show engines;

2. Several commands for viewing table structure and other information

Desc tablename: view the data table structure
Show create table tablename: displays the table creation statement.
Show table status like 'tablename' \ G: displays the current status value of the table.

3. Set or modify the storage engine of the table

Create table tablename (
Columnname (column name 1) type (data type) attri (attribute setting ),
Columnname (column name 1) type (data type) attri (attribute setting ),
) Engine = enginename: Set the storage engine when creating a database table

Alter table tablename engine = engineName: Modify the storage engine

The above is all about the MySQL storage engine in this article. I hope it will help you learn MySQL.