时间戳

首页 > 解决方案 > 使用 HAVING 查询耗时过长

php - 使用 HAVING 查询耗时过长

问题描述

我有两个表的查询——matchoverview

id, home_id, away_id, 日期, 季节, 结果

匹配属性

id、game_id、attribute_id、attribute_value

我的查询

select m.id from matchOverview m
  join matchAttributes ma on ma.match_id=m.id and ma.attribute_id in (3,4,5,6)
  group by m.id
     having sum(case when ma.attribute_id in (3,4)
     then ma.attribute_value end) > 3
     or sum(case when ma.attribute_id in (5,6)
     then ma.attribute_value end) > 3;

它返回属性 3 和 4 或 5 和 6 的总和大于 3 的所有匹配 id。

这个特定的查询返回 900k 行，不出所料，在 phpmyadmin 中，这个查询需要花费大量时间，因为我想它需要将结果格式化为表格，但它将查询计时为 0.0113 秒。

然而，当我通过 PHP 进行此查询时，它需要 15 秒，如果我将查询更改为 LIMIT 到只有 100 个结果，它几乎会立即运行，让我相信唯一可能是正在传输的数据量是减慢它的原因.

但是通过网络传输 1M 4 字节整数真的需要 15 秒吗？

是进一步限制查询以使其返回更少结果的唯一解决方案吗？

编辑

我的查询的解释结果

id  select_type  table  type   key             key     key_len ref                 rows    Extra
1   SIMPLE       m      index  PRIMARY         PRIMARY 4       NULL                2790717 Using index
1   SIMPLE       ma     ref    match,attribute match   4       opta_matches2.m.id  2       Using where

我如何计时我的 SQL 查询

$time_pre = microtime(true);
$quer = $db->query($sql);
$time_post = microtime(true);
$exec_time = $time_post - $time_pre;

来自慢查询日志的数据

# Thread_id: 15  Schema: opta_matches2  QC_hit: No
# Query_time: 15.594386  Lock_time: 0.000089  Rows_sent: 923962  Rows_examined: 15688514
# Rows_affected: 0  Bytes_sent: 10726615

我可以处理 15 秒的查询，因为这是数据在网络上移动所需的时间，但是如果可以优化查询或我的表，那是最好的解决方案

行数不是问题，以下查询

select m.id from matchOverview m
  join matchAttributes ma on ma.match_id=m.id and ma.attribute_id in (1,2,3,4)
 group by m.id
  having sum(case when ma.attribute_id in (3,4)
   then ma.attribute_value end) > 8
  and sum(case when ma.attribute_id in (1,2)
   then ma.attribute_value end) = 0;

仅返回 24 行，但也需要约 15 秒

标签： phpmysqlsqlsql-optimization

解决方案

时间安排可能有两个重要部分：定位行并决定发送哪些 id；然后发送它们。我将解决这两个问题。

这是一种更好地将查询（而不是网络）的经过时间分开的方法： SELECT COUNT(*) FROM (...) AS x; 其中“...”是 1M 行查询。

加快查询速度

既然你并没有真正使用matchoverview，让我们摆脱它：

select  ma.match_id
    from  matchAttributes ma
    WHERE  ma.attribute_id in (3,4,5,6)
    group by  ma.match_id
    having  sum(case when ma.attribute_id in (3,4) then ma.attribute_value end) > 3
        or  sum(case when ma.attribute_id in (5,6) then ma.attribute_value end) > 3;

并按此顺序具有包含列的复合索引：

INDEX(attribute_id, attribute_value, match_id)

至于快速LIMIT，那是因为它可以停下来。但是LIMIT没有 a 的aORDER BY是相当没有意义的。如果添加ORDER BY，它将必须收集所有结果，对它们进行排序，最后执行LIMIT.

网络传输时间

通过网络传输数百万行（我在慢日志中看到 10.7MB）非常耗时，但几乎不需要 CPU 时间。

一EXPLAIN意味着可能有 280 万行；这是正确的吗？慢日志说大约有 16M 行被触及——这可能是因为两个表、join、group by 等。我的重新制定和索引应该显着减少 16M，从而减少经过的时间（在网络传输时间之前）。

923K 行“已发送”——客户端将如何处理这么多行。一般来说，我发现“发送”超过几千行表明设计不佳。

“在网络上传输 1M 4 字节整数需要 15 秒”——这是经过的时间，除非发送更少的行，否则无法加速。（顺便说一句，它可能作为几位数字的字符串发送，加上每一行的开销；我不知道 10726615 是实际的网络字节还是只计算整数。）

“ids 用于内部计算”——你如何用 ids 计算？如果您在其他地方查找 id，也许您可​​以增加查询的复杂性，从而在访问网络之前做更多的工作；然后发送更少的数据？

如果您想进一步讨论，请提供SHOW CREATE TABLE。（它可能有一些细节没有出现在您的简化表定义中。）

推荐阅读