The difference between InnoDB and MyISAM in MySQL

InnoDB and MyISAM are the two most common table types that many people use when using MySQL, both of which have pros and cons, and are different after 5.7.

1. Transactions and foreign keys
InnoDB has transactional, rollback, crash-repair capabilities and multiple versions of concurrent transaction security, including acid. If you need to perform a large number of insert or update operations in your app, you should use InnoDB, which can improve the performance of multiple user concurrency operations
MyISAM Manage non-transactional tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. MyISAM is a better choice if you need to perform a large number of select queries in your application
2, Fulltext
InnoDB does not support full-text indexing, if necessary, it is best to use Sphinx. MyISAM is not very good at Chinese support
3. Lock

MySQL supports three kinds of locking levels, row level, page level, table level, INNOFB support row level lock, MyISAM support table-level lock
MyISAM, table lock. Provides row lock (locking on row level), provides non-lock read consistent with Oracle type (non-locking read in selects)
The row lock of the InnoDB table is also not absolute, and if MySQL cannot determine the range to scan when executing an SQL statement, the InnoDB table also locks the full table.
For example, Update table set num=1 where name like "%aaa%"

4. Storage

The MyISAM is stored as three files on disk. The first file name begins with the name of the table, the extension indicates the file type, the. frm file stores the table definition, and the data file has an extension of. MYD, the extension of the index file is. MYI and InnoDB, disk-based resources are InnoDB tablespace data files and its log files, InnoDB table size is limited only by the size of the operating system files, generally 2GB

MyISAM tables are saved as files, and using MyISAM storage in cross-platform data transfer saves a lot of hassle

5. Index

InnoDB used clustered index, index is data, sequential storage, and MyISAM use of non-clustered index, index and file separation, random storage

6. Other details

1) The exact number of rows in the table is not saved in InnoDB, note that the operation of the two tables is the same when the count (*) statement contains the Where condition
2) for fields of type auto_increment, InnoDB must contain only the index of the field, but in the MyISAM table, you can establish a federated index with other fields, if you specify a auto_increment column for a table, The InnoDB table handle in the data dictionary contains a counter called the autogrow counter, which is used to assign a new value to the column. The autogrow counter is stored only in main memory, not the disk
3) Delete from table, InnoDB does not reestablish table, but deletes one row at a time
4) The LOAD table from master operation has no effect on InnoDB, the workaround is to first change the InnoDB table to MyISAM table, import the data and then change it to the InnoDB table, but not for tables that use additional InnoDB features such as foreign keys
5) If performing a large number of select,myisam is a better option, if your data performs a large number of inserts or update, for performance reasons, you should use the InnoDB table

7, why MyISAM will be faster than the InnoDB query speed

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
InnoDB: Implement MVCC by adding two additional hidden values for each row of records, one to record when this row of data was created, and another to record when this row of data expires (or is deleted). However, InnoDB does not store the actual time at which these events occur, but instead only stores the system version number when these events occur. This is a growing number of transactions as they are created. Each transaction will record 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 the version number of the transaction. Let's take a look at how this policy is applied to specific operations when the isolation level is repeatable READ:
SELECT InnoDB must have each row of data to ensure that it complies with two conditions:
1. InnoDB must find a line version that is at least as old as the version of the transaction (i.e. its version number is not greater than the version number of the transaction). This ensures that the data is present either before the transaction starts, or when the transaction is created, or when the row data is modified.
2. The deleted version of this line of data must be undefined or larger than the transaction version. This ensures that this row of data is not deleted before the transaction begins.

The difference between InnoDB and MyISAM in MySQL