MySQL storage engine summary _ MySQL

This article mainly introduces the summary of the MySQL Storage Engine. This article describes the storage engine, MyISAM, InnoDB, MEMORY, MERGE, and other content. For more information, see Preface

A table is stored in a database that is closely related to each other. Therefore, the design of a table directly affects the entire database. When designing a table, we will focus on the storage engine used. Wait, storage engine? What is a storage engine?

What is a 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, similar to a workbook in Excel. Some tables are simple, some tables are complex, and some tables do not need to store any long-term data at all. some tables read very quickly, but are poorly inserted; in the actual development process, we may need a variety of tables. different tables mean that different types of data are stored and data processing is also different. For MySQL, it provides many types of storage engines. we can select different storage engines based on the data processing requirements to maximize the use of MySQL's powerful functions. This blog post will summarize and analyze the characteristics of each engine, as well as its applicability, and will not be entangled in deeper things. My learning method is to learn how to use it first, know how to use it, and then know how to use it. The following describes the storage engines supported by MySQL.

MyISAM

On the mysql client, run the following command to view the engines supported by MySQL.

The code is as follows:

Show engines;

MyISAM tables are independent of the operating system, which means that they can be easily transplanted from a Windows server to a Linux server. whenever we create a MyISAM engine table, three files will be created on the local disk. the file name indicates. For example, if I have created a tb_Demo table of the MyISAM engine, the following three files will be generated:

1. tb_demo.frm, storage table definition;
2. tb_demo.MYD: store data;
3. tb_demo.MYI, which stores indexes.

MyISAM tables cannot process transactions, which means tables with transaction processing requirements cannot use the MyISAM storage engine. The MyISAM storage engine is particularly suitable for the following scenarios:

1. select an intensive table. The MyISAM storage engine is very rapid in filtering large amounts of data, which is its most prominent advantage.
2. Insert intensive tables. The concurrent insertion feature of MyISAM allows you to select and insert data at the same time. For example, the MyISAM storage engine is suitable for managing mail or Web server log data.

InnoDB

InnoDB is a robust transactional storage engine, which has been used by many Internet companies and provides a powerful solution for users to operate very large data storage. MySQL 5.6.13 installed on my computer, InnoDB is used as the default storage engine. InnoDB also introduces row-level locking and foreign key constraints. InnoDB is the best choice in the following scenarios:

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. Unlike 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 reading frequency.

MEMORY

The starting point of using the MySQL Memory storage engine is speed. To get the fastest response time, the logical storage medium used is the system memory. Although storing table data in the Memory does provide high performance, when the mysqld daemon crashes, all Memory data will be lost. The acquisition speed also brings some defects. 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 it can be used because it is used as a CHAR type with fixed length in MySQL.

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 service.

Memory supports both hash indexes and B-tree indexes. B-tree indexes are better than hash indexes. partial queries and configuration queries can be used, and 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. Therefore, hash index values are suitable for operators of = and <>, not suitable <或> It is also not suitable for use in the order by clause.

You can use the USING clause to specify the version to use when creating a table. For example:

The code is as follows:

Create table users
(
Id smallint unsigned not null auto_increment,
Username varchar (15) not null,
Pwd varchar (15) not null,
Index using hash (username ),
Primary key (id)
) Engine = memory;

The code above creates a table and uses the HASH index on the username field. The following code creates a table and uses the BTREE index.

The code is as follows:

Create table users
(
Id smallint unsigned not null auto_increment,
Username varchar (15) not null,
Pwd varchar (15) not null,
Index using btree (username ),
Primary key (id)
) Engine = memory;

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. To put it bluntly, the Merge table is the aggregator of several identical MyISAM tables. the Merge table does not have data, and you can query, update, or 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, and each name is related to a specific time end. For example, you can use 12 identical tables to store server log data. Each table is named after each month. When it is necessary to generate reports based on the data of all 12 log tables, this means that you need to write and update multi-table queries to reflect the information in these tables. Instead of writing these queries that may cause errors, you can combine these tables to use a query and then delete the Merge table without affecting the original data, deleting a Merge table only deletes the definition of a Merge table, which has no impact on the internal table.

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 MySQL 5.5 and later 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.

Storage engine problems

1. how can I check which storage engines can be used on the server?
To determine which storage engines your MySQL server can use, run the following command:

The code is as follows:

Show engines;

This command can be done.

2. how to select an appropriate storage engine?
(1) selection criteria can be divided:
(2) whether to support transactions;
(3) whether hot backup is required;
(4) crash recovery: whether the crash can be accepted;
(5) whether foreign key support is required;
Then select the corresponding storage engine according to the standard.

Summary

This article summarizes several commonly used storage engines. it is the best learning method to apply the actual work according to the specific situation and the actual project instances.