Mysql

為什麼 MySQL 不使用具有更高基數的索引?

  • April 16, 2014

我有一個source基數為 1122 的索引和一個基數為 22 的第二個索引。 MySQL在查詢時state繼續使用該索引。這是正常的嗎?如果沒有,可能出了什麼問題?state``SELECT C1 FROM tbl WHERE source = 'x' and state = 'y'

基數

即使具有高基數,MySQL 查詢優化器使用的臨界點也不是密鑰分配就是儲存引擎。

早在 2012 年 11 月 13 日,我討論了不平衡鍵如何使查詢優化器選擇不同的索引(有時根本不選擇和索引):索引必須覆蓋所有選定的列才能用於 ORDER BY?

在那篇文章中,我載入了一個表呼叫mf(男性女性)並儲存了一個性別MF. 我插入了 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有 22
  • source有 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就不必觸發排序。

引用自:https://dba.stackexchange.com/questions/63047