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

最新推荐文章于 2025-06-22 22:35:07 发布

iteye_3001

最新推荐文章于 2025-06-22 22:35:07 发布

阅读量117

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL 文章标签： MySQL performance

本文链接：https://blog.youkuaiyun.com/iteye_3001/article/details/81823650

SQL 专栏收录该内容

3 篇文章

订阅专栏

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!

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