MySQL：提高大型表的性能，以正確使用複合索引動態建構查詢
假設我有一個包含約 3000 萬個條目和 40 列的 MySQL 表，我有一個高度活躍的查詢（5 個查詢/秒），它非常慢（平均約 20 秒）並且掃描的行數很高（平均 50.000行）。隨著表的增長，性能越來越差。我想通過添加正確的複合甚至覆蓋索引來解決問題。
教義查詢由動態查詢建構器建構，涉及以下屬性（任何查詢中僅使用 userId，所有其他列有時僅用於過濾）：
- 總是：
user_idint with=$$ > 1 m users, but single users may have > 200K entries $$- 有時：
statusvarchar(20) 與IN()$$ 7 possibilities $$- 有時：
expiration_timestamp日期時間與<$$ can be any timestamp $$- 有時：
typevarchar(20)( 與IN()$$ 7 possibilities $$- 稀有：
namevarchar(255) 與LIKE$$ with trailing wildcard, rarely repetitive $$- 非常罕見：
tagsvarchar(2000) 與LIKE$$ with leading and trailing wildcard $$- 經常：
orderBy id int DESC$$ id is the primary key, the orderBy is necessary $$未經測試（將需要具有維護視窗的生產部署，包括短停機時間）我會提出以下解決方案：
CREATE INDEX listing ON items(user_id,status,type,name,expiration_timestamp,id);這是我的推理：首先，
user_id總是與相等比較一起使用，所以這應該是第一個。status並且type有一個IN子句，因此它們應該是第二個。第三個是name，因為即使LIKE使用尾隨萬用字元，它也是高度選擇性的。索引expiration_timestamp將有助於顯著減少結果的數量。id由於 MySQL 使用索引進行排序，因此將 放在復合索引的末尾是有意義的。沒有理由將標籤放入索引中，因為帶有前導萬用字元的 LIKE 上的索引是無用的。這是正確的方法還是你會建議在這裡改進一些東西？
還有一個我不確定的事實：如果查詢沒有類型或狀態，MySQL 是否足夠“智能”以使用我的複合索引？對 MySQL 索引還是很陌生，感謝您的幫助！
這樣的索引有幾個問題。
您描述的所有條件
user_id = ?都被視為範圍條件。範圍條件是在每種情況下匹配多個值的任何條件。所以<,IN(),LIKE, 都是范圍條件。這是第一個問題：在復合索引中，只會使用範圍條件中涉及的一列。
範例：假設您在假設表中的 (a,b,c) 上有一個索引。
SELECT ... WHERE a = 1 AND b < 10 AND c IN (1,2,3)這將僅使用索引的 (a,b) 列。在範圍條件中使用第一列之後，需要逐行評估索引後續列的條件。
實際上，有一種緩解方法，即index condition pushdown。這會自動發生。但這不如真正的索引查找好。
第二個問題是索引中使用的列必須是連續的。如果您嘗試“跳過”一列，它不能使用索引中的列。
例子：
SELECT ... WHERE a = 1 AND c IN (1,2,3)我說過，除了用於相等的列之外，您還可以擁有一列，這個範例查詢似乎滿足了這一要求。但是，如果索引在列 (a,b,c) 上，但此查詢中沒有 b 上的條件，則 c 列上的條件也不能使用索引。
第三個問題是 ORDER BY 優化也被查詢中的任何範圍條件所破壞。也就是說，一旦查詢執行了範圍條件，排序順序就不會隱含在索引順序中。
所以底線是，給定您的動態查詢，在給定的執行中可能包含或不包含不同條件的混合，您無法創建一個滿足所有情況的單一複合索引。
您可以做的是創建幾個複合索引：
(user_id, status) (user_id, expiration_timestamp) (user_id, type) (user_id, name)然後讓優化器根據包含的動態條件選擇與給定查詢最相關的查詢。
但無論如何，
ORDER BY id都需要文件排序。