MySQL 單表億級資料量效能實測：分區索引與熱點資料的關鍵作用

MySQL 單表億級資料量效能實測：分區索引與熱點資料的關鍵作用

實測單表近 4 億行資料 (393,625,073 行) 的查詢與寫入效能，揭示大表效能的核心決定因素：業務操作集中在熱點資料 + 索引分區檔案不需 IO 切換。

實測環境與資料量

mysql> SELECT COUNT(1) FROM account_log;
+-----------+
| count(1)  |
+-----------+
| 393625073 |  -- 3.9 億行
+-----------+
1 row in set (1 min 6.98 sec)  -- 全表掃描耗時參考

效能實測結果

核心分析：為什麼億級表依然快？

1. 業務熱點集中：99% 以上操作針對近期資料 (如最近 30 天)
2. 索引分區檔案小：熱點索引頁常駐 Buffer Pool，無磁碟 IO
3. 資料分區檔案小：熱點資料頁同樣常駐記憶體
4. 冷資料不訪問：歷史分區檔案不載入記憶體，不佔用資源

關鍵架構設計

-- 範圍分區表示例 (按月分區)
CREATE TABLE account_log (
    id BIGINT NOT NULL AUTO_INCREMENT,
    order_id VARCHAR(64) NOT NULL,
    amount DECIMAL(18,2),
    create_time DATETIME NOT NULL,
    PRIMARY KEY (id, create_time)  -- 分區鍵必須包含在主鍵中
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(create_time)) (
    PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

分區裁剪驗證

EXPLAIN PARTITIONS 
SELECT * FROM account_log 
WHERE create_time >= '2024-03-01' AND create_time < '2024-04-01'
AND order_id = 'ORD_12345';
-- partitions 欄位應只顯示 p202403

大表效能優化清單

監控關鍵指標

-- 分區統計
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'account_log'
ORDER BY PARTITION_ORDINAL_POSITION;

-- Buffer Pool 熱點頁面
SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE TABLE_NAME = 'account_log'
ORDER BY PAGE_TYPE, INDEX_NAME;

結論

單表資料量不是效能殺手，**訪問模式**才是。只要：

• ✅ 業務邏輯天然聚焦熱點區間 (時間/ID 範圍)
• ✅ 合理分區 + 索引設計，使熱點資料常駐記憶體
• ✅ 嚴格禁止全表掃描與跨分區大查詢

億級甚至十億級單表在 MySQL/InnoDB 上完全可控，核心在於冷熱分離的物理實現。