MySQL 性能優化進階：分表策略、增量備份與還原實戰

一、水平分表

將大表按邏輯拆分為多個同結構小表，查詢時僅掃描相關分表。

適用場景

• 單表超千萬行，頻繁查詢
• 業務天然可分：按時間（訂單按月/年）、按地域、按用戶 ID 取模

關鍵點

• 分表鍵必須出現在查詢條件中，否則需 UNION ALL 全表掃描，得不償失
• 應用層路由：根據分表鍵動態拼表名

-- 查詢 2023 年訂單
SELECT * FROM order_2023 WHERE user_id=100;

二、垂直分表

將寬表拆為「主表（高頻小字段）」+「副表（低頻大字段）」，一對一關聯。

典型案例

試題表 3 萬行 1GB，列表頁只查標題、難度，詳情頁才需內容、答案。拆分後列表頁不再掃描大字段，IO 驟降。

-- 主表
CREATE TABLE question (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    difficulty TINYINT,
    unit_id INT
);
-- 副表
CREATE TABLE question_content (
    qid INT PRIMARY KEY,
    content TEXT,
    answer TEXT,
    FOREIGN KEY (qid) REFERENCES question(id)
);

三、字段類型選擇原則

• 越小越好：能用 TINYINT 不用 INT，能用 INT 不用 BIGINT
• 主鍵首選自增整數：4 字節 vs 32 字節 UUID，Join 性能差數量級
• 業務主鍵與物理主鍵分離：物理主鍵用無意義自增 ID，業務唯一性加唯一索引
• 大文本/圖片存文件系統：DB 僅存路徑，甚至上傳對象存儲

四、核心參數配置

[mysqld]
innodb_buffer_pool_size = 1G
innodb_additional_mem_pool_size = 64M
key_buffer_size = 256M
default-storage-engine = INNODB
max_connections = 500
port = 3306

五、架構擴展：讀寫分離

Master 負責寫，多 Slave 負責讀。中間件（MyCat、ProxySQL、ShardingSphere）實現透明路由，應用無感知。

六、定時備份策略

全量備份

# Windows 計劃任務每天 02:00
mysqldump -u root -p db > D:/backup/db_%date%.sql

增量備份（基於 Binlog）

1. 開啟 Binlog：log-bin=G:/mysqlbinlog/mylog，重啟服務
2. 誤操作發生後，記錄時間點或 Position
3. 按時間點恢復：

mysqlbinlog --stop-datetime="2023-10-01 12:00:00" \
  G:/mysqlbinlog/mylog.000001 | mysql -u root -p

4. 按位置恢復：

mysqlbinlog --stop-position="614" \
  G:/mysqlbinlog/mylog.000001 | mysql -u root -p

注意：Binlog 僅記錄 DDL/DML，不記錄 SELECT；MySQL 5.0+ 支持。