Differences between Mysql InnoDB and MyISAM

InnoDB and MyISAM are the two most common table types used by many people when using MySQL. The two table types have their own advantages and disadvantages, depending on the specific application. The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, while the InnoDB type does. MyISAM tables emphasize performance, and the execution speed is faster than that of InnoDB, but transactions are not supported. InnoDB provides advanced database functions such as external keys for transactions. The following are some details and specific implementation differences: 1. InnoDB does not support FULLTEXT indexes. 2. innoDB does not store the specific number of rows in the table. That is to say, when you execute select count (*) from table, InnoDB needs to scan the entire table to calculate the number of rows, however, MyISAM simply needs to read the number of lines saved. Note that when the count (*) statement contains the where condition, the operations on the two tables are one or three. for fields of the AUTO_INCREMENT type, InnoDB must contain only the index of this field. However, in the MyISAM table, you can create a joint index with other fields. 4. When deleting FROM table, InnoDB does not create a new table, but deletes a row. 5. the load table from master operation does not work for InnoDB. The solution is to first change the InnoDB TABLE to the MyISAM TABLE, and then change the imported data to the InnoDB TABLE, however, it is not applicable to tables that use additional InnoDB features (such as foreign keys. In addition, the row lock of the InnoDB table is not absolute. If MySQL cannot determine the scope to be scanned when an SQL statement is executed, the InnoDB table will also lock the entire table, for example, in the actual application of update table set num = 1 where name like "% aaa %" www.2cto.com, MyISAM is not necessarily faster than InnoDB because: myISAM uses table-level locks and optimistic read locks, that is, when a read operation is performed, all operations on the same table will be blocked and wait until the read operation is completed. The opposite is true, multiple write operations are also excluded by Hu Xing. However, multiple read operations read from the same table without mutual exclusion. Therefore, in this case, MyISAM performs better than InnoDB with multi-Version Control at the row level. InnoDB uses multi-version row-level locks. The so-called multi-version row-level locks, in simple terms, InnoDB will add a queue similar to a version database for no Row Records, the read operation obtains the previous version of the current operation. Ensure that the read content is the data saved in the previous operation, during the write operation, the data row of the current version is locked. After completion, the version of the data row is added to 1. In this way, the lock is applied only when a row of records is written and updated. The data of the previous version is read, which provides a higher concurrency for scenarios with frequent reads and writes. Author: sunset mangrove forest