分區表解決單表過大維護難題,主從複寫解決高可用與讀寫分離。兩者結合是大規模 MySQL 架構的標配。本文梳理分區類型選型、主從拓撲配置、常用運維命令,形成可直接落地的知識卡片。
| 類型 | 分區鍵 | 典型場景 | 優缺點 |
| RANGE | 連續範圍(日期、ID段) | 按月/年存儲日志、訂單;歷史分區易歸檔刪除 | + 輕鬆 DROP PARTITION 歸檔 - 熱點寫入集中最新分區 |
| LIST | 離散值集合(地區、狀態碼) | 按省份/業務線隔離數據 | + 精確控制分佈 - 值變更需 REORGANIZE |
| HASH | 任意表達式返回非負整數 | 均勻分散寫入熱點,無明確業務邊界 | + 寫入負載均衡 - 範圍查詢需掃描全部分區 |
| KEY | 列列表,MySQL 內部哈希 | 同 HASH,但支援非整數列、NDB Cluster | + 無需自定義函數 - 同 HASH 查詢特點 |
| LINEAR HASH/KEY | 線性哈希算法 | 大量分區(>1024)時增減分區更高效 | + 避免大量數據重分佈 - 分布略不均勻 |
3. 時間分區最佳實踐
-- RANGE COLUMNS (5.5+ 原生支援 DATE/DATETIME,無需 TO_DAYS)
CREATE TABLE orders (
id BIGINT, order_date DATE, amount DECIMAL(10,2), ...
PRIMARY KEY (id, order_date)
) PARTITION BY RANGE COLUMNS (order_date) (
PARTITION p202301 VALUES LESS THAN ('2023-02-01'),
PARTITION p202302 VALUES LESS THAN ('2023-03-01'),
...
PARTITION pmax VALUES LESS THAN MAXVALUE
);
-- 歸檔:秒級元資料操作,無數據拷貝
ALTER TABLE orders DROP PARTITION p202301;
-- 新增:
ALTER TABLE orders ADD PARTITION (PARTITION p202312 VALUES LESS THAN ('2024-01-01'));
二、主從複寫生產級配置
1. 主庫必備配置
[mysqld]
server-id = 101 # 集群唯一
log-bin = mysql-bin
binlog_format = ROW # 唯一安全格式
binlog_expire_logs_seconds = 604800 # 7 天
max_binlog_size = 500M
gtid_mode = ON # 5.6+ GTID 模式(強烈推薦)
enforce_gtid_consistency = ON
sync_binlog = 1 # 雙 1 配合 innodb_flush_log_at_trx_commit=1
innodb_flush_log_at_trx_commit = 1
2. 從庫必備配置
[mysqld]
server-id = 102
relay-log = relay-bin
relay-log-index = relay-bin.index
gtid_mode = ON
enforce_gtid_consistency = ON
read_only = ON # 防誤寫
super_read_only = ON # 8.0+ 連 SUPER 也禁寫
skip_slave_start = ON # 手動控制啟動
slave_parallel_workers = 8 # 並行複寫(邏輯時鐘/基於庫)
slave_preserve_commit_order = ON # 保證提交順序一致性
master_info_repository = TABLE
relay_log_info_repository = TABLE
3. 建立複寫關係(GTID 模式,無需手動座標)
-- 主庫建帳號
CREATE USER 'repl'@'10.0.0.%' IDENTIFIED BY 'StrongPass#2024';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%';
-- 從庫配置並啟動
CHANGE MASTER TO
MASTER_HOST='10.0.0.101',
MASTER_USER='repl',
MASTER_PASSWORD='StrongPass#2024',
MASTER_AUTO_POSITION=1; -- GTID 核心,自動定位
START SLAVE;
-- 驗證
SHOW SLAVE STATUS\G
-- Slave_IO_Running: Yes, Slave_SQL_Running: Yes, Seconds_Behind_Master: 0
-- Retrieved_Gtid_Set / Executed_Gtid_Set 對比
三、常用運維命令速查
| 場景 | 命令 |
| 查看分區信息 | SHOW TABLE STATUS LIKE 'orders'\G / EXPLAIN PARTITIONS SELECT * FROM orders WHERE order_date>'2023-01-01'; |
| 查看主從狀態 | SHOW SLAVE STATUS\G / SHOW MASTER STATUS\G |
| 跳過錯誤事務(極謹慎) | STOP SLAVE; SET GLOBAL sql_slave_skip_counter=1; START SLAVE; (GTID 模式用 SET GTID_NEXT='...'; BEGIN; COMMIT; SET GTID_NEXT=AUTOMATIC;) |
| 主從切換(手動) | 原主 READ ONLY=ON → 從庫 STOP SLAVE; RESET SLAVE ALL; READ ONLY=OFF → 應用切 VIP |
| 授權任意主機 root(僅內網臨時) | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES; |
| 查看 server_id | SHOW VARIABLES LIKE 'server_id'; |
四、分區 + 主從 組合避坑
- 分區表 DDL(ADD/DROP/TRUNCATE PARTITION)會寫 Binlog,從庫同步執行,**大分區操作會阻塞 SQL 線程**,建議低峰期操作。
- 從庫
read_only 不阻擋複寫線程執行 DDL。
- 分區裁剪只在主庫生效,從庫同步執行時同樣受益於分區裁剪(ROW 格式)。
- 監控
Seconds_Behind_Master、Relay_Log_Space、Slave_SQL_Running_State 及時發現延遲/錯誤。
分區讓單表維护可控,主從讓架構高可用。掌握 RANGE COLUMNS 時間分區、GTID 自動定位、並行複寫三大核心技能,配合標準化運維清單,即可駕馭 TB 級 MySQL 集群穩健演進。
copyright ©
萬盛學電腦網 all rights reserved