MySQL storage engine

Source: Internet
Author: User
MySQL storage engine Introduction

  • Data in MySQL is stored in files (or memory) using different technologies;

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

  • These different data storage technologies are logically converted into "storage engine layer" in the overall MySQL architecture ";

  • Storage Engine, also known as "table type" (you can specify a storage engine when creating a table, but cannot specify a storage engine for a database)

View

Show engines; # display the supported storage engine show table status [{from | in} Db_Name] [like 'pattern' | where expr]; # display the status information of the specified table in the specified database # Method 1: show table status like 'classs'; # Method 2: show table status where Name = 'class'/G

You can view the table status information to obtain the storage engine category used by the table.:

Modify default storage engine:

# In the configuration file/etc/my. cnf, set default_storage_engine = InnoDB.

Storage engines and their features

InnoDB

Supports transactions: transaction logs; consistent log files: for example, ib_logfile0 and ib_logfile1 support foreign keys support MVCC support clustering indexes support row-level locks: that is, the gap lock supports secondary indexes and supports adaptive hash indexes. hot backup does not support full-text indexes.

MyISAM

Supports full-text indexing and compression: used to implement data warehouses, saving storage space and improving performance. supports spatial indexing and table-level locks. if the read/write ratio is or, the use of MyISAM can improve performance latency index update: reducing IO pressure does not support transactions, foreign keys, MVCC, row-level locks, and other crash scenarios where data cannot be safely restored: multiple reads and less writes, small tables can tolerate modifications and data loss after a crash. for example, tables in the built-in mysql database use the MyISAM storage engine. # recommendation: Use the InnoDB storage engine, the read commited isolation level is used, and the performance is not inferior to that of MyISAM. Therefore, InnoDB storage engine is recommended.

ARCHIVE

Only supports insert and select, supports good compression, does not support transactions, and does not support indexes well. Suitable for: storage of log information, or other data collection applications implemented by time series, such as monitoring logs

CSV

12 Stores data in csv format and does not support indexingApplicable only to data exchange scenarios

BLACKHOLE

12 No storage mechanism, any data sent to this engine will be discardedBecause it records binary logs, it is often used as a transit server in a multi-level replication architecture.

MEMORY

1234 Data is stored in memory, which is a memory table.

Only supportedhashIndex, using table-level locks

Blob and text data types are not supportedIt is often used to store intermediate data, such as periodically aggregated data. it is also used to implement temporary tables.

MRG_MYISAM

12 Is a variant of MyISAM. it can combine multiple MyISAM tables into a virtual table.It is a traditional storage engine and is not commonly used.

NDB

1 It is a dedicated storage engine in MySQL Cluster and is not commonly used.

Third-party storage engine

XtraDB

Enhanced InnoDB, provided by Percona. usage: When compiling and installing MySQL, download the XtraDB source code to replace the InnoDB source code in the MySQL storage engine, and re-compile and install MySQL

PBXT

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

TokuDB

Fractal Trees indexes are used to store big data with high performance. The new version of MariaDB has a good compression ratio.

Columnar storage engine:

Infobright

Formerly named Maria, the enhanced version of MyISAM supports secure recovery after crash and supports data caching

Other columnar storage engines

Such as InfiniDB, MonetDB, and LucidDB

Open Source community storage engine:

Aria

Formerly named Maria, the enhanced version of MyISAM supports secure recovery after crash and supports data caching

Groona

Full-text index engine; Mroonga is a secondary development version based on Groona

OQGraph

Developed by Open Query, the storage engine supports graph structure

SphinxSE

Provides an SQL interface for the Sphinx full-text search server.

Spider

Data can be split into different shards, achieving efficient and transparent sharding (shard); supporting parallel query on shards

For details about how to select a storage engine, refer

Transaction required?

Supported backup types (hot backup or cold backup)

Recovery after crash

Special features (such as SSD support)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.