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

What are the storage engines in MySQL? I believe many inexperienced people don't know what to do about it. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

I. introduction

After mysql5, there are more than a dozen storage engines supported, but only a few are commonly used, and InnoDB is also supported by default. Since we want to make a comparison, we need to look at it from different dimensions.

We can use commands to see which storage engines the current database can support.

Here we find that 9 are supported by default. There are still a lot of them. Let's make a comparison.

Different storage engines have their own characteristics to meet different needs, as shown in the table. To make a choice, first consider what different functions each storage engine provides.

Here we enumerate some characteristics and make a comparison. Let's make a concrete analysis and comparison.

Second, storage engine

1 、 MyISAM

With this storage engine, each MyISAM is stored as three files on disk.

(1) frm file: stores the definition data of the table

(2) MYD file: store the data recorded in the table

(3) MYI files: store indexes

Frm and MYI can be stored in different directories. The MYI file is used to store the index, but only the pointer to the page where the record is located. The structure of the index is a B + tree structure. The following figure shows how MYI files are saved:

From this figure, you can see that the storage engine looks up the record page through the MYI's B+ tree structure, and then finds the record according to the record page. And support full-text index, B-tree index and data compression.

There are also three types of data that are supported:

(1) static fixed length table

The advantage of this approach is that the storage speed is very fast, caching is easy to occur, and the table is easy to repair when it is damaged. The disadvantage is that it takes up space. This is also the default storage format.

(2) dynamic variable length table

The advantage is that it saves space, but it is more troublesome to recover once something goes wrong.

(3) Compression table

It is mentioned above that data compression is supported, which means that this format must also be supported. In the event of an error in the data file, you can use the check table tool to check, and you can also use the repair table tool to recover.

There is an important feature that does not support transactions, but it also means that its storage speed is faster, if your read and write operations allow incorrect data, just the pursuit of speed, you can choose this storage engine.

2 、 InnoDB

InnoDB is the default database storage engine, and its main features are:

(1) you can automatically grow columns by using auto_increment.

(2) support transactions. The default transaction isolation level is repeatability, which is achieved through MVCC (concurrent versioning).

(3) the lock granularity used is row-level lock, which can support higher concurrency.

(4) support foreign key constraints; foreign key constraints actually reduce the query speed of tables, but increase the degree of coupling between tables.

(5) cooperate with some hot backup tools to support online hot backup.

(6) there is buffer management in InnoDB, which caches all indexes and data through the buffer pool to speed up the query.

(7) for the InnoDB type table, the physical organization of the data is the cluster table. All data is organized according to the primary key. The data and the index are put together, both on the leaf node of the B+ number.

Of course, there are two forms of storage tables and indexes for InnoDB:

(1) use shared tablespace storage: all tables and indexes are stored in the same tablespace.

(2) use multiple table spaces to store: the table structure is placed in the frm file, and the data and indexes are placed in the IBD file. For partition tables, each partition corresponds to a separate IBD file, and the definition of the partition table can be seen in my other articles. The advantage of using partition tables is to improve query efficiency.

For InnoDB, the major feature is that it supports transactions. But this is in exchange for the loss of efficiency.

3 、 Memory

The data is stored in memory, and in order to improve the speed of data access, each table is actually associated with a disk file. The file is frm.

(1) there are restrictions on the supported data types, for example, TEXT and BLOB types are not supported. For string data, only fixed-length rows are supported, and VARCHAR will be automatically stored as CHAR type.

(2) the supported lock granularity is table-level lock. Therefore, when the traffic is relatively large, table-level locking will become the bottleneck of the MEMORY storage engine.

(3) because the data is stored in memory, if the server fails, the data will be lost.

(4) when querying, if it is useful to a temporary table, and there are fields of type BLOB,TEXT in the temporary table, then the temporary table will be converted to a table of type MyISAM, and the performance will degrade sharply.

(5) hash index is used by default.

(6) if an internal table is large, it will be converted to a disk table.

Only three common storage engines are given here. Which engine to use requires flexible choice. Multiple tables in a database can use different engines to meet various performance and practical requirements. Using a suitable storage engine will improve the performance of the whole database.

After reading the above, have you mastered the methods of storage engine in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.