每次在大表上运行很少更改的数据的查询的替代方法

Question

我有一个内部web应用程序正在运行，每次用户进入“搜索”视图时，它都会在数据库中查询三个不同的表，以便为视图中的三个下拉列表生成值。

它基本上是运行一个

SELECT DISTINCT (PortName)
FROM Ports
ORDER BY PortName ASC

但是该表包含大约10,000‘000行，并且处于相当重的负载下，这意味着页面的加载时间(由于用数据加载下拉列表)可能会超过10-15秒。

那么，是否有更好的方法来做到这一点，例如，在特定的时间间隔运行一些脚本，并在不同的位置创建一个表/视图/任何东西，从而卸载查询大表的任务，只需要从主表中的10,000‘000返回80行？

Answer 1

不知怎么的，没人提到索引视图。在对索引视图可以(和不能)做的事情上可以找到索引视图的非常简短的介绍。

本质上，它是一个缓存，它由引擎在幕后自动维护。索引视图存储在磁盘上，并在基础表更改时自动更新。

因此，更新、删除和插入主表的速度会稍微慢一些，但是查询索引视图将是即时的，因为它不会扫描主表的10M行。在任何情况下，当引擎被更新以调整存储在索引视图中的值时，引擎就不会扫描整个10M行表。

此外，问题标题说“对于很少更改的数据运行查询的替代方案”，所以我假设这个大表不会经常更改。我想，索引视图在这里会是完美的。

不能在索引视图中使用DISTINCT，但是如果没有索引视图，查询可以重写如下：

SELECT PortName, COUNT_BIG(*) AS cc 
FROM Ports 
GROUP BY PortName

如果索引视图包含GROUP BY，它需要COUNT_BIG(*)，所以我添加了它。

Answer 2

我从DISTINCT中假设PortNames在您的表中是重复的，并且没有返回1,000万个不同的端口名。

最小的解决方案是在该列上放置一个索引：

CREATE INDEX IX_Ports_PortName ON Ports(PortName);

当然，还有一些带有这种和存储开销的DB负载，所以您可能需要一个更复杂的解决方案，比如缓存，Aaron Bertrand在他的回答中很好地介绍了这个解决方案。

您还可以使用更多的规范化:如果端口名称被重复并且清楚地知道它们很重要，那么您可以创建一个PortNames表，并在端口表中使用一个PortNameID。这样，您就可以只扫描PortNames表，这可能会更小、更快。当然，这可能会带来额外的费用和自身的考虑。

Answer 3

对于不经常更改的数据，可以在查询所在的地方使用缓存层。有许多替代方案，如梅卡奇，而且已经存在许多讨论：

• 缓存比较: Memcached与Server………(等待…什么？)
• X64(我能让Memcached在Windows )64位环境中运行吗？

您也可以自己很容易地做到这一点，并且取决于数据的范围和大小，您可以廉价地做到这一点。在以前的生活中，我做过这样的事情，我在每个应用程序/web服务器上放置了一个server实例，并编写了我自己的脚本来周期性地交换这些实例中的数据，并且尽可能少的中断。这使得所有繁重的读取活动都远离主实例，并提供了数据缓存副本如何过时的灵活性(只需更改刷新作业的频率)。我在这里写过这个过程：

• 模式切换-
• 模式切换-a:第2部分

您可以做的另一件事是使用日志传送来实现穷人的可用性组。基本上，您有一组日志附带的目标，循环遍历它们，按照计划恢复最新的日志，以及一个动态应用程序，它知道在下一个读取请求中使用哪个目标。我在这里写过这个过程：

• 预算案的可读性副词

如果您的数据大于10 at，或者将来会超过它，那么Express将无法工作，您必须至少使用标准版。但是，这种类型的操作(将读取扩展到普通硬件上)比在主服务器上增加内核/内存/磁盘来进行扩展要便宜得多。

如果将读与写隔离不是主要目标，那么对于这种非常特殊的情况，您可以使用其他本地解决方案，比如索引视图。请记住，它们会造成开销，您不能灵活地处理这些开销，比如调整数据被复制的频率(以及读取副本的陈旧程度)。其他查询方案不适合索引视图。