调用某些查询时 MySQL "Lock wait timeout exceeded"

Question

我有一个MySQL 5.7，有一组表，我使用以下查询:

SET innodb_lock_wait_timeout = 900;
DROP TABLE IF EXISTS aggregated_table;
CREATE TABLE aggregated_table AS
SELECT
itbl.u AS u,
LEFT(itbl.e, 10) AS e,
itbl.b AS b,
itbl.c AS c,
itbl.d AS d,
itbl.ee AS ee,
h,
p,
s,
SUM(time_column) AS total_time
FROM
input_table AS itbl
WHERE
s = 1
AND b = 0
GROUP BY
u,
e,
b,
c,
d,
ee,
h,
p,
s;

80%的情况下，此查询执行正常，但有时CREATE与ERROR __main__: (1205, 'Lock wait timeout exceeded; try restarting transaction')会失败，这可能取决于在特定时刻数据库中找到的特定数据。对于相同的数据库状态，总是会重复出现错误。

我想知道为什么它可能会失败，以及如何保护查询不受无限锁的影响。

UPDATE:EXPLAIN SELECT...提供答案(json格式的数据库客户端):

[
{
"id": 1,
"select_type": "SIMPLE",
"table": "itbl",
"partitions": null,
"type": "ALL",
"possible_keys": null,
"key": null,
"key_len": null,
"ref": null,
"rows": 56035815,
"filtered": 1,
"Extra": "Using where; Using temporary; Using filesort"
}
]

SHOW ENGINE INNODB STATUS输出:https://pastebin.com/ZNeRi6AY

Answer 1

这个综合指数可能会有所帮助:

INDEX(s, b)   -- (in either order)

问题似乎是读大约56M行需要900秒。

这是一个相当可怕的GROUP BY，它是有意义的，还是ONLY_FULL_GROUP_BY的变通方法?

你有多少RAM ?桌子有多大?

如果this " aggreated& quot;("Summary")表可以增量地构建(例如每天晚上用白天的数据更新)，可以非常有效地避免超时。http://mysql.rjweb.org/doc.php/summarytables如果你能做到这一点，我将不是索引我建议，而是使用id或日期来控制哪些行是每天晚上读取，不用担心对s和b过滤。