MySQL：如何優化導致負載非常高的某個 SELECT 語句？
有一個包含 25.000.000 個條目的表，我有以下查詢，每 2 秒啟動一次，這會導致非常高的負載（亞馬遜 AWS 中高達 40 AAS）。執行需要 20 秒到 5 分鐘，這甚至會導致使用者瀏覽器超時和高丟棄率。
SELECT COUNT ( * ) AS `chk` FROM ( SELECT `item_id` FROM `items` WHERE `item_status` IN (...) AND `item_type` = ? AND `user_id` != ? AND `item_name` IN (...) LIMIT 3 ) AS OTHERS ;索引優化已經完成 -
user_id,item_name,item_type並且item_status都被索引（每列一個索引）。更多資訊：
- 一個使用者有 1 - 1.000.000 百萬個條目
- item_name 是
varchar128- item_type 的基數為 7
- item_status 的基數也為 7
- 只需要知道是否有三個或更多匹配項
請注意，在大約 50% 的情況下，MySQL 必須檢查完整的表，因為找到的項目少於 3 個。所以這個限制只有在超過 3 個項目的情況下才有幫助。
雖然我對某些結果進行 Redis 記憶體，但對於此查詢，這是不可能的，因為始終需要準確的結果。隨著數據庫以每秒約 1 個條目的速度增長，查詢性能變得非常快。
雖然過去我可以解決大多數索引問題，但這裡有一個嚴重的問題。想過用一些觸發器或視圖來解決問題，但我不確定這是否有幫助？在高度活躍的生產數據庫中，在不知道是否解決問題的情況下進行此類更改是危險的。
我在這裡向專業人士提出的問題是：如何用 MySQL 解決這個問題？
您可以嘗試使用複合索引。
您的索引可能如下所示：
INDEX (item_type, item_name, item_status, user_id)該索引應該允許 MySQL 有效地查找所有行，而無需從實際表中查找任何行。
列的順序在這裡很重要：
item_type排在第一位，因為您在列上有一個相等過濾器。item_name並排在第二和item_status第三，因為兩者都使用IN. 列的順序可以更改，但因為item_name可能比item_status首先擁有更高的基數可能會更快一些。user_id排在最後，因為它使用了一個不能用索引有效處理的不等式過濾器。一般來說，MySQL 每個表只使用 1 個索引¹，因此擁有超過 1 個索引並沒有幫助。
此外，如果索引看起來不會過濾足夠多的行（查詢計劃器根據表和索引統計資訊計算出*足夠多的行），MySQL 也不會使用索引。*這樣做的原因是，當使用索引時，MySQL 仍然需要查找表中的原始行（除非您有覆蓋索引²），這有點昂貴，因此使用索引查找大量行實際上可能比進行全表掃描。
鑑於您的表中的行數，如果不是全部，您的 4 個現有索引中的大多數可能每個值都有太多行，並且 MySQL 甚至不會考慮此查詢。儘管這實際上取決於數據分佈和基數。
您可以檢查使用了哪些索引以及它們是否覆蓋或不使用
EXPLAIN. 例如EXPLAIN SELECT COUNT ( * ) AS `chk` FROM ( SELECT `item_id` FROM `items` WHERE `item_status` IN (...) AND `item_type` = ? AND `user_id` != ? AND `item_name` IN (...) LIMIT 3 ) AS OTHERS;如果您使用 MySQL 8，您還可以使用它
EXPLAIN ANALYZE來實際了解 MySQL 如何讀取和過濾計時數據。Rick James 寫了一篇很棒的文件，介紹如何為 SELECT 建構最佳索引，我建議您閱讀該文件。他還寫了一篇關於復合（複合）索引的文件，我也可以推薦。
將來請嘗試至少提供表模式和索引（您可以
SHOW CREATE TABLE ?用於此）以及執行有問題的查詢的輸出EXPLAIN以及 MySQL 版本。這減少了我們必須做的猜測，並允許我們給出更具體和更好的答案。¹ 有一個優化，MySQL 可以使用多個索引，稱為索引合併優化，但它只在極少數情況下有效，應該避免。
² 覆蓋索引是包含查詢中使用的所有列的索引。由於所有列都已經在索引中，MySQL 不需要從表本身獲取其他列。我在這個答案中提出的索引是覆蓋索引的一個例子。