为什么InnoDB对COUNT(*)进行全面扫描? [英] Why does InnoDB do a full scan for COUNT(*)?

问题描述

在具有500万行的表中，SELECT count(*) FROM table在MyISAM中将是即时的，但在InnoDB中将花费几秒钟.

In a table with 5 millions rows, a SELECT count(*) FROM table would be instant in MyISAM but would take several seconds in InnoDB.

为什么这样呢?他们为什么不像MyISAM那样在InnoDB中优化计数?

Why is this that way? Why haven't they optimise count in InnoDB like MyISAM?

谢谢.

推荐答案

在实现上有所不同. InnoDB支持事务，因此它必须根据您对表的事务一致视图对行进行计数.由于MyISAM不支持ACID属性，因此如果插入一行，则会为所有人插入该行，因此它可以仅更新存储在存储引擎中的计数.

It's a difference in implementation. InnoDB supports transactions and therefore it has to count the rows based on your transactionally consistent view of the table(s). Since MyISAM doesn't support ACID properties, if a row is inserted, it's inserted for everyone and therefore it can just update a count it keeps within the storage engine.

这篇关于为什么InnoDB对COUNT(*)进行全面扫描?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！