Shulou(Shulou.com)05/31 Report--

This article mainly explains "what's the difference between InnoDB and MyISAM". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what's the difference between InnoDB and MyISAM"?

InnoDB and MyISAM are the two most commonly used table types in MySQL, each with its own advantages and disadvantages, depending on the specific application. The basic difference is that the MyISAM type does not support advanced processing such as transactions, while the InnoDB type does. Tables of type MyISAM emphasize performance and perform several times faster than type InnoDB, but do not provide transaction support, while InnoDB provides advanced database functions such as transaction support already external keys.

MyIASM is a new version of the IASM table with the following extensions:

Portability at the binary level.

NULL column index.

There is less fragmentation for variable-length rows than for ISAM tables.

Large files are supported.

Better index compression.

Better keys? statistical distribution.

Better and faster auto_increment processing.

Here are some of the details and differences in implementation:

1.InnoDB does not support indexes of type FULLTEXT.

The specific number of rows of the table is not saved in 2.InnoDB, that is, when select count (*) from table is executed, InnoDB scans the entire table to calculate how many rows there are, but MyISAM can simply read out the number of saved rows. Note that when the count (*) statement contains the where condition, the operation of the two tables is the same.

3. For fields of type AUTO_INCREMENT, the InnoDB must contain an index with only that field, but in the MyISAM table, a federated index can be established with other fields.

When 4.DELETE FROM table, InnoDB does not re-establish the table, but deletes it row by row.

The 5.LOAD TABLE FROM MASTER operation does not work with InnoDB, and the solution is to change the InnoDB table to the MyISAM table first, and then to the InnoDB table after importing the data, but it does not apply to tables that use additional InnoDB features such as foreign keys.

In addition, the row lock of the InnoDB table is not absolute. If the MySQL cannot determine the range to scan when executing a SQL statement, the InnoDB table will also lock the whole table, such as update table set num=1 where name like "% aaa%.

Thank you for reading, the above is the content of "what's the difference between InnoDB and MyISAM". After the study of this article, I believe you have a deeper understanding of the difference between InnoDB and MyISAM, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.