MySQL – Einfache Abfragen leiden manchmal unter einer Endlosschleife und einer zunehmenden Anzahl von rows_fetched

Question

Ich habe in den letzten 4 Wochen versucht herauszufinden, warum Abfragen in der Docker Mysql Percona Distribution (percona:8.0.32-24, empty my.cnf) zeitweise und ewig ausgeführt werden. Diese Postscript-Abfrage wird ausgeführt, nachdem mehrere CSVs importiert wurden, die mit dem Data-Mining-Algorithmus von MySQL Shell generiert wurden. In der Hälfte der Fälle dauert die erfolgreiche Ausführung 2–3 Sekunden.

Andernfalls stoppt es, obwohl die korrekte rows_inserted-Nummer angezeigt wird, in eine Endlosschleife (2+ Tage) und erhöht die rows_fetched-Nummer ständig (fig1.png). Warum dauert die Ausführung dieser Abfrage so lange und warum wird die Tabelle endlos gelesen (hoher 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

** Fügen Sie zwei Metriken (Vertrauen und Unterstützung) ein, bevor die Abfrage ausgeführt wird:

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';

Das gleiche Verhalten wird ohne INSERT beobachtet.

Siehe Erklärung (fig3.png). Wenn eine Endlosschleife auftritt, wird Folgendes beobachtet:

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

• 显示引擎innodb状态: Abfrage im Transaktionsbereich nicht gefunden.

• select * from sys.schema_table_statistics WHERE table_schema = 'DB_NAME' Die rows_fetched-Ausgabe scheint unendlich zu wachsen. (siehe fig1.png funktioniert nicht, fig4.png funktioniert, beide werden mit denselben Daten ausgeführt).

Jede Hilfe oder Einsicht könnte ein Leben retten.

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 字节，但这是“最坏情况”。