iOS的并发与并行 pg并发查询

转载

charlesc 2023-11-21 07:06:29

文章标签 iOS的并发与并行 postgresql 多表关联 PostgreSQL SQL 文章分类 iOS 移动开发

PostgreSQL 9.6 版本前还不支持并行查询， SQL 无法利用多核CPU 提升性能，
PostgreSQL 9.6 版本开始支持并行查询，只是9.6 版本的并行查询所支持的范围非常有限【只在顺序扫描、多表关联、聚合查询中支持并行】
PostgreSQL 9.6 10 版本增强了并行查询功能，例如增加了并行索引扫描、并行index-only 扫描、并行bitmap heap 扫描等

本章介绍的PostgreSQL 10的并行查询功能

【6.1 并行查询相关的参数设置】
   1. max_worker_processes(integer)：设置系统支持的最大后台进程数，默认值为8 ，如果有备库，备库上此参数必须大于或等于主库上的此参数配置值，重启生效。
       ~]# grep max_worker_processes /opt/pg/data/9.5/postgresql.conf
       #max_worker_processes = 8

   2. max_parallel_workers (integer)：设置系统支持的并行查询进程数，默认值为8
       max_parallel_workers的值必须小于等于max_worker_processes 的值
       建议同时调整max_parallel_workers_per_gather参数值。
   3. max_parallel_workers_per_gather (integer):设置允许启用的并行进程的进程数，默认值为2 ，设置成0 表示禁用并行查询
       受max_worker_processes 参数和max_parallel_workers 参数限制

       这三个参数的配置值大小关系通常如下所示：
       max_worker_processes > max_parallel_workers > max_parallel_workers_per_gather

       并行查询进程数预估值由 max_parallel_workers_per_gather 控制，并行进程数预估值是指优化器解析SQL 时执行计划预计会启用的并行进程数，
       而实际执行查询时的并行进程数受参数max_worker_processes 、max_worker_processes的限制，即SQL 实际获得的并行进程数不会超过这两个参数设直的值

   4. parallel_setup_cost(floating point):设置优化器启动并行进程的成本，默认为1000 。
   5. parallel_tuple_cost(floating point):设置优化器通过并行进程处理一行数据的成本，默认为0.1 。

   6. min_parallel_table_scan_size(integer):设置开启并行的条件之一，表占用空间小于此值将不会开启并行，并行顺序扫描场景下扫描的数据大小通常等于表大小，默认值为8MB 。
   7. min_parallel_index_scan_size(integer)设置开启并行的条件之一，实际上并行索引扫描不会扫描索引所有数据块，只是扫描索引相关数据块，默认值为512kb 。
   8. force_parallel_mode (enum)强制开启并行，一般作为测试目的， OLTP 生产环境开启需慎重，一般不建议开启。

【实例】

max_worker_processes = 16
     max_parallel_workers = 8
     max_parallel_workers_per_gather = 4
     parallel_setup_cost =1000.0
     parallel_tuple_cost = 0.1
     min_parallel_table_scan_size = 8M
     min_parallel_index_scan_size = 512KB
     force_parallel_mode = off

【6.2 并行扫描】
   并行顺序扫描、并行索引扫描、并行index-only扫描、并行bitmap heap 扫描

   【并行顺序扫描】
       顺序扫描（ sequential scan ），顺序扫描通常也称之为全表扫描，
           全表扫描会扫描整张表数据，当表很大时，全表扫描会占用大量CPU 、内存、IO 资源，对数据库性能有较大影响，在OLTP 事务型数据库系统中应当尽量避免。
           Seq Scan on table即在进行顺序扫描

           PostgreSQL 9.6 版本开始支持并行处理，并行顺序扫描会产生多个子进程，并利用多个逻辑CPU 并行全表扫描

           查看执行计划
           mydb=>EXPLAIN ANALYZE SELECT * FROM test_ bigl WHERE name='l_test';
               Workers Planned 表示执行计划预估的并行进程数，
               Worker Launched 表示查询实际获得的并行进程数，
               Parallel Seq Scan on test_ big 1 表示进行了并行顺序扫描

           关闭并行查询
           mydb=> SET max parallel workers_per_gather =0 ;
   【并行索引扫描】
       Index Scan using 表示执行计划预计进行索引扫描，索引扫描也支持并行，称为并行索引扫描（ Parallel index scan ）

       创建索引：

