MySQL 分區技術核心原理與四大類型完整指南（一）

為什麼需要分區

海量資料優化兩大路徑：

1. 物理拆表：垂直拆欄、水平拆行 → 應用層需維護多表邏輯，開發成本高、易出錯。
2. SQL/索引優化：大表索引維護代價大，單表極限難突破。

分區技術：邏輯上單表，物理上多檔案，應用零改造，兼具管理彈性與查詢效能。

MySQL 5.1+ 四大分區類型

分區實體架構

• 邏輯：單一表名，SHOW TABLES 僅見一張。
• 物理：每分區對應獨立 .ibd（InnoDB）或 .MYD/.MYI（MyISAM）。
• 索引：每分區獨立 B+ 樹，查詢可透過 Partition Pruning 僅掃描相關分區。

InnoDB 獨立表空間前置條件

InnoDB 預設共享表空間 ibdata1 無法真正分區，必須啟用 innodb_file_per_table = 1 並重啟，新建表才會產生獨立 .ibd。

[mysqld]
innodb_file_per_table = 1
innodb_data_home_dir = /data/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /data/mysql/

常用檢查指令

SHOW ENGINES;              -- 確認 partitioning 支援
SHOW PLUGINS;              -- partition 插件 ACTIVE
SHOW CREATE TABLE tbl;     -- 查看分區定義
SHOW INDEX FROM tbl;       -- 索引分區資訊
EXPLAIN PARTITIONS SELECT ... -- 驗證 Partition Pruning

測試存儲過程批量寫入

DELIMITER //
CREATE PROCEDURE batch_insert()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i < 100000 DO
        INSERT INTO t_hash VALUES (i);
        SET i = i + 1;
    END WHILE;
END//
DELIMITER ;

CALL batch_insert();

避坑指南

1. 分區鍵必須包含於主鍵/唯一鍵中。
2. 查詢未帶分區鍵條件 → 全分區掃描，效能反而下降。
3. 外鍵不支援分區表（InnoDB 限制）。
4. 分區數過大（>100）元數據開銷大，DDL 變慢。