有沒有辦法提高這個簡單的 SELECT 查詢的性能？

我對 MySQL 還比較陌生，所以請原諒我的任何無知。我嘗試過查看此處和 StackExchange 上的其他文章，但在提高這個簡單 SELECT 查詢的性能方面並沒有太多運氣。讓我從表模式、SELECT 語句和它的 EXPLAIN 開始：

桌子：

CREATE TABLE `Observations` (
 `InstrumentID` bigint(20) NOT NULL,
 `Epoch` bigint(20) NOT NULL,
 `EndingEpoch` bigint(20) DEFAULT NULL,
 `PhenomenonName` varchar(16) NOT NULL,
 `Value` decimal(18,5) DEFAULT NULL,
 `IsEstimated` bit(1) DEFAULT NULL,
 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`InstrumentID`,`PhenomenonName`,`Epoch`,`Value`),
 KEY `FK_31` (`PhenomenonName`),
 KEY `idx_Epoch` (`Epoch`),
 CONSTRAINT `FK_27` FOREIGN KEY (`InstrumentID`) REFERENCES `Instruments` (`InstrumentID`),
 CONSTRAINT `FK_31` FOREIGN KEY (`PhenomenonName`) REFERENCES `Phenomena` (`PhenomenonName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

選擇：

SELECT PhenomenonName, Value, Epoch
FROM Observations
WHERE InstrumentID=2
AND Epoch BETWEEN 1514782800 AND 1546318740
AND PhenomenonName IN ('demand')
ORDER BY Epoch

解釋：

{
   "id": 1,
   "select_type": "SIMPLE",
   "table": "Observations",
   "partitions": null,
   "type": "range",
   "possible_keys": "PRIMARY,FK_31,idx_Epoch",
   "key": "PRIMARY",
   "key_len": "34",
   "ref": null,
   "rows": 1042464,
   "filtered": 100,
   "Extra": "Using where"
}

抱歉，解釋是 JSON，我知道這不是典型的，但我公司的 VPN 需要更新網路配置，所以我現在無法通過 Workbench 連接。

因此，此查詢是針對 1 年 1 分鐘的數據，即約 18M 行的表中的約 525K 行，需要約 22 秒。最初 PRIMARY 鍵在 PhenomenonName 之前有 Epoch，這迫使它執行 index_merge_intersect。但是，修復鍵的順序（如您所見，現在只使用 PRIMARY 鍵）只減少了 1 秒。我也嘗試過在桌子上執行 ANALYZE 並更改Epoch BETWEEN 1514782800 AND 1546318740為Epoch >= 1514782800 AND Epoch <= 1546318740，這兩者似乎都沒有區別。

由於我們仍在開發伺服器，因此現在的硬體非常小——只有一個 db.t2.small，它是 1 個 vCPU 和 2GB RAM。但是，將其增加到 db.t2.large、2 個 vCPU 和 8GB RAM，將其縮短到 13 秒。然後再次將其增加到 db.t2.2xlarge、8 個 vCPU 和 32GB RAM，似乎並沒有提高性能——它仍然是大約 13 秒。此外，根據 AWS 的預設設置，儲存為 100 GiB SSD，緩衝池大小為總 RAM 的 75%。

這只是硬體限制嗎？如果是這樣，那很好，但我想確保我不會錯過任何優化表模式或選擇語句的機會，特別是因為這只是對一種工具的查詢，並且查詢通常必須聚合幾個他們在一起。

提前謝謝，如果我能提供更多資訊，請告訴我。

編輯：順便說一句，這是不相關的，但正如你所見，我有兩個外鍵限制，但其中只有一個創建了一個實際的鍵。這有什麼原因嗎？我的理解是外鍵約束會自動創建一個鍵。

Edit2：查詢在小實例上花費了大約 23 秒，並且由於按照@SQLRaptor 的建議將 Value 添加到 PRIMARY KEY，因此它減少到了大約 21 秒。在 db.t2.2xlarge 上仍然大約 13 秒。

525K 行需要時間來鏟。從磁碟讀取需要時間（除非它碰巧記憶體在 RAM 中。通過線路將這麼多行發送到客戶端需要時間。客戶端必須接收它們。

指標最優；它具有所需順序的所需列。但這很可疑。請記住PRIMARY KEY，根據定義，a 是唯一的。在PK中出現“值”通常是不合理的。如果你把它排除在外，也沒有什麼區別。

將做出小的改進的一件事是在可行的情況下縮小數據類型。幾乎沒有應用程序需要BIGINT，但你有 3. BIGINT是 8 個字節。 INT只有 4 個字節，可以保存一個時間戳（一個時代）。當然，您沒有數十億的“儀器”。 PhenomenonName可以正規化或變成 1-byte ENUM。

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