MySQL Partition

MySQL Partition

• 使用 Partition 的優點

1. 可以有效減少單一檔案存放過多資料而造的檔案肥大問題。
2. 一些查詢可以得到極大的最佳化
• 滿足一個給定WHERE 語句的數據可以只保存在一個或多個分區內，這樣在搜尋時就不用搜尋其他剩餘的分區。
3. 涉及到例如SUM() 和 COUNT()這樣聚合函數的查詢，可以很容易地進行並行處理。
• 通過「並行」， 這意味著該查詢可以在每個分區上同時進行，最終結果只需通過總計所有分區得到的結果。
4.  通過跨多個磁盤來分散數據查詢，來獲得更大的查詢吞吐量。

• 分區類型

• RANGE 分區：基於屬於一個給定連續區間的列值，把多行分配給分區。
• KEY 分區：類似於按HASH分區，區別在於KEY分區只支援計算一列或多列，
  且MySQL 伺服器提供其自身的哈希函數。必須有一列或多列包含整數值。
• HASH分區：基於用戶定義的資料表達式的返回值來進行選擇的分區，
  該資料表達式使用將要插入到資料表中的這些行的列值進行計算。
  這個函數可以包含MySQL 中有效的、產生非負整數值的任何資料表達式。
• LIST 分區：類似於按RANGE分區，
  區別在於LIST分區是基於列值匹配一個離散值集合中的某個值來進行選擇。

• 建立方式
• New table
• CREATE TABLE employees (
      id INT NOT NULL,
      fname VARCHAR(30),
      lname VARCHAR(30))
  
  PARTITION BY RANGE (store_id) (
      PARTITION p0 VALUES LESS THAN (6),
      PARTITION p1 VALUES LESS THAN (11),
      PARTITION p2 VALUES LESS THAN (16),
      PARTITION p3 VALUES LESS THAN (21));
• Existing table
• alter table employess partition by RANGE (year(remittance_date)) (PARTITION r0 VALUES LESS THAN (2012), PARTITION r1 VALUES LESS THAN (2013), PARTITION r2 VALUES LESS THAN (2014), PARTITION r3 VALUES LESS THAN (2015), PARTITION r4 VALUES LESS THAN (2016), PARTITION r5 VALUES LESS THAN (2017), PARTITION r6 VALUES LESS THAN (2018), PARTITION r7 VALUES LESS THAN (2019), PARTITION r8 VALUES LESS THAN (2020), PARTITION r9 VALUES LESS THAN MAXVALUE );

• 注意事項
• 若 index 已經做了, 要先將索引清除,partition完成後, 再重做 index。
• Partition 時, 用來做為分區的key 必須為 pk 或是 pk 的一部份。
• 若想做為分區的key不為pk時, 最簡單的做法為把pk先暫時清除掉, 再做 paration, partition 完成後, 再重新加回 pk 。
• 上述之事項，若沒注意，你將會得到很多的 Error Code: 1503...