![c887aa6a6dd4daf2830da94830ce49e7.png](https://img-blog.csdnimg.cn/img_convert/c887aa6a6dd4daf2830da94830ce49e7.png)
PostgreSQL已经成为当下最流行的开源关系数据库系统之一。当客户从Oracle及微软SQL Server等商业数据库向外迁移时,PostgreSQL已经成为一大首选替代方案。目前,AWS为大家提供两种PostgreSQL托管选项:Amazon RDS与Amazon Aurora。
除了提供托管PostgreSQL服务之外,AWS还准备了一系列用于协助迁移的工具与资源。AWS Schema Conversion Tool(SCT)就是一款免费AWS工具,可帮助您转换现有schema,且同时支持多个源数据库与目标数据库。AWS Database Migration Service(数据库迁移服务,简称DMS)则用于在异构数据库与同构数据库之间完成数据的传输与连接复制。当然,AWS还提供迁移指导手册,其中包含关于商业数据库以及开源数据库(包括PostgreSQL)之间的大量功能映射说明。
在今天的文章中,我们将介绍从PL/SQL转换为PL/pgSQL的技巧与最佳实践,希望帮助大家在顺利将代码转换为PostgreSQL形式的同时,获取良好的数据库运行性能。本文主要面向从事数据库迁移的开发人员,并要求您预先掌握关于数据库及PL/SQL的基础知识。
性能考量
本节主要探讨一系列在从商业或传统数据库(例如SQL Server及Oracle)向PostgreSQL迁移时,可能对数据库性能造成影响的具体因素。虽然大部分数据库中包含类似的对象,但其中仍有部分对象可能在迁移之后影响到系统的运作方式。本节将向大家介绍如何通过调整存储流程、函数以及SQL语句获得更好的性能。
数据类型
为了避免不必要的返工,在正式启动迁移项目之前,大家需要将目标数据库中的数据类型与源系统正确映射起来。下表总结了从Oracle到SQL Server、再到PostgreSQL的一系列常见数据类型映射。
![9d3cb86f6b2af9ba59ff6be1cc354ab8.png](https://img-blog.csdnimg.cn/img_convert/9d3cb86f6b2af9ba59ff6be1cc354ab8.png)
为什么要使用smallint/integer/bigint,而不直接使用数字?
要在数据库中获取最佳性能,选择最适合的数据类型非常重要。
如果您的表列中最多只能包含四位数字,那么具有2字节(smallint)的列数据类型就足以完成任务,意味着我们不必将其定义为4字节(整数/实数)、8字节(bigint/双精度)或者可变字节(数字)等更占资源的数据类型。
数值是一种可以容纳13万1千个数位的复杂类型,主要用于表示货币金额及其他少数需要极高精度的数量。但与整数类型或者浮点类型相比,数字的运算符处理速度很慢,因此计算速度也相当缓慢。
在下表的示例当中,我们可以看到在分别使用smallint/int/bigint建立无索引非精确列时,表整体大小发生的变化。
![fc23e1f585995bd3e88ff7550d22ac22.png](https://img-blog.csdnimg.cn/img_convert/fc23e1f585995bd3e88ff7550d22ac22.png)
AWS SCT在不了解实际数据大小的情况下,也能够将数字与表中的数字数据类型映射起来。这款工具还提供选项,帮助用户在转换过程中配置/映射正确的数据类型。
存储过程与函数
PostgreSQL 10及较早版本并不支持存储过程。Oracle与SQL Server中的所有存储过程与函数都将被映射为PostgreSQL中的函数。但从版本11开始,PostgreSQL也引入了存储过程支持,其基本原理与Oracle类似。
PostgreSQL支持三种波动函数类别,您需要在迁移当中根据函数特性指定适当的类别,即:Volatile、Stable与Immutable。正确标记函数类别,有望给我们的数据库性能带来显著提升。
Volatile
![35e1724b475339f79f401b1330e274bd.png](https://img-blog.csdnimg.cn/img_convert/35e1724b475339f79f401b1330e274bd.png)
执行以下函数即可查看执行成本。
![598dde5845a8b3b89dfcd841bac93b18.png](https://img-blog.csdnimg.cn/img_convert/598dde5845a8b3b89dfcd841bac93b18.png)
类型表示该函数无法修改数据库。此外,Stable还表示在单一表扫描操作当中,它对于相同的参数值将始终返回相同的结果,但具体结果可能会在不同SQL语句之间有所区别。如果需要创建一条结果取决于数据库查找或者参数变量(例如当前时区)的函数,那么Stable类型往往是理想的选择。current_timestamp
函数家族就是其中的典型代表,它们的值在事务执行过程中始终保持不变。
下面是一条示例函数,用以显示执行Stable函数需要花费多长时间。
![9910a7e73e25475f552ff214ae1e2927.png](https://img-blog.csdnimg.cn/img_convert/9910a7e73e25475f552ff214ae1e2927.png)
执行以下函数即可查看执行成本。
![cb19ac2cee565ebb1cfc8445405d8c61.png](https://img-blog.csdnimg.cn/img_convert/cb19ac2cee565ebb1cfc8445405d8c61.png)
Immutable
Immutable
类型表示该函数无法修改数据库,而且在给定相同的参数值时将始终返回相同的结果。这意味着起无法自行数据库查找,也无法使用参数列表中未直接存在的信息。如果选定此选项,对该函数的一切全常数参数调用都将被立即替换为该函数的值。
下面是一条示例函数,用以显示执行Immutable函数需要花费多长时间。