mydb=> CREATE INDEX indx_test_big1_id ON test_big1 USING btree (id);
             CREATE INDEX

       目前PostgreSQL 10 暂不支持非btree 索引类型的并行索引扫描。

   【并行index-only 扫描】
       index-only 扫描是指只需扫描索引，也就是说SQL 仅根据索引就能获得所需检索的数据，而不需要通过索引回表查询数据。
       index-only 扫描支持并行，称为并行index-only 扫描

       【开启并行index-only扫描】
       mydb=> SET max_parallel_workers_per_gather TO default;
       SET
       mydb=> EXPLAIN SELECT count(*) FROM test_bigl WHERE id<1000000;
           ID 字段上建立了索引，统计记录数不需要再回表查询其他信息，因此进行了index-only 扫描

   【并行bitmap heap 扫描】
       Bitmap Index 扫描
       Bitmap Heap 扫描：

       当SQL的where 条件中出现or 时很有可能出现Bitmap Index 扫描，
       mydb=> EXPLAIN SELECT * FROM test_bigl WHERE id=l OR id=2 ;
           出现两次Bitmap index Scan on idx_test_bigl_id,然后将结果结合起来

           bitmap index scan 就相当于 index scan。只是它们需要组合起结果来，所以被称为 Bitmap Index Scan。
           Bitmap Index Scan 的结果组合起来，就是 Bitmap Heap Scan（可能涉及排序等）。

mydb=> SET max_parallel_workers_per_gather =0;
         SET
         mydb=> EXPLAIN ANALYZE SELECT count (*} FROM test_bigl WHERE id <1000000 OR id > 49000000;

           进行了并行Bitmap Heap 扫描
【6.3 并行聚合】
   聚合操作是指使用min()、max()、count()、sum() 等聚合函数的SQL

mydb=> SET max_parallel_workers_per_gather =4;   #建议这个数不要大于postgrep所在服务器的CPU核数
         SET
     mydb=> EXPLAIN ANALYZE SELECT count(*) FROM test_bigl;

【6.4 多表关联】
   多表关联也能用到并行扫描，例如nested loop 、merge join 、hash join
   多表关联场景能够使用并行并不是指多表关联本身使用并行，而是指多表关联涉及的表数据检索时能够使用并行处理

   【Nested loop 多表关联】
       多表关联Nested loop 实际上是一个嵌套循环，伪代码如下所示：

for (i = O; i < length(outer); i++)
             for (]= 0 ; ]< length(inner) ; j++)
                 if (outer(i] == inner[j])
                     output(outer[i], inner[j]) ;
                     
                     
         CREATE TABLE test_small(id int4 , name character varying(32));
         INSERT INTO test small(id, name) SELECT n , n||'_small' FROM generate_series(l, 8000000) n ;

创建索引并做表分析，如下所示：

mydb=> CREATE INDEX idx_test_small_id ON test_small USING btree (id);
             CREATE INDEX

       mydb=> ANALYZE test_small;
       ANALYZE 命令用于收集表上的统计信息，使优化器能够获得更准确的执行计划

mydb=> EXPLAIN ANALYZE SELECT test_small.name FROM test_big1 , test_small
                 WHERE test_bigl.id = test_small.id
                 AND test_small.id < 10000;

   【Merge join 多表关联】
       Merge join 多表关联首先将两个表进行排序，之后进行关联宇段匹配

mydb=> EXPLAIN ANALYZE SELECT test_small.name FROM test_bigl , test_small
                 WHERE test big1.id = test_small.id
                 AND test_small.id < 200000 ;

           为啥同样的查询，执行出来一个是Nested loop，一个是Merge join？？

   【Hash join 多表关联】
       PostgreSQL 多表关联也支持Hash join ，当关联宇段没有索引情况下两表关联通常会进行Hash join

mydb=> DROP INDEX idx_test_bigl_id;
         DROP INDEX
         mydb=> DROP INDEX idx_test_smal1_id;
         DROP INDEX
         
         mydb=> SET max_parallel_workers_per_gather =4;
         SET
         
         mydb=> EXPLAIN SELECT test_small.name FROM test_bigl JOIN test_small ON (test_bigl.id = test_small.id)
         AND test small.id < 100;

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。