當分析記錄的執行時間小於 1 秒時，什麼可以解釋 30 秒的 MySQL SELECT 查詢延遲？

我試圖弄清楚為什麼LIMIT 1在 AWS RDS Aurora 上執行帶有子句的簡單選擇（誠然，在具有大量行和索引的非常臃腫的表上）有時需要 30 多秒（有時甚至需要 2 分鐘）實例。這是在一個作家實例上。

它似乎發生在來自客戶端的第一個查詢中，僅在查看數十萬行的特定選擇上發生，並且僅在某些時候發生。

查詢格式為：

SELECT some_table.col1, some_table.col2, some_table.col3, some_table.col4, 
 MAX(some_table.col2) AS SomeValue 
FROM some_table 
WHERE some_table.col3=123456 LIMIT 1;

並“解釋”輸出：

+----+-------------+---------------+------+---------------+---------+---------+-------+--------+-------+
| id | select_type | table         | type | possible_keys | key     | key_len | ref   | rows   | Extra |
+----+-------------+---------------+------+---------------+---------+---------+-------+--------+-------+
|  1 | SIMPLE      | some_table    | ref  | col1          | col1    | 4       | const | 268202 | NULL  |
+----+-------------+---------------+------+---------------+---------+---------+-------+--------+-------+

我設法重現了該問題並在 PhpMyAdmin 中擷取了查詢的配置文件。PhpMyAdmin 將查詢記錄為執行需要 30.1 秒，但分析器顯示執行本身需要不到一秒：

所以看起來執行本身並沒有花費很多時間；什麼可能導致此延遲問題？我還發現 RDS Performance Insights 中記錄了相同的查詢：

這似乎發生在一系列相同或相似查詢中的第一個查詢中。會不會是記憶體問題？我嘗試執行RESET QUERY CACHE;以嘗試重現延遲，但沒有成功。如果有幫助，很高興提供有關基礎設施的更多資訊。

更多資訊

SHOW VARIABLES LIKE 'query_cache%';

SHOW GLOBAL STATUS LIKE 'Qc%';

檢查和發送的行（來自 Performance Insights）：

SHOW CREATE TABLE輸出：

CREATE TABLE `some_table` (
`col1` int(10) unsigned NOT NULL AUTO_INCREMENT,
`col2` int(10) unsigned NOT NULL DEFAULT '0',
`col3` int(10) unsigned NOT NULL DEFAULT '0',
`col4` int(10) unsigned NOT NULL DEFAULT '0',
`col5` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
`col6` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`col7` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`col1`),
KEY `col2` (`col2`),
KEY `col3` (`col3`),
KEY `col4` (`col4`),
KEY `col6` (`col6`),
KEY `col7` (`col7`)
) ENGINE=InnoDB AUTO_INCREMENT=123456789 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

考慮創建這個複合索引，

CREATE INDEX some_table_indx_col3_col2 ON some_table(col3,col2);

您應該刪除 col3 索引以避免索引冗餘並節省儲存空間。

Considering these existing details, 
Query_cache_size        2,556,593,152 
less Qcache_free_memory 2,555,753,592 
                      ---------------- 
Query_cache bytes in use      839,560 
Qcache_queries_in_cache     /     288 
You have avg bytes per query    2,915 in use 

Consider query_cache_limit of 192K  for ~ 64 * avg bytes per query 
query_cache_min_res_unit          512  the minimum to store more queries in your cache 
query_cache_size   50M   for ~ 64 * Query_cache bytes in use - from ~2.5G size. 

為了節省每次需要 qcache_lowmem_prunes 時使用的 98% 的 CPU 週期。

如果在請求的表刪除完成之前刪除了涉及從 innodb_buffer_pool 更改活動中刷新的 innodb 表，則可以觀察到停頓。

請讓我們知道您的進展/挫折，並考慮獎勵賞金。新年快樂

引用自：https://dba.stackexchange.com/questions/281235