MySQL分割槽表的分割槽原理和優缺點

1.分割槽表的原理

分割槽表是由多個相關的底層表實現，這些底層表也是由控制程式碼物件表示，所以我們也可以直接訪問各個分割槽，儲存引擎管理分割槽的各個底層表和管理普通表一樣（所有的底層表都必須使用相同的儲存引擎），分割槽表的索引只是在各個底層表上各自加上一個相同的索引，從儲存引擎的角度來看，底層表和一個普通表沒有任何不同，儲存引擎也無須知道這是一個普通表還是一個分割槽表的一部分。

在分割槽表上的操作按照下面的操作邏輯進行：


select查詢：


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


insert操作：


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


delete操作：


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


update操作：


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


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



2.在下面的場景中，分割槽可以起到非常大的作用：


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


B：分割槽表的資料更容易維護，如：想批次刪除大量資料可以使用清除整個分割槽的方式。另外，還可以對一個獨立分割槽進行最佳化、檢查、修復等操作


C：分割槽表的資料可以分佈在不同的物理裝置上，從而高效地利用多個硬體裝置


D：可以使用分割槽表來避免某些特殊的瓶頸，如：innodb的單個索引的互斥訪問，ext3檔案系統的inode鎖競爭等


E：如果需要，還可以備份和恢復獨立的分割槽，這在非常大的資料集的場景下效果非常好


F：最佳化查詢，在where字句中包含分割槽列時，可以只使用必要的分割槽來提高查詢效率，同時在涉及sum()和count()這類聚合函式的查詢時，可以在每個分割槽上面並行處理，最終只需要彙總所有分割槽得到的結果。




3.分割槽本身也有一些限制：


A：一個表最多隻能有1024個分割槽（mysql5.6之後支援8192個分割槽）


B：在mysql5.1中分割槽表示式必須是整數，或者是返回整數的表示式，在5.5之後，某些場景可以直接使用字串列和日期型別列來進行分割槽（使用varchar字串型別列時，一般還是字串的日期作為分割槽）。


C：如果分割槽欄位中有主鍵或者唯一索引列，那麼所有主鍵列和唯一索引列都必須包含進來，如果表中有主鍵或唯一索引，那麼分割槽鍵必須是主鍵或唯一索引


D：分割槽表中無法使用外來鍵約束


E：mysql資料庫支援的分割槽型別為水平分割槽，並不支援垂直分割槽，因此，mysql資料庫的分割槽中索引是區域性分割槽索引，一個分割槽中既存放了資料又存放了索引，而全域性分割槽是指的資料庫放在各個分割槽中，但是所有的資料的索引放在另外一個物件中




F：目前mysql不支援空間型別和臨時表型別進行分割槽。不支援全文索引




4.子分割槽的建立需要注意以下幾個問題：


A：每個子分割槽的數量必須相同


B：只要在一個分割槽表的任何分割槽上使用subpartition來明確定義任何子分割槽，就必須在所有分割槽上定義子分割槽，不能漏掉一些分割槽不進行子分割槽。


C：每個subpartition子句必須包括子分割槽的一個名字


D：子分割槽的名字必須是唯一的，不能在一張表中出現重名的子分割槽


E：mysql資料庫的分割槽總是把null當作比任何非null更小的值，這和資料庫中處理null值的order by操作是一樣的，升序排序時null總是在最前面，因此對於不同的分割槽型別，mysql資料庫對於null的處理也各不相同。對於range分割槽，如果向分割槽列插入了null，則mysql資料庫會將該值放入最左邊的分割槽，注意，如果刪除分割槽，分割槽下的所有內容都從磁碟中刪掉了，null所在分割槽被刪除，null值也就跟著被刪除了。在list分割槽下要使用null，則必須顯式地定義在分割槽的雜湊值中，否則插入null時會報錯。hash和key分割槽對於null的處理方式和range,list分割槽不一樣，任何分割槽函式都會將null返回為0.