MySQL Storage engine comparison

MySQL storage engine: The storage Engine is also commonly referred to as the "table type"

Modify the original Hellodb.sql storage engine to InnoDB

Sed-i ' [email protected][email protected][email protected] '/home/hellodb.sql

Import the modified SQL file

Mysql-uroot-p MyDB 
View the table engine, status
Mysql> show engines; Mysql> Show table status, Show table status in Hellodb \g mysql> Show Table status in Hellodb where name = ' class Es ' \g
Name Table name
Engine Storage Engines
Version: Versions
Row_format: Row format
{default| dynamic| fixed| commpressed| Redundant| COMPACT}
Rows: Number of rows in a table
Avg_row_length: Average number of bytes per row
Data_length: The overall size of the data in the table, in bytes
Max_data_length: The maximum amount of space a table can occupy, in bytes, 0 for a limit
Index_length: Size of index, in bytes
Data_free: For the MyISAM table, represents the space that has been allocated but not yet used, which contains space that was freed after the row was previously deleted

Auto_increment: The value of the next auto_increment
Create_time: When the table was created
Update_time: Last modified time for table data
Check_time: Using check table or Myisamchk last check table time
Collation: Sorting rules
Checksum: The Checksum of the table if it is started
Create_options: Specify additional options to use when creating tables
Comment: note Information for tables

SHOW TABLE STATUS [{from | in} Db_name]
[like ' Pattern ' | WHERE Expr]
InnoDB
Two types of formats:
1.innodb_file_per_table=off, that is, with shared table spaces
One unique format definition file for each table: TB_NAME.FRM
There is also a shared tablespace file that is located by default in the data directory: ibdata#
2.innodb_file_per_table=on, that is, using a separate table space
Each table stores two files in a database directory
Tb_name.frm
Tb_name.ibd
MyISAM:
Each table stores three files in a database directory
Tb_name.frm
Tb_name. MYD
Tb_name. MYI

Tablespace: Table space, a unique format data file managed by InnoDB, which stores data and indexes internally

How to modify the default storage engine: implemented by Default_storage_engine service variables

Features of each storage engine:
InnoDB:
Support transactions, with transaction logs
Ib_logfile0
Ib_logfile1
Support for FOREIGN KEY constraints
Support for MVCC (multi-version concurrency control)
Support for clustered Indexes
Indexes other than clustered indexes, often referred to as secondary indexes
Row-level Lock: Gap lock
Support for using secondary indexes
Support self-use hash index
Support for Hot backup

MyISAM:
Full-Text Indexing
Support Table Compression storage: Do data Warehouse, save storage space and improve performance
Support for Spatial indexes
Table-Level Locks
Defer updating indexes

Transactions, foreign keys, and row-level locks are not supported
Unable to recover data safely after a crash

Usage scenarios: read-only data, small tables, able to tolerate post-crash repair operations and data loss
Show Table status in Mysql\g
MySQL comes with a table using MyISAM
ARCHIVE
Only inserts and select are supported, which supports good compression capabilities
Application for storing log information, or other data collection classes implemented by Time series
Csv:
Store data in CSV format, do not support indexing, use only with data exchange scenarios
Blackhole:
No storage mechanism, any data sent to the secondary engine will be discarded, it will record the binary log, it is often used in multi-level replication architecture as a transit server
MEMORY:
Saves data in memory, memory tables, and is commonly used to hold intermediate data, such as periodic aggregated data, to implement temporal tables
Support for hash indexes, table-level locks, blob and text data types not supported
Mrg_myisam:
is a variant of MyISAM, capable of merging multiple MyISAM tables into a single virtual table
NDB:
is a dedicated storage engine in MySQL cluster,

Third-party storage engines
Olpt
XtraDB: Enhanced InnoDB, Percona available
When compiling the installation, download the source code of the xtradb instead of the InnoDB in the MySQL storage engine
PBXT:MARIADB comes with this storage engine
Supports engine-level replication, foreign key constraints, and appropriate support for SSD disks
Support Transactions, MVCC
TOKUDB: Using the Fractal Trees Index, which is suitable for storing big data, has a good compression ratio, has been introduced MARIADB

Column data storage Engine:
Infobright: Currently more well-known column engine, suitable for Yu Hai data storage scenarios, such as PB level, designed for data analysis and data warehousing
Infinidb
MonetDB
Luciddb

Open Source Community Storage Engine:
Aria: Formerly Maria, is an enhanced version of MyISAM (supports post-crash security recovery, supports data caching)
Groona: Full-text indexing engine
Mroonga: is based on the Groona two times development version
Oqgraph: A storage engine that supports graph (mesh) architecture, developed by open query
SPHINXSE: Provides SQL interface for Sphinx full-Text Search server
Spider: Can cut data into different shards, more efficient and transparent implementation of the Shard (shared), and support on the Shard support parallel query

How to choose??
Whether a transaction is required
Support for type of backup
Post-crash recovery
Unique Features

This article comes from "Life is endless, tossing and turning." "Blog, be sure to keep this provenance http://990487026.blog.51cto.com/10133282/1693663
MySQL Storage engine comparison