mysql的innodb引擎的count(*)陷阱

    今天同学们在群里讨论oracle的count(*)与count(1)的问题，正好提到mysql的情况。我突然想到自己遇到的问题：在myisam引擎执行count(*)速度非常快，而且执行速度与记录条数无关，而innodb却不是这样，记录越多，速度越慢。

    于是做了一个实验，在一个有8000W条记录的innodb表执行了一下 select count(*) from table 。 果然一直等待，大概8分多后出来结果。马上再次执行相同的语句，用时大约22秒。马上执行第三次，还是约22秒。

    于是我猜想innodb没有把记录数保存起来，而是做了实时统计，所以导致速度比较慢。第二次、第三次相对较快是因为高速缓存的原因。于是打电话咨询DBA同学。经过DBA同学的专业解答，我明白了：我猜对了，呵呵。所以，以后要注意在innodb中count(*)的问题，尽量避免吧……除非能确保该表始终保持很少的记录数。

    另外，在网上查了一些资料：

   

引用

InnoDB Pitfalls
However, all is not rosy with InnoDB. Because of its transactional nature, it has bottlenecks of its own. On MyISAM, doing a query that does SELECT COUNT(*) FROM {some_table}, is very fast, since MyISAM keeps the information in the index.

On InnoDB, this info is not stored in an index, and even the index and the data are kept in the same file. So, doing the same query on a table can incur a significant performance penalty.

To check what overhead this has, I wrote a simple test benchmark code. I duplicated a client node table that has 20,243 rows from MyISAM to InnoDB.

On a quiescent AMD 64 machine with MySQL server 5.0.24, doing a SELECT COUNT(*) FROM node takes 0.835 milliseconds on MyISAM, while on InnoDB it takes 12.292 milliseconds!

记录一下，innodb引擎的count(*)问题。

评论

11 楼 linux1689 2010-12-05  

如果想彻底学习InnoDB，推荐大家看一看《MySQL技术内幕：InnoDB存储引擎》

10 楼 pcwang 2010-02-20  

如果只是模糊查询count，或者确保不会删除数据的话，建议用max(id)这样的方式，当然前提是有自增主键。

9 楼 kkito 2010-02-07  

后面22秒的问题，查询过应该是会缓存住的。要花这么长时间？跟8kw记录数有关？？

8 楼 kkito 2010-02-07  

MyISAM增加记录是顺序往后加，所以会存下记录数，count实际并没有去数，innodb的话记录是按主键排序的，count星和count主键没有区别，数量很大的话从头数一遍肯定要花时间。所以选引擎的时候也要看应用场景。8kw的记录肯定得要花点心思。我是这么理解的。

7 楼 myreligion 2010-02-05  

这不是innodb的问题，这个现象以前Mysql的站点上也讨论过。

Mysql开发人员解释说，由于事务的原因他们没有办法缓存数据，因为这样的话可能导致查到的值不对。后来又说准备增加一个hint（像sql_no_cache之类的），允许模糊查询行数，类似MyISAM，不知道什么时候才能提供。

6 楼 mccxj 2010-02-05  

这是innodb和myisam引擎的区别问题。。。不至于大惊小怪～～

5 楼 freespace 2010-02-05  

用select count(id) from table试试看

4 楼 yhailj 2010-02-05  

xiaoych 写道

在一个有8000W条记录的innodb表执行了一下 select count(*) from table 。 果然一直等待，大概8分多后出来结果。马上再次执行相同的语句，用时大约22秒。马上执行第三次，还是约22秒。

此 innodb 引擎的 table 有没有主键 ?
使用 count(1) 的执行时间是多久 ?

如果有 where 条件, 而条件中又有索引列的话, 好像两者没多大区别吧

3 楼 rainv 2010-02-05  

有解决办法没？难道要读写分离？

2 楼 rain2005 2010-02-05  

用数据库开发，还是很有必要搞清楚事务和锁的原理的，俺以前只搞过oracle，最近使用mysql，在innodb中，大数据库查询甚至会锁表，阻塞更新太不可思意了。

1 楼 redwave 2010-02-04  

现在的项目中就是这么用的，还好看到此贴，我去改改，谢谢！！！