PostgreSQL (七) 性能优化_pg库查询性能优化

作者：2023面试高手 | 2024-02-23 08:57:12

踩

pg库查询性能优化

1.SQL优化

1.1.SQL执行顺序

序号	顺序
1	FROM
2	ON
3	JOIN
4	WHERE
5	GROUP BY
6	HAVING
7	DISTINCT
8	ORDER BY
9	LIMIT

1.2.优化方案

序号	优化
1	尽量不使用子查询
2	索引的数量不宜过多, 每次新增或修改数据都会新建或修改索引, 消耗性能, 因此不要超过5个
3	尽量不要使用*查询所有字段, 否则索引失效
4	使用排序时, 尽量使用有索引字段
5	如果两张表没有重复数据, 则尽量使用union all, 而不是union
6	`select * from 表A where id in (select id from 表B)` 和 `select * from 表A where id exists (select id from 表B)`, 如果A表数据量大于B表, 则使用in, 反之使用exists
7	尽量使用数字型字段就不要使用字符类型,能使用小的类型就使用小的类型, varchar可以根据字符串长度自动调整, 金融类数字使用decimal
8	避免使用部分查询, 从而导致索引失效, 如: !=、<>、not in、not exists、not like、is null、is not null、or, 使用IN替代OR
9	LIKE 查询不要用%开头, 如: LIKE ‘%fra%’, 而使用LIKE ‘fra%’
10	不要在where的等于号左侧使用表达式和函数运算
11	order by的时候使用字段尽量在where条件的字段
12	多表查询时, 小表在前, 大表在后
13	尽量使用别名, 减少解析的时间
14	使用LIMIT M OFFSET N分页查询时, 如果数据量过大时, 可以使用`where id > N LIMIT M`
15	join表数量进来不要超过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:

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

SQL2:

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

使用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等.

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/2023面试高手/article/detail/135196

PostgreSQL (七) 性能优化_pg库 查询性能优化