centos mysql分区策略 - 问答

CentOS 上 MySQL 分区策略

一策略总览

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

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

类型	典型场景	关键要点
RANGE	时间序列、按日期归档	支持分区裁剪，便于按时间快速删除旧数据
LIST	按地区/租户等离散值分片	值必须明确枚举，维护新增值需调整分区
HASH	需要均匀分布的场景	数据分布均衡，但不利于范围查询与裁剪
KEY	使用 MySQL 内部哈希函数	类似 HASH，适合主键或高基数列
子分区	超大型表进一步拆分	常与 RANGE + HASH/KEY 组合，提升管理与并行度
说明：分区类型与特性以 MySQL 官方能力为准，以上为工程常用取舍。

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

目录分离：将数据目录 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 并发。

0 赞

0 踩