目录
数据库查询预准备
1. VACUUM
- vacuum只是简单的回收空间且令其可以再次使用,没有请求排它锁,仍旧可以对表读写
- vacuum full执行更广泛的处理,包括跨块移动行,以便把表压缩至使用最少的磁盘块数目存储。相对vacuum要慢,而且会请求排它锁。
- 定期执行:在日常维护中,需要对数据字典定期执行vacuum,可以每天在数据库空闲的时候进行。然后每隔一段较长时间(两三个月)对系统表执行一次vacuum full,这个操作需要停机,比较耗时,大表可能耗时几个小时。
- reindex:执行vacuum之后,最好对表上的索引进行重建
2. ANALYZE
- 命令:analyze [talbe [(column,..)]]
- 收集表内容的统计信息,以优化执行计划。如创建索引后,执行此命令,对于随即查询将会利用索引。
- 自动统计信息收集
- 在postgresql.conf中有控制自动收集的参数gp_autostats_mode设置,gp_autostats_mode三个值:none、no_change、on_no_stats(默认)
- none:禁止收集统计信息
- on change:当一条DML执行后影响的行数超过gp_autostats_on_change_threshold参数指定的值时,会执行完这条DML后再自动执行一个analyze 的操作来收集表的统计信息。
- no_no_stats:当使用create talbe as select 、insert 、copy时,如果在目标表中没有收集过统计信息,那么会自动执行analyze 来收集这张表的信息。gp默认使用on_no_stats,对数据库的消耗比较小,但是对于不断变更的表,数据库在第一次收集统计信息之后就不会再收集了。需要人为定时执行analyze.
如果有大量的运行时间在1分钟以下的SQL,你会发现大量的时间消耗在收集统计信息上。为了降低这一部分的消耗,可以指定对某些列不收集统计信息,如下所示:
1. create table test(id int, name text,note text);
上面是已知道表列note不需出现在join列上,也不会出现在where语句的过滤条件下,因为可以把这个列设置为不收集统计信息:
1. alter table test alter note SET STATISTICS 0;
3. EXPLAIN执行计划
显示规划器为所提供的语句生成的执行规划。
- cost:返回第一行记录前的启动时间, 和返回所有记录的总时间(以磁盘页面存取为
单位计量) - rows:根据统计信息估计SQL返回结果集的行数
- width:返回的结果集的每一行的长度,这个长度值是根据pg_statistic表中的统计信息
来计算的。
4. 两种聚合方式
- hashaggregate
根据group by字段后面的值算出hash值,并根据前面使用的聚合函数在内存中维护对应的列表,几个聚合函数就有几个数组。相同数据量的情况下,聚合字段的重复度越小,使用的内存越大。 - groupaggregate
先将表中的数据按照group by的字段排序,在对排好序的数据进行全扫描,并进行聚合函数计算。消耗内存基本是恒定的。 - 选择
在SQL中有大量的聚合函数,group by的字段重复值比较少的时候,应该用groupaggregate
5. 关联
分为三类:hash join、nestloop join、merge join,在保证sql执行正确的前提下,规划器优先采用hash join。
- hash join: 先对其中一张关联