Summary of MySQL Myisam and Innodb

Some comparisons between Myisam and Innodb of MySQL summarize some differences between Myisam and Innodb engines of MySQL. First, the two are in the file structure: Myisam will store three files :. frm storage table structure ,. MYD Stores Table data ,. the index of the MYI file storage table. Therefore, it is required to copy all three files from one table. In addition, Myisam compresses indexes. Myisam backup is easier. There are also Innodb. frm table structure files, but data storage is different. Because of the concept of table space, data files may be independent or idle. If they are independent, data and indexes will all be in one. ibd file. Transaction support: Myisam is not supported. If you really need features such as transactions and Foreign keys, discard them. Innodb supports ACID features such as transactions and rollback, and supports foreign keys. Lock: Myisam only supports table locks. If you execute a DML Statement (data manipulation language, that is, add, delete, modify, and query), the entire table will get a lock, before the operation is completed, others cannot operate on this table. Therefore, if the table concurrency is high, Myisam performance may be inferior to some Myisam support for row locks, that is, you can lock only a row rather than the entire table. Therefore, in the case of concurrency, the performance of Update operations will be better. Of course, if an SQL statement cannot determine the scan range, it also locks the table, such as the where like '%' operation. To put it simply, if Innodb uses the where to retrieve columns without indexes, the table will be locked. DML (Insert, Select, Update, Delete): Select: Generally, the read performance of Myisam is better than that of Innodb, in this case, Myisam can have extra points. Insert: in this case, Myisam is obviously faster for single-threaded operations, but not necessarily for concurrent operations. It should also be noted that Myisam supports the Insert Delayed operation. If your application can accept this method of use (it may take effect after a delay), it will be better updated: It is generally considered that Innodb is faster to Delete: innodb, which deletes a table from one row instead of recreating the table. Others: count (*): Myisam stores the specific number of rows in the table, while innodb does not, therefore, when you use phpmyadmin to view the number of rows in the table '~ 'Approximate number of rows. Therefore, select count (*) is faster to use Myisam. However, if the where condition is not added, Myisam also supports full-text indexing, for example, when searching for text fields such as where * like "% *", the performance and efficiency will be better. Auto_Increment: Innodb must contain only the index of this field, while Myisam can use the Union index. Tables created by Myisam can use the Myisam_merge engine, which is useful in some cases.