MySQL 數據庫分區功能深度實戰：RANGE/LIST/HASH/KEY/子分區/Columns 全解析

什麼是分區

將一張大表的物理文件（.MYD/.MYI/.frm）按規則切分為多個小塊，查詢時只掃描相關分區，突破單文件大小限制，提升 I/O 與查詢性能。

分區類型全覽

1. RANGE 分區（範圍）

ALTER TABLE yl_hospital_url PARTITION BY RANGE (ID) (
    PARTITION p0 VALUES LESS THAN (100000),
    PARTITION p1 VALUES LESS THAN (200000),
    PARTITION p2 VALUES LESS THAN (300000),
    PARTITION p3 VALUES LESS THAN (400000),
    PARTITION p4 VALUES LESS THAN (500000),
    PARTITION p5 VALUES LESS THAN (600000),
    PARTITION p6 VALUES LESS THAN (700000),
    PARTITION p7 VALUES LESS THAN (MAXVALUE)
);

效果： 查詢速度從秒級降至毫秒級（文中實測截圖）。

2. 檢查分區支持

SHOW VARIABLES LIKE '%part%';
-- have_partitioning = YES 表示支持

3. 完整建表示例

CREATE TABLE user (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50),
    sex INT DEFAULT 0,
    PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (3),
    PARTITION p1 VALUES LESS THAN (6),
    PARTITION p2 VALUES LESS THAN (9),
    PARTITION p3 VALUES LESS THAN (12),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

4. 物理文件驗證

ls | grep user | xargs du -sh
# 4.0K user#P#p0.MYD
# 4.0K user#P#p0.MYI
# ...
# 12K user.frm
# 4.0K user.par  <-- 分區定義文件

5. 分區管理操作

-- 刪除分區（數據同步丟失！）
ALTER TABLE user DROP PARTITION p4;

-- 新增分區
ALTER TABLE user ADD PARTITION (PARTITION p4 VALUES LESS THAN MAXVALUE);

-- 重組分區
ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (
    PARTITION p0 VALUES LESS THAN MAXVALUE
);

6. LIST 分區（枚舉值）

CREATE TABLE list_part (
    id INT NOT NULL,
    province_id INT DEFAULT 0,
    name VARCHAR(50),
    sex INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY LIST (province_id) (
    PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),
    PARTITION p1 VALUES IN (9,10,11,12,16,21),
    PARTITION p2 VALUES IN (13,14,15,19),
    PARTITION p3 VALUES IN (17,18,20,22,23,24)
);
-- 注意：主鍵必須包含分區鍵，否則報錯 1503

7. HASH 分區（均勻分布）

CREATE TABLE hash_part (
    id INT NOT NULL AUTO_INCREMENT,
    comment VARCHAR(1000),
    ip VARCHAR(25),
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY HASH (id) PARTITIONS 3;

8. KEY 分區（服務器哈希）

CREATE TABLE key_part (
    news_id INT,
    content VARCHAR(1000),
    u_id VARCHAR(25),
    create_time DATE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY LINEAR HASH (YEAR(create_time)) PARTITIONS 3;

9. 子分區（復合分區）

CREATE TABLE sub_part (
    news_id INT,
    content VARCHAR(1000),
    u_id INT,
    create_time DATE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (YEAR(create_time))
SUBPARTITION BY HASH (TO_DAYS(create_time)) (
    PARTITION p0 VALUES LESS THAN (1990) (SUBPARTITION s0, s1, s2),
    PARTITION p1 VALUES LESS THAN (2000) (SUBPARTITION s3, s4, good),
    PARTITION p2 VALUES LESS THAN MAXVALUE (SUBPARTITION tank0, tank1, tank3)
);

10. Columns 分區（5.5+，支援字符串/日期直接分區）

-- RANGE COLUMNS
CREATE TABLE daily_log (
    id INT NOT NULL,
    sid CHAR(36),
    sname CHAR(20),
    date DATETIME NOT NULL,
    PRIMARY KEY (id, date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE COLUMNS (date) (
    PARTITION p1 VALUES LESS THAN ('2000-01-02'),
    PARTITION p2 VALUES LESS THAN ('2000-01-03'),
    ...
    PARTITION p367 VALUES LESS THAN (MAXVALUE)
);

分區 vs 分表 核心區別

分區優勢總結

1. 單表突破文件系統大小限制，可跨磁盤
2. 分區裁剪：WHERE 條件命中分區鍵，僅掃描目標分區
3. 大數據並行掃描多個分區
4. 歷史數據歸檔：DROP PARTITION 秒級刪除
5. 索引構建更快（分區級）

生產建議： 百萬行以上考慮分區，千萬行必須分區。分區鍵選擇頻繁出現在 WHERE/JOIN 的列，避免全分區掃描。