Notes for converting MySQL from MyISAM engine to InnoDB Engine

Notes for converting MySQL from MyISAM engine to InnoDB Engine

After analyzing the differences between the two engines, you can easily know the key points.

In general, considerations for switching from MyISAM to InnoDB include:

1. In the primary key index of MyISAM, you can use the auto-incrementing column in a non-first column (not the first field). When the primary key index of InnoDB contains the auto-incrementing column, it must be at the beginning; this feature is designed in the discuz forum for the "preemptive building" function. Therefore, if a similar business exists, the table cannot be converted from MyISAM to InnoDB, you need to implement it by yourself (we want to change it to Redis for implementation );

2. When the total number of records in the entire table is frequently counted without conditions (select count (*) from tab), InnoDB is relatively slow, while MyISAM is fast; however, if it is based on the index Condition statistics, the difference between the two is not big;

3. InnoDB did not support full-text indexing before 5.6, but I believe it doesn't matter. No one will directly run full-text indexing in MySQL, in particular, full-text indexing of Chinese characters (I did not accept the full-text indexing when a developer asked me for it a while ago). If you do need it, you can use other solutions such as sph.pdf and Lucene;

4. If you want to import a large amount of data at a time and process it later, you can import the data to the MyISAM engine table first, import the InnoDB table again (I used this method in my business to improve the efficiency of batch data import and processing );

5. InnoDB does not support the load table from master syntax (but it should be rarely used );

Benefits of converting from MyISAM to InnoDB include:

1. Support for complete Transaction Features and higher data concurrent access efficiency, that is, higher TPS;

2. After the database instance is restarted abnormally, the InnoDB table can be automatically repaired, and the speed is relatively faster. However, MyISAM needs to be triggered before it can be repaired, and the relative time consumption may be 4 ~ more ~ 5 times or more;

3. Higher Data Reading performance, because InnoDB caches data and indexes in the memory at the same time, while MyISAM only caches indexes;

4. InnoDB supports foreign keys (however, foreign keys should be rarely used in MySQL );

For details about the important differences between the two engines, see:

Features of the MyISAM engine:

1. Heap organization table;

2. transactions are not supported;

"Tn-Powered-by-XIUMI"> 3. Separate data files and index files;

"Tn-Powered-by-XIUMI"> 4. Full-text indexing is supported;

"Tn-Powered-by-XIUMI"> 5. The primary key index and secondary index are exactly the same as the data structure of the B + tree, there is only a unique difference (primary key and unique index have unique attributes, and other common indexes do not have unique attributes. B + tree leaf nodes store row pointer pointing to row records );

6. There are special counters to record the current number of records;

7. Crash recovery is not supported;

8. index files are easily damaged;

Features of the InnoDB Engine

1. index the Organizational table;

2. Supports transactions;

"Tn-Powered-by-XIUMI"> 3. data files and index files are stored in the same tablespace;

"Tn-Powered-by-XIUMI"> 4. Full-text indexing is not supported before 5.6;

"Tn-Powered-by-XIUMI"> 5. Data Structures of primary keys and secondary indexes are the same as those of B + trees, however, leaf nodes store different key values (the leaf node of the primary key stores the whole row of data, which is also called a clustered index; the leaf node of the secondary index stores the key values of the primary key)

5. Support for Crash recovery;

6. When the data volume is the same, the InnoDB tablespace file size is about 1.5 ~ of the MyISAM engine ~ 2x;