MySQL Basic-mysql Storage Engine Overview (eight)

0x01

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

Mysql> show engines; ---View all currently supported storage engines
Mysql> Show Table Status

Storage Engine Format:
SHOW TABLE STATUS [{from | in} Db_name]
[like ' Pattern ' | WHERE Expr]

Mysql> Show Table status in Hellodb where Name= ' class ' \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

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

MySQL Database other storage engine

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 provided, compile and install, download XtraDB source instead of InnoDB source in 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

Choose MySQL Storage engine based on your specific needs
Whether a transaction is required
Support for type of backup
Post-crash recovery
Unique Features

Index Type:
Clustered index
Secondary index

B-Tree Index
R-Tree Index
Hash index
Full-Text Indexing

MySQL Basic-mysql Storage Engine Overview (eight)