MYSQL：如何優化具有三重主鍵索引的日期排序表

幾年來，我一直在維護一個 MYSQL 數據庫，這可以追溯到我上數據庫管理課程的時候。它的最大表已經增長（1,8 GB 的 1500 萬個條目）並且這個查詢開始需要幾分鐘才能載入（pkey1 是 DATETIME，pkey2 和 pkey3 是 int(11)）：

SELECT * FROM table 
WHERE pkey2 = ... 
 AND pkey3= ... 
 AND HOUR(pkey1) = 0 
ORDER BY pkey1 DESC

該表的索引為(pkey1,pkey2,pkey3) BTREE，列數為20。

ORDER BY 曾經是 ASC，執行速度很慢，然後我將其更改為 DESC（因為如果無法優化，我將更改此分頁請求），並從 5.7x 遷移到 8 以便能夠重新創建此錶帶有 DESC 索引。現在甚至更慢了。

問題是：

• 將 DATETIME 更改為 DATE（從而刪除不再需要的 HOUR(0)）會以足夠令人滿意的方式解決問題嗎？我不再需要它，特別是如果它引起問題。
• 如果沒有，你能建議我做什麼？

更進一步：

據稱因為這張表，我的整個系統（弱：2Gb RAM，單核，20GB SSD）變慢了，甚至一個簡單的數據庫連接也延遲了幾秒鐘。如果這個表不是減速的情況（即使這個請求的觸發似乎是問題），這裡是我從 PHPmyadmin 得到的令人震驚的變數：

如您所見，事情可能是錯誤的。

感謝您的閱讀！

您的查詢不是 sargeable。Hour()在復合主鍵的第一列 ( ) 上使用函式pk1正在禁止使用主鍵進行索引查找；因此（在沒有任何其他適當索引的情況下），它很可能會進行全表掃描。定義工作索引時要遵循的一般經驗法則是：

1. 應優先考慮所有列，這些列位於WHERE子句內，並由AND子句連接，並使用、或與常量值進行比較。因此，在您的查詢中，下面有兩列：和.=``IS NULL``<=>``Data_Type``Status
2. 可以優先考慮以下三種情況：

• 具有範圍條件的列。
• GROUP BY子句中特定順序的列（如果存在）。
• ORDER BY子句中特定順序的列（如果存在）

現在，來回答你的問題：

將 DATETIME 更改為 DATE（從而刪除不再需要的 HOUR(0)）會以足夠令人滿意的方式解決問題嗎？我不再需要它，特別是如果它引起問題。

是的，你應該這樣做；如果時間部分與您無關。在MySQL 8+中，datetime 的大小為 5 個字節，而 date 的大小為 3 個字節。因此，您還將有效地節省大量空間。

通過此更改，您的新查詢將只是：

SELECT * FROM table 
WHERE pkey2 = ... 
 AND pkey3= ... 
 -- removed AND HOUR(pk1) = 0 
ORDER BY pkey1 DESC

在這種情況下，如果您參考上面討論的拇指規則，您需要定義一個新的複合索引(pkey2, pkey3, pkey1)（順序已更改；您需要在已定義的主鍵之外保留此索引）。

ALTER TABLE table_name ADD INDEX (pkey2, pkey3, pkey1);

這應該能夠對您有很大幫助。

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