postgresql 11 优化

• 硬件优化
• postgreSQL参数优化

服务器硬件

• 配置较大的内存
• 配置高速磁盘
• 将磁盘i/o分散到多个设备上
• 配置多CPU处理器

postgreSQL参数

• maintenance_work_mem：在装载大量数据，临时增加此参数可以改进性能（对create index,alter table操作）
• checkpoint_segments : PostgreSQL装载大量数据导致检查点操作比平常更加频繁。
• effective_cache_size: 此参数代表PostgreSQL能够使用的最大缓存（物理内存70-80%）
• max_connections: 最大连接数，防止max_connections*work_mem 超出实际内存大小
• shared_buffers: postgresql通过shared_buffers和内核和磁盘打交道，让更多数据缓存在shared_buffers中。
• work_mem: pgsql在执行排序操作时，会根据work_mem大小决定是否将结果集拆分为几个小的和work_mem差不多的临时文件（拆分会降低速度）

优化查询

• 分析查询语句explain

语句explain主要用于分析一个语句的执行规则，执行规则显示语句引用的表如何被扫描，即简单的顺序扫描还是索引扫描，并且如果引用多个表，采用什么连接算法从每个输入的表中取出所需要的记录

explain基本语法格式： explain [analyze] [verbose] statement select_options example: explain analyze select * from students where name='zhangsan';

• 子查询优化

子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作，子查询虽然可以使查询语句很灵活，但执行效率不高。执行子查询时，PostgreSQL需要为内层查询语句的查询结果建立临时表。然后外层查询语句从临时表中查询记录。查询完成后在撤销临时表。如果查询的数据量比较大，速度很变慢。

在PostgreSQL中可以使用连接join查询代替子查询，因为连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用到索引会更快，连接查询比子查询快是因为postgreSQL不需要在内存中创建临时表来完成查询工作。

优化数据库结构

• 将字段很多的表分解成多个表（将表中使用频率低的字段拆分）

• 增加中间表（对经常联合查询的表，将2表的联合查询改为对中间表的查询）

• 增加冗余字段

合理的加入冗余字段可以提高查询速度，表规范化程度高，表与表的关联就越多，需要的连接查询就越多。根据员工查员工的部门，几乎每次都要根据员工的部门id去部门表中查询，可以在员工表中增加部门字段，这样不必每次使用连接操作。

• 优化插入记录速度

删除索引（大量插入数据建立索引会降低速度，可以删除索引，插入后再建立索引） 使用批量插入（一个insert语句插入多条记录） 删除外键约束（与删除索引类似） 禁止自动提交 使用COPY批量导入（大量时比insert快）

分析标的统计信息

PostgreSQL中提供了analyze语句收集表内容的统计信息，然后把结果保存在系统表pg_statistic里。

格式如下：table表示对哪个表收集，省略表示所有表，column表示对表的哪个字段，省略表示所有字段 analyze [verbose] [table [(column[,...])]]

使用analyze分析student表： analyze verbose student;