PostgreSQL (七) 性能优化

1.SQL优化

1.1.SQL执行顺序

序号顺序1FROM2ON3JOIN4WHERE5GROUP BY6HAVING7DISTINCT8ORDER BY9LIMIT

1.2.优化方案

序号优化1尽量不使用子查询2索引的数量不宜过多, 每次新增或修改数据都会新建或修改索引, 消耗性能, 因此不要超过5个3尽量不要使用*查询所有字段, 否则索引失效4使用排序时, 尽量使用有索引字段5如果两张表没有重复数据, 则尽量使用union all, 而不是union6

select * from 表A where id in (select id from 表B)

和

select * from 表A where id exists (select id from 表B)

, 如果A表数据量大于B表, 则使用in, 反之使用exists7尽量使用数字型字段就不要使用字符类型,能使用小的类型就使用小的类型, varchar可以根据字符串长度自动调整, 金融类数字使用decimal8避免使用部分查询, 从而导致索引失效, 如: !=、<>、not in、not exists、not like、is null、is not null、or, 使用IN替代OR9LIKE 查询不要用%开头, 如: LIKE ‘%fra%’, 而使用LIKE ‘fra%’10不要在where的等于号左侧使用表达式和函数运算11order by的时候使用字段尽量在where条件的字段12多表查询时, 小表在前, 大表在后13尽量使用别名, 减少解析的时间14使用LIMIT M OFFSET N分页查询时, 如果数据量过大时, 可以使用

where id > N LIMIT M

15join表数量进来不要超过3个16查询时where条件尽量遵循索引最左前缀原则, 即: where查询的前几列条件字段对应索引的前几列

2.优化数据库结构

2.1.增加冗余字段

A和B两张表属于关联表, 其中B表的一个字段field_1, 经常被A表连表查询, 这样会减少查询速度, 尤其是在数据量较大的时候, 影响性能. 如果在这种类型字段不多情况下, 可以在A表中增加field_1字段, 避免连表查询.
缺点: 当B表中field_1字段发生改变时, 就要同时改变其他表关于这个字端的冗余字段.

2.2.批量插入

2.2.1.SQL语句的选择

SQL1:

INSERTINTO table_name values(1,'fracong1',30);INSERTINTO table_name values(2,'fracong2',31);INSERTINTO table_name values(3,'fracong3',32);

SQL2:

INSERTINTO table_name values(1,'fracong1',30),(2,'fracong2',31),(3,'fracong3',32);

使用SQL2的速度要快于使用SQL1的速度.

2.2.2.删除索引/外键约束

索引的目的是为了加快查询速度, 每插入一条数据, 就会创建一个索引记录. 但在在插入大量数据的时候, 创建索引会极大的降低插入记录的速度. 这个时候, 可以先删除索引, 等到完全插入好数据之后, 再新建索引.
同理, 外键约束也是如此, 每次插入新的数据,都会去校验是否有外键约束, 这样也降低了插入速度.

2.2.3.使用COPY命令

可以参考之前我写的文章: Java使用Postgresql的Copy功能大量数据保存数据库
使用COPY语句导入数据的速度比使用INSERT插入数据的数度要快.

2.2.4.关闭自动提交

在允许自动独立提交时, 每插入一条数据数据, 就多了大量的事务记录, 降低了批量插入操作的速度.可以在插入前关闭自动提交, 在完成插入后, 恢复自动提交.

2.3.增加中间表

如果两个关联表查询次数较多, 可以考虑将两个表需要经常查询的字段, 集中在一个一张中间表上, 只需要查询中间表, 避免了连表查询.
缺点: 如果两张表发生变化, 这张中间表也要及时更新. 如果数据量过大, 也不要做中间表.

2.4.拆分表

以PostgreSQL为例, 最多的字段个数为1664个, 但不是说就可以建1664个, 字段越多, 查询的速度也会越慢. 通常情况下, 一张表的字段最大数量控制在20到50之间, 尽量小于20个.
如果一张表当中有部分字段属于不经常读取的字段, 可以将这部分的字段提取出来组成一个新表, 使用关联表的方式, 通过第一张表的ID查询不经常使用的字段.从而提高了第一张表查询速度.

3.优化硬件和参数

硬件方面: 一般公司不会采取优化, 因为成本过高. 不过也可以采取以下的优化: 使用较大内存, 配置高度磁盘系统, 合理分配磁盘I/O, 配置多处理器.

参数方面:
1.max_connections 最大连接数, 在内存分配合理的情况下, 配置越高的服务器, 尽可能配置多的连接数.
2.shared_buffers 缓冲区, 通常设置为10%到25%, 如果内存很大的话, 可以配置更大的缓冲区.
3.effective_cache_size 使用的最大缓存, 设置为50%为保守设置, 75%为正常设置, 该值是一个估计值, 并不占据内存.
4.work_mem: 单个连接用户使用的内存, 在使用的时候, 通常是work_mem * max_connections, 不要超过实际使用的内存.
当work_mem设置过小时, 排序的时候, 会生成几个临时文件进行结果集排序, 极大消耗性能, 速度较慢.
而设置稍大的work_mem后, 将不会生成临时文件,而是将结果集放在内存中进行比较排序, 这样可以提高性能.
5.maintenance_work_mem:主要会影响vacuum,analyze,create index,reindex等操作, 系统默认值通常为64MB, 如果上述操作比较频繁, 可以调高一点内存.
6.其他参数: wal_buffers、checkpoint_timeout、synchronous_commit、default_statistics_target、max_wal_size、min_wal_size、wal_sync_method、checkpoint_completion_target等.