MySQL转PostgreSQL全流程指南：从评估到上线的完整方案

一、迁移前评估与规划

1.1 兼容性分析矩阵

建立包含数据类型、函数、存储过程、触发器等维度的兼容性矩阵。例如：

• 时间类型：MySQL的DATETIME对应PostgreSQL的TIMESTAMP，但时区处理机制不同
• 自增字段：MySQL的AUTO_INCREMENT需转换为PostgreSQL的SERIAL或IDENTITY
• 字符串处理：MySQL的CHAR/VARCHAR与PostgreSQL的TEXT类型在存储效率上有差异

1.2 迁移成本估算模型

采用公式：总成本 = 开发成本 + 测试成本 + 停机成本 + 性能优化成本
其中开发成本包含：

• 模式转换：约2-4人天/千张表
• 应用层改造：视SQL复杂度而定
• 存储过程重写：通常需要1:3的代码量转换

二、核心迁移工具与技术选型

2.1 主流迁移工具对比

工具名称	优势	局限性	适用场景
pgloader	全自动迁移，支持增量同步	复杂SQL转换能力有限	快速迁移，表结构简单
AWS Schema Conversion Tool	云原生集成，可视化界面	仅支持AWS生态	混合云环境迁移
自定义ETL脚本	完全可控，可处理复杂逻辑	开发周期长（通常2-4周）	金融等高安全要求行业

2.2 推荐技术栈

数据抽取层：Debezium + Kafka（CDC方案）
转换层：Apache Spark（复杂转换场景）
加载层：pg_bulkload（高性能导入）
监控层：Prometheus + Grafana（迁移过程监控）

三、数据类型深度转换指南

3.1 数值类型转换表

MySQL类型	PostgreSQL等效类型	精度说明
TINYINT	SMALLINT	需考虑符号位（MySQL无符号）
MEDIUMINT	INTEGER	范围差异需特别注意
FLOAT	REAL	浮点精度处理机制不同

3.2 JSON处理差异

MySQL 5.7+的JSON类型与PostgreSQL的JSONB对比：

-- MySQL查询示例
SELECT id, JSON_EXTRACT(data, '$.name') FROM table;
-- PostgreSQL等效查询
SELECT id, data->>'name' FROM table;

PostgreSQL的JSONB支持索引优化，但写入性能略低于MySQL的JSON

四、SQL语法适配方案

4.1 分页查询转换

MySQL的LIMIT OFFSET语法：

SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 20;

PostgreSQL推荐使用CTE优化：

WITH numbered_rows AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS row_num
  FROM orders
)
SELECT * FROM numbered_rows WHERE row_num BETWEEN 21 AND 30;

4.2 存储过程重写策略

1. 语法结构转换：

   • MySQL的DELIMITER //需替换为PostgreSQL的DO $$块
   • 变量声明从DECLARE var INT;改为var INTEGER;

2. 错误处理机制：
   ```sql
   — MySQL
   DECLARE EXIT HANDLER FOR SQLEXCEPTION
   BEGIN
   ROLLBACK;
   SELECT ‘Error occurred’;
   END;

— PostgreSQL
BEGIN
— 业务逻辑
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE NOTICE ‘Error occurred: %’, SQLERRM;
END;
$$

## 五、性能优化实战
### 5.1 索引策略调整
1. 复合索引设计原则：
   - PostgreSQL对索引顺序更敏感，建议将高选择性列放在前面
   - 示例：`CREATE INDEX idx_customer_name ON customers(last_name, first_name)`
2. 部分索引应用：
```sql
-- 仅索引活跃用户
CREATE INDEX idx_active_users ON users(email) 
WHERE status = 'active';

5.2 查询优化技巧

1. 统计信息更新：

   ANALYZE verbose table_name;  -- 生成详细统计信息

2. 执行计划分析：

   EXPLAIN (ANALYZE, BUFFERS) 
   SELECT * FROM orders WHERE order_date > '2023-01-01';

六、迁移后验证方案

6.1 数据一致性校验

1. 行数校验：
   ```sql
   — MySQL
   SELECT COUNT(*) FROM source_table;

— PostgreSQL
SELECT COUNT(*) FROM target_table;

2. 抽样校验脚本示例：
```python
import psycopg2
import pymysql
import random
def validate_sample(source_config, target_config, table_name, sample_size=100):
    # 连接数据库代码...
    # 获取源表主键列表
    cursor_source.execute(f"SELECT id FROM {table_name} ORDER BY RAND() LIMIT {sample_size}")
    ids = [row[0] for row in cursor_source.fetchall()]
    for id in ids:
        # 查询源表数据
        cursor_source.execute(f"SELECT * FROM {table_name} WHERE id = {id}")
        source_data = cursor_source.fetchone()
        # 查询目标表数据
        cursor_target.execute(f"SELECT * FROM {table_name} WHERE id = {id}")
        target_data = cursor_target.fetchone()
        # 比较数据...

6.2 性能基准测试

1. 测试用例设计：

   • 简单查询：单表主键查询
   • 复杂查询：多表JOIN+聚合
   • 并发测试：使用pgbench进行压力测试

2. 性能对比模板：
   | 测试场景 | MySQL响应时间 | PostgreSQL响应时间 | 优化建议 |
   |————————|————————|——————————-|—————————-|
   | 单表查询 | 12ms | 8ms | 保持现有索引 |
   | 多表JOIN | 120ms | 95ms | 添加物化视图 |
   | 高并发写入 | 450TPS | 620TPS | 调整work_mem参数 |

七、常见问题解决方案

7.1 字符集处理

1. 迁移前检查：
   ```sql
   — MySQL
   SHOW VARIABLES LIKE ‘character_set%’;

— PostgreSQL
SELECT name, setting FROM pg_settings WHERE name LIKE ‘%encoding%’;

2. 转换建议：
   - 统一使用UTF8编码
   - 特殊字符处理：使用`CONVERT()`函数或ETL工具转换
### 7.2 事务隔离级别适配
| MySQL级别 | PostgreSQL等效 | 行为差异                     |
|-----------|----------------|------------------------------|
| READ COMMITTED | READ COMMITTED | 基本一致                     |
| REPEATABLE READ | SERIALIZABLE | PostgreSQL实现更严格         |
| SERIALIZABLE | SERIALIZABLE | 性能开销增加约30%            |
## 八、持续优化建议
1. 监控指标体系：
   - 数据库连接数：`SELECT count(*) FROM pg_stat_activity`
   - 缓存命中率：`SELECT sum(blks_hit)*100/(sum(blks_hit)+sum(blks_read)) FROM pg_statio_user_tables`
   - 锁等待情况：`SELECT * FROM pg_locks WHERE granted = false`
2. 定期维护任务：
```sql
-- 每周维护
VACUUM ANALYZE VERBOSE;
-- 每月维护
REINDEX DATABASE your_db;

本方案通过系统化的迁移方法论，结合实际案例与工具推荐，为MySQL到PostgreSQL的迁移提供了从评估到优化的完整路径。实施过程中建议采用分阶段迁移策略，先进行非核心业务试点，再逐步推广至全量业务，确保迁移过程可控可逆。