MySQL到PostgreSQL迁移实战：从兼容性检查到性能优化

1. 为什么需要从MySQL迁移到PostgreSQL十年前我刚入行时MySQL几乎是所有项目的默认选择。但最近五年越来越多的团队开始考虑PostgreSQL。上周我刚帮一个日活百万的电商平台完成了数据库迁移整个过程踩了不少坑也积累了不少实战经验。PostgreSQL相比MySQL有几个显著优势更完善的SQL标准支持、更强大的JSON处理能力、更丰富的索引类型如GIN/GiST、原生的分区表功能。特别是在处理复杂查询和地理空间数据时PostgreSQL的性能优势非常明显。我最近经手的一个物流系统迁移后轨迹查询速度提升了3倍以上。2. 迁移前的准备工作2.1 环境评估与兼容性检查首先用pgloader工具的dry-run模式进行兼容性测试pgloader --dry-run mysql://user:passmysql_host/dbname postgresql://user:passpg_host/dbname重点关注以下兼容性问题MySQL的datetime默认值语法自增列的实现差异MySQL的AUTO_INCREMENT vs PostgreSQL的SERIAL字符串排序规则COLLATE索引长度的限制PostgreSQL没有3072字节的限制2.2 制定迁移方案根据我的经验迁移方案主要取决于业务规模数据规模推荐方案预估停机时间风险等级10GB一次性迁移30分钟低10-100GB全量增量1-2小时中100GB双写过渡按需控制高重要提示无论选择哪种方案务必在相同配置的测试环境完整演练至少3次3. 实战迁移步骤详解3.1 结构迁移使用pgloader进行基础结构迁移pgloader \ --with create no indexes \ --with create no triggers \ --with foreign keys deferred \ mysql://user:passsource/db \ postgresql://user:passtarget/db这个命令先迁移基础表结构跳过索引和触发器后续单独处理。我遇到过因为索引导致迁移速度下降10倍的情况。3.2 数据迁移优化技巧对于大表1000万行采用分批次迁移-- 在PostgreSQL创建临时表 CREATE UNLOGGED TABLE temp_orders (LIKE orders); -- 分批导入数据 pgloader \ --with batch size100MB \ --with prefetch rows5000 \ mysql://user:passsource/db \ postgresql://user:passtarget/db?tabletemp_orders迁移完成后-- 切换表原子操作 BEGIN; ALTER TABLE orders RENAME TO orders_old; ALTER TABLE temp_orders RENAME TO orders; COMMIT;3.3 索引与约束重建PostgreSQL的索引创建策略与MySQL不同-- 并发创建索引不锁表 CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id); -- 外键建议迁移后添加 ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) DEFERRABLE INITIALLY DEFERRED;4. 迁移后的关键调整4.1 参数优化postgresql.conf关键参数调整# 连接数通常比MySQL设置小30% max_connections 200 # 内存分配根据服务器内存调整 shared_buffers 4GB work_mem 16MB maintenance_work_mem 1GB # 针对从MySQL迁移的特殊设置 enable_nestloop off # 对JOIN多的查询更友好4.2 应用层适配最常见的应用层修改点LIMIT子句语法LIMIT 10 OFFSET 20→ PostgreSQL原生支持日期函数DATE_FORMAT→TO_CHAR字符串连接CONCAT()→||操作符分页查询建议改用PostgreSQL更高效的keyset pagination5. 常见问题解决方案5.1 字符集问题遇到乱码时检查-- 查看当前编码 SHOW server_encoding; -- 创建数据库时指定编码 CREATE DATABASE new_db WITH ENCODING UTF8 LC_COLLATE en_US.UTF-8 LC_CTYPE en_US.UTF-8;5.2 性能下降排查使用EXPLAIN ANALYZE定位问题EXPLAIN ANALYZE SELECT * FROM large_table WHERE json_column-property value; -- 通常需要创建GIN索引 CREATE INDEX idx_gin_json ON large_table USING GIN (json_column);5.3 存储过程重写示例MySQL存储过程转换-- MySQL DELIMITER // CREATE PROCEDURE update_stats(IN user_id INT) BEGIN -- 逻辑代码 END // DELIMITER ; -- PostgreSQL等效 CREATE OR REPLACE FUNCTION update_stats(user_id integer) RETURNS void AS $$ BEGIN -- 逻辑代码 END; $$ LANGUAGE plpgsql;6. 迁移后的监控与优化部署以下监控指标长事务监控SELECT * FROM pg_stat_activity WHERE state idle AND now() - xact_start interval 5 minutes索引使用率SELECT * FROM pg_stat_all_indexes WHERE idx_scan 0缓存命中率SELECT sum(heap_blks_hit) / (sum(heap_blks_hit) sum(heap_blks_read)) FROM pg_statio_user_tables我在实际项目中总结的黄金法则迁移后第一周必须每天检查pg_stat_statements找出TOP 10耗时查询进行优化。