Mysql table engine

The power of MySQL lies in its plug-in storage engine. We can use different storage engines based on the characteristics of the table to achieve the best performance.

If you are familiar enough and have certain work experience. You can also use the MySQL engine open sourced by Alibaba and NetEase in your own server.

You can learn the statements for creating tables in the following section "Database Structure Definition Statements". When mysql creates a table, you can specify the corresponding engine.

Use in the mysql command:

show engines;

You can view all engines supported by the current server.

We introduce several commonly used engines and learn about several less commonly used engines. Avoid seeing some engines that don’t know the concepts in actual work in the future.

MyISAM

does not support transactions, table locks (table-level locks, locking will lock the entire table), supports full-text indexing, and operates quickly. Often used for businesses that read a lot.

1. Myisam storage engine table consists of myd and myi. .myd is used to store data files, and .myi is used to store index files.
2. For myisam storage engine tables, the mysql database only caches its index files, and the caching of data files is completed by the operating system itself.

InnoDB

1. supports transactions, mainly for online transaction processing (OLTP) applications.
2. Row lock design supports foreign keys, that is, read operations are not locked by default.

InnoDB is designed for maximum performance when processing huge amounts of data.

Note:
Row lock: Lock this row during write and update operations to prevent others from operating it.
Table lock: During write and update operations, lock the table to prevent others from operating it.
Transaction: Operate multiple data at the same time, if one of the data operations fails. Can roll back to before the operation. Commonly used in banking, e-commerce, finance and other systems.