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 字節，但這是「最壞情況」。