Analysis of the advantages and disadvantages of MySQL storage engine MyISAM and InnoDB

MySQL, of course, will come in contact with MySQL's storage engine, as you will see when you create a new database and create a new datasheet.

The MySQL default storage engine is MyISAM, and the other commonly used is innodb.

As for what kind of storage engine is better? This question is inconclusive and needs to be measured in terms of your needs and circumstances. Therefore, the concept of the two engines, principles, similarities and differences and their respective advantages and disadvantages of a detailed understanding, and then choose according to their own situation is much easier.

	MyISAM	InnoDB
Storage structure	Each table is stored in three files: frm-table Definition MyD (MYData)-Data files Myi (myindex)-index file	All tables are stored in the same data file (possibly multiple files, or separate tablespace files), the size of the InnoDB table is limited to the size of the operating system file, typically 2GB
Storage space	MyISAM can be compressed with less storage space	The InnoDB table requires more memory and storage, and it creates its own dedicated buffer pool in main memory for caching data and indexing
Portability, backup, and recovery	Since MyISAM data is stored as a file, it is convenient for cross-platform data transfer. Can operate on a separate table for backup and restore	The free solution is to copy data files, backup Binlog, or use mysqldump, which is relatively painful when the amount of data reaches dozens of G.
Transaction security	Atomicity per query is not supported	Supports transaction security (Transaction-safe (ACID compliant)) tables with transaction (commit), rollback (rollback), and crash repair capabilities (crash recovery capabilities)
Auto_increment	MyISAM tables can be combined with other fields to establish a federated index	InnoDB must contain only the index of the field
SELECT	MyISAM more Excellent
INSERT		InnoDB more Excellent
UPDATE		InnoDB more Excellent
DELETE		InnoDB better it does not re-establish the table, but a row of deletes
COUNT without WHERE	MyISAM more excellent. Because MyISAM saves the exact number of rows in the table	InnoDB does not save the exact number of rows in the table, you need to scan the statistics by line, it's slow
COUNT with WHERE	The same	, InnoDB also locks the table.
Lock	Only table locks are supported	The support of table lock and row lock line lock greatly improves the new energy of multi-user concurrent operation. But the InnoDB row lock, just where the primary key is valid, the non primary key where will lock the entire table
FOREIGN key	does not support	Support
Fulltext Full-text Indexing	Support	It is not supported that you can get Full-text indexes from InnoDB by using Sphinx slower

In general, MyISAM and InnoDB each have their own advantages and disadvantages, each have their own use of the environment.

But InnoDB's design goal is to handle a large-capacity database system, which is not comparable to other disk-based relational database engines.

I think using InnoDB can deal with more complex situations, especially when concurrent processing is more efficient than MyISAM. Combined with Memcache, select can also be cached to reduce the select query, thereby improving overall performance.