Mysql Storage Engine Summary _mysql

Objective

In the database is a sheet of inextricably linked to the table, so the table design is good or bad, will directly affect the entire database. When designing a table, we all focus on a problem and what storage engine to use. Wait a minute, storage engine? What is a storage engine?

What is a storage engine?

Relational database tables are data structures that are used to store and organize information, and you can interpret a table as a table of rows and columns, similar to the form of an Excel spreadsheet. Some tables are simple, some tables are complex, and some tables don't have to store any long-term data. Some tables read very fast, but when inserting data is very poor, and we in the actual development process, we may need a variety of tables, different tables, means that storage of different types of data, data processing there will be differences, then. For MySQL, it offers many types of storage engines, and we can choose different storage engines based on the needs of data processing to maximize MySQL's powerful capabilities. This blog post will summarize and analyze the characteristics of each engine, as well as the applicable occasions, and will not dwell on something deeper. My Learning method is to learn to use first, know how to use, and then to know how to use. The following is a brief introduction to the storage engine supported by MySQL.

MyISAM

In the MySQL client, use the following command to view the MySQL-supported engine.

Copy Code code as follows:

Show engines;

The MyISAM table is independent of the operating system, which means it can be easily ported from a Windows Server to a Linux server, and every time we create a table for the MyISAM engine, we create three files on the local disk, which is what the filename indicates. For example, I set up a Tb_demo table for the MyISAM engine, and then the following three files are generated:

1.TB_DEMO.FRM, storage table definition;
2.tb_demo. MyD, storing data;
3.tb_demo. Myi, storing indexes.

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

1. Select the intensive table. The MyISAM storage engine is very fast when filtering large amounts of data, which is its most prominent advantage.
2. Insert-intensive tables. The concurrent Insert attribute of MyISAM allows data to be selected and inserted at the same time. For example, the MyISAM storage engine is ideal for managing mail or Web server log data.

InnoDB

InnoDB is a robust transactional storage engine that has been used by many Internet companies to provide a powerful solution for users to operate very large data stores. The MySQL 5.6.13 version installed on my computer, InnoDB is the default storage engine. InnoDB also introduces row-level locking and foreign key constraints, and using InnoDB is the ideal option in the following situations:

1. Update the intensive tables. The InnoDB storage engine is particularly well suited to handle multiple concurrent update requests.
2. Business. The InnoDB storage engine is a standard MySQL storage engine that supports transactions.
3. Automatic disaster recovery. Unlike other storage engines, the InnoDB table can automatically recover from a disaster.
4. Foreign KEY constraints. MySQL supports a foreign key storage engine with only InnoDB.
5. Supports automatic addition of column auto_increment properties.

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

MEMORY

The starting point for using the MySQL memory storage engine is speed. To get the fastest response time, the logical storage media used is system memory. While storing table data in memory does provide high performance, all memory data is lost when the mysqld daemon crashes. Getting speed also brings with it some flaws. It requires that the data stored in the Memory data table be in the same length format, which means that a variable length of data type such as BLOB and text cannot be used, and that varchar is a variable-length type, but because it is within MySQL as a fixed-length char type, So it can be used.

The memory storage engine is typically used in the following situations:

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

2. If the data is temporary and requires immediate availability, it can be stored in the memory table.

3. Data stored in the memory table, if suddenly lost, will not have a substantial negative impact on the application service.

Memory supports both hash and B-tree indexes. B-Tree index is superior to the hash index, you can use some queries and wildcard queries, you can use the <, > and >= operators to facilitate data mining. The hash index makes an "equality comparison" very fast, but it is much slower than the range comparison, so the hash index value is suitable for use in the = and <> operators and is not appropriate in the < or > operators, nor in the ORDER BY clause.

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

Copy Code code as follows:

CREATE TABLE Users
(
ID smallint unsigned NOT NULL auto_increment,
Username varchar () NOT NULL,
PWD varchar () NOT NULL,
Index using hash (username),
Primary KEY (ID)
) Engine=memory;

The preceding code creates a table with a hash hash index on the username field. The following code creates a table using the Btree index.

Copy Code code as follows:

CREATE TABLE Users
(
ID smallint unsigned NOT NULL auto_increment,
Username varchar () NOT NULL,
PWD varchar () NOT NULL,
Index using Btree (username),
Primary KEY (ID)
) Engine=memory;

MERGE

The merge storage engine is a combination of a set of MyISAM tables that must be identical, although they are not used as well as other engines, but are useful in some cases. To be blunt, the merge table is the aggregator of several identical myisam tables; there is no data in the merge table, and tables for the merge type can be queried, updated, and deleted, which actually operate on the internal MyISAM table. The use scenario for the merge storage engine.

For server logs, the commonly used storage strategy is to divide the data into tables, each of which is related to a particular time end. For example, you can use 12 identical tables to store server log data, each of which is named after each month's name. When it is necessary to generate a report based on data from all 12 log tables, this means that you need to write and update multiple table queries to reflect the information in those tables. Instead of writing these potentially erroneous queries, merge the tables to use a query and then delete the merge table without affecting the original data, deleting the merge table simply deletes the definition of the merge table and has no effect on the internal table.

ARCHIVE

Archive is the meaning of archiving, many of the advanced features are no longer supported after the archive, only supporting the most basic inserts and queries two functions. Prior to MySQL version 5.5, archive did not support indexing, but it began to support indexing in later versions of MySQL 5.5. Archive has a good compression mechanism that uses a zlib compression library, which is compressed in real time when the record is requested, so it is often used as a warehouse.

Some problems with the storage engine

1. How can I see which storage engines are available to the server?
To determine which storage engines your MySQL server can use, execute the following command:

Copy Code code as follows:

Show engines;

This command will be taken care of.

2. How do I choose the right storage engine?
(1) Selection criteria can be divided into:
(2) whether the need for support services;
(3) Whether to use hot standby;
(4) Crash recovery: can accept collapse;
(5) Whether the need for foreign key support;
Then follow the criteria and select the corresponding storage engine.

Summarize

This article summarizes several more commonly used storage engines, for the actual work needs to be based on specific circumstances, the actual project instance to apply, is the best learning method.