Why is MyISAM faster than Innodb?
INNODB requires much more maintenance than the MYISAM engine during SELECT:
1) Data blocks, INNODB needs to be cached, and MYISAM only caches index blocks, which also reduces the number of swap-in and swap-out;
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.
Note:
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 REPEATABLEREAD: 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.