Shulou(Shulou.com)06/01 Report--

The storage engine is a MySql component that handles SQL operations for different table types. InnoDB is the default and most general storage engine, and it is also the official recommended storage engine, except in some specific cases. The storage engine for tables created by the CREATE TABLE statement in MySql 5.6defaults to InnoDB.

The MySql server uses a pluggable storage engine architecture that can dynamically load or unload these storage engines at run time.

You can use the SHOW ENGINES statement to view the storage engines supported by your MySql server. The values in the Support column indicate whether an engine can be used by you: YES, NO, and DEFAULT values indicate that a storage engine is available, unavailable, available, and is the current default storage engine.

Storage engines supported by MySql 5.6

The default storage engine for InnoDB:MySql version 5.6. InnoDB is a transaction-safe storage engine with commit, rollback and crash recovery functions to protect user data. InnoDB's row-level locking and Oracle-style consistent lock-free reads improve its multi-user concurrency and performance. InnoDB stores user data in a clustered index to reduce the Icano overhead of normal queries based on primary keys. To ensure data integrity, InnoDB also supports foreign key constraints.

MyISAM: table-level locking limits its performance in terms of read-write load, so it is often used in read-only or read-based data scenarios.

Memory: stores all data in memory and applies it to scenarios where non-critical data is searched quickly. The Memory engine was once called the HEAP engine. Its use cases are decreasing: InnoDB's memory buffers provide a common and durable way to keep most or all of the data in memory, and NDBCLUSTER provides fast key-value access for large distributed datasets.

CSV: its table is really a comma-separated text file. The CSV table allows you to import and export data in CSV format and interact with scripts and applications in the same read and write format. Since the CSV table does not have an index, it is best to put the data in the InnoDB table in normal operations and use the CSV table only during the import or export phase.

Archive: black hole storage engine. / dev/null,Archive, similar to Unix, only receives but does not save data. Queries on tables of this engine often return an empty set. Such tables can be applied to DML statements that need to be sent to the slave server, but the master server does not retain the master-slave configuration of the backup of such data.

NDB: (aka NDBCLUSTER)-this clustered data engine is particularly suitable for applications that require the highest level of uptime and availability. Note: the NDB storage engine is not supported in the standard MySql version 5.6. Currently, the versions that can support MySql clusters are: MySQL Cluster NDB 7.1 based on MySql 5.1, MySQL Cluster NDB 7.2 based on MySql 5.5, and MySQL Cluster NDB 7.3 based on MySql 5.6. MySQL Cluster NDB 7.4, also based on MySql 5.6, is currently in the development stage.

Merge: allows MySql DBA or developers to group a series of the same MyISAM tables and reference them as an object. Suitable for very large-scale data scenarios, such as data warehouses.

Federated: provides the ability to join different MySql servers from multiple physical machines to create a logical database. Suitable for distributed or data market scenarios.

Example: this storage engine is used to hold examples of MySql source code that illustrates how to start writing a new storage engine. It is mainly aimed at interested developers. This storage engine is a stub that does nothing. You can use this engine to create tables, but you can't save any data to them or retrieve any indexes from them.

You are not limited to using the same storage engine for the entire server or the entire schema. You can define a storage engine for all tables. For example, an application may be dominated by InnoDB tables, supplemented by an CSV table to export data to spreadsheets, and a few MEMORY tables for temporary workspaces.

Selection of storage engine

These storage engines provided by MySql are designed for different application cases. The following table provides an overview of the storage engine provided by MySql:

Summary of storage engine characteristics

Characteristics

MyISAM

Memory

InnoDB

Archive

NDB

Storage upper limit

NDB

RAM

64TB

None

384EB

Transaction support

No

No

Yes

No

Yes

Lock granularity

Table

Table

OK

Table

OK

MVCC

No

No

Yes

No

No

Geospatial data type support

Yes

No

Yes

Yes

Yes

Geospatial indexing support

Yes

No

Yes

No

No

B-tree index

Yes

Yes

Yes

No

No

T-tree index

No

No

No

No

Yes

Hash index

No

Yes

No

No

Yes

Full-text retrieval index

Yes

No

Yes

No

No

Clustered index

No

No

Yes

No

No

Data caching

No

N/A

Yes

No

Yes

Index cache

Yes

N/A

Yes

No

Yes

Data compression

Yes

No

Yes

Yes

No

data encryption

Yes

Yes

Yes

Yes

Yes

Support for database clusters

No

No

No

No

Yes

Support master and slave

Yes

Yes

Yes

Yes

Yes

Foreign keys are supported

No

No

Yes

No

No

Backup / point-in-time recovery

Yes

Yes

Yes

Yes

Yes

Support for query caching

Yes

Yes

Yes

Yes

Yes

Update the statistics of the data dictionary

Yes

Yes

Yes

Yes

Yes

Be careful

InnoDB supports geospatial indexing in MySQL 5.7.5 and later.

InnoDB optimizes hash index built-in through its adaptive hash indexing feature

InnoDB can only support FULLTEXT indexes in MySql 5.6.4 and later.

MyISAM supports data compression only in row compression format. Read-only MyISAM table with row compression

InnoDB table compression requires InnoDB Barracuda file format

The data encryption of MySql is provided by the server's encryption function, not the storage engine.

Functions such as master-slave support, backup / point-in-time recovery are also provided by the server, not the storage engine

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.