【数据库学习】PostgreSQL优化

1，思路

2，执行计划

explain sql语句；  #查看执行计划。也可以使用navicat的解释功能查看。

结果说明：

QUERY PLAN
Index Scan using tenk1_unique1 on tenk1  (cost=0.00..10.01 rows=1 width=244)
--Index 使用索引
--cost：启动开销..总开销。不过事实总开销可能会低一点：带有 LIMIT 子句的查询将会在 Limit 规划节点的输入节点里很快停止。
--rows:预计输出的行数
--Filter：过滤条件Index Cond: (unique1 < 3)  --从索引中检索出的行的过滤器Filter: (stringu1 = 'xxx'::name)QUERY PLAN
--嵌套循环Nested Loop  (cost=2.37..553.11 rows=106 width=488) ->  Bitmap Heap Scan on tenk1 t1  (cost=2.37..232.35 rows=106 width=244) Recheck Cond: (unique1 < 100)--位图索引->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 width=0)Index Cond: (unique1 < 100)->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.00..3.01 rows=1 width=244)Index Cond: ("outer".unique2 = t2.unique2)QUERY PLAN
--内存 Hash 表Hash Join  (cost=232.61..741.67 rows=106 width=488)Hash Cond: ("outer".unique2 = "inner".unique2)->  Seq Scan on tenk2 t2  (cost=0.00..458.00 rows=10000 width=244)->  Hash  (cost=232.35..232.35 rows=106 width=244)->  Bitmap Heap Scan on tenk1 t1  (cost=2.37..232.35 rows=106 width=244)Recheck Cond: (unique1 < 100)->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 width=0)Index Cond: (unique1 < 100)

1）多表连接查询

①Nest Loop Join（嵌套循环连接）

1>场景

适合两个表的数据量都比较少的情况(最简单的 table join 方式)。

1. 外表为小表，且过滤后的数据量较少。
2. 内表的关联列上有高效索引(主键或者唯一性索引)。

2>举例

# 内表（t4)被外表(t5)驱动。外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回的结果集不能太大（大于1 万不适合）
select t4.*,t5.*from tmp_t4 t4,tmp_t5 t5where 1=1and t4.id = t5.idand t4.id = 1;

相当于for循环：

for(t4.data in tmp_t4) { t5.data in tmp_t5 on t5.data = t4.data}

②Hash JOIN（哈希、散列连接）

1>场景

针对那些没有索引或者其中任一个有索引的大表。
哈希连接只能应用于等值连接(如WHERE A.COL3 = B.COL4)、非等值连接(WHERE A.COL3 > B.COL4)、外连接(WHERE A.COL3 = B.COL4(+))。

2>操作步骤

优化器使用两个表中较小的表（或数据源）利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。
这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。但是在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量提高I/O 的性能。

③Sort Merge JOIN

1>场景

通常Hash JOIN的性能都优于Merge JOIN，对于那些连接列上有索引的表（已排好序）Merge JOIN性能会优于Hash JOIN。

2>操作步骤

mrege join的性能开销几乎都在前两步。

1. 对连接的每个表做全表扫描（table access full）;
2. 对table access full的结果进行排序。
3. 进行merge join对排序结果进行合并。

在全表扫描比索引范围扫描再通过rowid进行表访问更可取的情况下，merge join会比nested loops性能更佳。当表特别小或特别巨大的时候，实行全表访问可能会比索引范围扫描更有效。

3，SQL优化

对于重复的代码逻辑，sql执行速度远远大于代码逻辑。
但是，由于sql难以测试、难以复用、难以加工变量，对于复杂的逻辑不建议用在sql中。代码可以分成模块、逻辑独立、方便测试。

sql优化的思路有两种：一是：

The fastest way to do something is don’t do it

即去掉无用的步骤；二是优化算法，如让sql走更优的执行计划上。

①基于规则优化(RBO)和基于代价优化(CBO)

