7.1分區(qū)表

????????對用戶來說， 分區(qū)表是一個獨(dú)立的邏輯表， 但是底層由多個物理子表組成。 實(shí)現(xiàn)分區(qū)的代碼實(shí)際上是對一組底層表的句柄對象(Handler Object)的封裝。對分區(qū)表的請求， 都會通過句柄對象轉(zhuǎn)化成對存儲引擎的接口調(diào)用。 所以分區(qū)對于SQL層來說是一個完全封裝底層實(shí)現(xiàn)的黑盒子， 對應(yīng)用是透明的， 但是從底層的文件系統(tǒng)來看就很容易發(fā)現(xiàn)， 每一 個分區(qū)表都有一個使用＃分隔命名的表文件。

? ??????MySQL實(shí)現(xiàn)分區(qū)表的方式——對底層表的封裝——意味著索引也是按照分區(qū)的子表定義的， 而沒有全局索引。 這和Oracle不同，在Oracle中可以更加靈活地定義索引和表是否進(jìn)行分區(qū)。

? ? ? ? ? MySQL在創(chuàng)建表時使用PARTITION BY子句定義每個分區(qū)存放 的數(shù)據(jù)。 在執(zhí)行查詢的時候， 優(yōu)化器會根據(jù)分區(qū)定義過濾那些沒有我們需要數(shù)據(jù)的分區(qū)， 這樣查詢就無須掃描所有分區(qū)——只需要查找包含需要數(shù)據(jù)的分區(qū)就可以了。

? ??????分區(qū)的一個主要目的是將數(shù)據(jù)按照一個較粗的粒度分在不同的表中。這樣做可以將相關(guān)的數(shù)據(jù)存放在一起， 另外， 如果想一次批量刪除整個分區(qū)的數(shù)據(jù)也會變得很方便。

????????在下面的場景中， 分區(qū)可以起到非常大的作用：

? 表非常大以至于無法全部都放在內(nèi)存中， 或者只在表的最后部分有熱點(diǎn)數(shù)據(jù)， 其他均是歷史數(shù)據(jù)。

? 分區(qū)表的數(shù)據(jù)更容易維護(hù)。例如，想批最刪除大量數(shù)據(jù)可以使用清除整個分區(qū)的方式。 另外， 還可以對一個獨(dú)立分區(qū)進(jìn)行優(yōu)化、 檢查、 修復(fù)等操作。

? 分區(qū)表的數(shù)據(jù)可以分布在不同的物理設(shè)備上， 從而高效地利用多個硬件設(shè)備。

? 可以使用分區(qū)表來避免某些特殊的瓶頸， 例如InnoDB的單個索引的互斥訪問 、 ext3文件系統(tǒng)的inode鎖競爭等。

? 如果需要，還可以備份和恢復(fù)獨(dú)立的分區(qū)，這在非常大的數(shù)據(jù)集的場景下效果非常好。

分區(qū)表本身也有一些限制， 下面是其中比較重要的幾點(diǎn)：

? 一個表最多只能有1024個分區(qū)。

? 在MySQL5.1中，分區(qū)表達(dá)式必須是整數(shù)，或者是返回整數(shù)的表達(dá)式。在MySQL5.5

中， 某些場景中可以直接使用列來進(jìn)行分區(qū)。

? 如果分區(qū)字段中有主鍵或者唯一索引的列， 那么所有主鍵列和唯一索引列都必須包

含進(jìn)來。

? 分區(qū)表中無法使用外鍵約束

7.1.1分區(qū)表的原理

????????如前所述， 分區(qū)表由多個相關(guān)的底層表實(shí)現(xiàn)， 這些底層表也是由句柄對象(Handlerobject)表示， 所以我們也可以直接訪問各個分區(qū)。存儲引擎管理分區(qū)的各個底層表和管理普通表一樣（所有的底層表都必須使用相同的存儲引擎）， 分區(qū)表的索引只是在各個底層表上各自加上一個完全相同的索引。從存儲引擎的角度來看， 底層表和一個普通表沒有任何不同， 存儲引擎也無須知道這是一個普通表還是一個分區(qū)表的一部分。分區(qū)表上的操作按照下面的操作邏輯進(jìn)行：

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

SELECT查詢

