MySQL Storage engine

1. What is a storage engine?

A relational database is a data structure for storing and organizing information that can be interpreted as a table of rows and columns, similar to the form of spreadsheets in Excel. Some tables are simple, some tables are complex, and some tables do not store any long-term data at all, and some tables read very quickly, but the data is inserted poorly. In the actual development process, we may need a variety of tables, different tables, which means that the storage of different types of data, data processing will also exist differences. For MySQL, it provides many types of storage engine, we can choose different storage engine according to the requirement of data processing, so as to make maximum use of MySQL powerful function.

In the MySQL client, use the following command to view the MySQL-supported engine.

SHOW ENGINES;

2. Summary of MySQL storage engine

MyISAM: When we build a MyISAM engine table, we create three files on the local disk, and the file name is the table name. For example, if I build a tb_demo table for the MyISAM engine, the following three files will be generated:

1, tb_demo.frm, storage table definition

2, TB_DEMO.MYD, storage data

3, tb_demo.myi, storage index

The MyISAM table cannot handle transactions, which means that there is a table for transaction processing requirements and cannot use the MyISAM storage engine.

The MyISAM storage engine is ideal for use in the following situations:

1. Select Intensive tables: The MYIASM storage engine is very fast in filtering large amounts of data, which is the most prominent of its kind.

2. Insert-intensive table: MyISAM's concurrent insert feature allows you to select and insert data at the same time. For example, the MyISAM storage engine is ideal for managing right-click or Web server log data.

InnoDB is a robust transactional storage engine that has been used by many Internet companies to provide a powerful solution for users to manipulate very large data. The MySQL5.6.17 version installed on my computer, InnoDB is the default storage engine. InnoDB also introduces row-level locking and foreign key constraints, which are ideal for use with InnoDB in the following situations:

1. Update-intensive tables: The InnoDB storage engine is ideal for handling multiple concurrent update requests

2. Transaction: InnoDB storage engine is a standard MySQL storage engine that supports transactions

3. Automatic Disaster recovery: Unlike other storage engines, the InnoDB table can automatically recover from a disaster

4, FOREIGN KEY constraint: MySQL support foreign key storage engine only InnoDB

5, support automatic more column Auto_increment property

In general, InnoDB is a good choice if transaction support is required and there is a high frequency of concurrent reads.

MySQL Storage engine