RBO和CBO是两种数据库引擎在执行sql语句时的优化策略。

基于规则的优化(Rule Based Optimizer)
这是一种比较老的技术，简单说基于规则的优化就是当数据库执行一条query语句的时候必须遵循预先定义好的一系列规则（比如oracle的15条规则，排名越靠前的执行引擎认为效率越高）来确定执行过程，它不关心访问表的数据分布情况，仅仅凭借规则经验来确定，所以说是一种比较粗放的优化策略。

基于代价的优化(Cost Based Optimizer)
基于代价的优化的产生就是为了解决上面RBO的弊端，让执行引擎依据预先存储到数据库中表的一些实时更新的统计信息来选择出最优代价最小的执行计划来执行query语句，CBO会根据统计信息来生成一组可能被使用到的执行计划，进而估算出每个计划的代价，从而选择出代价最小的交给执行器去执行，其中表的统计信息一般会有表大小，行数，单行长度，单列数据分布情况，索引情况等等。

总结：
基于规则的优化器更像是一个经验丰富熟知各条路段的老司机，大部分情况可以根据自己的经验来判断走哪条路可以更快的到达目的地，而基于代价的优化更像手机里面的地图，它可以选择出许多不同的路径根据实时的路况信息综合考虑路程长度，交通状况来挑出最优的路径。

4，配置优化

pg中与内存有关的配置参数：

1>shared_buffers（共享缓存区）

i>工作原理

shared_buffers是一个8KB的数组，postgres在从磁盘中查询数据前，会先查找shared_buffers的页，如果命中，就直接返回，避免从磁盘查询。
多个进程通过共享内存技术来共享缓存中的数据。

1. shared_buffers存储什么？
   表数据；
   索引，索引也存储在8K块中；
   执行计划，存储基于会话的执行计划，会话结束，缓存的计划也就被丢弃。

2. 什么时候加载shared_buffers？
   1）在访问数据时，数据会先加载到os缓存，然后再加载到shared_buffers，这个加载过程可能是一些查询，也可以使用pg_prewarm预热缓存。
   2）当然也可能同时存在os和shared_buffers两份一样的缓存（双缓存）。
   3）查找到的时候会先在shared_buffers查找是否有缓存，如果没有再到os缓存查找，最后再从磁盘获取。
   4）os缓存使用简单的LRU（移除最近最久未使用的缓存），而数据库采用的优化的时钟扫描，即缓存使用频率高的会被保存，低的被移除。

ii>优化策略

提高shared_buffers，增加缓存命中率，提高查询效率。
同时为了避免Double Buffering问题，将shared_buffers设置较小，更多的内存留给文件系统使用。

1. 【Double Buffering（双缓存）】问题：
   pg的数据文件都存储在文件系统中，os的文件系统也有缓存，这导致pg的数据库副本可能同时存在于共享内存和文件系统中，造成内存利用率低的问题。
   Oracle中通过设置Birect I/O避免双缓存问题，但pg不支持。 shared_buffers的大小不应该超过内存的1/4。

2. shared_buffers设置的合理范围
   1）windows服务器有用范围是64MB到512MB，默认128MB
   2）linux服务器建议设置为25%，亚马逊服务器设置为75%（避免双缓存，数据会存储在os和shared_buffers两份）
   os缓存的重要性：数据写入时，从内存到磁盘，这个页面就会被标记为脏页，一旦被标记为脏页，它就会被刷新到os缓存，然后写入磁盘。所以如果os高速缓存大小较小，则它不能重新排序写入并优化io，这对于繁重的写入来说非常致命，因此os的缓存大小也非常重要。给予shared_buffers太大或太小都会损害性能。

3. shared_buffers调整策略

2> work_mem

为每个进程单独分配的内存，主要用于group by, sort, hash agg, hash join 等操作。
注意：work_mem是每次分配的内存，加入有M个并发进程，每个进程有N个HASH操作，那么需要分配的内存为 MNwork_mem。因此work_mem不宜设置太大，通常保持默认的4MB即可，如果设置的太大超过256MB，很容易因为瞬间的大并发操作导致oom。

