改进或消除 mysql COUNT 的方法 [英] Way to improve or eliminate a mysql COUNT

问题描述

请原谅我对这个问题的无知，因为我在这方面没有太多经验.我有一个使用 mysql COUNT(*) 查询返回用户统计信息的页面.例如，类似于:

Forgive my ignorance on this question, as I don't have much experience in this area. I have a page that uses a mysql COUNT(*) query to return a user's stats. For example, something like:

SELECT COUNT(*) FROM worker WHERE worker_id = '1234';

它使用 worker_id 的索引并检查 EXPLAIN 似乎查询看起来不错.但是，这绝对是残酷的，对于具有一百万多行的表进行单个查询可能需要 5 秒以上的时间.此外，这是一个非常常见的查询，有时会导致多个查询尝试运行该查询时出现 mysql 超时错误.

It uses the index for worker_id and checking out the EXPLAIN seems that the query looks good. However, this performs absolutely brutally, and can take 5+ seconds for a single query on a table with a million+ rows. Additionally, this is quite a common query, and sometimes it will result in a mysql timeout error with multiple queries trying to run this.

解决"这个问题的好方法是什么?信息变化很快，所以我认为缓存它不会起作用.我将如何解决这个问题，一些大型网站如何处理节目统计数据(例如，Facebook 好友或 Twitter 关注者)?

What would be a good way to 'solve' this issue? The information changes quite rapidly so I don't think caching it would work. How would I go about fixing this, and what do some large sites do the show stats (for example, Facebook Friends or Twitter Followers)?

推荐答案

首先，确保你在 worker(worker_id) 上有一个索引.

First, be sure that you have an index on worker(worker_id).

其次，如果列确实是数字而不是字符串，则不要使用单引号:

Second, if the column is really a number and not a string, then don't use single quotes:

SELECT COUNT(*)
FROM worker
WHERE worker_id = 1234;

第三，如果你只需要知道 worker 是否存在(并且实际上不需要计数)，那么运行这样的查询:

Third, if you just need to know if the worker exists (and don't actually need the count), then run a query like this:

select 1
from worker
where worker_id = 1234
limit 1;

并检查是否返回任何行.

And check to see if any rows are returned.

这篇关于改进或消除 mysql COUNT 的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！