1. Transaction ProcessingInnoDB supports transactional features, MyISAM not supported. the Myisam runs faster and performs better.
2,select, UPDATE, INSERT, delete operationMyISAM:assume that you run a large number of select. MyISAM is a better choice.InnoDB:Suppose your data runs a large number of inserts or update, for performance reasons. You should use the InnoDB table
3. different locking mechanisms InnoDB is a row-level lock and MyISAM is a table-level lock. Note: When the database is unable to determine the row that is being searched. The entire table will also become locked. 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 simply read out the number of rows saved and note that. The operation of the two tables is the same when the count (*) statement includes the Where condition
InnoDB :the detailed rows of the table are not saved in InnoDB, that is. When you run SELECT COUNT (*) from table. InnoDB to scan through the entire table to calculate how many rows
5, different physical structure MyISAM : Each MyISAM is stored as three files on disk. The first file name starts 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:The disk-based resource is the 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 making a select, to maintain more than the MyISAM engine is much more;
1) data block, InnoDB to cache, MyISAM just cache the index block , which also has the decrease of the swap-in ;
2) InnoDB addressing to map to a block, and then to a row. MYISAM record directly is the offset of the file, positioning is faster than the 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) Multiple version concurrency controls InnoDB: By adding two additional hidden values for each row of records to implement MVCC, these two values are a record of when this row of data was created. The other one records when this line of data expires (or is deleted). However, InnoDB does not store the actual time at which these events occur, but instead only stores the version of the system when those events occurred.
This is a growing number of transactions as they are created. Each transaction will record its own version of the system at the beginning of the transaction.
Each query must check whether the version of each row of data is the same as the version of the transaction.
Let's take a look at how this strategy 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 the version number of a line, which is at least as old as the version number of the transaction (i.e. its version number is not greater than the transaction's version number). This ensures that the data is present either before the transaction starts, or when the transaction is created, or when the data is changed. 2. The deleted version number of this row of data must be undefined or larger than the transaction version number. This ensures that this row of data is not deleted until the transaction is started.
So. The InnoDB is used for applications where data integrity/write performance requirements are high. MyISAM is suitable for querying applications.
Assuming that the transaction requirements are not very strong, high concurrency writes the recommended choice of MyISAM. Reasons such as the following:
- The index and data of the MyISAM are separate. And the index is compressed, the memory usage is correspondingly improved a lot. Can load many other indexes, and InnoDB is that the index and data are tightly bound. No compression is used, which can cause InnoDB to be larger than the MyISAM volume.
- The InnoDB storage engine spends a lot of other overhead on maintaining integrity and maintaining transactions when inserting data, so the efficiency is lower than MyISAM.
- According to the description of the main topic, the main data is inserted. And there is only one table, the late operation of the table is mainly to query it, in terms of query speed, MyISAM than InnoDB more superior. And there are MyISAM indexes. Can be very good to optimize the query speed.
InnoDB and MyISAM differences in MYSQL storage engine (network grooming)
