Introduction to MySQL's mainstream storage engine

We know that MySQL is the biggest feature of its pluggable plug-in storage engine, this article will introduce the current market mainstream storage engine. Here is a special point to mention: since MySQL is open source, if you are not satisfied with some of the storage engine, you can modify or write a storage engine, to increase their desired characteristics (as far as I know, the domestic more well-known with the TNT engine NetEase), which is MySQL as a source of the charm of the Open-source database.

Some people may be surprised when they touch MySQL, even with a storage engine that does not support transactions, because people who have studied relational database theory know that transactions are at the heart of relational databases. But in real-world applications (especially the Internet), in order to improve performance, it is possible to discard transactions in certain scenarios.

InnoDB

InnoDB is the most used storage engine for MySQL, with at least 95% of MySQL currently on the market using the storage engine, so starting with MySQL5.5, it replaces MyISAM as the default storage engine. It has several features as follows:

1 Support transactions: The use of MVCC (multiple version concurrency control) to support high concurrency, the implementation of the four ANSI standard isolation level, the default is repeatable read, and through the Gap Lock (Next-key Lock) to prevent Phantom reading.

2 The InnoDB table is based on a clustered index and has a high performance for primary key queries, but its level two index must contain primary key columns.

3 Support for Hot backup: The Open-source xtrabackup provided by Oracle for MySQL Enterprise backup and Percona can achieve hot standby

4) Support row-level lock

MyISAM

1 does not support row-level locks, which means that any operation will lock the entire table.

2 does not support transactions, can not be rolled back, failed to restore security after the crash.

3 support Full-text indexing, compression, space functions, etc.

The MyISAM design is simple, the data is stored in tight format, and in some scenarios performance is good, but the most typical problem is table lock.

Archive

is a simple engine optimized for high speed inserts and compression, only inserts and select operations are supported, and each select is a full table scan for log and data collection applications.

Csv

You can use the ordinary CSV file as a MySQL table processing, you can store Excel data in CSV format files, and then copied to the MySQL data directory, can be in the MySQL table open, so the CSV engine as a data exchange mechanism, very useful.

Memory

All data is kept in memory and data is lost after reboot. The memory table is a table-level lock, so write performance is low, but its query performance is very high, supporting the hash index.

NDB Cluster

MySQL servers, NDB cluster storage engines, and distributed, share-nothing, disaster-tolerant, highly available NDB database combinations become MySQL clusters.

The above is the MySQL built in the storage engine, the following is a Third-party storage engine.

XTRADB, PBXT

It's an improved version of the Percona company based on InnoDB.

Tokudb

A storage engine with large data.

Rethinkdb

SSD design for solid-state storage.

Infobright

Column-oriented storage engine for data analysis and Data Warehouse design.

How do I choose the right storage engine?

Guideline 1: Unless you need to use InnoDB features, you should choose the InnoDB engine first.

Guideline 2: Do not mix with multiple storage engines.

Changing the storage engine of a table

There are three ways to change a table's storage engine, the most common one being:

ALTER TABLE MYTBL engine = InnoDB;

But there is a problem with this approach: it takes a long time to execute because MySQL will copy the data from the original table to the new table in rows.

The 2nd approach is to use the Mysqldump tool to export data to a file, and then modify the storage engine options for the CREATE TABLE statement in the exported text file.

The final approach is to create a new storage engine table and then use the Insert...select statement to guide the data:

CREATE table newtbl like oldtbl;  
ALTER TABLE NEWTBL ENGINE=INNODB;  
INSERT INTO NEWTBL select * from Oldtbl;

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/