MySQL / MariaDB：許多索引與一個多列索引

我需要優化我的 MariaDB 表索引。我在 StackExchange 上閱讀了很多非常有趣的東西，但我不確定我的情況是否有答案。

就我而言，我有一個名為的表TableA，其中包含超過 4000 萬條記錄（用於統計目的），其中包含以下列：

• ID(PK)
• A(INT)
• B(INT)
• C(INT)
• D(INT)

我對這個表進行了不同的查詢，WHERE子句中有 4 列中的許多列。例如，我可以WHERE在同一查詢的子句中包含這些列：

• A、B 和 C
• 甲和乙
• A和D
• A、C 和 D
• 乙和丁
• B、C 和 D
• C和D
• 只有一個
• 只有乙
• 等等。

我可以遇到每一個案例。索引超過4000 萬條記錄的表的最佳方法是什麼？我應該只製作一個包含所有 4 列的 INDEX 嗎？還是 4 個不同的索引？這個表每次都在我的應用程序中使用，並且包含敏感數據……（用於統計目的）。

謝謝您的幫助 ！

喬佛里

沒有完美的答案。一個可能很好的答案是…

首先，列是否與= constant？或“範圍”，例如> constant？現在，我只假設=.

價值觀的選擇性如何？真/假值不是；讓我們忽略它們。

假設“最壞”的情況（總是=，都是選擇性的），讓我們採用兩列的 6 個排列：

INDEX(a, b) -- for (a), (a,b), (a,b,c), (a,b,d), (a,b,c,d)
INDEX(a, c)
INDEX(a, d)
INDEX(b, c)
INDEX(b, d)
INDEX(c, d)

此列表將有效地處理所有一列和兩列測試，並且至少在您使用 3 或 4 列時有所幫助。

如果某個列總是在一個範圍內使用，那麼永遠不要在INDEX. 例如，如果b始終通過範圍進行測試，則將第 4 和第 5 更改為(c,b)和(d,b)

其他提示：

• 你說INT。這允許 40 億個值並佔用 4 個字節。使用較小的數據類型。需要 40M 個不同的值INT UNSIGNED，但如果您有隻有一百萬個不同值的東西，請考慮MEDIUMINT UNSIGNED​​（3 個字節，0..16M）。等等。基本原理是使表和索引更小有助於減少 I/O，從而提高速度。
• 你需要id嗎？或者是一些列的組合是獨一無二的，可以成為PK，在那裡被淘汰id？
• 如果有共同的模式，擴展一些索引。例如，如果通常當使用者測試a和時c，他們也測試b，則更(a, c)改為(a, c, b).
• 使用 InnoDB。
• WHERE如果任何列是“低基數”，則當子句僅提及該列時將不使用索引；忍受它。該列與其他列結合使用時效果很好。
• 不要過度標準化。我希望 a,b,c,d 不是浮點數或日期時間的標準化。請注意這與我對“範圍”的評論有何關聯。

如果您想進一步討論，請提供有關所涉及數據和查詢的一些額外線索。（請注意，我必須對標誌和範圍進行揮手。）

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