Mysql分區表使用的一些限制和需要注意的地方

標籤：效能   條目   http   解決   單行   作業系統   文法   刪除   myisam   

詳見：http://blog.yemou.net/article/query/info/tytfjhfascvhzxcyt347

 

mysql分區策略都基於兩個非常重要的假設：查詢都能夠過濾（prunning)掉很多額外的分區、分區本身並不會帶來很多額外的代價。而事實證明，這兩個假設在某些情境下會有問題。下面介紹一些可能會遇到的問題。

 

NULL位會使分區過濾無效

  關於分區表一個容易讓人誤解的地方就是分區的運算式的值可以是NULL：第一個分區是一個特殊分區。假設按照PARTITION BY     RANGE YEAR(order_date）分區，那麼所有order_date為NULL或者是一個非法值的時候，記錄都會被存放到第一個分區。現在假設有   下面的查詢：WHERE order_date BETWEEN  ‘2012-01-01‘AND’2012-01-31’。實際上，MySQL會檢查兩個分區，而不是之前猜想的   一個：它會檢查2012年這個分區，同時它還會檢查這個表的第一個分區。檢查第一個分區是因為YEAR（）函數在接收非法值的時候   可能會返回NULL值，那麼這個範圍的值可能會返回NULL而被存放到第一個分區了。這一點對於其他很多函數，例如TO_DAYS（）也一   樣。

  如果第一個分區非常大，特別是當使用“全量掃描資料，不要任何索引”的策略時，代價會非常大。而且掃描兩個分區來尋找列     也不是我們使用分區表的初衷。為了避免這種情況，可以建立一個“無用”的第一個分區，例如，上面的例子中可以使用PARTITION   p_nulls VALUES LESS THAN(0）來建立第一個分區。如果插入表中的資料都是有效，那麼第一個分區就是空的，這樣即使需要檢   測第一個分區，代價也會非常小。

  在MySQL5.5中就不需要這個最佳化技巧了，因為可以直接使用列本身而不是基於列的函數進行分區：PARTITION BY RANGE        COLUMNS(order_date).所以這個案例最好的解決方越是能夠直接使用MySQL5.5的這個文法。

 

分區列和索引列不匹配

  如果定義的索引列和分區列不匹配，會導致查詢無法進行分區過濾。假設在列a上定義了索引，而在列b上進行分區。因為每個分區   都有其獨立的索引，所以掃描列b上的索引就需要掃描每一個分區內對應的索引。如果每個分區內對應索引的非葉子節點都在記憶體   中，那麼掃描的速度還可以接受，但如果能跳過某些分區索引當然會更好。要避免這個問題，應該避免建立和分區列不匹配的索     引，除非查詢中還同時包含了可以過濾分區的條件。

  聽起來避免這個問題很簡單，不過有時候也會遇到一些意想不到的問題。例如，在一個關聯查詢中，分區表在關聯順序中是第二個   表，井且關聯使用的索引和分區條件並不匹配。那麼關聯時針對第一個表合格每一行，都需要訪問並搜尋第二個表的所有分   區。

 

選擇分區的成本可能很高

  如前所述分區有很多類型，不同類型分區的實現方式也不同，所以它們的效能也各不相同。尤其是定界分割，對於回答“這一行     屬於哪個分區”、“這些符合查詢條件的行在哪些分區”這樣的問題的成本可能會非常高，因為伺服器需要掃描所有的分區定義的   列表來找到正確的答案。類似這樣的線性搜尋的效率不高，所以隨著分區數的增長，成本會越來越高。

  我們所實際碰到的類似這樣的最糟牒的一次問題是按行寫入大量資料的時候。每寫入一行資料到定界分割的表時，都需要掃描分區   定義列表來找到合適的目標資料分割。可以通過限制分區的數量來緩解此問題，根據實踐經驗，對大多數系統來說，100個左右的分區是   沒有問題的。其他的分區類型，比如鍵分區和雜湊分割，則沒有這樣的問題。

 

開啟並鎖住所有底層表的成本可能很高

  當查詢訪問分區表的時候，MySQL需要開啟井鎖住所有的底層表，這是分區表的另一個開銷。這個操作在分區過濾之前發生，所以無   法通過分區過濾降低此開銷，並且該開銷也和分區類型無關，會影響所有的查詢。這一點對一些本身操作非常快的查詢，比如根據   主鍵尋找單行，會帶來明顯的額外開銷。可以用大量操作的方式來降低單個操作的此類開銷，例如使用批量插入或者           LOAD DATA INFILE、一次刪除多行資料，等等。當然同時還是需要限制分區的個數。

 

維護分區的成本可能很高

  某些分區維護操作的速度會非常快，例如新增或者刪除分區（當刪除一個大分區可能會很慢，不過這是另一回事）。而有些操作，   例如重組分區或者類似ALTER語句的操作：這類操作需要複製資料。重組分區的原理與ALTER類似，先建立一個臨時的分區，然後將   資料複製到其中，最後再刪除原分區。

 

如上所述，分區表不是什麼“銀彈”。下面是目前分區實現中的一些其他限制：如上所述，分區表不是什麼“銀彈”。下面是目前分區實現中的一些其他限制：

 

• 所有分區都必須使用相同的儲存引擎。

• 分區函數中可以使用的函數和運算式也有一些限制。某些儲存引擎不支援分區。

• 對於MyISAM的分區表，不能再使用LOAD INDEXI INTO CACHE操作。

• 對於MyISAM表，使用分區表時需要開啟更多的檔案描述符。雖然看起來是一個表其實背後有很多獨立的分區，每一個分區對於儲存引擎來說都是一個獨立的表。這樣即使分區表只佔用一個表緩衝條目，檔案描述符還是需要多個。因此，即使已經配置了合適的表緩衝，以確保不會超過作業系統的單個進程可?以開啟的檔案描述符的個數，但對於分區表而言，還是會出現超過檔案描述符限制的問題。

 

最後，需要指出的是較老版本的MySQL問題會更多些。所有的軟體都是有bug的。分區表在MySQL5.1中引人，在後面的5.1.40和5.1.50之後修複了很多分區表的b噸。MySQL5.5中，分區表又做了很多改進，這才使得分區表可以逐步考慮用在生產環境了在即將發布的MySQL5.6版本中，分區表做了更多的增強，例如新引火的ALTER TABLE EXCHANGE PARTITION。

 

Mysql分區表使用的一些限制和需要注意的地方