為什麼帶有索引的 MySQL ‘where’ 子句會使 SUM 查詢變慢？

我有一張有 500 萬條記錄的表。表列是id、customer_id、status和amount。當我執行沒有索引（狀態）的查詢時，

SELECT SUM(amount) FROM `game_results` WHERE `status` = 1

查詢需要 4 秒才能完成，但是當我向狀態列添加索引時，查詢需要 45 秒才能完成。我需要狀態列的索引，因為我還需要在此表上進行搜尋查詢。

我該如何解決這個問題？

帶索引的慢查詢 - 解釋

帶索引的慢查詢 - ANALYZE

沒有索引的查詢 - 解釋

沒有索引的查詢 - ANALYZE

customer_id、game_id、date、prize_type 可過濾。

金額，匯總統計的點列。

顯示創建表

CREATE TABLE `game_results`(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`customer_id` bigint(20) NOT NULL,
`game_id` bigint(20) NOT NULL,
`amount` decimal(10,2) NOT NULL DEFAULT '0.00',
`prize_id` smallint(6) NOT NULL DEFAULT '0',
`prize_type` smallint(6) NOT NULL DEFAULT '1',
`point` decimal(10,2) NOT NULL DEFAULT '0.00',
`ext_id` bigint(20) DEFAULT NULL,
`ext_value` bigint(20) DEFAULT NULL,
`status` smallint(6) NOT NULL DEFAULT '0',
`site_user` bigint(20) NOT NULL DEFAULT '0',
`date` bigint(20) NOT NULL DEFAULT '0',
`update_date` bigint(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10009524
  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Rolando 的綜合指數是這個問題的“完美”解決方案。

或者是嗎？

當然，它使一個查詢的執行速度提高了 4 倍。我認為 1 秒仍然“太長”。為了討論這個問題，我們需要了解“為什麼”這個查詢是必要的，以及它的執行頻率。

另外，讓我們看看擁有該索引的副作用。列名 (status和amount) 聞起來像會經常更改的列。如果是這樣，那麼請注意，更新INDEX(status, amount)就像從 BTree 的一個位置刪除一行並在另一個位置插入另一行。誠然，InnoDB 在使這兩個步驟相當高效方面做得很好（參見“更改緩衝”），但它不是免費的。此成本發生在UPDATE修改status和/或的語句之後amount。在宏偉的計劃中，這可能沒什麼大不了的。

如果沒有有關該應用程序的更多資訊，我不一定會推薦“匯總表”或“觸發器”作為將 1 秒減少到 0.1 秒以下的方法。

對於“屬於使用者 1 的所有遊戲結果的列表”，您可能需要INDEX 以 user_id.

對於“上個月遊戲金額的匯總”，並且沒有其他過濾，以 開頭的索引date 可能有用。

我看到“基數 = 1” status。這意味著優化器認為只有一個不同的值status。如果是這種情況，優化器可能會做不同的事情。（當然，在測試該值INDEX(status)時對您給出的查詢毫無用處。）

狀態索引使其變慢的原因是：

• 的值status是從索引中讀取的
• 的值amount必須查表

這意味著必須為每個索引條目分別讀取索引和表。在您的情況下，在不使用索引的情況下進行全表掃描（其中status和amount值都在）會更好。您需要的是一個良好的覆蓋索引，一個包含特定查詢所需的所有列的索引。

刪除status索引並在 上添加複合索引status,amount：

ALTER TABLE game_results
   DROP INDEX status
  ,ADD INDEX status_amount_ndx (status,amount)
  ,ALGORITHM=INPLACE
  ,LOCK=NONE
;

查詢將使用這個新索引 ,status_amount_ndx來獲取status和amount值，而無需從表中讀取。

試試看！！！

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