當(dāng)查詢一個分區(qū)表的時候， 分區(qū)層先打開井鎖住所有的底層表， 優(yōu)化器先判斷是否可以過濾部分分區(qū)， 然后再調(diào)用對應(yīng)的存儲引擎接口訪問各個分區(qū)的數(shù)據(jù)。

INSERT操作

當(dāng)寫入一條記錄時，分區(qū)層先打開并鎖住所有的底層表，然后確定哪個分區(qū)接收這 條記錄，再將記錄寫入 對應(yīng)底層表。

DELETE操作

當(dāng)刪除一條記錄時，分區(qū)層先打開井鎖住所有的底層表，然后確定數(shù)據(jù) 對應(yīng)的分區(qū)，最后對相應(yīng)底層表進(jìn)行刪除操作。

UPDATE操作

當(dāng)更新一條記錄時，分區(qū)層先打開并鎖住所有的底層表，MySQL先確定需要 更新 的記錄在哪個分區(qū)，然后取出數(shù)據(jù)并更新，再判斷更新后的數(shù)據(jù)應(yīng)該放在哪個分區(qū)， 最后對底層表進(jìn)行寫入操作，并對原數(shù)據(jù)所在的底層表進(jìn)行刪除操作。

雖然每個操作都會 “先打開并鎖住所有的底層表”，但這并不是說分區(qū)表在處理過程中 是鎖住全表的。 如果存儲引擎能夠自己實(shí)現(xiàn)行級鎖，例如InnoDB, 則會在分區(qū)層釋放對應(yīng)表鎖。 這個加鎖和解鎖過程與普通InnoDB上的查詢類似。

7.1.2分區(qū)表的類型

????????MySQL支持多種分區(qū)表。 我們看到最多的是根據(jù)范圍進(jìn)行分區(qū)，每個分區(qū)存儲落在某個范圍的記錄，分區(qū)表達(dá)式可以是列，也可以是包含 列的表達(dá)式。

7.1.3如何使用分區(qū)表

????????假設(shè)我們希望從一個非常大的表中查詢出一段時間的記錄，而這個表中包含了很多年的歷史數(shù)據(jù)，數(shù)據(jù)是按照時間排序的，例如，希望查詢最近幾個月的數(shù)據(jù)，這大約有10億條記錄。可能過些年本書會過時，不過我們還是假設(shè)使用的是2012年的硬件設(shè)備，而原表中有10TB的數(shù)據(jù)，這個數(shù)據(jù)量遠(yuǎn)大千內(nèi)存，井且使用的是傳統(tǒng)硬盤，不是閃存（多數(shù)SSD也沒有這么大的空間）。你打算如何查詢這個表？如何才能更高效？

????????首先很肯定：因?yàn)閿?shù)據(jù)量巨大，肯定不能在每次查詢的時候都掃描全表?？紤]到索引在 空間和維護(hù)上的消耗，也不希望使用索引。即使真的使用索引，你會發(fā)現(xiàn)數(shù)據(jù)并不是按照想要的方式聚集的，而且會有大量的碎片產(chǎn)生，最終會導(dǎo)致一個查詢產(chǎn)生成千上萬的隨機(jī)I/O,應(yīng)用程序也隨之僵死。情況好一點(diǎn)的時候，也許可以通過一兩個索引解決一些問題。不過多數(shù)情況下，索引不會有任何作用。這時候只有兩條路可選：讓所有的查詢都只在數(shù)據(jù)表上做順序掃描， 或者將數(shù)據(jù)表和索引全部都緩存在內(nèi)存里。

????????這里需要再陳述一遍：在數(shù)據(jù)量超大的時候， B-Tree索引就無法起作用了。 除非是索 引覆蓋查詢， 否則數(shù)據(jù)庫服務(wù)器需要根據(jù)索引掃描的結(jié)果回表， 查詢所有符合條件的記錄， 如果數(shù)據(jù)最巨大， 這將產(chǎn)生大量隨機(jī)I/O????, 隨之， 數(shù)據(jù)庫的響應(yīng)時間將大到不可接受的程度。 另外， 索引維護(hù)（磁盤空間、I/O操作）的代價也非常高。 有些系統(tǒng)， 如Info bright, 意識到這一點(diǎn)， 千是就完全放棄使用B-Tree索引， 而選擇了一些更粗粒度的但消耗更少的方式檢索數(shù)據(jù)， 例如在大量數(shù)據(jù)上只索引對應(yīng)的一小塊元數(shù)據(jù)。

