The difference between MySQL and MyISAM

Basic differences: The MyISAM type does not support advanced processing such as transaction processing, while the InnoDB type does. The MyISAM type of tables emphasize performance, with faster execution times than the InnoDB type, but without transaction support, while InnoDB provides transaction support and advanced database capabilities such as external keys.
Here are some details and implementation differences:
1.InnoDB does not support indexes of type FULLTEXT.
2. The specific number of rows of the table is not saved in InnoDB, that is, when select count(*) from table is executed, InnoDB has to scan the entire table 1 to calculate how many rows there are, but MyISAM simply reads out the number of rows saved. Note that when the count(*) statement contains the where condition, the two tables operate identically.
3. For a field of type AUTO_INCREMENT, InnoDB must contain an index only for that field, but in the MyISAM table, a joint index can be established with other field 1.
When DELETE FROM table, InnoDB does not rebuild the table, but deletes row 1.
5. The LOAD TABLE FROM MASTER operation does not work for InnoDB. The solution is to change the InnoDB table to MyISAM table first and then to InnoDB table after importing the data, but not for tables that use additional InnoDB features (such as foreign keys).
In addition, InnoDB table row locks are not absolute. If MySQL cannot determine the range to scan when executing 1 SQL statement, InnoDB table locks the whole table as well. For example, update table set num=1 where name like "%aaa"
Major differences: The most significant difference between the two types is that Innodb supports transaction processing versus foreign key and row-level locking. MyISAM does not support it, so MyISAM tends to be considered suitable only for small projects.
Both Innodb and MyISAM are preferred as users of MySQL, and MyISAM is preferred if the database platform is to meet the requirements of 99.9% stability, convenient scalability and high availability.
The reasons are as follows:
1. Most of the projects on the platform are projects with more reading and less writing, while THE reading performance of MyISAM is much better than that of Innodb.
2. The index and data of MyISAM are separated, and the index is compressed, so the memory utilization increases correspondingly. It can load more indexes, while Innodb is closely tied to the index and data, so it does not use compression, which makes Innodb larger than MyISAM.
3, often occurs between 1, 2 months application developers accidentally update1 table where write wrong, lead to this table can't normal use, MyISAM superiority will show up at this time, just copy from the package to take out the corresponding table files, literally in a database directory, and then dump into sql lead back to the main library, and the corresponding binlog. If it is Innodb, I am afraid it is impossible to do this fast. Don't tell me to ask Innodb to periodically back up with the exported ES80en.es81EN mechanism, because the smallest database instance has a data size of approximately 10G.
4, from the contact application logic, select count(*) and order by are the most frequent, can account for more than 60% of the total sql statement operation, and this operation Innodb is actually the table lock, many people think that Innodb is row level lock, that is only where is effective for its primary key, non-primary key will lock the table.
5, and there are often many application department I need to give them some tables of data on a regular basis, MyISAM words is very convenient, as long as you send them correspond to the table of frm. MYD, MYI files, let them in the corresponding version of the database is started, and Innodb requires export xxx. sql, because light to others files, influenced by dictionary data file, the other party is unable to use.
6, if MyISAM than insert write operation, Innodb can not reach MyISAM write performance, if it is for update operation based on index, although MyISAM may be inferior to Innodb, but so high concurrency write, from the library can catch up with a problem, it is better to solve by multi-instance sub-library sub-table architecture.
7. If MyISAM is used, THE merge engine can greatly accelerate the development speed of application departments. They only need to do 1 select count(*) operation on this merge table, which is very suitable for the business table of some type (such as log, survey and statistics) of the total amount of several hundred million of rows.
Of course, Innodb is not always used, use Innodb for items that use transactions. In addition, one might say that you can't resist too many writes with MyISAM, but you can compensate with architecture.