MySQL storage engine MyISAM and InnoDB's pros and cons

Using MySQL will of course have access to the MySQL storage engine, which you will see when creating a new database and creating a new data table.

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

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 have a detailed understanding, and then according to their own circumstances to choose a lot easier.

	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 (possibly multiple files, or stand-alone tablespace files), and the size of the InnoDB table is limited only by the size of the operating system file, typically 2GB
Storage space	MyISAM can be compressed, storage space is small	The InnoDB table requires more memory and storage, and it establishes its dedicated buffer pool in main memory for caching data and indexes
Portability, backup, and recovery	Because MyISAM data is stored as files, it is convenient for cross-platform data transfer. Can operate on a single table for backup and recovery	The free solution can be copying data files, backing up Binlog, or using mysqldump, which is relatively painful when the amount of data reaches dozens of G.
Transaction security	Atomicity of each query is not supported	Supports transaction security with transaction (commit), rollback (rollback), and crash-repair capability (crash recovery capabilities) (Transaction-safe (ACID compliant)) table
Auto_increment	MyISAM tables can be combined with other fields to establish a federated index	The InnoDB must contain only the index of the field
SELECT	MyISAM more Excellent
INSERT		InnoDB more Excellent
UPDATE		InnoDB more Excellent
DELETE		InnoDB it does not re-establish the table, but deletes one row at a
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, it requires progressive scan statistics, it is very slow
COUNT with WHERE	The same	, InnoDB also locks the table
Lock	Only table locks are supported	Support table lock, row lock row lock greatly improves the new performance of multi-user concurrent operation. But the innodb of a row lock, just where the primary key is valid, where the non-primary key is locked in the full table
FOREIGN key	Not supported	Support
Fulltext Full-text index	Support	Not supported can get full-text indexing from InnoDB by using Sphinx, slower

In general, MyISAM and InnoDB each have their own merits and demerits, each have their own use of the environment.

But InnoDB's design goal is to deal with a large-capacity database system whose CPU utilization is not comparable to other disk-based relational database engines.

I think the use of InnoDB can handle more complex situations, especially if concurrency is more efficient than MyISAM. Combined with memcache, you can also cache select to reduce select queries, which improves overall performance.

This article is from "Xiao Yang" blog, please be sure to keep this source http://aqiang.blog.51cto.com/6086626/1896091

MySQL storage engine MyISAM and InnoDB's pros and cons