How to select the MySQL storage engine

One, the MySQL storage engine

Full engine description or look at the Official document: http://dev.mysql.com/doc/refman/5.6/en/storage-engines.html

Here are some of the main engines

1. InnoDB Storage Engine

InnoDB is the default transactional engine for MySQL and is designed to handle a large number of short-term (short-lived) transactions. Unless there are very specific reasons to use other storage engines, the InnoDB engine should be a priority.

It is recommended to use MySQL5.5 and later versions, as this version and later versions of the InnoDB engine perform better.

In later versions of MySQL4.1, InnoDB can store data and indexes for each table in a separate file. This has significant advantages in operations such as replication backup crash recovery. This function can be turned on by adding innodb_file_per_table to the my.cnf. As follows:

1. [Mysqld]
2. Innodb_file_per_table

The InnoDB employs MVCC to support high concurrency and achieves four standard isolation levels. Its default level is REPEATABLE READ (repeatable read), and the presence of Phantom reads is prevented through the gap lock (next-key locking) policy. (The transaction and transaction isolation levels are another big problem, and the respective nets are complementary).

InnoDB is based on clustered index, and clustered index has high performance for primary key query. However, its two-level index (secondary index, non-primary key indexes) must contain primary key columns, so if the primary key column is large, all other indexes will be large. Therefore, the primary key should be as small as possible if there are more indexes on the table.

The INNODB storage format is platform-independent and allows data and index files to be copied from the Intel platform to the Sun SPARC platform or other platforms.

InnoDB supports true hot backup through some mechanisms and tools, and MySQL's other storage engines do not support hot backups.

2. MyISAM Storage Engine

MyISAM provides a number of features, including full-text indexing, compression, spatial functions (GIS), and so on, but MyISAM does not support transactional and row-level locks, and there is no doubt that the failure to recover after a crash is not safe.

MyISAM will store the table in two files in: Data files and index files, respectively. MyD and. Myi are extensions.

Before MySQL5.0, only 4G of data can be processed, and in 5.0 it is possible to process 256T of data.

When the data is no longer being modified, the MyISAM table can be compressed, and the read capability can be increased after compression because of the reduced disk I/O.

3. Archive engine

The archive storage engine supports only insert and select operations, and indexes are not supported until MySQL5.1.

The archive table is suitable for log and data collection applications.

The archive engine supports row-level locks and dedicated buffers, so high concurrent insertions can be achieved, but it is not a thing-based engine, but a simple engine optimized for high-speed insertion and compression.

4. Blackhole Engine

The Blackhole engine does not implement any storage mechanism, it discards all inserted data and does not save any. However, the server logs the Blackhole table, so it can be used to replicate the data to the repository, or simply log it. But this kind of application will encounter many problems, so it is not recommended.

5. CSV engine

The CSV engine can handle the normal SCV file as a MySQL table, but it does not support indexing.

The CSV engine can be useful as a mechanism for exchanging data.

6. Federated Engine

The federated engine is a proxy for accessing other MySQL servers, although the engine appears to provide a good cross-server flexibility, but it often poses problems and is therefore disabled by default.

7. Memory Engine

It is useful to use memory tables if you need fast access to data, and the data is not modified, and restarts are lost later. The memory meter is at least one order of magnitude faster than the MyISAM table.

The memory table is a table-level lock, so the performance of concurrent writes is low. It does not support BLOB or text type columns, and the length of each row is fixed, which may be a waste of some memory.

Temporary tables and memory tables are not the same thing. A temporary table is a table created using the Create temporary table statement, which can use any storage engine that is visible only in a single connection, and the temporary table will no longer exist when the connection is broken.

8. NDB Cluster engine

MySQL server, NDB cluster storage engine, and a combination of distributed, share-nothing, disaster-tolerant, highly available NDB databases are known as MySQL clusters (MySQL Cluster).

Other third-party or community engines

XtraDB: is an improved version of InnoDB that can be used as a perfect replacement for InnoDB.

TOKUDB: A new index data structure called a fractal tree (Fractal Trees) is used.

Infobright: Is the most famous column-oriented storage engine.

Groonga: is a full-text indexing engine.

Oqgraph: The engine is developed by open query, which supports diagram operations (such as finding the shortest path between two points).

Q4M: The engine implements the queue operation within MySQL.

Sphinxse: The engine provides the SQL interface for the Sphinx full-text index search server.

Second, choose the right engine

In most cases, InnoDB are the right choice, and can simply be summed up in a sentence "Unless you need to use some InnoDB features that are not available, and there is no alternative, you should prefer the InnoDB engine."

Unless it is a last resort, it is recommended not to mix multiple storage engines, which could lead to a range of responsible issues and potential bug and boundary issues.

If your application requires a different storage engine, consider the following factors first:

Transaction:

If the application requires transactional support, then InnoDB (or XTRADB) is currently the most stable and validated option.

Backup:

If you can shut down the server periodically to perform backups, the backup factor can be ignored. Conversely, if you need online hot backup, then choosing InnoDB is the basic requirement.

Crash recovery

The probability of damage after the MyISAM crashes is much higher than the InnoDB, and the recovery rate is slower.

Unique Features

If a storage engine has some key features and lacks some of the necessary features, sometimes it has to be a compromise or a tradeoff in architecture design.

Some query SQL behaves differently on different engines. The more typical are:

SELECT COUNT (*) from table;

For MyISAM it will be quick, but the rest may not work.

Iii. Examples of application

1, log-type application

MyISAM or archive storage engines are suitable for such applications because they have low overhead and very fast insertion.

What if you need to make an analytic report of the logged log, and the SQL that generated the report is likely to cause a noticeable decrease in insertion efficiency?

One solution is to use MySQL's built-in replication scheme to copy data to a repository, and then perform comparisons of time-consuming and CPU-based queries on the standby. Of course, you can also perform report query operations when the system load is low, but the application is constantly changing, and if relying on this strategy may cause problems later.

Alternatively, the name of the log table contains year and month information, which allows frequent query operations on history tables that do not already have an insert operation, without interfering with the most recent insert operation on the current table.

2, read-only or read-only tables in most cases

Some tables of data are used for the preparation of categories or lists (such as jobs), which is typically read-write less business. If you don't mind the MyISAM crash recovery problem, choosing the MyISAM engine is appropriate. (MyISAM only writes data to memory, and then waits for the operating system to periodically brush the data out to disk)

3. Order Processing

Involving order processing, supporting transactions is necessary, InnoDB is the best choice for order Processing class applications.

4. Large Data volume

If your data grows to a level above 10TB, you may need to build a data warehouse. Infobright is the most successful solution for MySQL Data Warehouse. Some large databases are not suitable for infobright, but may be suitable for tokudb.

The following are the applicable environments for common storage engines:

1. MyISAM: Default MySQL plug-in storage engine, which is one of the most commonly used storage engines in the Web, data warehousing, and other application environments
2. InnoDB: For transactional applications, with many features, including acid transaction support.
3. Memory: Keep all your data in RAM and provide extremely fast access in environments where you need to quickly find references and other similar data.
4. Merge: Allows a MySQL DBA or developer to logically group together a series of equivalent MyISAM tables and reference them as 1 objects. Ideal for VLDB environments such as data warehousing.

How to select the MySQL storage engine