MySQL 分區支持檢測與核心原理：橫向分區 vs 縱向分區實戰指南

一、檢測是否支持分區

方法一：變量查詢

SHOW VARIABLES LIKE '%partition%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+

YES 表示支持。

方法二：插件查看

SHOW PLUGINS;
+------------+----------+----------------+---------+--------+
| Name       | Status   | Type           | Library | License|
+------------+----------+----------------+---------+--------+
| partition  | ACTIVE   | STORAGE ENGINE | NULL    | GPL    |
+------------+----------+----------------+---------+--------+

存在 partition ACTIVE STORAGE ENGINE 即支持。

二、分區核心原理

MySQL 數據以文件形式存儲於 datadir（默認 /var/lib/mysql）：

• .frm：表結構定義
• .ibd (InnoDB) / .MYD (MyISAM)：數據文件
• .MYI (MyISAM)：索引文件

分區本質：將單表的上述文件**物理拆分為多個小片段**，每個分區獨立存儲，邏輯上仍為一張表。

三、兩大分區策略

1. 橫向分區 - 行級劃分

定義：按行數據劃分，每個分區包含完整列結構，僅行數不同。

-- 範圍分區：按年份劃分訂單表
CREATE TABLE orders (
    id INT NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

優勢：

• 查詢裁剪：WHERE order_date > '2022-01-01' 僅掃描 p2022/pmax
• 歸檔刪除：ALTER TABLE orders DROP PARTITION p2020 秒級清理歷史數據
• 跨磁盤分佈：不同分區掛載不同存儲設備

2. 縱向分區 - 列級劃分

定義：將寬表拆分為多個窄表，共享主鍵，按業務訪問頻度分離。

-- 原寬表
CREATE TABLE user_profile (
    user_id BIGINT PRIMARY KEY,
    username VARCHAR(32),
    email VARCHAR(64),
    bio TEXT,              -- 大字段，低頻訪問
    avatar BLOB,           -- 二進制大對象
    settings JSON,         -- 配置信息
    created_at DATETIME
);

-- 縱向拆分
CREATE TABLE user_core (
    user_id BIGINT PRIMARY KEY,
    username VARCHAR(32),
    email VARCHAR(64),
    created_at DATETIME
);

CREATE TABLE user_extended (
    user_id BIGINT PRIMARY KEY,
    bio TEXT,
    avatar BLOB,
    settings JSON,
    FOREIGN KEY (user_id) REFERENCES user_core(user_id)
);

優勢：

• 核心表體積小，高頻查詢（登錄、列表）極快
• 大字段隔離，避免 SELECT * 讀取無關數據
• 獨立索引優化，核心表加聚合索引，擴展表加全文索引

四、MySQL 分區類型對比

五、分區限制與避坑

1. 分區鍵必須包含在主鍵/唯一鍵中：PRIMARY KEY (id, order_date)
2. 外鍵不支持分區表：需應用層維護關聯完整性
3. 最大 8192 個分區（5.7+），過多分區元數據開銷大
4. 全表鎖操作：ALTER TABLE ... REORGANIZE PARTITION 會鎖表，大表用 pt-online-schema-change
5. 查詢必須帶分區鍵條件：否則全分區掃描，性能反而下降

六、分區 vs 分表 vs 分庫

分區是**單表內部的物理優化**，解決「大表查詢慢、歷史數據難清理、單磁盤瓶頸」；分表/分庫是**架構級水平擴展**，解決「寫入吞吐瓶頸、存儲容量無上限」。**先優化索引，再考慮分區，最後才是分表分庫**。