最優索引策略

為了便於討論，請參考下面我們電子商務數據庫的簡化結構（使用InnoDB引擎在MySQL 5.6上執行）。此時，事務和transaction_items表大約位於。11.5MM和15MM行/每行。這 2 個也是（每天）使用最多的表，用於記錄新事務/ transaction_items並生成用於分析的摘要/匯總報告。

問題： 截至目前，我們認為（除了表中數據的大小）我們有很多索引佔用了不必要的空間，並且可能會被刪除或更改以提高磁碟使用效率。下面是幾個例子：

問題：僅 關注事務表，您將如何對錶進行最佳索引以執行涉及一個或多個****外鍵列以及is_void列（通常為 NULL）的基於日期範圍的查詢？

以下是我們現有查詢的一些範例：

SELECT 
t.* 
FROM 
transactions t 
WHERE 
t.location_id IN (1,2,3) 
AND t.created_at BETWEEN '2020-12-01 04:00:00' AND '2020-12-01 03:59:59' 
AND t.is_void IS NULL;

SELECT
t.* 
FROM
transactions t 
WHERE 
t.staff_id IN (1,2,3)
AND t.created_at BETWEEN '2020-12-01 04:00:00' AND '2020-12-01 03:59:59' 
AND t.is_void IS NULL;

SELECT
t.location_id,
t.topic_id,
COUNT(t.id) AS topic_count
FROM
transactions t 
WHERE 
t.created_at BETWEEN '2020-12-01 04:00:00' AND '2020-12-01 03:59:59' 
AND t.is_void IS NULL
GROUP BY
t.location_id, 
t.topic_id;

SELECT
t.location_id,
t.staff_id,
COUNT(t.id) AS staff_txs
FROM
transactions t 
WHERE 
t.created_at BETWEEN '2020-12-01 04:00:00' AND '2020-12-01 03:59:59' 
AND t.is_void IS NULL
GROUP BY
t.location_id, 
t.staff_id;

SELECT 
t.* 
FROM 
transactions t 
WHERE 
t.customer_id = 23
AND t.created_at BETWEEN '2020-07-01 04:00:00' AND NOW()
AND t.is_void IS NULL
ORDER BY
t.created_at DESC;

編輯 - 2020 年 12 月 24 日 @ 東部標準時間下午 3:05 數據庫：使用 InnoDB 引擎的 MySQL 5.6

因此，以最簡單的方式解決這個問題，我在您的範例查詢中看到的常見謂詞是created_atand is_void。所以你應該有一個至少由這兩列組成的索引（最好按照最唯一到最不唯一的順序），所以索引中的第一列很可能是created_at，然後是is_void後面的列。

從那裡您可以決定是否要讓您的索引完全覆蓋您在上面提供的每個場景，例如一個索引用於created_at, customer_id, is_void; created_at, staff_id,等的另一個索引is_void（再次注意這些欄位是按唯一性排列的）。created_at或者，如果您只想讓通用is_void索引部分覆蓋每個場景，然後您的查詢將執行額外級別的過濾。

通過查看表模式，transactions您似乎有 5 個外鍵，因此將索引與created_atandis_void欄位一起滿足每個外鍵（如果適用於您的場景）並不是世界末日。五項指標合理。而且您不需要完全覆蓋每個案例，但通常最好確定您的主要案例並嘗試通過在索引中使用這些場景的謂詞來盡可能地覆蓋它們。

它也經常看起來像你SELECT location_id，所以它會成為INCLUDE你的索引的候選者（它不是索引本身的謂詞）。例如CREATE INDEX idx_txs_on_created_at_is_void_includes_location_id ON transactions (created_at, is_void) INCLUDE (location_id)

根據您使用的數據庫系統（您應該在問題中標記），您可能還可以利用其他功能來改進索引，例如Colin 提到的部分（過濾）索引。由於您的正常案例（基於您提供的範例）似乎總是過濾is_void IS NULL然後這將成為部分索引中的一個很好的候選者。

INDEX(customer_id, is_void, location_id)
INDEX(customer_id, is_void, staff_id)
INDEX(customer_id, is_void, created_at)
INDEX(is_void, created_at, location_id, topic_id)
INDEX(is_void, created_at, location_id, staff_id)

每個列的順序INDEX很重要。包含is_void對性能很重要；忽略它是您的索引沒有太大幫助的原因。

更多討論：http: //mysql.rjweb.org/doc.php/index_cookbook_mysql

請SHOW CREATE TABLE以文本格式提供，而不是圖像，而不是索引名稱。

其他建議：

• 使用COUNT(*)而不是COUNT(id).
• 用來DECIMAL賺錢，不是FLOAT。
• 刪除不必要的INDEXes可以節省一些磁碟空間，只加快INSERTs一點速度，通常是低優先級。
• 同樣，“部分索引”不會比完整索引好多少。（無論如何，沒有任何版本的 MySQL 費心去實現它。）
• 其他查詢可能會也可能不會使用您擁有的索引或我建議的索引。

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