年末整理十一

摘要：

 

今天同学们在群里讨论oracle的count(*)与count(1)的问题，正好提到mysql的情况。
：在myisam引擎执行count(*)速度非常快，而且执行速度与记录条数无关，而innodb却不是这样，记录越多，速度越慢。
于是做了一个实验，在一个有8000W条记录的innodb表执行了一下 select count(*) from table 。
果然一直等待，大概8分多后出来结果。
马上再次执行相同的语句，用时大约22秒。马上执行第三次，还是约22秒。
于是我猜想innodb没有把记录数保存起来，而是做了实时统计，所以导致速度比较慢。
第二次、第三次相对较快是因为高速缓存的原因。

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!