為什麼某個欄位會導致查詢慢？

我在一個頁面上有 6 個幾乎相同的查詢。我正在按鍵名載入日誌。

緯度和經度各佔用 4 秒，而其他 4 個值佔用 0.09 秒 查詢中的一個文本值怎麼會導致這種情況，請參閱下面的 2 個查詢。

此查詢需要 4 秒：

SELECT
 h0_.keyname AS keyname_0,
 h0_.sensorvalue AS sensorvalue_1,
 h0_.receiveddate AS receiveddate_2,
 DATE_FORMAT(h0_.receiveddate, '%Y-%m-%d %H:%i') AS sclr_3
FROM history h0_
WHERE h0_.company_id = 1
 AND h0_.receiveddate > '2019-05-01 14:00'
 AND h0_.receiveddate < '2019-05-01 17:01'
 AND h0_.keyname = 'longitude'
 AND h0_.device_id = 9
GROUP BY sclr_3;

+----+-------------+-------+------------+-------+--------------------------------------------------------------------------+--------------+---------+------+-------+----------+---------------------------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys                                                            | key          | key_len | ref  | rows  | filtered | Extra                                                               |
+----+-------------+-------+------------+-------+--------------------------------------------------------------------------+--------------+---------+------+-------+----------+---------------------------------------------------------------------+
|  1 | SIMPLE      | h0_   | NULL       | range | IDX_27BA704B979B1AD6,IDX_27BA704B94A4C7D4,receiveddate,keyname,device_id | receiveddate | 5       | NULL | 24022 |     0.15 | Using index condition; Using where; Using temporary; Using filesort |
+----+-------------+-------+------------+-------+--------------------------------------------------------------------------+--------------+---------+------+-------+----------+---------------------------------------------------------------------+

此查詢需要 0.09 秒：

SELECT
 h0_.keyname AS keyname_0,
 h0_.sensorvalue AS sensorvalue_1,
 h0_.receiveddate AS receiveddate_2,
 DATE_FORMAT(h0_.receiveddate, '%Y-%m-%d %H:%i') AS sclr_3
FROM history h0_
WHERE h0_.company_id = 1
 AND h0_.receiveddate > '2019-05-01 14:00'
 AND h0_.receiveddate < '2019-05-01 17:01'
 AND h0_.keyname = 'temperature'
 AND h0_.device_id = 9
GROUP BY sclr_3;

+----+-------------+-------+------------+-------+--------------------------------------------------------------------------+--------------+---------+------+-------+----------+---------------------------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys                                                            | key          | key_len | ref  | rows  | filtered | Extra                                                               |
+----+-------------+-------+------------+-------+--------------------------------------------------------------------------+--------------+---------+------+-------+----------+---------------------------------------------------------------------+
|  1 | SIMPLE      | h0_   | NULL       | range | IDX_27BA704B979B1AD6,IDX_27BA704B94A4C7D4,receiveddate,keyname,device_id | receiveddate | 5       | NULL | 24022 |     0.48 | Using index condition; Using where; Using temporary; Using filesort |
+----+-------------+-------+------------+-------+--------------------------------------------------------------------------+--------------+---------+------+-------+----------+---------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

以下是一些細節：

• mysql Ver 14.14 Distrib 5.7.26，適用於 Linux
• Ubuntu 18
• 6GB 記憶體
• 2個中央處理器

該表有以下內容

   CREATE TABLE `history` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `company_id` int(11) NOT NULL,
 `device_id` int(11) DEFAULT NULL,
 `receiveddate` datetime NOT NULL,
 `keyname` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
 `sensorvalue` decimal(20,10) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `IDX_27BA704B979B1AD6` (`company_id`),
 KEY `IDX_27BA704B94A4C7D4` (`device_id`)
) ENGINE=InnoDB AUTO_INCREMENT=26656 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

我看不出任何可以解釋時間差異的東西。但是，我看到了一些需要改進的地方。他們甚至可能會在 0.09s 上有所改進。

WHERE h0_.company_id = 1
 AND h0_.receiveddate > '2019-05-01 14:00'
 AND h0_.receiveddate < '2019-05-01 17:01'
 AND h0_.keyname = 'longitude'
 AND h0_.device_id = 9

求一個“複合”索引：

INDEX(company_id, key_name, device_id, -- in any order
     receivedate)

這是一個奇怪的範圍；它是 3 小時 + 1 分鐘 - 1 秒：

 AND h0_.receiveddate > '2019-05-01 14:00'
 AND h0_.receiveddate < '2019-05-01 17:01'

decimal(20,10)似乎過分了。它佔用 10 個字節，並且比幾乎任何消費級感測器都具有更高的精度。考慮一個 4 字節FLOAT（沒有 (m,n)）。

您是否在一分鐘內收到多個值？如果是這樣，那GROUP BY是不合適的。輸出將選擇可能的感測器值和接收值之一，它們甚至可能相互對應。要麼刪除GROUP BY要麼做某種聚合，例如AVG().

有多少不同的keyname值？需要255varchar(255)嗎？您是否有數百萬行，而這是表格中相當龐大的部分？另請參閱ENUM。

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