Shulou(Shulou.com)06/01 Report--

This article mainly introduces the four commonly used storage engines of Mysql. The content of the article is carefully selected and edited by the author, which has a certain pertinence and is of great significance to everyone's reference. Let's learn about the four commonly used storage engines of Mysql with the author.

1. MyISAM

In the mysql client, use the following command to view the engines supported by MySQL.

Show engines

# vi my.cnf

[mysqld]

Default-storage-engine=InnoDB / / modify the default engine

The MyISAM table is independent of the operating system, which means that it can be easily migrated from the Windows CVM to the Linux CVM. Every time we create a MyISAM engine table, three files are created on the local disk, as indicated by the file name. For example, if I set up a tb_demo table for the MyISAM engine, the following three files are generated:

1.tb_demo.frm, storage table definition

2.tb_demo.MYD, storing data

3.tb_demo.MYI, store the index.

MyISAM tables cannot handle transactions, which means that tables with transaction requirements cannot use the MyISAM storage engine. The MyISAM storage engine is particularly suitable for the following situations:

1. Select a dense table. The MyISAM storage engine is very fast in filtering large amounts of data, which is its most prominent advantage.

two。 Insert intensive tables. MyISAM's concurrent insert feature allows you to select and insert data at the same time. For example, MyISAM storage engine is suitable for managing email or Web CVM log data.

II. 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, and 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.

III. MEMORY

The starting point for using the MySQL 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.

Memory supports both hash indexes and B-tree indexes. The advantage of B-tree index over hash index is that partial query and wildcard query can be used, and operators such as and > = can be used to facilitate data mining. The "equality comparison" of the hash index is very fast, but the "range comparison" is much slower, so the hash index value is suitable for use in the = and operator, not in the operator, nor in the order by clause.

You can use the USING clause to specify the version to use when the table is created. For example:

Create table user

(

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 above code creates a table that uses the HASH hash index on the username field. The following code creates a table using the BTREE index.

Create table user

(

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

IV. MERGE

The MERGE storage engine is a combination of MyISAM tables that must be identical in structure, and although their use is not as prominent as other engines, it is useful in some cases. To put it bluntly, the Merge table is the aggregator of several identical MyISAM tables; there is no data in the Merge table, and you can query, update, and delete tables of type Merge, which are actually operations on the internal MyISAM tables.

Usage scenario of the Merge storage engine.

For information such as CVM logs, a common storage strategy is to divide the data into many tables, and each name is related to a specific time end. For example, you can use 12 identical tables to store CVM log data, and each table is named after each month. When it is necessary to generate reports based on data from all 12 log tables, this means that multiple table queries need to be written and updated to reflect the information in those tables. Instead of writing these possible error queries, combine these tables and use a query, and then delete the Merge table without affecting the original data. Deleting the Merge table only removes the definition of the Merge table and has no effect on the internal table.

5. ARCHIVE

Archive means archiving. After archiving, many advanced functions are no longer supported, only the most basic insert and query functions are supported. Indexing was not supported in Archive before MySQL version 5.5, but it has been supported since MySQL version 5.5. Archive has a good compression mechanism. It uses the zlib compression library and compresses records in real time when requested, so it is often used as a repository.

Some problems with storage engine

1. How to check which storage engines are available for CVM?

To determine which storage engines are available for your MySQL CVM, execute the following command:

Show engines

This order can be done.

two。 How to choose the right storage engine?

(1) the selection criteria can be divided into:

(2) whether it is necessary to support transactions

(3) whether hot backup is required

(4) crash recovery: whether it is acceptable to collapse.

(5) whether foreign key support is required

According to the standard, just select the corresponding storage engine.

After reading the above about the four commonly used storage engines of Mysql, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

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.