查詢等式的最佳索引策略一種一種A， 範圍乙乙B， 通過…分組CCC, AND 排序方式count(P)C○你n噸(磷)count(P)

我有一個表現不佳的查詢：

SELECT  user_id, count(item_id) as count
FROM table items 
WHERE category = 'magazine'
AND created_at > 1384754400
GROUP BY user_id
ORDER BY count(item_id) desc
LIMIT 100

為了優化此查詢，最佳索引策略是什麼？

表詳細資訊

具有以下結構/基數的5 億條記錄：

• PRIMARY KEY (item_id)- 基數：500 M
• user_id- 基數：~ 25 M
• category- 基數：~ 2.5 M
• created_at- 基數：~ 150 M

索引：

• 我對每個 user_id、category 和 created_at 欄位都有單獨的索引

我還有以下覆蓋索引：

• ( category, user_id) -這是查詢優化器在執行解釋時的預設值
• ( category, created_at)
• ( category, created_at, user_id) -這是我試圖創建的一個以優化此查詢，但是，它似乎並沒有很好地工作。

如果您只想針對此查詢進行優化。這是最好的索引：

ALTER TABLE items ADD INDEX (category, created_at, user_id)

這優化了過濾器的值，從而減少了您接觸的數據總量。通過在查詢末尾添加user_id,可以使索引覆蓋並節省您對主索引的查找。item_id

我們可以假設它item_id不是 NULL（因為它是 PRIMARY 索引）。

但是，由於 MySQL 優化器非常愚蠢，您可能需要像這樣重寫：

SELECT  user_id, SUM(count) AS count
FROM
(
 SELECT category, created_at, user_id, COUNT(*) as count
 FROM items
 WHERE category = 'magazine'
 AND created_at > 1384754400
 GROUP BY category, created_at, user_id
) AS d
GROUP BY user_id
ORDER BY count DESC
LIMIT 100

我會刪除所有索引並重新開始。我認為一個類別索引就足夠了，或者最多一個（類別，created_at）。給定一個類別，應該有大約 200 個匹配項，所以這取決於 created_at 過濾器是否丟棄了很多行。您能否顯示查詢計劃和數字來驗證查詢的執行情況？我也不知道它是否重要，但 count(1) 也足夠了，而不是命名一個列。

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