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

What this article shares to you is about what is the difference between InnoDB and MyISAM in Mysql. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

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.

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 features such as transaction support already foreign keys.

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 a where condition, the operation of the two tables is a 3. 0. 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%"

Www.2cto.com

In practical applications, MyISAM is not necessarily faster than InnoDB for the following reasons:

MyISAM uses table-level locks and read optimistic locks, that is, when there is a read operation, all operations on the same table will be blocked and wait for the read operation to be completed, and vice versa, Hu Xing exclusion will also occur between multiple write operations. However, multiple reads do not repel each other when reading the same table, so MyISAM performs better than InnoDB at the row level using multi-version control in this scenario of more reads and less writes.

InnoDB uses multi-version row-level locks, the so-called multi-version row-level locks. To put it simply, InnoDB will add a queue similar to the version library for non-row records. The read operation obtains the previous version of the version of the current operation, ensuring that the read content is the data saved by the last operation. The data row of the current version will be locked during the write operation, and the version of the data row will be added 1 after the completion. In this way, the lock is added when a row of records is written and updated, and the previous version of the data is read, thus providing higher concurrency in scenarios where both reads and writes are frequent.

These are the differences between InnoDB and MyISAM in Mysql. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.