MySQL 分區表獨立資料/索引目錄配置實戰：DATA DIRECTORY 與 INDEX DIRECTORY 參數深度解析

MySQL 5.1+ 支援分區表，且允許為每個分區指定獨立的資料檔案與索引檔案儲存路徑（DATA DIRECTORY、INDEX DIRECTORY）。這對於將熱點分區放置 SSD、冷數據歸檔 HDD、分散 I/O 壓力至多磁碟極具價值。本文詳細解析配置語法、引擎支援度、權限要求與避坑指南。

分區表獨立路徑語法範例

CREATE TABLE users (
    uid INT UNSIGNED NOT NULL PRIMARY KEY,
    name VARCHAR(30) NOT NULL DEFAULT '',
    email VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY RANGE (uid) (
    PARTITION p0 VALUES LESS THAN (3000000)
        DATA DIRECTORY = '/data0/data'
        INDEX DIRECTORY = '/data1/idx',
    PARTITION p1 VALUES LESS THAN (6000000)
        DATA DIRECTORY = '/data2/data'
        INDEX DIRECTORY = '/data3/idx',
    PARTITION p2 VALUES LESS THAN (9000000)
        DATA DIRECTORY = '/data4/data'
        INDEX DIRECTORY = '/data5/idx',
    PARTITION p3 VALUES LESS THAN MAXVALUE
        DATA DIRECTORY = '/data6/data'
        INDEX DIRECTORY = '/data7/idx'
);

引擎支援度對照表

結論：若需使用 DATA/INDEX DIRECTORY 將分區分散至多磁碟，**MyISAM 是原生最佳選擇**；InnoDB 5.7+ 需開啟獨立表空間並透過 ALTER TABLE 遷移，操作較繁瑣。

my.cnf 相關配置與前置條件

必要條件

1. 目錄必須存在：MySQL 不會自動建立目錄
2. MySQL 進程使用者對目錄有讀寫執行權限：

   mkdir -p /data0/data /data1/idx /data2/data /data3/idx ...
   chown -R mysql:mysql /data0 /data1 /data2 /data3 ...
   chmod 750 /data0 /data1 /data2 /data3 ...

3. 檔案系統支援符號連結：參數實現機制是建立符號連結指向目標目錄
4. 關閉 skip_symbolic_links：

   [mysqld]
   skip_symbolic_links = OFF  # 預設 OFF，若開啟會導致失效

InnoDB 獨立表空間前置 (5.7+ 使用 DATA DIRECTORY)

[mysqld]
innodb_file_per_table = ON
# 可選：指定預設資料/索引目錄（不指定則用 datadir）
# innodb_data_home_dir = /data/mysql
# innodb_log_group_home_dir = /data/mysql/logs

實戰操作流程：MyISAM 分區表建立與驗證

1. 建立目錄與權限

# 假設掛載 4 塊磁碟：/mnt/ssd0 /mnt/ssd1 /mnt/hdd0 /mnt/hdd1
for d in /mnt/ssd0/data /mnt/ssd0/idx /mnt/ssd1/data /mnt/ssd1/idx /mnt/hdd0/data /mnt/hdd0/idx /mnt/hdd1/data /mnt/hdd1/idx; do
    mkdir -p $d
done
chown -R mysql:mysql /mnt/ssd0 /mnt/ssd1 /mnt/hdd0 /mnt/hdd1

2. 建立分區表

CREATE TABLE sensor_log (
    id BIGINT UNSIGNED NOT NULL,
    sensor_id INT NOT NULL,
    log_time DATETIME NOT NULL,
    value DOUBLE NOT NULL,
    PRIMARY KEY (id, log_time)
)
PARTITION BY RANGE (YEAR(log_time)) (
    PARTITION p2020 VALUES LESS THAN (2021)
        DATA DIRECTORY = '/mnt/hdd0/data' INDEX DIRECTORY = '/mnt/hdd0/idx',
    PARTITION p2021 VALUES LESS THAN (2022)
        DATA DIRECTORY = '/mnt/hdd0/data' INDEX DIRECTORY = '/mnt/hdd0/idx',
    PARTITION p2022 VALUES LESS THAN (2023)
        DATA DIRECTORY = '/mnt/ssd0/data' INDEX DIRECTORY = '/mnt/ssd0/idx',
    PARTITION p2023 VALUES LESS THAN (2024)
        DATA DIRECTORY = '/mnt/ssd1/data' INDEX DIRECTORY = '/mnt/ssd1/idx',
    PARTITION pmax VALUES LESS THAN MAXVALUE
        DATA DIRECTORY = '/mnt/ssd1/data' INDEX DIRECTORY = '/mnt/ssd1/idx'
) ENGINE = MyISAM;

3. 驗證檔案實際位置

# 檢查資料目錄下的符號連結
ls -la /var/lib/mysql/mydb/
# sensor_log#P#p2020.MYD -> /mnt/hdd0/data/sensor_log#P#p2020.MYD
# sensor_log#P#p2020.MYI -> /mnt/hdd0/idx/sensor_log#P#p2020.MYI

# 或查詢分區資訊
SELECT PARTITION_NAME, DATA_DIRECTORY, INDEX_DIRECTORY
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'sensor_log';

避坑指南與常見錯誤

效能與架構收益

• I/O 隔離：熱分區 (近期數據) 放 NVMe SSD，冷分區 (歷史數據) 放大容量 HDD
• 並行 I/O：多磁碟並行讀寫，突破單磁碟頻寬限制
• 維運彈性：單獨掛載/卸載磁碟、單獨備份分區、單獨壓縮冷數據
• 成本優化：按存取頻率分級儲存，大幅降低儲存成本

InnoDB 5.7+ 替代方案：ALTER TABLE 移動分區

-- 建立表時不指定目錄（使用預設 datadir）
CREATE TABLE t (id INT PRIMARY KEY, dt DATE) 
PARTITION BY RANGE (YEAR(dt)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022)
) ENGINE=InnoDB;

-- 事後遷移冷分區至 HDD
ALTER TABLE t REORGANIZE PARTITION p2020 INTO (
    PARTITION p2020 VALUES LESS THAN (2021) DATA DIRECTORY = '/mnt/hdd0/data'
);

需 innodb_file_per_table=ON，操作會鎖表重建 .ibd，大表建議低峰期執行。

總結

DATA DIRECTORY/INDEX DIRECTORY 是 MySQL 分區表實現儲存分級的核心功能。**MyISAM 原生完美支援**，InnoDB 5.7+ 需配合獨立表空間與 ALTER TABLE 實現。生產落地關鍵在於：**目錄預建與權限、檔案系統符號連結支援、備份工具相容性測試、監控實際 I/O 分布驗證效果**。