如何使用缓存加速mysql查询

Question

我正在使用下面的查询来创建本周排名前十的人的领导板。

SELECT 
users.id, 
concat(users.first_name, ' ', users.last_name) as name, 
users.picture_url, 
MAX(rounds.speed) as speed, 
AVG(rounds.accuracy) as accuracy, 
SUM(rounds.score) as score, 
users.level 
FROM users INNER JOIN rounds ON users.email=rounds.email 
WHERE DATE(rounds.dateTime) BETWEEN CURDATE()-INTERVAL 1 WEEK AND CURDATE() 
GROUP BY users.id  
ORDER BY score DESC, speed DESC 
LIMIT 10

目前，运行此查询需要大约6秒的时间。这张桌子的“圆圈”大约有3000行。很快它就会大得多。因此，一旦用户打开领导板，它需要超过6秒的加载！

是否有任何方法来缓存或改进查询，使其加载得更快？

数据库: MySQL后端: PHP5框架:代码点火器

Answer 1

要解决这个问题，您可以做很多事情--由于MAX和AVG元素涉及表中所有行的比较分析，查询速度很慢。

有几件事，你可以做，使这更快。

1. 将其分解为多个查询。您正在基于ORDER BY score DESC, speed DESC获取前10名用户，因此您不需要在同一个查询中获取AVG(rounds.accuracy)。获取前10个用户的user_id，然后分别查询数据库中这10个用户的AVG(rounds.accuracy)。
2. 根据你的领导板的性质添加一些逻辑。每次更新主板时都不需要检查所有行。排名第100位的人今天可能不太可能进入前10名，如果是这样的话，你今天就不需要检查那个人的分数了。 如果您添加了一条WHERE user_id IN语句，它将大大加快查询的速度，并且只包括最有可能进入前10位的用户数量。也许在任何一天，只有30、40或50个用户可能在前10名中。如果是这样，那么您可以创建一个包含前50个用户ids的单独表，每天运行一个“较慢”的查询一次，以获得前50名用户。在领导板查询中添加一个WHERE user_id IN (select id from top_users)条件。

可能还有其他方法和方法来提高查询速度。这几件事会有帮助。这在很大程度上取决于你的应用程序/领导板，以及当你看到它被使用时你对它的了解。

Answer 2

加上“丹回答”。使用Explain检查查询花费时间的原因，检查索引是否正确构建，并且由于您只需要上周的数据，您可以在datetime上创建索引，这将非常有用--您正在使用数学函数和order，Mysql需要对增加查询时间的值进行排序和计算。此外，您还可以启用mysql缓存并增加缓存大小，检查此给你。。