MySQL - 简单查询有时会出现无限循环和不断增加的 rows_fetched 数量

Question

过去 4 周我一直在努力弄清楚为什么查询在 Docker Mysql Percona Distribution（percona：8.0.32-24，空 my.cnf）上永远间歇性地执行。此 postscript 查询在导入使用 MySQL Shell 的数据挖掘算法生成的多个 CSV 后运行。一半的时候，2-3秒就成功执行了。

否则，即使显示了正确的 rows_inserted 数量，也会停止并进入无限循环（2 天以上），并且不断增加 rows_fetched 数量 (fig1.png) 。为什么这个查询运行了这么长时间，它无休止地读取表（高 rows_fetched）是什么？

CREATE TABLE algo_rca_rule_metric (
 key varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
 rule_id int unsigned NOT NULL,
 context_id int unsigned NOT NULL,
 value double NOT NULL,
 PRIMARY KEY (key,value,rule_id),
 KEY key_rule_id (rule_id,key,value)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

** 在查询运行之前插入 2 个指标（置信度和支持度）：

INSERT INTO algo_rca_rule_metric
SELECT
   'confidence_order' AS 'key',
   metric_confidence.rule_id,
   metric_confidence.context_id,
   row_number() over (ORDER BY metric_confidence.value ASC, metric_support.value ASC) AS 'value'
FROM algo_rca_rule_metric metric_confidence
LEFT JOIN algo_rca_rule_metric metric_support ON (metric_confidence.rule_id = metric_support.rule_id AND metric_support.key = 'rule_support')
WHERE metric_confidence.key = 'confidence';

在没有 INSERT 的情况下观察到相同的行为。

请参阅解释语句 (fig3.png)。当无限循环发生时，会观察到以下情况：

• 显示进程列表：查询被标记为status =“执行”。

• 显示引擎innodb状态：在事务部分找不到查询。

• select * from sys.schema_table_statistics WHERE table_schema = 'DB_NAME' rows_fetched 输出似乎无限增长。 （参见fig1.png 不起作用，fig4.png 起作用，两者都是针对相同的数据执行的）。

任何帮助或见解都可以挽救生命。

Answer 1

将值放在PK的最后：

PRIMARY KEY key_rule_id (rule_id, key, value),
KEY (key, value, rule_id)

请注意，322 来自 2+4*80，这意味着它仅使用一列。 （const 也是如此。）

• 2字节长度字段
• 每个 utf8mb4 字符最多 4 个字节
• 声明中包含 80 个字符

这并不意味着分配了完整的 322 字节，但这是“最坏情况”。