mysql - 即使有 1k 个结果，查询也花费了太长时间

Question

我做了几个测试来优化下面的查询，但都没有帮助。

我试过的是;

1. 添加额外的索引
2. 通过检查 IN 子句中的其他属性来更改查询逻辑
3. 在线查询优化工具测试建议(eversql等)

我正在使用的索引；

radacct (`_accttime`);
radacct (`username`);
radacct (`acctstoptime`,`_accttime`);

完成查询；

  (SELECT *
   FROM `radacct`
   WHERE (radacct._accttime > NOW() - INTERVAL 1.2 HOUR)
     AND radacct.acctstoptime IN
       (SELECT MAX(radacct.acctstoptime)
        FROM `radacct`
        GROUP BY radacct.username) )
UNION
  (SELECT *
   FROM `radacct`
   WHERE (radacct._accttime >= DATE_SUB(NOW(), INTERVAL 2 MONTH)
          AND radacct.acctstoptime IS NULL) )

当我自己执行上面的 SELECT 语句时，它们只需要几毫秒。

我对 IN 子句有疑问。所以这是需要很长时间的查询

Answer 1

在我看来，您的问题是您的 IN 中的依赖子查询。显然，优化器没有意识到子查询在技术上没有太大变化。 (此外，查询可能不是最优的)。本质上，子查询是针对每一行执行的(这是不好的)。

现在，我们必须找出是哪一部分触发它成为依赖项，因为它不是真的。我的第一个尝试是给它一个不同的别名:

IN (SELECT MAX(inner.acctstoptime) FROM radacct AS `inner` GROUP BY inner.username)

如果这还不足以使其独立，请使其成为一个完整的连接(INNER，以便从结果中丢弃非连接行 [= 非最大行]):

INNER JOIN (
       SELECT MAX(inner.accstoptime) as maxstoptime, inner.username 
       FROM `radacct` AS `inner` 
       GROUP BY inner.username
   ) sub ON (sub.maxstoptime=radacct.acctstoptime)

希望能解决问题。

由于您的结果包含具有最大 acctstoptime 的用户行，因此在极少数情况下，当有一行具有 acctstoptime 时，它​​可能包含多个用户行，这是'该用户的最大值，但它与另一个用户的最大值相匹配。在连接部分，您可以在 ON 子句中添加另一个条件。在 IN 子查询中，您将删除显式分组依据并添加 WHERE radacct.username=inner.username。 (这确实会使它成为一个显式依赖子查询，但优化器可能能够处理它)

更新:由于沟通不畅......

使用连接生成的完整查询:

(SELECT DISTINCT radacct.*
 FROM radacct
 INNER JOIN (
        SELECT MAX(inner.accstoptime) as maxstoptime, inner.username 
        FROM `radacct` AS `inner` 
        GROUP BY inner.username
    ) sub ON (sub.maxstoptime=radacct.acctstoptime) 
  WHERE (_accttime > NOW() - INTERVAL 1.2 HOUR)
 ) 
 UNION 
  (SELECT *
   FROM `radacct`
   WHERE (_accttime >= DATE_SUB(NOW(),INTERVAL 2 MONTH)
          AND acctstoptime IS NULL)
  )

您仍然可以在 ON 子句中添加用户名比较。

这个查询所做的是，它删除了“IN”选择器并为连接强制一个中间结果(对于每个用户名最大acctstoptime)。当且仅当acctstoptime 是某个用户(或那个用户，如果您添加用户名比较)的最大值时，连接然后将正常行连接到中间结果行。如果它没有最大 acctstoptime，因此没有连接“伙伴”，它将从结果中丢弃(由 INNER 引起，LEFT JOIN 有点不足)，因此只留下具有最​​大 acctstoptime 的行(在并集的第一部分)。