MYSQL - 優化 MAX（CASE WHEN …）

我有一個類似於以下的表（更新：添加了一個type列**我猜太多簡化了實際查詢）

CREATE TABLE versions(
  type INT NOT NULL,
  version INT NOT NULL,
  important BOOLEAN NOT NULL,
  PRIMARY KEY (type, version)
)

執行SELECT type, MAX(version) FROM versions GROUP BY type非常高效，並且從數據庫中檢索每種類型的單行。

但是，執行SELECT MAX(CASE WHEN important=1 THEN version END) FROM versions GROUP BY type似乎會生成全表掃描。

由於我有一個版本索引，我希望 MySQL 從最高版本開始掃描，並在找到第一個具有“important=1”的版本時停止。通常，我的數據只需要掃描幾行。

查看performance_schema.events_statements_history（ROWS_EXAMINED列）似乎 MySQL 掃描整個表以檢索我需要的行。

任何有關如何提高此查詢性能的想法都將不勝感激。

對於簡單查詢（不帶GROUOP BY），您可以將它們重寫為：

SELECT version 
FROM versions 
-- WHERE important = 1
ORDER BY version DESC
LIMIT 1 ;

並檢查執行計劃是否不進行表掃描並以您期望的方式使用索引。

此外，您可以在(important, version).

---

對於GROUP BY type查詢，索引(important, type, version)更適合。

重寫該查詢並非易事，LIMIT 1因為我們需要許多最大值（每種類型一個）。但是，如果單獨使用索引並沒有太大的改進，這裡有一個不同的查詢，它將使用上述複合索引。

當不同類型值很少時，通常使用此方法提高性能：

SELECT dt.type, v.version 
FROM 
   ( SELECT type 
     FROM versions 
     WHERE important = 1
     GROUP BY type
   ) AS dt
 -- LEFT
 JOIN versions AS v
 ON  v.important = 1
 AND v.type = dt.type 
 AND v.version = ( SELECT vi.version
                   FROM versions AS vi
                   WHERE vi.important = 1
                     AND vi.type = dt.type
                   ORDER BY vi.version DESC
                   LIMIT 1 
                 ) ;

您可以嘗試多種變化。例如，刪除important = 1 （僅從派生表內部dt）並LEFT JOIN在那裡使用。這將為您提供所有類型（以及沒有版本標記為“重要”的類型的 NULL 值。

如果您有一個單獨的表，包含所有類型（即PRIMARY KEY (type)），您可以完全dt用該表替換。

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