SQL优化是数据库优化中十分重要的手段，也是见效最快的手段。有时候添加一个索引，修改一条SQL语句能够把一个即将崩溃的数据库系统挽救回来。SQL优化的手段很多，大家比较常用的是添加索引、调整表连接方式、改写等价SQL等方法。
实际上POSTGRESQL数据库的SQL优化技巧大多数是和其他数据库，比如Oracle是类似的，因为现代数据库产品的核心技术就是CBO优化器技术。POSTGRESQL在内的多种商用和开源数据库产品的CBO优化器也大量参考了Oracle的技术，Oracle也不断的在吸收其他数据库产品的CBO技术。虽然在具体技术实现上存在一定的差异，不过SQL执行的基本技术路线是趋同的。
因此在做POSTGRESQL的SQL优化的时候，尽可以借鉴其他数据库的SQL优化经验。今天的这个话题中，我们不会讨论通用数据库技术的一些SQL优化技巧，而是结合POSTGRESQL数据库的特点来讨论一些SQL优化的实用技巧。这些技巧可能很多都是总体性的，并不能直接应用于你的SQL之中，不过这些技巧如果应用得当，会让你的POSTGRESQL数据库的SQL性能得到极大的提升。
技巧一：确保SQL语句使用了合适的扫描方式。很多做SQL优化的人认为只要表上做了Seq Scan，那么这个执行计划一定是有问题的，一定要让SQL采用Index Scan才行。这其实也是一个误区，在某些情况下，Index Scan并不是最优的，POSTGRESQL的优化器会根据两种扫描方式的成本来确定采用哪种扫描方式。这和表的数据量、扫描返回的元组的比例、存储系统顺序读与离散读的性能差异、操作系统的CACHE大小等都有关系。
在大多数情况下，优化器都可以找到最佳的扫描方式。不过在一些临界状态，Seq Scan和Index Scan的成本十分接近，此时很容易出现执行计划偏差，发现这些偏差，并纠正执行计划，对于SQL性能优化十分有效。当缺乏适当的索引可用的时候，使用Parallel Seq Scan替代Seq Scan也是提高SQL性能的十分重要的手段。
技巧二：确保多表关联使用了正确的连接方式。使用正确的连接方式，对于表连接的性能来说十分关键。如果你通过explain发现某条SQL的cost很大，那么首先你应该看看哪个地方存在较大的cost，如果cost集中在Join上，那么你应该首先考虑是不是这条SQL使用了不合理的表连接方式。有时候缺少必要的索引也是导致join性能问题的主因，因此如果你发现了SQL使用了错误的连接方式的时候，不要总是考虑强制指定连接方式，而是先考虑是不是可以通过索引来优化连接方式。
Nested Loop Join是最为常见的表连接方式，比较适合于外表结果集不大，内表于外表的连接条件有较好的索引的场景；Merge Join适合于内表与外表结果集大小差异较小，连接键存在排序的场景；Hash Join比较适合于内外表结果集大小存在一定差异，并且外表结果集比较大的场景，这种场景使用Nested Loop Join的效果较差。根据上述的场景分析，仔细分析你的SQL的表连接方式，发现存在错误连接方式的执行计划。一般来说如果表连接顺序出现问题，大多数情况是因为表的统计数据出现错误，让优化器产生了错误的执行计划，通过更新统计数据大多数情况下可以解决问题。不过如果你的POSTGRESQL数据库禁用了Hash Join，也可能会让优化器选择Nested Loop Join或者Merge Join替代Hash Join。在某些情况下，如果你能够确定某种表连接方式肯定是最优的，那么你也可以通过强制指定的方式来让SQL使用某种表连接方式。
技巧三：对于复杂的多表查询，确保表连接的顺序是最优的。必要时可通过SQL语法来固定表连接的顺序，从而避免优化器产生错误的执行计划。实际上，在很多情况下，软件开发人员是知道数据之间的连接顺序与关系的，而运维人员要知道这一切需要花费更多的时间。对于一些复杂的容易出现执行计划错误的SQL语句，可以使用显式Join语句替代隐式关联语句。并可以在会话级禁止SQL rewrite，从而确保执行计划是按照业务逻辑去做关联的。这种SQL编写方式也存在一定的风险，如果业务数据发生了较大的变化，那么可能你所预先设定的连接顺序是错误的，会引发新的性能问题。
技巧四：尽可能不要编写select * from 这样的语句。你需要查询哪些字段，就写清除哪些字段。这对于减少IO以及在某些时候优化器可以选择性能更好的Index only scan操作有帮助。
技巧五：分页查询的性能。分页查询是应用系统中最为常用的，POSTGRESQL数据库对于分页查询有很好的支持，采用下面的语法就可以轻松的写出分页查询语句。
select … from … order by … limit <n> offset <m>;
比如下面的一个语句：
select * from test_outer order by o_entry_d limit 50 offset 1000;
通过explain我们来看看这条语句的成本：
=# explain (analyze)select * from test_outer order by o_entry_d limit 50 offset 1000;
QUERY PLAN
-------------------------------------------------------------------------------
Limit (cost=434939.44..434945.27 rows=50 width=36) (actual time=515.714..515.725 rows=50 loops=1)
-> Gather Merge (cost=434822.76..1627087.20 rows=10218696 width=36) (actual time=515.529..516.793 rows=1050 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=433822.74..446596.11 rows=5109348 width=36) (actual time=511.974..512.016 rows=957 loops=3)
Sort Key: o_entry_d
Sort Method: top-N heapsort Memory: 179kB
Worker 0: Sort Method: top-N heapsort Memory: 188kB
Worker 1: Sort Method: top-N heapsort Memory: 191kB
-> Parallel Seq Scan on test_outer (cost=0.00..151884.48 rows=5109348 width=36) (actual time=0.003..227.158 rows=4087478 loops=3)
Planning Time: 0.062 ms
Execution Time: 516.873 ms
(12 rows)
在一个有1亿2千多万条记录的表中做一个分页查询，从第1000条记录开始读取50条记录，耗时516.873毫秒，执行的效率还是不错的。下面我们再来看看，如果从第100完条记录开始查找50条的执行效果：
=# explain (analyze)select * from test_outer order by o_entry_d limit 50 offset 1000000;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=998528.95..998534.78 rows=50 width=36) (actual time=1676.440..1676.451 rows=50 loops=1)
-> Gather Merge (cost=881854.14..2074118.58 rows=10218696 width=36) (actual time=1465.918..1672.129 rows=1000050 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=880854.11..893627.48 rows=5109348 width=36) (actual time=1460.427..1491.615 rows=333850 loops=3)
Sort Key: o_entry_d
Sort Method: external merge Disk: 197344kB
Worker 0: Sort Method: external merge Disk: 197136kB
Worker 1: Sort Method: external merge Disk: 193568kB
-> Parallel Seq Scan on test_outer (cost=0.00..151884.48 rows=5109348 width=36) (actual time=0.003..222.965 rows=4087478 loops=3)
Planning Time: 0.066 ms
Execution Time: 1721.528 ms
(12 rows
可以看到这条语句执行了1721.528毫秒，比上一条语句慢了不少。这也是分页查询语句的特点，读取靠后的记录所需要的时间比读取考前的记录要花费更多的时间。同样的测试，如果offset 为1亿，则执行时间变成了6秒多。对此我们如何去优化呢？
如果我们要读取最后的n条数据，那么如果使用降序排序，然后再扫描，效果要好得多。使用order by o_entry_d desc limit 50 offset 0这样的语法，执行时间又变成500多毫秒了。关于分页查询的这个小技巧大家一定要记住。
技巧六：分合理使用并行扫描。目前我们使用的X86服务器的CPU线程数很多，CPU资源,IO能力都大大提升了。因此当系统资源不存在瓶颈的前提下，并行查询可以解决一些我们在通过其他优化方式无法解决的问题。
技巧七：合理使用POSTGRESQL丰富的索引来进行SQL优化。POSTGRESQL的索引十分丰富，针对一些特殊的SQL语句，POSTGRESQL可以使用某些特殊的索引来进行优化。比如使用部分索引来减少索引维护的成本；使用覆盖索引将Index Scan优化为Index Only Scan，从而提升SQL执行效率。在POSTGRESQL数据库中甚至可以为某种特殊的应用场景创建特殊的索引来解决系统的性能问题。因此在POSTGRESQL的SQL优化工作中，一定要认真研究使用适当的索引类型来优化某个比较难以解决的问题，这种工作思路是其他数据库中所没有的，对于解决复杂的SQL性能问题十分有效。
技巧八：确保统计数据为最新的。POSTGRESQL的CBO优化器十分依赖于表的统计数据的准确性，如果你经常发现某条SQL的执行计划会出现错误，那么首先你需要分析一下表上的统计数是否准确。POSTGRESQL数据库虽然默认是开启AUTOVACUUM的，在AUTOVACUUM操作中，会根据表数据的变化情况，自动进行表分析操作。不过有时候系统级的定义对于某些表来说不一定有效，同时因为一些其他的因素，有可能某张表并没有及时的执行表分析操作。
=# select relname,last_analyze,last_autoanalyze frompg_stat_all_tables where last_autoanalyze is not null;
relname | last_analyze | last_autoanalyze
----------+--------------+-------------------------------
pdrdb | | 2021-09-30 08:10:35.457991+08
pdrsql | | 2021-09-30 07:10:33.380828+08
pdrtable | | 2021-09-30 08:10:35.521596+08
(3 rows)
如上面的查询，我们可以通过pg_stat_all_tables视图去查看某张表最后autoanalyze的时间，如果该时间过旧，而这张表的数据变化十分频繁，那么可能表分析数据是导致执行计划错误的原因。
技巧九：采用分区表。分区表往往是优化大数据量业务的利器，分区表可以让每个表分区的数据量得到有效的控制，从而减少Seq Scan的成本，也可以降低表维护的成本。对于部分SQL语句的性能优化也有一定的效果。根据业务的特点，选择合适的表分区类别，可以有效的提高某些SQL的性能。比如HASH 分区可以有效地避免表数据的热块冲突，提升大并发量写入的性能。与业务有关的时间范围分区可以让业务经常访问的最新的分区的数据总量得到控制，从而提升业务系统SQL的性能。同时按照时间的范围分区还可以让数据归档变得简单。在设计表结构的时候，根据业务特点，对大表设计合理的表分区，对于应用系统长期的高性能运行十分关键。
技巧十：定期归档历史数据。几乎所有的DBA与开发人员都知道，数据量大了，性能就不容易保障了。确保应用系统的数据量不随着系统上线的时间呈线性上升，是确保系统运行数年后仍能高性能运行的十分关键的工作。通过定期归档历史数据可以实现这个目标。不过要想实现定期归档历史数据，必须在系统设计建设阶段就做好设计与规划。应用系统可以正确的从历史数据库读取历史数据。
通过数据清理和数据归档的实施，可以提高数据库性能，应用可以更加迅捷地为客户提供优质服务，从而提高企业信誉、提升企业的核心竞争力。
- 内部业务利益
- 提高系统性能
- 更加充分地利用现有资源
- 增强系统稳定性
- 适应业务部门新的需求
- 降低运营成本
- 减少生产数据库的规模
- 缩短备份恢复时间
任何一个数据都有一定的生命周期，从数据产生（输入或者被采集）到使用到过期。所不同的是数据的保存周期不同，数据的保存方式不同。一般来说，根据数据生命周期和保管方式不同，可以分为几大类：
- 永久在线数据：这类数据不需要归档，从系统上线开始一直在线。这类数据的特点是数据量不大（一般从几十K到几百M不等），数据较为静态，变更量不大（比如参数数据）
- 周期性在线数据：这类数据往往在某个周期性事件中被采集往往具有很强的时间周期特性（比如月度周期），这些数据在采集后被集中处理，处理形成汇总或者帐务数据。数据处理后，在一定周期内还需要被查询，一定时间周期后查询量逐渐减少
- 工作流性质的数据：具有一定的实效性，根据流程的状态判断是否需要继续一级在线，归档不仅仅取决于时间，还取决于某些条件
- 一级汇总数据：根据明细汇总的数据，可以在明细数据归档或者删除后提供统计查询。一般在线1年或者数年
- 二级汇总数据：在一级汇总数据基础上汇总的数据，数据量较小，可以长期保留
- 临时数据：临时使用，使用后应该马上清理的数据。不过在往往清理力度不足，导致长期在线
每个数据在其生命周期内，其存储的方式也有多种形态， 根据其访问的便利性，在系统建设阶段可以进行生产库、查询库、历史库、归档库等的设计。比如某套生产系统，可以分为多个数据库来存访各种数据：
-
-
- 生产库在线存放，当前数据：保存在生产库的当前数据表中
- 生产库在线存放，历史数据：保存在生产库的历时数据表中
- 历史库在线存放：从生产库中转移到历史库，并在生产库中删除该数据
- 离线归档存放：存放在离线介质中（比如对象存储）
-
技巧十一：通过读写分离分离系统负载。如果POSTGRESQL数据库系统的负载很高，服务器资源存在不足的现象，而POSTGRESQL数据库又没有类似Oracle RAC的横向扩展能力，如何解决这个问题呢？采用读写分离集群来扩展POSTGRESQL的并发处理能力是一种常用的方法。对于绝大多数业务系统来说，数据库服务器的读写比例为8：2甚至9：1，大部分的系统负载只读的SELECT语句产生的，因此如果能够在系统设计之初就为读写分离做好设计。使用POSTGRESQLPOOL、PATRONI等技术构建POSTGRESQL数据库的读写分离集群，就可以大大提升POSTGRESQL数据库的横向扩展能力，让POSTGRESQL数据库在大型系统中发挥巨大的作用。
扫一扫
3358
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
