MySQL's storage engine

For MySQL, what form of data is ultimately saved? And where does the data remain on the hard drive?

1. mysql Storage engine

MySQL is a data management system that includes a database, is responsible for storing data, and a database access management interface system that manages the database.

To meet different business needs, MySQL's database uses a variety of storage engines for data storage.

2. What is the storage engine?

The storage engine specifies the storage type of the table, that is, how the data is stored and indexed, whether transactions are supported, and so on. At the same time, the storage engine determines how the table is stored on the computer.

My MySQL Server version is 5.7, the supported storage engine is the following table, default is InnoDB

　　

3, InnoDB and MyISAM the respective basic characteristics and use occasions:

MyISAM: does not require transaction processing to access the main application suitable for the use of the engine;

InnoDB: Supports transaction control and consumes more disk space than MyISAM. Suitable for frequent update, delete operation, but also the integrity of the transaction requirements are relatively high, need to implement concurrency control applications.

Function	InnoDB	MyISAM
Support Transactions	Support	Not supported
Support Full-Text indexing	Not supported	Support
FOREIGN KEY constraints	Support	Not supported
Table Space Size	Larger	Smaller
Data row Locking	Support	Not supported

　　

4, in order to achieve special functions, you can specify a table's storage engine, so the table's storage engine can also be called the table type

CREATE TABLE ' MyISAM ' (ID INT (4)) Engine=myisam;

5. What is the MySQL data file? How is it stored?

Data files differ under different storage engines.

A, the location of the data file

The default storage location for different operating system data files is different, native:datadir=c:/programdata/mysql/mysql Server 5.7/data

　　B, MyISAM type of table file, the data file has three kinds:

• • The. FRM table structure definition file, which is independent of the storage engine.
  • . MYI index file that holds the index information for the MyISAM type table.
  • . MYD data file, storing the data in the table

　　

C, InnoDB type of table file

• . frm
• Ibdata1 data file, save data for all InnoDB type tables

　　

　　

MySQL's storage engine