真實痛點：電商訂單表存儲優化場景

現狀分析
某電商平臺訂單表（order_info）每月新增500萬條記錄

• 主庫：高頻讀寫，SSD存儲（空間告急）
• 歷史庫：HDD存儲，只讀查詢

優化目標

• ? 自動遷移7天前的訂單到歷史庫
• ? 每周六23:30執行，不影響業務高峰
• ? 確保數據一致性

---

第一章：前期準備：沙盒實驗室搭建

1.1 實驗環境架構

生產庫：10.33.112.22

歷史庫：10.30.76.4

1.2 環境初始化（雙節點執行）

# 主庫建表
CREATE TABLE order_info (id BIGINT AUTO_INCREMENT PRIMARY KEY,order_no VARCHAR(32) UNIQUE,amount DECIMAL(10,2),create_time DATETIME DEFAULT CURRENT_TIMESTAMP,INDEX idx_create_time(create_time)
) ENGINE=InnoDB;# 需要在歷史庫建表（保持相同結構）
CREATE TABLE order_archive (id BIGINT AUTO_INCREMENT PRIMARY KEY,order_no VARCHAR(32) UNIQUE,amount DECIMAL(10,2),create_time DATETIME DEFAULT CURRENT_TIMESTAMP,INDEX idx_create_time(create_time)
) ENGINE=InnoDB;

---

第二章：數據搬遷實戰

2.1 模擬數據生成（快速生成30天數據）

# 登錄主庫執行
DELIMITER $$
CREATE PROCEDURE generate_orders()
BEGINDECLARE i INT DEFAULT 0;WHILE i < 50000 DOINSERT INTO order_info(order_no, amount, create_time)VALUES (CONCAT('NO', DATE_FORMAT(NOW(),'%Y%m%d'), LPAD(i,6,'0')),ROUND(RAND()*1000,2),DATE_SUB(NOW(), INTERVAL FLOOR(RAND()*30) DAY));SET i = i + 1;END WHILE;
END$$
DELIMITER ;CALL generate_orders();   -- 執行存儲過程
DROP PROCEDURE generate_orders;

數據驗證：

SELECT COUNT(*) AS total,MIN(create_time) AS earliest,MAX(create_time) AS latest 
FROM order_info;+-------+---------------------+---------------------+
| total | earliest            | latest              |
+-------+---------------------+---------------------+
| 50000 | 2025-02-19 16:24:17 | 2025-03-20 16:34:00 |
+-------+---------------------+---------------------+

---

2.2 PT-Archiver手動搬遷示范

./pt-archiver \
--source h=10.33.112.22,D=pt,t=order_info,u=root,p=密碼 \
--dest h=10.30.76.4,D=pt,t=order_archive,u=root,p=密碼 \
--where "create_time < DATE_SUB(NOW(), INTERVAL 7 DAY)" \
--progress 1000 \
--bulk-delete \
--limit 5000 \
--no-check-charset \
--nosafe-auto-increment \
--commit-each

• 參數解釋（參數 | 說明）：

  –source | 指定源數據庫連接信息（IP/庫名/表名/賬號密碼）
  –dest | 指定目標數據庫連接信息（IP/庫名/表名/賬號密碼）
  –where | 數據篩選條件（刪除7天前的數據）
  –progress | 每處理1000行輸出進度
  –bulk-delete | 啟用批量刪除模式（代替逐行刪除）
  –limit | 每批處理5000條數據
  –no-check-charset | 跳過字符集一致性檢查
  –nosafe-auto-increment | 禁用自增主鍵安全校驗 （避免漏掉最后一行數據）
  –commit-each | 逐行提交事務（默認批量提交）

---

2.3 遷移效果驗證

主庫查詢：

SELECT COUNT(*) AS total,MIN(create_time) AS earliest,MAX(create_time) AS latest 
FROM order_info;+-------+---------------------+---------------------+
| total | earliest            | latest              |
+-------+---------------------+---------------------+
| 11638 | 2025-03-15 11:16:51 | 2025-03-21 11:25:56 |
+-------+---------------------+---------------------+

歷史庫驗證：

SELECTCOUNT(*) AS total,MIN(create_time) AS earliest,MAX(create_time) AS latest
FROM order_archive;
+-------+---------------------+---------------------+
| total | earliest            | latest              |
+-------+---------------------+---------------------+
| 38362 | 2025-02-20 11:16:51 | 2025-03-14 11:25:55 |
+-------+---------------------+---------------------+

11638+38362=50000，無誤。遷移成功！

---

第三章：無人值守自動化方案

接下來我們要做成每周某，定時自動遷移

3.1 自動化配置

vim /scripts/archive_orders.sh#!/bin/bash
LOG_FILE="/var/log/archive_$(date +%Y%m%d).log"/opt/percona-toolkit-3.6.0/bin/pt-archiver \
--source h=10.33.112.22,D=pt,t=order_info,u=root,p=密碼 \
--dest h=10.30.76.4,D=pt,t=order_archive,u=root,p=密碼 \
--where "create_time < DATE_SUB(NOW(), INTERVAL 7 DAY)" \
--progress 1000 \
--bulk-delete \
--limit 5000 \
--purge \
--no-check-charset \
--nosafe-auto-increment \
--commit-each >> ${LOG_FILE} 2>&1

授權執行：

chmod +x /scripts/archive_orders.sh

---

3.3 配置定時任務

crontab -e# 每周六23:30執行
30 23 * * 6 /bin/bash /scripts/archive_orders.sh

關鍵檢查項：

1. 確保pt-archiver在PATH中
2. 定時任務用戶有權限訪問數據庫
3. 日志目錄提前創建

---

結語：解放人力的最后一步

大功告成！此時生產數據庫：

? 主庫始終保持輕量級狀態
? 歷史查詢不再影響核心業務
? 自動歸檔策略穩定運行

現在就去為你的數據庫實施這套方案吧！