Introduction to MySQL Storage engine

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

What is a storage engine?

Data in MySQL is stored in files (or memory) in a variety of different technologies. Each of these technologies uses different storage mechanisms, indexing techniques, locking levels, and ultimately offers a wide range of different capabilities and capabilities. By selecting different technologies, you can gain additional speed or functionality to improve the overall functionality of your application. For example, if you are working on a large amount of temporary data, you may need to use the memory MySQL storage engine. The memory storage engine can store all the tabular data in memory. Alternatively, you might need a database that supports transactional processing (to ensure that the data is backed up when transaction processing is unsuccessful).

these different technologies and associated functions are called storage engines (also known as table types) in MySQL. MySQL has a number of different storage engines configured by default and can be pre-set or enabled in MySQL server. You can choose the storage engine for servers, databases, and tables to provide you with maximum flexibility when choosing how to store your information, how to retrieve it, and what performance and features you need to combine with your data.

Relational database tables are data structures for storing and organizing information, and you can interpret tables as tables of rows and columns, similar to the form of spreadsheets in Excel. Some tables are simple, some tables are complex, some tables do not have to store any long-term data, some tables are 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, it means the storage of different types of data, data processing will also exist differences, then. For MySQL, which offers many types of storage engines (or table types that are not), we can take advantage of MySQL's powerful capabilities by choosing different storage engines based on the need for data processing.

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

Show engines;

MyISAM

　　It does not support transactions, does not support foreign keys, especially fast access, there is no requirement for transactional integrity, or a SELECT, insert-based application can basically use this engine to create tables.
Each myisam is stored as 3 files on disk, with the same file name and table names, but with the following extensions:

• . frm (save table definition)
• MYD (MYData, storing data)
• MYI (myindex, storage index)

Data files and index files can be placed in different directories, evenly allocated Io, to get faster speeds. To specify the path to the data file and the index file, you need to specify the file path by using Data directory and the index directory statement when creating the table, which requires an absolute path.
Each MyISAM table has a flag that is set by the server or MYISAMCHK program when it checks the MyISAM data table. The MyISAM table also has a flag indicating whether the data table has been shut down properly since it was last used. This flag can be used to determine if a data table needs to be checked and repaired if the server thinks it is a crash or a machine. If you want this check to occur automatically, you can use the--myisam-recover behavior when you start the server. This causes the server to automatically check the data table flags and perform the necessary repair processing each time a MyISAM data table is opened. Tables of type MyISAM may be corrupted, you can use the Check Table statement to check the health of the MyISAM table, and fix a corrupt to MyISAM table with the Repair table statement.
The MyISAM table also supports 3 different storage formats:

• static (fixed-length) table
• Dynamic tables
• Compression table

Where the static table is the default storage format. The fields in the static table are non-variable length fields, so each record is fixed-length, the advantage of this storage method is that the storage is very fast, easy to cache, the fault is easy to recover, the disadvantage is that the space occupied is usually more than the dynamic table. A static table complements the spaces defined by the column definition when the data is stored, but does not receive these spaces when accessed, and these spaces are removed before they are returned to the app. It is also important to note that, in some cases, you may need to return a space after the field, and the following space will be automatically processed when using this format.
Dynamic tables contain variable-length fields, where records are not fixed lengths, so storage has the advantage of less space, but frequent updates to delete records can be fragmented, requiring periodic execution of optimize table statements or MYISAMCHK-R commands to improve performance, and recovery is relatively difficult in the event of a failure.
Compressed tables are created by the Myisamchk tool and occupy very little space because each record is individually compressed, so there is very little access expense.

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. MySQL version 5.6.13 is installed on my computer, and InnoDB is the default storage engine. InnoDB also introduces row-level locking and foreign key constraints, which are ideal for use with InnoDB in the following situations:

1. Update the dense table. The InnoDB storage engine is ideal for handling 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, the InnoDB table can automatically recover from a disaster.
4. Foreign KEY constraints. MySQL supports the foreign key storage engine only InnoDB.
5. Support automatic increment of column auto_increment attribute.

In general, InnoDB is a good choice if transaction support is required and there is a high 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 medium used is system memory. Although storing table data in memory does provide high performance, all memory data will be lost when the mysqld daemon crashes. The speed of the acquisition also brings some drawbacks. It requires that the data stored in the Memory data table use a constant length format, which means that a variable length data type such as BLOB and text cannot be used, and varchar is a variable-length type, but because it is a fixed-length char type within MySQL, So it can be used.

Memory storage engines are typically used in the following situations:

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

2. If the data is temporary and required to be immediately available, 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 app service.

Memory supports both hash index and B-Tree index. The B-Tree index is better than the hash index, you can use partial queries and wildcard queries, or you can use operators such as <, >, and >= to facilitate data mining. Hash indexes are very fast for equality comparisons, but are much slower for range comparison, so the hash index values are suitable for use in operators of = and <>, not in the < or > operators, 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:

Copy CodeThe code is 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 code above creates a table that uses a hash hash index on the username field. The following code creates a table that uses the Btree index.

Copy CodeThe code is 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 in structure, although their use is not as prominent as other engines, but is useful in some cases. To be blunt, the merge table is just a few aggregators of the same MyISAM table, and there is no data in the merge table, and the merge type table can be queried, updated, deleted, and actually operated on the internal MyISAM table. The usage scenario for the merge storage engine.

For server logs, the most common storage strategy is to divide the data into tables, each associated with a specific time-end. For example, you can use 12 identical tables to store server log data, each named by the name of each month. When it is necessary to generate a report 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 queries that may have errors, instead of merging the tables with a single query and then deleting the merge table without affecting the original data, deleting the merge table simply removes the definition of the merge table and has no effect on the internal table.

ARCHIVE

Archive is archived, many of the advanced features are no longer supported after archiving, supporting only the most basic insert and query functions. Prior to MySQL version 5.5, archive did not support indexing, but it began to support indexing in MySQL 5.5. Archive has a good compression mechanism, which uses the Zlib compression library, which is compressed in real time when the record is requested, so it is often used as a repository.

Some problems with the storage engine

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

Copy CodeThe code is as follows:
Show engines;

This command will take care of it.

2. How do I choose the right storage engine?
(1) Selection criteria can be divided into:
(2) Whether support services are required;
(3) Whether it is necessary to use hot standby;
(4) Crash recovery: can accept crashes;
(5) Whether foreign key support is required;
Then, according to the standard, select the corresponding storage engine.

Summarize

This article summarizes several more commonly used storage engine, for the actual work, needs according to the concrete situation, the actual project instance carries on the application, is the best study method.

Ext.: http://blog.csdn.net/cymm_liu/article/details/45745431

Introduction to MySQL Storage engine