MySql GROUP BY, ORDER BY LIMIT 查詢耗時超過 5000 秒

我有一張簡單的桌子。在其中創建一個複合索引。我們有一個工作在從伺服器上命中 select 語句，執行查詢大約需要 5000 秒。請使用解釋輸出檢查下表和查詢：

CREATE TABLE `abc_mins` (
 `abc_key` varchar(500) DEFAULT NULL,
 `val` int(11) DEFAULT NULL,
 `location` varchar(250) DEFAULT NULL,
 `a_time` datetime DEFAULT NULL,
 KEY `location` (`location`,`a_time`),
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> EXPLAIN select location, a_time, sum(val) val from abc_mins where abc_key like 'xyz:requests:3189:%' and location='Pune' group by 1,2 order by 1,2 desc limit 4;
+----+-------------+---------------------+------+---------------+------+---------+-------+---------+-------------+
| id | select_type | table               | type | possible_keys | key  | key_len | ref   | rows    | Extra       |
+----+-------------+---------------------+------+---------------+------+---------+-------+---------+-------------+
|  1 | SIMPLE      | abc_mins            | ref  | location      |location| 753   | const | 2422060 | Using where |
+----+-------------+---------------------+------+---------------+------+---------+-------+---------+-------------+
1 row in set (0.12 sec)

mysql> EXPLAIN DELETE FROM abc_mins WHERE a_time < (NOW() - INTERVAL 65 MINUTE); 
`
+----+-------------+---------------------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table               | type | possible_keys | key  | key_len | ref  | rows     | Extra       |
+----+-------------+---------------------+------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | abc_mins            | ALL  | NULL          | NULL | NULL    | NULL | 16454335 | Using where |
+----+-------------+---------------------+------+---------------+------+---------+------+----------+-------------+
1 row in set (0.00 sec)

我嘗試在 a_time 列上再添加一個索引，但沒有運氣。DELETE 查詢在主伺服器上執行並在從伺服器上複製。並且 SELECT 查詢僅在從屬設備上執行。mysql 5.6版binlog格式混合在master上

我會嘗試一個索引：

location, abc_key, a_time

如果您想稍微擴展一下並創建一個覆蓋索引

location, abc_key, a_time, val

然而，沒有主鍵的表有點可疑。事實上，沒有什麼是強制性的。你可能想調查為什麼會這樣，如果可能的話修復

給定

select  location, a_time, sum(val) val
   from  abc_mins
   where  abc_key like 'xyz:requests:3189:%'
     and  location='Pune'
   group by  1,2
   order by  1,2 desc
   limit  4;

INDEX(location, abc_key) 按此順序將允許它過濾到很少的行以進行分組和排序。

可能對有INDEX(location, a_time)幫助GROUP BY，但對 沒有幫助ORDER BY（因為您沒有使用 8.0）。因此，在僅提供 4 個結果之前會有一個很大的臨時文件。

但是，有一個問題VARCHAR(500) CHARSET utf8。但是，但是，我在這裡提供了 5 種解決方法——您可以決定哪些（如果有）可以應用於您的案例。請注意，這意味著如果沒有一些解決方法，Lennart 的建議將無法工作。

至於奴隸滯後——SELECT這是讓奴隸陷入困境嗎？如果在此處修復索引後，您仍然有一個滯後的奴隸，那麼使用您的下一個滯後理論開始一個新問題。

long_query_time=1同時，使用和打開慢日誌log_slow_slave_statements=ON。

缺少 aPRIMARY KEY很頑皮，但可能不會影響這一點SELECT。

如果DELETEs造成麻煩，我在這裡有幾個建議

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