MySQL數(shù)據(jù)表分區(qū)策略及優(yōu)缺點(diǎn)分析

為什么需要分區(qū)？

當(dāng)面對(duì)巨大的數(shù)據(jù)表的時(shí)候，至少有一件事情是確定的，表太大了以至于每次查詢的時(shí)候我們沒(méi)法做全表掃描。而這個(gè)時(shí)候也沒(méi)法使用索引，或者說(shuō)索引意義不大，更不用說(shuō)索引的維護(hù)代價(jià)和空間占用非常高。如果是依賴索引，會(huì)導(dǎo)致大量的碎片和低聚集度的數(shù)據(jù)，這會(huì)導(dǎo)致查詢的時(shí)候有上千次的隨機(jī) I/O 訪問(wèn)而導(dǎo)致宕機(jī)。這種情況下一般只會(huì)使用1-2個(gè)索引，而不會(huì)更多。這種情況下，有兩個(gè)可行的選項(xiàng)：查詢必須從數(shù)據(jù)表的指定的部分順序查找或者是期望的部分?jǐn)?shù)據(jù)及其索引與服務(wù)器的內(nèi)存匹配。

需要再次重申：在存儲(chǔ)空間過(guò)大時(shí)，除非索引覆蓋了整個(gè)查詢，否則二叉樹(shù)索引就無(wú)法發(fā)揮作用。服務(wù)端需要查找數(shù)據(jù)表的一整行數(shù)據(jù)，并且會(huì)在一個(gè)大空間跨度里執(zhí)行隨機(jī) I/O 操作，這會(huì)導(dǎo)致查詢響應(yīng)時(shí)間無(wú)法接受。而維護(hù)索引（磁盤(pán)空間，I/O 操作）的代價(jià)同樣很高。

而這是分區(qū)能夠解決的問(wèn)題。這其中的關(guān)鍵就是分區(qū)是索引的一個(gè)初級(jí)形式，它的負(fù)荷低并且能夠讓我們從臨近的數(shù)據(jù)中獲取結(jié)果。這種情形下，我們可以依次掃描相鄰的數(shù)據(jù)或者是將臨近的數(shù)據(jù)加載到內(nèi)存進(jìn)行檢索。分區(qū)之所以負(fù)荷低是因?yàn)樗](méi)有指針指向?qū)?yīng)的數(shù)據(jù)行，也不需要被更新。分區(qū)并不精確地將數(shù)據(jù)按行劃分，也沒(méi)有涉及到所謂的數(shù)據(jù)結(jié)構(gòu)。實(shí)際上，分區(qū)相當(dāng)于對(duì)數(shù)據(jù)進(jìn)行了分類。

分區(qū)的策略

對(duì)于大數(shù)據(jù)表，有兩種策略進(jìn)行分區(qū)：

• 不使用索引：創(chuàng)建數(shù)據(jù)表時(shí)不增加索引，而是使用分區(qū)定位到所需要的數(shù)據(jù)行。只要你使用 WHERE 條件將查詢切分到很小的分區(qū)范圍，就已經(jīng)足夠了。這個(gè)時(shí)候需要通過(guò)數(shù)學(xué)方法計(jì)算查詢的響應(yīng)時(shí)間是否能夠接受。當(dāng)然，這里的假設(shè)是不會(huì)將數(shù)據(jù)放到內(nèi)存中，而是全部數(shù)據(jù)都從磁盤(pán)讀取。因此數(shù)據(jù)很快就會(huì)被其他查詢覆蓋，使用緩存沒(méi)什么意義。這種情況一般用于大量數(shù)據(jù)表的基數(shù)是常規(guī)的。需要注意的是，需要限制分區(qū)數(shù)在幾百。
• 使用索引，并且隔離熱區(qū)數(shù)據(jù)：如果除了熱區(qū)數(shù)據(jù)外，大部分?jǐn)?shù)據(jù)是不使用的，則可以將熱區(qū)數(shù)據(jù)單獨(dú)的分區(qū)，這個(gè)分區(qū)算上索引都能夠加載到內(nèi)存中。這個(gè)時(shí)候可以通過(guò)索引來(lái)優(yōu)化性能，就像操作普通的數(shù)據(jù)表一樣。
  