? ??????這正是分區(qū)要做的事情。 理解分區(qū)時還可以將其當(dāng)作索引的最初形態(tài)， 以代價非常小的 方式定位到需要的數(shù)據(jù)在哪一片 “區(qū)域”。在這片 “區(qū)域” 中， 你可以做順序掃描， 可以建索引， 還可以將數(shù)據(jù)都緩存到內(nèi)存， 等等。 因?yàn)榉謪^(qū)無須額外的數(shù)據(jù)結(jié)構(gòu)記錄每個分 區(qū)有哪些數(shù)據(jù)一分區(qū)不需要精確定位每條數(shù)據(jù)的位置， 也就無須額外的數(shù)據(jù)結(jié)構(gòu)——所以其代價非常低。 只需要一個簡單的表達(dá)式就可以表達(dá)每個分區(qū)存放的是什么數(shù)據(jù)。

為了保證大數(shù)據(jù)量的可擴(kuò)展性， 一般有下面兩個策略：

全量掃描數(shù)據(jù)， 不要任何索引。

? ??????可以使用簡單的分區(qū)方式存放表， 不要任何索引， 根據(jù)分區(qū)的規(guī)則大致定位需要的數(shù)據(jù)位置。 只要能夠使用 WHERE條件， 將需要的數(shù)據(jù)限制在少數(shù)分區(qū)中， 則效率是很高的。 當(dāng)然， 也需要做一些簡單的運(yùn)算保證查詢的響應(yīng)時間能夠滿足需求。 使用該策略假設(shè)不用將數(shù)據(jù)完全放入到內(nèi)存中， 同時還假設(shè)需要的數(shù)據(jù)全都在磁盤上， 因?yàn)閮?nèi)存相對很小， 數(shù)據(jù)很快會被擠出內(nèi)存， 所以緩存起不了任何作用。 這個策略適用于以正常的方式訪問大量數(shù)據(jù)的時候。 警告：后面我們會詳細(xì)解釋， 必須將查 詢需要掃描的分區(qū)個數(shù)限制在一個很小的數(shù)量。

索引數(shù)據(jù)， 并分離熱點(diǎn)。

????????如果數(shù)據(jù)有明顯的 “熱點(diǎn)” ，而且除了這部分?jǐn)?shù)據(jù)， 其他數(shù)據(jù)很少被訪問到， 那么可以將這部分熱點(diǎn)數(shù)據(jù)單獨(dú)放在一個分區(qū)中， 讓這個分區(qū)的數(shù)據(jù)能夠有機(jī)會都緩存在 內(nèi)存中。 這樣查詢就可以只訪問一個很小的分區(qū)表， 能夠使用索引， 也能夠有效地使用緩存。

7.1.4什么情況下會出問題

????????上面我們介紹的兩個分區(qū)策略都基于兩個非常重要的假設(shè)：查詢都能夠過濾(prunning)掉很多額外的分區(qū)、 分區(qū)本身井不會帶來很多額外的代價。 而事實(shí)證明， 這兩個假設(shè)在某些場景下會有問題。 下面介紹一些可能會遇到的問題。

NULL值會使分區(qū)過濾無效

????????關(guān)于分區(qū)表一個容易讓人誤解的地方就是分區(qū)的表達(dá)式的值可以是NULL:第一個 分區(qū)是一個特殊分區(qū)。假設(shè)按照PARTITIONBY RANGE YEAR(order_date)分區(qū)，那么所有order_date為NULL或者是一個非法值的時候，記錄都會被存放到第一個 分區(qū)。現(xiàn)在假設(shè)有下面的查詢：WHERE order_date BElWEEN'2012-01-01'AND '2012-01-31'。實(shí)際上，MySQL會檢查兩個分區(qū)，而不是之前猜想的一個：它會檢查2012年這個分區(qū)，同時它還會檢查這個表的第一個分區(qū)。檢查第一個分區(qū)是因?yàn)閅EAR()函數(shù)在接收非法值的時候可能會返回NULL值，那么這個范圍的值可能會返 回NULL而被存放到第一個分區(qū)了。

