Advantages and disadvantages of MySQL storage engine MyISAM and InnoDB

Common Data Storage Methods in mysql include MyISAM and InnoDB. We don't need to think about any other memory. Let's introduce the advantages and disadvantages of MyISAM and InnoDB.

When using MySQL, you will certainly access the MySQL storage engine, which will be displayed when you create a database or a new data table.

MySQL's default storage engine is MyISAM, and other commonly used storage engines are InnoDB.

Which storage engine is better? This issue is inconclusive and needs to be measured based on your needs and environment. Therefore, after having a detailed understanding of the concepts, principles, similarities and differences of the two engines, it is much easier to choose based on your own situation.

	MyISAM	InnoDB
Storage Structure	Each table is stored in three files: Frm-table definition MYD (MYData)-Data File MYI (MYIndex)-index file	All tables are stored in the same data file (or multiple files, or independent tablespace files). The InnoDB table size is limited by the operating system file size, generally 2 GB
Storage space	MyISAM can be compressed with a small storage space	InnoDB tables require more memory and storage. It creates a dedicated buffer pool in the primary memory for high-speed data buffering and indexing.
Portability, backup and recovery	MyISAM data is stored as files, so it is convenient to transfer data across platforms. You can operate on a table separately during backup and recovery.	The free solution can be copying data files, backing up binlogs, or using mysqldump, which is relatively painful when the data volume reaches dozens of GB.
Transaction Security	It does not support atomicity of each query.	Supports transaction-safe (ACID compliant) tables with transaction (commit), rollback, and crash recovery capabilities
AUTO_INCREMENT	The MyISAM table can create a joint index with other fields.	InnoDB must contain only the index of this field
SELECT	Better MyISAM
INSERT		InnoDB is better
UPDATE		InnoDB is better
DELETE		InnoDB is better. It does not create a new table, but deletes a row.
COUNT without WHERE	MyISAM is better. Because MyISAM saves the specific number of rows in the table	InnoDB does not store the specific number of rows in the Table. Therefore, it takes a long time to scan statistics row by row.
COUNT with WHERE	Same	Similarly, InnoDB locks the table.
Lock	Only table locks are supported.	Table locks and row locks greatly improve the performance of concurrent operations by multiple users. However, the row lock of InnoDB is valid only in the WHERE primary key. The WHERE lock of non-primary keys locks the entire table.
Foreign key	Not Supported	Supported
FULLTEXT full-text index	Supported	It is not supported to obtain full-text indexes from InnoDB by using sphenders, which slows down a bit.

In general, MyISAM and InnoDB have their own advantages and disadvantages and their respective use environments.

However, InnoDB is designed to handle large-capacity database systems, and its CPU utilization is incomparable to other disk-based relational database engines.

I think InnoDB can be used to deal with more complex situations, especially for concurrent processing than MyISAM. Combined with memcache, you can also cache SELECT statements to reduce SELECT queries and improve overall performance.