分區(qū)隱患

兩種分區(qū)策略是基于兩個(gè)關(guān)鍵假設(shè)：在查詢的時(shí)候可以通過(guò)過(guò)濾分區(qū)縮小查找范圍，且分區(qū)自身的代價(jià)不高。然而，這兩個(gè)假設(shè)未必總是有效，下面是可能遇到的問(wèn)題：

• NULL 空值可能導(dǎo)致分區(qū)過(guò)濾失效：當(dāng)分區(qū)函數(shù)可能是 NULL 時(shí)，分區(qū)工作的結(jié)果就會(huì)很奇特。它會(huì)假設(shè)第一個(gè)分區(qū)是特殊的。假設(shè)使用 PARTITION BY RANGE YEAR(order_date)這樣的分區(qū)方法，如果 order_date 這個(gè)列是 NULL 或者無(wú)效的日期都會(huì)存儲(chǔ)在第一個(gè)分區(qū)。假設(shè)寫(xiě)了一個(gè)查詢使用了這樣的查詢條件 :WHERE order_date BETWEEN '2021-01-01' AND '2021-01-31'。MySQL 實(shí)際上會(huì)檢查2個(gè)分區(qū)，一個(gè)是 YEAR 這個(gè)函數(shù) 在接收到無(wú)效輸入時(shí)可能會(huì)返回 NULL，另一個(gè)是符合條件的值可能是 NULL（存儲(chǔ)在第一個(gè)分區(qū)中）。這種情況對(duì)其他函數(shù)也可能，例如 TO_DAYS。如果第一個(gè)分區(qū)很大的話，就會(huì)產(chǎn)生問(wèn)題，尤其是使用第一種不使用索引策略時(shí)。從兩個(gè)分區(qū)查找數(shù)據(jù)而不是一個(gè)分區(qū)的效果是完全意外的。為了避免這種情況，應(yīng)該創(chuàng)造“假的”第一分區(qū)，例如 PARTITION p_nulls VALUES LESS THAN (0)。如果沒(méi)有無(wú)效數(shù)據(jù)存入數(shù)據(jù)表的話，這個(gè)第一分區(qū)將是空的，即便它也會(huì)被掃描，但是因?yàn)槭强盏幕蛘邤?shù)據(jù)量很少，對(duì)性能影響不大。這種情況在 MySQL 5.5以后，如果直接使用列進(jìn)行分區(qū)的話就不需要處理，但是如果是使用函數(shù)的話就要這樣做。
• 索引與分區(qū)不匹配：假設(shè)定義了一個(gè)索引與分區(qū)條件不匹配，查詢就可能無(wú)法對(duì)分區(qū)進(jìn)行過(guò)濾。假設(shè)定義了 字段 a 的索引卻使用 字段 b 進(jìn)行分區(qū)。由于每個(gè)分區(qū)都會(huì)有自己的索引，針對(duì)這個(gè)索引的查詢會(huì)遍歷所有分區(qū)的索引樹(shù)。如果索引樹(shù)的非葉子節(jié)點(diǎn)都常駐內(nèi)存查詢起來(lái)還比較快，但是也沒(méi)法避免全部索引的掃描。為了避免這種情況，應(yīng)當(dāng)盡量避免使用非分區(qū)的索引列，除非WHERE 條件本身能夠指定分區(qū)?？雌饋?lái)這樣很容易避免，實(shí)際上卻令人吃驚。例如，假設(shè)一個(gè)分區(qū)表用在第二個(gè)表查詢聯(lián)合查詢后，而聯(lián)合查詢使用的索引并不是分區(qū)的索引。則聯(lián)合查詢的每一行都會(huì)訪問(wèn)和掃碼第二張表的分區(qū)。
• 決定使用哪個(gè)分區(qū)代價(jià)可能很高：分區(qū)實(shí)現(xiàn)的方式各有差異，因此實(shí)際的性能并不總是一致。特別是當(dāng)遇到“這個(gè)數(shù)據(jù)行屬于哪個(gè)分區(qū)”或者“如何才能查找到與查詢條件匹配的數(shù)據(jù)行”這樣的問(wèn)題時(shí)。在眾多分區(qū)的情況下來(lái)回答這樣的問(wèn)題很費(fèi)勁。線性搜索并不總是那么有效，結(jié)果是隨著分區(qū)數(shù)的增長(zhǎng)代價(jià)也在上升。最為糟糕的形式是逐行插入。每次插入一行數(shù)據(jù)到分區(qū)的數(shù)據(jù)表，服務(wù)器都需要掃描一次使用哪個(gè)分區(qū)存放新的數(shù)據(jù)行。可以通過(guò)限制分區(qū)的數(shù)量來(lái)減輕這個(gè)問(wèn)題，事實(shí)上，一般不建議超過(guò)100個(gè)分區(qū)。當(dāng)然，對(duì)于其他分區(qū)類型，如鍵值和哈希分區(qū)則不會(huì)有這樣的限制。
• 打開(kāi)和鎖定分區(qū)代價(jià)也可能很高：分區(qū)表帶來(lái)的一個(gè)負(fù)面效應(yīng)是查詢時(shí)需要對(duì)每個(gè)分區(qū)進(jìn)行打開(kāi)和鎖定。而這個(gè)過(guò)程是在過(guò)濾分區(qū)前進(jìn)行的。這個(gè)代價(jià)與分區(qū)類型無(wú)關(guān)，且會(huì)影響所有的操作語(yǔ)句。這種影響對(duì)于短數(shù)據(jù)量的查詢尤其明顯，例如只查詢一行數(shù)據(jù)時(shí)。這種缺陷可以通過(guò)批量操作替代單次來(lái)降低，例如一次插入多行，或 LOAD DATA INFILE，一次按范圍刪除數(shù)據(jù)等等。當(dāng)然，限制分區(qū)的數(shù)量也是有效的。
• 維護(hù)操作代價(jià)可能很高：有些分區(qū)的維護(hù)是很快的，例如創(chuàng)建或者刪除分區(qū)。而其他操作，例如調(diào)整分區(qū)，就有點(diǎn)像 ALTER 對(duì)表的操作那樣了：需要循環(huán)復(fù)制數(shù)據(jù)行。例如，調(diào)整分區(qū)會(huì)創(chuàng)建一個(gè)臨時(shí)分區(qū)，然后將數(shù)據(jù)移入到新的分區(qū)，再刪除舊的分區(qū)。
  

