MySQL Learning Note (1)-Storage Engine Overview

Unlike most databases, there is a concept of a storage engine in MySQL that can choose the optimal storage engine for different storage requirements.

I. Overview of the MySQL storage engine

The plug-in storage engine is one of the most important features of MySQL database, and the user can find out how to store and index data, use transactions, etc. according to the needs of the application. MySQL supports a variety of storage engines by default to suit the needs of different domains of database applications, users can choose to choose different storage engine to provide the efficiency of the application, provide flexible storage, users can even customize and use their own storage engine to achieve the maximum degree of customization.

The storage engines supported by MySQL include MyISAM, InnoDB, BDB, Memory, MERGE, EXAMPLE, NDB Cluster, ARCHIVE, CVS, blackhole, federated, etc. where InnoDB and BDB provide transaction security tables, other storage engines are non-transactional security tables.

By default, when you create a table without specifying the table's storage engine, the table uses the default storage engine. If you need to modify the default storage engine, you can set defalut-table-type in the parameters file. To view the current default storage engine, you can use the following command:

Show variables like ' table_type ';

The storage engines supported by the current database can be queried in the following two ways:

Mysql> show engines;mysql> show variables like ' have% ';

When you create a table, you can set the storage engine for the table by adding the engine keyword:

CREATE TABLE table_name1 (  ...) Engine=myisam; CREATE TABLE table_name2 (  ...) Engine=innodb;

You can also modify an existing table into another storage engine by using the ALTER TABLE statement:

ALTER TABLE table_name ENGINE=INNODB;

Ii. the characteristics of various types of storage engines in MySQL are shown in the following table:

Characteristics	MyISAM	InnoDB	Memory	Merge	NDB
Storage limits	Yes	64TB	Yes	No	Yes
Transaction security		Support
Lock mechanism	Table lock	Row lock	Table lock	Table lock	Row lock
B-Tree Index	Support	Support	Support	Support	Support
Hash index		Support			Support
Full-Text Indexing	Support
Cluster index		Support
Data caching		Support	Support		Support
Index cache	Support	Support	Support	Support	Support
Data can be compressed	Support
Space use	Low	High		Low	Low
Memory usage	Low	High	In	Low	High
BULK INSERT Speed	High	Low	High	High	High
Support for foreign keys		Support

　

MySQL Learning Note (1)-Storage Engine Overview