缓存查询结果

Question

比方说，我们有一个高度可配置的报表系统，允许用户选择列、筛选器和排序。

所有这些配置都是这样的:将其转换为SQL，针对DB执行，然后用户看到他的报告并继续使用它。但是对于每个操作，比如排序，我们仍然构建一个查询。

转换本身只需几毫秒，但对DB的查询执行可能需要3-5秒(如果有大量并行执行，则最多20秒钟)。

所以，我正在考虑加入某种缓存。

目前，我认为有三种方式：

1. 在不过滤的情况下添加一个表来缓存所有结果，然后根据用户请求对其进行排序/过滤，
2. 为每个结果添加一个表，但仍然没有过滤器。在这种情况下，我可以对数量少得多的数据进行排序/筛选，但是有超过10k个不同的报告，我不认为创建10k小表会更好。
3. 喜欢第一个选项，但是LRU缓存在Java端。我们可以在内存中加入2-3k报告结果。它通常比第一个选项要快，因为我们没有很多并行用户，只是有大量报告的用户。

缓存失效将是一天几次。

你认为什么是让它更快的最好方法？从你的角度看，提出的解决方案有哪些缺点和优点？如果您可以自由选择数据库和技术(Java堆栈)，您会做什么？

Answer 1

好吧，让我们确保我弄好了。

有超过10k个不同的报告

因此，预先计算和预缓存是没有意义的，它们必须按需生成。

--行中没有很多数据，只有短字符串、日期和整数。在内存中取出它，甚至保存一段时间并不昂贵。

因此，缓存少量的数据可以避免昂贵的查询，这很好。

添加一个表来缓存所有结果而不进行过滤，然后在用户请求排序/过滤时在Java端进行筛选。

问题是，每个报表查询很可能都有不同的列，具有不同的名称，因此，除非使用像JSON这样的格式，否则不能很好地匹配单个表，将每个缓存的结果行存储为JSON字典.在本例中，索引将是一个问题，即使您在JSON值中的字段上创建索引，如果您的许多报告有无数不同的列名，那么您也需要无数个索引.

闻起来像一罐虫子。

为每个结果添加一个表，但仍然没有过滤器。在这种情况下，我可以对数量更少的数据进行排序/筛选，但是有超过10k个不同的报告，我不认为创建10k小表是件好事。

Pros:每个缓存表都可以有适当的列、数据类型和索引。很容易使缓存失效，只需截断它。您可以将所有缓存表设置为未登录，以使它们更快。您可以使用之前使用的SQL查询对缓存的结果执行所有额外的排序/筛选，因此这可能是编写代码的更简单的选项。如果您只想获取部分结果，那么分页也是很好的。对于将报告查询的结果复制到缓存中来说，这将是最快的选择，因为缓存已经在postgres中，因此不需要传输数据。您还可以将缓存存储在另一个驱动器/SSD上。

缺点:我听说过大量表的主要问题是，如果您的文件系统在有大量文件的目录上减慢速度。不过，这在现代文件系统上不应该是个问题，我不认为postgres本身会被10k表所困扰。

这可能会使对information_schema的查询变得缓慢，而psql中的“dt”之类的内容也会出现问题，因此缓存表最好隐藏在“缓存”模式中，这样它们就不会受到干扰。这也将使它们更容易被排除在备份之外。

它还将使用postgres服务器上的一些RAM来缓存缓存表，这取决于在线用户的数量。

我会说，这将是值得一点的基准。创建一个模式，添加10k表，看看是否有什么东西坏了。

喜欢第一个选项，但是LRU缓存在Java端。我们可以在内存中加入2-3k报告结果。它通常比第一个选项要快，因为我们没有太多的并行用户，只有有大量报告的用户。

这是对轮子的重新发明，您必须在java中重新实现排序/过滤器.加上高速缓存标志..。我啊。

不过，还有其他选择：

• 将缓存放在另一个数据库中，放在另一台计算机上。这可能是postgres实例或另一个数据库(可能需要重写某些查询)。只有当缓存占用了database.
• Put上太多的内存-- web浏览器中的缓存，并且使用javascript来过滤/排序时，才会很有趣。这可能会更快，取决于互联网连接的速度，它将减少服务器负载，但您必须编写大量javascript代码.

。

我的意思是，你对大量的表都很谨慎，谨慎是很好的，但是如果它运行良好，它确实是最简单的解决方案.