The main difference between Mysql storage engine InnoDB and MyISAM

Tag: Store default control returns the start of the unreachable scheme trunc

MySQL is MyISAM by default.

1, transaction processing

InnoDB supports transactional features, MyISAM not supported.
Myisam executes faster and performs better.

MyISAM does not support transactions, while InnoDB supports. InnoDB autocommit By default is open, that is, each SQL statement will be encapsulated into a transaction by default, automatically commit, which will affect the speed, so it is best to put a number of SQL statements between Begin and commit, compose a transaction to commit.

2,select, UPDATE, INSERT, delete operation

MyISAM: If performing a large number of select,myisam is a better choice.
InnoDB: If your data performs a large number of inserts or update, you should use the InnoDB table for performance reasons. Delete is InnoDB better on performance, but when you delete from table, InnoDB does not re-establish the table, but deletes one row at a time, and if you want to empty a table that holds a large amount of data on InnoDB, it is best to use the TRUNCATE TABLE command.

3, locking mechanism is different

InnoDB is a row-level lock and MyISAM is a table-level lock.
Note: When the database is not determined, the row you are looking for is also locked into the entire table.
such as: Update table Set num = ten where username like "%test%";

4, the number of rows in the query table is different

Myisam:select count () from Table,myisam as long as the number of rows saved is simply read, it is important to note that when the count () statement contains the Where condition, the operation of the two tables is the same

The exact number of rows in a table is not saved in Innodb:innodb, that is, when you execute select COUNT (*) from table, InnoDB scans the entire table to calculate how many rows

No where count () uses MyISAM much faster than InnoDB. Because MyISAM has a built-in counter, COUNT () reads directly from the counter, and InnoDB must scan the entire table. Therefore, when you execute count () on InnoDB, you typically accompany where, and where you want to include an index column other than the primary key. Why is this special emphasis on "outside the primary key"? Because primary index in INNODB is stored with raw data, secondary index is stored separately and a pointer to primary key. So just count () is faster with secondary index scans, while primary key is mostly useful when scanning indexes while returning raw data.

5, different physical structure

MyISAM: Each MyISAM is stored on disk as three files. The first file name begins with the name of the table, and the extension indicates the file type.
The. frm file stores the table definition.
The data file has an extension of. MYD (MYData).
The extension of the index file is. MYI (Myindex)

InnoDB: A disk-based resource is a InnoDB tablespace data file and its log file, and the size of the InnoDB table is limited only by the size of the operating system file, typically 2GB

6. Storage space

MyISAM: Can be compressed, storage space is small. supports three different storage formats: Static table (default, but note that there can be no space at the end of the data, it will be removed), dynamic tables, compressed tables.
InnoDB: Requires more memory and storage, it establishes its dedicated buffer pool in main memory for caching data and indexes.

Different 6,anto_increment mechanisms

Better and faster auto_increment processing

7, Index

Full-text indexing refers to the backward-sort index of each word in char, varchar, and text (except for the inactive word). MyISAM's full-text index is actually useless, because it does not support Chinese word segmentation, must be used by the user to add a space after the word and then write to the data table, and less than 4 Chinese characters will be ignored as the word stop.

InnoDB supports foreign keys, MyISAM not supported.
MyISAM: Supports full-text indexing of fulltext types
InnoDB: Full-text indexing of fulltext type is not supported, but InnoDB can support full-text indexing using Sphinx plug-in and works better.

8, Other: Why MyISAM is faster than InnoDB's query.

InnoDB when doing select, to maintain more than the MyISAM engine much more;
1) data block, InnoDB to cache, MyISAM only cache the index block, which also has the reduction of swap-out;
2) InnoDB addressing to map to the block, and then to the line, MYISAM record is directly the offset of the file, positioning is faster than InnoDB
3) InnoDB also need to maintain MVCC consistent; Although your scene is not, he still needs to check and maintain
MVCC (multi-version Concurrency Control) multi-version concurrency controls

9. Portability, backup and recovery
MyISAM: Data is stored as a file, so it is convenient for cross-platform data transfer. You can operate on a table separately for backup and recovery.
InnoDB: 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.

The main difference between Mysql storage engine InnoDB and MyISAM