mysql计数性能
select count(*) from mytable; select count(table_id) from mytable; //table_id is the primary_key
在具有 1000 万行的表上，这两个查询都运行缓慢。 我想知道为什么 mysql 不容易保持一个在所有插入、更新和删除上更新的计数器？
有没有办法改进这个查询？我使用了解释，但没有多大帮助。
select count(*) from mytable; select count(table_id) from mytable; //table_id is the primary_key
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
看一下以下博客文章：
1) COUNT(*** ) 与 COUNT(col)
2) 简单的 MySQL 性能技巧
3) InnoDB 的快速计数(*)
顺便说一句，您使用哪种发动机？
编辑：当您只需要知道是否有一定数量的行时，有关加速计数的技术。抱歉，我的查询有误。因此，当您只需要知道，例如特定条件下是否有 300 行时，您可以尝试子查询：
首先缩小结果集，然后对结果进行计数；它仍然会扫描结果集，但你可以限制它（再次，当数据库的问题是“这里是多于还是少于 300 行），并且如果数据库包含超过 300 行满足查询速度更快的条件时，它会起作用
测试结果（我的表有 670 万行）：
1)
SELECT count(*) FROM _table_ WHERE START_DATE > '2011-02-01'返回 420 万，持续 65.4 秒
2)
SELECT count(*) FROM ( select 1 FROM _table_ WHERE START_DATE > '2011-02-01' LIMIT 100 ) AS 结果< br>返回 100 持续 0.03 秒
下面是解释查询的结果，以查看那里发生了什么：
take a look at the following blog posts:
1) COUNT(***) vs COUNT(col)
2) Easy MySQL Performance Tips
3) Fast count(*) for InnoDB
btw, which engine do you use?
EDITED: About technique to speed up count when you need just to know if there are some amount of rows. Sorry, just was wrong with my query. So, when you need just to know, if there is e.g. 300 rows by specific condition you can try subquery:
at first you minify result set, and then count the result; it will still scan result set, but you can limit it (once more, it works when the question to DB is "is here more or less than 300 rows), and if DB contains more than 300 rows which satisfy condition that query is faster
Testing results (my table has 6.7mln rows):
1)
SELECT count(*) FROM _table_ WHERE START_DATE > '2011-02-01'returns 4.2mln for 65.4 seconds
2)
SELECT count(*) FROM ( select 1 FROM _table_ WHERE START_DATE > '2011-02-01' LIMIT 100 ) AS resultreturns 100 for 0.03 seconds
Below is result of the explain query to see what is going on there:
正如 cherouvim 在评论中指出的那样，这取决于存储引擎。
MyISAM确实保留表行的计数，并且可以保持其准确，因为 MyISAM 支持的唯一锁是表锁。然而，InnoDB 支持事务，并且需要进行表扫描来计算行数。
http://www.mysqlperformanceblog.com/2006/12/ 01/innodb-tables 的计数/
As cherouvim pointed out in the comments, it depends on the storage engine.
MyISAMdoes keep a count of the table rows, and can keep it accurate since the only locks MyISAM supports is a table lock.InnoDBhowever supports transactions, and needs to do a table scan to count the rows.http://www.mysqlperformanceblog.com/2006/12/01/count-for-innodb-tables/