Home > Article > Backend Development > Summary of the difference analysis between InnoDB and MyISAM in mysql_PHP tutorial
Summary of the difference analysis between InnoDB and MyISAM in mysql_PHP tutorial
- WBOYOriginal
- 2016-07-21 15:52:401087browse
MyIASM is a new version of the IASM table with the following extensions:
Binary level portability.
NULL column index.
Has less fragmentation than ISAM tables for variable-length rows.
Support large files.
Better index compression.
Better statistical distribution of keys.
Better and faster auto_increment handling.
The following are some details and specific implementation differences:
1.InnoDB does not support FULLTEXT type indexes.
2. InnoDB does not save the specific number of rows in the table. That is to say, when executing select count(*) from table, InnoDB has to scan the entire table to calculate how many rows there are, but MyISAM only needs to simply read and save them. The number of rows is enough. Note that when the count(*) statement contains the where condition, the operations of the two tables are the same.
3. For fields of type AUTO_INCREMENT, InnoDB must contain an index with only this field, but in the MyISAM table, a joint index can be established with other fields.
4. When DELETE FROM table, InnoDB will not re-create the table, but will delete it row by row.
5. The LOAD TABLE FROM MASTER operation does not work for InnoDB. The solution is to first change the InnoDB table to a MyISAM table, and then change it to an InnoDB table after importing the data. However, for the additional InnoDB features used (such as external Key) tables are not applicable.
In addition, the row lock of the InnoDB table is not absolute. If MySQL cannot determine the range to be scanned when executing a SQL statement, the InnoDB table will also lock the entire table, such as update table set num=1 where name like “%aaa%”
No table is omnipotent. Only by choosing the appropriate table type appropriately for the business type can the performance advantages of MySQL be maximized.