3>maintenance_work_mem

为每个进程单独分配的内存，主要进行维护操作时需要的内存，如VACUUM、create index、ALTER TABLE ADD FOREIGN KEY等操作需要的内存。

4>autovacuum_work_mem

pg9.4版本新增参数。
9.4之后，AutoVacuum的worker进程分配的内存由参数autovacuum_work_mem控制，手动Vacuum时分配的内存由maintenance_work_mem 控制。9.4之前都用maintenance_work_mem 参数。
默认值为-1，表示与maintenance_work_mem 一样。
vacuum 大小 = autovacuum_max_workers * autovacuum_work_mem

5>temp_buffers（临时表缓存）

为每个不同的进程单独分配的内存，不在共享内存中，默认为8MB。

6>wal_buffers（WAL日志缓存大小）

默认为-1，表示根据shared_buffer的大小自动设置。

7>huge_pages（是否使用大页）

默认值为try，表示尽量使用大页。若os未开启大页，不使用大页内存，不影响数据库正常使用。

8>effective_cache_size（sql执行中的实际磁盘缓存）

与具体内存分配无关

5，sql审计

相关配置：

参数调整	说明
log_min_duration_statement	sql审计记录的标准，超过该时长的sql将被记录到日志文件。默认为-1，不记录超时sql。
log_statement	none默认，不记录；all-记录所有语句；ddl-记录所有数据定义语句；mod记录所有ddl和数据修改语句；
log_min_error_statement	控制日志中是否记录导致数据库出现错误的SQL语句。默认为error

6，排查sql

-- 查看表结构 
SELECT column_name,data_type FROM information_schema.columns WHERE table_name = '表名'; -- 查看慢查询日志是否开启 
SHOW log_min_duration_statement; -- 设置慢查询日志 
ALTER DATABASE test SET log_min_duration_statement TO 10000; --慢sql 添加\watch 1监控
select query,wait_event_type,wait_event from pg_stat_activity 
where wait_event is not null and now()-state_change>interval '5 second';--长wait事件
select query,wait_event_type,wait_event from pg_stat_activity where state='active' and wait_event is not null and now()-state_change>interval '5 second';-- 查找经常被扫描的大型表 
SELECT schemaname, relname, seq_scan, seq_tup_read, idx_scan, seq_tup_read / seq_scan AS avg FROM pg_stat_user_tables WHERE seq_scan > 0 ORDER BY seq_tup_read DESC LIMIT 20; -- 跟踪 vacuum 进度 
SELECT * FROM pg_stat_progress_vacuum ;

7，pg统计收集器

比较耗费性能，默认关闭，可在postgresql.conf进行配置（ stats_start_collector = true ）。

这些表的具体说明参见：华为 openGauss (GaussDB) v2.1 使用手册

1>pg_stat_activity

常见应用

-- 展示在数据库中当前正在执行多少查询 
SELECTdatname,COUNT ( * ) AS OPEN,COUNT ( * ) FILTER ( WHERE STATE = 'active' ) AS active,COUNT ( * ) FILTER ( WHERE STATE = 'idle' ) AS idle,COUNT ( * ) FILTER ( WHERE STATE = 'idle in transaction' ) AS idle_in_trans 
FROMpg_stat_activity
GROUP BYROLLUP ( 1 )-- 查看事务已经打开了多久
SELECTpid,xact_start,now( ) - xact_start AS duration 
FROMpg_stat_activity 
WHERESTATE LIKE'%transaction%' 
ORDER BY3 DESC;-- 检查是否有长查询运行 
SELECT now() - query_start AS duration, datname, query FROM pg_stat_activity WHERE state = 'active' ORDER BY 1 DESC; --查询服务进程PID: 通过count(*)获取当前连接数
select pid,usename,client_addr,client_port from pg_stat_activity;