如上所述，分區(qū)并不是完美解決方案，目前版本的 MySQL還有一些其他的約束：

• 所有分區(qū)必須使用相同的存儲(chǔ)引擎。
• 分區(qū)函數(shù)能夠選用的函數(shù)或表達(dá)式有一定的限制。
• 有些存儲(chǔ)引擎并不支持分區(qū)。
• 對(duì)于 MYISAM 數(shù)據(jù)表，無(wú)法使用 LOAD INDEX INTO CACHE。
• 對(duì)于 MYISAM 數(shù)據(jù)表，分區(qū)表需要更多的打開(kāi)文件描述符，這意味著單個(gè)數(shù)據(jù)表的緩存入口可能對(duì)應(yīng)多個(gè)文件描述符。因此基本配置限制了數(shù)據(jù)表的緩存以避免超出服務(wù)器操作系統(tǒng)的預(yù)處理量，而分區(qū)表可能導(dǎo)致實(shí)際超出這個(gè)限制。

當(dāng)然，隨著 MySQL 版本的更新迭代，對(duì)分區(qū)的支持也越來(lái)越好，并且很多分區(qū)的問(wèn)題都得到了修復(fù)。

以上就是MySQL數(shù)據(jù)表分區(qū)策略及優(yōu)缺點(diǎn)分析的詳細(xì)內(nèi)容，更多關(guān)于MySQL數(shù)據(jù)表分區(qū)策略及優(yōu)缺點(diǎn)的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！