mysql分區表

標籤：外鍵   upd   曆史   自己實現   主鍵   inno   最佳化器   表達   執行   

　　對使用者來說，分區表是一個獨立的邏輯表，但是底層由多個物理子表組成。實現分區的代碼實際上是對一組底層表的控制代碼對象的封裝。

　　mysql在建立表時使用PARTITION BY子句定義每個分區存放的資料。在執行查詢的時候，最佳化器會根據分區定義過濾那些沒有我們需要資料的分區，這樣查詢就無須掃描所有分區——只需要查詢包含需要資料的分區就可以了。

　　分區的一個主要目的是將資料按照一個較粗的粒度分在不同的表中，這樣做可以將相關的資料放在一起，另外，如果想一次大量刪除整個分區的資料也會變得很方便。

 

　　在下面的情境中，分區可以起到非常大的作用：

　　1.表非常大以至於無法全部都放在記憶體中，或者只在表的最後部分有熱點資料，其他均是曆史資料。

　　2.分區表的資料更容易維護。例如想大量刪除大量資料可以使用清除整個分區的方式。另外，還可以對一個獨立分區進行最佳化、檢查、修複等操作。

　　3.分區表的資料可以分布在不同的物理裝置上，從而高效地利用多個硬體裝置。

　　4.可以使用分區表來避免某些特殊的瓶頸，例如InnoDB的單個索引的互斥訪問，ext3檔案系統的inode鎖競爭等。

　　5.如果需要，還可以備份和恢複獨立的分區，這在非常大的資料集的情境下效果非常好。

 

　　分區表本身也有一些限制，下面是其中比較重要的幾點：

　　1.一個表最多隻能有1024個分區。

　　2.在mysql5.1中，分區運算式必須是整數，或者是返回整數的運算式。在mysql5.5中，某些情境中可以直接使用列進行分區。

　　3.如果分區欄位中有主鍵或者唯一索引的列，那麼所有主鍵列和唯一索引列都必須包含進來。

　　4.分區表中無法使用外鍵約束。

 

　　分區表上的操作按照下面的操作邏輯進行：

　　select查詢

　　當查詢一個分區表的時候，分區層先開啟並鎖住所有的底層表，最佳化器先判斷是否可以過濾部分分區，然後再調用對應的儲存引擎介面訪問各個分區的資料。

　　insert操作

　　當寫入一條記錄時，分區層先開啟並鎖住所有的底層表，然後確定哪個分區接收這條記錄，再將記錄寫入對應底層表。

　　delete操作

　　當刪除一條記錄時，分區層先開啟並鎖住所有的底層表，然後確定資料對應的分區，最後對相應底層表進行刪除操作。

　　update操作

　　當更新一條記錄時，分區層先開啟並鎖住所有的底層表，mysql先確定需要更新的記錄在哪個分區，然後取出資料並更新，再判斷更新後的資料在哪個分區，最後對底層進行寫入操作，並對原資料所在的底層表進行刪除操作。

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

mysql分區表