centos mysql分区策略

CentOS 上 MySQL 分区策略

一 策略总览

• 明确目标：优先解决查询裁剪、快速删除历史、降低单表体积、I/O 隔离等痛点，避免为了分区而分区。
• 选择分区键：优先选择出现在高频查询的WHERE条件中、具备高基数且单调变化的列（如时间列）。
• 选择分区类型：按时间范围查询用RANGE；离散值集合用LIST；需要均匀打散用HASH/KEY；超大数据可结合子分区。
• 分区粒度：按天/周/月分区较常见；粒度越细管理成本越高，建议先做容量评估与压测。
• 分区上限：单表最多1024个分区；分区过多会带来元数据与维护开销，影响优化器与 DML 性能。
• 约束与限制：有主键/唯一索引时，分区键必须包含其中；分区表不支持外键；所有分区必须使用相同存储引擎。

二 表级分区类型与适用场景

三 操作系统与磁盘分区建议

• 目录分离：将数据目录 datadir、二进制日志、备份目录分别挂载到不同磁盘/LV，降低 I/O 争用。
• 文件系统：CentOS 7 默认使用XFS；MySQL 数据目录常见路径为**/var/lib/mysql**（可在 my.cnf 的datadir查看/修改）。
• 示例（简化）：使用新磁盘创建分区并挂载到**/data**，然后在 my.cnf 中设置datadir=/data，重启 mysqld 生效。
• 临时目录：可将**/tmp挂载为tmpfs**（如 8–16G）以加速临时文件操作（注意内存容量）。

四 落地示例 按时间范围分区

• 场景：日志表按天分区，便于按日期查询与快速删除旧分区。
• 建表示例（MySQL 5.7+，InnoDB）：

CREATE TABLE logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  log_date DATE NOT NULL,
  message VARCHAR(255) NOT NULL,
  PRIMARY KEY (id, log_date)  -- 分区键必须进入主键/唯一键
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(log_date)) (
  PARTITION p20250101 VALUES LESS THAN (TO_DAYS('2025-01-02')),
  PARTITION p20250102 VALUES LESS THAN (TO_DAYS('2025-01-03')),
  PARTITION p20250103 VALUES LESS THAN (TO_DAYS('2025-01-04')),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

• 维护要点：
  • 提前创建未来分区，避免插入时报错。
  • 按保留策略DROP PARTITION快速删除旧数据，避免大表 DELETE 带来的碎片与长事务。
  • 查询带上分区键（如log_date）以触发分区裁剪。

五 运维与优化要点

• 分区裁剪：确保高频查询在 WHERE 中使用分区键，减少扫描分区数量。
• 数量控制：避免过多分区（≤1024），关注元数据、优化器与备份恢复成本。
• 约束遵循：主键/唯一索引必须包含分区键；分区表不支持外键；统一存储引擎。
• 设计权衡：分区能改善局部性能与维护效率，但跨分区查询可能变慢；必要时结合应用侧分片或冷热分层。
• 变更与回滚：生产变更前在准生产环境压测；准备回滚脚本与备份策略，变更窗口内控制 DML 并发。