Differences between InnoDB and MyISAM In the Mysql storage engine (network arrangement), innodbmyisam

Differences between InnoDB and MyISAM In the Mysql storage engine (network arrangement), innodbmyisam
1,Transaction Processing innodb supports the transaction function, which is not supported by myisam. Myisam is faster and has better performance.
2. select, update, insert, and delete operationsMyISAM: If you execute a large number of SELECT statements, MyISAM is a better choice for InnoDB: If you execute a large number of INSERT or UPDATE statements for data, InnoDB tables should be used for performance considerations.
3. Different lock mechanismsInnoDB is a row-Level Lock and myisam is a table-Level Lock. Note: When the database cannot determine the row to be found, the entire table will also be locked. For example:Update table set num = 10 where username like "% test % ";
4. The number of rows in the query table is different.MyISAM: select count (*) from table, as long as MyISAM simply reads the number of rows saved, note that when the count (*) statement containsWhen the where condition is specified, the operations on the two tables are the same.
InnoDB: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.
5. Different physical structuresMyISAM: Each MyISAM is stored as three files on the disk. The name of the first file starts with the name of the table. The extension indicates the file type.. Frm file storage table definition.The data file extension is. MYD (MYData ). The extension of the index file is. MYI (MYIndex)
InnoDB: disk-based resources are InnoDB tablespace data files and their log files. The InnoDB table size is limited by the operating system file size, generally 2 GB
6. Different anto_increment mechanisms Better and faster auto_increment Processing

Others: WhyMyISAM is faster than Innodb. INNODB requires much more maintenance than the MYISAM engine during SELECT;
1) Data blocks. INNODB needs to be cached. MYISAM only caches index blocks, There is also a reduction in the exchange rate;
2) innodb addressing is mapped to blocks and then to rows. MYISAM records the file OFFSET directly, which is faster than INNODB.
3) INNODB also needs to maintain MVCC consistency. Although you do not have a scenario, INNODB still needs to check and maintain MVCC (Multi-Version Concurrency Control) Multi-Version Concurrency Control.InnoDB: MVCC is implemented by adding two additional hidden values to each row of records. These two values record when this row of data is created, the other one records when this row of Data expires (or is deleted ). However, InnoDB does not store the actual time when these events occur. Instead, it only stores the system version number when these events occur. This is a number that continues to grow with the creation of transactions. Each transaction records its own system version number at the beginning of the transaction. Each query must check whether the version number of each row of data is the same as that of the transaction. Let's take a look at how this policy applies to specific operations when the isolation level is repeatable read: SELECT InnoDB must have each row of data to ensure that it meets two conditions: 1. InnoDB must find a row version. It must be at least as old as the transaction version (that is, its version number is not greater than the transaction version number ). This ensures that this row of data exists no matter before the transaction starts, when the transaction is created, or when the row of data is modified. 2. The deleted version of this row of data must be undefined or larger than the transaction version. This ensures that the row of data is not deleted before the transaction starts.

Therefore, InnoDB is used for applications with high data integrity/Write Performance requirements. MyISAM is suitable for query applications.

If you do not have high transaction requirements, MyISAM is recommended for highly concurrent writes. The reasons are as follows:

1. MyISAM indexes and data are separated, and the indexes are compressed. The memory usage increases a lot and more indexes can be loaded, while Innodb is closely bound with indexes and data. Without compression, Innodb is much larger than MyISAM;
   
2. When inserting data, the InnoDB Storage engine will spend more time maintaining integrity and maintaining transactions. Therefore, the efficiency is lower than that of MyISAM;
   
3. According to the topic description, data is inserted and there is only one table. Later operations on the table are mainly queries. In terms of query speed, MyISAM is superior to InnoDB, the MyISAM index can be used to optimize the query speed.