簡單使用 Mysql Partition 優化查詢

[NeroCube]

CREATE TABLE member (
    id INT NOT NULL AUTO_INCREMENT,
    age INT NOT NULL,
    cont VARCHAR(255) NOT NULL, 
        PRIMARY KEY(id, age)
)
PARTITION BY range(age) (
   -- 由大至小設定
    PARTITION p0 VALUES LESS THAN (10), -- 0~9
    PARTITION p1 VALUES LESS THAN (20), -- 10~19
    PARTITION p2 VALUES LESS THAN (30), -- 20~29
    PARTITION p3 VALUES LESS THAN (40), -- 30~39
    PARTITION p4 VALUES LESS THAN (MAXVALUE) -- over 40
)
;

-- 若表已存在使用 alter (注意執行時間與效能)
ALTER table member 
PARTITION BY RANGE(age) -- partition key 必須為 primary key
    PARTITION p0 VALUES LESS THAN (10), -- 0~9
    PARTITION p1 VALUES LESS THAN (20), -- 10~19
    PARTITION p2 VALUES LESS THAN (30), -- 20~29
    PARTITION p3 VALUES LESS THAN (40), -- 30~39
    PARTITION p4 VALUES LESS THAN (MAXVALUE) -- over 40
);

Reference:

• https://cola.workxplay.net/mysql-partitioning-for-performance-optimization/
• https://docs.microsoft.com/zh-tw/azure/architecture/best-practices/data-partitioning