MySQL數據表分區策略及優缺點分析

Posted on by

為什麼需要分區？

當面對巨大的數據表的時候，至少有一件事情是確定的，表太大瞭以至於每次查詢的時候我們沒法做全表掃描。而這個時候也沒法使用索引，或者說索引意義不大，更不用說索引的維護代價和空間占用非常高。如果是依賴索引，會導致大量的碎片和低聚集度的數據，這會導致查詢的時候有上千次的隨機 I/O 訪問而導致宕機。這種情況下一般隻會使用1-2個索引，而不會更多。這種情況下，有兩個可行的選項：查詢必須從數據表的指定的部分順序查找或者是期望的部分數據及其索引與服務器的內存匹配。

需要再次重申：在存儲空間過大時，除非索引覆蓋瞭整個查詢，否則二叉樹索引就無法發揮作用。服務端需要查找數據表的一整行數據，並且會在一個大空間跨度裡執行隨機 I/O 操作，這會導致查詢響應時間無法接受。而維護索引（磁盤空間，I/O 操作）的代價同樣很高。

而這是分區能夠解決的問題。這其中的關鍵就是分區是索引的一個初級形式，它的負荷低並且能夠讓我們從臨近的數據中獲取結果。這種情形下，我們可以依次掃描相鄰的數據或者是將臨近的數據加載到內存進行檢索。分區之所以負荷低是因為它並沒有指針指向對應的數據行，也不需要被更新。分區並不精確地將數據按行劃分，也沒有涉及到所謂的數據結構。實際上，分區相當於對數據進行瞭分類。

分區的策略

對於大數據表，有兩種策略進行分區：

  • 不使用索引：創建數據表時不增加索引，而是使用分區定位到所需要的數據行。隻要你使用 WHERE 條件將查詢切分到很小的分區范圍，就已經足夠瞭。這個時候需要通過數學方法計算查詢的響應時間是否能夠接受。當然，這裡的假設是不會將數據放到內存中，而是全部數據都從磁盤讀取。因此數據很快就會被其他查詢覆蓋，使用緩存沒什麼意義。這種情況一般用於大量數據表的基數是常規的。需要註意的是，需要限制分區數在幾百。
  • 使用索引，並且隔離熱區數據：如果除瞭熱區數據外，大部分數據是不使用的，則可以將熱區數據單獨的分區，這個分區算上索引都能夠加載到內存中。這個時候可以通過索引來優化性能，就像操作普通的數據表一樣。

分區隱患

兩種分區策略是基於兩個關鍵假設：在查詢的時候可以通過過濾分區縮小查找范圍，且分區自身的代價不高。然而，這兩個假設未必總是有效，下面是可能遇到的問題：

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

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

  • 所有分區必須使用相同的存儲引擎。
  • 分區函數能夠選用的函數或表達式有一定的限制。
  • 有些存儲引擎並不支持分區。
  • 對於 MYISAM 數據表，無法使用 LOAD INDEX INTO CACHE。
  • 對於 MYISAM 數據表，分區表需要更多的打開文件描述符，這意味著單個數據表的緩存入口可能對應多個文件描述符。因此基本配置限制瞭數據表的緩存以避免超出服務器操作系統的預處理量，而分區表可能導致實際超出這個限制。

當然，隨著 MySQL 版本的更新迭代，對分區的支持也越來越好，並且很多分區的問題都得到瞭修復。

以上就是MySQL數據表分區策略及優缺點分析的詳細內容，更多關於MySQL數據表分區策略及優缺點的資料請關註WalkonNet其它相關文章！

推薦閱讀：