mysql查询停留在“发送数据”上，似乎永远也没有完成

nwlls2ji 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(235)

我的每一个小时都有一个问题 book_records 表，并获取获取的图书数量（值=1）与空闲图书数量（值=0）：

SELECT sr.time AS h,
       COUNT(CASE WHEN sr.value = 1 THEN 1 END) AS taken,
       COUNT(CASE WHEN sr.value = 0 THEN 1 END) AS free,
       sr.libID AS libID
FROM `book_records` AS sr
WHERE sr.time BETWEEN '2017-01-01 00:00:00' AND '2017-01-01 23:00:00' 
AND MINUTE(sr.time) = 0
AND libID = 0 
GROUP BY h, libID
ORDER BY h, libID

这里要看的主线是：

WHERE sr.time BETWEEN '2017-01-01 00:00:00' AND '2017-01-01 23:00:00'

查询大约需要0.03秒。
如果我改成这个（5天）：

WHERE sr.time BETWEEN '2017-01-01 00:00:00' AND '2017-01-05 23:00:00'

大约需要0.15秒。
10天：

WHERE sr.time BETWEEN '2017-01-01 00:00:00' AND '2017-01-10 23:00:00'

大约需要0.28秒
但15天之后：

WHERE sr.time BETWEEN '2017-01-01 00:00:00' AND '2017-01-15 23:00:00'

在撰写本文时，查询仍在进行。 SHOW FULL PROCESSLIST 告诉我随着时间的推移，查询的状态设置为“sleep”。
那是什么原因呢？在我的MySQL5.7配置中是否有可能导致这种情况？查询速度在1天、5天和10天都可以，但是再多5天（15天）查询就会进入睡眠状态？为什么？
编辑：显示完整进程列表输出：

| 2234 | phpmyadmin | localhost | NULL    | Sleep   |     4 |              | NULL
| 2235 | root       | localhost | library | Query   |     4 | Sending data | 
SELECT SQL_CALC_FOUND_ROWS sr.time AS h, 
COUNT(CASE WHEN sr.value = 1 THEN 1 END) AS taken, 
COUNT(CASE WHEN sr.value = 0 THEN 1 END) AS free, sr.libID AS libID FROM `book_records` AS sr
WHERE sr.time BETWEEN '2017-01-01 00:00:00' AND '2017-01-15 23:00:00' 
AND MINUTE(sr.time) = 0
AND libID = 0 
GROUP BY h, libID
ORDER BY h, libID LIMIT 1 |

mysql query-performance

来源：https://stackoverflow.com/questions/51337848/mysql-query-stuck-on-sending-data-doesnt-seem-to-ever-finish

1条答案

按热度按时间

jm81lzqq1#

让我们看看您的查询。

SELECT sr.time AS h, 
       COUNT(CASE WHEN sr.value = 1 THEN 1 END) AS taken, 
       COUNT(CASE WHEN sr.value = 0 THEN 1 END) AS free, 
       sr.libID AS libID
  FROM `book_records` AS sr
WHERE sr.time BETWEEN '2017-01-01 00:00:00' AND '2017-01-01 23:00:00' 
  AND MINUTE(sr.time) = 0
  AND libID = 0 
GROUP BY h, libID
ORDER BY h, libID

首先，你需要一个复合索引 book_records (libID, time) 你的问题是可以接受的。不可组织的查询需要全表扫描，扫描速度很慢。
接下来，你的选择标准 sr.time BETWEEN '2017-01-01 00:00:00' AND '2017-01-01 23:00:00' 有点奇怪。获取2017年7月1日当天的所有记录。你需要使用这些标准

sr.time >= '2017-01-01 00:00:00'
   AND sr.time <  '2017-01-02 00:00:00'

为什么？您需要时间从7月1日午夜开始，直到但不包括7月2日午夜的所有记录。
接下来，您有了这个选择标准。 MINUTE(sr.time) = 0 . 它从结果集中删除许多记录，并保留其他记录。例如，它会删除一条带有时间的记录 10:03:00 记录下时间 10:00:59 . 那可能不是你想要的。消除这个标准；它使查询不可搜索，并选择一组奇怪的行。
下一步，看起来您正在尝试按一天中的小时汇总结果。要做到这一点，你需要 GROUP BY HOUR(sr.time) . 这将为您提供一天中每小时的记录数，即使您在筛选范围中包含了多天。
第四，也不太重要的是，通过在三列上创建索引，可以更快地执行此查询 (libID, time, value) 而不是两个。这称为覆盖索引，因为它包含查询所需的所有行。完全可以通过索引满足查询，因此mysql不需要同时读取索引和表。使用创建此索引

CREATE INDEX book_records_id_time_val ON book_records (libID, time, value);

最后，您的查询如下所示

SELECT HOUR(sr.time) AS h, 
       COUNT(CASE WHEN sr.value = 1 THEN 1 END) AS taken, 
       COUNT(CASE WHEN sr.value = 0 THEN 1 END) AS free, 
       sr.libID AS libID
  FROM `book_records` AS sr
 WHERE sr.time >= '2017-01-01 00:00:00'
   AND sr.time <  '2017-01-02 00:00:00'
   AND libID = 0 
 GROUP BY HOUR(sr.time), libID
 ORDER BY HOUR(sr.time), libID

这些更改将使您的查询更准确、更快。
顺便说一下，要查看正在运行的查询，您必须发出 SHOW FULL PROCESSLIST 从不同的mysql连接（另一个客户端程序或另一个查询窗口）运行查询。当连接显示时 sleep 它什么也没做。

赞(0）回复(0）举报 2021-06-20

我来回答

mysql查询停留在“发送数据”上，似乎永远也没有完成

1条答案

相关问题

热门标签

最新问答