1, transaction processingInnoDB supports transactional features, MyISAM not supported. Myisam executes faster and performs better.
2,select, UPDATE, INSERT, delete operationMyISAM:if executing a lot of select,myisam is a better choiceInnoDB:If your data performs a large number of inserts or update, for performance reasons, you should use the InnoDB table
3, locking mechanism is different InnoDB is a row-level lock and MyISAM is a table-level lock. Note: When the database is unable to determine which row is being searched, it becomes 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 differentMyISAM:Select COUNT (*) from Table,myisam as long as you simply read the number of rows saved, note that when the COUNT (*) statement contains a where condition, the operation of the two tables is the same
InnoDB :the exact number of rows in a table is not saved in InnoDB, that is, when you execute select COUNT (*) from table, InnoDB scans the entire table to calculate how many rows
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
different 6,anto_increment mechanismsbetter and faster auto_increment processing
Other: WhyMyISAMwill be faster than the InnoDB 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 maintainMVCC (multi-version Concurrency Control) Multi-version concurrency controlsInnoDB: 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 version of the line, at least as old as the version of the transaction ( That is, 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.
Therefore, InnoDB is used for applications with high data integrity/write performance requirements. MyISAM is suitable for querying applications.
If it is not very strong for transaction requirements, high concurrency write is recommended to select MyISAM. The reasons are as follows:
- MyISAM index and data are separate, and the index is compressed, the memory usage of the corresponding increased a lot, can load more indexes, and InnoDB is the index and data is tightly bound, not using compression resulting in InnoDB than MyISAM volume is large;
- The InnoDB storage engine spends more overhead on maintaining integrity, maintaining transactions, and therefore less efficient than myisam when inserting data.
- According to the main description, the main is to insert data, and only a table, the late operation of the table is mainly query it, in terms of query speed, MyISAM more superior than InnoDB, and there are MyISAM indexes, can be very good to optimize the query speed.
InnoDB and MyISAM differences in MYSQL storage engine (network grooming)
