When you understand the difference between two different engines, it's easy to know what the key points are.
In general, the points of attention from MyISAM to InnoDB are:
1, in the primary key index of MyISAM, you can use the self-add column in a non-first column (not the first field), and the InnoDB primary key index must be at the top when it contains an additional column; This feature is designed for the "grab building" feature in the Discuz forum, so if you have a similar business, It is not possible to convert the table from MyISAM to InnoDB, which needs to be implemented by itself (we change it to Redis);
2, without the condition of frequent statistics total table total record number (SELECT COUNT (*) from TAB), InnoDB relatively slow, and MyISAM is fast; however, if the statistics based on index conditions, then the difference is small;
3, InnoDB in 5.6 did not support Full-text indexing, but this trust does not matter, no one will run directly in MySQL Full-text indexing, especially for the Chinese Full-text index (the former has developed students to ask the need to be directly by me), indeed, if necessary, you can use Sphinx, Lucene and other programs to achieve;
4, a one-time import of a large number of data and follow-up processing, you can first import into the MyISAM engine table, after a processing processing, and then import InnoDB table (I used this method in the business to improve the data batch import and processing efficiency);
5, InnoDB does not support the load TABLE from master syntax (but should also be very few people use it);
The benefits of converting from MyISAM to InnoDB are:
1, full transaction characteristics support, as well as higher data concurrency access efficiency, that is, higher TPS;
2, the database instance restarts, the InnoDB table can be repaired automatically, and the speed is relatively faster, and MyISAM need to be triggered to repair, and relatively time-consuming may be more 4~5 times or more;
3, higher data read performance, because InnoDB the data and index cache in memory, while MyISAM only cached index;
4, InnoDB support foreign key (but in MySQL, should be very few people use foreign key);
Important differences between the two engines see below:
Features of the MyISAM engine:
1, the heap organization table;
2, do not support business;
"Tn-powered-by-xiumi" >3, data files and index files stored separately;
"Tn-powered-by-xiumi" >4, support full-text indexing;
The "Tn-powered-by-xiumi" >5, primary key index and level two index are exactly the same as the data structure of a B + tree, with only a single difference (primary key and unique index have unique attributes, and other normal indexes have no unique attributes.) B + Tree leaf nodes are stored in row pointer that point to the line record;
6, there are special counters record the current number of records;
7, does not support crash recovery;
8, the index file is very easy to damage;
Features of the InnoDB engine
1, index organization table;
2, support services;
"Tn-powered-by-xiumi" >3, data files, and index files are stored in the same table space;
The "Tn-powered-by-xiumi" >4, before 5.6, does not support full-text indexing;
The "Tn-powered-by-xiumi" >5, primary key and two-level index data structures are all B + trees, but the leaf nodes store different key values (the primary Key's leaf node stores the entire row of data, so is also called a clustered index; the leaf node of the level two index stores the key value of the primary key).
5, support crash recovery;
6, the same amount of data, InnoDB table space file size is about MyISAM engine 1.5~2 times;