Mysql5.5-innodb and Mysql5.1-myiam engine differences

Just browsing the MySQL related documents, see INNODB and Mysam engine features and differences, feel good, I first excerpt down.

features of the MyISAM engine:

1, heap organization table;
2, do not support business;
3, data files and index files are stored separately;
4, support full-text indexing;
5, the primary key index and the level two index are exactly the same as the B + Tree data structure, only if the only difference
(primary keys and unique indexes have unique properties, and other normal indexes do not have unique properties.) B + Tree leaf nodes are stored in row pointer that point to the rows of records;
6, there are special counters record the current number of records;
7, does not support crash recovery;
8, the index file is easily damaged;

Features of the InnoDB engine:

1, index organization table;
2, support business;
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 level two index data structure are all B + trees, but the leaf node stores the key value is not the same
(The leaf node of the primary key stores the entire row of data, so it is also called a clustered index, whereas the leaf node of the level two index stores the key value of the primary key)
5, support crash recovery;
6, the same data volume, InnoDB table space file size is about MyISAM engine 1.5~2 times;

Considerations for MyISAM Steering INNODB

1, MyISAM in the primary key index, you can use the self-increment column in the non-first column (not the first field), while the InnoDB primary key index contains the self-increment column, must be at the front; This feature is designed to "grab the floor" function in the Discuz forum, so if you have a similar business, It is not possible to convert the table from MyISAM to InnoDB, which requires a self-adapting implementation (we will change it to Redis);

2. When the total record count is not frequently counted (SELECT COUNT (*) from TAB), the InnoDB is relatively slow, while the MyISAM is fast; however, if it is based on the index condition of the statistics, then the difference is not small;

3, InnoDB before 5.6 does not support full-text indexing, but this does not matter, no one will be in MySQL directly run the full-text index, especially the Chinese full-text index (before the development of students have to ask the demand directly by me), indeed there is a need, you can use Sphinx, Lucene and other programs to achieve;

4, a one-time import of large amounts of data and subsequent processing, you can first import into the MyISAM engine table, after a pass processing finished, 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 load TABLE from master syntax (but it should be used by very few people);

MyISAM turn into InnoDB can enjoy the benefits of

1, full transaction feature support, and higher data concurrent access efficiency, that is, higher TPS;

2, the database instance after the abnormal restart, 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 than double or even more;

3, higher data read performance, because InnoDB the data and indexes are cached in memory, and MyISAM only cache the index;

4, INNODB support foreign keys (but in MySQL, it should be very few people use the foreign key)

http://imysql.com/

Modifying the MY.CNF configuration does not take effect

http://oldboy.blog.51cto.com/2561410/1122867

Environment variables

Backup data not successful-a when the database was specified

This article from "As always" blog, declined reprint!

Mysql5.5-innodb and Mysql5.1-myiam engine differences