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)