第十篇：MySQL 实战：数据迁移、分库分表与分区技术指南

随着系统数据量与访问压力的增长，MySQL 单实例常面临性能瓶颈。本篇系统讲解如何进行 数据迁移、分库分表 与 分区表设计，并结合实践案例提供完整的优化思路。

一、MySQL 数据迁移方式

1. 场景分类

场景	推荐工具
同版本、本地迁移	mysqldump、cp+ibdata
跨版本、跨机房迁移	mysqldump、mydumper、pt-archiver
在线无停机迁移	gh-ost、Percona XtraBackup、同步复制

2. 使用 mysqldump（适合小型数据）

# 备份数据库
mysqldump -uroot -p mydb > mydb.sql

# 迁移目标服务器导入
mysql -uroot -p mydb < mydb.sql

3. 使用 Percona XtraBackup（推荐企业使用）

支持热备份、不锁表，适合大规模数据在线迁移。

xtrabackup --backup --target-dir=/backup/ --datadir=/var/lib/mysql
xtrabackup --prepare --target-dir=/backup/

二、分库分表策略详解

1. 为什么要分库分表？

• 单表数据量过大（>千万行）导致查询效率下降；

• 单库连接数、锁竞争瓶颈严重；

• 提升写入吞吐量与负载均衡能力。

2. 分库 vs 分表

类型	说明	应用场景
水平分表	同库，按主键或时间拆分多个表	单表太大
水平分库	多库，按用户 ID 分散到不同库	访问压力大
垂直分表	不同字段拆分存储	表字段过多
垂直分库	模块拆分，如用户、订单库	系统解耦

3. 分表策略示例

-- 按用户 ID hash 分表
CREATE TABLE user_0 (...);
CREATE TABLE user_1 (...);

-- 插入数据时
SET @table_id = MOD(user_id, 2);
SET @sql = CONCAT('INSERT INTO user_', @table_id, ' VALUES(...)');
PREPARE stmt FROM @sql;
EXECUTE stmt;

注意： 分表后不支持跨表 JOIN 查询，需借助中间件如 ShardingSphere、MyCat、PolarDB。

 三、MySQL 分区表详解（Partition）

1. 为什么使用分区？

• 优化大表查询性能；

• 加快归档与清理；

• 自动路由 SQL 到指定分区；

• 支持高效的范围查询、按时间删除等。

2. 分区类型

分区类型	描述
RANGE	按数值/时间范围
LIST	枚举列表分区
HASH	按哈希值均匀分布
KEY	类似 HASH，自动选择分区函数

3. RANGE 分区示例（按月份）

CREATE TABLE order_log (
  id BIGINT,
  create_time DATE
)
PARTITION BY RANGE (YEAR(create_time)*100 + MONTH(create_time)) (
  PARTITION p202301 VALUES LESS THAN (202302),
  PARTITION p202302 VALUES LESS THAN (202303),
  PARTITION pmax     VALUES LESS THAN MAXVALUE
);

4. 分区操作命令

• 添加新分区：

ALTER TABLE order_log ADD PARTITION ( PARTITION p202304 VALUES LESS THAN (202305) );

• 删除旧分区（归档或清理）：

ALTER TABLE order_log DROP PARTITION p202301;

四、工具链与中间件推荐

工具/平台	功能
ShardingSphere	分库分表中间件，支持事务、读写分离
MyCat	国人开发的分库分表中间件
Canal	数据同步/增量迁移利器
gh-ost	在线无锁结构变更/迁移
MySQL Fabric	官方分库高可用方案（已不推荐）

五、实战：百万级订单表拆分方案

背景

• 单表超过 500 万订单，查询严重拖慢；

• 要求按月查询效率高、支持并发写入；

• 目标：分区表 + 分库备份归档。

方案

1. 按月 RANGE 分区建表；

2. 设置自动任务，每月自动添加新分区；

3. 使用 DROP PARTITION 实现 6 个月外数据自动归档；

4. 归档数据转存到历史库中。

总结

• 小规模数据可用 mysqldump 迁移，大数据建议用 XtraBackup；

• 分库分表需考虑分片键、路由策略与查询代价；

• 分区表可解决部分大表场景，但不适用于高频跨分区操作；

• 多表多库方案建议配合中间件，简化业务改造。