MySQL storage engine MyISAM and InnoDB's pros and cons

Source: Internet
Author: User
Tags table definition

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:
  1. frm-table Definition
  2. MYD (MYData)-Data file
  3. 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.



From for notes (Wiz)

MySQL storage engine MyISAM and InnoDB's pros and cons

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.