為什麼 MySQL 不使用具有更高基數的索引?
我有一個
source
基數為 1122 的索引和一個基數為 22 的第二個索引。 MySQL在查詢時state
繼續使用該索引。這是正常的嗎?如果沒有,可能出了什麼問題?state``SELECT C1 FROM tbl WHERE source = 'x' and state = 'y'
基數
即使具有高基數,MySQL 查詢優化器使用的臨界點也不是密鑰分配就是儲存引擎。
早在 2012 年 11 月 13 日,我討論了不平衡鍵如何使查詢優化器選擇不同的索引(有時根本不選擇和索引):索引必須覆蓋所有選定的列才能用於 ORDER BY?
在那篇文章中,我載入了一個表呼叫
mf
(男性女性)並儲存了一個性別M
或F
. 我插入了 37 個 Ms 和 3Fs。然後,我執行了與 MyISAM 和 InnoDB 交叉的男性和女性值的解釋計劃。這些密鑰和儲存引擎的選擇產生了一些有趣的結果。您的查詢
您需要做的是檢查您的密鑰的分佈(或不平衡)程度。
您需要執行以下兩個查詢
SELECT IFNULL(source,'Total'),COUNT(1) RowCount FROM tbl GROUP BY source WITH ROLLUP; SELECT IFNULL(state,'Total'),COUNT(1) RowCount FROM tbl GROUP BY state WITH ROLLUP;
這將為您提供每個值的計數以及總行數。
在我之前的文章中,我說過我使用 5% 的經驗法則。查看每個值的行數。如果任何特定源或狀態的行數超過表行數的 5%,則查詢優化器會在匯流排下拋出一個索引並嘗試另一個索引。在極少數情況下,它可能只是進行全表掃描。
如果任何具有低行數的鍵不能產生好的索引選擇,您可能需要重新計算 MyISAM 表的索引統計資訊(特別是如果 MyISAM 表經歷了大量的 INSERT、UPDATE 和 DELETE,從而使索引統計資訊陳舊) . 執行這個:
ANALYZE TABLE tbl;
或對 MyISAM 表進行碎片整理並重新計算索引統計資訊,執行
OPTIMIZE TABLE tbl;
試一試 !!!
更新 2014-04-12 18:06 EDT
我喜歡 Alexandros (+1 for you) 發布的答案。
我想通過他的回答來更新和擴展我的解釋。
您提到了以下基數
state
有 22source
有 1122在這種情況下,您需要執行以下命令
ALTER TABLE tbl DROP INDEX state; ALTER TABLE tbl ADD INDEX state_source_index (state,source);
不要顛倒列的順序。您應該始終首先索引較低的基數列。
好的,創建該索引將使您的查詢變得更好。或者,會嗎???
創建索引後,請執行此查詢
SELECT IF(ISNULL(state)=1,'Total', CONCAT('Total Sources for ',state)) Statistic,RowCount FROM ( SELECT state,source COUNT(1) RowCount FROM tbl GROUP BY state,source WITH ROLLUP ) A;
這將為您提供
(state,source)
表中所有組合鍵的計數以及每個州的小計。任何RowCount
超過表 5% 的組合鍵很可能會導致表掃描。更新 2014-04-15 16:43 EDT
你的最後一條評論是
我從你的回答中學到了很多。謝謝你。當鍵和多列鍵不平衡時,您會建議什麼策略?我不明白為什麼我應該首先索引較低的基數列?
列的順序將根據您需要的查詢類型有所幫助。
如果您執行這樣的查詢
SELECT ... FROM tbl ORDER BY state,source;
SELECT ... FROM tbl WHERE state='NY' ORDER BY source;
SELECT source,COUNT(1) RowCount FROM tbl WHERE state='NY' GROUP BY source;
那麼,索引應該是
(state,source)
如果您執行這樣的查詢
SELECT ... FROM tbl ORDER BY source,state;
SELECT ... FROM tbl WHERE source='blog' ORDER BY state;
SELECT state,COUNT(1) RowCount FROM tbl WHERE source='blog' GROUP BY state;
那麼,索引應該是
(source,state)
。我通常首先排列具有較低基數的列,以使索引掃描對於 ORDER BY 或 GROUP BY 以及其他列中涉及較低基數的查詢更加平滑。
如果您對索引
(state,source)
不滿意,您可以同時製作兩者ALTER TABLE tbl ADD INDEX state_source_index (state,source); ALTER TABLE tbl ADD INDEX source_state_index (source,state);
對查詢執行 EXPLAIN 計劃,看看哪個索引使用得更頻繁。
無論鍵組合是不平衡的,無論您在復合索引中列的順序如何,如果正在使用的列按組編入索引,則評估數據的範圍和順序會更快。
更新 2014-04-16 12:17 EDT
如果每個
(source,state)
組合鍵都需要按 排序date
,則需要將該date
列合併到索引中作為最後一列。ALTER TABLE tbl ADD INDEX state_source_date_index (state,source,date); ALTER TABLE tbl ADD INDEX source_state_date_index (source,state,date);
這樣
ORDER BY
就不必觸發排序。