MySQL Storage Engine

Brief introduction

• Data in MySQL is stored in files (or memory) in a variety of different technologies;

• Each technology uses different storage mechanisms, indexing techniques, locking levels, and ultimately provides a wide range of capabilities and capabilities;

• These different techniques for implementing data storage are logically translated into the "Storage engine layer" in MySQL's overall architecture;

• Storage engine, often referred to as a "table type" (that is, you can specify the storage engine when you create a table, but you cannot specify a storage engine for a database)

View

Show engines; # Displays a supported storage engine show Table status [{from |  inexpr]; # Displays the status information for the specified table in the specified database # method One: Show table status like ' classes '; # France II: Show Table status where Name= ' Classes ' \g

The storage engine category used by the table can be obtained by looking at the status information of the table:

To modify the default storage engine :

# in config file/etc/= InnoDB

Each storage engine and its characteristics

InnoDB

Support transactions: Transaction log, log file size consistent: Support for IB_LOGFILE0 and Ib_logfile1 supported by foreign key support MVCC support clustered index support row-level lock: That is, the gap lock supports the secondary index support Adaptive Hash index support hot backup does not support full-text indexing

MyISAM

support for full-text indexing supports compression: for implementing data warehouses, saving storage space and improving performance support for table-level locks: If the ratio of read and write operations is 9:1 or 8: 2 , using MyISAM can improve performance by delaying the update of indexes: Reduce IO Pressure does not support transactions, foreign keys, MVCC, row-level locks and other crashes after the crash can not safely recover data applicable scenarios: Read less write data, smaller tables, can tolerate the crash after the modification operation and data loss ; If the tables in your own MySQL database are the # Recommendations for using the MyISAM storage engine: Using the InnoDB storage engine and using the Read Commited isolation level, performance is not worse than MyISAM performance, it is recommended to use the InnoDB storage engine

ARCHIVE

Supports only insert and select Support Good compression does not support transactions, and does not support indexes well for: storage log information, or other applications of data collection classes implemented by time series, such as monitoring logs

Csv

1 2	将数据存储为csv格式，不支持索引 仅适用于数据交换场景

Blackhole

1 2	没有存储机制，任何发往此引擎的数据都会丢弃 由于其会记录二进制日志，故常用于多级复制架构中作中转服务器

MEMORY

1 2 3 4

数据保存在内存中，即是一个内存表 仅支持hash索引，使用表级锁 不支持blob和text数据类型 常用于保存中间数据，如周期性的聚合数据等；也用于实现临时表

Mrg_myisam

0 S	是MyISAM的一个变种，能够将多个MyISAM表合并为一个虚表 属于传统的存储引擎，一般不常用

NDB

1	是MySQL Cluster中专用的存储引擎，不常用

Third-party storage engines

XtraDB

Enhanced InnoDB, provided by Percona; usage: Compile and install MySQL, download xtradb source to replace InnoDB source in MySQL storage engine, and recompile install MySQL

Pbxt

MARIADB comes with this storage engine supports engine-level replication, foreign key constraints support transactions, and MVCC provides appropriate support for SSD disks

Tokudb

Using the fractal Trees Index, the performance is strong for storing big data, with a good compression ratio has been introduced to the new version of the MARIADB

column Storage Engine :

Infobright

Formerly Maria, is the enhanced version of MyISAM support for post-crash security recovery support for data caching

Other column storage engines

such as INFINIDB,MONETDB,LUCIDDB, etc.

Open Source Community storage Engine :

Aria

Formerly Maria, is the enhanced version of MyISAM support for post-crash security recovery support for data caching

Groona

Full-text indexing engine; Mroonga is a Groona-based two-time development version

Oqgraph

A storage engine that supports graph structure developed by open query

Sphinxse

SQL interface provided for Sphinx full-Text Search server

Spider

The data can be divided into different shards, more efficient and transparent implementation of the Shard (shard); Support for parallel queries on shards

Select the storage engine to reference features

Whether a transaction is required

Backup type support (hot standby or cold standby)

Post-crash recovery

Unique features (such as SSD support, etc.)