c# - 使用 EF 使用 sum 进行慢速 mysql 查询

Question

我需要每天/每周/每月进行一次相当大的搜索，而且速度有问题。我一直在增加缓冲区大小以使用 50% 的 ram (2gb) 和一些其他调整，但没有任何性能提升。

SELECT SUM(MessageCount) AS ErrorCount, Country, StatusCode, StatusText
              FROM messages
              WHERE StatusCode IN(3,4,5,6,8,9,10,11,13) AND SentOn >= 1394275285391 AND SentON < 1396863685391
              GROUP BY Country, StatusCode

在 mysql workshop 中运行查询时需要大约 50-70 秒来处理，并且该表包含大约 6,500,000 行。为了加快速度，我为 StatusCode、SentOn 和 Country ( = CHAR(2)) 添加了一个索引，并将其称为 ErrorCountries，如下所示。我对车间的速度不是很满意，但我想如果必须的话，我可以忍受。

我做了一个解释并得到了这个结果:

id=1    
select_typ=SIMPLE
table=messages  type=range
possible_keys=SentOn,ErrorCountries
key=ErrorCountries
key_len=12
rows=290977
filter=100.00
extra=Using index condition; Using MRR; Using temporary; Using filesort

我猜 temporary 和 filesort 可能是速度问题。

所以虽然我在研讨会上说查询需要 50-70 秒，但它在我的程序中并不是这样执行的。我在连接字符串中没有任何命令超时，所以我认为添加它可以解决这个问题。但是当我现在将超时更改为 5 分钟时，我开始思考问题的真正所在......

var sql = "SELECT SUM(MessageCount) AS ErrorCount, Country, StatusCode, StatusText" +
              " FROM messages" +
              " WHERE StatusCode IN(3,4,5,6,8,9,10,11,13) AND SentOn >= " + @from + " AND SentOn < " + to +
              " GROUP BY Country, StatusCode";

var res = db.Database.SqlQuery<Result>(sql).ToList();

为什么这个查询需要一分钟才能在研讨会上运行？为什么在程序中运行要超过5分钟？我有两个时间导入到数据库的程序。我该怎么做才能让它更快？

Answer 1

您可以尝试做三件事来提高速度。

首先，您提到您在(StatusCode, SentOn, Country) 上创建了一个索引。你在正确的轨道上，但你在这个索引中的列顺序是错误的。

此查询对 SentOn 进行范围扫描，然后对 StatusCode 进行值选择，然后获取 MessageCount、Country 和 StatusText 详细信息字段。列索引中的第一列应该是您进行范围扫描的列。尝试在 (SentOn, StatusCode, MessageCount, Country, StatusText) 上创建索引。那应该有助于加速您的查询。如果您想知道原因，请阅读覆盖索引。

其次，StatusCode 和StatusText 的值之间似乎存在一对一的关系。我猜这是因为您没有按 StatusText 分组。

如果是这样，请尝试使用这个小子查询仅对 StatusCode 执行摘要查询。

SELECT SUM(MessageCount) AS ErrorCount, 
       Country, 
       StatusCode
  FROM messages
 WHERE StatusCode IN (3,4,5,6,8,9,10,11,13) 
   AND SentOn >= 1394275285391 
   AND SentON < 1396863685391
 GROUP BY Country, StatusCode

这会快一点，因为您不必像执行满足 GROUP BY 所需的数据改组(文件排序)操作那样拖拉 StatusText 值。

您需要另一个子查询将 StatusCode 与 StatusText 相关联。这可能会表现得很糟糕；我建议您在继续之前尝试一下。

SELECT DISTINCT StatusCode, StatusText
  FROM messages
 WHERE StatusCode IN (3,4,5,6,8,9,10,11,13)

您可以通过在 (StatusCode, StatusText) 上创建复合索引来提高此子查询的性能。

然后尝试将这两个子查询连接在一起。

SELECT a.ErrorCount, a.Country, a.StatusCode, b.StatusText
  FROM (
        SELECT SUM(MessageCount) AS ErrorCount, 
               Country, 
               StatusCode
          FROM messages
         WHERE StatusCode IN (3,4,5,6,8,9,10,11,13) 
           AND SentOn >= 1394275285391 
           AND SentOn < 1396863685391
         GROUP BY Country, StatusCode
       ) AS a
  JOIN (
        SELECT DISTINCT StatusCode, StatusText
          FROM messages
         WHERE StatusCode IN (3,4,5,6,8,9,10,11,13)
       ) AS b ON a.StatusCode=b.StatusCode

第三，如果您可以从消息表中完全删除 StatusText 列，并为自己制作一个小查找表以根据 StatusCode 查找 StatusText 值，那将可能是一个巨大的胜利。但这需要更改您的应用程序。