MySQL table Storage Engine

Document directory

• Data Table portability

Mysql Data Tables support six types: bdb, heap, isam, merge, MyISAM, and innobdb.

These six types are divided into two categories: Transaction-safe, including bdb and InnoDB, And the other are in the second category, it is called non-Transaction Security (non-transaction-safe ).

Bdb Brekeley dB is the earliest type of MySQL table with transaction capabilities, developed by sleepycat software (http://www.sleepycat.com. It provides the transaction control capability, which ensures that all commands are successfully executed, or the results of all commands are rolled back when an error occurs in any command, it can be imagined that the transaction control capability in e-banking is very important. Supports commit, rollback, and other transaction features. The latest version of MySQL has planned to remove support for bdb and develop InnoDB.

 

InnoDB

It is a new transaction-security storage engine used for transaction processing applications. It supports almost all the features of bdb and has many new features, including ACID transaction support. Its features are as follows:

• Transaction Processing Mechanism
• Support for external links
• Immediate recovery after crash
• Supports the foreign key function and cascading Deletion
• Support concurrency
• Storage Method on Hard Disk: innobdb FRM

 

MyISAM

The default MySQL plug-in storage engine is based on the isam type, but it adds many useful extensions, it is one of the most commonly used storage engines in Web, data warehousing, and other application environments. Note: by changing the storage_engine configuration variables, you can easily change the default storage engine of the MySQL server. The advantages are as follows:

• 1. Smaller than the isam table, with less resources
• 2. You can specify the type of binary porting table between different platforms when creating the table.

 

Isam

Is the default type used by MySQL tables before the MyISAM type is displayed, and is now replaced by MyISAM.

 

Memory (HEAP)

It is the fastest table to access in MySQL tables. It stores all data in Ram and provides extremely fast access in environments where reference and other similar data need to be quickly searched. Note: The data in this type is non-persistent. It is generally applicable to temporary tables. If MySQL or the server crashes, all the data in the table is lost.

 

Merge

It is a new type of table that deserves attention. It is composed of a group of MyISAM tables, which are merged mainly for performance considerations because it can improve search speed and repair efficiency, saves disk space. Allows MySQL DBAs or developers to logically combine a series of equivalent MyISAM tables and reference them as one object. It is suitable for vldb environments such as data warehousing. Sometimes it usesMrg_myisamThe name appears.

 

Archive

It provides a perfect solution for storing and retrieving a large number of rarely referenced historical, archived, or security audit information.

 

Federated

Connects multiple separated MySQL servers to create a logical database from multiple physical servers. It is very suitable for distributed environments or data mart environments.

 

Ndbcluster/cluster/NDB

MySQL's cluster-based database engine is especially suitable for applications with high-performance search requirements. Such search requirements also require the highest normal working time and availability.

 

CSV

Text Files separated by commas in the Standard CSV format are used for external data exchange.

 

Blackhole

It accepts but does not save the data, and returns an empty set for any retrieval request. It is generally applicable to Distributed Database Design where data is automatically copied and not stored locally. (Uncertain) It may also be used to temporarily disable/ignore database application input.

 

Example

This is a test engine. You can create such a table, but neither write nor retrieve data. It seems to be a sample engine provided for MySQL developers.

 

The command syntax for operating and viewing table types is as follows:? [Copy to clipboard] view code SQL

123456789101112131415161718192021222324

---- Create table 'message' ('id' int (11) not null auto_increment, 'sender' varchar (255) default null, 'cycler' varchar (255) default null, 'content' text, 'is _ read' char (1) default 'n', 'Send _ time' datetime default null, 'del _ flag' tinyint (4) default '0', primary key ('id') engine = MyISAM; -- you can also change it after creation: alter table 'message' engine = InnoDB; -- view the table type (storage engine) show table status from 'test'; -- result Is the information of all tables in the database test. The second type is the table type. -- View the table type (storage engine) 2 show create table 'message'; -- the result is the information when the table message is created, with either type or engine, specifies the table type (storage engine ). -- Show tables cannot obtain table type information.

Note: MySQL of earlier versions uses type instead of engine (for example, type = MyISAM ). MySQL 5.1 supports this syntax for backward compatibility, but type is currently ignored, and engine is the first usage. Generally, the engine option is unnecessary. Unless the default value has been changed, MyISAM is the default storage engine.

Data Table portability

Common data migration method: export the data table content to a text file, copy it to the hard disk, and import it to the target database.

For file-level operations, some data tables can be moved by directly copying table files separately. See the following table.

Isam	No
Myiasm	Yes
Bdb	No
Innobdb	Yes

View all tables in the database
Show table status;