The difference between InnoDB and MyISAM storage engines of MySQL databases
MySQL databases have multiple storage engines, but the two most commonly used are InnoDB and MyISAM.
There are many differences between them, and the main characteristics and differences are explained in detail below.
InnoDB
Transaction support：
- Support transactions: InnoDB supports four major features: ACID (atomicity, consistency, isolation, and persistence), which can ensure the reliability of transactions.
- Rollback: Support rollback and crash recovery.
Foreign keys：
- Support for foreign keys:InnoDB supports foreign keys and reference integrity constraints, allowing relationships between multiple tables.
Row-level locking：
- Row-level lock: InnoDB supports row-level locking, with good concurrency control performance, suitable for high concurrency write operation scenarios.
Crash recovery：
- Crash recovery: Provides a crash recovery mechanism to restore data after a system crash through its log files (redo log and undo log).
Tablespace：
- Tablespace management: InnoDB uses tablespaces to manage data files.
File format：
- Data files and index files: Stored in tablespace files, usually with the .ibd extension.
MyISAM
Transaction support：
- No transaction supported: MyISAM does not support transactions, so it does not have the ACID feature of transactions.
Foreign keys：
- Foreign keys are not supported: MyISAM does not support foreign key constraints and cannot guarantee reference integrity.
Table-level locking：
- Table-level lock: MyISAM uses table-level locking, and has poor concurrent write performance, which is suitable for scenarios with frequent read operations.
Crash recovery：
- Simple recovery mechanism: MyISAM has poor crash recovery ability and can only be easily restored through inspection and repair tools.
Tablespace：
- Separate table files: Each table has three separate files: .frm (table structure), .MYD (data file), and .MYI (index file).
Full text index：
- Full text index: MyISAM provides built-in full-text search function, suitable for some applications that require full-text search.
Summary of the difference
| characteristic | InnoDB | MyISAM |
|---|---|---|
| Transaction support | Support ACID transactions | Not supported |
| Foreign key support | support | Not supported |
| Locking mechanism | Row-level Locking | Table-level locking |
| Crash recovery | Log files support crash recovery | Only simple inspection and repair tools are supported |
| Tablespace management | Using tablespace files | Each table has a separate table structure and data index file |
| File extension | .ibd (data and index) | .frm, .MYD, .MYI |
| Full text index | Support begins with MySQL 5.6 | Built-in full-text index support |
| Data Integrity | Supports reference constraints and foreign keys | Reference integrity check is not supported |
| Data reading speed | Suitable for high concurrent write operations, slow reading speed | Fast reading speed, suitable for scenarios where more reads and fewer writes |
| Applicable scenarios | Systems with high reliability requirements such as banking and finance | Systems with many reading operations such as logs and data warehouses |
Summarize
The above is personal experience. I hope you can give you a reference and I hope you can support me more.