EF查询包含“Distinct()”首次缓慢

Question

我在网上搜索了很多相关讨论，但是我没有发现适用于我问题的模式:

我有一个基本的.NET WinForm应用程序，使用EF 3.5和MySQL。该应用程序在大多数查询中都能正常工作，但包含Distinct()的任何查询非常缓慢或会导致"The timeout period elapsed prior to completion of the operation or the server is not responding."异常，特别是在重新启动机器后第一次运行时，即使停止并重新启动MySQL服务，Distinct()查询从现在开始工作得非常好（即仅需不到1秒）。

由于重新运行应用程序或重新启动MySQL无法重复此问题，因此可以排除EF编译或MySQL缓存是因素所致。只有在重启后才会再次出现问题。这让我想到可能是.NET初始化问题或与操作系统相关的其他问题。这个问题已经在XP和Win 7上观察到。

欢迎提出任何想法。 (同样可以想出在不重新启动机器的情况下尝试重现该问题的想法，这非常不实际 :-)

更新：

EF生成的查询如下：

SELECT Distinct1.C2 FROM (
    SELECT DISTINCT YEAR(Extent1.RecDate) AS C2 
    FROM dailyrecord AS Extent1
    WHERE (Extent1.STN = 430030) AND (Extent1.WBAN = 99999)
) AS Distinct1;

在mysql shell中运行此命令第一次需要约30秒，之后即使重新启动mysql，也只需要约1秒。在重启后又恢复到了30秒。看起来操作系统第一次读取整个数据库文件，但之后不再如此（即使使用修改后的查询参数，后续查询几乎没有硬盘活动）。我该如何清除操作系统的读缓存以测试我的理论？

- Matthias

---

也许第一个 Distinct() 让 DBMS 启动并缓存所有数据以执行某种表扫描，之后它就会留在内存中，因此速度很快。您可以拦截纯查询并在重新启动后执行它，以查看是否仍然存在缓慢的情况？然后，您可以更准确地确定问题的来源。 - CodeCaster

@CodeCaster：嗯，OP说重新启动MySQL服务不会影响缓慢的提取，只有完全重启才会。据我所知，重新启动MySQL服务应该导致它放弃所有内存缓存，包括数据和执行计划。 - Boris B.

你有尝试过追踪并手动执行由EF生成的SQL查询吗？点击这里查看。 - Boris B.

@BorisB。我假设 OP 意思是重启 MySQL 服务器，你是正确的。如果只有在计算机重启后应用程序变慢了，那么您需要查看网络配置。 - CodeCaster

谢谢你的建议。我会尝试运行原始查询。只是重新制造问题场景（即刚启动的机器）真的很痛苦:-( 至于网络方面，我研究了MySQL中众所周知的skip_name_resolve问题，但我不认为它适用于本地主机 - 或者说呢？（客户端和服务器都在本地机器上运行。）有没有一种方法可以“重置” .Net框架或其某些方面，例如内存或DLL管理，以便缩小范围？谢谢。马蒂亚斯 - Matthias

Answer 1

经过相当多的测试，这就是所得到的结果：

• MySQL和其他DBMS中，一些查询由于需要读取整个数据文件，因此本质上是相当慢的，比如上面的“DISTINCT”查询，其中“WHERE”子句包含了主键的一部分，但不包括整个主键（在这种情况下，主键是由4个字段组成的复合主键）。

• 如果慢查询在应用程序中频繁出现并且很烦人，那么应该在搜索条件（“WHERE”字段）上添加索引。这将大大加快查询速度（最终解决了最初发布的问题），但会增加磁盘上的数据大小（.MYI文件），同时略微减小.MYD文件的大小，但总体上会增加总数据大小。添加索引可能会增加插入、更新和删除查询的时间，因为索引也需要更新。插入通常是逐个完成的，因此性能损失通常不会被注意到。与批量删除查询（见下文）不同。

• 查询的低效率被操作系统（在我的情况下是MS Windows XP）掩盖了。任何需要逐条记录读取整个表格的查询第一次遇到时都非常慢。然而，操作系统将缓存文件，即使参数发生变化，即使查询本身发生变化，后续查询也会快得多：任何需要读取整个表格文件的低效查询一旦被操作系统缓存，就会显得很快。这使得调整查询变得困难，因为您需要在测试之间重新启动操作系统。在搜索网络后，我还没有找到一种实用的方法来清除Windows中的读取缓存。

• 相关的低效查询是：DELETE FROM myTable WHERE field1 = value。即使field1上有索引，在删除许多记录时（在我的情况下是10,000条记录需要30秒），这也会很慢（使用MySQL，所有这些示例都使用MYISAM存储引擎），因为DBMS需要从（到）磁盘读取（可能写入）整个表格文件。同样，这将被操作系统掩盖：第一次很慢，但是在后续查询中非常快（在我的XP机器上快了30倍），因为操作系统进行了一些魔法，这很难（或不可能？）关闭。如上所述添加索引可能会增加删除查询所需的时间。

欢迎提出任何想法或评论！

- Matthias