Main differences between InnoDB and MyISAM In the Mysql storage engine

Source: Internet
Author: User
Tags table definition

1,Transaction Processing

Innodb supports the transaction function, which is not supported by myisam. Myisam is faster and has better performance.
2, select
, Update, insert, delete operations
MyISAM: If you execute a large number of SELECT statements, MyISAM is a better choice for InnoDB: If you execute a large number of INSERT or UPDATE statements for data, InnoDB tables should be used for performance considerations.
3. Different lock mechanismsInnoDB is a row-Level Lock and myisam is a table-Level Lock. Note: When the database cannot determine the row to be found, the entire table will also be locked. For example: Update table set num = 10 where username like "% test % "; 4. The number of rows in the query table is different.MyISAM: select count (*) from table, as long as MyISAM simply reads the number of rows saved, note that when the count (*) statement contains
When the where condition is specified, the operations on the two tables are the same.
InnoDB :  InnoDB
Does not save the specific number of rows of the table, that is, when you execute select count (*) from table, InnoDB needs to scan the entire table to calculate the number of rows
5. Different physical structuresMyISAM : Each MyISAM is stored as three files on the disk. The name of the first file starts with the name of the table. The extension indicates the file type.  . Frm file storage table definition.  The data file extension is. MYD (MYData ).    The extension of the index file is. MYI (MYIndex)InnoDB: disk-based resources are InnoDB tablespace data files and their log files. The InnoDB table size is limited by the operating system file size, generally 2 GB
6, anto_increment
Different mechanisms
  Better and faster auto_increment Processing

Others: WhyCompared with Innodb, MyISAM
. INNODB requires much more maintenance than the MYISAM engine during SELECT;
1) Data blocks. INNODB needs to be cached. MYISAM only caches index blocks,   There is also a reduction in the exchange rate;
2) innodb addressing is mapped to blocks and then to rows. MYISAM records the file OFFSET directly, which is faster than INNODB.
3) INNODB also needs to maintain MVCC consistency. Although you do not have a scenario, INNODB still needs to check and maintain MVCC (Multi-Version Concurrency Control) Multi-Version Concurrency Control. InnoDB: MVCC is implemented by adding two additional hidden values to each row of records. These two values record when this row of data is created, the other one records when this row of Data expires (or is deleted ). However, InnoDB does not store the actual time when these events occur. Instead, it only stores the system version number when these events occur. This is a number that continues to grow with the creation of transactions. Each transaction records its own system version number at the beginning of the transaction. Each query must check whether the version number of each row of data is the same as that of the transaction. Let's take a look at how this policy applies to specific operations when the isolation level is repeatable read: SELECT InnoDB must have each row of data to ensure that it meets two conditions: 1. InnoDB must find a row version. It must be at least as old as the transaction version (that is, its version number is not greater than the transaction version number ). This ensures that this row of data exists no matter before the transaction starts, when the transaction is created, or when the row of data is modified. 2. The deleted version of this row of data must be undefined or larger than the transaction version. This ensures that the row of data is not deleted before the transaction starts. Therefore, InnoDB is used for applications with high data integrity/Write Performance requirements. MyISAM
Suitable for query applications. Appendix:

The most direct method is to specify the storage engine type when creating a table, as shown below:

CREATE
TABLE mytable (id int, titlechar(20)) ENGINE = INNODB

Modify the storage engine of a table:

ALTER
TABLE engineTest ENGINE = INNODB;

Modify the default storage engine:

In the mysql configuration file (/etc/my. cnf in linux), add default-storage-engine = INNODB after mysqld.

However, if MyISAM is used to create a table and you want to change the storage engine of the entire database table, you generally need to modify one table and one table, which is cumbersome. You can export the database first to obtain the SQL statement, modify MyISAM to INNODB and then import it.

Related Article

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.