Differential analysis of MySQL InnoDB and MyISAM data engines
- 2020-05-10 23:01:58
- OfStack
Tables of type MyISAM emphasize performance, and the number of executions is faster than those of type InnoDB, but do not provide transaction support, while InnoDB provides transaction support and advanced database functions such as external keys.
MyIASM is a new version of the IASM table with the following extensions:
Portability of the base 2 level.
NULL column index.
There are fewer fragments for elongated rows than for the ISAM table.
Support for large files.
Better index compression.
Better keys? Statistical distribution.
Better and faster auto_increment processing.
Here are some details and implementation differences:
1.InnoDB does not support indexes of type FULLTEXT.
2.InnoDB does not store the exact number of rows in the table, that is, when select count(*) from table is executed, InnoDB scans the entire table once to calculate the number of rows, but MyISAM simply reads the number of saved rows. Note that when the count(*) statement contains the where condition, the operations for both tables are identical.
3. For fields of type AUTO_INCREMENT, InnoDB must contain an index for that field only, but in the MyISAM table, it is possible to set up a joint index with other fields 1.
4. When DELETE FROM table, InnoDB will not re-establish the table, but delete 1 row and 1 row.
5. The LOAD TABLE FROM MASTER operation does not work for InnoDB. The solution is to first change the InnoDB table to MyISAM table and then to InnoDB table after importing the data, but not for tables that use additional InnoDB features (such as foreign keys).
In addition, the row lock of InnoDB table is not absolute. If MySQL is not sure about the scope to be scanned when executing an SQL statement, InnoDB table will also lock the full table, such as update table set num=1 where name like "%aaa%".
Any table is not a panacea. Only by selecting the appropriate table type according to the appropriate business type can MySQL take full advantage of its performance.
MyIASM is a new version of the IASM table with the following extensions:
Portability of the base 2 level.
NULL column index.
There are fewer fragments for elongated rows than for the ISAM table.
Support for large files.
Better index compression.
Better keys? Statistical distribution.
Better and faster auto_increment processing.
Here are some details and implementation differences:
1.InnoDB does not support indexes of type FULLTEXT.
2.InnoDB does not store the exact number of rows in the table, that is, when select count(*) from table is executed, InnoDB scans the entire table once to calculate the number of rows, but MyISAM simply reads the number of saved rows. Note that when the count(*) statement contains the where condition, the operations for both tables are identical.
3. For fields of type AUTO_INCREMENT, InnoDB must contain an index for that field only, but in the MyISAM table, it is possible to set up a joint index with other fields 1.
4. When DELETE FROM table, InnoDB will not re-establish the table, but delete 1 row and 1 row.
5. The LOAD TABLE FROM MASTER operation does not work for InnoDB. The solution is to first change the InnoDB table to MyISAM table and then to InnoDB table after importing the data, but not for tables that use additional InnoDB features (such as foreign keys).
In addition, the row lock of InnoDB table is not absolute. If MySQL is not sure about the scope to be scanned when executing an SQL statement, InnoDB table will also lock the full table, such as update table set num=1 where name like "%aaa%".
Any table is not a panacea. Only by selecting the appropriate table type according to the appropriate business type can MySQL take full advantage of its performance.