Differences between InnoDB and MyISAM storage engines in mysql

In my understanding, when you operate a myisam table, the select, update, delete, and insert statements automatically lock the table. If the table after the lock meets the insert concurrency conditions, new data can be inserted at the end of the table, which is fast and Innodb supports transactions and row-level locks, which is the biggest feature of innodb. ACID properties of transactions: atomicity, con

In my understanding, when you operate a myisam table, the select, update, delete, and insert statements automatically lock the table. If the table after the lock meets the insert concurrency conditions, new data can be inserted at the end of the table, which is fast and Innodb supports transactions and row-level locks, which is the biggest feature of innodb. ACID properties of transactions: atomicity, con

InnoDB and MyISAM are the two most common table types used by many people when using MySQL. The 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 transaction processing, while the InnoDB type does. MyISAM tables emphasize performance, and the execution speed is faster than that of InnoDB tables, but transactions are not supported. InnoDB provides advanced functions such as external keys for transaction support.
MyIASM is a new version of the IASM table and has the following extensions:
· Binary hierarchy portability.
· NULL column index.
· There are fewer fragments for Long-varying rows than the ISAM table.
· Supports large files.
· Better index compression.
· Better key statistics distribution.
· Better and faster auto_increment processing.

MyISAM:

When you operate a myisam table, the update, delete, and insert statements automatically lock the table. If the table after the lock meets the insert concurrency conditions, you can insert new data at the end of the table. You can also use the lock table command to lock the table. This operation can simulate transactions, but it consumes a lot. It is generally used only in the experiment demonstration.

The following are some differences between details and specific implementations:
◆ 1. InnoDB does not support FULLTEXT indexes.
◆ 2. innoDB does not store the specific number of rows in the table. That is to say, when you execute select count (*) from table, InnoDB needs to scan the entire table to calculate the number of rows, however, MyISAM simply needs to read the number of lines saved. Note that when the count (*) statement contains the where condition, the operations on the two tables are the same.
◆ 3. For fields of the AUTO_INCREMENT type, InnoDB must contain only the index of this field. However, in the MyISAM table, you can create a joint index with other fields.
◆ 4. When deleting FROM table, InnoDB does not create a new table, but deletes a row.
◆ 5. the load table from master operation does not work for InnoDB. The solution is to first change the InnoDB TABLE to the MyISAM TABLE, and then change the imported data to the InnoDB TABLE, however, it is not applicable to tables that use additional InnoDB features (such as foreign keys.
　

In addition, the row lock of the InnoDB table is not absolute. If MySQL cannot determine the scope to be scanned when an SQL statement is executed, the InnoDB table will also lock the entire table, for example, update table set num = 1 where name like "% aaa %"
To sum up, none of the tables is omnipotent. Only by selecting the appropriate table type for the business type can we maximize the performance advantages of MySQL.

Innodb supports transactions and row-level locks, which is the biggest feature of innodb.

ACID properties of transactions: atomicity, consistent, isolation, durable.

Concurrent transactions bring about several problems: loss of updates, dirty reads, non-repeated reads, Phantom reads.

Transaction isolation level: Read uncommitted, Read committed, Repeatable read, and Serializable)