MySQL到PostgreSQL迁移实战与性能优化-开发者社区

1. 项目背景与迁移决策

去年接手公司核心业务系统时，这套基于MySQL 5.7的架构已经运行了5年。随着业务量从日均10万增长到300万订单，我们开始频繁遭遇几个典型问题：事务锁等待超时、复杂查询性能骤降、JSON字段处理笨拙。最严重的一次促销活动，因为间隙锁(Next-Key Lock)导致的死锁，直接造成订单服务瘫痪47分钟。

经过三个月的数据基准测试(PG 14 vs MySQL 8.0)，在相同硬件环境下：PostgreSQL的JSONB查询速度比MySQL快3-8倍，50个并发事务时PG的吞吐量高出62%，特别是GIS地理查询性能差异达到惊人的15倍。最终技术委员会拍板：在Q2季度完成数据库迁移。

关键决策点：业务系统需要处理大量非结构化数据（用户行为轨迹）、频繁的复杂分析查询、以及未来可能引入的时序数据需求，这些都是PostgreSQL的强项。

2. 迁移前技术准备

2.1 环境差异梳理

我们使用pgloader工具进行初步兼容性检查时，发现了几个关键差异点：

自增ID处理：

MySQL的AUTO_INCREMENT
PostgreSQL的SERIAL/IDENTITY

/* MySQL */ CREATE TABLE orders (id INT AUTO_INCREMENT PRIMARY KEY); /* PostgreSQL */ CREATE TABLE orders (id SERIAL PRIMARY KEY); -- 或PG10+推荐 CREATE TABLE orders (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY);

分页语法：
- MySQL:LIMIT 10 OFFSET 20
- PostgreSQL:LIMIT 10 OFFSET 20或更标准的FETCH FIRST 10 ROWS ONLY OFFSET 20

日期函数：

/* MySQL */ SELECT DATE_FORMAT(NOW(), '%Y-%m-%d'); /* PostgreSQL */ SELECT TO_CHAR(NOW(), 'YYYY-MM-DD');

2.2 应用层改造清单

我们的SpringBoot 2.7 + MyBatisPlus技术栈需要调整：

JDBC连接配置：

# 原MySQL配置 spring.datasource.url=jdbc:mysql://localhost:3306/db spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver # 新PG配置 spring.datasource.url=jdbc:postgresql://localhost:5432/db spring.datasource.driver-class-name=org.postgresql.Driver spring.datasource.hikari.connection-test-query=SELECT 1

MyBatisPlus方言设置：

@Configuration public class MybatisPlusConfig { @Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); // 关键修改点 interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.POSTGRE_SQL)); return interceptor; } }

3. 数据迁移实战

3.1 使用pgloader进行全量迁移

我们最终选择pgloader而不是AWS DMS，因为它能更好地处理类型转换：

pgloader \ mysql://user:pass@source-host:3306/source_db \ postgresql://user:pass@target-host:5432/target_db \ --with "batch size=10000" \ --with "prefetch rows=50000" \ --with "workers=8" \ --with "max parallel create index=4"

遇到的坑与解决方案：

编码问题：
- MySQL的utf8mb4需要明确指定：
```
CAST type varchar to varchar encoding 'utf8mb4'
```

默认值差异：

MySQL的ON UPDATE CURRENT_TIMESTAMP在PG中需要用触发器实现：

CREATE OR REPLACE FUNCTION update_modified_column() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER update_customer_modtime BEFORE UPDATE ON customers FOR EACH ROW EXECUTE FUNCTION update_modified_column();

3.2 增量数据同步方案

采用Debezium + Kafka实现CDC：

# debezium-connector-mysql配置 { "name": "inventory-connector", "config": { "connector.class": "io.debezium.connector.mysql.MySqlConnector", "database.hostname": "mysql", "database.port": "3306", "database.user": "debezium", "database.password": "dbz", "database.server.id": "184054", "database.server.name": "dbserver1", "database.include.list": "inventory", "database.history.kafka.bootstrap.servers": "kafka:9092", "database.history.kafka.topic": "schema-changes.inventory" } }

4. 性能优化实践

4.1 索引策略调整

PostgreSQL的BRIN索引在时间序列数据上表现出色：

-- 传统B-tree索引 CREATE INDEX idx_orders_created ON orders(created_at); -- 针对10亿级数据表的BRIN索引 CREATE INDEX idx_orders_created_brin ON orders USING BRIN(created_at) WITH (pages_per_range=128);

实测效果：

索引类型	索引大小	查询延迟(ms)
B-tree	14GB	23
BRIN	48MB	27

4.2 连接池优化

HikariCP配置调整：

spring.datasource.hikari: maximum-pool-size: 20 # PG建议值是(CPU核心*2)+有效磁盘数 idle-timeout: 30000 max-lifetime: 1800000 connection-timeout: 5000 leak-detection-threshold: 60000 >@Transactional(isolation = Isolation.REPEATABLE_READ) public void batchInsert(List<Order> orders) { orderMapper.insertBatchSomeColumn(orders); }

5.2 JSONB查询优化

错误写法：

queryWrapper.apply("json_extract(extra_info, '$.channel') = 'wechat'");

正确姿势：

queryWrapper.apply("extra_info::jsonb->>'channel' = 'wechat'");

性能对比：

方式	执行时间(100万数据)
JSON解析函数	1200ms
JSONB原生操作	280ms

6. 监控体系搭建

使用pg_stat_statements扩展收集SQL指标：

CREATE EXTENSION pg_stat_statements; -- 查询TOP10慢SQL SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 10;

Grafana监控看板关键指标：