mysql计数性能 [英] mysql count performance

问题描述

select count(*) from mytable;
select count(table_id) from mytable; //table_id is the primary_key

两个查询在具有1000万行的表上运行缓慢. 我想知道为什么为什么从mysql保留所有insert，update和delete更新的计数器变得不容易?
有没有办法改善这个查询?我使用了说明，但并没有太大帮助.

both query were running slow on a table with 10 million rows. I am wondering why since wouldn't it easy for mysql to keep a counter that gets updated on all insert,update and delete?
and is there a way to improve this query? I used explain but didn't help much.

推荐答案

cherouvim在注释中指出，它取决于存储引擎.

As cherouvim pointed out in the comments, it depends on the storage engine.

MyISAM确实保留了表行的计数，并且由于MyISAM支持的唯一锁是表锁，因此可以使其保持准确.

MyISAM does keep a count of the table rows, and can keep it accurate since the only locks MyISAM supports is a table lock.

InnoDB但是支持事务，并且需要进行表扫描以对行进行计数.

InnoDB however supports transactions, and needs to do a table scan to count the rows.

http://www.mysqlperformanceblog.com/2006 /12/01/count-for-innodb-tables/

这篇关于mysql计数性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！