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.
| Engine name | Special |
|---|---|
| MyISAM | Commonly used . Engines with high read efficiency |
| InnoDB | are commonly used. Writing, supporting offices, etc. are all supported |
| Archive | Not commonly used. Archive engine, compression ratio up to 1:10, used for data archiving |
| NDB | is not commonly used. Mainly used in MySQL cluster servers, without introduction |
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.
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.
