Mysql storage engine and mysql Storage

Mysql storage engine and mysql Storage

1. What is a storage engine?

A relational database is a data structure used to store and organize information. It can be understood as a table composed of rows and columns, similar to a workbook in excel. Some tables are simple, some tables are complex, and some tables do not need to store any long-term data at all. Some tables read very quickly, but are poorly inserted. In the actual development process, we may need a variety of tables. Different tables mean that different types of data are stored and there will also be differences in data processing. For mysql, it provides many types of storage engines. We can select different storage engines based on data processing needs to maximize the use of Mysql's powerful functions.

On the mysql client, run the following command to view the engines supported by Mysql.

Show engines;

2. Summary of mysql storage engine

MyISAM: When we create a MyISAM engine table, three files will be created on the local disk. The file name is the table name. For example, if I create a tb_Demo TABLE OF THE MyISAM engine, the following three files will be generated:

1. tb_Demo.frm, storage table definition

2. tb_Demo.MYD: Store Data

3. Tb_Demo.MYI, storage Index

 

MyISAM tables cannot process transactions, which means tables with transaction processing requirements cannot use the MyISAM storage engine.

 

The MyISAM storage engine is particularly suitable for the following scenarios:

1. Select-intensive tables: The MyIASM storage engine is very fast in filtering large amounts of data, which is the most prominent.

2. Insert-intensive tables: the concurrent insert feature of MyISAM allows you to select and insert data at the same time. For example, the MyISAM storage engine is suitable for right-click Management or web server log data management.

 

InnoDB is a robust transactional storage engine, which has been used by many Internet companies and provides a powerful solution for users to operate very large data. MySQL 5.6.17 is installed on my computer. InnoDB is the default storage engine. InnoDB also introduces row-level locking and foreign key constraints. InnoDB is the best choice in the following scenarios:

1. Update-intensive tables: the InnoDB Storage engine is particularly suitable for processing multiple concurrent update requests.

2. Transactions: the InnoDB Storage engine is a standard MySQL storage engine that supports transactions.

3. Automatic disaster recovery: different from other storage engines, InnoDB tables can be automatically recovered from disasters.

4. Foreign key constraints: MySQL only supports the storage engine InnoDB

5. Support automatic columns of AUTO_INCREMENT attributes

 

In general, InnoDB is a good choice if you need transaction support and a high concurrent reading frequency.