萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 分區表與主從複寫學習筆記:從原理到生產級配置清單

分區表解決單表過大維護難題,主從複寫解決高可用與讀寫分離。兩者結合是大規模 MySQL 架構的標配。本文梳理分區類型選型、主從拓撲配置、常用運維命令,形成可直接落地的知識卡片。

一、分區表核心知識

1. 支援與限制

  • MySQL 5.6+ 僅支援水平分區(行級拆分),不支援垂直分區。
  • 引擎支援:InnoDB、MyISAM、NDB(需 KEY/LINEAR KEY);不支援 MERGE、CSV、FEDERATED。
  • 檢查支援:SHOW PLUGINS; 找 partition ACTIVE。
  • 顯式分區查詢:SELECT * FROM t PARTITION (p0, p1) WHERE c < 5;

2. 四大分區類型選型指南

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 對比

三、常用運維命令速查

類型分區鍵典型場景優缺點
RANGE連續範圍(日期、ID段)按月/年存儲日志、訂單;歷史分區易歸檔刪除+ 輕鬆 DROP PARTITION 歸檔
- 熱點寫入集中最新分區
LIST離散值集合(地區、狀態碼)按省份/業務線隔離數據+ 精確控制分佈
- 值變更需 REORGANIZE
HASH任意表達式返回非負整數均勻分散寫入熱點,無明確業務邊界+ 寫入負載均衡
- 範圍查詢需掃描全部分區
KEY列列表,MySQL 內部哈希同 HASH,但支援非整數列、NDB Cluster+ 無需自定義函數
- 同 HASH 查詢特點
LINEAR HASH/KEY線性哈希算法大量分區(>1024)時增減分區更高效+ 避免大量數據重分佈
- 分布略不均勻
場景命令
查看分區信息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_idSHOW 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