一、整体调优流程
1、 收集SQL中涉及到的所有表的统计信息。在数据库中,统计信息是规划器生成计划的源数据。没有收集统计信息或者统计信息陈旧往往会造成执行计划严重劣化,从而导致性能问题。从经验数据来看,10%左右性能问题是因为没有收集统计信息。具体请参见更新统计信息。
2、 通过查看执行计划来查找原因。如果SQL长时间运行未结束,通过EXPLAIN命令查看执行计划,进行初步定位。如果SQL可以运行出来,则推荐使用EXPLAIN ANALYZE或EXPLAIN PERFORMANCE查看执行计划及实际运行情况,以便更精准地定位问题原因。有关执行计划的详细介绍请参见SQL执行计划介绍。
3、 审视和修改表定义。
4、 针对EXPLAIN或EXPLAIN PERFORMANCE信息,定位SQL慢的具体原因以及改进措施,具体参见典型SQL调优点。
5、 通常情况下,有些SQL语句可以通过查询重写转换成等价的,或特定场景下等价的语句。重写后的语句比原语句更简单,且可以简化某些执行步骤达到提升性能的目的。查询重写方法在各个数据库中基本是通用的。经验总结:SQL语句改写规则介绍了几种常用的通过改写SQL进行调优的方法。
二、更新统计信息
在数据库中,统计信息是规划器生成计划的源数据。没有收集统计信息或者统计信息陈旧往往会造成执行计划严重劣化,从而导致性能问题。
ANALYZE
语句可收集与数据库中表内容相关的统计信息,统计结果存储在系统表PG_STATISTIC中。查询优化器会使用这些统计数据,以生成最有效的执行计划。
建议在执行了大批量插入/删除操作后,例行对表或全库执行ANALYZE语句更新统计信息。目前默认收集统计信息的采样比例是30000行(即:GUC参数default_statistics_target默认设置为100),如果表的总行数超过一定行数(大于1600000),建议设置GUC参数default_statistics_target为-2,即按2%收集样本估算统计信息。
对于在批处理脚本或者存储过程中生成的中间表,也需要在完成数据生成之后显式的调用ANALYZE。
对于表中多个列有相关性且查询中有同时基于这些列的条件或分组操作的情况,可尝试收集多列统计信息,以便查询优化器可以更准确地估算行数,并生成更有效的执行计划。
1.使用 ANALYZE 语句更新统计信息
ANALYZE customer_info;
2.使用 ANALYZE VERBOSE 语句更新统计信息,并输出表的相关信息
ANALYZE VERBOSE customer_info;
3.更新全库的统计信息
ANALYZE;
三、审视和修改表定义
好的表定义至少需要达到以下几个目标:
1、 减少扫描数据数据量:通过分区的剪枝机制可以实现该点。
2、 尽量减少随机I/O:通过聚簇/局部聚簇可以实现该点。
表定义在数据库设计阶段创建,在SQL调优过程中进行审视和修改。
1.选择存储模型
进行数据库设计时,表设计上的一些关键项将严重影响后续整库的查询性能。表设计对数据存储也有影响:好的表设计能够减少I/O操作及最小化内存使用,进而提升查询性能。
表的存储模型选择是表定义的第一步。客户业务属性是表的存储模型的决定性因素,依据下面表格选择适合当前业务的存储模型。
存储模型 | 适用场景 |
---|---|
行存 | 点查询(返回记录少,基于索引的简单查询)。 增删改比较多的场景。 |
列存 | 统计分析类查询(group 、join多的场景)。 |
2.使用局部聚簇
局部聚簇(Partial Cluster Key)是列存下的一种技术。这种技术可以通过min/max稀疏索引较快的实现基表扫描的filter过滤。Partial Cluster Key可以指定多列,但是一般不建议超过2列。
Partial Cluster Key的选取原则:
-
受基表中的简单表达式约束。这种约束一般形如col op const,其中:
-
col为列名。
-
op为操作符
=、>、>=、<=、<。
-
const为常量值。
-
-
尽量采用选择度比较高(过滤掉更多数据)的简单表达式中的列。
-
尽量把选择度比较低的约束 col 放在Partial Cluster Key中的前面。
-
尽量把枚举类型的列放在Partial Cluster Key中的前面。
3.使用分区表
分区表是把逻辑上的一张表根据某种方案分成几张物理块进行存储。这张逻辑上的表称之为分区表,物理块称之为分区。分区表是一张逻辑表,不存储数据,数据实际是存储在分区上的。分区表和普通表相比具有以下优点:
1、 改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索效率。
2、 增强可用性:如果分区表的某个分区出现故障,表在其他分区的数据仍然可用。
3、 方便维护:如果分区表的某个分区出现故障,需要修复数据,只修复该分区即可。
4.选择数据类型
高效数据类型,主要包括以下三方面:
1、尽量使用执行效率比较高的数据类型
一般来说整型数据运算(包括=、>、<、≧、≦、≠等常规的比较运算,以及group by
)的效率比字符串、浮点数要高。比如某客户场景中对列存表进行点查询,filter条件在一个numeric列上,执行时间为10+s;修改numeric为int类型之后,执行时间缩短为1.8s左右。
2、尽量使用短字段的数据类型
长度较短的数据类型不仅可以减小数据文件的大小,提升I/O性能;同时也可以减小相关计算时的内存消耗,提升计算性能。比如对于整型数据,如果可以用smallint就尽量不用int,如果可以用int就尽量不用bigint。
3、使用一致的数据类型
表关联列尽量使用相同的数据类型。如果表关联列数据类型不同,数据库必须动态地转化为相同的数据类型进行比较,这种转换会带来一定的性能开销。
未完待续。。持续更新。。。