如何使用各種排序優化 MySQL 查詢的索引

我有一個 INNODB 表levels：

+--------------------+--------------+------+-----+---------+-------+
| 領域 | 類型 | 空 | 鑰匙 | 預設 | 額外 |
+--------------------+--------------+------+-----+---------+-------+
| 編號 | 整數(9) | 否 | 優先級 | 空 | |
| 級別名稱 | varchar(20) | 否 | | 空 | |
| 使用者 ID | 整數(10) | 否 | | 空 | |
| 使用者名 | varchar(45) | 否 | | 空 | |
| 評級 | 十進制(5,4) | 否 | | 0.0000 | |
| 票 | 整數(5) | 否 | | 0 | |
| 戲劇| 整數(5) | 否 | | 0 | |
| 發布日期 | 日期 | 否 | 穆爾 | 空 | |
| 使用者評論 | varchar(255) | 否 | | 空 | |
| 可玩角色 | 整數(2) | 否 | | 1 | |
| is_featured | 小整數(1) | 否 | 穆爾 | 0 | |
+--------------------+--------------+------+-----+---------+-------+

大約有 400 萬行。由於前端功能的原因，我需要使用各種過濾器和排序來查詢此表。它們在playable_character、rating、plays和上date_published。date_published可以過濾以按最後一天、一周、一個月或任何時間（過去 3 年）顯示。還有分頁。因此，根據使用者的選擇，查詢可能看起來像以下之一：

SELECT * FROM levels
WHERE playable_character = 0 AND
   date_published BETWEEN date_sub(now(), INTERVAL 3 YEAR) AND now()
ORDER BY date_published DESC
LIMIT 0, 1000;

SELECT * FROM levels
WHERE playable_character = 4 AND
   date_published BETWEEN date_sub(now(), INTERVAL 1 WEEK) AND now()
ORDER BY rating DESC
LIMIT 4000, 1000;

SELECT * FROM levels
WHERE playable_character = 5 AND
   date_published BETWEEN date_sub(now(), INTERVAL 1 MONTH) AND now()
ORDER BY plays DESC
LIMIT 1000, 1000;

我應該補充一點rating，並且plays總是被查詢為DESC. 只能date_published是DESC或ASC。

我從一個索引開始，該索引idx_date_char(date_published, playable_character)在此處的第一個範例查詢中執行良好。根據其他一些答案，我更改為其他兩個索引（date_published、playable_character、plays）和（date_published、playable_character、rating）。

第一個查詢仍然執行得非常快，但是在 EXPLAIN 中發生了一些不尋常的事情，當 player_character = x 超過一定數量的行（~700,000）時：在 EXPLAIN 中彈出 USING WHERE。

因此，第一個問題是查詢或索引是否有任何改進，其次，應該更改哪些 MySQL 設置以允許大型結果集。

任何建議都非常感謝。TIA。

WHERE playable_character = 0 AND
   date_published BETWEEN date_sub(now(), INTERVAL 3 YEAR) AND now()

從“=”項開始，然後執行範圍：

INDEX(playable_character, date_published);

“分頁”，ORDER BY rating DESC LIMIT 4000, 1000;最好記住你“離開”的地方。這樣，您就不必掃描您不需要的 4000 條記錄。

如果插入或更新查詢不多，則創建兩個索引以獲得更好的性能：

ADD INDEX (playable_character, date_published, rating desc)

ADD INDEX (playable_character, date_published, plays desc)

否則，如果在此表上使用插入和更新查詢比創建索引更多：

ADD INDEX (playable_character, date_published)

或者不創建任何索引

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