MySQL Storage engine

See which storage engines are supported in MySQL by executing the show engines command

MySQL Storage engine Properties Comparison

How to set the storage engine for a table  

Setting the Default-storage-engine parameter in the MY.CNF configuration file indicates setting the default storage engine
Set the default storage engine for the current connection on the MySQL connection

Mysql> set Default_storage_engine=innodb;

　　

Specify the storage engine for the table by using the engine= statement when creating the table

CREATE TABLE T1 (i INT) ENGINE = INNODB;

　　

Modify the table's storage engine with the ALTER statement after the table is created

ALTER TABLE T1 ENGINE = csv;

　　

INNODB Storage Engine

Storage Engine InnoDB is the current MySQL version of the default storage engine, is also recommended by MySQL storage engine, is a high reliability and high performance of the storage engine.

In the MySQL5.7 version, unless the default storage engine is explicitly specified in the configuration file or when the table is created, the engine= statement is used to specify a different storage engine, otherwise the defaults are InnoDB

The advantages of the InnoDB storage engine are:

DML statements support transactional functionality to ensure acid performance
The use of row-level locks guarantees high concurrency properties
InnoDB a table with a primary key optimizes query performance based on primary key, also known as clustered index, which stores all data on a clustered index to reduce IO consumption for primary key queries
To ensure data consistency, InnoDB also supports foreign key attributes to ensure that there are no inconsistent data between tables with foreign KEY constraints
When a server hardware or software failure causes MySQL to restart, InnoDB automatically identifies data that has been committed before the failure and rolls back all uncommitted data at the time of failure, maximizing protection against loss of data (crash recovery)

InnoDB properties of the storage engine

Myisam Storage Engine

The MyISAM storage engine is the default storage engine for MySQL's older version, which limits its performance in read and write operations due to its table-level lock characteristics, often on read-only tables or on the vast majority of read operations, such as some Web applications and data Warehouse related tables

Each MyISAM table generates three files on disk, with the same table name and file name but with different suffix, .frm file storage table structure information, .MYD file storage table data information, .MYI file storage table index information

Memory Storage Engine

The memory storage engine stores all of the data in RAM to speed up access to some unimportant data

The use of this storage engine has become smaller because InnoDB has provided a data buffer to cache data that is frequently accessed in memory

When MySQL restarts, the data in the memory table is lost, but the table structure is still
Memory only applies to the majority of read-only tables or read operations, because writing to tables can also cause table locks to significantly limit concurrency

After the memory table is created, a file with the same table name will be generated in the disk file, with the suffix. frm, which stores only the table structure and does not store table data

CSV Storage Engine

The table under the CSV storage engine corresponds to a text file where the data is separated by commas and CSV tables can be used to import and export tables in CSV format

When you create a CSV table, the disk generates three files with the name of the table, and. frm stores the structure information for the table. CSV files are used to store comma-separated data information. CSM files are used to store the table's metadata, including the status of the table and how many rows of data information

Mysql> CREATE TABLE t (ID int not NULL, name char (TEN) not null) Engine=csv; Query OK, 0 rows Affected (0.00 sec) mysql> insert into t values (1, ' One '), (2, ' one '); Query OK, 2 rows Affected (0.00 sec) Records:2  duplicates:0  warnings:0mysql> select * FROM t;+----+------+| I D | Name |+----+------+|  1 | One  | |  2 |  |+----+------+2 rows in Set (0.00 sec) mysql> System ls/usr/local/mysql/data/testdb.opt  T.CSM  t.csv  t.frmmysql> system CAT/USR/LOCAL/MYSQL/DATA/TEST/T.CSV1, "one" 2, "one"

　　

ARCHIVE Storage Engine

Archive storage engine tables are used to store large amounts of unindexed historical archived data

The archive table creates two files on disk, and the. frm file is used to store table structure information. Arz files are used to store historical archived data
The archive table supports insert, replace, and select statements, but does not support delete and UPDATE statements

Archive table supports row-level locks
Archive supports Auto_incrment columns and can contain one index on columns, but cannot create indexes on other fields

Archive does not support inserting a data that is less than the current maximum value for the Auto_incrment column
Archive storage engine uses zlib to compress data

Blackhole Storage Engine

The merge storage engine can have the same number of fields, and the MyISAM table with the same index and the same order is logically considered to be the same

The merge table creates two files on disk, and the. frm file holds the structure information for the table. The MRG file contains all MyISAM tables that are considered to be the same table

The merge table supports Select,delete,update,insert statement execution

A UNION clause is required to create the merge table to combine the specified MyISAM, and the Insert_method option is used to specify whether the INSERT statement inserts data into the first or last table, or does not specify or no table does not allow insertions.

CREATE TABLE T1 (a int not NULL Auto_increment primary key, message char) engine=myisam;create table T2 (a int. NOT NULL Auto_increment primary key, Message char ()) Engine=myisam;insert into T1 (message) VALUES (' testing '), (' table '), (' T1 ') INSERT into t2 (message) VALUES (' testing '), (' table '), (' T2 '), "CREATE TABLE Total (a int. NOT NULL auto_increment, message CH AR (+), index (a)) Engine=merge union= (T1,T2) insert_method=last;select * from total;mysql> SELECT * FROM total;+---+-- -------+| A | Message |+---+---------+| 1 | Testing | | 2 | Table   | | 3 | T1      | | 1 | testing | | 2 | table   | | 3 | t2      |+---+---------+6 rows in Set (0.00 sec)

　　

Federated Storage Engine

The Federated storage Engine provides the ability to connect data from a MySQL instance to other instances
The Federated storage Engine is disable state by default, and if you want to turn it on, you need to enable MySQL when you start
With the-federated option

Example Storage Engine

Example storage engine only exists in the MySQL source code, only for the developer, for the actual database users do not have much meaning
The example table retains only the table structure and does not save the data itself

NDB Storage Engine

Dedicated to the MySQL cluster software, the NDB storage engine is MySQL's own cluster software for improved usability and reliability

MySQL Storage engine