MySQL分區表的分區原理及優(yōu)缺點(diǎn)

這篇文章主要介紹“分區表的分區原理及優(yōu)缺點(diǎn)”，在日常操作中，相信很多人在MySQL分區表的分區原理及優(yōu)缺點(diǎn)問(wèn)題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對大家解答”MySQL分區表的分區原理及優(yōu)缺點(diǎn)”的疑惑有所幫助！接下來(lái)，請跟著(zhù)小編一起來(lái)學(xué)習吧！

1.分區表的原理

分區表是由多個(gè)相關(guān)的底層表實(shí)現，這些底層表也是由句柄對象表示，所以我們也可以直接訪(fǎng)問(wèn)各個(gè)分區，存儲引擎管理分區的各個(gè)底層表和管理普通表一樣（所有的底層表都必須使用相同的存儲引擎），分區表的索引只是在各個(gè)底層表上各自加上一個(gè)相同的索引，從存儲引擎的角度來(lái)看，底層表和一個(gè)普通表沒(méi)有任何不同，存儲引擎也無(wú)須知道這是一個(gè)普通表還是一個(gè)分區表的一部分。

在分區表上的操作按照下面的操作邏輯進(jìn)行：


select查詢(xún)：


當查詢(xún)一個(gè)分區表的時(shí)候，分區層先打開(kāi)并鎖住所有的底層表，優(yōu)化器判斷是否可以過(guò)濾部分分區，然后再調用對應的存儲引擎接口訪(fǎng)問(wèn)各個(gè)分區的數據


insert操作：


當寫(xiě)入一條記錄時(shí)，分區層打開(kāi)并鎖住所有的底層表，然后確定哪個(gè)分區接受這條記錄，再將記錄寫(xiě)入對應的底層表


delete操作：


當刪除一條記錄時(shí)，分區層先打開(kāi)并鎖住所有的底層表，然后確定數據對應的分區，最后對相應底層表進(jìn)行刪除操作


update操作：


當更新一條數據時(shí)，分區層先打開(kāi)并鎖住所有的底層表，mysql先確定需要更新的記錄在哪個(gè)分區，然后取出數據并更新，再判斷更新后的數據應該放在哪個(gè)分區，然后對底層表進(jìn)行寫(xiě)入操作，并對原數據所在的底層表進(jìn)行刪除操作


雖然每個(gè)操作都會(huì )打開(kāi)并鎖住所有的底層表，但這并不是說(shuō)分區表在處理過(guò)程中是鎖住全表的，如果存儲引擎能夠自己實(shí)現行級鎖，如：innodb，則會(huì )在分區層釋放對應的表鎖，這個(gè)加鎖和解鎖過(guò)程與普通Innodb上的查詢(xún)類(lèi)似。


2.在下面的場(chǎng)景中，分區可以起到非常大的作用：


A：表非常大以至于無(wú)法全部都放在內存中，或者只在表的最后部分有熱點(diǎn)數據，其他都是歷史數據


B：分區表的數據更容易維護，如：想批量刪除大量數據可以使用清除整個(gè)分區的方式。另外，還可以對一個(gè)獨立分區進(jìn)行優(yōu)化、檢查、修復等操作


C：分區表的數據可以分布在不同的物理設備上，從而高效地利用多個(gè)硬件設備


D：可以使用分區表來(lái)避免某些特殊的瓶頸，如：innodb的單個(gè)索引的互斥訪(fǎng)問(wèn)，ext3文件系統的inode鎖競爭等


E：如果需要，還可以備份和恢復獨立的分區，這在非常大的數據集的場(chǎng)景下效果非常好


F：優(yōu)化查詢(xún)，在where字句中包含分區列時(shí)，可以只使用必要的分區來(lái)提高查詢(xún)效率，同時(shí)在涉及sum()和count()這類(lèi)聚合函數的查詢(xún)時(shí)，可以在每個(gè)分區上面并行處理，最終只需要匯總所有分區得到的結果。


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


A：一個(gè)表最多只能有1024個(gè)分區（mysql5.6之后支持8192個(gè)分區）


B：在mysql5.1中分區表達式必須是整數，或者是返回整數的表達式，在5.5之后，某些場(chǎng)景可以直接使用字符串列和日期類(lèi)型列來(lái)進(jìn)行分區（使用varchar字符串類(lèi)型列時(shí)，一般還是字符串的日期作為分區）。


C：如果分區字段中有主鍵或者唯一索引列，那么所有主鍵列和唯一索引列都必須包含進(jìn)來(lái)，如果表中有主鍵或唯一索引，那么分區鍵必須是主鍵或唯一索引


D：分區表中無(wú)法使用外鍵約束

E：mysql數據庫支持的分區類(lèi)型為水平分區，并不支持垂直分區，因此，mysql數據庫的分區中索引是局部分區索引，一個(gè)分區中既存放了數據又存放了索引，而全局分區是指的數據庫放在各個(gè)分區中，但是所有的數據的索引放在另外一個(gè)對象中


F：目前mysql不支持空間類(lèi)型和臨時(shí)表類(lèi)型進(jìn)行分區。不支持全文索引


4.子分區的建立需要注意以下幾個(gè)問(wèn)題：


A：每個(gè)子分區的數量必須相同


B：只要在一個(gè)分區表的任何分區上使用subpartition來(lái)明確定義任何子分區，就必須在所有分區上定義子分區，不能漏掉一些分區不進(jìn)行子分區。


C：每個(gè)subpartition子句必須包括子分區的一個(gè)名字


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


E：mysql數據庫的分區總是把null當作比任何非null更小的值，這和數據庫中處理null值的order by操作是一樣的，升序排序時(shí)null總是在最前面，因此對于不同的分區類(lèi)型，mysql數據庫對于null的處理也各不相同。對于range分區，如果向分區列插入了null，則mysql數據庫會(huì )將該值放入最左邊的分區，注意，如果刪除分區，分區下的所有內容都從磁盤(pán)中刪掉了，null所在分區被刪除，null值也就跟著(zhù)被刪除了。在list分區下要使用null，則必須顯式地定義在分區的散列值中，否則插入null時(shí)會(huì )報錯。hash和key分區對于null的處理方式和range,list分區不一樣，任何分區函數都會(huì )將null返回為0.