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_INCREMENTPostgreSQL的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 20PostgreSQL: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.urljdbc:mysql://localhost:3306/db spring.datasource.driver-class-namecom.mysql.cj.jdbc.Driver # 新PG配置 spring.datasource.urljdbc:postgresql://localhost:5432/db spring.datasource.driver-class-nameorg.postgresql.Driver spring.datasource.hikari.connection-test-querySELECT 1MyBatisPlus方言设置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:passsource-host:3306/source_db \ postgresql://user:passtarget-host:5432/target_db \ --with batch size10000 \ --with prefetch rows50000 \ --with workers8 \ --with max parallel create index4遇到的坑与解决方案编码问题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_range128);实测效果索引类型索引大小查询延迟(ms)B-tree14GB23BRIN48MB274.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(ListOrder orders) { orderMapper.insertBatchSomeColumn(orders); }5.2 JSONB查询优化错误写法queryWrapper.apply(json_extract(extra_info, $.channel) wechat);正确姿势queryWrapper.apply(extra_info::jsonb-channel wechat);性能对比方式执行时间(100万数据)JSON解析函数1200msJSONB原生操作280ms6. 监控体系搭建使用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监控看板关键指标连接数波动锁等待时间WAL写入延迟缓存命中率7. 最终收益与建议迁移三个月后的核心指标对比指标MySQLPostgreSQL提升平均查询延迟86ms32ms62%高峰期TPS1,2002,800133%存储空间占用1.4TB0.9TB35%给后来者的建议类型转换要提前用pgloader的dry run模式验证在测试环境模拟生产负载的2倍压力准备完善的回滚方案我们准备了LVM快照Binlog双保险重点检查使用了MySQL特有函数的SQL如GROUP_CONCAT要用STRING_AGG替代