????????如果第一個分區(qū)非常大，特別是當(dāng)使用“全量掃描數(shù)據(jù)，不要任何索引”的策略時， 代價會非常大。而且掃描兩個分區(qū)來查找列也不是我們使用分區(qū)表的初衷。為了避免這種情況，可以創(chuàng)建一個“無用”的第一個分區(qū)，例如，上面的例子中可以使用 PARTITION p_nulls VALUES LESS THAN (0)來創(chuàng)建第一個分區(qū)。如果插入表中的數(shù)據(jù)都是有效的，那么第一個分區(qū)就是空的，這樣即使需要檢測第一個分區(qū)，代價也 會非常小。

????????在MySQL5.5中就不需要這個優(yōu)化技巧了，因?yàn)榭梢灾苯邮褂昧斜旧矶皇腔诹械暮瘮?shù)進(jìn)行分區(qū)：PARTITION BY RANGE COLUMNS(order_date)。．所以這個案例最好的解決方法是能夠直接使用MySQL5.5的這個語法。

分區(qū)列和索引列不匹配

? ??????如果定義的索引列和分區(qū)列不匹配， 會導(dǎo)致查詢無法進(jìn)行分區(qū)過濾。 假設(shè)在列a上定義了索引， 而在列b上進(jìn)行分區(qū)。 因?yàn)槊總€分區(qū)都有其獨(dú)立的索引， 所以掃描列b上的索引就需要掃描每一個分區(qū)內(nèi)對應(yīng)的索引。 如果每個分區(qū)內(nèi)對應(yīng)索引的非葉子節(jié)點(diǎn)都在內(nèi)存中， 那么掃描的速度還可以接受， 但如果能跳過某些分區(qū)索引當(dāng)然會更好。要避免這個問題， 應(yīng)該避免建立和分區(qū)列不匹配的索引， 除非查詢中還同時包含了可以過濾分區(qū)的條件。

選擇分區(qū)的成本可能很高

? ??????如前所述分區(qū)有很多類型， 不同類型分區(qū)的實(shí)現(xiàn)方式也不同， 所以它們的性能也各不相同。 尤其是范圍分區(qū)， 對于回答 “這一行屬于哪個分區(qū)” 這些符合查詢條件的行在哪些分區(qū)” 這樣的問題的成本可能會非常高， 因?yàn)榉?wù)器需要掃描所有的分 區(qū)定義的列表來找到正確的答案。 類似這樣的線性搜索的效率不高， 所以隨著分區(qū) 數(shù)的增長， 成本會越來越高。

打開并鎖住所有底層表的成本可能很高? ??????

????????當(dāng)查詢訪問分區(qū)表的時候，MySQL需要打開并鎖住所有的底層表， 這是分區(qū)表的另一個開銷。 這個操作在分區(qū)過濾之前發(fā)生， 所以無法通過分區(qū)過濾降低此開銷， 并 且該開銷也和分區(qū)類型無關(guān)， 會影響所有的查詢。 這一點(diǎn)對一些本身操作非常快的 查詢， 比如根據(jù)主鍵查找單行， 會帶來明顯的額外開銷。 可以用批量操作的方式來 降低單個操作的此類開銷， 例如使用批批插入或者 LOAD DATA INFILE 一次刪除多行數(shù)據(jù)， 等等。 當(dāng)然同時還是需要限制分區(qū)的個數(shù)。

維護(hù)分區(qū)的成本可能很高

????????某些分區(qū)維護(hù)操作的速度會非?？?， 例如新增或者刪除分區(qū)（當(dāng)刪除一個大分區(qū)可 能會很慢， 不過這是另一回事）。 而有些操作， 例如重組分區(qū)或者類似 ALTER 語句的 操作：這類操作需要復(fù)制數(shù)據(jù)。 重組分區(qū)的原理與 ALTER 類似， 先創(chuàng)建一個臨時的 分區(qū)， 然后將數(shù)據(jù)復(fù)制到其中， 最后再刪除原分區(qū)。

7.1.5查詢優(yōu)化

? ? ? ? 分區(qū)最大的優(yōu)點(diǎn)就是優(yōu)化器可以根據(jù)分區(qū)函數(shù)來過濾一些分區(qū)。所以，對于訪問分區(qū)表來說，很重要的一點(diǎn)是要在WHERE條件中帶入分區(qū)列，有時候即使看似多余的也要帶上，這樣就可以讓優(yōu)化器能夠過濾掉無須訪問的分區(qū)。如果沒有這 些條件，MySQL就需要讓對應(yīng)存儲引擎訪問這個表的所有分區(qū)，如果表非常大的話， 就可能會非常慢。