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

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。

特别說明：尊重作者的勞動成果，轉載請注明出處哦~~~

http://blog.yemou.net/article/query/info/tytfjhfascvhzxcyt347