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

What is the difference between InnoDB and MyISAM? I believe many inexperienced people don't know what to do about it. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

InnoDB and MyISAM are the two most commonly used table types for many people when using MySQL. These two table types have their own advantages and disadvantages, depending on the specific application. MYISAM used to be the default storage engine for MySQL, but InnoDB has been used since 5.5.5. The basic difference between them 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 for InnoDB, and the solution is to change the InnoDB table to MyISAM table first, and then to InnoDB table after importing data, but it does not apply to tables that use additional InnoDB features, such as foreign keys.

It is also said on the high-performance MYSQL that the default MYISAM, in fact, depends on that version. My default now is InnoDB. This is what the manual says:

InnoDB is a high-reliability and high-performance storage engine for MySQL. Starting with MySQL 5.5, it is the default MySQL storage engine. Key advantages of InnoDB include:

Its design follows the ACID model, with transactions featuring commit, rollback, and crash-recovery capabilities to protect user data.

Row-level locking and Oracle-style consistent reads increase multi-user concurrency and performance.

InnoDB tables arrange your data on disk to optimize common queries based on primary keys. Each InnoDB table has a primary key index called the clustered index that organizes the data to minimize I/O for primary key lookups.

To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints.

You can freely mix InnoDB tables with tables from other MySQL storage engines, even within the same statement. For example, you can use a join operation to combine data from InnoDB and MEMORY tables in a single query.

To determine whether your server supports InnoDB use the SHOW ENGINES statement.

Before MySQL 5.5.5, MyISAM is the default storage engine. (The default was changed to InnoDB in MySQL 5.5.5.) MyISAM is based on the older (and no longer available) ISAM storage engine but has many useful extensions.

Table 13.10. MyISAM Storage Engine Features

After reading the above, have you mastered the difference between InnoDB and MyISAM? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.