MySQL Storage engine

Directory

1. Write in front

2. mysql built-in other storage engine

3. Third-party storage engine

4, the storage Engine selection criteria

1. Write in front

Mysql> SHOW ENGINES; #查看支持的存储引擎

The storage engine is a table-level concept that allows you to specify a table's storage engine when you create a table.

Mysql> SHOW TABLE STATUS like ' tn_name ' \g #显示表状态信息

Table status information:

Row_format:dynamic,fixed,compressed,compact

Rows: The number of row rows in the table, the table with the InnoDB engine is the estimated value

Avg_row_length: Average number of bytes in a row

Data_length: The amount of data in a table, in bytes

Max_data_length: Maximum capacity of a table in bytes

Index_length: The amount of index data, in bytes

Data_free: Storage space that has been allocated, but not stored, usually refers to the space that is freed after the data has been populated and then deleted

Auto_increment: The next growth value for a field with automatic attributes

Create_time: When the table was created

Update_time: Last update time of data

Check_time: Check the time of the table using the check command

Collation: Sorting rules

Checksum: The checksum of the table

Create_options: Additional options specified when creating a table

Coment: The MyISAM table, which stores the information specified by the Comment table option when creating the table, stores the corresponding tablespace information for the InnoDB table

2. mysql built-in storage engine

2.1. InnoDB Engine

Inodb Features:

InnoDB engine is a transactional storage engine, the InnoDB table is built with clustered index, the data and index are stored together, in the order of the primary index is stored as an ordered file, through the gap lock to effectively prevent Phantom reading, support Adaptive Hash index accelerated read operation, the use of MVCC Multi-version concurrency control to support high concurrency, Implemented four standard transaction isolation levels, unlike the MyISAM engine that only supports table-level locks, InnoDB tables support row-level locks and real-hot backups with MySQL Enterprise backup and open source Percona xtrabackup tools.

Files stored at the file level by tables with the InnoDB storage engine:

TB_NAME.FRM: Storing table format

TB_NAME.IBD: After opening "Innodb_file_per_table=on", the data and index are stored in this file, the location of the default file is stored in the FRM table storage location, if this variable is not turned on, All the table data and indexes are stored in a tablespace file such as "Ibdata1" in the data directory by default, which is a black box managed by InnoDB and consists of a series of data files.

2.2. MyISAM Engine

MyISAM supports full-text indexing, compression, spatial functions, but does not support row-level locks and transactions, and has the delay to update the index key attribute, that is, when the "delay_key_write" option is specified, the modified index data is not immediately written to disk when the modify execution completes. Instead, it writes to the in-memory key buffer, which writes the corresponding index block to the disk only when the key buffer is cleared or the table is closed, which can improve write performance, but can cause index corruption when the host crashes.

Cons: Unable to recover safely after a crash

MyISAM Scenario: A table with a small amount of data, capable of accepting manual repair operations

Files stored at the file level by tables with the MyISAM storage engine:

TB_NAME.FRM: Format File

Tb_name. MYD: Data files

Tb_name. MYI: Index File

2.3. Archive engine

Supports only insert and select, supports good compression, supports full table scan, does not support indexing

Application scenarios: Storing log information, or data collection and application by time series

2.4. CSV engine

Save data in CSV format, index not supported

Scenarios for exchanging data in different types of databases

2.5, Blackhole:

Without a storage mechanism, any data will be discarded, but binary logs will be logged

Storage engine for replication scenarios in MySQL as a relay server

2.6. Federated engine

The ability to access data on a remote server, similar to an agent. be improved in MARIADB

2.7. Memory Engine:

Memory storage engine, can achieve fast data query, modification, support hash index, at least one order of magnitude faster than MyISAM, but also only support table-level lock, concurrent write performance is low. Applies to the need to quickly retrieve data, typically for implementing temporal tables.

2.8. MRG Engine

The ability to merge multiple MyISAM tables does not natively store the data, but after the partitioning feature is introduced, the engine has been discarded.

2.9. NDB Cluster engine

Storage engine dedicated to MySQL cluster cluster

3. Third-party storage engine

3.1. OLTP class (on-line Transaction processing online transaction processing system (OLTP) also known as transaction-oriented processing system)

Xtradb:innodb enhanced, the INNODB engine has been improved to focus on sexual, measurable and operational flexibility, and has been integrated into the MARIADB.

PBXT: The community is inactive, no longer maintained, PBXT exists in the 5.1/5.2/5.3 version of MariaDB, but MariaDB storage engine is no longer available from PBXT 5.5

TOKUDB: A big data storage engine that has the advantage of storing massive amounts of data, supports fractal tree index data structures, and is suitable for scenarios where large numbers of analytical data are needed

3.2. column-type storage engine (more efficient when mass storage)

Infobright: Suitable for scenarios with a data volume of dozens of TB, compression not supported

There are also Infinidb, MonetDB, LUCIDDB and so on the column area engine.

3.3. Community Storage Engine

Most of the community storage engines are not or rarely used in production environments, here are just a few examples:

Sphinxse, Aria

Groona: Full-text indexing engine

Qqgraph: Support diagram, developed by open query

Spider: Support Shard (Shard)

Vpformysql: Supports vertical partitioning

4, the storage Engine selection criteria

The InnoDB engine should be preferred unless some InnoDB features are not available and there is no alternative.

A), whether transaction support is required

b), whether hot backup is available

c), data security for crash recovery

Suggestions:

Store journaled data, select MyISAM and archive

Storage Forum app, select InnoDB

Order Processing, select InnoDB

This article is from the "knowledge needs summary and records" blog, please be sure to keep this source http://zhaochj.blog.51cto.com/368705/1631323

MySQL Storage engine