MySQL之進階特性---分區表

標籤：

對使用者來說，分區表示一個獨立的邏輯表，但是底層由多個物理子表組成。實現分區的代碼實際上是對一組底層表的控制代碼對象的封裝。對分區表的請求，都會通過控制代碼對象轉換成對儲存引擎的介面調用。MYSQL 實現分區表的方式-》 對底層表的封裝 -》意味著索引也是按照分區的子表定義，而沒有全域索引。分區的一個主要目的是 將資料按照一個較粗的粒度分在不同的表中。分區表的索引只是在各個底層表各自加上一個完全相同的索引。之於儲存引擎，分區表的底層表與普通表沒有區別。這裡有使用分區表的一個案例：

CREATE TABLE sales (　　order_date DATETIME NOT NULL,　　-- Other columns omitted) ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date)) (PARTITION p_2010 VALUES LESS THAN (2010),PARTITION p_2011 VALUES LESS THAN (2011),PARTITION p_2012 VALUES LESS THAN (2012),PARTITION p_catchall VALUES LESS THAN MAXVALUE );

PARTITION 分區子句可以使用各種函數。但有一個要求，運算式返回的值要是一個確定的整數，且不能是一個常數。

分區表上的操作按照下面的操作邏輯進行：
　　select查詢： 
　　　　當查詢一個分區表時，分區表先開啟並鎖住所有的底層表，最佳化器先判斷是否可以過濾部分分區，
　　　　然後再調用對應的儲存引擎介面訪問各個分區的資料。
　　insert操作：
　　　　當寫入一條記錄時，分區層先開啟並鎖住所有的底層表，然後確定哪個分區接收這條記錄，再將記錄寫入對應底層表。
　　delete操作：
　　　　當刪除一條記錄時，分區層先開啟並鎖住所有的底層表，然後確定資料對應的分區，最後對相應底層表進行刪除操作
　　update操作
　　　　當更新一條記錄時，分區層先開啟並鎖住所有的底層表，mysql先確定需要更新的記錄在哪個分區，然後取出資料並更新，再判斷更新後的資料應該
　　　　放在哪個分區，最後對底層表進行寫入操作，並對原資料所在的底層表進行刪除操作。

雖然每個操作都會"先開啟並鎖住所有的底層表"，但並不是說分區表在處理過程中是鎖住全表的。如果儲存引擎能夠自己實現行級鎖，則會在分區層釋放對應表所。
這個加鎖和解鎖過程和普通InnoDB上的查詢相似。

　　　　分區表的限制：
　　1.一個表最多1024個分區
　　2.在mysql5.1中，分區運算式必須是整數，或者返回整數的運算式。在mysql5.5中某情境
　　　中可以直接使用列來進行分區。
　　3.如果分區欄位中有主鍵或者唯一索引的列，那麼所有主鍵列和唯一索引列都必須包含起來。
　　4.分區表無法使用外鍵約束。


在資料量巨大的情況，肯定不能在每次查詢的時候都掃描全表。考慮到索引在空間和維護上的消耗，也不希望使用索引。即使真的使用索引，你會探索資料並不是按照想要的方式聚集的，而且會有大量的片段產生，最終會導致一個查詢產產生千上萬的隨機IO，應用程式也隨之僵死。在這裡需要再陳述一遍，在資料量超大的時候，B-Tree索引就無法起作用了。除非是索引覆蓋查詢，否則資料庫伺服器要根據索引掃描的結果回表，查詢所有合格記錄，如果資料量巨大，這將產生大量隨機IO，隨之，資料庫的回應時間將大到不可接受的程度。另外索引維護的代價也非常高。這正是需要用到分區的情境。
為了保證大資料量的可擴充性，一般有下面兩個策略：　　1.全域掃描資料，不要任何索引　　2.索引資料，並分離熱點。
分區可能會遇到的問題：    null值會使分區過濾無效：        null值會被自動過濾到第一個分區。為了便面這種情況，可以建立一個無用的第一個分區。        分區列和索引列不匹配        如果定義的索引列和分區列不匹配，會導致查詢無法進行分區過濾。            選擇分區的成本可能會很高        開啟並鎖住所有底層表的成本可能很高        維護分區的成本可能很高。    分區表注意點：    所有分區都必須使用相同的儲存引擎        分區函數中可以使用的函數和運算式有一定的限制。        某些儲存引擎不支援分區    EXPLAIN PARTITIONS SELECT * FROM sales_by_day where YEAR(day) = 2010\gMYSQL只能在使用分區函數的列本身進行比較時才能過濾分區，而不能根據運算式的值去過濾分區，即使這個運算式就是分區函數也不行。合并表，早期的分區表，（老的，早期的，功能有限的）

 

MySQL之進階特性---分區表