php - MySQL 存储引擎

Question

mysql 5.6以后，innodb和myisam 哪个查询数据更快。能不能给个证明。

Answer 1

Personal test (MySQL version 5.7.18), tested 60W and 1W records respectively
With index, MyISAM is faster than Innodb query, the difference is small
Without index, MyISAM is faster than Innodb query, the difference in query speed is about 1 times

Answer 2

Lots of queries and few modifications (insert update delete) ==> MyISAM

Full text search support, and MySQL version is less than 5.5 ==> MyISAM

In other cases, please use innodb

Off-topic:
Most people are lazy, including me. It is estimated that not many "Lei Feng" will take the time to prove to you something that has many conclusions on the Internet.
You should search related topics yourself, I believe you can find a lot of comparisons made by others. If you are still skeptical about those conclusions, you can do your own research. After all, what others have done online is more likely to be an older version of mysql. You can conduct a benchmark test yourself. If you ask how to create a convincing benchmark, perhaps it is a better question.

Answer 3

It is not possible to directly say which one is faster. It needs to be looked at based on business logic. However, it is worth mentioning that there is a misunderstanding on the Internet. Many people think that it is faster to query myisam一定要比innodb快，的确，当我们在对整个表不加以任何条件进行查询统计时，myisam的确要快一些，这是因为myisam在插入数据的时候会更新表的数据，当我们查询的时候，直接走的统计数量
但是，一旦当我们给sql语句加入条件时，innodb并不一定会比myisam慢，因为此时都是走的磁盘或索引,即使是面对第一种情况,我们也可以用一个总数跟进表来统计总数,所以,一般没有特殊情况的话,建议InnoDB.

Answer 4

But when analyzing the two engines, MYISAM is faster than INNODB, because when INNODB does SELECT, it has to maintain many more things than the MYISAM engine:
1. INNODB, INNODB needs to be cached, MYISAM only caches index blocks, and in the middle There is also a reduction in swapping in and out.
2. INNODB addressing needs to be mapped to blocks and then to rows. MYISAM records directly the OFFSET of the file, and positioning is faster than INNODB.
3.INNODB also needs to maintain MVCC consistency; although it does not exist in your scenario, it still needs to check and maintain MVCC (Multi-Version Concurrency Control) multi-version concurrency control