重新计数或缓存mysql表的计数 [英] Recount or cache count of mysql table

问题描述

我想在我的网站上将排队提醒的次数显示为统计数据.该统计数据就是我表中的行数.过去我一直在做一个单独的表来缓存数字.缓存的表只有一行，其中一列包含提醒表中的行数.每次添加新的提醒时，我都会有另一个查询，该查询将值增加一个.

I want to display the number of reminders in queue as a statistic on my website. That statistic is the number of rows in my table. What I have been doing in the past is having a separate table that caches the number. The cached table has just one row and one column contained the number of rows in the reminder table. Every time a new reminder is added, I have another query that increments the value by one.

我最近开始将 count()函数合并到我的项目中.我的问题是；在表上执行 count()操作以在每次有人加载页面时显示当前提醒数(统计信息显示在页脚中)是否更好?该数字已经存储在另一个表中，以便我从该表中读取?在什么时候使用缓存的值而不是使用计数功能会更快?

I have recently started incorporating the count() function into my project. My question is; is it better performance wise to perform a count() operation on the table to display the current number of reminders every time someone loads a page (the statistic is displayed in the footer) or is it faster to have that number already stored in another table so that I am just reading from that table? At what point would it be faster to use a cached value rather than the count function?

推荐答案

与最优化相关的问题一样，答案是:嗯，这取决于.

As with the most optimization related question the answer is: well, it depends.

如果您的表使用的是myisam表类型，那么表本身已经缓存了行数，而count(*)则没有在该位置读取该数字.

If your table uses myisam table type, then the number of rows is already cached in the table itself and count(*) without where will read that number.

如果您使用innodb表引擎并且插入量大而选择量少，那么维护计数数将比对行计数更为昂贵.如果您没有太多的插入，那么使用缓存的数字可能会更快，因为innodb的count(*)相对较慢，没有位.

If you use innodb table engine and you have lots of inserts and less selects, then maintaining the count number will be more expensive than counting the rows. If you do not have too many inserts, then using a cached number will probably be faster, since innodb is relatively slow on count(*) without where.

请参阅有关count(*)的mysql性能博客有关innodb的详细说明.

See mysql performance blog on count(*) for more detailed explanation on innodb.

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