Differences between MySQL storage engine InnoDB and MyISAM

Basic differences:The MyISAM type does not support advanced processing such as transaction processing, whereas 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 transaction support and external keys.
The following are some differences between details and specific implementations:
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 an SQL statement is executed, the InnoDB table will also lock the entire table, for example, update table set num = 1 where name like "% aaa %"
Major differences:The main difference between the two types is that Innodb supports transaction processing and Foreign keys and row-level locks. MyISAM is not supported. Therefore, MyISAM is often considered only suitable for small projects.
As a user of MySQL, Innodb and MyISAM are both preferred. If the database platform meets the requirement of 99.9% stability, convenient scalability and high availability, MyISAM is definitely the first choice.
The reason is as follows:
1. Most projects hosted on the platform are read-write-less projects, while MyISAM has a much better read performance than Innodb.
2. MyISAM indexes and data are separated, and the indexes are compressed, so the memory usage increases a lot. More indexes can be loaded, while Innodb is closely bound with indexes and data. Without compression, Innodb is much larger than MyISAM.
3. Application developers often fail to update the where write range of a table every one or two months. As a result, the table cannot be used normally, at this time, the superiority of MyISAM is reflected. The files of the corresponding table can be retrieved from the compressed package copied on the current day, stored in a database directory, dumped into SQL, and then exported back to the master database, and add the corresponding binlog. If it is Innodb, I am afraid it cannot be so fast. Don't tell me to let Innodb regularly use the xxx. SQL export mechanism for backup, because the minimum data volume of a database instance is usually dozens of GB.
4. In terms of application logic, select count (*) and order by are the most frequent operations, which may account for more than 60% of the total SQL statements, in fact, this operation of Innodb locks the table. Many people think that Innodb is a row-Level Lock, that is, where is only valid for its primary key, and non-primary keys will lock the entire table.
5. There are often many application departments that need to regularly provide some table data to them. For MyISAM, It is very convenient to send it to the corresponding table's frm. MYD and MYI files, so that they can start the database in the corresponding version, and Innodb needs to export xxx. SQL, because the dictionary data files cannot be used by the other party.
6. If Innodb and MyISAM are compared with insert write operations, Innodb still cannot achieve the Write Performance of MyISAM. If it is an index-based update operation, although MyISAM may be inferior to Innodb, however, it is also a problem whether the database can catch up with such highly concurrent writes. It is better to solve this problem through the Multi-instance database/table sharding architecture.
7. If MyISAM is used, the merge engine can greatly accelerate the development speed of the Application Department. They only need to perform some select count (*) operations on the merge table, it is very suitable for business tables of a type of rows (such as logs, survey statistics) with a total number of hundreds of millions of projects.
Of course, Innodb is not absolutely unnecessary. Innodb is used for transaction projects. In addition, some may say that MyISAM cannot resist too many write operations, but it can be compensated by the architecture.