MySQL 分割槽表探索

zyxcba發表於2019-01-28
MySql

背景

如果需要定時清理一張普通大表裡的歷史資料。

可以使用一個或多個帶 where 條件的 delete 語句去刪除（where條件是時間）。 如果表資料量較大，這對資料庫的造成了很大壓力。即使我們把這些舊資料刪除了，但是底層的資料檔案並沒有變小。

為什麼沒有變小？
當刪除資料 時，MYSQL 並不會立即回收表空間。被已刪除資料的佔據的儲存空間，以及索引位會空在那裡，等待新的資料來彌補這個空缺。
強行回收： OPTIMIZE TABLE

面對這類問題，其實最有效的方法就是在使用分割槽表。分割槽表最大的優點就是可以非常高效的進行歷史資料的清理。

關於分割槽表

分割槽表不是在儲存引擎層完成的。這是 MySQL 支援的功能（5.1開始）

原理：
將表索引分解為多個更小、更可管理的部分。

從邏輯上講，只有一個表或者索引，但是物理上這個表或者索引可能由數十個物理分割槽組成。

分割槽表最大的優點就是可以非常高效的進行歷史資料的清理。

每個分割槽都是獨立的物件，可以獨自處理，也可以作為一個更大物件的一部分進行處理（如果分割槽表很大，亦可以將分割槽分配到不同的磁碟上去）。

在執行查詢的時候，優化器會根據分割槽定義過濾哪些沒有我們需要資料的分割槽，這樣查詢就無須全表掃描所有分割槽，只查詢包含需要資料的分割槽即可。

檢查分割槽功能是否啟用

mysql> SHOW PLUGINS \G;

*************************** 43. row ***************************
   Name: partition
 Status: ACTIVE
   Type: STORAGE ENGINE
Library: NULL
License: GPL

分割槽型別

MySQL目前只支援 水平分割槽（水平分割槽就是將不同的行分配到不同的物理檔案中）。

  • 範圍分割槽（RANGE）
    行資料基於一個給定的連續區間的值被放入分割槽。

  • 列表分割槽（LIST）
    和 RANGE 分割槽類似，只不過面向的是離散的值。

  • 雜湊分割槽（HASH）
    根據使用者自定義的表示式返回的值來區分放入那個分割槽。

  • KEY分割槽
    根據 MySQL 資料庫提供的雜湊函式來進行分割槽。

  • COLUMNS 分割槽
    可以對多個列的值進行分割槽。（MySQL 5.5+ 開始支援）。

RANGE 分割槽

這是最常用的一種分割槽型別。最常見的是基於時間欄位（基於分割槽的列最好是整型）來分割槽。
分割槽的列可以允許 null 值，如果分割槽的列值是 null，則會選擇第一個分割槽。

CREATE TABLE range_partition_test (
    id INT,
    pdate INT
)
PARTITION BY RANGE (pdate) (
    PARTITION p1 VALUES LESS THAN ( 201702 ),
    PARTITION p2 VALUES LESS THAN ( 201703 ),
    PARTITION p3 VALUES LESS THAN ( 201704 ),
    PARTITION p4 VALUES LESS THAN (MAXVALUE)
);

MAXVALUE 是一個無窮大的值，所以p4 分割槽即為預設的分割槽。

在執行查詢的時候，帶上分割槽欄位，這樣可以使用分割槽剪裁功能。

mysql> select * from range_partition_test;
+------+--------+
| id   | pdate  |
+------+--------+
|    1 | 201701 |
|    2 | 201702 |
|    3 | 201703 |
|    4 | 201704 |
|    5 | 201705 |
+------+--------+

mysql> explain partitions select * from range_partition_test where pdate between 201702 and 201703;
+----+-------------+----------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table                | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | range_partition_test | p2,p3      | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+----------------------+------------+------+---------------+------+---------+------+------+----------+-------------+

只查詢了p2,p3分割槽。

LIST 分割槽

LIST 分割槽和 RANGE 分割槽類似。
區別在於 LIST 是列舉值列表的集合，RANGE 是連續的區間值的集合。二者在語法方面非常的相似。
建議 LIST 分割槽列是非 null 列，否則插入 null 值如果列舉列表裡面不存在 null 值會插入失敗（和 RANGE 分割槽不一樣）。

CREATE TABLE list_partition_test (
    id INT,
    pdate INT
)
PARTITION BY LIST (pdate) (
    PARTITION p1 VALUES IN (1,3,5,7,9),
    PARTITION p2 VALUES IN (2,4,6,8,0)
);

Hash 分割槽

HASH 分割槽的目的是講資料均勻的分不到預先定義的各個分割槽中。保證各個分割槽的記錄數量大體上都是一致的。

在實際工作中經常遇到像會員表的這種表。並沒有明顯可以分割槽的特徵欄位。但表資料有非常龐大。這時候可以使用 HASH 分割槽。

基於給定的分割槽個數，將資料分配到不同的分割槽，HASH分割槽只能針對整數進行 HASH。

CREATE TABLE hash_partition_test (
    id INT,
    pdate INT
)
PARTITION BY HASH(id)
PARTITIONS 4;
  • 上面的分割槽物件(id)也可以是一個表示式，表示式的結果必須是整數值。
  • HASH 分割槽可以不用指定 PARTITIONS 子句，則預設分割槽數為1。
  • 不允許只寫 PARTITIONS，而不指定分割槽數。
  • HASH 分割槽的底層實現其實是基於 MOD 函式。

KEY 分割槽

KEY 分割槽和 HASH 分割槽相似。不同之處在於：

  • KEY 分割槽允許多列，而 HASH 分割槽只允許一列。
  • 如果在有主鍵或者唯一鍵的情況下，key 中分割槽列可不指定，預設為主鍵或者唯一鍵，如果沒有，則必須顯性指定列。
  • KEY 分割槽物件必須為列，而不能是基於列的表示式。
  • KEY 分割槽和 HASH 分割槽的演算法不一樣，對於 innodb 引擎，採用的是 MD5 值來分割槽。

COLUMNS 分割槽

可以直接使用非整型的資料進行分割槽。分割槽根據型別直接比較而得，不需要轉化為整型。同時，可以對多個列值進行分割槽。

CREATE TABLE listvardou (
    id INT,
    pdate INT
)
PARTITION BY LIST COLUMNS(id,pdate)
(
    PARTITION a VALUES IN ( (1, 201701), (1, 201702), (1, 201703)),
    PARTITION b VALUES IN ( (2, 201702) )
    PARTITION b VALUES IN ( (3, 201703) )
);

總結

  • RANGE 分割槽，LIST 分割槽，HASH 分割槽，KEY 分割槽物件返回的只能是整數值，如果不是整型，則需要使用函式將其轉化為整型。
  • 資料表非常大以至於無法全部都放到記憶體，或者只在表的最後部分有熱點資料，其他均為歷史資料的情況下，可以選用分割槽表。
  • 分割槽表資料更容易維護（可獨立對分割槽進行優化、檢查、修復及批量刪除大資料可以採用drop分割槽的形式等）。
  • 分割槽表的資料可以分佈在不同的物理裝置上，從而高效地利用多個硬體裝置。
  • 可以備份和恢復獨立的分割槽，非常適用於大資料集的場景。
  • 分割槽的主要目的是用於資料庫的高可用性管理。

相關文章