Differences between MyISAM and InnoDB

Recently, the systems in charge of the database often encounter timeout between and. The analysis shows that the number of records in the database is from to, and the database updates records in batches, resulting in frequent lock tables, at this time, any server involved in dB operations will time out. The previous volume is small and the timeout is not too serious. The number of users has increased recently.

The original table type is MyISAM. I plan to change it to InnoDB and do some homework in advance. The following describes the differences between MyISAM and InnoDB.

 

 

InnoDB and MyISAM are the two most commonly used table types in MySQL, each with its own advantages and disadvantages, depending on the specific application. The following are the known differences between the two.
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 the same.
3. 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 executing an SQL statement, the InnoDB table will also lock the entire table, for example, update table set num = 1 where name like "% AAA %"

Any type of table is not omnipotent. You only need to select a proper table type for the business type to maximize the performance advantage of MySQL.