MySQL storage engine features and selection recommendations

MySQL official storage engine more common storage engines are: Innodb, MyISAM, Memory, Archive, NDB, BDB, third-party more famous: Tokudb, Infobright, INNFINIDB, XtraDB (InnoDB enhanced version)

The official storage engine features a comparison

MySQL Storage engine comparison

properties	Span style= "Font-weight:bold;text-align:center;background-color:rgb (247,247,247);" >myisam	InnoDB	Memory		NDB
Storage limits	No	64TB	Yes	No	Yes	No
Transaction		√				√
MVCC		√		√	√
Lock particle size	Table	Row	Table	Row	Row	Page
B-Tree Index	√	√	√		√	√
Hash index		√	√		√
Full-Text Indexing	√	5.6 Support E-Text
Cluster index		√
Data caching		√	√		√
Index cache	√	√	√		√
Data compression	√			√
BULK INSERT	High	Relatively low	High	Very high	High	High
Memory consumption	Low	High	In	Low	High	Low
FOREIGN key support		√
Replication support	√	√	√	√	√	√
Query cache	√	√	√	√	√	√
Backup recovery	√	√	√	√	√	√
Cluster support					√

TOKUDB: Support for data compression, one engine for high-speed writing, but not for update-multiple scenarios

INFOBRIGHT/INFINIDB: Columnstore-based engine for OLAP environments, Infobright Community Edition supports only load data operations

Choose the storage engine and recommendations: according to different business to choose the right business storage engine, MySQL storage engine many, different libraries, different tables are supported to select different storage engine, the same library is recommended to use the same storage engine, because the different storage engine tables between the join operation is slow

Common recommendation: Innodb, non-special scenes, Innodb storage engine can generally meet the requirements

If there is big data written to the bulk read operation: tokudb

For OLAP you can consider using Infinidb/infobright

If the data volume is small and requires fast, no persistence requirement: Memory

Try not to select the MyISAM storage engine: Because the MyISAM storage engine can only use a single CPU, memory can only use 4 G, only index in memory, and poor concurrency

This article is from the "Small Fish Blog" blog, please be sure to keep this source http://395469372.blog.51cto.com/1150982/1726147

MySQL storage engine features and selection recommendations