问题描述
我正在构建一个相当大的统计系统，该系统需要允许用户请求给定过滤器集(例如日期范围)的统计信息.
I am building a fairly large statistics system, which needs to allow users to requests statistics for a given set of filters (e.g. a date range).
例如这是一个简单的查询，可返回10个结果，包括player_id和每个玩家造成的击杀次数:
e.g. This is a simple query that returns 10 results, including the player_id and amount of kills each player has made:
SELECT player_id, SUM(kills) as kills
FROM `player_cache`
GROUP BY player_id
ORDER BY kills DESC
LIMIT 10
OFFSET 30
上述查询会将结果偏移30(即结果的第3个页面").然后，当用户选择下一页"页面时，它将使用OFFSET 40(而不是30).
The above query will offset the results by 30 (i.e. The 3rd 'page' of results). When the user then selects the 'next' page, it will then use OFFSET 40 instead of 30.
我的问题是，即使在同一个数据集上使用了LIMIT/OFFSET对，也不会缓存任何内容，它再次执行SUM()，只是将结果再偏移10个.
My problem is that nothing is cached, even though the LIMIT/OFFSET pair are being used on the same dataset, it is performing the SUM() all over again, just to offset the results by 10 more.
上面的示例是一个更大的查询的简化版本，它只返回更多字段，并且花费很长时间(20+秒，并且随着系统的增长只会越来越长).
The above example is a simplified version of a much bigger query which just returns more fields, and takes a very long time (20+ seconds, and will only get longer as the system grows).
因此，我本质上是在寻求一种通过在应用LIMIT/OFFSET之前缓存状态来加快页面加载速度的解决方案.
So I am essentially looking for a solution to speed up the page load, by caching the state before the LIMIT/OFFSET is applied.
推荐答案
您当然可以使用缓存，但是我建议您缓存结果，而不是mysql中的查询.
You can of course use caching, but i would recommend caching the result, not the query in mysql.
但是首先要确保 a)您对数据有正确的索引， b)正在使用 .
But first things first, make sure that a) you have the proper indexing on your data, b) that it's being used.
如果这行不通，因为对于大型数据集，group by趋于缓慢，则需要将摘要数据放入静态表/文件/数据库中.
If this does not work, as group by tends to be slow with large datasets, you need to put the summary data in a static table/file/database.
有几种技术/库等可以帮助您执行数据的服务器端缓存. PHP缓存可以加速动态生成的站点提供了一个非常简单但自我说明的示例
There are several techniques/libraries etc that help you perform server side caching of your data. PHP Caching to Speed up Dynamically Generated Sites offers a pretty simple but self explanatory example of this.
这篇关于MySQL中的查询缓存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！