MySQL datasheet type MyISAM go to InnoDB considerations

At present, most of the business scene with InnoDB has been completely done, more and more business from MyISAM to InnoDB engine, then what are the considerations?

When you understand the difference between the two 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;
3, data files and index files stored separately;
4, support full-text indexing;
5, primary key index and level two index is exactly the same as the data structure of the B + tree, only the only difference (primary key and unique index have unique attributes, 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;
3. Data files and index files are stored in the same table space;
4. Before 5.6, full-text indexing is not supported;
5, the primary key and the two-level index data structure are all B + trees, but the leaf node stores a different key value (the primary key leaf node stores the entire row of data, so also known as a clustered index; the leaf node of the two-level 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;