Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the classifications of MySQL database storage engine". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn what the classification of MySQL database storage engine is.

1. Brief introduction data in MySQL is stored in files (or memory) using a variety of different technologies. Each technology uses different storage mechanisms, indexing techniques, locking levels, and ultimately provides a wide range of different functions and capabilities.

By choosing different technologies, you can gain additional speed or functionality to improve the overall functionality of your application. For example, if you are studying large amounts of temporary data, you may only need to store the data in memory instead of in files (the memory storage engine can store all table data in memory). Or, you may need a database that supports transactions (to ensure the fallback ability of the data if the transaction is not successful).

These different technologies and associated functions are called storage engines (also known as table types) in MySQL.

two。 Classification of storage engines

MySQL is configured with many different storage engines by default, which can be pre-set or enabled in the MySQL server. We can choose different storage engines according to the needs of data processing, so as to maximize the power of MySQL.

Use related commands on the client to display all storage engines: show engines

Here's an introduction to common storage engines.

1) 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. InnoDB is the default storage engine. InnoDB also introduces row-level locking and foreign key constraints.

Using InnoDB is the ideal choice in the following situations:

1. Update dense tables. The InnoDB storage engine is particularly suitable for handling multiple concurrent update requests.

two。 Business. The InnoDB storage engine is the standard MySQL storage engine that supports transactions.

3. Automatic disaster recovery. Unlike other storage engines, InnoDB tables can automatically recover from disasters.

4. Foreign key constraint. The only storage engine that MySQL supports foreign keys is InnoDB.

5. Support the automatic addition of column AUTO_INCREMENT attributes.

In general, InnoDB is a good choice if transaction support is required and there is a high frequency of concurrent reads.

2) MyISAM

MyISAM does not support transactions and foreign keys, especially with fast access speed, no requirement for transaction integrity, or applications based on SELECT and INSERT can use this engine to create tables. Each MyISAM is stored as 3 files on disk, where the file name and table name are the same, but the extensions are:

.frm (storage table definition)

.MYD (MYData, storing data)

.MYI (MYIndex, store index)

3) MEMORY

The starting point for using the Memory storage engine is speed. In order to get the fastest response time, the logical storage medium used is system memory. Although storing table data in memory does provide high performance, when the mysqld daemon crashes, all Memory data is lost. The acquisition of speed also brings some defects. It requires that the data stored in the Memory data table is in a fixed-length format, which means that variable-length data types such as BLOB and TEXT cannot be used. VARCHAR is a variable-length type, but it can be used within MySQL as a fixed-length CHAR type.

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

1. The target data is small and is accessed very frequently. Store data in memory, so it will cause the use of memory. You can control the size of the Memory table through the parameter max_heap_table_size. By setting this parameter, you can limit the maximum size of the Memory table.

two。 If the data is temporary and requires it to be available immediately, it can be stored in a 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.

3. How to choose the right storage engine?

You can choose according to the following judgment

(1) whether transactions need to be supported

(2) whether hot backup is required

(3) crash recovery: whether it is acceptable to collapse

(4) whether foreign key support is required

Then, according to the standard, select the corresponding storage engine.

Thank you for reading, the above is the content of "what is the classification of MySQL database storage engine". After the study of this article, I believe you have a deeper understanding of what the classification of MySQL database storage engine has, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.